sqlglot.dialects.presto
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 NormalizationStrategy, 9 binary_from_function, 10 bool_xor_sql, 11 date_trunc_to_time, 12 datestrtodate_sql, 13 encode_decode_sql, 14 build_formatted_time, 15 if_sql, 16 left_to_substring_sql, 17 no_ilike_sql, 18 no_pivot_sql, 19 no_safe_divide_sql, 20 no_timestamp_sql, 21 regexp_extract_sql, 22 rename_func, 23 right_to_substring_sql, 24 sha256_sql, 25 struct_extract_sql, 26 str_position_sql, 27 timestamptrunc_sql, 28 timestrtotime_sql, 29 ts_or_ds_add_cast, 30 unit_to_str, 31 sequence_sql, 32 build_regexp_extract, 33) 34from sqlglot.dialects.hive import Hive 35from sqlglot.dialects.mysql import MySQL 36from sqlglot.helper import apply_index_offset, seq_get 37from sqlglot.tokens import TokenType 38from sqlglot.transforms import unqualify_columns 39 40DATE_ADD_OR_SUB = t.Union[exp.DateAdd, exp.TimestampAdd, exp.DateSub] 41 42 43def _explode_to_unnest_sql(self: Presto.Generator, expression: exp.Lateral) -> str: 44 if isinstance(expression.this, exp.Explode): 45 return self.sql( 46 exp.Join( 47 this=exp.Unnest( 48 expressions=[expression.this.this], 49 alias=expression.args.get("alias"), 50 offset=isinstance(expression.this, exp.Posexplode), 51 ), 52 kind="cross", 53 ) 54 ) 55 return self.lateral_sql(expression) 56 57 58def _initcap_sql(self: Presto.Generator, expression: exp.Initcap) -> str: 59 regex = r"(\w)(\w*)" 60 return f"REGEXP_REPLACE({self.sql(expression, 'this')}, '{regex}', x -> UPPER(x[1]) || LOWER(x[2]))" 61 62 63def _no_sort_array(self: Presto.Generator, expression: exp.SortArray) -> str: 64 if expression.args.get("asc") == exp.false(): 65 comparator = "(a, b) -> CASE WHEN a < b THEN 1 WHEN a > b THEN -1 ELSE 0 END" 66 else: 67 comparator = None 68 return self.func("ARRAY_SORT", expression.this, comparator) 69 70 71def _schema_sql(self: Presto.Generator, expression: exp.Schema) -> str: 72 if isinstance(expression.parent, exp.Property): 73 columns = ", ".join(f"'{c.name}'" for c in expression.expressions) 74 return f"ARRAY[{columns}]" 75 76 if expression.parent: 77 for schema in expression.parent.find_all(exp.Schema): 78 column_defs = schema.find_all(exp.ColumnDef) 79 if column_defs and isinstance(schema.parent, exp.Property): 80 expression.expressions.extend(column_defs) 81 82 return self.schema_sql(expression) 83 84 85def _quantile_sql(self: Presto.Generator, expression: exp.Quantile) -> str: 86 self.unsupported("Presto does not support exact quantiles") 87 return self.func("APPROX_PERCENTILE", expression.this, expression.args.get("quantile")) 88 89 90def _str_to_time_sql( 91 self: Presto.Generator, expression: exp.StrToDate | exp.StrToTime | exp.TsOrDsToDate 92) -> str: 93 return self.func("DATE_PARSE", expression.this, self.format_time(expression)) 94 95 96def _ts_or_ds_to_date_sql(self: Presto.Generator, expression: exp.TsOrDsToDate) -> str: 97 time_format = self.format_time(expression) 98 if time_format and time_format not in (Presto.TIME_FORMAT, Presto.DATE_FORMAT): 99 return self.sql(exp.cast(_str_to_time_sql(self, expression), exp.DataType.Type.DATE)) 100 return self.sql( 101 exp.cast(exp.cast(expression.this, exp.DataType.Type.TIMESTAMP), exp.DataType.Type.DATE) 102 ) 103 104 105def _ts_or_ds_add_sql(self: Presto.Generator, expression: exp.TsOrDsAdd) -> str: 106 expression = ts_or_ds_add_cast(expression) 107 unit = unit_to_str(expression) 108 return self.func("DATE_ADD", unit, expression.expression, expression.this) 109 110 111def _ts_or_ds_diff_sql(self: Presto.Generator, expression: exp.TsOrDsDiff) -> str: 112 this = exp.cast(expression.this, exp.DataType.Type.TIMESTAMP) 113 expr = exp.cast(expression.expression, exp.DataType.Type.TIMESTAMP) 114 unit = unit_to_str(expression) 115 return self.func("DATE_DIFF", unit, expr, this) 116 117 118def _build_approx_percentile(args: t.List) -> exp.Expression: 119 if len(args) == 4: 120 return exp.ApproxQuantile( 121 this=seq_get(args, 0), 122 weight=seq_get(args, 1), 123 quantile=seq_get(args, 2), 124 accuracy=seq_get(args, 3), 125 ) 126 if len(args) == 3: 127 return exp.ApproxQuantile( 128 this=seq_get(args, 0), quantile=seq_get(args, 1), accuracy=seq_get(args, 2) 129 ) 130 return exp.ApproxQuantile.from_arg_list(args) 131 132 133def _build_from_unixtime(args: t.List) -> exp.Expression: 134 if len(args) == 3: 135 return exp.UnixToTime( 136 this=seq_get(args, 0), 137 hours=seq_get(args, 1), 138 minutes=seq_get(args, 2), 139 ) 140 if len(args) == 2: 141 return exp.UnixToTime(this=seq_get(args, 0), zone=seq_get(args, 1)) 142 143 return exp.UnixToTime.from_arg_list(args) 144 145 146def _first_last_sql(self: Presto.Generator, expression: exp.Func) -> str: 147 """ 148 Trino doesn't support FIRST / LAST as functions, but they're valid in the context 149 of MATCH_RECOGNIZE, so we need to preserve them in that case. In all other cases 150 they're converted into an ARBITRARY call. 151 152 Reference: https://trino.io/docs/current/sql/match-recognize.html#logical-navigation-functions 153 """ 154 if isinstance(expression.find_ancestor(exp.MatchRecognize, exp.Select), exp.MatchRecognize): 155 return self.function_fallback_sql(expression) 156 157 return rename_func("ARBITRARY")(self, expression) 158 159 160def _unix_to_time_sql(self: Presto.Generator, expression: exp.UnixToTime) -> str: 161 scale = expression.args.get("scale") 162 timestamp = self.sql(expression, "this") 163 if scale in (None, exp.UnixToTime.SECONDS): 164 return rename_func("FROM_UNIXTIME")(self, expression) 165 166 return f"FROM_UNIXTIME(CAST({timestamp} AS DOUBLE) / POW(10, {scale}))" 167 168 169def _to_int(self: Presto.Generator, expression: exp.Expression) -> exp.Expression: 170 if not expression.type: 171 from sqlglot.optimizer.annotate_types import annotate_types 172 173 annotate_types(expression, dialect=self.dialect) 174 if expression.type and expression.type.this not in exp.DataType.INTEGER_TYPES: 175 return exp.cast(expression, to=exp.DataType.Type.BIGINT) 176 return expression 177 178 179def _build_to_char(args: t.List) -> exp.TimeToStr: 180 fmt = seq_get(args, 1) 181 if isinstance(fmt, exp.Literal): 182 # We uppercase this to match Teradata's format mapping keys 183 fmt.set("this", fmt.this.upper()) 184 185 # We use "teradata" on purpose here, because the time formats are different in Presto. 186 # See https://prestodb.io/docs/current/functions/teradata.html?highlight=to_char#to_char 187 return build_formatted_time(exp.TimeToStr, "teradata")(args) 188 189 190def _date_delta_sql( 191 name: str, negate_interval: bool = False 192) -> t.Callable[[Presto.Generator, DATE_ADD_OR_SUB], str]: 193 def _delta_sql(self: Presto.Generator, expression: DATE_ADD_OR_SUB) -> str: 194 interval = _to_int(self, expression.expression) 195 return self.func( 196 name, 197 unit_to_str(expression), 198 interval * (-1) if negate_interval else interval, 199 expression.this, 200 ) 201 202 return _delta_sql 203 204 205class Presto(Dialect): 206 INDEX_OFFSET = 1 207 NULL_ORDERING = "nulls_are_last" 208 TIME_FORMAT = MySQL.TIME_FORMAT 209 STRICT_STRING_CONCAT = True 210 SUPPORTS_SEMI_ANTI_JOIN = False 211 TYPED_DIVISION = True 212 TABLESAMPLE_SIZE_IS_PERCENT = True 213 LOG_BASE_FIRST: t.Optional[bool] = None 214 215 TIME_MAPPING = MySQL.TIME_MAPPING 216 217 # https://github.com/trinodb/trino/issues/17 218 # https://github.com/trinodb/trino/issues/12289 219 # https://github.com/prestodb/presto/issues/2863 220 NORMALIZATION_STRATEGY = NormalizationStrategy.CASE_INSENSITIVE 221 222 # The result of certain math functions in Presto/Trino is of type 223 # equal to the input type e.g: FLOOR(5.5/2) -> DECIMAL, FLOOR(5/2) -> BIGINT 224 ANNOTATORS = { 225 **Dialect.ANNOTATORS, 226 exp.Floor: lambda self, e: self._annotate_by_args(e, "this"), 227 exp.Ceil: lambda self, e: self._annotate_by_args(e, "this"), 228 exp.Mod: lambda self, e: self._annotate_by_args(e, "this", "expression"), 229 exp.Round: lambda self, e: self._annotate_by_args(e, "this"), 230 exp.Sign: lambda self, e: self._annotate_by_args(e, "this"), 231 exp.Abs: lambda self, e: self._annotate_by_args(e, "this"), 232 exp.Rand: lambda self, e: self._annotate_by_args(e, "this") 233 if e.this 234 else self._set_type(e, exp.DataType.Type.DOUBLE), 235 } 236 237 class Tokenizer(tokens.Tokenizer): 238 UNICODE_STRINGS = [ 239 (prefix + q, q) 240 for q in t.cast(t.List[str], tokens.Tokenizer.QUOTES) 241 for prefix in ("U&", "u&") 242 ] 243 244 KEYWORDS = { 245 **tokens.Tokenizer.KEYWORDS, 246 "START": TokenType.BEGIN, 247 "MATCH_RECOGNIZE": TokenType.MATCH_RECOGNIZE, 248 "ROW": TokenType.STRUCT, 249 "IPADDRESS": TokenType.IPADDRESS, 250 "IPPREFIX": TokenType.IPPREFIX, 251 "TDIGEST": TokenType.TDIGEST, 252 "HYPERLOGLOG": TokenType.HLLSKETCH, 253 } 254 KEYWORDS.pop("/*+") 255 KEYWORDS.pop("QUALIFY") 256 257 class Parser(parser.Parser): 258 VALUES_FOLLOWED_BY_PAREN = False 259 260 FUNCTIONS = { 261 **parser.Parser.FUNCTIONS, 262 "ARBITRARY": exp.AnyValue.from_arg_list, 263 "APPROX_DISTINCT": exp.ApproxDistinct.from_arg_list, 264 "APPROX_PERCENTILE": _build_approx_percentile, 265 "BITWISE_AND": binary_from_function(exp.BitwiseAnd), 266 "BITWISE_NOT": lambda args: exp.BitwiseNot(this=seq_get(args, 0)), 267 "BITWISE_OR": binary_from_function(exp.BitwiseOr), 268 "BITWISE_XOR": binary_from_function(exp.BitwiseXor), 269 "CARDINALITY": exp.ArraySize.from_arg_list, 270 "CONTAINS": exp.ArrayContains.from_arg_list, 271 "DATE_ADD": lambda args: exp.DateAdd( 272 this=seq_get(args, 2), expression=seq_get(args, 1), unit=seq_get(args, 0) 273 ), 274 "DATE_DIFF": lambda args: exp.DateDiff( 275 this=seq_get(args, 2), expression=seq_get(args, 1), unit=seq_get(args, 0) 276 ), 277 "DATE_FORMAT": build_formatted_time(exp.TimeToStr, "presto"), 278 "DATE_PARSE": build_formatted_time(exp.StrToTime, "presto"), 279 "DATE_TRUNC": date_trunc_to_time, 280 "DAY_OF_WEEK": exp.DayOfWeekIso.from_arg_list, 281 "ELEMENT_AT": lambda args: exp.Bracket( 282 this=seq_get(args, 0), expressions=[seq_get(args, 1)], offset=1, safe=True 283 ), 284 "FROM_HEX": exp.Unhex.from_arg_list, 285 "FROM_UNIXTIME": _build_from_unixtime, 286 "FROM_UTF8": lambda args: exp.Decode( 287 this=seq_get(args, 0), replace=seq_get(args, 1), charset=exp.Literal.string("utf-8") 288 ), 289 "NOW": exp.CurrentTimestamp.from_arg_list, 290 "REGEXP_EXTRACT": build_regexp_extract, 291 "REGEXP_REPLACE": lambda args: exp.RegexpReplace( 292 this=seq_get(args, 0), 293 expression=seq_get(args, 1), 294 replacement=seq_get(args, 2) or exp.Literal.string(""), 295 ), 296 "ROW": exp.Struct.from_arg_list, 297 "SEQUENCE": exp.GenerateSeries.from_arg_list, 298 "SET_AGG": exp.ArrayUniqueAgg.from_arg_list, 299 "SPLIT_TO_MAP": exp.StrToMap.from_arg_list, 300 "STRPOS": lambda args: exp.StrPosition( 301 this=seq_get(args, 0), substr=seq_get(args, 1), instance=seq_get(args, 2) 302 ), 303 "TO_CHAR": _build_to_char, 304 "TO_UNIXTIME": exp.TimeToUnix.from_arg_list, 305 "TO_UTF8": lambda args: exp.Encode( 306 this=seq_get(args, 0), charset=exp.Literal.string("utf-8") 307 ), 308 "MD5": exp.MD5Digest.from_arg_list, 309 "SHA256": lambda args: exp.SHA2(this=seq_get(args, 0), length=exp.Literal.number(256)), 310 "SHA512": lambda args: exp.SHA2(this=seq_get(args, 0), length=exp.Literal.number(512)), 311 } 312 313 FUNCTION_PARSERS = parser.Parser.FUNCTION_PARSERS.copy() 314 FUNCTION_PARSERS.pop("TRIM") 315 316 class Generator(generator.Generator): 317 INTERVAL_ALLOWS_PLURAL_FORM = False 318 JOIN_HINTS = False 319 TABLE_HINTS = False 320 QUERY_HINTS = False 321 IS_BOOL_ALLOWED = False 322 TZ_TO_WITH_TIME_ZONE = True 323 NVL2_SUPPORTED = False 324 STRUCT_DELIMITER = ("(", ")") 325 LIMIT_ONLY_LITERALS = True 326 SUPPORTS_SINGLE_ARG_CONCAT = False 327 LIKE_PROPERTY_INSIDE_SCHEMA = True 328 MULTI_ARG_DISTINCT = False 329 SUPPORTS_TO_NUMBER = False 330 HEX_FUNC = "TO_HEX" 331 PARSE_JSON_NAME = "JSON_PARSE" 332 PAD_FILL_PATTERN_IS_REQUIRED = True 333 EXCEPT_INTERSECT_SUPPORT_ALL_CLAUSE = False 334 335 PROPERTIES_LOCATION = { 336 **generator.Generator.PROPERTIES_LOCATION, 337 exp.LocationProperty: exp.Properties.Location.UNSUPPORTED, 338 exp.VolatileProperty: exp.Properties.Location.UNSUPPORTED, 339 } 340 341 TYPE_MAPPING = { 342 **generator.Generator.TYPE_MAPPING, 343 exp.DataType.Type.INT: "INTEGER", 344 exp.DataType.Type.FLOAT: "REAL", 345 exp.DataType.Type.BINARY: "VARBINARY", 346 exp.DataType.Type.TEXT: "VARCHAR", 347 exp.DataType.Type.TIMETZ: "TIME", 348 exp.DataType.Type.TIMESTAMPTZ: "TIMESTAMP", 349 exp.DataType.Type.STRUCT: "ROW", 350 exp.DataType.Type.DATETIME: "TIMESTAMP", 351 exp.DataType.Type.DATETIME64: "TIMESTAMP", 352 exp.DataType.Type.HLLSKETCH: "HYPERLOGLOG", 353 } 354 355 TRANSFORMS = { 356 **generator.Generator.TRANSFORMS, 357 exp.AnyValue: rename_func("ARBITRARY"), 358 exp.ApproxQuantile: rename_func("APPROX_PERCENTILE"), 359 exp.ArgMax: rename_func("MAX_BY"), 360 exp.ArgMin: rename_func("MIN_BY"), 361 exp.Array: lambda self, e: f"ARRAY[{self.expressions(e, flat=True)}]", 362 exp.ArrayAny: rename_func("ANY_MATCH"), 363 exp.ArrayConcat: rename_func("CONCAT"), 364 exp.ArrayContains: rename_func("CONTAINS"), 365 exp.ArraySize: rename_func("CARDINALITY"), 366 exp.ArrayToString: rename_func("ARRAY_JOIN"), 367 exp.ArrayUniqueAgg: rename_func("SET_AGG"), 368 exp.AtTimeZone: rename_func("AT_TIMEZONE"), 369 exp.BitwiseAnd: lambda self, e: self.func("BITWISE_AND", e.this, e.expression), 370 exp.BitwiseLeftShift: lambda self, e: self.func( 371 "BITWISE_ARITHMETIC_SHIFT_LEFT", e.this, e.expression 372 ), 373 exp.BitwiseNot: lambda self, e: self.func("BITWISE_NOT", e.this), 374 exp.BitwiseOr: lambda self, e: self.func("BITWISE_OR", e.this, e.expression), 375 exp.BitwiseRightShift: lambda self, e: self.func( 376 "BITWISE_ARITHMETIC_SHIFT_RIGHT", e.this, e.expression 377 ), 378 exp.BitwiseXor: lambda self, e: self.func("BITWISE_XOR", e.this, e.expression), 379 exp.Cast: transforms.preprocess([transforms.epoch_cast_to_ts]), 380 exp.CurrentTimestamp: lambda *_: "CURRENT_TIMESTAMP", 381 exp.DateAdd: _date_delta_sql("DATE_ADD"), 382 exp.DateDiff: lambda self, e: self.func( 383 "DATE_DIFF", unit_to_str(e), e.expression, e.this 384 ), 385 exp.DateStrToDate: datestrtodate_sql, 386 exp.DateToDi: lambda self, 387 e: f"CAST(DATE_FORMAT({self.sql(e, 'this')}, {Presto.DATEINT_FORMAT}) AS INT)", 388 exp.DateSub: _date_delta_sql("DATE_ADD", negate_interval=True), 389 exp.DayOfWeek: lambda self, e: f"(({self.func('DAY_OF_WEEK', e.this)} % 7) + 1)", 390 exp.DayOfWeekIso: rename_func("DAY_OF_WEEK"), 391 exp.Decode: lambda self, e: encode_decode_sql(self, e, "FROM_UTF8"), 392 exp.DiToDate: lambda self, 393 e: f"CAST(DATE_PARSE(CAST({self.sql(e, 'this')} AS VARCHAR), {Presto.DATEINT_FORMAT}) AS DATE)", 394 exp.Encode: lambda self, e: encode_decode_sql(self, e, "TO_UTF8"), 395 exp.FileFormatProperty: lambda self, e: f"FORMAT='{e.name.upper()}'", 396 exp.First: _first_last_sql, 397 exp.FirstValue: _first_last_sql, 398 exp.FromTimeZone: lambda self, 399 e: f"WITH_TIMEZONE({self.sql(e, 'this')}, {self.sql(e, 'zone')}) AT TIME ZONE 'UTC'", 400 exp.GenerateSeries: sequence_sql, 401 exp.GenerateDateArray: sequence_sql, 402 exp.Group: transforms.preprocess([transforms.unalias_group]), 403 exp.GroupConcat: lambda self, e: self.func( 404 "ARRAY_JOIN", self.func("ARRAY_AGG", e.this), e.args.get("separator") 405 ), 406 exp.If: if_sql(), 407 exp.ILike: no_ilike_sql, 408 exp.Initcap: _initcap_sql, 409 exp.JSONExtract: lambda self, e: self.jsonextract_sql(e), 410 exp.Last: _first_last_sql, 411 exp.LastValue: _first_last_sql, 412 exp.LastDay: lambda self, e: self.func("LAST_DAY_OF_MONTH", e.this), 413 exp.Lateral: _explode_to_unnest_sql, 414 exp.Left: left_to_substring_sql, 415 exp.Levenshtein: rename_func("LEVENSHTEIN_DISTANCE"), 416 exp.LogicalAnd: rename_func("BOOL_AND"), 417 exp.LogicalOr: rename_func("BOOL_OR"), 418 exp.Pivot: no_pivot_sql, 419 exp.Quantile: _quantile_sql, 420 exp.RegexpExtract: regexp_extract_sql, 421 exp.Right: right_to_substring_sql, 422 exp.SafeDivide: no_safe_divide_sql, 423 exp.Schema: _schema_sql, 424 exp.SchemaCommentProperty: lambda self, e: self.naked_property(e), 425 exp.Select: transforms.preprocess( 426 [ 427 transforms.eliminate_qualify, 428 transforms.eliminate_distinct_on, 429 transforms.explode_to_unnest(1), 430 transforms.eliminate_semi_and_anti_joins, 431 ] 432 ), 433 exp.SortArray: _no_sort_array, 434 exp.StrPosition: lambda self, e: str_position_sql(self, e, generate_instance=True), 435 exp.StrToDate: lambda self, e: f"CAST({_str_to_time_sql(self, e)} AS DATE)", 436 exp.StrToMap: rename_func("SPLIT_TO_MAP"), 437 exp.StrToTime: _str_to_time_sql, 438 exp.StructExtract: struct_extract_sql, 439 exp.Table: transforms.preprocess([transforms.unnest_generate_series]), 440 exp.Timestamp: no_timestamp_sql, 441 exp.TimestampAdd: _date_delta_sql("DATE_ADD"), 442 exp.TimestampTrunc: timestamptrunc_sql(), 443 exp.TimeStrToDate: timestrtotime_sql, 444 exp.TimeStrToTime: timestrtotime_sql, 445 exp.TimeStrToUnix: lambda self, e: self.func( 446 "TO_UNIXTIME", self.func("DATE_PARSE", e.this, Presto.TIME_FORMAT) 447 ), 448 exp.TimeToStr: lambda self, e: self.func("DATE_FORMAT", e.this, self.format_time(e)), 449 exp.TimeToUnix: rename_func("TO_UNIXTIME"), 450 exp.ToChar: lambda self, e: self.func("DATE_FORMAT", e.this, self.format_time(e)), 451 exp.TryCast: transforms.preprocess([transforms.epoch_cast_to_ts]), 452 exp.TsOrDiToDi: lambda self, 453 e: f"CAST(SUBSTR(REPLACE(CAST({self.sql(e, 'this')} AS VARCHAR), '-', ''), 1, 8) AS INT)", 454 exp.TsOrDsAdd: _ts_or_ds_add_sql, 455 exp.TsOrDsDiff: _ts_or_ds_diff_sql, 456 exp.TsOrDsToDate: _ts_or_ds_to_date_sql, 457 exp.Unhex: rename_func("FROM_HEX"), 458 exp.UnixToStr: lambda self, 459 e: f"DATE_FORMAT(FROM_UNIXTIME({self.sql(e, 'this')}), {self.format_time(e)})", 460 exp.UnixToTime: _unix_to_time_sql, 461 exp.UnixToTimeStr: lambda self, 462 e: f"CAST(FROM_UNIXTIME({self.sql(e, 'this')}) AS VARCHAR)", 463 exp.VariancePop: rename_func("VAR_POP"), 464 exp.With: transforms.preprocess([transforms.add_recursive_cte_column_names]), 465 exp.WithinGroup: transforms.preprocess( 466 [transforms.remove_within_group_for_percentiles] 467 ), 468 exp.Xor: bool_xor_sql, 469 exp.MD5Digest: rename_func("MD5"), 470 exp.SHA: rename_func("SHA1"), 471 exp.SHA2: sha256_sql, 472 } 473 474 RESERVED_KEYWORDS = { 475 "alter", 476 "and", 477 "as", 478 "between", 479 "by", 480 "case", 481 "cast", 482 "constraint", 483 "create", 484 "cross", 485 "current_time", 486 "current_timestamp", 487 "deallocate", 488 "delete", 489 "describe", 490 "distinct", 491 "drop", 492 "else", 493 "end", 494 "escape", 495 "except", 496 "execute", 497 "exists", 498 "extract", 499 "false", 500 "for", 501 "from", 502 "full", 503 "group", 504 "having", 505 "in", 506 "inner", 507 "insert", 508 "intersect", 509 "into", 510 "is", 511 "join", 512 "left", 513 "like", 514 "natural", 515 "not", 516 "null", 517 "on", 518 "or", 519 "order", 520 "outer", 521 "prepare", 522 "right", 523 "select", 524 "table", 525 "then", 526 "true", 527 "union", 528 "using", 529 "values", 530 "when", 531 "where", 532 "with", 533 } 534 535 def md5_sql(self, expression: exp.MD5) -> str: 536 this = expression.this 537 538 if not this.type: 539 from sqlglot.optimizer.annotate_types import annotate_types 540 541 this = annotate_types(this) 542 543 if this.is_type(*exp.DataType.TEXT_TYPES): 544 this = exp.Encode(this=this, charset=exp.Literal.string("utf-8")) 545 546 return self.func("LOWER", self.func("TO_HEX", self.func("MD5", self.sql(this)))) 547 548 def strtounix_sql(self, expression: exp.StrToUnix) -> str: 549 # Since `TO_UNIXTIME` requires a `TIMESTAMP`, we need to parse the argument into one. 550 # To do this, we first try to `DATE_PARSE` it, but since this can fail when there's a 551 # timezone involved, we wrap it in a `TRY` call and use `PARSE_DATETIME` as a fallback, 552 # which seems to be using the same time mapping as Hive, as per: 553 # https://joda-time.sourceforge.net/apidocs/org/joda/time/format/DateTimeFormat.html 554 this = expression.this 555 value_as_text = exp.cast(this, exp.DataType.Type.TEXT) 556 value_as_timestamp = ( 557 exp.cast(this, exp.DataType.Type.TIMESTAMP) if this.is_string else this 558 ) 559 560 parse_without_tz = self.func("DATE_PARSE", value_as_text, self.format_time(expression)) 561 562 formatted_value = self.func( 563 "DATE_FORMAT", value_as_timestamp, self.format_time(expression) 564 ) 565 parse_with_tz = self.func( 566 "PARSE_DATETIME", 567 formatted_value, 568 self.format_time(expression, Hive.INVERSE_TIME_MAPPING, Hive.INVERSE_TIME_TRIE), 569 ) 570 coalesced = self.func("COALESCE", self.func("TRY", parse_without_tz), parse_with_tz) 571 return self.func("TO_UNIXTIME", coalesced) 572 573 def bracket_sql(self, expression: exp.Bracket) -> str: 574 if expression.args.get("safe"): 575 return self.func( 576 "ELEMENT_AT", 577 expression.this, 578 seq_get( 579 apply_index_offset( 580 expression.this, 581 expression.expressions, 582 1 - expression.args.get("offset", 0), 583 ), 584 0, 585 ), 586 ) 587 return super().bracket_sql(expression) 588 589 def struct_sql(self, expression: exp.Struct) -> str: 590 from sqlglot.optimizer.annotate_types import annotate_types 591 592 expression = annotate_types(expression) 593 values: t.List[str] = [] 594 schema: t.List[str] = [] 595 unknown_type = False 596 597 for e in expression.expressions: 598 if isinstance(e, exp.PropertyEQ): 599 if e.type and e.type.is_type(exp.DataType.Type.UNKNOWN): 600 unknown_type = True 601 else: 602 schema.append(f"{self.sql(e, 'this')} {self.sql(e.type)}") 603 values.append(self.sql(e, "expression")) 604 else: 605 values.append(self.sql(e)) 606 607 size = len(expression.expressions) 608 609 if not size or len(schema) != size: 610 if unknown_type: 611 self.unsupported( 612 "Cannot convert untyped key-value definitions (try annotate_types)." 613 ) 614 return self.func("ROW", *values) 615 return f"CAST(ROW({', '.join(values)}) AS ROW({', '.join(schema)}))" 616 617 def interval_sql(self, expression: exp.Interval) -> str: 618 if expression.this and expression.text("unit").upper().startswith("WEEK"): 619 return f"({expression.this.name} * INTERVAL '7' DAY)" 620 return super().interval_sql(expression) 621 622 def transaction_sql(self, expression: exp.Transaction) -> str: 623 modes = expression.args.get("modes") 624 modes = f" {', '.join(modes)}" if modes else "" 625 return f"START TRANSACTION{modes}" 626 627 def offset_limit_modifiers( 628 self, expression: exp.Expression, fetch: bool, limit: t.Optional[exp.Fetch | exp.Limit] 629 ) -> t.List[str]: 630 return [ 631 self.sql(expression, "offset"), 632 self.sql(limit), 633 ] 634 635 def create_sql(self, expression: exp.Create) -> str: 636 """ 637 Presto doesn't support CREATE VIEW with expressions (ex: `CREATE VIEW x (cola)` then `(cola)` is the expression), 638 so we need to remove them 639 """ 640 kind = expression.args["kind"] 641 schema = expression.this 642 if kind == "VIEW" and schema.expressions: 643 expression.this.set("expressions", None) 644 return super().create_sql(expression) 645 646 def delete_sql(self, expression: exp.Delete) -> str: 647 """ 648 Presto only supports DELETE FROM for a single table without an alias, so we need 649 to remove the unnecessary parts. If the original DELETE statement contains more 650 than one table to be deleted, we can't safely map it 1-1 to a Presto statement. 651 """ 652 tables = expression.args.get("tables") or [expression.this] 653 if len(tables) > 1: 654 return super().delete_sql(expression) 655 656 table = tables[0] 657 expression.set("this", table) 658 expression.set("tables", None) 659 660 if isinstance(table, exp.Table): 661 table_alias = table.args.get("alias") 662 if table_alias: 663 table_alias.pop() 664 expression = t.cast(exp.Delete, expression.transform(unqualify_columns)) 665 666 return super().delete_sql(expression) 667 668 def jsonextract_sql(self, expression: exp.JSONExtract) -> str: 669 is_json_extract = self.dialect.settings.get("variant_extract_is_json_extract", True) 670 671 # Generate JSON_EXTRACT unless the user has configured that a Snowflake / Databricks 672 # VARIANT extract (e.g. col:x.y) should map to dot notation (i.e ROW access) in Presto/Trino 673 if not expression.args.get("variant_extract") or is_json_extract: 674 return self.func( 675 "JSON_EXTRACT", expression.this, expression.expression, *expression.expressions 676 ) 677 678 this = self.sql(expression, "this") 679 680 # Convert the JSONPath extraction `JSON_EXTRACT(col, '$.x.y) to a ROW access col.x.y 681 segments = [] 682 for path_key in expression.expression.expressions[1:]: 683 if not isinstance(path_key, exp.JSONPathKey): 684 # Cannot transpile subscripts, wildcards etc to dot notation 685 self.unsupported( 686 f"Cannot transpile JSONPath segment '{path_key}' to ROW access" 687 ) 688 continue 689 key = path_key.this 690 if not exp.SAFE_IDENTIFIER_RE.match(key): 691 key = f'"{key}"' 692 segments.append(f".{key}") 693 694 expr = "".join(segments) 695 696 return f"{this}{expr}"
206class Presto(Dialect): 207 INDEX_OFFSET = 1 208 NULL_ORDERING = "nulls_are_last" 209 TIME_FORMAT = MySQL.TIME_FORMAT 210 STRICT_STRING_CONCAT = True 211 SUPPORTS_SEMI_ANTI_JOIN = False 212 TYPED_DIVISION = True 213 TABLESAMPLE_SIZE_IS_PERCENT = True 214 LOG_BASE_FIRST: t.Optional[bool] = None 215 216 TIME_MAPPING = MySQL.TIME_MAPPING 217 218 # https://github.com/trinodb/trino/issues/17 219 # https://github.com/trinodb/trino/issues/12289 220 # https://github.com/prestodb/presto/issues/2863 221 NORMALIZATION_STRATEGY = NormalizationStrategy.CASE_INSENSITIVE 222 223 # The result of certain math functions in Presto/Trino is of type 224 # equal to the input type e.g: FLOOR(5.5/2) -> DECIMAL, FLOOR(5/2) -> BIGINT 225 ANNOTATORS = { 226 **Dialect.ANNOTATORS, 227 exp.Floor: lambda self, e: self._annotate_by_args(e, "this"), 228 exp.Ceil: lambda self, e: self._annotate_by_args(e, "this"), 229 exp.Mod: lambda self, e: self._annotate_by_args(e, "this", "expression"), 230 exp.Round: lambda self, e: self._annotate_by_args(e, "this"), 231 exp.Sign: lambda self, e: self._annotate_by_args(e, "this"), 232 exp.Abs: lambda self, e: self._annotate_by_args(e, "this"), 233 exp.Rand: lambda self, e: self._annotate_by_args(e, "this") 234 if e.this 235 else self._set_type(e, exp.DataType.Type.DOUBLE), 236 } 237 238 class Tokenizer(tokens.Tokenizer): 239 UNICODE_STRINGS = [ 240 (prefix + q, q) 241 for q in t.cast(t.List[str], tokens.Tokenizer.QUOTES) 242 for prefix in ("U&", "u&") 243 ] 244 245 KEYWORDS = { 246 **tokens.Tokenizer.KEYWORDS, 247 "START": TokenType.BEGIN, 248 "MATCH_RECOGNIZE": TokenType.MATCH_RECOGNIZE, 249 "ROW": TokenType.STRUCT, 250 "IPADDRESS": TokenType.IPADDRESS, 251 "IPPREFIX": TokenType.IPPREFIX, 252 "TDIGEST": TokenType.TDIGEST, 253 "HYPERLOGLOG": TokenType.HLLSKETCH, 254 } 255 KEYWORDS.pop("/*+") 256 KEYWORDS.pop("QUALIFY") 257 258 class Parser(parser.Parser): 259 VALUES_FOLLOWED_BY_PAREN = False 260 261 FUNCTIONS = { 262 **parser.Parser.FUNCTIONS, 263 "ARBITRARY": exp.AnyValue.from_arg_list, 264 "APPROX_DISTINCT": exp.ApproxDistinct.from_arg_list, 265 "APPROX_PERCENTILE": _build_approx_percentile, 266 "BITWISE_AND": binary_from_function(exp.BitwiseAnd), 267 "BITWISE_NOT": lambda args: exp.BitwiseNot(this=seq_get(args, 0)), 268 "BITWISE_OR": binary_from_function(exp.BitwiseOr), 269 "BITWISE_XOR": binary_from_function(exp.BitwiseXor), 270 "CARDINALITY": exp.ArraySize.from_arg_list, 271 "CONTAINS": exp.ArrayContains.from_arg_list, 272 "DATE_ADD": lambda args: exp.DateAdd( 273 this=seq_get(args, 2), expression=seq_get(args, 1), unit=seq_get(args, 0) 274 ), 275 "DATE_DIFF": lambda args: exp.DateDiff( 276 this=seq_get(args, 2), expression=seq_get(args, 1), unit=seq_get(args, 0) 277 ), 278 "DATE_FORMAT": build_formatted_time(exp.TimeToStr, "presto"), 279 "DATE_PARSE": build_formatted_time(exp.StrToTime, "presto"), 280 "DATE_TRUNC": date_trunc_to_time, 281 "DAY_OF_WEEK": exp.DayOfWeekIso.from_arg_list, 282 "ELEMENT_AT": lambda args: exp.Bracket( 283 this=seq_get(args, 0), expressions=[seq_get(args, 1)], offset=1, safe=True 284 ), 285 "FROM_HEX": exp.Unhex.from_arg_list, 286 "FROM_UNIXTIME": _build_from_unixtime, 287 "FROM_UTF8": lambda args: exp.Decode( 288 this=seq_get(args, 0), replace=seq_get(args, 1), charset=exp.Literal.string("utf-8") 289 ), 290 "NOW": exp.CurrentTimestamp.from_arg_list, 291 "REGEXP_EXTRACT": build_regexp_extract, 292 "REGEXP_REPLACE": lambda args: exp.RegexpReplace( 293 this=seq_get(args, 0), 294 expression=seq_get(args, 1), 295 replacement=seq_get(args, 2) or exp.Literal.string(""), 296 ), 297 "ROW": exp.Struct.from_arg_list, 298 "SEQUENCE": exp.GenerateSeries.from_arg_list, 299 "SET_AGG": exp.ArrayUniqueAgg.from_arg_list, 300 "SPLIT_TO_MAP": exp.StrToMap.from_arg_list, 301 "STRPOS": lambda args: exp.StrPosition( 302 this=seq_get(args, 0), substr=seq_get(args, 1), instance=seq_get(args, 2) 303 ), 304 "TO_CHAR": _build_to_char, 305 "TO_UNIXTIME": exp.TimeToUnix.from_arg_list, 306 "TO_UTF8": lambda args: exp.Encode( 307 this=seq_get(args, 0), charset=exp.Literal.string("utf-8") 308 ), 309 "MD5": exp.MD5Digest.from_arg_list, 310 "SHA256": lambda args: exp.SHA2(this=seq_get(args, 0), length=exp.Literal.number(256)), 311 "SHA512": lambda args: exp.SHA2(this=seq_get(args, 0), length=exp.Literal.number(512)), 312 } 313 314 FUNCTION_PARSERS = parser.Parser.FUNCTION_PARSERS.copy() 315 FUNCTION_PARSERS.pop("TRIM") 316 317 class Generator(generator.Generator): 318 INTERVAL_ALLOWS_PLURAL_FORM = False 319 JOIN_HINTS = False 320 TABLE_HINTS = False 321 QUERY_HINTS = False 322 IS_BOOL_ALLOWED = False 323 TZ_TO_WITH_TIME_ZONE = True 324 NVL2_SUPPORTED = False 325 STRUCT_DELIMITER = ("(", ")") 326 LIMIT_ONLY_LITERALS = True 327 SUPPORTS_SINGLE_ARG_CONCAT = False 328 LIKE_PROPERTY_INSIDE_SCHEMA = True 329 MULTI_ARG_DISTINCT = False 330 SUPPORTS_TO_NUMBER = False 331 HEX_FUNC = "TO_HEX" 332 PARSE_JSON_NAME = "JSON_PARSE" 333 PAD_FILL_PATTERN_IS_REQUIRED = True 334 EXCEPT_INTERSECT_SUPPORT_ALL_CLAUSE = False 335 336 PROPERTIES_LOCATION = { 337 **generator.Generator.PROPERTIES_LOCATION, 338 exp.LocationProperty: exp.Properties.Location.UNSUPPORTED, 339 exp.VolatileProperty: exp.Properties.Location.UNSUPPORTED, 340 } 341 342 TYPE_MAPPING = { 343 **generator.Generator.TYPE_MAPPING, 344 exp.DataType.Type.INT: "INTEGER", 345 exp.DataType.Type.FLOAT: "REAL", 346 exp.DataType.Type.BINARY: "VARBINARY", 347 exp.DataType.Type.TEXT: "VARCHAR", 348 exp.DataType.Type.TIMETZ: "TIME", 349 exp.DataType.Type.TIMESTAMPTZ: "TIMESTAMP", 350 exp.DataType.Type.STRUCT: "ROW", 351 exp.DataType.Type.DATETIME: "TIMESTAMP", 352 exp.DataType.Type.DATETIME64: "TIMESTAMP", 353 exp.DataType.Type.HLLSKETCH: "HYPERLOGLOG", 354 } 355 356 TRANSFORMS = { 357 **generator.Generator.TRANSFORMS, 358 exp.AnyValue: rename_func("ARBITRARY"), 359 exp.ApproxQuantile: rename_func("APPROX_PERCENTILE"), 360 exp.ArgMax: rename_func("MAX_BY"), 361 exp.ArgMin: rename_func("MIN_BY"), 362 exp.Array: lambda self, e: f"ARRAY[{self.expressions(e, flat=True)}]", 363 exp.ArrayAny: rename_func("ANY_MATCH"), 364 exp.ArrayConcat: rename_func("CONCAT"), 365 exp.ArrayContains: rename_func("CONTAINS"), 366 exp.ArraySize: rename_func("CARDINALITY"), 367 exp.ArrayToString: rename_func("ARRAY_JOIN"), 368 exp.ArrayUniqueAgg: rename_func("SET_AGG"), 369 exp.AtTimeZone: rename_func("AT_TIMEZONE"), 370 exp.BitwiseAnd: lambda self, e: self.func("BITWISE_AND", e.this, e.expression), 371 exp.BitwiseLeftShift: lambda self, e: self.func( 372 "BITWISE_ARITHMETIC_SHIFT_LEFT", e.this, e.expression 373 ), 374 exp.BitwiseNot: lambda self, e: self.func("BITWISE_NOT", e.this), 375 exp.BitwiseOr: lambda self, e: self.func("BITWISE_OR", e.this, e.expression), 376 exp.BitwiseRightShift: lambda self, e: self.func( 377 "BITWISE_ARITHMETIC_SHIFT_RIGHT", e.this, e.expression 378 ), 379 exp.BitwiseXor: lambda self, e: self.func("BITWISE_XOR", e.this, e.expression), 380 exp.Cast: transforms.preprocess([transforms.epoch_cast_to_ts]), 381 exp.CurrentTimestamp: lambda *_: "CURRENT_TIMESTAMP", 382 exp.DateAdd: _date_delta_sql("DATE_ADD"), 383 exp.DateDiff: lambda self, e: self.func( 384 "DATE_DIFF", unit_to_str(e), e.expression, e.this 385 ), 386 exp.DateStrToDate: datestrtodate_sql, 387 exp.DateToDi: lambda self, 388 e: f"CAST(DATE_FORMAT({self.sql(e, 'this')}, {Presto.DATEINT_FORMAT}) AS INT)", 389 exp.DateSub: _date_delta_sql("DATE_ADD", negate_interval=True), 390 exp.DayOfWeek: lambda self, e: f"(({self.func('DAY_OF_WEEK', e.this)} % 7) + 1)", 391 exp.DayOfWeekIso: rename_func("DAY_OF_WEEK"), 392 exp.Decode: lambda self, e: encode_decode_sql(self, e, "FROM_UTF8"), 393 exp.DiToDate: lambda self, 394 e: f"CAST(DATE_PARSE(CAST({self.sql(e, 'this')} AS VARCHAR), {Presto.DATEINT_FORMAT}) AS DATE)", 395 exp.Encode: lambda self, e: encode_decode_sql(self, e, "TO_UTF8"), 396 exp.FileFormatProperty: lambda self, e: f"FORMAT='{e.name.upper()}'", 397 exp.First: _first_last_sql, 398 exp.FirstValue: _first_last_sql, 399 exp.FromTimeZone: lambda self, 400 e: f"WITH_TIMEZONE({self.sql(e, 'this')}, {self.sql(e, 'zone')}) AT TIME ZONE 'UTC'", 401 exp.GenerateSeries: sequence_sql, 402 exp.GenerateDateArray: sequence_sql, 403 exp.Group: transforms.preprocess([transforms.unalias_group]), 404 exp.GroupConcat: lambda self, e: self.func( 405 "ARRAY_JOIN", self.func("ARRAY_AGG", e.this), e.args.get("separator") 406 ), 407 exp.If: if_sql(), 408 exp.ILike: no_ilike_sql, 409 exp.Initcap: _initcap_sql, 410 exp.JSONExtract: lambda self, e: self.jsonextract_sql(e), 411 exp.Last: _first_last_sql, 412 exp.LastValue: _first_last_sql, 413 exp.LastDay: lambda self, e: self.func("LAST_DAY_OF_MONTH", e.this), 414 exp.Lateral: _explode_to_unnest_sql, 415 exp.Left: left_to_substring_sql, 416 exp.Levenshtein: rename_func("LEVENSHTEIN_DISTANCE"), 417 exp.LogicalAnd: rename_func("BOOL_AND"), 418 exp.LogicalOr: rename_func("BOOL_OR"), 419 exp.Pivot: no_pivot_sql, 420 exp.Quantile: _quantile_sql, 421 exp.RegexpExtract: regexp_extract_sql, 422 exp.Right: right_to_substring_sql, 423 exp.SafeDivide: no_safe_divide_sql, 424 exp.Schema: _schema_sql, 425 exp.SchemaCommentProperty: lambda self, e: self.naked_property(e), 426 exp.Select: transforms.preprocess( 427 [ 428 transforms.eliminate_qualify, 429 transforms.eliminate_distinct_on, 430 transforms.explode_to_unnest(1), 431 transforms.eliminate_semi_and_anti_joins, 432 ] 433 ), 434 exp.SortArray: _no_sort_array, 435 exp.StrPosition: lambda self, e: str_position_sql(self, e, generate_instance=True), 436 exp.StrToDate: lambda self, e: f"CAST({_str_to_time_sql(self, e)} AS DATE)", 437 exp.StrToMap: rename_func("SPLIT_TO_MAP"), 438 exp.StrToTime: _str_to_time_sql, 439 exp.StructExtract: struct_extract_sql, 440 exp.Table: transforms.preprocess([transforms.unnest_generate_series]), 441 exp.Timestamp: no_timestamp_sql, 442 exp.TimestampAdd: _date_delta_sql("DATE_ADD"), 443 exp.TimestampTrunc: timestamptrunc_sql(), 444 exp.TimeStrToDate: timestrtotime_sql, 445 exp.TimeStrToTime: timestrtotime_sql, 446 exp.TimeStrToUnix: lambda self, e: self.func( 447 "TO_UNIXTIME", self.func("DATE_PARSE", e.this, Presto.TIME_FORMAT) 448 ), 449 exp.TimeToStr: lambda self, e: self.func("DATE_FORMAT", e.this, self.format_time(e)), 450 exp.TimeToUnix: rename_func("TO_UNIXTIME"), 451 exp.ToChar: lambda self, e: self.func("DATE_FORMAT", e.this, self.format_time(e)), 452 exp.TryCast: transforms.preprocess([transforms.epoch_cast_to_ts]), 453 exp.TsOrDiToDi: lambda self, 454 e: f"CAST(SUBSTR(REPLACE(CAST({self.sql(e, 'this')} AS VARCHAR), '-', ''), 1, 8) AS INT)", 455 exp.TsOrDsAdd: _ts_or_ds_add_sql, 456 exp.TsOrDsDiff: _ts_or_ds_diff_sql, 457 exp.TsOrDsToDate: _ts_or_ds_to_date_sql, 458 exp.Unhex: rename_func("FROM_HEX"), 459 exp.UnixToStr: lambda self, 460 e: f"DATE_FORMAT(FROM_UNIXTIME({self.sql(e, 'this')}), {self.format_time(e)})", 461 exp.UnixToTime: _unix_to_time_sql, 462 exp.UnixToTimeStr: lambda self, 463 e: f"CAST(FROM_UNIXTIME({self.sql(e, 'this')}) AS VARCHAR)", 464 exp.VariancePop: rename_func("VAR_POP"), 465 exp.With: transforms.preprocess([transforms.add_recursive_cte_column_names]), 466 exp.WithinGroup: transforms.preprocess( 467 [transforms.remove_within_group_for_percentiles] 468 ), 469 exp.Xor: bool_xor_sql, 470 exp.MD5Digest: rename_func("MD5"), 471 exp.SHA: rename_func("SHA1"), 472 exp.SHA2: sha256_sql, 473 } 474 475 RESERVED_KEYWORDS = { 476 "alter", 477 "and", 478 "as", 479 "between", 480 "by", 481 "case", 482 "cast", 483 "constraint", 484 "create", 485 "cross", 486 "current_time", 487 "current_timestamp", 488 "deallocate", 489 "delete", 490 "describe", 491 "distinct", 492 "drop", 493 "else", 494 "end", 495 "escape", 496 "except", 497 "execute", 498 "exists", 499 "extract", 500 "false", 501 "for", 502 "from", 503 "full", 504 "group", 505 "having", 506 "in", 507 "inner", 508 "insert", 509 "intersect", 510 "into", 511 "is", 512 "join", 513 "left", 514 "like", 515 "natural", 516 "not", 517 "null", 518 "on", 519 "or", 520 "order", 521 "outer", 522 "prepare", 523 "right", 524 "select", 525 "table", 526 "then", 527 "true", 528 "union", 529 "using", 530 "values", 531 "when", 532 "where", 533 "with", 534 } 535 536 def md5_sql(self, expression: exp.MD5) -> str: 537 this = expression.this 538 539 if not this.type: 540 from sqlglot.optimizer.annotate_types import annotate_types 541 542 this = annotate_types(this) 543 544 if this.is_type(*exp.DataType.TEXT_TYPES): 545 this = exp.Encode(this=this, charset=exp.Literal.string("utf-8")) 546 547 return self.func("LOWER", self.func("TO_HEX", self.func("MD5", self.sql(this)))) 548 549 def strtounix_sql(self, expression: exp.StrToUnix) -> str: 550 # Since `TO_UNIXTIME` requires a `TIMESTAMP`, we need to parse the argument into one. 551 # To do this, we first try to `DATE_PARSE` it, but since this can fail when there's a 552 # timezone involved, we wrap it in a `TRY` call and use `PARSE_DATETIME` as a fallback, 553 # which seems to be using the same time mapping as Hive, as per: 554 # https://joda-time.sourceforge.net/apidocs/org/joda/time/format/DateTimeFormat.html 555 this = expression.this 556 value_as_text = exp.cast(this, exp.DataType.Type.TEXT) 557 value_as_timestamp = ( 558 exp.cast(this, exp.DataType.Type.TIMESTAMP) if this.is_string else this 559 ) 560 561 parse_without_tz = self.func("DATE_PARSE", value_as_text, self.format_time(expression)) 562 563 formatted_value = self.func( 564 "DATE_FORMAT", value_as_timestamp, self.format_time(expression) 565 ) 566 parse_with_tz = self.func( 567 "PARSE_DATETIME", 568 formatted_value, 569 self.format_time(expression, Hive.INVERSE_TIME_MAPPING, Hive.INVERSE_TIME_TRIE), 570 ) 571 coalesced = self.func("COALESCE", self.func("TRY", parse_without_tz), parse_with_tz) 572 return self.func("TO_UNIXTIME", coalesced) 573 574 def bracket_sql(self, expression: exp.Bracket) -> str: 575 if expression.args.get("safe"): 576 return self.func( 577 "ELEMENT_AT", 578 expression.this, 579 seq_get( 580 apply_index_offset( 581 expression.this, 582 expression.expressions, 583 1 - expression.args.get("offset", 0), 584 ), 585 0, 586 ), 587 ) 588 return super().bracket_sql(expression) 589 590 def struct_sql(self, expression: exp.Struct) -> str: 591 from sqlglot.optimizer.annotate_types import annotate_types 592 593 expression = annotate_types(expression) 594 values: t.List[str] = [] 595 schema: t.List[str] = [] 596 unknown_type = False 597 598 for e in expression.expressions: 599 if isinstance(e, exp.PropertyEQ): 600 if e.type and e.type.is_type(exp.DataType.Type.UNKNOWN): 601 unknown_type = True 602 else: 603 schema.append(f"{self.sql(e, 'this')} {self.sql(e.type)}") 604 values.append(self.sql(e, "expression")) 605 else: 606 values.append(self.sql(e)) 607 608 size = len(expression.expressions) 609 610 if not size or len(schema) != size: 611 if unknown_type: 612 self.unsupported( 613 "Cannot convert untyped key-value definitions (try annotate_types)." 614 ) 615 return self.func("ROW", *values) 616 return f"CAST(ROW({', '.join(values)}) AS ROW({', '.join(schema)}))" 617 618 def interval_sql(self, expression: exp.Interval) -> str: 619 if expression.this and expression.text("unit").upper().startswith("WEEK"): 620 return f"({expression.this.name} * INTERVAL '7' DAY)" 621 return super().interval_sql(expression) 622 623 def transaction_sql(self, expression: exp.Transaction) -> str: 624 modes = expression.args.get("modes") 625 modes = f" {', '.join(modes)}" if modes else "" 626 return f"START TRANSACTION{modes}" 627 628 def offset_limit_modifiers( 629 self, expression: exp.Expression, fetch: bool, limit: t.Optional[exp.Fetch | exp.Limit] 630 ) -> t.List[str]: 631 return [ 632 self.sql(expression, "offset"), 633 self.sql(limit), 634 ] 635 636 def create_sql(self, expression: exp.Create) -> str: 637 """ 638 Presto doesn't support CREATE VIEW with expressions (ex: `CREATE VIEW x (cola)` then `(cola)` is the expression), 639 so we need to remove them 640 """ 641 kind = expression.args["kind"] 642 schema = expression.this 643 if kind == "VIEW" and schema.expressions: 644 expression.this.set("expressions", None) 645 return super().create_sql(expression) 646 647 def delete_sql(self, expression: exp.Delete) -> str: 648 """ 649 Presto only supports DELETE FROM for a single table without an alias, so we need 650 to remove the unnecessary parts. If the original DELETE statement contains more 651 than one table to be deleted, we can't safely map it 1-1 to a Presto statement. 652 """ 653 tables = expression.args.get("tables") or [expression.this] 654 if len(tables) > 1: 655 return super().delete_sql(expression) 656 657 table = tables[0] 658 expression.set("this", table) 659 expression.set("tables", None) 660 661 if isinstance(table, exp.Table): 662 table_alias = table.args.get("alias") 663 if table_alias: 664 table_alias.pop() 665 expression = t.cast(exp.Delete, expression.transform(unqualify_columns)) 666 667 return super().delete_sql(expression) 668 669 def jsonextract_sql(self, expression: exp.JSONExtract) -> str: 670 is_json_extract = self.dialect.settings.get("variant_extract_is_json_extract", True) 671 672 # Generate JSON_EXTRACT unless the user has configured that a Snowflake / Databricks 673 # VARIANT extract (e.g. col:x.y) should map to dot notation (i.e ROW access) in Presto/Trino 674 if not expression.args.get("variant_extract") or is_json_extract: 675 return self.func( 676 "JSON_EXTRACT", expression.this, expression.expression, *expression.expressions 677 ) 678 679 this = self.sql(expression, "this") 680 681 # Convert the JSONPath extraction `JSON_EXTRACT(col, '$.x.y) to a ROW access col.x.y 682 segments = [] 683 for path_key in expression.expression.expressions[1:]: 684 if not isinstance(path_key, exp.JSONPathKey): 685 # Cannot transpile subscripts, wildcards etc to dot notation 686 self.unsupported( 687 f"Cannot transpile JSONPath segment '{path_key}' to ROW access" 688 ) 689 continue 690 key = path_key.this 691 if not exp.SAFE_IDENTIFIER_RE.match(key): 692 key = f'"{key}"' 693 segments.append(f".{key}") 694 695 expr = "".join(segments) 696 697 return f"{this}{expr}"
Default NULL
ordering method to use if not explicitly set.
Possible values: "nulls_are_small"
, "nulls_are_large"
, "nulls_are_last"
Whether the behavior of a / b
depends on the types of a
and b
.
False means a / b
is always float division.
True means a / b
is integer division if both a
and b
are integers.
Whether the base comes first in the LOG
function.
Possible values: True
, False
, None
(two arguments are not supported by LOG
)
Associates this dialect's time formats with their equivalent Python strftime
formats.
Specifies the strategy according to which identifiers should be normalized.
Inherited Members
- sqlglot.dialects.dialect.Dialect
- Dialect
- WEEK_OFFSET
- UNNEST_COLUMN_ONLY
- ALIAS_POST_TABLESAMPLE
- IDENTIFIERS_CAN_START_WITH_DIGIT
- DPIPE_IS_STRING_CONCAT
- SUPPORTS_USER_DEFINED_TYPES
- COPY_PARAMS_ARE_CSV
- NORMALIZE_FUNCTIONS
- SAFE_DIVISION
- CONCAT_COALESCE
- HEX_LOWERCASE
- DATE_FORMAT
- DATEINT_FORMAT
- FORMAT_MAPPING
- UNESCAPED_SEQUENCES
- PSEUDOCOLUMNS
- PREFER_CTE_ALIAS_COLUMN
- FORCE_EARLY_ALIAS_REF_EXPANSION
- EXPAND_ALIAS_REFS_EARLY_ONLY_IN_GROUP_BY
- SUPPORTS_ORDER_BY_ALL
- HAS_DISTINCT_ARRAY_CONSTRUCTORS
- SUPPORTS_FIXED_SIZE_ARRAYS
- STRICT_JSON_PATH_SYNTAX
- ON_CONDITION_EMPTY_BEFORE_ERROR
- ARRAY_AGG_INCLUDES_NULLS
- REGEXP_EXTRACT_DEFAULT_GROUP
- SET_OP_DISTINCT_BY_DEFAULT
- CREATABLE_KIND_MAPPING
- DATE_PART_MAPPING
- TYPE_TO_EXPRESSIONS
- get_or_raise
- format_time
- settings
- normalize_identifier
- case_sensitive
- can_identify
- quote_identifier
- to_json_path
- parse
- parse_into
- generate
- transpile
- tokenize
- tokenizer
- jsonpath_tokenizer
- parser
- generator
238 class Tokenizer(tokens.Tokenizer): 239 UNICODE_STRINGS = [ 240 (prefix + q, q) 241 for q in t.cast(t.List[str], tokens.Tokenizer.QUOTES) 242 for prefix in ("U&", "u&") 243 ] 244 245 KEYWORDS = { 246 **tokens.Tokenizer.KEYWORDS, 247 "START": TokenType.BEGIN, 248 "MATCH_RECOGNIZE": TokenType.MATCH_RECOGNIZE, 249 "ROW": TokenType.STRUCT, 250 "IPADDRESS": TokenType.IPADDRESS, 251 "IPPREFIX": TokenType.IPPREFIX, 252 "TDIGEST": TokenType.TDIGEST, 253 "HYPERLOGLOG": TokenType.HLLSKETCH, 254 } 255 KEYWORDS.pop("/*+") 256 KEYWORDS.pop("QUALIFY")
Inherited Members
- sqlglot.tokens.Tokenizer
- Tokenizer
- SINGLE_TOKENS
- BIT_STRINGS
- BYTE_STRINGS
- HEX_STRINGS
- RAW_STRINGS
- HEREDOC_STRINGS
- IDENTIFIERS
- IDENTIFIER_ESCAPES
- QUOTES
- STRING_ESCAPES
- VAR_SINGLE_TOKENS
- HEREDOC_TAG_IS_IDENTIFIER
- HEREDOC_STRING_ALTERNATIVE
- STRING_ESCAPES_ALLOWED_IN_RAW_STRINGS
- NESTED_COMMENTS
- WHITE_SPACE
- COMMANDS
- COMMAND_PREFIX_TOKENS
- NUMERIC_LITERALS
- COMMENTS
- dialect
- reset
- tokenize
- tokenize_rs
- size
- sql
- tokens
258 class Parser(parser.Parser): 259 VALUES_FOLLOWED_BY_PAREN = False 260 261 FUNCTIONS = { 262 **parser.Parser.FUNCTIONS, 263 "ARBITRARY": exp.AnyValue.from_arg_list, 264 "APPROX_DISTINCT": exp.ApproxDistinct.from_arg_list, 265 "APPROX_PERCENTILE": _build_approx_percentile, 266 "BITWISE_AND": binary_from_function(exp.BitwiseAnd), 267 "BITWISE_NOT": lambda args: exp.BitwiseNot(this=seq_get(args, 0)), 268 "BITWISE_OR": binary_from_function(exp.BitwiseOr), 269 "BITWISE_XOR": binary_from_function(exp.BitwiseXor), 270 "CARDINALITY": exp.ArraySize.from_arg_list, 271 "CONTAINS": exp.ArrayContains.from_arg_list, 272 "DATE_ADD": lambda args: exp.DateAdd( 273 this=seq_get(args, 2), expression=seq_get(args, 1), unit=seq_get(args, 0) 274 ), 275 "DATE_DIFF": lambda args: exp.DateDiff( 276 this=seq_get(args, 2), expression=seq_get(args, 1), unit=seq_get(args, 0) 277 ), 278 "DATE_FORMAT": build_formatted_time(exp.TimeToStr, "presto"), 279 "DATE_PARSE": build_formatted_time(exp.StrToTime, "presto"), 280 "DATE_TRUNC": date_trunc_to_time, 281 "DAY_OF_WEEK": exp.DayOfWeekIso.from_arg_list, 282 "ELEMENT_AT": lambda args: exp.Bracket( 283 this=seq_get(args, 0), expressions=[seq_get(args, 1)], offset=1, safe=True 284 ), 285 "FROM_HEX": exp.Unhex.from_arg_list, 286 "FROM_UNIXTIME": _build_from_unixtime, 287 "FROM_UTF8": lambda args: exp.Decode( 288 this=seq_get(args, 0), replace=seq_get(args, 1), charset=exp.Literal.string("utf-8") 289 ), 290 "NOW": exp.CurrentTimestamp.from_arg_list, 291 "REGEXP_EXTRACT": build_regexp_extract, 292 "REGEXP_REPLACE": lambda args: exp.RegexpReplace( 293 this=seq_get(args, 0), 294 expression=seq_get(args, 1), 295 replacement=seq_get(args, 2) or exp.Literal.string(""), 296 ), 297 "ROW": exp.Struct.from_arg_list, 298 "SEQUENCE": exp.GenerateSeries.from_arg_list, 299 "SET_AGG": exp.ArrayUniqueAgg.from_arg_list, 300 "SPLIT_TO_MAP": exp.StrToMap.from_arg_list, 301 "STRPOS": lambda args: exp.StrPosition( 302 this=seq_get(args, 0), substr=seq_get(args, 1), instance=seq_get(args, 2) 303 ), 304 "TO_CHAR": _build_to_char, 305 "TO_UNIXTIME": exp.TimeToUnix.from_arg_list, 306 "TO_UTF8": lambda args: exp.Encode( 307 this=seq_get(args, 0), charset=exp.Literal.string("utf-8") 308 ), 309 "MD5": exp.MD5Digest.from_arg_list, 310 "SHA256": lambda args: exp.SHA2(this=seq_get(args, 0), length=exp.Literal.number(256)), 311 "SHA512": lambda args: exp.SHA2(this=seq_get(args, 0), length=exp.Literal.number(512)), 312 } 313 314 FUNCTION_PARSERS = parser.Parser.FUNCTION_PARSERS.copy() 315 FUNCTION_PARSERS.pop("TRIM")
Parser consumes a list of tokens produced by the Tokenizer and produces a parsed syntax tree.
Arguments:
- error_level: The desired error level. Default: ErrorLevel.IMMEDIATE
- error_message_context: The amount of context to capture from a query string when displaying the error message (in number of characters). Default: 100
- max_errors: Maximum number of error messages to include in a raised ParseError. This is only relevant if error_level is ErrorLevel.RAISE. Default: 3
Inherited Members
- sqlglot.parser.Parser
- Parser
- NO_PAREN_FUNCTIONS
- STRUCT_TYPE_TOKENS
- NESTED_TYPE_TOKENS
- ENUM_TYPE_TOKENS
- AGGREGATE_TYPE_TOKENS
- TYPE_TOKENS
- SIGNED_TO_UNSIGNED_TYPE_TOKEN
- SUBQUERY_PREDICATES
- RESERVED_TOKENS
- DB_CREATABLES
- CREATABLES
- ALTERABLES
- INTERVAL_VARS
- ALIAS_TOKENS
- ARRAY_CONSTRUCTORS
- COMMENT_TABLE_ALIAS_TOKENS
- UPDATE_ALIAS_TOKENS
- TRIM_TYPES
- FUNC_TOKENS
- CONJUNCTION
- ASSIGNMENT
- DISJUNCTION
- EQUALITY
- COMPARISON
- BITWISE
- TERM
- FACTOR
- EXPONENT
- TIMES
- TIMESTAMPS
- SET_OPERATIONS
- JOIN_METHODS
- JOIN_SIDES
- JOIN_KINDS
- JOIN_HINTS
- LAMBDAS
- COLUMN_OPERATORS
- EXPRESSION_PARSERS
- STATEMENT_PARSERS
- UNARY_PARSERS
- STRING_PARSERS
- NUMERIC_PARSERS
- PRIMARY_PARSERS
- PLACEHOLDER_PARSERS
- RANGE_PARSERS
- PROPERTY_PARSERS
- CONSTRAINT_PARSERS
- ALTER_PARSERS
- ALTER_ALTER_PARSERS
- SCHEMA_UNNAMED_CONSTRAINTS
- NO_PAREN_FUNCTION_PARSERS
- INVALID_FUNC_NAME_TOKENS
- FUNCTIONS_WITH_ALIASED_ARGS
- KEY_VALUE_DEFINITIONS
- QUERY_MODIFIER_PARSERS
- SET_PARSERS
- SHOW_PARSERS
- TYPE_LITERAL_PARSERS
- TYPE_CONVERTERS
- DDL_SELECT_TOKENS
- PRE_VOLATILE_TOKENS
- TRANSACTION_KIND
- TRANSACTION_CHARACTERISTICS
- CONFLICT_ACTIONS
- CREATE_SEQUENCE
- ISOLATED_LOADING_OPTIONS
- USABLES
- CAST_ACTIONS
- SCHEMA_BINDING_OPTIONS
- KEY_CONSTRAINT_OPTIONS
- INSERT_ALTERNATIVES
- CLONE_KEYWORDS
- HISTORICAL_DATA_PREFIX
- HISTORICAL_DATA_KIND
- OPCLASS_FOLLOW_KEYWORDS
- OPTYPE_FOLLOW_TOKENS
- TABLE_INDEX_HINT_TOKENS
- VIEW_ATTRIBUTES
- WINDOW_ALIAS_TOKENS
- WINDOW_BEFORE_PAREN_TOKENS
- WINDOW_SIDES
- JSON_KEY_VALUE_SEPARATOR_TOKENS
- FETCH_TOKENS
- ADD_CONSTRAINT_TOKENS
- DISTINCT_TOKENS
- NULL_TOKENS
- UNNEST_OFFSET_ALIAS_TOKENS
- SELECT_START_TOKENS
- COPY_INTO_VARLEN_OPTIONS
- IS_JSON_PREDICATE_KIND
- ODBC_DATETIME_LITERALS
- ON_CONDITION_TOKENS
- PRIVILEGE_FOLLOW_TOKENS
- DESCRIBE_STYLES
- OPERATION_MODIFIERS
- STRICT_CAST
- PREFIXED_PIVOT_COLUMNS
- IDENTIFY_PIVOT_STRINGS
- LOG_DEFAULTS_TO_LN
- ALTER_TABLE_ADD_REQUIRED_FOR_EACH_COLUMN
- TABLESAMPLE_CSV
- DEFAULT_SAMPLING_METHOD
- SET_REQUIRES_ASSIGNMENT_DELIMITER
- TRIM_PATTERN_FIRST
- STRING_ALIASES
- MODIFIERS_ATTACHED_TO_SET_OP
- SET_OP_MODIFIERS
- NO_PAREN_IF_COMMANDS
- JSON_ARROWS_REQUIRE_JSON_TYPE
- COLON_IS_VARIANT_EXTRACT
- SUPPORTS_IMPLICIT_UNNEST
- INTERVAL_SPANS
- SUPPORTS_PARTITION_SELECTION
- error_level
- error_message_context
- max_errors
- dialect
- reset
- parse
- parse_into
- check_errors
- raise_error
- expression
- validate_expression
- errors
- sql
317 class Generator(generator.Generator): 318 INTERVAL_ALLOWS_PLURAL_FORM = False 319 JOIN_HINTS = False 320 TABLE_HINTS = False 321 QUERY_HINTS = False 322 IS_BOOL_ALLOWED = False 323 TZ_TO_WITH_TIME_ZONE = True 324 NVL2_SUPPORTED = False 325 STRUCT_DELIMITER = ("(", ")") 326 LIMIT_ONLY_LITERALS = True 327 SUPPORTS_SINGLE_ARG_CONCAT = False 328 LIKE_PROPERTY_INSIDE_SCHEMA = True 329 MULTI_ARG_DISTINCT = False 330 SUPPORTS_TO_NUMBER = False 331 HEX_FUNC = "TO_HEX" 332 PARSE_JSON_NAME = "JSON_PARSE" 333 PAD_FILL_PATTERN_IS_REQUIRED = True 334 EXCEPT_INTERSECT_SUPPORT_ALL_CLAUSE = False 335 336 PROPERTIES_LOCATION = { 337 **generator.Generator.PROPERTIES_LOCATION, 338 exp.LocationProperty: exp.Properties.Location.UNSUPPORTED, 339 exp.VolatileProperty: exp.Properties.Location.UNSUPPORTED, 340 } 341 342 TYPE_MAPPING = { 343 **generator.Generator.TYPE_MAPPING, 344 exp.DataType.Type.INT: "INTEGER", 345 exp.DataType.Type.FLOAT: "REAL", 346 exp.DataType.Type.BINARY: "VARBINARY", 347 exp.DataType.Type.TEXT: "VARCHAR", 348 exp.DataType.Type.TIMETZ: "TIME", 349 exp.DataType.Type.TIMESTAMPTZ: "TIMESTAMP", 350 exp.DataType.Type.STRUCT: "ROW", 351 exp.DataType.Type.DATETIME: "TIMESTAMP", 352 exp.DataType.Type.DATETIME64: "TIMESTAMP", 353 exp.DataType.Type.HLLSKETCH: "HYPERLOGLOG", 354 } 355 356 TRANSFORMS = { 357 **generator.Generator.TRANSFORMS, 358 exp.AnyValue: rename_func("ARBITRARY"), 359 exp.ApproxQuantile: rename_func("APPROX_PERCENTILE"), 360 exp.ArgMax: rename_func("MAX_BY"), 361 exp.ArgMin: rename_func("MIN_BY"), 362 exp.Array: lambda self, e: f"ARRAY[{self.expressions(e, flat=True)}]", 363 exp.ArrayAny: rename_func("ANY_MATCH"), 364 exp.ArrayConcat: rename_func("CONCAT"), 365 exp.ArrayContains: rename_func("CONTAINS"), 366 exp.ArraySize: rename_func("CARDINALITY"), 367 exp.ArrayToString: rename_func("ARRAY_JOIN"), 368 exp.ArrayUniqueAgg: rename_func("SET_AGG"), 369 exp.AtTimeZone: rename_func("AT_TIMEZONE"), 370 exp.BitwiseAnd: lambda self, e: self.func("BITWISE_AND", e.this, e.expression), 371 exp.BitwiseLeftShift: lambda self, e: self.func( 372 "BITWISE_ARITHMETIC_SHIFT_LEFT", e.this, e.expression 373 ), 374 exp.BitwiseNot: lambda self, e: self.func("BITWISE_NOT", e.this), 375 exp.BitwiseOr: lambda self, e: self.func("BITWISE_OR", e.this, e.expression), 376 exp.BitwiseRightShift: lambda self, e: self.func( 377 "BITWISE_ARITHMETIC_SHIFT_RIGHT", e.this, e.expression 378 ), 379 exp.BitwiseXor: lambda self, e: self.func("BITWISE_XOR", e.this, e.expression), 380 exp.Cast: transforms.preprocess([transforms.epoch_cast_to_ts]), 381 exp.CurrentTimestamp: lambda *_: "CURRENT_TIMESTAMP", 382 exp.DateAdd: _date_delta_sql("DATE_ADD"), 383 exp.DateDiff: lambda self, e: self.func( 384 "DATE_DIFF", unit_to_str(e), e.expression, e.this 385 ), 386 exp.DateStrToDate: datestrtodate_sql, 387 exp.DateToDi: lambda self, 388 e: f"CAST(DATE_FORMAT({self.sql(e, 'this')}, {Presto.DATEINT_FORMAT}) AS INT)", 389 exp.DateSub: _date_delta_sql("DATE_ADD", negate_interval=True), 390 exp.DayOfWeek: lambda self, e: f"(({self.func('DAY_OF_WEEK', e.this)} % 7) + 1)", 391 exp.DayOfWeekIso: rename_func("DAY_OF_WEEK"), 392 exp.Decode: lambda self, e: encode_decode_sql(self, e, "FROM_UTF8"), 393 exp.DiToDate: lambda self, 394 e: f"CAST(DATE_PARSE(CAST({self.sql(e, 'this')} AS VARCHAR), {Presto.DATEINT_FORMAT}) AS DATE)", 395 exp.Encode: lambda self, e: encode_decode_sql(self, e, "TO_UTF8"), 396 exp.FileFormatProperty: lambda self, e: f"FORMAT='{e.name.upper()}'", 397 exp.First: _first_last_sql, 398 exp.FirstValue: _first_last_sql, 399 exp.FromTimeZone: lambda self, 400 e: f"WITH_TIMEZONE({self.sql(e, 'this')}, {self.sql(e, 'zone')}) AT TIME ZONE 'UTC'", 401 exp.GenerateSeries: sequence_sql, 402 exp.GenerateDateArray: sequence_sql, 403 exp.Group: transforms.preprocess([transforms.unalias_group]), 404 exp.GroupConcat: lambda self, e: self.func( 405 "ARRAY_JOIN", self.func("ARRAY_AGG", e.this), e.args.get("separator") 406 ), 407 exp.If: if_sql(), 408 exp.ILike: no_ilike_sql, 409 exp.Initcap: _initcap_sql, 410 exp.JSONExtract: lambda self, e: self.jsonextract_sql(e), 411 exp.Last: _first_last_sql, 412 exp.LastValue: _first_last_sql, 413 exp.LastDay: lambda self, e: self.func("LAST_DAY_OF_MONTH", e.this), 414 exp.Lateral: _explode_to_unnest_sql, 415 exp.Left: left_to_substring_sql, 416 exp.Levenshtein: rename_func("LEVENSHTEIN_DISTANCE"), 417 exp.LogicalAnd: rename_func("BOOL_AND"), 418 exp.LogicalOr: rename_func("BOOL_OR"), 419 exp.Pivot: no_pivot_sql, 420 exp.Quantile: _quantile_sql, 421 exp.RegexpExtract: regexp_extract_sql, 422 exp.Right: right_to_substring_sql, 423 exp.SafeDivide: no_safe_divide_sql, 424 exp.Schema: _schema_sql, 425 exp.SchemaCommentProperty: lambda self, e: self.naked_property(e), 426 exp.Select: transforms.preprocess( 427 [ 428 transforms.eliminate_qualify, 429 transforms.eliminate_distinct_on, 430 transforms.explode_to_unnest(1), 431 transforms.eliminate_semi_and_anti_joins, 432 ] 433 ), 434 exp.SortArray: _no_sort_array, 435 exp.StrPosition: lambda self, e: str_position_sql(self, e, generate_instance=True), 436 exp.StrToDate: lambda self, e: f"CAST({_str_to_time_sql(self, e)} AS DATE)", 437 exp.StrToMap: rename_func("SPLIT_TO_MAP"), 438 exp.StrToTime: _str_to_time_sql, 439 exp.StructExtract: struct_extract_sql, 440 exp.Table: transforms.preprocess([transforms.unnest_generate_series]), 441 exp.Timestamp: no_timestamp_sql, 442 exp.TimestampAdd: _date_delta_sql("DATE_ADD"), 443 exp.TimestampTrunc: timestamptrunc_sql(), 444 exp.TimeStrToDate: timestrtotime_sql, 445 exp.TimeStrToTime: timestrtotime_sql, 446 exp.TimeStrToUnix: lambda self, e: self.func( 447 "TO_UNIXTIME", self.func("DATE_PARSE", e.this, Presto.TIME_FORMAT) 448 ), 449 exp.TimeToStr: lambda self, e: self.func("DATE_FORMAT", e.this, self.format_time(e)), 450 exp.TimeToUnix: rename_func("TO_UNIXTIME"), 451 exp.ToChar: lambda self, e: self.func("DATE_FORMAT", e.this, self.format_time(e)), 452 exp.TryCast: transforms.preprocess([transforms.epoch_cast_to_ts]), 453 exp.TsOrDiToDi: lambda self, 454 e: f"CAST(SUBSTR(REPLACE(CAST({self.sql(e, 'this')} AS VARCHAR), '-', ''), 1, 8) AS INT)", 455 exp.TsOrDsAdd: _ts_or_ds_add_sql, 456 exp.TsOrDsDiff: _ts_or_ds_diff_sql, 457 exp.TsOrDsToDate: _ts_or_ds_to_date_sql, 458 exp.Unhex: rename_func("FROM_HEX"), 459 exp.UnixToStr: lambda self, 460 e: f"DATE_FORMAT(FROM_UNIXTIME({self.sql(e, 'this')}), {self.format_time(e)})", 461 exp.UnixToTime: _unix_to_time_sql, 462 exp.UnixToTimeStr: lambda self, 463 e: f"CAST(FROM_UNIXTIME({self.sql(e, 'this')}) AS VARCHAR)", 464 exp.VariancePop: rename_func("VAR_POP"), 465 exp.With: transforms.preprocess([transforms.add_recursive_cte_column_names]), 466 exp.WithinGroup: transforms.preprocess( 467 [transforms.remove_within_group_for_percentiles] 468 ), 469 exp.Xor: bool_xor_sql, 470 exp.MD5Digest: rename_func("MD5"), 471 exp.SHA: rename_func("SHA1"), 472 exp.SHA2: sha256_sql, 473 } 474 475 RESERVED_KEYWORDS = { 476 "alter", 477 "and", 478 "as", 479 "between", 480 "by", 481 "case", 482 "cast", 483 "constraint", 484 "create", 485 "cross", 486 "current_time", 487 "current_timestamp", 488 "deallocate", 489 "delete", 490 "describe", 491 "distinct", 492 "drop", 493 "else", 494 "end", 495 "escape", 496 "except", 497 "execute", 498 "exists", 499 "extract", 500 "false", 501 "for", 502 "from", 503 "full", 504 "group", 505 "having", 506 "in", 507 "inner", 508 "insert", 509 "intersect", 510 "into", 511 "is", 512 "join", 513 "left", 514 "like", 515 "natural", 516 "not", 517 "null", 518 "on", 519 "or", 520 "order", 521 "outer", 522 "prepare", 523 "right", 524 "select", 525 "table", 526 "then", 527 "true", 528 "union", 529 "using", 530 "values", 531 "when", 532 "where", 533 "with", 534 } 535 536 def md5_sql(self, expression: exp.MD5) -> str: 537 this = expression.this 538 539 if not this.type: 540 from sqlglot.optimizer.annotate_types import annotate_types 541 542 this = annotate_types(this) 543 544 if this.is_type(*exp.DataType.TEXT_TYPES): 545 this = exp.Encode(this=this, charset=exp.Literal.string("utf-8")) 546 547 return self.func("LOWER", self.func("TO_HEX", self.func("MD5", self.sql(this)))) 548 549 def strtounix_sql(self, expression: exp.StrToUnix) -> str: 550 # Since `TO_UNIXTIME` requires a `TIMESTAMP`, we need to parse the argument into one. 551 # To do this, we first try to `DATE_PARSE` it, but since this can fail when there's a 552 # timezone involved, we wrap it in a `TRY` call and use `PARSE_DATETIME` as a fallback, 553 # which seems to be using the same time mapping as Hive, as per: 554 # https://joda-time.sourceforge.net/apidocs/org/joda/time/format/DateTimeFormat.html 555 this = expression.this 556 value_as_text = exp.cast(this, exp.DataType.Type.TEXT) 557 value_as_timestamp = ( 558 exp.cast(this, exp.DataType.Type.TIMESTAMP) if this.is_string else this 559 ) 560 561 parse_without_tz = self.func("DATE_PARSE", value_as_text, self.format_time(expression)) 562 563 formatted_value = self.func( 564 "DATE_FORMAT", value_as_timestamp, self.format_time(expression) 565 ) 566 parse_with_tz = self.func( 567 "PARSE_DATETIME", 568 formatted_value, 569 self.format_time(expression, Hive.INVERSE_TIME_MAPPING, Hive.INVERSE_TIME_TRIE), 570 ) 571 coalesced = self.func("COALESCE", self.func("TRY", parse_without_tz), parse_with_tz) 572 return self.func("TO_UNIXTIME", coalesced) 573 574 def bracket_sql(self, expression: exp.Bracket) -> str: 575 if expression.args.get("safe"): 576 return self.func( 577 "ELEMENT_AT", 578 expression.this, 579 seq_get( 580 apply_index_offset( 581 expression.this, 582 expression.expressions, 583 1 - expression.args.get("offset", 0), 584 ), 585 0, 586 ), 587 ) 588 return super().bracket_sql(expression) 589 590 def struct_sql(self, expression: exp.Struct) -> str: 591 from sqlglot.optimizer.annotate_types import annotate_types 592 593 expression = annotate_types(expression) 594 values: t.List[str] = [] 595 schema: t.List[str] = [] 596 unknown_type = False 597 598 for e in expression.expressions: 599 if isinstance(e, exp.PropertyEQ): 600 if e.type and e.type.is_type(exp.DataType.Type.UNKNOWN): 601 unknown_type = True 602 else: 603 schema.append(f"{self.sql(e, 'this')} {self.sql(e.type)}") 604 values.append(self.sql(e, "expression")) 605 else: 606 values.append(self.sql(e)) 607 608 size = len(expression.expressions) 609 610 if not size or len(schema) != size: 611 if unknown_type: 612 self.unsupported( 613 "Cannot convert untyped key-value definitions (try annotate_types)." 614 ) 615 return self.func("ROW", *values) 616 return f"CAST(ROW({', '.join(values)}) AS ROW({', '.join(schema)}))" 617 618 def interval_sql(self, expression: exp.Interval) -> str: 619 if expression.this and expression.text("unit").upper().startswith("WEEK"): 620 return f"({expression.this.name} * INTERVAL '7' DAY)" 621 return super().interval_sql(expression) 622 623 def transaction_sql(self, expression: exp.Transaction) -> str: 624 modes = expression.args.get("modes") 625 modes = f" {', '.join(modes)}" if modes else "" 626 return f"START TRANSACTION{modes}" 627 628 def offset_limit_modifiers( 629 self, expression: exp.Expression, fetch: bool, limit: t.Optional[exp.Fetch | exp.Limit] 630 ) -> t.List[str]: 631 return [ 632 self.sql(expression, "offset"), 633 self.sql(limit), 634 ] 635 636 def create_sql(self, expression: exp.Create) -> str: 637 """ 638 Presto doesn't support CREATE VIEW with expressions (ex: `CREATE VIEW x (cola)` then `(cola)` is the expression), 639 so we need to remove them 640 """ 641 kind = expression.args["kind"] 642 schema = expression.this 643 if kind == "VIEW" and schema.expressions: 644 expression.this.set("expressions", None) 645 return super().create_sql(expression) 646 647 def delete_sql(self, expression: exp.Delete) -> str: 648 """ 649 Presto only supports DELETE FROM for a single table without an alias, so we need 650 to remove the unnecessary parts. If the original DELETE statement contains more 651 than one table to be deleted, we can't safely map it 1-1 to a Presto statement. 652 """ 653 tables = expression.args.get("tables") or [expression.this] 654 if len(tables) > 1: 655 return super().delete_sql(expression) 656 657 table = tables[0] 658 expression.set("this", table) 659 expression.set("tables", None) 660 661 if isinstance(table, exp.Table): 662 table_alias = table.args.get("alias") 663 if table_alias: 664 table_alias.pop() 665 expression = t.cast(exp.Delete, expression.transform(unqualify_columns)) 666 667 return super().delete_sql(expression) 668 669 def jsonextract_sql(self, expression: exp.JSONExtract) -> str: 670 is_json_extract = self.dialect.settings.get("variant_extract_is_json_extract", True) 671 672 # Generate JSON_EXTRACT unless the user has configured that a Snowflake / Databricks 673 # VARIANT extract (e.g. col:x.y) should map to dot notation (i.e ROW access) in Presto/Trino 674 if not expression.args.get("variant_extract") or is_json_extract: 675 return self.func( 676 "JSON_EXTRACT", expression.this, expression.expression, *expression.expressions 677 ) 678 679 this = self.sql(expression, "this") 680 681 # Convert the JSONPath extraction `JSON_EXTRACT(col, '$.x.y) to a ROW access col.x.y 682 segments = [] 683 for path_key in expression.expression.expressions[1:]: 684 if not isinstance(path_key, exp.JSONPathKey): 685 # Cannot transpile subscripts, wildcards etc to dot notation 686 self.unsupported( 687 f"Cannot transpile JSONPath segment '{path_key}' to ROW access" 688 ) 689 continue 690 key = path_key.this 691 if not exp.SAFE_IDENTIFIER_RE.match(key): 692 key = f'"{key}"' 693 segments.append(f".{key}") 694 695 expr = "".join(segments) 696 697 return f"{this}{expr}"
Generator converts a given syntax tree to the corresponding SQL string.
Arguments:
- pretty: Whether to format the produced SQL string. Default: False.
- identify: Determines when an identifier should be quoted. Possible values are: False (default): Never quote, except in cases where it's mandatory by the dialect. True or 'always': Always quote. 'safe': Only quote identifiers that are case insensitive.
- normalize: Whether to normalize identifiers to lowercase. Default: False.
- pad: The pad size in a formatted string. For example, this affects the indentation of a projection in a query, relative to its nesting level. Default: 2.
- indent: The indentation size in a formatted string. For example, this affects the
indentation of subqueries and filters under a
WHERE
clause. Default: 2. - normalize_functions: How to normalize function names. Possible values are: "upper" or True (default): Convert names to uppercase. "lower": Convert names to lowercase. False: Disables function name normalization.
- unsupported_level: Determines the generator's behavior when it encounters unsupported expressions. Default ErrorLevel.WARN.
- max_unsupported: Maximum number of unsupported messages to include in a raised UnsupportedError. This is only relevant if unsupported_level is ErrorLevel.RAISE. Default: 3
- leading_comma: Whether the comma is leading or trailing in select expressions. This is only relevant when generating in pretty mode. Default: False
- max_text_width: The max number of characters in a segment before creating new lines in pretty mode. The default is on the smaller end because the length only represents a segment and not the true line length. Default: 80
- comments: Whether to preserve comments in the output SQL code. Default: True
536 def md5_sql(self, expression: exp.MD5) -> str: 537 this = expression.this 538 539 if not this.type: 540 from sqlglot.optimizer.annotate_types import annotate_types 541 542 this = annotate_types(this) 543 544 if this.is_type(*exp.DataType.TEXT_TYPES): 545 this = exp.Encode(this=this, charset=exp.Literal.string("utf-8")) 546 547 return self.func("LOWER", self.func("TO_HEX", self.func("MD5", self.sql(this))))
549 def strtounix_sql(self, expression: exp.StrToUnix) -> str: 550 # Since `TO_UNIXTIME` requires a `TIMESTAMP`, we need to parse the argument into one. 551 # To do this, we first try to `DATE_PARSE` it, but since this can fail when there's a 552 # timezone involved, we wrap it in a `TRY` call and use `PARSE_DATETIME` as a fallback, 553 # which seems to be using the same time mapping as Hive, as per: 554 # https://joda-time.sourceforge.net/apidocs/org/joda/time/format/DateTimeFormat.html 555 this = expression.this 556 value_as_text = exp.cast(this, exp.DataType.Type.TEXT) 557 value_as_timestamp = ( 558 exp.cast(this, exp.DataType.Type.TIMESTAMP) if this.is_string else this 559 ) 560 561 parse_without_tz = self.func("DATE_PARSE", value_as_text, self.format_time(expression)) 562 563 formatted_value = self.func( 564 "DATE_FORMAT", value_as_timestamp, self.format_time(expression) 565 ) 566 parse_with_tz = self.func( 567 "PARSE_DATETIME", 568 formatted_value, 569 self.format_time(expression, Hive.INVERSE_TIME_MAPPING, Hive.INVERSE_TIME_TRIE), 570 ) 571 coalesced = self.func("COALESCE", self.func("TRY", parse_without_tz), parse_with_tz) 572 return self.func("TO_UNIXTIME", coalesced)
574 def bracket_sql(self, expression: exp.Bracket) -> str: 575 if expression.args.get("safe"): 576 return self.func( 577 "ELEMENT_AT", 578 expression.this, 579 seq_get( 580 apply_index_offset( 581 expression.this, 582 expression.expressions, 583 1 - expression.args.get("offset", 0), 584 ), 585 0, 586 ), 587 ) 588 return super().bracket_sql(expression)
590 def struct_sql(self, expression: exp.Struct) -> str: 591 from sqlglot.optimizer.annotate_types import annotate_types 592 593 expression = annotate_types(expression) 594 values: t.List[str] = [] 595 schema: t.List[str] = [] 596 unknown_type = False 597 598 for e in expression.expressions: 599 if isinstance(e, exp.PropertyEQ): 600 if e.type and e.type.is_type(exp.DataType.Type.UNKNOWN): 601 unknown_type = True 602 else: 603 schema.append(f"{self.sql(e, 'this')} {self.sql(e.type)}") 604 values.append(self.sql(e, "expression")) 605 else: 606 values.append(self.sql(e)) 607 608 size = len(expression.expressions) 609 610 if not size or len(schema) != size: 611 if unknown_type: 612 self.unsupported( 613 "Cannot convert untyped key-value definitions (try annotate_types)." 614 ) 615 return self.func("ROW", *values) 616 return f"CAST(ROW({', '.join(values)}) AS ROW({', '.join(schema)}))"
636 def create_sql(self, expression: exp.Create) -> str: 637 """ 638 Presto doesn't support CREATE VIEW with expressions (ex: `CREATE VIEW x (cola)` then `(cola)` is the expression), 639 so we need to remove them 640 """ 641 kind = expression.args["kind"] 642 schema = expression.this 643 if kind == "VIEW" and schema.expressions: 644 expression.this.set("expressions", None) 645 return super().create_sql(expression)
Presto doesn't support CREATE VIEW with expressions (ex: CREATE VIEW x (cola)
then (cola)
is the expression),
so we need to remove them
647 def delete_sql(self, expression: exp.Delete) -> str: 648 """ 649 Presto only supports DELETE FROM for a single table without an alias, so we need 650 to remove the unnecessary parts. If the original DELETE statement contains more 651 than one table to be deleted, we can't safely map it 1-1 to a Presto statement. 652 """ 653 tables = expression.args.get("tables") or [expression.this] 654 if len(tables) > 1: 655 return super().delete_sql(expression) 656 657 table = tables[0] 658 expression.set("this", table) 659 expression.set("tables", None) 660 661 if isinstance(table, exp.Table): 662 table_alias = table.args.get("alias") 663 if table_alias: 664 table_alias.pop() 665 expression = t.cast(exp.Delete, expression.transform(unqualify_columns)) 666 667 return super().delete_sql(expression)
Presto only supports DELETE FROM for a single table without an alias, so we need to remove the unnecessary parts. If the original DELETE statement contains more than one table to be deleted, we can't safely map it 1-1 to a Presto statement.
669 def jsonextract_sql(self, expression: exp.JSONExtract) -> str: 670 is_json_extract = self.dialect.settings.get("variant_extract_is_json_extract", True) 671 672 # Generate JSON_EXTRACT unless the user has configured that a Snowflake / Databricks 673 # VARIANT extract (e.g. col:x.y) should map to dot notation (i.e ROW access) in Presto/Trino 674 if not expression.args.get("variant_extract") or is_json_extract: 675 return self.func( 676 "JSON_EXTRACT", expression.this, expression.expression, *expression.expressions 677 ) 678 679 this = self.sql(expression, "this") 680 681 # Convert the JSONPath extraction `JSON_EXTRACT(col, '$.x.y) to a ROW access col.x.y 682 segments = [] 683 for path_key in expression.expression.expressions[1:]: 684 if not isinstance(path_key, exp.JSONPathKey): 685 # Cannot transpile subscripts, wildcards etc to dot notation 686 self.unsupported( 687 f"Cannot transpile JSONPath segment '{path_key}' to ROW access" 688 ) 689 continue 690 key = path_key.this 691 if not exp.SAFE_IDENTIFIER_RE.match(key): 692 key = f'"{key}"' 693 segments.append(f".{key}") 694 695 expr = "".join(segments) 696 697 return f"{this}{expr}"
Inherited Members
- sqlglot.generator.Generator
- Generator
- NULL_ORDERING_SUPPORTED
- IGNORE_NULLS_IN_FUNC
- LOCKING_READS_SUPPORTED
- WRAP_DERIVED_VALUES
- CREATE_FUNCTION_RETURN_AS
- MATCHED_BY_SOURCE
- SINGLE_STRING_INTERVAL
- LIMIT_FETCH
- RENAME_TABLE_WITH_DB
- GROUPINGS_SEP
- INDEX_ON
- QUERY_HINT_SEP
- DUPLICATE_KEY_UPDATE_WITH_SET
- LIMIT_IS_TOP
- RETURNING_END
- EXTRACT_ALLOWS_QUOTES
- VALUES_AS_TABLE
- ALTER_TABLE_INCLUDE_COLUMN_KEYWORD
- UNNEST_WITH_ORDINALITY
- AGGREGATE_FILTER_SUPPORTED
- SEMI_ANTI_JOIN_WITH_SIDE
- COMPUTED_COLUMN_WITH_TYPE
- SUPPORTS_TABLE_COPY
- TABLESAMPLE_REQUIRES_PARENS
- TABLESAMPLE_SIZE_IS_ROWS
- TABLESAMPLE_KEYWORDS
- TABLESAMPLE_WITH_METHOD
- TABLESAMPLE_SEED_KEYWORD
- COLLATE_IS_FUNC
- DATA_TYPE_SPECIFIERS_ALLOWED
- ENSURE_BOOLS
- CTE_RECURSIVE_KEYWORD_REQUIRED
- LAST_DAY_SUPPORTS_DATE_PART
- SUPPORTS_TABLE_ALIAS_COLUMNS
- UNPIVOT_ALIASES_ARE_IDENTIFIERS
- JSON_KEY_VALUE_PAIR_SEP
- INSERT_OVERWRITE
- SUPPORTS_SELECT_INTO
- SUPPORTS_UNLOGGED_TABLES
- SUPPORTS_CREATE_TABLE_LIKE
- JSON_TYPE_REQUIRED_FOR_EXTRACTION
- JSON_PATH_BRACKETED_KEY_SUPPORTED
- JSON_PATH_SINGLE_QUOTE_ESCAPE
- SUPPORTED_JSON_PATH_PARTS
- CAN_IMPLEMENT_ARRAY_ANY
- SET_OP_MODIFIERS
- COPY_PARAMS_ARE_WRAPPED
- COPY_PARAMS_EQ_REQUIRED
- COPY_HAS_INTO_KEYWORD
- TRY_SUPPORTED
- SUPPORTS_UESCAPE
- STAR_EXCEPT
- WITH_PROPERTIES_PREFIX
- QUOTE_JSON_PATH
- SUPPORTS_EXPLODING_PROJECTIONS
- ARRAY_CONCAT_IS_VAR_LEN
- SUPPORTS_CONVERT_TIMEZONE
- TIME_PART_SINGULARS
- TOKEN_MAPPING
- PARAMETER_TOKEN
- NAMED_PLACEHOLDER_TOKEN
- WITH_SEPARATED_COMMENTS
- EXCLUDE_COMMENTS
- UNWRAPPED_INTERVAL_VALUES
- PARAMETERIZABLE_TEXT_TYPES
- EXPRESSIONS_WITHOUT_NESTED_CTES
- SENTINEL_LINE_BREAK
- pretty
- identify
- normalize
- pad
- unsupported_level
- max_unsupported
- leading_comma
- max_text_width
- comments
- dialect
- normalize_functions
- unsupported_messages
- generate
- preprocess
- unsupported
- sep
- seg
- pad_comment
- maybe_comment
- wrap
- no_identify
- normalize_func
- indent
- sql
- uncache_sql
- cache_sql
- characterset_sql
- column_parts
- column_sql
- columnposition_sql
- columndef_sql
- columnconstraint_sql
- computedcolumnconstraint_sql
- autoincrementcolumnconstraint_sql
- compresscolumnconstraint_sql
- generatedasidentitycolumnconstraint_sql
- generatedasrowcolumnconstraint_sql
- periodforsystemtimeconstraint_sql
- notnullcolumnconstraint_sql
- transformcolumnconstraint_sql
- primarykeycolumnconstraint_sql
- uniquecolumnconstraint_sql
- createable_sql
- sequenceproperties_sql
- clone_sql
- describe_sql
- heredoc_sql
- prepend_ctes
- with_sql
- cte_sql
- tablealias_sql
- bitstring_sql
- hexstring_sql
- bytestring_sql
- unicodestring_sql
- rawstring_sql
- datatypeparam_sql
- datatype_sql
- directory_sql
- drop_sql
- set_operation
- set_operations
- fetch_sql
- filter_sql
- hint_sql
- indexparameters_sql
- index_sql
- identifier_sql
- hex_sql
- lowerhex_sql
- inputoutputformat_sql
- national_sql
- partition_sql
- properties_sql
- root_properties
- properties
- with_properties
- locate_properties
- property_name
- property_sql
- likeproperty_sql
- fallbackproperty_sql
- journalproperty_sql
- freespaceproperty_sql
- checksumproperty_sql
- mergeblockratioproperty_sql
- datablocksizeproperty_sql
- blockcompressionproperty_sql
- isolatedloadingproperty_sql
- partitionboundspec_sql
- partitionedofproperty_sql
- lockingproperty_sql
- withdataproperty_sql
- withsystemversioningproperty_sql
- insert_sql
- introducer_sql
- kill_sql
- pseudotype_sql
- objectidentifier_sql
- onconflict_sql
- returning_sql
- rowformatdelimitedproperty_sql
- withtablehint_sql
- indextablehint_sql
- historicaldata_sql
- table_parts
- table_sql
- tablesample_sql
- pivot_sql
- version_sql
- tuple_sql
- update_sql
- values_sql
- var_sql
- into_sql
- from_sql
- groupingsets_sql
- rollup_sql
- cube_sql
- group_sql
- having_sql
- connect_sql
- prior_sql
- join_sql
- lambda_sql
- lateral_op
- lateral_sql
- limit_sql
- offset_sql
- setitem_sql
- set_sql
- pragma_sql
- lock_sql
- literal_sql
- escape_str
- loaddata_sql
- null_sql
- boolean_sql
- order_sql
- withfill_sql
- cluster_sql
- distribute_sql
- sort_sql
- ordered_sql
- matchrecognizemeasure_sql
- matchrecognize_sql
- query_modifiers
- options_modifier
- queryoption_sql
- after_limit_modifiers
- select_sql
- schema_sql
- schema_columns_sql
- star_sql
- parameter_sql
- sessionparameter_sql
- placeholder_sql
- subquery_sql
- qualify_sql
- unnest_sql
- prewhere_sql
- where_sql
- window_sql
- partition_by_sql
- windowspec_sql
- withingroup_sql
- between_sql
- bracket_offset_expressions
- all_sql
- any_sql
- exists_sql
- case_sql
- constraint_sql
- nextvaluefor_sql
- extract_sql
- trim_sql
- convert_concat_args
- concat_sql
- concatws_sql
- check_sql
- foreignkey_sql
- primarykey_sql
- if_sql
- matchagainst_sql
- jsonkeyvalue_sql
- jsonpath_sql
- json_path_part
- formatjson_sql
- jsonobject_sql
- jsonobjectagg_sql
- jsonarray_sql
- jsonarrayagg_sql
- jsoncolumndef_sql
- jsonschema_sql
- jsontable_sql
- openjsoncolumndef_sql
- openjson_sql
- in_sql
- in_unnest_op
- return_sql
- reference_sql
- anonymous_sql
- paren_sql
- neg_sql
- not_sql
- alias_sql
- pivotalias_sql
- aliases_sql
- atindex_sql
- attimezone_sql
- fromtimezone_sql
- add_sql
- and_sql
- or_sql
- xor_sql
- connector_sql
- bitwiseand_sql
- bitwiseleftshift_sql
- bitwisenot_sql
- bitwiseor_sql
- bitwiserightshift_sql
- bitwisexor_sql
- cast_sql
- currentdate_sql
- collate_sql
- command_sql
- comment_sql
- mergetreettlaction_sql
- mergetreettl_sql
- commit_sql
- rollback_sql
- altercolumn_sql
- alterdiststyle_sql
- altersortkey_sql
- alterrename_sql
- renamecolumn_sql
- alterset_sql
- alter_sql
- add_column_sql
- droppartition_sql
- addconstraint_sql
- distinct_sql
- ignorenulls_sql
- respectnulls_sql
- havingmax_sql
- intdiv_sql
- dpipe_sql
- div_sql
- overlaps_sql
- distance_sql
- dot_sql
- eq_sql
- propertyeq_sql
- escape_sql
- glob_sql
- gt_sql
- gte_sql
- ilike_sql
- ilikeany_sql
- is_sql
- like_sql
- likeany_sql
- similarto_sql
- lt_sql
- lte_sql
- mod_sql
- mul_sql
- neq_sql
- nullsafeeq_sql
- nullsafeneq_sql
- slice_sql
- sub_sql
- trycast_sql
- try_sql
- log_sql
- use_sql
- binary
- function_fallback_sql
- func
- format_args
- too_wide
- format_time
- expressions
- op_expressions
- naked_property
- tag_sql
- token_sql
- userdefinedfunction_sql
- joinhint_sql
- kwarg_sql
- when_sql
- merge_sql
- tochar_sql
- tonumber_sql
- dictproperty_sql
- dictrange_sql
- dictsubproperty_sql
- duplicatekeyproperty_sql
- distributedbyproperty_sql
- oncluster_sql
- clusteredbyproperty_sql
- anyvalue_sql
- querytransform_sql
- indexconstraintoption_sql
- checkcolumnconstraint_sql
- indexcolumnconstraint_sql
- nvl2_sql
- comprehension_sql
- columnprefix_sql
- opclass_sql
- predict_sql
- forin_sql
- refresh_sql
- toarray_sql
- tsordstotime_sql
- tsordstotimestamp_sql
- tsordstodate_sql
- unixdate_sql
- lastday_sql
- dateadd_sql
- arrayany_sql
- partitionrange_sql
- truncatetable_sql
- convert_sql
- copyparameter_sql
- credentials_sql
- copy_sql
- semicolon_sql
- datadeletionproperty_sql
- maskingpolicycolumnconstraint_sql
- gapfill_sql
- scope_resolution
- scoperesolution_sql
- parsejson_sql
- rand_sql
- changes_sql
- pad_sql
- summarize_sql
- explodinggenerateseries_sql
- arrayconcat_sql
- converttimezone_sql
- json_sql
- jsonvalue_sql
- conditionalinsert_sql
- multitableinserts_sql
- oncondition_sql
- jsonexists_sql
- arrayagg_sql
- apply_sql
- grant_sql
- grantprivilege_sql
- grantprincipal_sql
- columns_sql
- overlay_sql