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