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