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
 52# (FuncType, Multiplier)
 53DATE_DELTA_INTERVAL = {
 54    "YEAR": ("ADD_MONTHS", 12),
 55    "MONTH": ("ADD_MONTHS", 1),
 56    "QUARTER": ("ADD_MONTHS", 3),
 57    "WEEK": ("DATE_ADD", 7),
 58    "DAY": ("DATE_ADD", 1),
 59}
 60
 61TIME_DIFF_FACTOR = {
 62    "MILLISECOND": " * 1000",
 63    "SECOND": "",
 64    "MINUTE": " / 60",
 65    "HOUR": " / 3600",
 66}
 67
 68DIFF_MONTH_SWITCH = ("YEAR", "QUARTER", "MONTH")
 69
 70
 71def _add_date_sql(self: Hive.Generator, expression: DATE_ADD_OR_SUB) -> str:
 72    if isinstance(expression, exp.TsOrDsAdd) and not expression.unit:
 73        return self.func("DATE_ADD", expression.this, expression.expression)
 74
 75    unit = expression.text("unit").upper()
 76    func, multiplier = DATE_DELTA_INTERVAL.get(unit, ("DATE_ADD", 1))
 77
 78    if isinstance(expression, exp.DateSub):
 79        multiplier *= -1
 80
 81    increment = expression.expression
 82    if isinstance(increment, exp.Literal):
 83        value = increment.to_py() if increment.is_number else int(increment.name)
 84        increment = exp.Literal.number(value * multiplier)
 85    elif multiplier != 1:
 86        increment *= exp.Literal.number(multiplier)
 87
 88    return self.func(func, expression.this, increment)
 89
 90
 91def _date_diff_sql(self: Hive.Generator, expression: exp.DateDiff | exp.TsOrDsDiff) -> str:
 92    unit = expression.text("unit").upper()
 93
 94    factor = TIME_DIFF_FACTOR.get(unit)
 95    if factor is not None:
 96        left = self.sql(expression, "this")
 97        right = self.sql(expression, "expression")
 98        sec_diff = f"UNIX_TIMESTAMP({left}) - UNIX_TIMESTAMP({right})"
 99        return f"({sec_diff}){factor}" if factor else sec_diff
