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