sqlglot.generators.presto

  1from __future__ import annotations
  2
  3import typing as t
  4
  5from sqlglot import exp, generator, transforms
  6from sqlglot.dialects.dialect import (
  7    array_sort_order,
  8    array_slice_to_slice_sql,
  9    bool_xor_sql,
 10    bracket_to_element_at_sql,
 11    datestrtodate_sql,
 12    encode_decode_sql,
 13    if_sql,
 14    left_to_substring_sql,
 15    no_ilike_sql,
 16    no_pivot_sql,
 17    no_timestamp_sql,
 18    regexp_extract_sql,
 19    rename_func,
 20    right_to_substring_sql,
 21    strposition_sql,
 22    struct_extract_sql,
 23    timestamptrunc_sql,
 24    timestrtotime_sql,
 25    ts_or_ds_add_cast,
 26    unit_to_str,
 27    week_unit_to_dow,
 28    sequence_sql,
 29    explode_to_unnest_sql,
 30)
 31from sqlglot.dialects.hive import Hive
 32from sqlglot.generator import unsupported_args
 33from sqlglot.optimizer.scope import find_all_in_scope
 34from sqlglot.transforms import unqualify_columns
 35
 36DATE_ADD_OR_SUB = t.Union[exp.DateAdd, exp.TimestampAdd, exp.DateSub]
 37
 38
 39def _sha2_digest_sql(self: PrestoGenerator, expression: exp.SHA2Digest) -> str:
 40    length = expression.text("length") or "256"
 41    if length not in ("256", "512"):
 42        self.unsupported(f"SHA{length} is not supported in Presto")
 43
 44    this = expression.this
 45    if this.is_type(*exp.DataType.TEXT_TYPES):
 46        # the native digest takes VARBINARY, so a text-typed argument needs encoding
 47        this = exp.Encode(this=this, charset=exp.Literal.string("utf-8"))
 48
 49    return self.func(f"SHA{length}", this)
 50
 51
 52def _initcap_sql(self: PrestoGenerator, expression: exp.Initcap) -> str:
 53    delimiters = expression.expression
 54    if delimiters and not (
 55        delimiters.is_string and delimiters.this == self.dialect.INITCAP_DEFAULT_DELIMITER_CHARS
 56    ):
 57        self.unsupported("INITCAP does not support custom delimiters")
 58
 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 _array_sort_sql(self: PrestoGenerator, expression: exp.ArraySort) -> str:
 64    if expression.expression:
 65        return self.func("ARRAY_SORT", expression.this, expression.expression)
 66
 67    # ARRAY_SORT puts NULLs last, so reversing it sorts descending with NULLs first
 68    asc, nulls_first = array_sort_order(expression) or (True, True)
 69    if nulls_first == asc:
 70        self.unsupported(
 71            "ARRAY_SORT can't sort ascending with NULLs first or descending with NULLs last"
 72        )
 73    sort = self.func("ARRAY_SORT", expression.this)
 74    return sort if asc else self.func("REVERSE", sort)
 75
 76
 77def _schema_sql(self: PrestoGenerator, expression: exp.Schema) -> str:
 78    if isinstance(expression.parent, exp.PartitionedByProperty):
 79        # Any columns in the ARRAY[] string literals should not be quoted
 80        expression.transform(lambda n: n.name if isinstance(n, exp.Identifier) else n, copy=False)
 81
 82        partition_exprs = [
 83            self.sql(c) if isinstance(c, (exp.Func, exp.Property)) else self.sql(c, "this")
 84            for c in expression.expressions
 85        ]
 86        return self.sql(exp.Array(expressions=[exp.Literal.string(c) for c in partition_exprs]))
 87
 88    if expression.parent:
 89        for schema in expression.parent.find_all(exp.Schema):
 90            if schema is expression:
 91                continue
 92
 93            column_defs = schema.find_all(exp.ColumnDef)
 94            if column_defs and isinstance(schema.parent, exp.Property):
 95                expression.expressions.extend(column_defs)
 96
 97    return self.schema_sql(expression)
 98
 99
