sqlglot.dialects.hive

  1from __future__ import annotations
  2
  3import typing as t
  4from copy import deepcopy
  5from functools import partial
  6from collections import defaultdict
  7
  8from sqlglot import exp, generator, parser, tokens, transforms
  9from sqlglot.dialects.dialect import (
 10    DATE_ADD_OR_SUB,
 11    Dialect,
 12    NormalizationStrategy,
 13    approx_count_distinct_sql,
 14    arg_max_or_min_no_count,
 15    datestrtodate_sql,
 16    build_formatted_time,
 17    if_sql,
 18    is_parse_json,
 19    left_to_substring_sql,
 20    max_or_greatest,
 21    min_or_least,
 22    no_ilike_sql,
 23    no_recursive_cte_sql,
 24    no_trycast_sql,
 25    regexp_extract_sql,
 26    regexp_replace_sql,
 27    rename_func,
 28    right_to_substring_sql,
 29    strposition_sql,
 30    struct_extract_sql,
 31    time_format,
 32    timestrtotime_sql,
 33    unit_to_str,
 34    var_map_sql,
 35    sequence_sql,
 36    property_sql,
 37    build_regexp_extract,
 38)
 39from sqlglot.transforms import (
 40    remove_unique_constraints,
 41    ctas_with_tmp_tables_to_create_tmp_view,
 42    preprocess,
 43    move_schema_columns_to_partitioned_by,
 44)
 45from sqlglot.helper import seq_get
 46from sqlglot.tokens import TokenType
 47from sqlglot.generator import unsupported_args
 48from sqlglot.optimizer.annotate_types import TypeAnnotator
 49
 50# (FuncType, Multiplier)
 51DATE_DELTA_INTERVAL = {
 52    "YEAR": ("ADD_MONTHS", 12),
 53    "MONTH": ("ADD_MONTHS", 1),
 54    "QUARTER": ("ADD_MONTHS", 3),
 55    "WEEK": ("DATE_ADD", 7),
 56    "DAY": ("DATE_ADD", 1),
 57}
 58
 59TIME_DIFF_FACTOR = {
 60    "MILLISECOND": " * 1000",
 61    "SECOND": "",
 62    "MINUTE": " / 60",
 63    "HOUR": " / 3600",
 64}
 65
 66DIFF_MONTH_SWITCH = ("YEAR", "QUARTER", "MONTH")
 67
 68
 69def _add_date_sql(self: Hive.Generator, expression: DATE_ADD_OR_SUB) -> str:
 70    if isinstance(expression, exp.TsOrDsAdd) and not expression.unit:
 71        return self.func("DATE_ADD", expression.this, expression.expression)
 72
 73    unit = expression.text("unit").upper()
 74    func, multiplier = DATE_DELTA_INTERVAL.get(unit, ("DATE_ADD", 1))
 75
 76    if isinstance(expression, exp.DateSub):
 77        multiplier *= -1
 78
 79    increment = expression.expression
 80    if isinstance(increment, exp.Literal):
 81        value = increment.to_py() if increment.is_number else int(increment.name)
 82        increment = exp.Literal.number(value * multiplier)
 83    elif multiplier != 1:
 84        increment *= exp.Literal.number(multiplier)
 85
 86    return self.func(func, expression.this, increment)
 87
 88
 89def _date_diff_sql(self: Hive.Generator, expression: exp.DateDiff | exp.TsOrDsDiff) -> str:
 90    unit = expression.text("unit").upper()
 91
 92    factor = TIME_DIFF_FACTOR.get(unit)
 93    if factor is not None:
 94        left = self.sql(expression, "this")
 95        right = self.sql(expression, "expression")
 96        sec_diff = f"UNIX_TIMESTAMP({left}) - UNIX_TIMESTAMP({right})"
 97        return f"({sec_diff}){factor}" if factor else sec_diff
 98
 99    months_between = unit in DIFF_MONTH_SWITCH
