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