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