sqlglot.dialects.presto

  1from __future__ import annotations
  2
  3import typing as t
  4
  5from sqlglot import exp, generator, parser, tokens, transforms
  6from sqlglot.dialects.dialect import (
  7    Dialect,
  8    NormalizationStrategy,
  9    binary_from_function,
 10    bool_xor_sql,
 11    date_trunc_to_time,
 12    datestrtodate_sql,
 13    encode_decode_sql,
 14    build_formatted_time,
 15    if_sql,
 16    left_to_substring_sql,
 17    no_ilike_sql,
 18    no_pivot_sql,
 19    no_timestamp_sql,
 20    regexp_extract_sql,
 21    rename_func,
 22    right_to_substring_sql,
 23    sha256_sql,
 24    strposition_sql,
 25    struct_extract_sql,
 26    timestamptrunc_sql,
 27    timestrtotime_sql,
 28    ts_or_ds_add_cast,
 29    unit_to_str,
 30    sequence_sql,
 31    build_regexp_extract,
 32    explode_to_unnest_sql,
 33)
 34from sqlglot.dialects.hive import Hive
 35from sqlglot.dialects.mysql import MySQL
 36from sqlglot.helper import apply_index_offset, seq_get
 37from sqlglot.tokens import TokenType
 38from sqlglot.transforms import unqualify_columns
 39from sqlglot.generator import unsupported_args
 40
 41DATE_ADD_OR_SUB = t.Union[exp.DateAdd, exp.TimestampAdd, exp.DateSub]
 42
 43
 44def _initcap_sql(self: Presto.Generator, expression: exp.Initcap) -> str:
 45    regex = r"(\w)(\w*)"
 46    return f"REGEXP_REPLACE({self.sql(expression, 'this')}, '{regex}', x -> UPPER(x[1]) || LOWER(x[2]))"
 47
 48
 49def _no_sort_array(self: Presto.Generator, expression: exp.SortArray) -> str:
 50    if expression.args.get("asc") == exp.false():
 51        comparator = "(a, b) -> CASE WHEN a < b THEN 1 WHEN a > b THEN -1 ELSE 0 END"
 52    else:
 53        comparator = None
 54    return self.func("ARRAY_SORT", expression.this, comparator)
 55
 56
 57def _schema_sql(self: Presto.Generator, expression: exp.Schema) -> str:
 58    if isinstance(expression.parent, exp.Property):
 59        columns = ", ".join(f"'{c.name}'" for c in expression.expressions)
 60        return f"ARRAY[{columns}]"
 61
 62    if expression.parent:
 63        for schema in expression.parent.find_all(exp.Schema):
 64            column_defs = schema.find_all(exp.ColumnDef)
 65            if column_defs and isinstance(schema.parent, exp.Property):
 66                expression.expressions.extend(column_defs)
 67
 68    return self.schema_sql(expression)
 69
 70
 71def _quantile_sql(self: Presto.Generator, expression: exp.Quantile) -> str:
 72    self.unsupported("Presto does not support exact quantiles")
 73    return self.func("APPROX_PERCENTILE", expression.this, expression.args.get("quantile"))
 74
 75
 76def _str_to_time_sql(
 77    self: Presto.Generator, expression: exp.StrToDate | exp.StrToTime | exp.TsOrDsToDate
 78) -> str:
 79    return self.func("DATE_PARSE", expression.this, self.format_time(expression))
 80
 81
 82def _ts_or_ds_to_date_sql(self: Presto.Generator, expression: exp.TsOrDsToDate) -> str:
 83    time_format = self.format_time(expression)
 84    if time_format and time_format not in (Presto.TIME_FORMAT, Presto.DATE_FORMAT):
 85        return self.sql(exp.cast(_str_to_time_sql(self, expression), exp.DataType.Type.DATE))
 86    return self.sql(
 87        exp.cast(exp.cast(expression.this, exp.DataType.Type.TIMESTAMP), exp.DataType.Type.DATE)
 88    )
 89
 90
 91def _ts_or_ds_add_sql(self: Presto.Generator, expression: exp.TsOrDsAdd) -> str:
 92    expression = ts_or_ds_add_cast(expression)
 93    unit = unit_to_str(expression)
 94    return self.func("DATE_ADD", unit, expression.expression, expression.this)
 95
 96
 97def _ts_or_ds_diff_sql(self: Presto.Generator, expression: exp.TsOrDsDiff) -> str:
 98    this = exp.cast(expression.this, exp.DataType.Type.TIMESTAMP)
 99    expr = exp.cast(expression.expression, exp.DataType.Type.TIMESTAMP)
