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