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