100
101    months_between = unit in DIFF_MONTH_SWITCH
102    sql_func = "MONTHS_BETWEEN" if months_between else "DATEDIFF"
103    _, multiplier = DATE_DELTA_INTERVAL.get(unit, ("", 1))
104    multiplier_sql = f" / {multiplier}" if multiplier > 1 else ""
105    diff_sql = f"{sql_func}({self.format_args(expression.this, expression.expression)})"
106
107    if months_between or multiplier_sql:
108        # MONTHS_BETWEEN returns a float, so we need to truncate the fractional part.
109        # For the same reason, we want to truncate if there's a divisor present.
110        diff_sql = f"CAST({diff_sql}{multiplier_sql} AS INT)"
111
112    return diff_sql
113
114
115def _json_format_sql(self: Hive.Generator, expression: exp.JSONFormat) -> str:
116    this = expression.this
117
118    if is_parse_json(this):
119        if this.this.is_string:
120            # Since FROM_JSON requires a nested type, we always wrap the json string with
121            # an array to ensure that "naked" strings like "'a'" will be handled correctly
122            wrapped_json = exp.Literal.string(f"[{this.this.name}]")
123
124            from_json = self.func(
125                "FROM_JSON", wrapped_json, self.func("SCHEMA_OF_JSON", wrapped_json)
126            )
127            to_json = self.func("TO_JSON", from_json)
128
129            # This strips the [, ] delimiters of the dummy array printed by TO_JSON
130            return self.func("REGEXP_EXTRACT", to_json, "'^.(.*).$'", "1")
131        return self.sql(this)
132
133    return self.func("TO_JSON", this, expression.args.get("options"))
134
135
136@generator.unsupported_args(("expression", "Hive's SORT_ARRAY does not support a comparator."))
137def _array_sort_sql(self: Hive.Generator, expression: exp.ArraySort) -> str:
138    return self.func("SORT_ARRAY", expression.this)
139
140
141def _str_to_unix_sql(self: Hive.Generator, expression: exp.StrToUnix) -> str:
142    return self.func("UNIX_TIMESTAMP", expression.this, time_format("hive")(self, expression))
143
144
145def _unix_to_time_sql(self: Hive.Generator, expression: exp.UnixToTime) -> str:
146    timestamp = self.sql(expression, "this")
147    scale = expression.args.get("scale")
148    if scale in (None, exp.UnixToTime.SECONDS):
149        return rename_func("FROM_UNIXTIME")(self, expression)
150
151    return f"FROM_UNIXTIME({timestamp} / POW(10, {scale}))"
152
153
154def _str_to_date_sql(self: Hive.Generator, expression: exp.StrToDate) -> str:
155    this = self.sql(expression, "this")
156    time_format = self.format_time(expression)
157    if time_format not in (Hive.TIME_FORMAT, Hive.DATE_FORMAT):
158        this = f"FROM_UNIXTIME(UNIX_TIMESTAMP({this}, {time_format}))"
159    return f"CAST({this} AS DATE)"
160
161
162def _str_to_time_sql(self: Hive.Generator, expression: exp.StrToTime) -> str:
163    this = self.sql(expression, "this")
164    time_format = self.format_time(expression)
165    if time_format not in (Hive.TIME_FORMAT, Hive.DATE_FORMAT):
166        this = f"FROM_UNIXTIME(UNIX_TIMESTAMP({this}, {time_format}))"
167    return f"CAST({this} AS TIMESTAMP)"
168
169
170def _to_date_sql(self: Hive.Generator, expression: exp.TsOrDsToDate) -> str:
171    time_format = self.format_time(expression)
172    if time_format and time_format not in (Hive.TIME_FORMAT, Hive.DATE_FORMAT):
173        return self.func("TO_DATE", expression.this, time_format)
174
175    if isinstance(expression.parent, self.TS_OR_DS_EXPRESSIONS):
176        return self.sql(expression, "this")
177
178    return self.func("TO_DATE", expression.this)
179
180
181def _build_with_ignore_nulls(
182    exp_class: t.Type[exp.Expression],
183) -> t.Callable[[t.List[exp.Expression]], exp.Expression]:
184    def _parse(args: t.List[exp.Expression]) -> exp.Expression:
185        this = exp_class(this=seq_get(args, 0))
186        if seq_get(args, 1) == exp.true():
187            return exp.IgnoreNulls(this=this)
188        return this
189
190    return _parse
191
192
193def _build_to_date(args: t.List) -> exp.TsOrDsToDate:
194    expr = build_formatted_time(exp.TsOrDsToDate, "hive")(args)
195    expr.set("safe", True)
196    return expr
197
198
199def _build_date_add(args: t.List) -> exp.TsOrDsAdd:
200    expression = seq_get(args, 1)
201    if expression:
202        expression = expression * -1
203
204    return exp.TsOrDsAdd(
205        this=seq_get(args, 0), expression=expression, unit=exp.Literal.string("DAY")
206    )
207
208
209class Hive(Dialect):
210    ALIAS_POST_TABLESAMPLE = True
211    IDENTIFIERS_CAN_START_WITH_DIGIT = True
212    SUPPORTS_USER_DEFINED_TYPES = False
213    SAFE_DIVISION = True
214    ARRAY_AGG_INCLUDES_NULLS = None
215    REGEXP_EXTRACT_DEFAULT_GROUP = 1
216    ALTER_TABLE_SUPPORTS_CASCADE = True
217
218    # https://spark.apache.org/docs/latest/sql-ref-identifier.html#description
219    NORMALIZATION_STRATEGY = NormalizationStrategy.CASE_INSENSITIVE
220
221    EXPRESSION_METADATA = EXPRESSION_METADATA.copy()
222
223    # https://cwiki.apache.org/confluence/pages/viewpage.action?pageId=27362046#LanguageManualUDF-StringFunctions
224    # https://github.com/apache/hive/blob/master/ql/src/java/org/apache/hadoop/hive/ql/exec/Utilities.java#L266-L269
225    INITCAP_DEFAULT_DELIMITER_CHARS = " \t\n\r\f\u000b\u001c\u001d\u001e\u001f"
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.Array: transforms.preprocess([transforms.inherit_struct_field_names]),
580            exp.ArrayConcat: rename_func("CONCAT"),
581            exp.ArrayToString: lambda self, e: self.func("CONCAT_WS", e.expression, e.this),
582            exp.ArraySort: _array_sort_sql,
583            exp.With: no_recursive_cte_sql,
584            exp.DateAdd: _add_date_sql,
585            exp.DateDiff: _date_diff_sql,
586            exp.DateStrToDate: datestrtodate_sql,
587            exp.DateSub: _add_date_sql,
588            exp.DateToDi: lambda self,
589            e: f"CAST(DATE_FORMAT({self.sql(e, 'this')}, {Hive.DATEINT_FORMAT}) AS INT)",
590            exp.DiToDate: lambda self,
591            e: f"TO_DATE(CAST({self.sql(e, 'this')} AS STRING), {Hive.DATEINT_FORMAT})",
592            exp.StorageHandlerProperty: lambda self, e: f"STORED BY {self.sql(e, 'this')}",
593            exp.FromBase64: rename_func("UNBASE64"),
594            exp.GenerateSeries: sequence_sql,
595            exp.GenerateDateArray: sequence_sql,
596            exp.If: if_sql(),
597            exp.ILike: no_ilike_sql,
598            exp.IntDiv: lambda self, e: self.binary(e, "DIV"),
599            exp.IsNan: rename_func("ISNAN"),
600            exp.JSONExtract: lambda self, e: self.func("GET_JSON_OBJECT", e.this, e.expression),
601            exp.JSONExtractScalar: lambda self, e: self.func(
602                "GET_JSON_OBJECT", e.this, e.expression
603            ),
604            exp.JSONFormat: _json_format_sql,
605            exp.Left: left_to_substring_sql,
606            exp.Map: var_map_sql,
607            exp.Max: max_or_greatest,
608            exp.MD5Digest: lambda self, e: self.func("UNHEX", self.func("MD5", e.this)),
609            exp.Min: min_or_least,
610            exp.MonthsBetween: lambda self, e: self.func("MONTHS_BETWEEN", e.this, e.expression),
611            exp.NotNullColumnConstraint: lambda _, e: (
612                "" if e.args.get("allow_null") else "NOT NULL"
613            ),
614            exp.VarMap: var_map_sql,
615            exp.Create: preprocess(
616                [
617                    remove_unique_constraints,
618                    ctas_with_tmp_tables_to_create_tmp_view,
619                    move_schema_columns_to_partitioned_by,
620                ]
621            ),
622            exp.Quantile: rename_func("PERCENTILE"),
623            exp.ApproxQuantile: rename_func("PERCENTILE_APPROX"),
624            exp.RegexpExtract: regexp_extract_sql,
625            exp.RegexpExtractAll: regexp_extract_sql,
626            exp.RegexpReplace: regexp_replace_sql,
627            exp.RegexpLike: lambda self, e: self.binary(e, "RLIKE"),
628            exp.RegexpSplit: rename_func("SPLIT"),
629            exp.Right: right_to_substring_sql,
630            exp.SchemaCommentProperty: lambda self, e: self.naked_property(e),
631            exp.ArrayUniqueAgg: rename_func("COLLECT_SET"),
632            exp.Split: lambda self, e: self.func(
633                "SPLIT", e.this, self.func("CONCAT", "'\\\\Q'", e.expression, "'\\\\E'")
634            ),
635            exp.Select: transforms.preprocess(
636                [
637                    transforms.eliminate_qualify,
638                    transforms.eliminate_distinct_on,
639                    partial(transforms.unnest_to_explode, unnest_using_arrays_zip=False),
640                    transforms.any_to_exists,
641                ]
642            ),
643            exp.StrPosition: lambda self, e: strposition_sql(
644                self, e, func_name="LOCATE", supports_position=True
645            ),
646            exp.StrToDate: _str_to_date_sql,
647            exp.StrToTime: _str_to_time_sql,
648            exp.StrToUnix: _str_to_unix_sql,
649            exp.StructExtract: struct_extract_sql,
650            exp.StarMap: rename_func("MAP"),
651            exp.Table: transforms.preprocess([transforms.unnest_generate_series]),
652            exp.TimeStrToDate: rename_func("TO_DATE"),
653            exp.TimeStrToTime: timestrtotime_sql,
654            exp.TimeStrToUnix: rename_func("UNIX_TIMESTAMP"),
655            exp.TimestampTrunc: lambda self, e: self.func("TRUNC", e.this, unit_to_str(e)),
656            exp.TimeToUnix: rename_func("UNIX_TIMESTAMP"),
657            exp.ToBase64: rename_func("BASE64"),
658            exp.TsOrDiToDi: lambda self,
659            e: f"CAST(SUBSTR(REPLACE(CAST({self.sql(e, 'this')} AS STRING), '-', ''), 1, 8) AS INT)",
660            exp.TsOrDsAdd: _add_date_sql,
661            exp.TsOrDsDiff: _date_diff_sql,
662            exp.TsOrDsToDate: _to_date_sql,
663            exp.TryCast: no_trycast_sql,
664            exp.Trim: trim_sql,
665            exp.Unicode: rename_func("ASCII"),
666            exp.UnixToStr: lambda self, e: self.func(
667                "FROM_UNIXTIME", e.this, time_format("hive")(self, e)
668            ),
669            exp.UnixToTime: _unix_to_time_sql,
670            exp.UnixToTimeStr: rename_func("FROM_UNIXTIME"),
671            exp.Unnest: rename_func("EXPLODE"),
672            exp.PartitionedByProperty: lambda self, e: f"PARTITIONED BY {self.sql(e, 'this')}",
673            exp.NumberToStr: rename_func("FORMAT_NUMBER"),
674            exp.National: lambda self, e: self.national_sql(e, prefix=""),
675            exp.ClusteredColumnConstraint: lambda self,
676            e: f"({self.expressions(e, 'this', indent=False)})",
677            exp.NonClusteredColumnConstraint: lambda self,
678            e: f"({self.expressions(e, 'this', indent=False)})",
679            exp.NotForReplicationColumnConstraint: lambda *_: "",
680            exp.OnProperty: lambda *_: "",
681            exp.PartitionedByBucket: lambda self, e: self.func("BUCKET", e.expression, e.this),
682            exp.PartitionByTruncate: lambda self, e: self.func("TRUNCATE", e.expression, e.this),
683            exp.PrimaryKeyColumnConstraint: lambda *_: "PRIMARY KEY",
684            exp.WeekOfYear: rename_func("WEEKOFYEAR"),
685            exp.DayOfMonth: rename_func("DAYOFMONTH"),
686            exp.DayOfWeek: rename_func("DAYOFWEEK"),
687            exp.Levenshtein: unsupported_args("ins_cost", "del_cost", "sub_cost", "max_dist")(
688                rename_func("LEVENSHTEIN")
689            ),
690        }
691
692        PROPERTIES_LOCATION = {
693            **generator.Generator.PROPERTIES_LOCATION,
694            exp.FileFormatProperty: exp.Properties.Location.POST_SCHEMA,
695            exp.PartitionedByProperty: exp.Properties.Location.POST_SCHEMA,
696            exp.VolatileProperty: exp.Properties.Location.UNSUPPORTED,
697            exp.WithDataProperty: exp.Properties.Location.UNSUPPORTED,
698        }
699
700        TS_OR_DS_EXPRESSIONS: t.Tuple[t.Type[exp.Expression], ...] = (
701            exp.DateDiff,
702            exp.Day,
703            exp.Month,
704            exp.Year,
705        )
706
707        def unnest_sql(self, expression: exp.Unnest) -> str:
708            return rename_func("EXPLODE")(self, expression)
709
710        def _jsonpathkey_sql(self, expression: exp.JSONPathKey) -> str:
711            if isinstance(expression.this, exp.JSONPathWildcard):
712                self.unsupported("Unsupported wildcard in JSONPathKey expression")
713                return ""
714
715            return super()._jsonpathkey_sql(expression)
716
717        def parameter_sql(self, expression: exp.Parameter) -> str:
718            this = self.sql(expression, "this")
719            expression_sql = self.sql(expression, "expression")
720
721            parent = expression.parent
722            this = f"{this}:{expression_sql}" if expression_sql else this
723
724            if isinstance(parent, exp.EQ) and isinstance(parent.parent, exp.SetItem):
725                # We need to produce SET key = value instead of SET ${key} = value
726                return this
727
728            return f"${{{this}}}"
729
730        def schema_sql(self, expression: exp.Schema) -> str:
731            for ordered in expression.find_all(exp.Ordered):
732                if ordered.args.get("desc") is False:
733                    ordered.set("desc", None)
734
735            return super().schema_sql(expression)
736
737        def constraint_sql(self, expression: exp.Constraint) -> str:
738            for prop in list(expression.find_all(exp.Properties)):
739                prop.pop()
740
741            this = self.sql(expression, "this")
742            expressions = self.expressions(expression, sep=" ", flat=True)
743            return f"CONSTRAINT {this} {expressions}"
744
745        def rowformatserdeproperty_sql(self, expression: exp.RowFormatSerdeProperty) -> str:
746            serde_props = self.sql(expression, "serde_properties")
747            serde_props = f" {serde_props}" if serde_props else ""
748            return f"ROW FORMAT SERDE {self.sql(expression, 'this')}{serde_props}"
749
750        def arrayagg_sql(self, expression: exp.ArrayAgg) -> str:
751            return self.func(
752                "COLLECT_LIST",
753                expression.this.this if isinstance(expression.this, exp.Order) else expression.this,
754            )
755
756        def datatype_sql(self, expression: exp.DataType) -> str:
757            if expression.this in self.PARAMETERIZABLE_TEXT_TYPES and (
758                not expression.expressions or expression.expressions[0].name == "MAX"
759            ):
760                expression = exp.DataType.build("text")
761            elif expression.is_type(exp.DataType.Type.TEXT) and expression.expressions:
762                expression.set("this", exp.DataType.Type.VARCHAR)
763            elif expression.this in exp.DataType.TEMPORAL_TYPES:
764                expression = exp.DataType.build(expression.this)
765            elif expression.is_type("float"):
766                size_expression = expression.find(exp.DataTypeParam)
767                if size_expression:
768                    size = int(size_expression.name)
769                    expression = (
770                        exp.DataType.build("float") if size <= 32 else exp.DataType.build("double")
771                    )
772
773            return super().datatype_sql(expression)
774
775        def version_sql(self, expression: exp.Version) -> str:
776            sql = super().version_sql(expression)
777            return sql.replace("FOR ", "", 1)
778
779        def struct_sql(self, expression: exp.Struct) -> str:
780            values = []
781
782            for i, e in enumerate(expression.expressions):
783                if isinstance(e, exp.PropertyEQ):
784                    self.unsupported("Hive does not support named structs.")
785                    values.append(e.expression)
786                else:
787                    values.append(e)
788
789            return self.func("STRUCT", *values)
790
791        def columndef_sql(self, expression: exp.ColumnDef, sep: str = " ") -> str:
792            return super().columndef_sql(
793                expression,
794                sep=(
795                    ": "
796                    if isinstance(expression.parent, exp.DataType)
797                    and expression.parent.is_type("struct")
798                    else sep
799                ),
800            )
801
802        def altercolumn_sql(self, expression: exp.AlterColumn) -> str:
803            this = self.sql(expression, "this")
804            new_name = self.sql(expression, "rename_to") or this
805            dtype = self.sql(expression, "dtype")
806            comment = (
807                f" COMMENT {self.sql(expression, 'comment')}"
808                if self.sql(expression, "comment")
809                else ""
810            )
811            default = self.sql(expression, "default")
812            visible = expression.args.get("visible")
813            allow_null = expression.args.get("allow_null")
814            drop = expression.args.get("drop")
815
816            if any([default, drop, visible, allow_null, drop]):
817                self.unsupported("Unsupported CHANGE COLUMN syntax")
818
819            if not dtype:
820                self.unsupported("CHANGE COLUMN without a type is not supported")
821
822            return f"CHANGE COLUMN {this} {new_name} {dtype}{comment}"
823
824        def renamecolumn_sql(self, expression: exp.RenameColumn) -> str:
825            self.unsupported("Cannot rename columns without data type defined in Hive")
826            return ""
827
828        def alterset_sql(self, expression: exp.AlterSet) -> str:
829            exprs = self.expressions(expression, flat=True)
830            exprs = f" {exprs}" if exprs else ""
831            location = self.sql(expression, "location")
832            location = f" LOCATION {location}" if location else ""
833            file_format = self.expressions(expression, key="file_format", flat=True, sep=" ")
834            file_format = f" FILEFORMAT {file_format}" if file_format else ""
835            serde = self.sql(expression, "serde")
836            serde = f" SERDE {serde}" if serde else ""
837            tags = self.expressions(expression, key="tag", flat=True, sep="")
838            tags = f" TAGS {tags}" if tags else ""
839
840            return f"SET{serde}{exprs}{location}{file_format}{tags}"
841
842        def serdeproperties_sql(self, expression: exp.SerdeProperties) -> str:
843            prefix = "WITH " if expression.args.get("with_") else ""
844            exprs = self.expressions(expression, flat=True)
845
846            return f"{prefix}SERDEPROPERTIES ({exprs})"
847
848        def exists_sql(self, expression: exp.Exists) -> str:
849            if expression.expression:
850                return self.function_fallback_sql(expression)
851
852            return super().exists_sql(expression)
853
854        def timetostr_sql(self, expression: exp.TimeToStr) -> str:
855            this = expression.this
856            if isinstance(this, exp.TimeStrToTime):
857                this = this.this
858
859            return self.func("DATE_FORMAT", this, self.format_time(expression))
860
861        def fileformatproperty_sql(self, expression: exp.FileFormatProperty) -> str:
862            if isinstance(expression.this, exp.InputOutputFormat):
863                this = self.sql(expression, "this")
864            else:
865                this = expression.name.upper()
866
867            return f"STORED AS {this}"