100    unit = unit_to_str(expression)
101    return self.func("DATE_DIFF", unit, expr, this)
102
103
104def _build_approx_percentile(args: t.List) -> exp.Expression:
105    if len(args) == 4:
106        return exp.ApproxQuantile(
107            this=seq_get(args, 0),
108            weight=seq_get(args, 1),
109            quantile=seq_get(args, 2),
110            accuracy=seq_get(args, 3),
111        )
112    if len(args) == 3:
113        return exp.ApproxQuantile(
114            this=seq_get(args, 0), quantile=seq_get(args, 1), accuracy=seq_get(args, 2)
115        )
116    return exp.ApproxQuantile.from_arg_list(args)
117
118
119def _build_from_unixtime(args: t.List) -> exp.Expression:
120    if len(args) == 3:
121        return exp.UnixToTime(
122            this=seq_get(args, 0),
123            hours=seq_get(args, 1),
124            minutes=seq_get(args, 2),
125        )
126    if len(args) == 2:
127        return exp.UnixToTime(this=seq_get(args, 0), zone=seq_get(args, 1))
128
129    return exp.UnixToTime.from_arg_list(args)
130
131
132def _first_last_sql(self: Presto.Generator, expression: exp.Func) -> str:
133    """
134    Trino doesn't support FIRST / LAST as functions, but they're valid in the context
135    of MATCH_RECOGNIZE, so we need to preserve them in that case. In all other cases
136    they're converted into an ARBITRARY call.
137
138    Reference: https://trino.io/docs/current/sql/match-recognize.html#logical-navigation-functions
139    """
140    if isinstance(expression.find_ancestor(exp.MatchRecognize, exp.Select), exp.MatchRecognize):
141        return self.function_fallback_sql(expression)
142
143    return rename_func("ARBITRARY")(self, expression)
144
145
146def _unix_to_time_sql(self: Presto.Generator, expression: exp.UnixToTime) -> str:
147    scale = expression.args.get("scale")
148    timestamp = self.sql(expression, "this")
149    if scale in (None, exp.UnixToTime.SECONDS):
150        return rename_func("FROM_UNIXTIME")(self, expression)
151
152    return f"FROM_UNIXTIME(CAST({timestamp} AS DOUBLE) / POW(10, {scale}))"
153
154
155def _to_int(self: Presto.Generator, expression: exp.Expression) -> exp.Expression:
156    if not expression.type:
157        from sqlglot.optimizer.annotate_types import annotate_types
158
159        annotate_types(expression, dialect=self.dialect)
160    if expression.type and expression.type.this not in exp.DataType.INTEGER_TYPES:
161        return exp.cast(expression, to=exp.DataType.Type.BIGINT)
162    return expression
163
164
165def _build_to_char(args: t.List) -> exp.TimeToStr:
166    fmt = seq_get(args, 1)
167    if isinstance(fmt, exp.Literal):
168        # We uppercase this to match Teradata's format mapping keys
169        fmt.set("this", fmt.this.upper())
170
171    # We use "teradata" on purpose here, because the time formats are different in Presto.
172    # See https://prestodb.io/docs/current/functions/teradata.html?highlight=to_char#to_char
173    return build_formatted_time(exp.TimeToStr, "teradata")(args)
174
175
176def _date_delta_sql(
177    name: str, negate_interval: bool = False
178) -> t.Callable[[Presto.Generator, DATE_ADD_OR_SUB], str]:
179    def _delta_sql(self: Presto.Generator, expression: DATE_ADD_OR_SUB) -> str:
180        interval = _to_int(self, expression.expression)
181        return self.func(
182            name,
183            unit_to_str(expression),
184            interval * (-1) if negate_interval else interval,
185            expression.this,
186        )
187
188    return _delta_sql
189
190
191class Presto(Dialect):
192    INDEX_OFFSET = 1
193    NULL_ORDERING = "nulls_are_last"
194    TIME_FORMAT = MySQL.TIME_FORMAT
195    STRICT_STRING_CONCAT = True
196    SUPPORTS_SEMI_ANTI_JOIN = False
197    TYPED_DIVISION = True
198    TABLESAMPLE_SIZE_IS_PERCENT = True
199    LOG_BASE_FIRST: t.Optional[bool] = None
200    SUPPORTS_VALUES_DEFAULT = False
201
202    TIME_MAPPING = MySQL.TIME_MAPPING
203
204    # https://github.com/trinodb/trino/issues/17
205    # https://github.com/trinodb/trino/issues/12289
206    # https://github.com/prestodb/presto/issues/2863
207    NORMALIZATION_STRATEGY = NormalizationStrategy.CASE_INSENSITIVE
208
209    # The result of certain math functions in Presto/Trino is of type
210    # equal to the input type e.g: FLOOR(5.5/2) -> DECIMAL, FLOOR(5/2) -> BIGINT
211    ANNOTATORS = {
212        **Dialect.ANNOTATORS,
213        exp.Floor: lambda self, e: self._annotate_by_args(e, "this"),
214        exp.Ceil: lambda self, e: self._annotate_by_args(e, "this"),
215        exp.Mod: lambda self, e: self._annotate_by_args(e, "this", "expression"),
216        exp.Round: lambda self, e: self._annotate_by_args(e, "this"),
217        exp.Sign: lambda self, e: self._annotate_by_args(e, "this"),
218        exp.Abs: lambda self, e: self._annotate_by_args(e, "this"),
219        exp.Rand: lambda self, e: self._annotate_by_args(e, "this")
220        if e.this
221        else self._set_type(e, exp.DataType.Type.DOUBLE),
222    }
223
224    class Tokenizer(tokens.Tokenizer):
225        HEX_STRINGS = [("x'", "'"), ("X'", "'")]
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), occurrence=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.FromTimeZone: lambda self,
397            e: f"WITH_TIMEZONE({self.sql(e, 'this')}, {self.sql(e, 'zone')}) AT TIME ZONE 'UTC'",
398            exp.GenerateSeries: sequence_sql,
399            exp.GenerateDateArray: sequence_sql,
400            exp.Group: transforms.preprocess([transforms.unalias_group]),
401            exp.If: if_sql(),
402            exp.ILike: no_ilike_sql,
403            exp.Initcap: _initcap_sql,
404            exp.Last: _first_last_sql,
405            exp.LastDay: lambda self, e: self.func("LAST_DAY_OF_MONTH", e.this),
406            exp.Lateral: explode_to_unnest_sql,
407            exp.Left: left_to_substring_sql,
408            exp.Levenshtein: unsupported_args("ins_cost", "del_cost", "sub_cost", "max_dist")(
409                rename_func("LEVENSHTEIN_DISTANCE")
410            ),
411            exp.LogicalAnd: rename_func("BOOL_AND"),
412            exp.LogicalOr: rename_func("BOOL_OR"),
413            exp.Pivot: no_pivot_sql,
414            exp.Quantile: _quantile_sql,
415            exp.RegexpExtract: regexp_extract_sql,
416            exp.RegexpExtractAll: regexp_extract_sql,
417            exp.Right: right_to_substring_sql,
418            exp.Schema: _schema_sql,
419            exp.SchemaCommentProperty: lambda self, e: self.naked_property(e),
420            exp.Select: transforms.preprocess(
421                [
422                    transforms.eliminate_qualify,
423                    transforms.eliminate_distinct_on,
424                    transforms.explode_to_unnest(1),
425                    transforms.eliminate_semi_and_anti_joins,
426                ]
427            ),
428            exp.SortArray: _no_sort_array,
429            exp.StrPosition: lambda self, e: strposition_sql(self, e, supports_occurrence=True),
430            exp.StrToDate: lambda self, e: f"CAST({_str_to_time_sql(self, e)} AS DATE)",
431            exp.StrToMap: rename_func("SPLIT_TO_MAP"),
432            exp.StrToTime: _str_to_time_sql,
433            exp.StructExtract: struct_extract_sql,
434            exp.Table: transforms.preprocess([transforms.unnest_generate_series]),
435            exp.Timestamp: no_timestamp_sql,
436            exp.TimestampAdd: _date_delta_sql("DATE_ADD"),
437            exp.TimestampTrunc: timestamptrunc_sql(),
438            exp.TimeStrToDate: timestrtotime_sql,
439            exp.TimeStrToTime: timestrtotime_sql,
440            exp.TimeStrToUnix: lambda self, e: self.func(
441                "TO_UNIXTIME", self.func("DATE_PARSE", e.this, Presto.TIME_FORMAT)
442            ),
443            exp.TimeToStr: lambda self, e: self.func("DATE_FORMAT", e.this, self.format_time(e)),
444            exp.TimeToUnix: rename_func("TO_UNIXTIME"),
445            exp.ToChar: lambda self, e: self.func("DATE_FORMAT", e.this, self.format_time(e)),
446            exp.TryCast: transforms.preprocess([transforms.epoch_cast_to_ts]),
447            exp.TsOrDiToDi: lambda self,
448            e: f"CAST(SUBSTR(REPLACE(CAST({self.sql(e, 'this')} AS VARCHAR), '-', ''), 1, 8) AS INT)",
449            exp.TsOrDsAdd: _ts_or_ds_add_sql,
450            exp.TsOrDsDiff: _ts_or_ds_diff_sql,
451            exp.TsOrDsToDate: _ts_or_ds_to_date_sql,
452            exp.Unhex: rename_func("FROM_HEX"),
453            exp.UnixToStr: lambda self,
454            e: f"DATE_FORMAT(FROM_UNIXTIME({self.sql(e, 'this')}), {self.format_time(e)})",
455            exp.UnixToTime: _unix_to_time_sql,
456            exp.UnixToTimeStr: lambda self,
457            e: f"CAST(FROM_UNIXTIME({self.sql(e, 'this')}) AS VARCHAR)",
458            exp.VariancePop: rename_func("VAR_POP"),
459            exp.With: transforms.preprocess([transforms.add_recursive_cte_column_names]),
460            exp.WithinGroup: transforms.preprocess(
461                [transforms.remove_within_group_for_percentiles]
462            ),
463            exp.Xor: bool_xor_sql,
464            exp.MD5Digest: rename_func("MD5"),
465            exp.SHA: rename_func("SHA1"),
466            exp.SHA2: sha256_sql,
467        }
468
469        RESERVED_KEYWORDS = {
470            "alter",
471            "and",
472            "as",
473            "between",
474            "by",
475            "case",
476            "cast",
477            "constraint",
478            "create",
479            "cross",
480            "current_time",
481            "current_timestamp",
482            "deallocate",
483            "delete",
484            "describe",
485            "distinct",
486            "drop",
487            "else",
488            "end",
489            "escape",
490            "except",
491            "execute",
492            "exists",
493            "extract",
494            "false",
495            "for",
496            "from",
497            "full",
498            "group",
499            "having",
500            "in",
501            "inner",
502            "insert",
503            "intersect",
504            "into",
505            "is",
506            "join",
507            "left",
508            "like",
509            "natural",
510            "not",
511            "null",
512            "on",
513            "or",
514            "order",
515            "outer",
516            "prepare",
517            "right",
518            "select",
519            "table",
520            "then",
521            "true",
522            "union",
523            "using",
524            "values",
525            "when",
526            "where",
527            "with",
528        }
529
530        def md5_sql(self, expression: exp.MD5) -> str:
531            this = expression.this
532
533            if not this.type:
534                from sqlglot.optimizer.annotate_types import annotate_types
535
536                this = annotate_types(this)
537
538            if this.is_type(*exp.DataType.TEXT_TYPES):
539                this = exp.Encode(this=this, charset=exp.Literal.string("utf-8"))
540
541            return self.func("LOWER", self.func("TO_HEX", self.func("MD5", self.sql(this))))
542
543        def strtounix_sql(self, expression: exp.StrToUnix) -> str:
544            # Since `TO_UNIXTIME` requires a `TIMESTAMP`, we need to parse the argument into one.
545            # To do this, we first try to `DATE_PARSE` it, but since this can fail when there's a
546            # timezone involved, we wrap it in a `TRY` call and use `PARSE_DATETIME` as a fallback,
547            # which seems to be using the same time mapping as Hive, as per:
548            # https://joda-time.sourceforge.net/apidocs/org/joda/time/format/DateTimeFormat.html
549            this = expression.this
550            value_as_text = exp.cast(this, exp.DataType.Type.TEXT)
551            value_as_timestamp = (
552                exp.cast(this, exp.DataType.Type.TIMESTAMP) if this.is_string else this
553            )
554
555            parse_without_tz = self.func("DATE_PARSE", value_as_text, self.format_time(expression))
556
557            formatted_value = self.func(
558                "DATE_FORMAT", value_as_timestamp, self.format_time(expression)
559            )
560            parse_with_tz = self.func(
561                "PARSE_DATETIME",
562                formatted_value,
563                self.format_time(expression, Hive.INVERSE_TIME_MAPPING, Hive.INVERSE_TIME_TRIE),
564            )
565            coalesced = self.func("COALESCE", self.func("TRY", parse_without_tz), parse_with_tz)
566            return self.func("TO_UNIXTIME", coalesced)
567
568        def bracket_sql(self, expression: exp.Bracket) -> str:
569            if expression.args.get("safe"):
570                return self.func(
571                    "ELEMENT_AT",
572                    expression.this,
573                    seq_get(
574                        apply_index_offset(
575                            expression.this,
576                            expression.expressions,
577                            1 - expression.args.get("offset", 0),
578                        ),
579                        0,
580                    ),
581                )
582            return super().bracket_sql(expression)
583
584        def struct_sql(self, expression: exp.Struct) -> str:
585            from sqlglot.optimizer.annotate_types import annotate_types
586
587            expression = annotate_types(expression)
588            values: t.List[str] = []
589            schema: t.List[str] = []
590            unknown_type = False
591
592            for e in expression.expressions:
593                if isinstance(e, exp.PropertyEQ):
594                    if e.type and e.type.is_type(exp.DataType.Type.UNKNOWN):
595                        unknown_type = True
596                    else:
597                        schema.append(f"{self.sql(e, 'this')} {self.sql(e.type)}")
598                    values.append(self.sql(e, "expression"))
599                else:
600                    values.append(self.sql(e))
601
602            size = len(expression.expressions)
603
604            if not size or len(schema) != size:
605                if unknown_type:
606                    self.unsupported(
607                        "Cannot convert untyped key-value definitions (try annotate_types)."
608                    )
609                return self.func("ROW", *values)
610            return f"CAST(ROW({', '.join(values)}) AS ROW({', '.join(schema)}))"
611
612        def interval_sql(self, expression: exp.Interval) -> str:
613            if expression.this and expression.text("unit").upper().startswith("WEEK"):
614                return f"({expression.this.name} * INTERVAL '7' DAY)"
615            return super().interval_sql(expression)
616
617        def transaction_sql(self, expression: exp.Transaction) -> str:
618            modes = expression.args.get("modes")
619            modes = f" {', '.join(modes)}" if modes else ""
620            return f"START TRANSACTION{modes}"
621
622        def offset_limit_modifiers(
623            self, expression: exp.Expression, fetch: bool, limit: t.Optional[exp.Fetch | exp.Limit]
624        ) -> t.List[str]:
625            return [
626                self.sql(expression, "offset"),
627                self.sql(limit),
628            ]
629
630        def create_sql(self, expression: exp.Create) -> str:
631            """
632            Presto doesn't support CREATE VIEW with expressions (ex: `CREATE VIEW x (cola)` then `(cola)` is the expression),
633            so we need to remove them
634            """
635            kind = expression.args["kind"]
636            schema = expression.this
637            if kind == "VIEW" and schema.expressions:
638                expression.this.set("expressions", None)
639            return super().create_sql(expression)
640
641        def delete_sql(self, expression: exp.Delete) -> str:
642            """
643            Presto only supports DELETE FROM for a single table without an alias, so we need
644            to remove the unnecessary parts. If the original DELETE statement contains more
645            than one table to be deleted, we can't safely map it 1-1 to a Presto statement.
646            """
647            tables = expression.args.get("tables") or [expression.this]
648            if len(tables) > 1:
649                return super().delete_sql(expression)
650
651            table = tables[0]
652            expression.set("this", table)
653            expression.set("tables", None)
654
655            if isinstance(table, exp.Table):
656                table_alias = table.args.get("alias")
657                if table_alias:
658                    table_alias.pop()
659                    expression = t.cast(exp.Delete, expression.transform(unqualify_columns))
660
661            return super().delete_sql(expression)
662
663        def jsonextract_sql(self, expression: exp.JSONExtract) -> str:
664            is_json_extract = self.dialect.settings.get("variant_extract_is_json_extract", True)
665
666            # Generate JSON_EXTRACT unless the user has configured that a Snowflake / Databricks
667            # VARIANT extract (e.g. col:x.y) should map to dot notation (i.e ROW access) in Presto/Trino
668            if not expression.args.get("variant_extract") or is_json_extract:
669                return self.func(
670                    "JSON_EXTRACT", expression.this, expression.expression, *expression.expressions
671                )
672
673            this = self.sql(expression, "this")
674
675            # Convert the JSONPath extraction `JSON_EXTRACT(col, '$.x.y) to a ROW access col.x.y
676            segments = []
677            for path_key in expression.expression.expressions[1:]:
678                if not isinstance(path_key, exp.JSONPathKey):
679                    # Cannot transpile subscripts, wildcards etc to dot notation
680                    self.unsupported(
681                        f"Cannot transpile JSONPath segment '{path_key}' to ROW access"
682                    )
683                    continue
684                key = path_key.this
685                if not exp.SAFE_IDENTIFIER_RE.match(key):
686                    key = f'"{key}"'
687                segments.append(f".{key}")
688
689            expr = "".join(segments)
690
691            return f"{this}{expr}"
692
693        def groupconcat_sql(self, expression: exp.GroupConcat) -> str:
694            return self.func(
695                "ARRAY_JOIN",
696                self.func("ARRAY_AGG", expression.this),
697                expression.args.get("separator"),
698            )