sqlglot.dialects.presto

  1from __future__ import annotations
  2
  3import typing as t
  4
  5from sqlglot import exp, generator, parser, tokens, transforms
  6from sqlglot.dialects.dialect import (
  7    Dialect,
  8    NormalizationStrategy,
  9    binary_from_function,
 10    bool_xor_sql,
 11    build_replace_with_optional_replacement,
 12    date_trunc_to_time,
 13    datestrtodate_sql,
 14    encode_decode_sql,
 15    build_formatted_time,
 16    if_sql,
 17    left_to_substring_sql,
 18    no_ilike_sql,
 19    no_pivot_sql,
 20    no_timestamp_sql,
 21    regexp_extract_sql,
 22    rename_func,
 23    right_to_substring_sql,
 24    sha256_sql,
 25    strposition_sql,
 26    struct_extract_sql,
 27    timestamptrunc_sql,
 28    timestrtotime_sql,
 29    ts_or_ds_add_cast,
 30    unit_to_str,
 31    sequence_sql,
 32    build_regexp_extract,
 33    explode_to_unnest_sql,
 34    sha2_digest_sql,
 35)
 36from sqlglot.dialects.hive import Hive
 37from sqlglot.dialects.mysql import MySQL
 38from sqlglot.helper import apply_index_offset, seq_get
 39from sqlglot.optimizer.scope import find_all_in_scope
 40from sqlglot.tokens import TokenType
 41from sqlglot.transforms import unqualify_columns
 42from sqlglot.generator import unsupported_args
 43from sqlglot.typing.presto import EXPRESSION_METADATA
 44
 45DATE_ADD_OR_SUB = t.Union[exp.DateAdd, exp.TimestampAdd, exp.DateSub]
 46
 47
 48def _initcap_sql(self: Presto.Generator, expression: exp.Initcap) -> str:
 49    delimiters = expression.expression
 50    if delimiters and not (
 51        delimiters.is_string and delimiters.this == self.dialect.INITCAP_DEFAULT_DELIMITER_CHARS
 52    ):
 53        self.unsupported("INITCAP does not support custom delimiters")
 54
 55    regex = r"(\w)(\w*)"
 56    return f"REGEXP_REPLACE({self.sql(expression, 'this')}, '{regex}', x -> UPPER(x[1]) || LOWER(x[2]))"
 57
 58
 59def _no_sort_array(self: Presto.Generator, expression: exp.SortArray) -> str:
 60    if expression.args.get("asc") == exp.false():
 61        comparator = "(a, b) -> CASE WHEN a < b THEN 1 WHEN a > b THEN -1 ELSE 0 END"
 62    else:
 63        comparator = None
 64    return self.func("ARRAY_SORT", expression.this, comparator)
 65
 66
 67def _schema_sql(self: Presto.Generator, expression: exp.Schema) -> str:
 68    if isinstance(expression.parent, exp.PartitionedByProperty):
 69        # Any columns in the ARRAY[] string literals should not be quoted
 70        expression.transform(lambda n: n.name if isinstance(n, exp.Identifier) else n, copy=False)
 71
 72        partition_exprs = [
 73            self.sql(c) if isinstance(c, (exp.Func, exp.Property)) else self.sql(c, "this")
 74            for c in expression.expressions
 75        ]
 76        return self.sql(exp.Array(expressions=[exp.Literal.string(c) for c in partition_exprs]))
 77
 78    if expression.parent:
 79        for schema in expression.parent.find_all(exp.Schema):
 80            if schema is expression:
 81                continue
 82
 83            column_defs = schema.find_all(exp.ColumnDef)
 84            if column_defs and isinstance(schema.parent, exp.Property):
 85                expression.expressions.extend(column_defs)
 86
 87    return self.schema_sql(expression)
 88
 89
 90def _quantile_sql(self: Presto.Generator, expression: exp.Quantile) -> str:
 91    self.unsupported("Presto does not support exact quantiles")
 92    return self.func("APPROX_PERCENTILE", expression.this, expression.args.get("quantile"))
 93
 94
 95def _str_to_time_sql(
 96    self: Presto.Generator, expression: exp.StrToDate | exp.StrToTime | exp.TsOrDsToDate
 97) -> str:
 98    return self.func("DATE_PARSE", expression.this, self.format_time(expression))
 99