100def _quantile_sql(self: PrestoGenerator, expression: exp.Quantile) -> str:
101    self.unsupported("Presto does not support exact quantiles")
102    return self.func("APPROX_PERCENTILE", expression.this, expression.args.get("quantile"))
103
104
105def _str_to_time_sql(
106    self: PrestoGenerator, expression: exp.StrToDate | exp.StrToTime | exp.TsOrDsToDate
107) -> str:
108    return self.func("DATE_PARSE", expression.this, self.format_time(expression))
109
110
111def _ts_or_ds_to_date_sql(self: PrestoGenerator, expression: exp.TsOrDsToDate) -> str:
112    time_format = self.format_time(expression)
113    dialect_class = type(self.dialect)
114    if time_format and time_format not in (dialect_class.TIME_FORMAT, dialect_class.DATE_FORMAT):
115        return self.sql(exp.cast(_str_to_time_sql(self, expression), exp.DType.DATE))
116    return self.sql(exp.cast(exp.cast(expression.this, exp.DType.TIMESTAMP), exp.DType.DATE))
117
118
119def _ts_or_ds_add_sql(self: PrestoGenerator, expression: exp.TsOrDsAdd) -> str:
120    expression = ts_or_ds_add_cast(expression)
121    unit = unit_to_str(expression)
122    return self.func("DATE_ADD", unit, expression.expression, expression.this)
123
124
125def _ts_or_ds_diff_sql(self: PrestoGenerator, expression: exp.TsOrDsDiff) -> str:
126    this = exp.cast(expression.this, exp.DType.TIMESTAMP)
127    expr = exp.cast(expression.expression, exp.DType.TIMESTAMP)
128    unit = unit_to_str(expression)
129    return self.func("DATE_DIFF", unit, expr, this)
130
131
132def _first_last_sql(self: PrestoGenerator, 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: PrestoGenerator, 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: PrestoGenerator, expression: exp.Expr) -> exp.Expr:
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.DType.BIGINT)
162    return expression
163
164
165def _date_delta_sql(
166    name: str, negate_interval: bool = False
167) -> t.Callable[[PrestoGenerator, DATE_ADD_OR_SUB], str]:
168    def _delta_sql(self: PrestoGenerator, expression: DATE_ADD_OR_SUB) -> str:
169        interval = _to_int(self, expression.expression)
170        return self.func(
171            name,
172            unit_to_str(expression),
173            interval * (-1) if negate_interval else interval,
174            expression.this,
175        )
176
177    return _delta_sql
178
179
180def _date_diff_sql(
181    self: PrestoGenerator, expression: exp.DateDiff | exp.DatetimeDiff | exp.TimestampDiff
182) -> str:
183    # Presto/Trino only expose date_diff(unit, ts1, ts2); it returns ts2 - ts1, so the
184    # operands are emitted as (expression, this) to preserve `this - expression` semantics.
185    this: exp.Expr = expression.this
186    expr: exp.Expr = expression.expression
187    unit = unit_to_str(expression)
188
189    # DATE_DIFF counts complete units between its operands, whereas dialects that set
190    # date_part_boundary count unit boundary crossings, so the operands are truncated
191    # down to the unit to make the two coincide
192    if unit and expression.args.get("date_part_boundary"):
193        raw_unit = expression.args.get("unit")
194        dow = week_unit_to_dow(raw_unit)
195
196        if dow is not None:
197            unit = exp.Literal.string("WEEK")
198
199            # DATE_TRUNC('WEEK', ...) is Monday-based; shifting both operands by the same
200            # delta realigns it to the requested week start without changing the diff
201            shift_days = 1 if dow == 7 else 1 - dow
202            if shift_days:
203                delta = exp.Interval(this=exp.Literal.string(str(shift_days)), unit=exp.var("DAY"))
204                this = exp.Add(this=this, expression=delta)
205                expr = exp.Add(this=expr, expression=delta.copy())
206
207        if not isinstance(raw_unit, exp.WeekStart) or dow is not None:
208            this = exp.DateTrunc(unit=unit.copy(), this=this)
209            expr = exp.DateTrunc(unit=unit.copy(), this=expr)
210
211    return self.func("DATE_DIFF", unit, expr, this)
212
213
214def _explode_to_unnest_sql(self: PrestoGenerator, expression: exp.Lateral) -> str:
215    explode = expression.this
216    if isinstance(explode, exp.Explode):
217        exploded_type = explode.this.type
218        alias = expression.args.get("alias")
219
220        # This attempts a best-effort transpilation of LATERAL VIEW EXPLODE on a struct array
221        if (
222            isinstance(alias, exp.TableAlias)
223            and isinstance(exploded_type, exp.DataType)
224            and exploded_type.is_type(exp.DType.ARRAY)
225            and exploded_type.expressions
226            and exploded_type.expressions[0].is_type(exp.DType.STRUCT)
227        ):
228            # When unnesting a ROW in Presto, it produces N columns, so we need to fix the alias
229            alias.set("columns", [c.this.copy() for c in exploded_type.expressions[0].expressions])
230    elif isinstance(explode, exp.Inline):
231        explode.replace(exp.Explode(this=explode.this.copy()))
232
233    return explode_to_unnest_sql(self, expression)
234
235
236def amend_exploded_column_table(expression: exp.Expr) -> exp.Expr:
237    # We check for expression.type because the columns can be amended only if types were inferred
238    if isinstance(expression, exp.Select) and expression.type:
239        for lateral in expression.args.get("laterals") or []:
240            alias = lateral.args.get("alias")
241            if (
242                not isinstance(lateral.this, exp.Explode)
243                or not isinstance(alias, exp.TableAlias)
244                or len(alias.columns) != 1
245            ):
246                continue
247
248            new_table = alias.this
249            old_table = alias.columns[0].name.lower()
250
251            # When transpiling a LATERAL VIEW EXPLODE Spark query, the exploded fields may be qualified
252            # with the struct column, resulting in invalid Presto references that need to be amended
253            for column in find_all_in_scope(expression, exp.Column):
254                if column.db.lower() == old_table:
255                    column.set("table", column.args["db"].pop())
256                elif column.table.lower() == old_table:
257                    column.set("table", new_table.copy())
258                elif column.name.lower() == old_table and isinstance(column.parent, exp.Dot):
259                    column.parent.replace(exp.column(column.parent.expression, table=new_table))
260
261    return expression
262
263
264class PrestoGenerator(generator.Generator):
265    SELECT_KINDS: tuple[str, ...] = ()
266    SUPPORTS_DECODE_CASE = False
267    SUPPORTS_TEMPORAL_PRECISION = False
268
269    AFTER_HAVING_MODIFIER_TRANSFORMS = generator.AFTER_HAVING_MODIFIER_TRANSFORMS
270
271    INTERVAL_ALLOWS_PLURAL_FORM = False
272    JOIN_HINTS = False
273    CAST_TO_INT_ROUNDS = True
274    TABLE_HINTS = False
275    QUERY_HINTS = False
276    IS_BOOL_ALLOWED = False
277    TZ_TO_WITH_TIME_ZONE = True
278    NVL2_SUPPORTED = False
279    STRUCT_DELIMITER = ("(", ")")
280    LIMIT_ONLY_LITERALS = True
281    SUPPORTS_SINGLE_ARG_CONCAT = False
282    LIKE_PROPERTY_INSIDE_SCHEMA = True
283    MULTI_ARG_DISTINCT = False
284    SUPPORTS_TO_NUMBER = False
285    HEX_FUNC = "TO_HEX"
286    PARSE_JSON_NAME: str | None = "JSON_PARSE"
287    PAD_FILL_PATTERN_IS_REQUIRED = True
288    EXCEPT_INTERSECT_SUPPORT_ALL_CLAUSE = False
289    SUPPORTS_MEDIAN = False
290    ARRAY_SIZE_NAME = "CARDINALITY"
291
292    PROPERTIES_LOCATION = {
293        **generator.Generator.PROPERTIES_LOCATION,
294        exp.LocationProperty: exp.Properties.Location.UNSUPPORTED,
295        exp.VolatileProperty: exp.Properties.Location.UNSUPPORTED,
296    }
297
298    TYPE_MAPPING = {
299        **generator.Generator.TYPE_MAPPING,
300        exp.DType.BINARY: "VARBINARY",
301        exp.DType.BIT: "BOOLEAN",
302        exp.DType.DATETIME: "TIMESTAMP",
303        exp.DType.DATETIME64: "TIMESTAMP",
304        exp.DType.FLOAT: "REAL",
305        exp.DType.HLLSKETCH: "HYPERLOGLOG",
306        exp.DType.INT: "INTEGER",
307        exp.DType.STRUCT: "ROW",
308        exp.DType.TEXT: "VARCHAR",
309        exp.DType.TIMESTAMPTZ: "TIMESTAMP",
310        exp.DType.TIMESTAMPNTZ: "TIMESTAMP",
311        exp.DType.TIMETZ: "TIME",
312    }
313
314    TRANSFORMS = {
315        **generator.Generator.TRANSFORMS,
316        exp.AnyValue: rename_func("ARBITRARY"),
317        exp.ApproxQuantile: lambda self, e: self.func(
318            "APPROX_PERCENTILE",
319            e.this,
320            e.args.get("weight"),
321            e.args.get("quantile"),
322            e.args.get("accuracy"),
323        ),
324        exp.ArgMax: rename_func("MAX_BY"),
325        exp.ArgMin: rename_func("MIN_BY"),
326        exp.Array: transforms.preprocess(
327            [transforms.inherit_struct_field_names],
328            generator=lambda self, e: f"ARRAY[{self.expressions(e, flat=True)}]",
329        ),
330        exp.ArrayAny: rename_func("ANY_MATCH"),
331        exp.ArrayConcat: rename_func("CONCAT"),
332        exp.ArrayContains: rename_func("CONTAINS"),
333        exp.ArraySort: _array_sort_sql,
334        exp.ArrayToString: rename_func("ARRAY_JOIN"),
335        exp.ArrayUniqueAgg: rename_func("SET_AGG"),
336        exp.ArraySlice: array_slice_to_slice_sql,
337        exp.AtTimeZone: rename_func("AT_TIMEZONE"),
338        exp.BitwiseAnd: lambda self, e: self.func("BITWISE_AND", e.this, e.expression),
339        exp.BitwiseLeftShift: rename_func("BITWISE_LEFT_SHIFT"),
340        exp.BitwiseNot: lambda self, e: self.func("BITWISE_NOT", e.this),
341        exp.BitwiseOr: lambda self, e: self.func("BITWISE_OR", e.this, e.expression),
342        exp.BitwiseRightShift: rename_func("BITWISE_RIGHT_SHIFT"),
343        exp.BitwiseXor: lambda self, e: self.func("BITWISE_XOR", e.this, e.expression),
344        exp.Cast: transforms.preprocess([transforms.epoch_cast_to_ts]),
345        exp.CurrentTime: lambda *_: "CURRENT_TIME",
346        exp.CurrentTimestamp: lambda *_: "CURRENT_TIMESTAMP",
347        exp.CurrentUser: lambda *_: "CURRENT_USER",
348        exp.DateAdd: _date_delta_sql("DATE_ADD"),
349        exp.DateDiff: _date_diff_sql,
350        exp.DatetimeDiff: _date_diff_sql,
351        exp.TimestampDiff: _date_diff_sql,
352        exp.DateStrToDate: datestrtodate_sql,
353        exp.DateToDi: lambda self, e: (
354            f"CAST(DATE_FORMAT({self.sql(e, 'this')}, {type(self.dialect).DATEINT_FORMAT}) AS INT)"
355        ),
356        exp.DateSub: _date_delta_sql("DATE_ADD", negate_interval=True),
357        exp.DayOfWeek: lambda self, e: f"(({self.func('DAY_OF_WEEK', e.this)} % 7) + 1)",
358        exp.DayOfWeekIso: rename_func("DAY_OF_WEEK"),
359        exp.Decode: lambda self, e: encode_decode_sql(self, e, "FROM_UTF8"),
360        exp.DiToDate: lambda self, e: (
361            f"CAST(DATE_PARSE(CAST({self.sql(e, 'this')} AS VARCHAR), {type(self.dialect).DATEINT_FORMAT}) AS DATE)"
362        ),
363        exp.Encode: lambda self, e: encode_decode_sql(self, e, "TO_UTF8"),
364        exp.FileFormatProperty: lambda self, e: f"format={self.sql(exp.Literal.string(e.name))}",
365        exp.First: _first_last_sql,
366        exp.FromISO8601Date: rename_func("FROM_ISO8601_DATE"),
367        exp.FromISO8601Timestamp: rename_func("FROM_ISO8601_TIMESTAMP"),
368        exp.FromTimeZone: lambda self, e: (
369            f"WITH_TIMEZONE({self.sql(e, 'this')}, {self.sql(e, 'zone')}) AT TIME ZONE 'UTC'"
370        ),
371        exp.GenerateSeries: sequence_sql,
372        exp.GenerateDateArray: sequence_sql,
373        exp.If: if_sql(),
374        exp.ILike: no_ilike_sql,
375        exp.Initcap: _initcap_sql,
376        exp.Last: _first_last_sql,
377        exp.LastDay: lambda self, e: self.func("LAST_DAY_OF_MONTH", e.this),
378        exp.Lateral: _explode_to_unnest_sql,
379        exp.Left: left_to_substring_sql,
380        exp.Levenshtein: unsupported_args("ins_cost", "del_cost", "sub_cost", "max_dist")(
381            rename_func("LEVENSHTEIN_DISTANCE")
382        ),
383        exp.LogicalAnd: rename_func("BOOL_AND"),
384        exp.LogicalOr: rename_func("BOOL_OR"),
385        exp.Pivot: no_pivot_sql,
386        exp.Quantile: _quantile_sql,
387        exp.RegexpExtract: regexp_extract_sql,
388        exp.RegexpExtractAll: regexp_extract_sql,
389        exp.Right: right_to_substring_sql,
390        exp.Schema: _schema_sql,
391        exp.SchemaCommentProperty: lambda self, e: self.naked_property(e),
392        exp.Select: transforms.preprocess(
393            [
394                transforms.eliminate_window_clause,
395                transforms.eliminate_qualify,
396                transforms.eliminate_distinct_on,
397                transforms.explode_projection_to_unnest(1, unnest_map=True),
398                transforms.eliminate_semi_and_anti_joins,
399                amend_exploded_column_table,
400            ]
401        ),
402        exp.SqlSecurityProperty: lambda self, e: f"SECURITY {self.sql(e.this)}",
403        exp.StrPosition: lambda self, e: strposition_sql(self, e, supports_occurrence=True),
404        exp.StrToDate: lambda self, e: f"CAST({_str_to_time_sql(self, e)} AS DATE)",
405        exp.StrToMap: rename_func("SPLIT_TO_MAP"),
406        exp.StrToTime: _str_to_time_sql,
407        exp.StructExtract: struct_extract_sql,
408        exp.Table: transforms.preprocess([transforms.unnest_generate_series]),
409        exp.Timestamp: no_timestamp_sql,
410        exp.TimestampAdd: _date_delta_sql("DATE_ADD"),
411        exp.TimestampTrunc: timestamptrunc_sql(),
412        exp.TimeStrToDate: timestrtotime_sql,
413        exp.TimeStrToTime: timestrtotime_sql,
414        exp.TimeStrToUnix: lambda self, e: self.func(
415            "TO_UNIXTIME", self.func("DATE_PARSE", e.this, type(self.dialect).TIME_FORMAT)
416        ),
417        exp.TimeToStr: lambda self, e: self.func("DATE_FORMAT", e.this, self.format_time(e)),
418        exp.TimeToUnix: rename_func("TO_UNIXTIME"),
419        exp.ToChar: lambda self, e: self.func("DATE_FORMAT", e.this, self.format_time(e)),
420        exp.TryCast: transforms.preprocess([transforms.epoch_cast_to_ts]),
421        exp.TsOrDiToDi: lambda self, e: (
422            f"CAST(SUBSTR(REPLACE(CAST({self.sql(e, 'this')} AS VARCHAR), '-', ''), 1, 8) AS INT)"
423        ),
424        exp.TsOrDsAdd: _ts_or_ds_add_sql,
425        exp.TsOrDsDiff: _ts_or_ds_diff_sql,
426        exp.TsOrDsToDate: _ts_or_ds_to_date_sql,
427        exp.Unhex: rename_func("FROM_HEX"),
428        exp.UnixToStr: lambda self, e: (
429            f"DATE_FORMAT(FROM_UNIXTIME({self.sql(e, 'this')}), {self.format_time(e)})"
430        ),
431        exp.UnixToTime: _unix_to_time_sql,
432        exp.UnixToTimeStr: lambda self, e: f"CAST(FROM_UNIXTIME({self.sql(e, 'this')}) AS VARCHAR)",
433        exp.VariancePop: rename_func("VAR_POP"),
434        exp.With: transforms.preprocess([transforms.add_recursive_cte_column_names]),
435        exp.WithinGroup: transforms.preprocess([transforms.remove_within_group_for_percentiles]),
436        # Note: Presto's TRUNCATE always returns DOUBLE, even with decimals=0, whereas
437        # most dialects return INT (SQLite also returns REAL, see sqlite.py). This creates
438        # a bidirectional transpilation gap: Presto→Other may change float division to int
439        # division, and vice versa. Modeling precisely would require exp.FloatTrunc or
440        # similar, deemed overengineering for this subtle semantic difference.
441        exp.Trunc: rename_func("TRUNCATE"),
442        exp.Xor: bool_xor_sql,
443        exp.MD5Digest: rename_func("MD5"),
444        exp.SHA: rename_func("SHA1"),
445        exp.SHA1Digest: rename_func("SHA1"),
446        exp.SHA2Digest: _sha2_digest_sql,
447        exp.Substring: rename_func("SUBSTR"),
448    }
449
450    RESERVED_KEYWORDS = {
451        "alter",
452        "and",
453        "as",
454        "between",
455        "by",
456        "case",
457        "cast",
458        "constraint",
459        "create",
460        "cross",
461        "current_time",
462        "current_timestamp",
463        "deallocate",
464        "delete",
465        "describe",
466        "distinct",
467        "drop",
468        "else",
469        "end",
470        "escape",
471        "except",
472        "execute",
473        "exists",
474        "extract",
475        "false",
476        "for",
477        "from",
478        "full",
479        "group",
480        "having",
481        "in",
482        "inner",
483        "insert",
484        "intersect",
485        "into",
486        "is",
487        "join",
488        "left",
489        "like",
490        "natural",
491        "not",
492        "null",
493        "on",
494        "or",
495        "order",
496        "outer",
497        "prepare",
498        "right",
499        "select",
500        "table",
501        "then",
502        "true",
503        "union",
504        "using",
505        "values",
506        "when",
507        "where",
508        "with",
509    }
510
511    def datatype_sql(self, expression: exp.DataType) -> str:
512        if not self.SUPPORTS_TEMPORAL_PRECISION and expression.this in exp.DataType.TEMPORAL_TYPES:
513            if expression.expressions and expression.expressions[0].name != "3":
514                self.unsupported("Presto only supports millisecond precision for temporal types")
515            expression.set("expressions", None)
516        return super().datatype_sql(expression)
517
518    def extract_sql(self, expression: exp.Extract) -> str:
519        date_part = expression.name
520
521        if not date_part.startswith("EPOCH"):
522            return super().extract_sql(expression)
523
524        if date_part == "EPOCH_MILLISECOND":
525            scale = 10**3
526        elif date_part == "EPOCH_MICROSECOND":
527            scale = 10**6
528        elif date_part == "EPOCH_NANOSECOND":
529            scale = 10**9
530        else:
531            scale = None
532
533        value = expression.expression
534
535        ts = exp.cast(value, to=exp.DType.TIMESTAMP.into_expr())
536        to_unix: exp.Expr = exp.TimeToUnix(this=ts)
537
538        if scale:
539            to_unix = exp.Mul(this=to_unix, expression=exp.Literal.number(scale))
540
541        return self.sql(to_unix)
542
543    def jsonformat_sql(self, expression: exp.JSONFormat) -> str:
544        this = expression.this
545        is_json = expression.args.get("is_json")
546
547        if this and not (is_json or this.type):
548            from sqlglot.optimizer.annotate_types import annotate_types
549
550            this = annotate_types(this, dialect=self.dialect)
551
552        if not (is_json or this.is_type(exp.DType.JSON)):
553            this.replace(exp.cast(this, exp.DType.JSON))
554
555        return self.function_fallback_sql(expression)
556
557    def md5_sql(self, expression: exp.MD5) -> str:
558        this = expression.this
559
560        if not this.type:
561            from sqlglot.optimizer.annotate_types import annotate_types
562
563            this = annotate_types(this, dialect=self.dialect)
564
565        if this.is_type(*exp.DataType.TEXT_TYPES):
566            this = exp.Encode(this=this, charset=exp.Literal.string("utf-8"))
567
568        return self.func("LOWER", self.func("TO_HEX", self.func("MD5", self.sql(this))))
569
570    def sha2_sql(self, expression: exp.SHA2) -> str:
571        length = expression.text("length") or "256"
572        if length not in ("256", "512"):
573            self.unsupported(f"SHA{length} is not supported in Presto")
574
575        this = expression.this
576
577        if this.is_type(*exp.DataType.TEXT_TYPES):
578            this = exp.Encode(this=this, charset=exp.Literal.string("utf-8"))
579
580        return self.func("LOWER", self.func("TO_HEX", self.func(f"SHA{length}", self.sql(this))))
581
582    def strtounix_sql(self, expression: exp.StrToUnix) -> str:
583        # Since `TO_UNIXTIME` requires a `TIMESTAMP`, we need to parse the argument into one.
584        # To do this, we first try to `DATE_PARSE` it, but since this can fail when there's a
585        # timezone involved, we wrap it in a `TRY` call and use `PARSE_DATETIME` as a fallback,
586        # which seems to be using the same time mapping as Hive, as per:
587        # https://joda-time.sourceforge.net/apidocs/org/joda/time/format/DateTimeFormat.html
588        this = expression.this
589        value_as_text = exp.cast(this, exp.DType.TEXT)
590        value_as_timestamp = exp.cast(this, exp.DType.TIMESTAMP) if this.is_string else this
591
592        parse_without_tz = self.func("DATE_PARSE", value_as_text, self.format_time(expression))
593
594        formatted_value = self.func("DATE_FORMAT", value_as_timestamp, self.format_time(expression))
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 bracket_to_element_at_sql(self, expression)
606        return super().bracket_sql(expression)
607
608    def struct_sql(self, expression: exp.Struct) -> str:
609        if not expression.type:
610            from sqlglot.optimizer.annotate_types import annotate_types
611
612            annotate_types(expression, dialect=self.dialect)
613
614        values: list[str] = []
615        schema: list[str] = []
616        unknown_type = False
617
618        for e in expression.expressions:
619            if isinstance(e, exp.PropertyEQ):
620                if e.type and e.type.is_type(exp.DType.UNKNOWN):
621                    unknown_type = True
622                else:
623                    schema.append(f"{self.sql(e, 'this')} {self.sql(e.type)}")
624                values.append(self.sql(e, "expression"))
625            else:
626                values.append(self.sql(e))
627
628        size = len(expression.expressions)
629
630        if not size or len(schema) != size:
631            if unknown_type:
632                self.unsupported(
633                    "Cannot convert untyped key-value definitions (try annotate_types)."
634                )
635            return self.func("ROW", *values)
636        return f"CAST(ROW({', '.join(values)}) AS ROW({', '.join(schema)}))"
637
638    def interval_sql(self, expression: exp.Interval) -> str:
639        if expression.this and expression.text("unit").upper().startswith("WEEK"):
640            return f"({expression.this.name} * INTERVAL '7' DAY)"
641        return super().interval_sql(expression)
642
643    def transaction_sql(self, expression: exp.Transaction) -> str:
644        modes = expression.args.get("modes")
645        modes = f" {', '.join(modes)}" if modes else ""
646        return f"START TRANSACTION{modes}"
647
648    def offset_limit_modifiers(
649        self, expression: exp.Expr, fetch: bool, limit: exp.Fetch | exp.Limit | None
650    ) -> list[str]:
651        return [
652            self.sql(expression, "offset"),
653            self.sql(limit),
654        ]
655
656    def create_sql(self, expression: exp.Create) -> str:
657        """
658        Presto doesn't support CREATE VIEW with expressions (ex: `CREATE VIEW x (cola)` then `(cola)` is the expression),
659        so we need to remove them
660        """
661        kind = expression.args["kind"]
662        schema = expression.this
663        if kind == "VIEW" and schema.expressions:
664            expression.this.set("expressions", None)
665        return super().create_sql(expression)
666
667    def delete_sql(self, expression: exp.Delete) -> str:
668        """
669        Presto only supports DELETE FROM for a single table without an alias, so we need
670        to remove the unnecessary parts. If the original DELETE statement contains more
671        than one table to be deleted, we can't safely map it 1-1 to a Presto statement.
672        """
673        tables = expression.args.get("tables") or [expression.this]
674        if len(tables) > 1:
675            return super().delete_sql(expression)
676
677        table = tables[0]
678        expression.set("this", table)
679        expression.set("tables", None)
680
681        if isinstance(table, exp.Table):
682            table_alias = table.args.get("alias")
683            if table_alias:
684                table_alias.pop()
685                expression = t.cast(exp.Delete, expression.transform(unqualify_columns))
686
687        return super().delete_sql(expression)
688
689    def jsonextract_sql(self, expression: exp.JSONExtract) -> str:
690        is_json_extract = self.dialect.settings.get("variant_extract_is_json_extract", True)
691
692        # Generate JSON_EXTRACT unless the user has configured that a Snowflake / Databricks
693        # VARIANT extract (e.g. col:x.y) should map to dot notation (i.e ROW access) in Presto/Trino
694        if not expression.args.get("variant_extract") or is_json_extract:
695            return self.func(
696                "JSON_EXTRACT", expression.this, expression.expression, *expression.expressions
697            )
698
699        this = self.sql(expression, "this")
700
701        # Convert the JSONPath extraction `JSON_EXTRACT(col, '$.x.y) to a ROW access col.x.y
702        segments = []
703        for path_key in expression.expression.expressions[1:]:
704            if not isinstance(path_key, exp.JSONPathKey):
705                # Cannot transpile subscripts, wildcards etc to dot notation
706                self.unsupported(f"Cannot transpile JSONPath segment '{path_key}' to ROW access")
707                continue
708            key = path_key.this
709            if not exp.SAFE_IDENTIFIER_RE.match(key):
710                key = f'"{key}"'
711            segments.append(f".{key}")
712
713        expr = "".join(segments)
714
715        return f"{this}{expr}"
716
717    def groupconcat_sql(self, expression: exp.GroupConcat) -> str:
718        return self.func(
719            "ARRAY_JOIN",
720            self.func("ARRAY_AGG", expression.this),
721            expression.args.get("separator"),
722        )