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.StorageHandlerProperty: lambda self, e: f"STORED BY {self.sql(e, 'this')}",
554            exp.FromBase64: rename_func("UNBASE64"),
555            exp.GenerateSeries: sequence_sql,
556            exp.GenerateDateArray: sequence_sql,
557            exp.If: if_sql(),
558            exp.ILike: no_ilike_sql,
559            exp.IntDiv: lambda self, e: self.binary(e, "DIV"),
560            exp.IsNan: rename_func("ISNAN"),
561            exp.JSONExtract: lambda self, e: self.func("GET_JSON_OBJECT", e.this, e.expression),
562            exp.JSONExtractScalar: lambda self, e: self.func(
563                "GET_JSON_OBJECT", e.this, e.expression
564            ),
565            exp.JSONFormat: _json_format_sql,
566            exp.Left: left_to_substring_sql,
567            exp.Map: var_map_sql,
568            exp.Max: max_or_greatest,
569            exp.MD5Digest: lambda self, e: self.func("UNHEX", self.func("MD5", e.this)),
570            exp.Min: min_or_least,
571            exp.MonthsBetween: lambda self, e: self.func("MONTHS_BETWEEN", e.this, e.expression),
572            exp.NotNullColumnConstraint: lambda _, e: (
573                "" if e.args.get("allow_null") else "NOT NULL"
574            ),
575            exp.VarMap: var_map_sql,
576            exp.Create: preprocess(
577                [
578                    remove_unique_constraints,
579                    ctas_with_tmp_tables_to_create_tmp_view,
580                    move_schema_columns_to_partitioned_by,
581                ]
582            ),
583            exp.Quantile: rename_func("PERCENTILE"),
584            exp.ApproxQuantile: rename_func("PERCENTILE_APPROX"),
585            exp.RegexpExtract: regexp_extract_sql,
586            exp.RegexpExtractAll: regexp_extract_sql,
587            exp.RegexpReplace: regexp_replace_sql,
588            exp.RegexpLike: lambda self, e: self.binary(e, "RLIKE"),
589            exp.RegexpSplit: rename_func("SPLIT"),
590            exp.Right: right_to_substring_sql,
591            exp.SchemaCommentProperty: lambda self, e: self.naked_property(e),
592            exp.ArrayUniqueAgg: rename_func("COLLECT_SET"),
593            exp.Split: lambda self, e: self.func(
594                "SPLIT", e.this, self.func("CONCAT", "'\\\\Q'", e.expression, "'\\\\E'")
595            ),
596            exp.Select: transforms.preprocess(
597                [
598                    transforms.eliminate_qualify,
599                    transforms.eliminate_distinct_on,
600                    partial(transforms.unnest_to_explode, unnest_using_arrays_zip=False),
601                    transforms.any_to_exists,
602                ]
603            ),
604            exp.StrPosition: lambda self, e: strposition_sql(
605                self, e, func_name="LOCATE", supports_position=True
606            ),
607            exp.StrToDate: _str_to_date_sql,
608            exp.StrToTime: _str_to_time_sql,
609            exp.StrToUnix: _str_to_unix_sql,
610            exp.StructExtract: struct_extract_sql,
611            exp.StarMap: rename_func("MAP"),
612            exp.Table: transforms.preprocess([transforms.unnest_generate_series]),
613            exp.TimeStrToDate: rename_func("TO_DATE"),
614            exp.TimeStrToTime: timestrtotime_sql,
615            exp.TimeStrToUnix: rename_func("UNIX_TIMESTAMP"),
616            exp.TimestampTrunc: lambda self, e: self.func("TRUNC", e.this, unit_to_str(e)),
617            exp.TimeToUnix: rename_func("UNIX_TIMESTAMP"),
618            exp.ToBase64: rename_func("BASE64"),
619            exp.TsOrDiToDi: lambda self,
620            e: f"CAST(SUBSTR(REPLACE(CAST({self.sql(e, 'this')} AS STRING), '-', ''), 1, 8) AS INT)",
621            exp.TsOrDsAdd: _add_date_sql,
622            exp.TsOrDsDiff: _date_diff_sql,
623            exp.TsOrDsToDate: _to_date_sql,
624            exp.TryCast: no_trycast_sql,
625            exp.Unicode: rename_func("ASCII"),
626            exp.UnixToStr: lambda self, e: self.func(
627                "FROM_UNIXTIME", e.this, time_format("hive")(self, e)
628            ),
629            exp.UnixToTime: _unix_to_time_sql,
630            exp.UnixToTimeStr: rename_func("FROM_UNIXTIME"),
631            exp.Unnest: rename_func("EXPLODE"),
632            exp.PartitionedByProperty: lambda self, e: f"PARTITIONED BY {self.sql(e, 'this')}",
633            exp.NumberToStr: rename_func("FORMAT_NUMBER"),
634            exp.National: lambda self, e: self.national_sql(e, prefix=""),
635            exp.ClusteredColumnConstraint: lambda self,
636            e: f"({self.expressions(e, 'this', indent=False)})",
637            exp.NonClusteredColumnConstraint: lambda self,
638            e: f"({self.expressions(e, 'this', indent=False)})",
639            exp.NotForReplicationColumnConstraint: lambda *_: "",
640            exp.OnProperty: lambda *_: "",
641            exp.PartitionedByBucket: lambda self, e: self.func("BUCKET", e.expression, e.this),
642            exp.PartitionByTruncate: lambda self, e: self.func("TRUNCATE", e.expression, e.this),
643            exp.PrimaryKeyColumnConstraint: lambda *_: "PRIMARY KEY",
644            exp.WeekOfYear: rename_func("WEEKOFYEAR"),
645            exp.DayOfMonth: rename_func("DAYOFMONTH"),
646            exp.DayOfWeek: rename_func("DAYOFWEEK"),
647            exp.Levenshtein: unsupported_args("ins_cost", "del_cost", "sub_cost", "max_dist")(
648                rename_func("LEVENSHTEIN")
649            ),
650        }
651
652        PROPERTIES_LOCATION = {
653            **generator.Generator.PROPERTIES_LOCATION,
654            exp.FileFormatProperty: exp.Properties.Location.POST_SCHEMA,
655            exp.PartitionedByProperty: exp.Properties.Location.POST_SCHEMA,
656            exp.VolatileProperty: exp.Properties.Location.UNSUPPORTED,
657            exp.WithDataProperty: exp.Properties.Location.UNSUPPORTED,
658        }
659
660        def unnest_sql(self, expression: exp.Unnest) -> str:
661            return rename_func("EXPLODE")(self, expression)
662
663        def _jsonpathkey_sql(self, expression: exp.JSONPathKey) -> str:
664            if isinstance(expression.this, exp.JSONPathWildcard):
665                self.unsupported("Unsupported wildcard in JSONPathKey expression")
666                return ""
667
668            return super()._jsonpathkey_sql(expression)
669
670        def parameter_sql(self, expression: exp.Parameter) -> str:
671            this = self.sql(expression, "this")
672            expression_sql = self.sql(expression, "expression")
673
674            parent = expression.parent
675            this = f"{this}:{expression_sql}" if expression_sql else this
676
677            if isinstance(parent, exp.EQ) and isinstance(parent.parent, exp.SetItem):
678                # We need to produce SET key = value instead of SET ${key} = value
679                return this
680
681            return f"${{{this}}}"
682
683        def schema_sql(self, expression: exp.Schema) -> str:
684            for ordered in expression.find_all(exp.Ordered):
685                if ordered.args.get("desc") is False:
686                    ordered.set("desc", None)
687
688            return super().schema_sql(expression)
689
690        def constraint_sql(self, expression: exp.Constraint) -> str:
691            for prop in list(expression.find_all(exp.Properties)):
692                prop.pop()
693
694            this = self.sql(expression, "this")
695            expressions = self.expressions(expression, sep=" ", flat=True)
696            return f"CONSTRAINT {this} {expressions}"
697
698        def rowformatserdeproperty_sql(self, expression: exp.RowFormatSerdeProperty) -> str:
699            serde_props = self.sql(expression, "serde_properties")
700            serde_props = f" {serde_props}" if serde_props else ""
701            return f"ROW FORMAT SERDE {self.sql(expression, 'this')}{serde_props}"
702
703        def arrayagg_sql(self, expression: exp.ArrayAgg) -> str:
704            return self.func(
705                "COLLECT_LIST",
706                expression.this.this if isinstance(expression.this, exp.Order) else expression.this,
707            )
708
709        def datatype_sql(self, expression: exp.DataType) -> str:
710            if expression.this in self.PARAMETERIZABLE_TEXT_TYPES and (
711                not expression.expressions or expression.expressions[0].name == "MAX"
712            ):
713                expression = exp.DataType.build("text")
714            elif expression.is_type(exp.DataType.Type.TEXT) and expression.expressions:
715                expression.set("this", exp.DataType.Type.VARCHAR)
716            elif expression.this in exp.DataType.TEMPORAL_TYPES:
717                expression = exp.DataType.build(expression.this)
718            elif expression.is_type("float"):
719                size_expression = expression.find(exp.DataTypeParam)
720                if size_expression:
721                    size = int(size_expression.name)
722                    expression = (
723                        exp.DataType.build("float") if size <= 32 else exp.DataType.build("double")
724                    )
725
726            return super().datatype_sql(expression)
727
728        def version_sql(self, expression: exp.Version) -> str:
729            sql = super().version_sql(expression)
730            return sql.replace("FOR ", "", 1)
731
732        def struct_sql(self, expression: exp.Struct) -> str:
733            values = []
734
735            for i, e in enumerate(expression.expressions):
736                if isinstance(e, exp.PropertyEQ):
737                    self.unsupported("Hive does not support named structs.")
738                    values.append(e.expression)
739                else:
740                    values.append(e)
741
742            return self.func("STRUCT", *values)
743
744        def columndef_sql(self, expression: exp.ColumnDef, sep: str = " ") -> str:
745            return super().columndef_sql(
746                expression,
747                sep=(
748                    ": "
749                    if isinstance(expression.parent, exp.DataType)
750                    and expression.parent.is_type("struct")
751                    else sep
752                ),
753            )
754
755        def alterset_sql(self, expression: exp.AlterSet) -> str:
756            exprs = self.expressions(expression, flat=True)
757            exprs = f" {exprs}" if exprs else ""
758            location = self.sql(expression, "location")
759            location = f" LOCATION {location}" if location else ""
760            file_format = self.expressions(expression, key="file_format", flat=True, sep=" ")
761            file_format = f" FILEFORMAT {file_format}" if file_format else ""
762            serde = self.sql(expression, "serde")
763            serde = f" SERDE {serde}" if serde else ""
764            tags = self.expressions(expression, key="tag", flat=True, sep="")
765            tags = f" TAGS {tags}" if tags else ""
766
767            return f"SET{serde}{exprs}{location}{file_format}{tags}"
768
769        def serdeproperties_sql(self, expression: exp.SerdeProperties) -> str:
770            prefix = "WITH " if expression.args.get("with") else ""
771            exprs = self.expressions(expression, flat=True)
772
773            return f"{prefix}SERDEPROPERTIES ({exprs})"
774
775        def exists_sql(self, expression: exp.Exists) -> str:
776            if expression.expression:
777                return self.function_fallback_sql(expression)
778
779            return super().exists_sql(expression)
780
781        def timetostr_sql(self, expression: exp.TimeToStr) -> str:
782            this = expression.this
783            if isinstance(this, exp.TimeStrToTime):
784                this = this.this
785
786            return self.func("DATE_FORMAT", this, self.format_time(expression))
787
788        def fileformatproperty_sql(self, expression: exp.FileFormatProperty) -> str:
789            if isinstance(expression.this, exp.InputOutputFormat):
790                this = self.sql(expression, "this")
791            else:
792                this = expression.name.upper()
793
794            return f"STORED AS {this}"