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