sqlglot.dialects.hive

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