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