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