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