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