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