100
101def _ts_or_ds_to_date_sql(self: Presto.Generator, expression: exp.TsOrDsToDate) -> str:
102    time_format = self.format_time(expression)
103    if time_format and time_format not in (Presto.TIME_FORMAT, Presto.DATE_FORMAT):
104        return self.sql(exp.cast(_str_to_time_sql(self, expression), exp.DataType.Type.DATE))
105    return self.sql(
106        exp.cast(exp.cast(expression.this, exp.DataType.Type.TIMESTAMP), exp.DataType.Type.DATE)
107    )
108
109
110def _ts_or_ds_add_sql(self: Presto.Generator, expression: exp.TsOrDsAdd) -> str:
111    expression = ts_or_ds_add_cast(expression)
112    unit = unit_to_str(expression)
113    return self.func("DATE_ADD", unit, expression.expression, expression.this)
114
115
116def _ts_or_ds_diff_sql(self: Presto.Generator, expression: exp.TsOrDsDiff) -> str:
117    this = exp.cast(expression.this, exp.DataType.Type.TIMESTAMP)
118    expr = exp.cast(expression.expression, exp.DataType.Type.TIMESTAMP)
119    unit = unit_to_str(expression)
120    return self.func("DATE_DIFF", unit, expr, this)
121
122
123def _build_approx_percentile(args: t.List) -> exp.Expression:
124    if len(args) == 4:
125        return exp.ApproxQuantile(
126            this=seq_get(args, 0),
127            weight=seq_get(args, 1),
128            quantile=seq_get(args, 2),
129            accuracy=seq_get(args, 3),
130        )
131    if len(args) == 3:
132        return exp.ApproxQuantile(
133            this=seq_get(args, 0), quantile=seq_get(args, 1), accuracy=seq_get(args, 2)
134        )
135    return exp.ApproxQuantile.from_arg_list(args)
136
137
138def _build_from_unixtime(args: t.List) -> exp.Expression:
139    if len(args) == 3:
140        return exp.UnixToTime(
141            this=seq_get(args, 0),
142            hours=seq_get(args, 1),
143            minutes=seq_get(args, 2),
144        )
145    if len(args) == 2:
146        return exp.UnixToTime(this=seq_get(args, 0), zone=seq_get(args, 1))
147
148    return exp.UnixToTime.from_arg_list(args)
149
150
151def _first_last_sql(self: Presto.Generator, expression: exp.Func) -> str:
152    """
153    Trino doesn't support FIRST / LAST as functions, but they're valid in the context
154    of MATCH_RECOGNIZE, so we need to preserve them in that case. In all other cases
155    they're converted into an ARBITRARY call.
156
157    Reference: https://trino.io/docs/current/sql/match-recognize.html#logical-navigation-functions
158    """
159    if isinstance(expression.find_ancestor(exp.MatchRecognize, exp.Select), exp.MatchRecognize):
160        return self.function_fallback_sql(expression)
161
162    return rename_func("ARBITRARY")(self, expression)
163
164
165def _unix_to_time_sql(self: Presto.Generator, expression: exp.UnixToTime) -> str:
166    scale = expression.args.get("scale")
167    timestamp = self.sql(expression, "this")
168    if scale in (None, exp.UnixToTime.SECONDS):
169        return rename_func("FROM_UNIXTIME")(self, expression)
170
171    return f"FROM_UNIXTIME(CAST({timestamp} AS DOUBLE) / POW(10, {scale}))"
172
173
174def _to_int(self: Presto.Generator, expression: exp.Expression) -> exp.Expression:
175    if not expression.type:
176        from sqlglot.optimizer.annotate_types import annotate_types
177
178        annotate_types(expression, dialect=self.dialect)
179    if expression.type and expression.type.this not in exp.DataType.INTEGER_TYPES:
180        return exp.cast(expression, to=exp.DataType.Type.BIGINT)
181    return expression
182
183
184def _build_to_char(args: t.List) -> exp.TimeToStr:
185    fmt = seq_get(args, 1)
186    if isinstance(fmt, exp.Literal):
187        # We uppercase this to match Teradata's format mapping keys
188        fmt.set("this", fmt.this.upper())
189
190    # We use "teradata" on purpose here, because the time formats are different in Presto.
191    # See https://prestodb.io/docs/current/functions/teradata.html?highlight=to_char#to_char
192    return build_formatted_time(exp.TimeToStr, "teradata")(args)
193
194
195def _date_delta_sql(
196    name: str, negate_interval: bool = False
197) -> t.Callable[[Presto.Generator, DATE_ADD_OR_SUB], str]:
198    def _delta_sql(self: Presto.Generator, expression: DATE_ADD_OR_SUB) -> str:
199        interval = _to_int(self, expression.expression)
200        return self.func(
201            name,
202            unit_to_str(expression),
203            interval * (-1) if negate_interval else interval,
204            expression.this,
205        )
206
207    return _delta_sql
208
209
210def _explode_to_unnest_sql(self: Presto.Generator, expression: exp.Lateral) -> str:
211    explode = expression.this
212    if isinstance(explode, exp.Explode):
213        exploded_type = explode.this.type
214        alias = expression.args.get("alias")
215
216        # This attempts a best-effort transpilation of LATERAL VIEW EXPLODE on a struct array
217        if (
218            isinstance(alias, exp.TableAlias)
219            and isinstance(exploded_type, exp.DataType)
220            and exploded_type.is_type(exp.DataType.Type.ARRAY)
221            and exploded_type.expressions
222            and exploded_type.expressions[0].is_type(exp.DataType.Type.STRUCT)
223        ):
224            # When unnesting a ROW in Presto, it produces N columns, so we need to fix the alias
225            alias.set("columns", [c.this.copy() for c in exploded_type.expressions[0].expressions])
226    elif isinstance(explode, exp.Inline):
227        explode.replace(exp.Explode(this=explode.this.copy()))
228
229    return explode_to_unnest_sql(self, expression)
230
231
232def amend_exploded_column_table(expression: exp.Expression) -> exp.Expression:
233    # We check for expression.type because the columns can be amended only if types were inferred
234    if isinstance(expression, exp.Select) and expression.type:
235        for lateral in expression.args.get("laterals") or []:
236            alias = lateral.args.get("alias")
237            if (
238                not isinstance(lateral.this, exp.Explode)
239                or not isinstance(alias, exp.TableAlias)
240                or len(alias.columns) != 1
241            ):
242                continue
243
244            new_table = alias.this
245            old_table = alias.columns[0].name.lower()
246
247            # When transpiling a LATERAL VIEW EXPLODE Spark query, the exploded fields may be qualified
248            # with the struct column, resulting in invalid Presto references that need to be amended
249            for column in find_all_in_scope(expression, exp.Column):
250                if column.db.lower() == old_table:
251                    column.set("table", column.args["db"].pop())
252                elif column.table.lower() == old_table:
253                    column.set("table", new_table.copy())
254                elif column.name.lower() == old_table and isinstance(column.parent, exp.Dot):
255                    column.parent.replace(exp.column(column.parent.expression, table=new_table))
256
257    return expression
258
259
260class Presto(Dialect):
261    INDEX_OFFSET = 1
262    NULL_ORDERING = "nulls_are_last"
263    TIME_FORMAT = MySQL.TIME_FORMAT
264    STRICT_STRING_CONCAT = True
265    SUPPORTS_SEMI_ANTI_JOIN = False
266    TYPED_DIVISION = True
267    TABLESAMPLE_SIZE_IS_PERCENT = True
268    LOG_BASE_FIRST: t.Optional[bool] = None
269    SUPPORTS_VALUES_DEFAULT = False
270    LEAST_GREATEST_IGNORES_NULLS = False
271
272    TIME_MAPPING = MySQL.TIME_MAPPING
273
274    # https://github.com/trinodb/trino/issues/17
275    # https://github.com/trinodb/trino/issues/12289
276    # https://github.com/prestodb/presto/issues/2863
277    NORMALIZATION_STRATEGY = NormalizationStrategy.CASE_INSENSITIVE
278
279    EXPRESSION_METADATA = EXPRESSION_METADATA.copy()
280
281    SUPPORTED_SETTINGS = {
282        *Dialect.SUPPORTED_SETTINGS,
283        "variant_extract_is_json_extract",
284    }
285
286    class Tokenizer(tokens.Tokenizer):
287        HEX_STRINGS = [("x'", "'"), ("X'", "'")]
288        UNICODE_STRINGS = [
289            (prefix + q, q)
290            for q in t.cast(t.List[str], tokens.Tokenizer.QUOTES)
291            for prefix in ("U&", "u&")
292        ]
293
294        NESTED_COMMENTS = False
295
296        KEYWORDS = {
297            **tokens.Tokenizer.KEYWORDS,
298            "DEALLOCATE PREPARE": TokenType.COMMAND,
299            "DESCRIBE INPUT": TokenType.COMMAND,
300            "DESCRIBE OUTPUT": TokenType.COMMAND,
301            "RESET SESSION": TokenType.COMMAND,
302            "START": TokenType.BEGIN,
303            "MATCH_RECOGNIZE": TokenType.MATCH_RECOGNIZE,
304            "ROW": TokenType.STRUCT,
305            "IPADDRESS": TokenType.IPADDRESS,
306            "IPPREFIX": TokenType.IPPREFIX,
307            "TDIGEST": TokenType.TDIGEST,
308            "HYPERLOGLOG": TokenType.HLLSKETCH,
309        }
310        KEYWORDS.pop("/*+")
311        KEYWORDS.pop("QUALIFY")
312
313    class Parser(parser.Parser):
314        VALUES_FOLLOWED_BY_PAREN = False
315        ZONE_AWARE_TIMESTAMP_CONSTRUCTOR = True
316
317        FUNCTIONS = {
318            **parser.Parser.FUNCTIONS,
319            "ARBITRARY": exp.AnyValue.from_arg_list,
320            "APPROX_DISTINCT": exp.ApproxDistinct.from_arg_list,
321            "APPROX_PERCENTILE": _build_approx_percentile,
322            "BITWISE_AND": binary_from_function(exp.BitwiseAnd),
323            "BITWISE_NOT": lambda args: exp.BitwiseNot(this=seq_get(args, 0)),
324            "BITWISE_OR": binary_from_function(exp.BitwiseOr),
325            "BITWISE_XOR": binary_from_function(exp.BitwiseXor),
326            "CARDINALITY": exp.ArraySize.from_arg_list,
327            "CONTAINS": exp.ArrayContains.from_arg_list,
328            "DATE_ADD": lambda args: exp.DateAdd(
329                this=seq_get(args, 2), expression=seq_get(args, 1), unit=seq_get(args, 0)
330            ),
331            "DATE_DIFF": lambda args: exp.DateDiff(
332                this=seq_get(args, 2), expression=seq_get(args, 1), unit=seq_get(args, 0)
333            ),
334            "DATE_FORMAT": build_formatted_time(exp.TimeToStr, "presto"),
335            "DATE_PARSE": build_formatted_time(exp.StrToTime, "presto"),
336            "DATE_TRUNC": date_trunc_to_time,
337            "DAY_OF_WEEK": exp.DayOfWeekIso.from_arg_list,
338            "DOW": exp.DayOfWeekIso.from_arg_list,
339            "DOY": exp.DayOfYear.from_arg_list,
340            "ELEMENT_AT": lambda args: exp.Bracket(
341                this=seq_get(args, 0), expressions=[seq_get(args, 1)], offset=1, safe=True
342            ),
343            "FROM_HEX": exp.Unhex.from_arg_list,
344            "FROM_UNIXTIME": _build_from_unixtime,
345            "FROM_UTF8": lambda args: exp.Decode(
346                this=seq_get(args, 0), replace=seq_get(args, 1), charset=exp.Literal.string("utf-8")
347            ),
348            "JSON_FORMAT": lambda args: exp.JSONFormat(
349                this=seq_get(args, 0), options=seq_get(args, 1), is_json=True
350            ),
351            "LEVENSHTEIN_DISTANCE": exp.Levenshtein.from_arg_list,
352            "NOW": exp.CurrentTimestamp.from_arg_list,
353            "REGEXP_EXTRACT": build_regexp_extract(exp.RegexpExtract),
354            "REGEXP_EXTRACT_ALL": build_regexp_extract(exp.RegexpExtractAll),
355            "REGEXP_REPLACE": lambda args: exp.RegexpReplace(
356                this=seq_get(args, 0),
357                expression=seq_get(args, 1),
358                replacement=seq_get(args, 2) or exp.Literal.string(""),
359            ),
360            "REPLACE": build_replace_with_optional_replacement,
361            "ROW": exp.Struct.from_arg_list,
362            "SEQUENCE": exp.GenerateSeries.from_arg_list,
363            "SET_AGG": exp.ArrayUniqueAgg.from_arg_list,
364            "SPLIT_TO_MAP": exp.StrToMap.from_arg_list,
365            "STRPOS": lambda args: exp.StrPosition(
366                this=seq_get(args, 0), substr=seq_get(args, 1), occurrence=seq_get(args, 2)
367            ),
368            "SLICE": exp.ArraySlice.from_arg_list,
369            "TO_CHAR": _build_to_char,
370            "TO_UNIXTIME": exp.TimeToUnix.from_arg_list,
371            "TO_UTF8": lambda args: exp.Encode(
372                this=seq_get(args, 0), charset=exp.Literal.string("utf-8")
373            ),
374            "MD5": exp.MD5Digest.from_arg_list,
375            "SHA256": lambda args: exp.SHA2(this=seq_get(args, 0), length=exp.Literal.number(256)),
376            "SHA512": lambda args: exp.SHA2(this=seq_get(args, 0), length=exp.Literal.number(512)),
377            "WEEK": exp.WeekOfYear.from_arg_list,
378        }
379
380        FUNCTION_PARSERS = parser.Parser.FUNCTION_PARSERS.copy()
381        FUNCTION_PARSERS.pop("TRIM")
382
383    class Generator(generator.Generator):
384        INTERVAL_ALLOWS_PLURAL_FORM = False
385        JOIN_HINTS = False
386        TABLE_HINTS = False
387        QUERY_HINTS = False
388        IS_BOOL_ALLOWED = False
389        TZ_TO_WITH_TIME_ZONE = True
390        NVL2_SUPPORTED = False
391        STRUCT_DELIMITER = ("(", ")")
392        LIMIT_ONLY_LITERALS = True
393        SUPPORTS_SINGLE_ARG_CONCAT = False
394        LIKE_PROPERTY_INSIDE_SCHEMA = True
395        MULTI_ARG_DISTINCT = False
396        SUPPORTS_TO_NUMBER = False
397        HEX_FUNC = "TO_HEX"
398        PARSE_JSON_NAME = "JSON_PARSE"
399        PAD_FILL_PATTERN_IS_REQUIRED = True
400        EXCEPT_INTERSECT_SUPPORT_ALL_CLAUSE = False
401        SUPPORTS_MEDIAN = False
402        ARRAY_SIZE_NAME = "CARDINALITY"
403
404        PROPERTIES_LOCATION = {
405            **generator.Generator.PROPERTIES_LOCATION,
406            exp.LocationProperty: exp.Properties.Location.UNSUPPORTED,
407            exp.VolatileProperty: exp.Properties.Location.UNSUPPORTED,
408        }
409
410        TYPE_MAPPING = {
411            **generator.Generator.TYPE_MAPPING,
412            exp.DataType.Type.BINARY: "VARBINARY",
413            exp.DataType.Type.BIT: "BOOLEAN",
414            exp.DataType.Type.DATETIME: "TIMESTAMP",
415            exp.DataType.Type.DATETIME64: "TIMESTAMP",
416            exp.DataType.Type.FLOAT: "REAL",
417            exp.DataType.Type.HLLSKETCH: "HYPERLOGLOG",
418            exp.DataType.Type.INT: "INTEGER",
419            exp.DataType.Type.STRUCT: "ROW",
420            exp.DataType.Type.TEXT: "VARCHAR",
421            exp.DataType.Type.TIMESTAMPTZ: "TIMESTAMP",
422            exp.DataType.Type.TIMESTAMPNTZ: "TIMESTAMP",
423            exp.DataType.Type.TIMETZ: "TIME",
424        }
425
426        TRANSFORMS = {
427            **generator.Generator.TRANSFORMS,
428            exp.AnyValue: rename_func("ARBITRARY"),
429            exp.ApproxQuantile: lambda self, e: self.func(
430                "APPROX_PERCENTILE",
431                e.this,
432                e.args.get("weight"),
433                e.args.get("quantile"),
434                e.args.get("accuracy"),
435            ),
436            exp.ArgMax: rename_func("MAX_BY"),
437            exp.ArgMin: rename_func("MIN_BY"),
438            exp.Array: transforms.preprocess(
439                [transforms.inherit_struct_field_names],
440                generator=lambda self, e: f"ARRAY[{self.expressions(e, flat=True)}]",
441            ),
442            exp.ArrayAny: rename_func("ANY_MATCH"),
443            exp.ArrayConcat: rename_func("CONCAT"),
444            exp.ArrayContains: rename_func("CONTAINS"),
445            exp.ArrayToString: rename_func("ARRAY_JOIN"),
446            exp.ArrayUniqueAgg: rename_func("SET_AGG"),
447            exp.ArraySlice: rename_func("SLICE"),
448            exp.AtTimeZone: rename_func("AT_TIMEZONE"),
449            exp.BitwiseAnd: lambda self, e: self.func("BITWISE_AND", e.this, e.expression),
450            exp.BitwiseLeftShift: lambda self, e: self.func(
451                "BITWISE_ARITHMETIC_SHIFT_LEFT", e.this, e.expression
452            ),
453            exp.BitwiseNot: lambda self, e: self.func("BITWISE_NOT", e.this),
454            exp.BitwiseOr: lambda self, e: self.func("BITWISE_OR", e.this, e.expression),
455            exp.BitwiseRightShift: lambda self, e: self.func(
456                "BITWISE_ARITHMETIC_SHIFT_RIGHT", e.this, e.expression
457            ),
458            exp.BitwiseXor: lambda self, e: self.func("BITWISE_XOR", e.this, e.expression),
459            exp.Cast: transforms.preprocess([transforms.epoch_cast_to_ts]),
460            exp.CurrentTime: lambda *_: "CURRENT_TIME",
461            exp.CurrentTimestamp: lambda *_: "CURRENT_TIMESTAMP",
462            exp.CurrentUser: lambda *_: "CURRENT_USER",
463            exp.DateAdd: _date_delta_sql("DATE_ADD"),
464            exp.DateDiff: lambda self, e: self.func(
465                "DATE_DIFF", unit_to_str(e), e.expression, e.this
466            ),
467            exp.DateStrToDate: datestrtodate_sql,
468            exp.DateToDi: lambda self,
469            e: f"CAST(DATE_FORMAT({self.sql(e, 'this')}, {Presto.DATEINT_FORMAT}) AS INT)",
470            exp.DateSub: _date_delta_sql("DATE_ADD", negate_interval=True),
471            exp.DayOfWeek: lambda self, e: f"(({self.func('DAY_OF_WEEK', e.this)} % 7) + 1)",
472            exp.DayOfWeekIso: rename_func("DAY_OF_WEEK"),
473            exp.Decode: lambda self, e: encode_decode_sql(self, e, "FROM_UTF8"),
474            exp.DiToDate: lambda self,
475            e: f"CAST(DATE_PARSE(CAST({self.sql(e, 'this')} AS VARCHAR), {Presto.DATEINT_FORMAT}) AS DATE)",
476            exp.Encode: lambda self, e: encode_decode_sql(self, e, "TO_UTF8"),
477            exp.FileFormatProperty: lambda self,
478            e: f"format={self.sql(exp.Literal.string(e.name))}",
479            exp.First: _first_last_sql,
480            exp.FromTimeZone: lambda self,
481            e: f"WITH_TIMEZONE({self.sql(e, 'this')}, {self.sql(e, 'zone')}) AT TIME ZONE 'UTC'",
482            exp.GenerateSeries: sequence_sql,
483            exp.GenerateDateArray: sequence_sql,
484            exp.If: if_sql(),
485            exp.ILike: no_ilike_sql,
486            exp.Initcap: _initcap_sql,
487            exp.Last: _first_last_sql,
488            exp.LastDay: lambda self, e: self.func("LAST_DAY_OF_MONTH", e.this),
489            exp.Lateral: _explode_to_unnest_sql,
490            exp.Left: left_to_substring_sql,
491            exp.Levenshtein: unsupported_args("ins_cost", "del_cost", "sub_cost", "max_dist")(
492                rename_func("LEVENSHTEIN_DISTANCE")
493            ),
494            exp.LogicalAnd: rename_func("BOOL_AND"),
495            exp.LogicalOr: rename_func("BOOL_OR"),
496            exp.Pivot: no_pivot_sql,
497            exp.Quantile: _quantile_sql,
498            exp.RegexpExtract: regexp_extract_sql,
499            exp.RegexpExtractAll: regexp_extract_sql,
500            exp.Right: right_to_substring_sql,
501            exp.Schema: _schema_sql,
502            exp.SchemaCommentProperty: lambda self, e: self.naked_property(e),
503            exp.Select: transforms.preprocess(
504                [
505                    transforms.eliminate_window_clause,
506                    transforms.eliminate_qualify,
507                    transforms.eliminate_distinct_on,
508                    transforms.explode_projection_to_unnest(1),
509                    transforms.eliminate_semi_and_anti_joins,
510                    amend_exploded_column_table,
511                ]
512            ),
513            exp.SortArray: _no_sort_array,
514            exp.StrPosition: lambda self, e: strposition_sql(self, e, supports_occurrence=True),
515            exp.StrToDate: lambda self, e: f"CAST({_str_to_time_sql(self, e)} AS DATE)",
516            exp.StrToMap: rename_func("SPLIT_TO_MAP"),
517            exp.StrToTime: _str_to_time_sql,
518            exp.StructExtract: struct_extract_sql,
519            exp.Table: transforms.preprocess([transforms.unnest_generate_series]),
520            exp.Timestamp: no_timestamp_sql,
521            exp.TimestampAdd: _date_delta_sql("DATE_ADD"),
522            exp.TimestampTrunc: timestamptrunc_sql(),
523            exp.TimeStrToDate: timestrtotime_sql,
524            exp.TimeStrToTime: timestrtotime_sql,
525            exp.TimeStrToUnix: lambda self, e: self.func(
526                "TO_UNIXTIME", self.func("DATE_PARSE", e.this, Presto.TIME_FORMAT)
527            ),
528            exp.TimeToStr: lambda self, e: self.func("DATE_FORMAT", e.this, self.format_time(e)),
529            exp.TimeToUnix: rename_func("TO_UNIXTIME"),
530            exp.ToChar: lambda self, e: self.func("DATE_FORMAT", e.this, self.format_time(e)),
531            exp.TryCast: transforms.preprocess([transforms.epoch_cast_to_ts]),
532            exp.TsOrDiToDi: lambda self,
533            e: f"CAST(SUBSTR(REPLACE(CAST({self.sql(e, 'this')} AS VARCHAR), '-', ''), 1, 8) AS INT)",
534            exp.TsOrDsAdd: _ts_or_ds_add_sql,
535            exp.TsOrDsDiff: _ts_or_ds_diff_sql,
536            exp.TsOrDsToDate: _ts_or_ds_to_date_sql,
537            exp.Unhex: rename_func("FROM_HEX"),
538            exp.UnixToStr: lambda self,
539            e: f"DATE_FORMAT(FROM_UNIXTIME({self.sql(e, 'this')}), {self.format_time(e)})",
540            exp.UnixToTime: _unix_to_time_sql,
541            exp.UnixToTimeStr: lambda self,
542            e: f"CAST(FROM_UNIXTIME({self.sql(e, 'this')}) AS VARCHAR)",
543            exp.VariancePop: rename_func("VAR_POP"),
544            exp.With: transforms.preprocess([transforms.add_recursive_cte_column_names]),
545            exp.WithinGroup: transforms.preprocess(
546                [transforms.remove_within_group_for_percentiles]
547            ),
548            exp.Xor: bool_xor_sql,
549            exp.MD5Digest: rename_func("MD5"),
550            exp.SHA: rename_func("SHA1"),
551            exp.SHA1Digest: rename_func("SHA1"),
552            exp.SHA2: sha256_sql,
553            exp.SHA2Digest: sha2_digest_sql,
554        }
555
556        RESERVED_KEYWORDS = {
557            "alter",
558            "and",
559            "as",
560            "between",
561            "by",
562            "case",
563            "cast",
564            "constraint",
565            "create",
566            "cross",
567            "current_time",
568            "current_timestamp",
569            "deallocate",
570            "delete",
571            "describe",
572            "distinct",
573            "drop",
574            "else",
575            "end",
576            "escape",
577            "except",
578            "execute",
579            "exists",
580            "extract",
581            "false",
582            "for",
583            "from",
584            "full",
585            "group",
586            "having",
587            "in",
588            "inner",
589            "insert",
590            "intersect",
591            "into",
592            "is",
593            "join",
594            "left",
595            "like",
596            "natural",
597            "not",
598            "null",
599            "on",
600            "or",
601            "order",
602            "outer",
603            "prepare",
604            "right",
605            "select",
606            "table",
607            "then",
608            "true",
609            "union",
610            "using",
611            "values",
612            "when",
613            "where",
614            "with",
615        }
616
617        def extract_sql(self, expression: exp.Extract) -> str:
618            date_part = expression.name
619
620            if not date_part.startswith("EPOCH"):
621                return super().extract_sql(expression)
622
623            if date_part == "EPOCH_MILLISECOND":
624                scale = 10**3
625            elif date_part == "EPOCH_MICROSECOND":
626                scale = 10**6
627            elif date_part == "EPOCH_NANOSECOND":
628                scale = 10**9
629            else:
630                scale = None
631
632            value = expression.expression
633
634            ts = exp.cast(value, to=exp.DataType.build("TIMESTAMP"))
635            to_unix: exp.Expression = exp.TimeToUnix(this=ts)
636
637            if scale:
638                to_unix = exp.Mul(this=to_unix, expression=exp.Literal.number(scale))
639
640            return self.sql(to_unix)
641
642        def jsonformat_sql(self, expression: exp.JSONFormat) -> str:
643            this = expression.this
644            is_json = expression.args.get("is_json")
645
646            if this and not (is_json or this.type):
647                from sqlglot.optimizer.annotate_types import annotate_types
648
649                this = annotate_types(this, dialect=self.dialect)
650
651            if not (is_json or this.is_type(exp.DataType.Type.JSON)):
652                this.replace(exp.cast(this, exp.DataType.Type.JSON))
653
654            return self.function_fallback_sql(expression)
655
656        def md5_sql(self, expression: exp.MD5) -> str:
657            this = expression.this
658
659            if not this.type:
660                from sqlglot.optimizer.annotate_types import annotate_types
661
662                this = annotate_types(this, dialect=self.dialect)
663
664            if this.is_type(*exp.DataType.TEXT_TYPES):
665                this = exp.Encode(this=this, charset=exp.Literal.string("utf-8"))
666
667            return self.func("LOWER", self.func("TO_HEX", self.func("MD5", self.sql(this))))
668
669        def strtounix_sql(self, expression: exp.StrToUnix) -> str:
670            # Since `TO_UNIXTIME` requires a `TIMESTAMP`, we need to parse the argument into one.
671            # To do this, we first try to `DATE_PARSE` it, but since this can fail when there's a
672            # timezone involved, we wrap it in a `TRY` call and use `PARSE_DATETIME` as a fallback,
673            # which seems to be using the same time mapping as Hive, as per:
674            # https://joda-time.sourceforge.net/apidocs/org/joda/time/format/DateTimeFormat.html
675            this = expression.this
676            value_as_text = exp.cast(this, exp.DataType.Type.TEXT)
677            value_as_timestamp = (
678                exp.cast(this, exp.DataType.Type.TIMESTAMP) if this.is_string else this
679            )
680
681            parse_without_tz = self.func("DATE_PARSE", value_as_text, self.format_time(expression))
682
683            formatted_value = self.func(
684                "DATE_FORMAT", value_as_timestamp, self.format_time(expression)
685            )
686            parse_with_tz = self.func(
687                "PARSE_DATETIME",
688                formatted_value,
689                self.format_time(expression, Hive.INVERSE_TIME_MAPPING, Hive.INVERSE_TIME_TRIE),
690            )
691            coalesced = self.func("COALESCE", self.func("TRY", parse_without_tz), parse_with_tz)
692            return self.func("TO_UNIXTIME", coalesced)
693
694        def bracket_sql(self, expression: exp.Bracket) -> str:
695            if expression.args.get("safe"):
696                return self.func(
697                    "ELEMENT_AT",
698                    expression.this,
699                    seq_get(
700                        apply_index_offset(
701                            expression.this,
702                            expression.expressions,
703                            1 - expression.args.get("offset", 0),
704                            dialect=self.dialect,
705                        ),
706                        0,
707                    ),
708                )
709            return super().bracket_sql(expression)
710
711        def struct_sql(self, expression: exp.Struct) -> str:
712            if not expression.type:
713                from sqlglot.optimizer.annotate_types import annotate_types
714
715                annotate_types(expression, dialect=self.dialect)
716
717            values: t.List[str] = []
718            schema: t.List[str] = []
719            unknown_type = False
720
721            for e in expression.expressions:
722                if isinstance(e, exp.PropertyEQ):
723                    if e.type and e.type.is_type(exp.DataType.Type.UNKNOWN):
724                        unknown_type = True
725                    else:
726                        schema.append(f"{self.sql(e, 'this')} {self.sql(e.type)}")
727                    values.append(self.sql(e, "expression"))
728                else:
729                    values.append(self.sql(e))
730
731            size = len(expression.expressions)
732
733            if not size or len(schema) != size:
734                if unknown_type:
735                    self.unsupported(
736                        "Cannot convert untyped key-value definitions (try annotate_types)."
737                    )
738                return self.func("ROW", *values)
739            return f"CAST(ROW({', '.join(values)}) AS ROW({', '.join(schema)}))"
740
741        def interval_sql(self, expression: exp.Interval) -> str:
742            if expression.this and expression.text("unit").upper().startswith("WEEK"):
743                return f"({expression.this.name} * INTERVAL '7' DAY)"
744            return super().interval_sql(expression)
745
746        def transaction_sql(self, expression: exp.Transaction) -> str:
747            modes = expression.args.get("modes")
748            modes = f" {', '.join(modes)}" if modes else ""
749            return f"START TRANSACTION{modes}"
750
751        def offset_limit_modifiers(
752            self, expression: exp.Expression, fetch: bool, limit: t.Optional[exp.Fetch | exp.Limit]
753        ) -> t.List[str]:
754            return [
755                self.sql(expression, "offset"),
756                self.sql(limit),
757            ]
758
759        def create_sql(self, expression: exp.Create) -> str:
760            """
761            Presto doesn't support CREATE VIEW with expressions (ex: `CREATE VIEW x (cola)` then `(cola)` is the expression),
762            so we need to remove them
763            """
764            kind = expression.args["kind"]
765            schema = expression.this
766            if kind == "VIEW" and schema.expressions:
767                expression.this.set("expressions", None)
768            return super().create_sql(expression)
769
770        def delete_sql(self, expression: exp.Delete) -> str:
771            """
772            Presto only supports DELETE FROM for a single table without an alias, so we need
773            to remove the unnecessary parts. If the original DELETE statement contains more
774            than one table to be deleted, we can't safely map it 1-1 to a Presto statement.
775            """
776            tables = expression.args.get("tables") or [expression.this]
777            if len(tables) > 1:
778                return super().delete_sql(expression)
779
780            table = tables[0]
781            expression.set("this", table)
782            expression.set("tables", None)
783
784            if isinstance(table, exp.Table):
785                table_alias = table.args.get("alias")
786                if table_alias:
787                    table_alias.pop()
788                    expression = t.cast(exp.Delete, expression.transform(unqualify_columns))
789
790            return super().delete_sql(expression)
791
792        def jsonextract_sql(self, expression: exp.JSONExtract) -> str:
793            is_json_extract = self.dialect.settings.get("variant_extract_is_json_extract", True)
794
795            # Generate JSON_EXTRACT unless the user has configured that a Snowflake / Databricks
796            # VARIANT extract (e.g. col:x.y) should map to dot notation (i.e ROW access) in Presto/Trino
797            if not expression.args.get("variant_extract") or is_json_extract:
798                return self.func(
799                    "JSON_EXTRACT", expression.this, expression.expression, *expression.expressions
800                )
801
802            this = self.sql(expression, "this")
803
804            # Convert the JSONPath extraction `JSON_EXTRACT(col, '$.x.y) to a ROW access col.x.y
805            segments = []
806            for path_key in expression.expression.expressions[1:]:
807                if not isinstance(path_key, exp.JSONPathKey):
808                    # Cannot transpile subscripts, wildcards etc to dot notation
809                    self.unsupported(
810                        f"Cannot transpile JSONPath segment '{path_key}' to ROW access"
811                    )
812                    continue
813                key = path_key.this
814                if not exp.SAFE_IDENTIFIER_RE.match(key):
815                    key = f'"{key}"'
816                segments.append(f".{key}")
817
818            expr = "".join(segments)
819
820            return f"{this}{expr}"
821
822        def groupconcat_sql(self, expression: exp.GroupConcat) -> str:
823            return self.func(
824                "ARRAY_JOIN",
825                self.func("ARRAY_AGG", expression.this),
826                expression.args.get("separator"),
827            )