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