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