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