100    sql_func = "MONTHS_BETWEEN" if months_between else "DATEDIFF"
101    _, multiplier = DATE_DELTA_INTERVAL.get(unit, ("", 1))
102    multiplier_sql = f" / {multiplier}" if multiplier > 1 else ""
103    diff_sql = f"{sql_func}({self.format_args(expression.this, expression.expression)})"
104
105    if months_between or multiplier_sql:
106        # MONTHS_BETWEEN returns a float, so we need to truncate the fractional part.
107        # For the same reason, we want to truncate if there's a divisor present.
108        diff_sql = f"CAST({diff_sql}{multiplier_sql} AS INT)"
109
110    return diff_sql
111
112
113def _json_format_sql(self: Hive.Generator, expression: exp.JSONFormat) -> str:
114    this = expression.this
115
116    if is_parse_json(this):
117        if this.this.is_string:
118            # Since FROM_JSON requires a nested type, we always wrap the json string with
119            # an array to ensure that "naked" strings like "'a'" will be handled correctly
120            wrapped_json = exp.Literal.string(f"[{this.this.name}]")
121
122            from_json = self.func(
123                "FROM_JSON", wrapped_json, self.func("SCHEMA_OF_JSON", wrapped_json)
124            )
125            to_json = self.func("TO_JSON", from_json)
126
127            # This strips the [, ] delimiters of the dummy array printed by TO_JSON
128            return self.func("REGEXP_EXTRACT", to_json, "'^.(.*).$'", "1")
129        return self.sql(this)
130
131    return self.func("TO_JSON", this, expression.args.get("options"))
132
133
134@generator.unsupported_args(("expression", "Hive's SORT_ARRAY does not support a comparator."))
135def _array_sort_sql(self: Hive.Generator, expression: exp.ArraySort) -> str:
136    return self.func("SORT_ARRAY", expression.this)
137
138
139def _str_to_unix_sql(self: Hive.Generator, expression: exp.StrToUnix) -> str:
140    return self.func("UNIX_TIMESTAMP", expression.this, time_format("hive")(self, expression))
141
142
143def _unix_to_time_sql(self: Hive.Generator, expression: exp.UnixToTime) -> str:
144    timestamp = self.sql(expression, "this")
145    scale = expression.args.get("scale")
146    if scale in (None, exp.UnixToTime.SECONDS):
147        return rename_func("FROM_UNIXTIME")(self, expression)
148
149    return f"FROM_UNIXTIME({timestamp} / POW(10, {scale}))"
150
151
152def _str_to_date_sql(self: Hive.Generator, expression: exp.StrToDate) -> str:
153    this = self.sql(expression, "this")
154    time_format = self.format_time(expression)
155    if time_format not in (Hive.TIME_FORMAT, Hive.DATE_FORMAT):
156        this = f"FROM_UNIXTIME(UNIX_TIMESTAMP({this}, {time_format}))"
157    return f"CAST({this} AS DATE)"
158
159
160def _str_to_time_sql(self: Hive.Generator, expression: exp.StrToTime) -> str:
161    this = self.sql(expression, "this")
162    time_format = self.format_time(expression)
163    if time_format not in (Hive.TIME_FORMAT, Hive.DATE_FORMAT):
164        this = f"FROM_UNIXTIME(UNIX_TIMESTAMP({this}, {time_format}))"
165    return f"CAST({this} AS TIMESTAMP)"
166
167
168def _to_date_sql(self: Hive.Generator, expression: exp.TsOrDsToDate) -> str:
169    time_format = self.format_time(expression)
170    if time_format and time_format not in (Hive.TIME_FORMAT, Hive.DATE_FORMAT):
171        return self.func("TO_DATE", expression.this, time_format)
172
173    if isinstance(expression.parent, self.TS_OR_DS_EXPRESSIONS):
174        return self.sql(expression, "this")
175
176    return self.func("TO_DATE", expression.this)
177
178
179def _build_with_ignore_nulls(
180    exp_class: t.Type[exp.Expression],
181) -> t.Callable[[t.List[exp.Expression]], exp.Expression]:
182    def _parse(args: t.List[exp.Expression]) -> exp.Expression:
183        this = exp_class(this=seq_get(args, 0))
184        if seq_get(args, 1) == exp.true():
185            return exp.IgnoreNulls(this=this)
186        return this
187
188    return _parse
189
190
191def _build_to_date(args: t.List) -> exp.TsOrDsToDate:
192    expr = build_formatted_time(exp.TsOrDsToDate, "hive")(args)
193    expr.set("safe", True)
194    return expr
195
196
197def _build_date_add(args: t.List) -> exp.TsOrDsAdd:
198    expression = seq_get(args, 1)
199    if expression:
200        expression = expression * -1
201
202    return exp.TsOrDsAdd(
203        this=seq_get(args, 0), expression=expression, unit=exp.Literal.string("DAY")
204    )
205
206
207class Hive(Dialect):
208    ALIAS_POST_TABLESAMPLE = True
209    IDENTIFIERS_CAN_START_WITH_DIGIT = True
210    SUPPORTS_USER_DEFINED_TYPES = False
211    SAFE_DIVISION = True
212    ARRAY_AGG_INCLUDES_NULLS = None
213    REGEXP_EXTRACT_DEFAULT_GROUP = 1
214
215    # https://spark.apache.org/docs/latest/sql-ref-identifier.html#description
216    NORMALIZATION_STRATEGY = NormalizationStrategy.CASE_INSENSITIVE
217
218    ANNOTATORS = {
219        **Dialect.ANNOTATORS,
220        exp.If: lambda self, e: self._annotate_by_args(e, "true", "false", promote=True),
221        exp.Coalesce: lambda self, e: self._annotate_by_args(
222            e, "this", "expressions", promote=True
223        ),
224    }
225
226    # Support only the non-ANSI mode (default for Hive, Spark2, Spark)
227    COERCES_TO = defaultdict(set, deepcopy(TypeAnnotator.COERCES_TO))
228    for target_type in {
229        *exp.DataType.NUMERIC_TYPES,
230        *exp.DataType.TEMPORAL_TYPES,
231        exp.DataType.Type.INTERVAL,
232    }:
233        COERCES_TO[target_type] |= exp.DataType.TEXT_TYPES
234
235    TIME_MAPPING = {
236        "y": "%Y",
237        "Y": "%Y",
238        "YYYY": "%Y",
239        "yyyy": "%Y",
240        "YY": "%y",
241        "yy": "%y",
242        "MMMM": "%B",
243        "MMM": "%b",
244        "MM": "%m",
245        "M": "%-m",
246        "dd": "%d",
247        "d": "%-d",
248        "HH": "%H",
249        "H": "%-H",
250        "hh": "%I",
251        "h": "%-I",
252        "mm": "%M",
253        "m": "%-M",
254        "ss": "%S",
255        "s": "%-S",
256        "SSSSSS": "%f",
257        "a": "%p",
258        "DD": "%j",
259        "D": "%-j",
260        "E": "%a",
261        "EE": "%a",
262        "EEE": "%a",
263        "EEEE": "%A",
264        "z": "%Z",
265        "Z": "%z",
266    }
267
268    DATE_FORMAT = "'yyyy-MM-dd'"
269    DATEINT_FORMAT = "'yyyyMMdd'"
270    TIME_FORMAT = "'yyyy-MM-dd HH:mm:ss'"
271
272    class Tokenizer(tokens.Tokenizer):
273        QUOTES = ["'", '"']
274        IDENTIFIERS = ["`"]
275        STRING_ESCAPES = ["\\"]
276
277        SINGLE_TOKENS = {
278            **tokens.Tokenizer.SINGLE_TOKENS,
279            "$": TokenType.PARAMETER,
280        }
281
282        KEYWORDS = {
283            **tokens.Tokenizer.KEYWORDS,
284            "ADD ARCHIVE": TokenType.COMMAND,
285            "ADD ARCHIVES": TokenType.COMMAND,
286            "ADD FILE": TokenType.COMMAND,
287            "ADD FILES": TokenType.COMMAND,
288            "ADD JAR": TokenType.COMMAND,
289            "ADD JARS": TokenType.COMMAND,
290            "MINUS": TokenType.EXCEPT,
291            "MSCK REPAIR": TokenType.COMMAND,
292            "REFRESH": TokenType.REFRESH,
293            "TIMESTAMP AS OF": TokenType.TIMESTAMP_SNAPSHOT,
294            "VERSION AS OF": TokenType.VERSION_SNAPSHOT,
295            "SERDEPROPERTIES": TokenType.SERDE_PROPERTIES,
296        }
297
298        NUMERIC_LITERALS = {
299            "L": "BIGINT",
300            "S": "SMALLINT",
301            "Y": "TINYINT",
302            "D": "DOUBLE",
303            "F": "FLOAT",
304            "BD": "DECIMAL",
305        }
306
307    class Parser(parser.Parser):
308        LOG_DEFAULTS_TO_LN = True
309        STRICT_CAST = False
310        VALUES_FOLLOWED_BY_PAREN = False
311        JOINS_HAVE_EQUAL_PRECEDENCE = True
312        ADD_JOIN_ON_TRUE = True
313
314        FUNCTIONS = {
315            **parser.Parser.FUNCTIONS,
316            "BASE64": exp.ToBase64.from_arg_list,
317            "COLLECT_LIST": lambda args: exp.ArrayAgg(this=seq_get(args, 0), nulls_excluded=True),
318            "COLLECT_SET": exp.ArrayUniqueAgg.from_arg_list,
319            "DATE_ADD": lambda args: exp.TsOrDsAdd(
320                this=seq_get(args, 0), expression=seq_get(args, 1), unit=exp.Literal.string("DAY")
321            ),
322            "DATE_FORMAT": lambda args: build_formatted_time(exp.TimeToStr, "hive")(
323                [
324                    exp.TimeStrToTime(this=seq_get(args, 0)),
325                    seq_get(args, 1),
326                ]
327            ),
328            "DATE_SUB": _build_date_add,
329            "DATEDIFF": lambda args: exp.DateDiff(
330                this=exp.TsOrDsToDate(this=seq_get(args, 0)),
331                expression=exp.TsOrDsToDate(this=seq_get(args, 1)),
332            ),
333            "DAY": lambda args: exp.Day(this=exp.TsOrDsToDate(this=seq_get(args, 0))),
334            "FIRST": _build_with_ignore_nulls(exp.First),
335            "FIRST_VALUE": _build_with_ignore_nulls(exp.FirstValue),
336            "FROM_UNIXTIME": build_formatted_time(exp.UnixToStr, "hive", True),
337            "GET_JSON_OBJECT": lambda args, dialect: exp.JSONExtractScalar(
338                this=seq_get(args, 0), expression=dialect.to_json_path(seq_get(args, 1))
339            ),
340            "LAST": _build_with_ignore_nulls(exp.Last),
341            "LAST_VALUE": _build_with_ignore_nulls(exp.LastValue),
342            "MAP": parser.build_var_map,
343            "MONTH": lambda args: exp.Month(this=exp.TsOrDsToDate.from_arg_list(args)),
344            "PERCENTILE": exp.Quantile.from_arg_list,
345            "PERCENTILE_APPROX": exp.ApproxQuantile.from_arg_list,
346            "REGEXP_EXTRACT": build_regexp_extract(exp.RegexpExtract),
347            "REGEXP_EXTRACT_ALL": build_regexp_extract(exp.RegexpExtractAll),
348            "SEQUENCE": exp.GenerateSeries.from_arg_list,
349            "SIZE": exp.ArraySize.from_arg_list,
350            "SPLIT": exp.RegexpSplit.from_arg_list,
351            "STR_TO_MAP": lambda args: exp.StrToMap(
352                this=seq_get(args, 0),
353                pair_delim=seq_get(args, 1) or exp.Literal.string(","),
354                key_value_delim=seq_get(args, 2) or exp.Literal.string(":"),
355            ),
356            "TO_DATE": _build_to_date,
357            "TO_JSON": exp.JSONFormat.from_arg_list,
358            "TRUNC": exp.TimestampTrunc.from_arg_list,
359            "UNBASE64": exp.FromBase64.from_arg_list,
360            "UNIX_TIMESTAMP": lambda args: build_formatted_time(exp.StrToUnix, "hive", True)(
361                args or [exp.CurrentTimestamp()]
362            ),
363            "YEAR": lambda args: exp.Year(this=exp.TsOrDsToDate.from_arg_list(args)),
364        }
365
366        NO_PAREN_FUNCTION_PARSERS = {
367            **parser.Parser.NO_PAREN_FUNCTION_PARSERS,
368            "TRANSFORM": lambda self: self._parse_transform(),
369        }
370
371        NO_PAREN_FUNCTIONS = parser.Parser.NO_PAREN_FUNCTIONS.copy()
372        NO_PAREN_FUNCTIONS.pop(TokenType.CURRENT_TIME)
373
374        PROPERTY_PARSERS = {
375            **parser.Parser.PROPERTY_PARSERS,
376            "SERDEPROPERTIES": lambda self: exp.SerdeProperties(
377                expressions=self._parse_wrapped_csv(self._parse_property)
378            ),
379        }
380
381        def _parse_transform(self) -> t.Optional[exp.Transform | exp.QueryTransform]:
382            if not self._match(TokenType.L_PAREN, advance=False):
383                self._retreat(self._index - 1)
384                return None
385
386            args = self._parse_wrapped_csv(self._parse_lambda)
387            row_format_before = self._parse_row_format(match_row=True)
388
389            record_writer = None
390            if self._match_text_seq("RECORDWRITER"):
391                record_writer = self._parse_string()
392
393            if not self._match(TokenType.USING):
394                return exp.Transform.from_arg_list(args)
395
396            command_script = self._parse_string()
397
398            self._match(TokenType.ALIAS)
399            schema = self._parse_schema()
400
401            row_format_after = self._parse_row_format(match_row=True)
402            record_reader = None
403            if self._match_text_seq("RECORDREADER"):
404                record_reader = self._parse_string()
405
406            return self.expression(
407                exp.QueryTransform,
408                expressions=args,
409                command_script=command_script,
410                schema=schema,
411                row_format_before=row_format_before,
412                record_writer=record_writer,
413                row_format_after=row_format_after,
414                record_reader=record_reader,
415            )
416
417        def _parse_types(
418            self, check_func: bool = False, schema: bool = False, allow_identifiers: bool = True
419        ) -> t.Optional[exp.Expression]:
420            """
421            Spark (and most likely Hive) treats casts to CHAR(length) and VARCHAR(length) as casts to
422            STRING in all contexts except for schema definitions. For example, this is in Spark v3.4.0:
423
424                spark-sql (default)> select cast(1234 as varchar(2));
425                23/06/06 15:51:18 WARN CharVarcharUtils: The Spark cast operator does not support
426                char/varchar type and simply treats them as string type. Please use string type
427                directly to avoid confusion. Otherwise, you can set spark.sql.legacy.charVarcharAsString
428                to true, so that Spark treat them as string type as same as Spark 3.0 and earlier
429
430                1234
431                Time taken: 4.265 seconds, Fetched 1 row(s)
432
433            This shows that Spark doesn't truncate the value into '12', which is inconsistent with
434            what other dialects (e.g. postgres) do, so we need to drop the length to transpile correctly.
435
436            Reference: https://spark.apache.org/docs/latest/sql-ref-datatypes.html
437            """
438            this = super()._parse_types(
439                check_func=check_func, schema=schema, allow_identifiers=allow_identifiers
440            )
441
442            if this and not schema:
443                return this.transform(
444                    lambda node: (
445                        node.replace(exp.DataType.build("text"))
446                        if isinstance(node, exp.DataType) and node.is_type("char", "varchar")
447                        else node
448                    ),
449                    copy=False,
450                )
451
452            return this
453
454        def _parse_partition_and_order(
455            self,
456        ) -> t.Tuple[t.List[exp.Expression], t.Optional[exp.Expression]]:
457            return (
458                (
459                    self._parse_csv(self._parse_assignment)
460                    if self._match_set({TokenType.PARTITION_BY, TokenType.DISTRIBUTE_BY})
461                    else []
462                ),
463                super()._parse_order(skip_order_token=self._match(TokenType.SORT_BY)),
464            )
465
466        def _parse_parameter(self) -> exp.Parameter:
467            self._match(TokenType.L_BRACE)
468            this = self._parse_identifier() or self._parse_primary_or_var()
469            expression = self._match(TokenType.COLON) and (
470                self._parse_identifier() or self._parse_primary_or_var()
471            )
472            self._match(TokenType.R_BRACE)
473            return self.expression(exp.Parameter, this=this, expression=expression)
474
475        def _to_prop_eq(self, expression: exp.Expression, index: int) -> exp.Expression:
476            if expression.is_star:
477                return expression
478
479            if isinstance(expression, exp.Column):
480                key = expression.this
481            else:
482                key = exp.to_identifier(f"col{index + 1}")
483
484            return self.expression(exp.PropertyEQ, this=key, expression=expression)
485
486    class Generator(generator.Generator):
487        LIMIT_FETCH = "LIMIT"
488        TABLESAMPLE_WITH_METHOD = False
489        JOIN_HINTS = False
490        TABLE_HINTS = False
491        QUERY_HINTS = False
492        INDEX_ON = "ON TABLE"
493        EXTRACT_ALLOWS_QUOTES = False
494        NVL2_SUPPORTED = False
495        LAST_DAY_SUPPORTS_DATE_PART = False
496        JSON_PATH_SINGLE_QUOTE_ESCAPE = True
497        SUPPORTS_TO_NUMBER = False
498        WITH_PROPERTIES_PREFIX = "TBLPROPERTIES"
499        PARSE_JSON_NAME: t.Optional[str] = None
500        PAD_FILL_PATTERN_IS_REQUIRED = True
501        SUPPORTS_MEDIAN = False
502        ARRAY_SIZE_NAME = "SIZE"
503
504        EXPRESSIONS_WITHOUT_NESTED_CTES = {
505            exp.Insert,
506            exp.Select,
507            exp.Subquery,
508            exp.SetOperation,
509        }
510
511        SUPPORTED_JSON_PATH_PARTS = {
512            exp.JSONPathKey,
513            exp.JSONPathRoot,
514            exp.JSONPathSubscript,
515            exp.JSONPathWildcard,
516        }
517
518        TYPE_MAPPING = {
519            **generator.Generator.TYPE_MAPPING,
520            exp.DataType.Type.BIT: "BOOLEAN",
521            exp.DataType.Type.BLOB: "BINARY",
522            exp.DataType.Type.DATETIME: "TIMESTAMP",
523            exp.DataType.Type.ROWVERSION: "BINARY",
524            exp.DataType.Type.TEXT: "STRING",
525            exp.DataType.Type.TIME: "TIMESTAMP",
526            exp.DataType.Type.TIMESTAMPNTZ: "TIMESTAMP",
527            exp.DataType.Type.TIMESTAMPTZ: "TIMESTAMP",
528            exp.DataType.Type.UTINYINT: "SMALLINT",
529            exp.DataType.Type.VARBINARY: "BINARY",
530        }
531
532        TRANSFORMS = {
533            **generator.Generator.TRANSFORMS,
534            exp.Group: transforms.preprocess([transforms.unalias_group]),
535            exp.Property: property_sql,
536            exp.AnyValue: rename_func("FIRST"),
537            exp.ApproxDistinct: approx_count_distinct_sql,
538            exp.ArgMax: arg_max_or_min_no_count("MAX_BY"),
539            exp.ArgMin: arg_max_or_min_no_count("MIN_BY"),
540            exp.ArrayConcat: rename_func("CONCAT"),
541            exp.ArrayToString: lambda self, e: self.func("CONCAT_WS", e.expression, e.this),
542            exp.ArraySort: _array_sort_sql,
543            exp.With: no_recursive_cte_sql,
544            exp.DateAdd: _add_date_sql,
545            exp.DateDiff: _date_diff_sql,
546            exp.DateStrToDate: datestrtodate_sql,
547            exp.DateSub: _add_date_sql,
548            exp.DateToDi: lambda self,
549            e: f"CAST(DATE_FORMAT({self.sql(e, 'this')}, {Hive.DATEINT_FORMAT}) AS INT)",
550            exp.DiToDate: lambda self,
551            e: f"TO_DATE(CAST({self.sql(e, 'this')} AS STRING), {Hive.DATEINT_FORMAT})",
552            exp.StorageHandlerProperty: lambda self, e: f"STORED BY {self.sql(e, 'this')}",
553            exp.FromBase64: rename_func("UNBASE64"),
554            exp.GenerateSeries: sequence_sql,
555            exp.GenerateDateArray: sequence_sql,
556            exp.If: if_sql(),
557            exp.ILike: no_ilike_sql,
558            exp.IntDiv: lambda self, e: self.binary(e, "DIV"),
559            exp.IsNan: rename_func("ISNAN"),
560            exp.JSONExtract: lambda self, e: self.func("GET_JSON_OBJECT", e.this, e.expression),
561            exp.JSONExtractScalar: lambda self, e: self.func(
562                "GET_JSON_OBJECT", e.this, e.expression
563            ),
564            exp.JSONFormat: _json_format_sql,
565            exp.Left: left_to_substring_sql,
566            exp.Map: var_map_sql,
567            exp.Max: max_or_greatest,
568            exp.MD5Digest: lambda self, e: self.func("UNHEX", self.func("MD5", e.this)),
569            exp.Min: min_or_least,
570            exp.MonthsBetween: lambda self, e: self.func("MONTHS_BETWEEN", e.this, e.expression),
571            exp.NotNullColumnConstraint: lambda _, e: (
572                "" if e.args.get("allow_null") else "NOT NULL"
573            ),
574            exp.VarMap: var_map_sql,
575            exp.Create: preprocess(
576                [
577                    remove_unique_constraints,
578                    ctas_with_tmp_tables_to_create_tmp_view,
579                    move_schema_columns_to_partitioned_by,
580                ]
581            ),
582            exp.Quantile: rename_func("PERCENTILE"),
583            exp.ApproxQuantile: rename_func("PERCENTILE_APPROX"),
584            exp.RegexpExtract: regexp_extract_sql,
585            exp.RegexpExtractAll: regexp_extract_sql,
586            exp.RegexpReplace: regexp_replace_sql,
587            exp.RegexpLike: lambda self, e: self.binary(e, "RLIKE"),
588            exp.RegexpSplit: rename_func("SPLIT"),
589            exp.Right: right_to_substring_sql,
590            exp.SchemaCommentProperty: lambda self, e: self.naked_property(e),
591            exp.ArrayUniqueAgg: rename_func("COLLECT_SET"),
592            exp.Split: lambda self, e: self.func(
593                "SPLIT", e.this, self.func("CONCAT", "'\\\\Q'", e.expression, "'\\\\E'")
594            ),
595            exp.Select: transforms.preprocess(
596                [
597                    transforms.eliminate_qualify,
598                    transforms.eliminate_distinct_on,
599                    partial(transforms.unnest_to_explode, unnest_using_arrays_zip=False),
600                    transforms.any_to_exists,
601                ]
602            ),
603            exp.StrPosition: lambda self, e: strposition_sql(
604                self, e, func_name="LOCATE", supports_position=True
605            ),
606            exp.StrToDate: _str_to_date_sql,
607            exp.StrToTime: _str_to_time_sql,
608            exp.StrToUnix: _str_to_unix_sql,
609            exp.StructExtract: struct_extract_sql,
610            exp.StarMap: rename_func("MAP"),
611            exp.Table: transforms.preprocess([transforms.unnest_generate_series]),
612            exp.TimeStrToDate: rename_func("TO_DATE"),
613            exp.TimeStrToTime: timestrtotime_sql,
614            exp.TimeStrToUnix: rename_func("UNIX_TIMESTAMP"),
615            exp.TimestampTrunc: lambda self, e: self.func("TRUNC", e.this, unit_to_str(e)),
616            exp.TimeToUnix: rename_func("UNIX_TIMESTAMP"),
617            exp.ToBase64: rename_func("BASE64"),
618            exp.TsOrDiToDi: lambda self,
619            e: f"CAST(SUBSTR(REPLACE(CAST({self.sql(e, 'this')} AS STRING), '-', ''), 1, 8) AS INT)",
620            exp.TsOrDsAdd: _add_date_sql,
621            exp.TsOrDsDiff: _date_diff_sql,
622            exp.TsOrDsToDate: _to_date_sql,
623            exp.TryCast: no_trycast_sql,
624            exp.Unicode: rename_func("ASCII"),
625            exp.UnixToStr: lambda self, e: self.func(
626                "FROM_UNIXTIME", e.this, time_format("hive")(self, e)
627            ),
628            exp.UnixToTime: _unix_to_time_sql,
629            exp.UnixToTimeStr: rename_func("FROM_UNIXTIME"),
630            exp.Unnest: rename_func("EXPLODE"),
631            exp.PartitionedByProperty: lambda self, e: f"PARTITIONED BY {self.sql(e, 'this')}",
632            exp.NumberToStr: rename_func("FORMAT_NUMBER"),
633            exp.National: lambda self, e: self.national_sql(e, prefix=""),
634            exp.ClusteredColumnConstraint: lambda self,
635            e: f"({self.expressions(e, 'this', indent=False)})",
636            exp.NonClusteredColumnConstraint: lambda self,
637            e: f"({self.expressions(e, 'this', indent=False)})",
638            exp.NotForReplicationColumnConstraint: lambda *_: "",
639            exp.OnProperty: lambda *_: "",
640            exp.PartitionedByBucket: lambda self, e: self.func("BUCKET", e.expression, e.this),
641            exp.PartitionByTruncate: lambda self, e: self.func("TRUNCATE", e.expression, e.this),
642            exp.PrimaryKeyColumnConstraint: lambda *_: "PRIMARY KEY",
643            exp.WeekOfYear: rename_func("WEEKOFYEAR"),
644            exp.DayOfMonth: rename_func("DAYOFMONTH"),
645            exp.DayOfWeek: rename_func("DAYOFWEEK"),
646            exp.Levenshtein: unsupported_args("ins_cost", "del_cost", "sub_cost", "max_dist")(
647                rename_func("LEVENSHTEIN")
648            ),
649        }
650
651        PROPERTIES_LOCATION = {
652            **generator.Generator.PROPERTIES_LOCATION,
653            exp.FileFormatProperty: exp.Properties.Location.POST_SCHEMA,
654            exp.PartitionedByProperty: exp.Properties.Location.POST_SCHEMA,
655            exp.VolatileProperty: exp.Properties.Location.UNSUPPORTED,
656            exp.WithDataProperty: exp.Properties.Location.UNSUPPORTED,
657        }
658
659        TS_OR_DS_EXPRESSIONS: t.Tuple[t.Type[exp.Expression], ...] = (
660            exp.DateDiff,
661            exp.Day,
662            exp.Month,
663            exp.Year,
664        )
665
666        def unnest_sql(self, expression: exp.Unnest) -> str:
667            return rename_func("EXPLODE")(self, expression)
668
669        def _jsonpathkey_sql(self, expression: exp.JSONPathKey) -> str:
670            if isinstance(expression.this, exp.JSONPathWildcard):
671                self.unsupported("Unsupported wildcard in JSONPathKey expression")
672                return ""
673
674            return super()._jsonpathkey_sql(expression)
675
676        def parameter_sql(self, expression: exp.Parameter) -> str:
677            this = self.sql(expression, "this")
678            expression_sql = self.sql(expression, "expression")
679
680            parent = expression.parent
681            this = f"{this}:{expression_sql}" if expression_sql else this
682
683            if isinstance(parent, exp.EQ) and isinstance(parent.parent, exp.SetItem):
684                # We need to produce SET key = value instead of SET ${key} = value
685                return this
686
687            return f"${{{this}}}"
688
689        def schema_sql(self, expression: exp.Schema) -> str:
690            for ordered in expression.find_all(exp.Ordered):
691                if ordered.args.get("desc") is False:
692                    ordered.set("desc", None)
693
694            return super().schema_sql(expression)
695
696        def constraint_sql(self, expression: exp.Constraint) -> str:
697            for prop in list(expression.find_all(exp.Properties)):
698                prop.pop()
699
700            this = self.sql(expression, "this")
701            expressions = self.expressions(expression, sep=" ", flat=True)
702            return f"CONSTRAINT {this} {expressions}"
703
704        def rowformatserdeproperty_sql(self, expression: exp.RowFormatSerdeProperty) -> str:
705            serde_props = self.sql(expression, "serde_properties")
706            serde_props = f" {serde_props}" if serde_props else ""
707            return f"ROW FORMAT SERDE {self.sql(expression, 'this')}{serde_props}"
708
709        def arrayagg_sql(self, expression: exp.ArrayAgg) -> str:
710            return self.func(
711                "COLLECT_LIST",
712                expression.this.this if isinstance(expression.this, exp.Order) else expression.this,
713            )
714
715        def datatype_sql(self, expression: exp.DataType) -> str:
716            if expression.this in self.PARAMETERIZABLE_TEXT_TYPES and (
717                not expression.expressions or expression.expressions[0].name == "MAX"
718            ):
719                expression = exp.DataType.build("text")
720            elif expression.is_type(exp.DataType.Type.TEXT) and expression.expressions:
721                expression.set("this", exp.DataType.Type.VARCHAR)
722            elif expression.this in exp.DataType.TEMPORAL_TYPES:
723                expression = exp.DataType.build(expression.this)
724            elif expression.is_type("float"):
725                size_expression = expression.find(exp.DataTypeParam)
726                if size_expression:
727                    size = int(size_expression.name)
728                    expression = (
729                        exp.DataType.build("float") if size <= 32 else exp.DataType.build("double")
730                    )
731
732            return super().datatype_sql(expression)
733
734        def version_sql(self, expression: exp.Version) -> str:
735            sql = super().version_sql(expression)
736            return sql.replace("FOR ", "", 1)
737
738        def struct_sql(self, expression: exp.Struct) -> str:
739            values = []
740
741            for i, e in enumerate(expression.expressions):
742                if isinstance(e, exp.PropertyEQ):
743                    self.unsupported("Hive does not support named structs.")
744                    values.append(e.expression)
745                else:
746                    values.append(e)
747
748            return self.func("STRUCT", *values)
749
750        def columndef_sql(self, expression: exp.ColumnDef, sep: str = " ") -> str:
751            return super().columndef_sql(
752                expression,
753                sep=(
754                    ": "
755                    if isinstance(expression.parent, exp.DataType)
756                    and expression.parent.is_type("struct")
757                    else sep
758                ),
759            )
760
761        def alterset_sql(self, expression: exp.AlterSet) -> str:
762            exprs = self.expressions(expression, flat=True)
763            exprs = f" {exprs}" if exprs else ""
764            location = self.sql(expression, "location")
765            location = f" LOCATION {location}" if location else ""
766            file_format = self.expressions(expression, key="file_format", flat=True, sep=" ")
767            file_format = f" FILEFORMAT {file_format}" if file_format else ""
768            serde = self.sql(expression, "serde")
769            serde = f" SERDE {serde}" if serde else ""
770            tags = self.expressions(expression, key="tag", flat=True, sep="")
771            tags = f" TAGS {tags}" if tags else ""
772
773            return f"SET{serde}{exprs}{location}{file_format}{tags}"
774
775        def serdeproperties_sql(self, expression: exp.SerdeProperties) -> str:
776            prefix = "WITH " if expression.args.get("with") else ""
777            exprs = self.expressions(expression, flat=True)
778
779            return f"{prefix}SERDEPROPERTIES ({exprs})"
780
781        def exists_sql(self, expression: exp.Exists) -> str:
782            if expression.expression:
783                return self.function_fallback_sql(expression)
784
785            return super().exists_sql(expression)
786
787        def timetostr_sql(self, expression: exp.TimeToStr) -> str:
788            this = expression.this
789            if isinstance(this, exp.TimeStrToTime):
790                this = this.this
791
792            return self.func("DATE_FORMAT", this, self.format_time(expression))
793
794        def fileformatproperty_sql(self, expression: exp.FileFormatProperty) -> str:
795            if isinstance(expression.this, exp.InputOutputFormat):
796                this = self.sql(expression, "this")
797            else:
798                this = expression.name.upper()
799
800            return f"STORED AS {this}"