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 explode_to_unnest_sql, 34) 35from sqlglot.dialects.hive import Hive 36from sqlglot.dialects.mysql import MySQL 37from sqlglot.helper import apply_index_offset, seq_get 38from sqlglot.tokens import TokenType 39from sqlglot.transforms import unqualify_columns 40 41DATE_ADD_OR_SUB = t.Union[exp.DateAdd, exp.TimestampAdd, exp.DateSub] 42 43 44def _initcap_sql(self: Presto.Generator, expression: exp.Initcap) -> str: 45 regex = r"(\w)(\w*)" 46 return f"REGEXP_REPLACE({self.sql(expression, 'this')}, '{regex}', x -> UPPER(x[1]) || LOWER(x[2]))" 47 48 49def _no_sort_array(self: Presto.Generator, expression: exp.SortArray) -> str: 50 if expression.args.get("asc") == exp.false(): 51 comparator = "(a, b) -> CASE WHEN a < b THEN 1 WHEN a > b THEN -1 ELSE 0 END" 52 else: 53 comparator = None 54 return self.func("ARRAY_SORT", expression.this, comparator) 55 56 57def _schema_sql(self: Presto.Generator, expression: exp.Schema) -> str: 58 if isinstance(expression.parent, exp.Property): 59 columns = ", ".join(f"'{c.name}'" for c in expression.expressions) 60 return f"ARRAY[{columns}]" 61 62 if expression.parent: 63 for schema in expression.parent.find_all(exp.Schema): 64 column_defs = schema.find_all(exp.ColumnDef) 65 if column_defs and isinstance(schema.parent, exp.Property): 66 expression.expressions.extend(column_defs) 67 68 return self.schema_sql(expression) 69 70 71def _quantile_sql(self: Presto.Generator, expression: exp.Quantile) -> str: 72 self.unsupported("Presto does not support exact quantiles") 73 return self.func("APPROX_PERCENTILE", expression.this, expression.args.get("quantile")) 74 75 76def _str_to_time_sql( 77 self: Presto.Generator, expression: exp.StrToDate | exp.StrToTime | exp.TsOrDsToDate 78) -> str: 79 return self.func("DATE_PARSE", expression.this, self.format_time(expression)) 80 81 82def _ts_or_ds_to_date_sql(self: Presto.Generator, expression: exp.TsOrDsToDate) -> str: 83 time_format = self.format_time(expression) 84 if time_format and time_format not in (Presto.TIME_FORMAT, Presto.DATE_FORMAT): 85 return self.sql(exp.cast(_str_to_time_sql(self, expression), exp.DataType.Type.DATE)) 86 return self.sql( 87 exp.cast(exp.cast(expression.this, exp.DataType.Type.TIMESTAMP), exp.DataType.Type.DATE) 88 ) 89 90 91def _ts_or_ds_add_sql(self: Presto.Generator, expression: exp.TsOrDsAdd) -> str: 92 expression = ts_or_ds_add_cast(expression) 93 unit = unit_to_str(expression) 94 return self.func("DATE_ADD", unit, expression.expression, expression.this) 95 96 97def _ts_or_ds_diff_sql(self: Presto.Generator, expression: exp.TsOrDsDiff) -> str: 98 this = exp.cast(expression.this, exp.DataType.Type.TIMESTAMP) 99 expr = exp.cast(expression.expression, exp.DataType.Type.TIMESTAMP) 100 unit = unit_to_str(expression) 101 return self.func("DATE_DIFF", unit, expr, this) 102 103 104def _build_approx_percentile(args: t.List) -> exp.Expression: 105 if len(args) == 4: 106 return exp.ApproxQuantile( 107 this=seq_get(args, 0), 108 weight=seq_get(args, 1), 109 quantile=seq_get(args, 2), 110 accuracy=seq_get(args, 3), 111 ) 112 if len(args) == 3: 113 return exp.ApproxQuantile( 114 this=seq_get(args, 0), quantile=seq_get(args, 1), accuracy=seq_get(args, 2) 115 ) 116 return exp.ApproxQuantile.from_arg_list(args) 117 118 119def _build_from_unixtime(args: t.List) -> exp.Expression: 120 if len(args) == 3: 121 return exp.UnixToTime( 122 this=seq_get(args, 0), 123 hours=seq_get(args, 1), 124 minutes=seq_get(args, 2), 125 ) 126 if len(args) == 2: 127 return exp.UnixToTime(this=seq_get(args, 0), zone=seq_get(args, 1)) 128 129 return exp.UnixToTime.from_arg_list(args) 130 131 132def _first_last_sql(self: Presto.Generator, expression: exp.Func) -> str: 133 """ 134 Trino doesn't support FIRST / LAST as functions, but they're valid in the context 135 of MATCH_RECOGNIZE, so we need to preserve them in that case. In all other cases 136 they're converted into an ARBITRARY call. 137 138 Reference: https://trino.io/docs/current/sql/match-recognize.html#logical-navigation-functions 139 """ 140 if isinstance(expression.find_ancestor(exp.MatchRecognize, exp.Select), exp.MatchRecognize): 141 return self.function_fallback_sql(expression) 142 143 return rename_func("ARBITRARY")(self, expression) 144 145 146def _unix_to_time_sql(self: Presto.Generator, expression: exp.UnixToTime) -> str: 147 scale = expression.args.get("scale") 148 timestamp = self.sql(expression, "this") 149 if scale in (None, exp.UnixToTime.SECONDS): 150 return rename_func("FROM_UNIXTIME")(self, expression) 151 152 return f"FROM_UNIXTIME(CAST({timestamp} AS DOUBLE) / POW(10, {scale}))" 153 154 155def _to_int(self: Presto.Generator, expression: exp.Expression) -> exp.Expression: 156 if not expression.type: 157 from sqlglot.optimizer.annotate_types import annotate_types 158 159 annotate_types(expression, dialect=self.dialect) 160 if expression.type and expression.type.this not in exp.DataType.INTEGER_TYPES: 161 return exp.cast(expression, to=exp.DataType.Type.BIGINT) 162 return expression 163 164 165def _build_to_char(args: t.List) -> exp.TimeToStr: 166 fmt = seq_get(args, 1) 167 if isinstance(fmt, exp.Literal): 168 # We uppercase this to match Teradata's format mapping keys 169 fmt.set("this", fmt.this.upper()) 170 171 # We use "teradata" on purpose here, because the time formats are different in Presto. 172 # See https://prestodb.io/docs/current/functions/teradata.html?highlight=to_char#to_char 173 return build_formatted_time(exp.TimeToStr, "teradata")(args) 174 175 176def _date_delta_sql( 177 name: str, negate_interval: bool = False 178) -> t.Callable[[Presto.Generator, DATE_ADD_OR_SUB], str]: 179 def _delta_sql(self: Presto.Generator, expression: DATE_ADD_OR_SUB) -> str: 180 interval = _to_int(self, expression.expression) 181 return self.func( 182 name, 183 unit_to_str(expression), 184 interval * (-1) if negate_interval else interval, 185 expression.this, 186 ) 187 188 return _delta_sql 189 190 191class Presto(Dialect): 192 INDEX_OFFSET = 1 193 NULL_ORDERING = "nulls_are_last" 194 TIME_FORMAT = MySQL.TIME_FORMAT 195 STRICT_STRING_CONCAT = True 196 SUPPORTS_SEMI_ANTI_JOIN = False 197 TYPED_DIVISION = True 198 TABLESAMPLE_SIZE_IS_PERCENT = True 199 LOG_BASE_FIRST: t.Optional[bool] = None 200 201 TIME_MAPPING = MySQL.TIME_MAPPING 202 203 # https://github.com/trinodb/trino/issues/17 204 # https://github.com/trinodb/trino/issues/12289 205 # https://github.com/prestodb/presto/issues/2863 206 NORMALIZATION_STRATEGY = NormalizationStrategy.CASE_INSENSITIVE 207 208 # The result of certain math functions in Presto/Trino is of type 209 # equal to the input type e.g: FLOOR(5.5/2) -> DECIMAL, FLOOR(5/2) -> BIGINT 210 ANNOTATORS = { 211 **Dialect.ANNOTATORS, 212 exp.Floor: lambda self, e: self._annotate_by_args(e, "this"), 213 exp.Ceil: lambda self, e: self._annotate_by_args(e, "this"), 214 exp.Mod: lambda self, e: self._annotate_by_args(e, "this", "expression"), 215 exp.Round: lambda self, e: self._annotate_by_args(e, "this"), 216 exp.Sign: lambda self, e: self._annotate_by_args(e, "this"), 217 exp.Abs: lambda self, e: self._annotate_by_args(e, "this"), 218 exp.Rand: lambda self, e: self._annotate_by_args(e, "this") 219 if e.this 220 else self._set_type(e, exp.DataType.Type.DOUBLE), 221 } 222 223 class Tokenizer(tokens.Tokenizer): 224 UNICODE_STRINGS = [ 225 (prefix + q, q) 226 for q in t.cast(t.List[str], tokens.Tokenizer.QUOTES) 227 for prefix in ("U&", "u&") 228 ] 229 230 KEYWORDS = { 231 **tokens.Tokenizer.KEYWORDS, 232 "START": TokenType.BEGIN, 233 "MATCH_RECOGNIZE": TokenType.MATCH_RECOGNIZE, 234 "ROW": TokenType.STRUCT, 235 "IPADDRESS": TokenType.IPADDRESS, 236 "IPPREFIX": TokenType.IPPREFIX, 237 "TDIGEST": TokenType.TDIGEST, 238 "HYPERLOGLOG": TokenType.HLLSKETCH, 239 } 240 KEYWORDS.pop("/*+") 241 KEYWORDS.pop("QUALIFY") 242 243 class Parser(parser.Parser): 244 VALUES_FOLLOWED_BY_PAREN = False 245 246 FUNCTIONS = { 247 **parser.Parser.FUNCTIONS, 248 "ARBITRARY": exp.AnyValue.from_arg_list, 249 "APPROX_DISTINCT": exp.ApproxDistinct.from_arg_list, 250 "APPROX_PERCENTILE": _build_approx_percentile, 251 "BITWISE_AND": binary_from_function(exp.BitwiseAnd), 252 "BITWISE_NOT": lambda args: exp.BitwiseNot(this=seq_get(args, 0)), 253 "BITWISE_OR": binary_from_function(exp.BitwiseOr), 254 "BITWISE_XOR": binary_from_function(exp.BitwiseXor), 255 "CARDINALITY": exp.ArraySize.from_arg_list, 256 "CONTAINS": exp.ArrayContains.from_arg_list, 257 "DATE_ADD": lambda args: exp.DateAdd( 258 this=seq_get(args, 2), expression=seq_get(args, 1), unit=seq_get(args, 0) 259 ), 260 "DATE_DIFF": lambda args: exp.DateDiff( 261 this=seq_get(args, 2), expression=seq_get(args, 1), unit=seq_get(args, 0) 262 ), 263 "DATE_FORMAT": build_formatted_time(exp.TimeToStr, "presto"), 264 "DATE_PARSE": build_formatted_time(exp.StrToTime, "presto"), 265 "DATE_TRUNC": date_trunc_to_time, 266 "DAY_OF_WEEK": exp.DayOfWeekIso.from_arg_list, 267 "ELEMENT_AT": lambda args: exp.Bracket( 268 this=seq_get(args, 0), expressions=[seq_get(args, 1)], offset=1, safe=True 269 ), 270 "FROM_HEX": exp.Unhex.from_arg_list, 271 "FROM_UNIXTIME": _build_from_unixtime, 272 "FROM_UTF8": lambda args: exp.Decode( 273 this=seq_get(args, 0), replace=seq_get(args, 1), charset=exp.Literal.string("utf-8") 274 ), 275 "NOW": exp.CurrentTimestamp.from_arg_list, 276 "REGEXP_EXTRACT": build_regexp_extract, 277 "REGEXP_REPLACE": lambda args: exp.RegexpReplace( 278 this=seq_get(args, 0), 279 expression=seq_get(args, 1), 280 replacement=seq_get(args, 2) or exp.Literal.string(""), 281 ), 282 "ROW": exp.Struct.from_arg_list, 283 "SEQUENCE": exp.GenerateSeries.from_arg_list, 284 "SET_AGG": exp.ArrayUniqueAgg.from_arg_list, 285 "SPLIT_TO_MAP": exp.StrToMap.from_arg_list, 286 "STRPOS": lambda args: exp.StrPosition( 287 this=seq_get(args, 0), substr=seq_get(args, 1), instance=seq_get(args, 2) 288 ), 289 "TO_CHAR": _build_to_char, 290 "TO_UNIXTIME": exp.TimeToUnix.from_arg_list, 291 "TO_UTF8": lambda args: exp.Encode( 292 this=seq_get(args, 0), charset=exp.Literal.string("utf-8") 293 ), 294 "MD5": exp.MD5Digest.from_arg_list, 295 "SHA256": lambda args: exp.SHA2(this=seq_get(args, 0), length=exp.Literal.number(256)), 296 "SHA512": lambda args: exp.SHA2(this=seq_get(args, 0), length=exp.Literal.number(512)), 297 } 298 299 FUNCTION_PARSERS = parser.Parser.FUNCTION_PARSERS.copy() 300 FUNCTION_PARSERS.pop("TRIM") 301 302 class Generator(generator.Generator): 303 INTERVAL_ALLOWS_PLURAL_FORM = False 304 JOIN_HINTS = False 305 TABLE_HINTS = False 306 QUERY_HINTS = False 307 IS_BOOL_ALLOWED = False 308 TZ_TO_WITH_TIME_ZONE = True 309 NVL2_SUPPORTED = False 310 STRUCT_DELIMITER = ("(", ")") 311 LIMIT_ONLY_LITERALS = True 312 SUPPORTS_SINGLE_ARG_CONCAT = False 313 LIKE_PROPERTY_INSIDE_SCHEMA = True 314 MULTI_ARG_DISTINCT = False 315 SUPPORTS_TO_NUMBER = False 316 HEX_FUNC = "TO_HEX" 317 PARSE_JSON_NAME = "JSON_PARSE" 318 PAD_FILL_PATTERN_IS_REQUIRED = True 319 EXCEPT_INTERSECT_SUPPORT_ALL_CLAUSE = False 320 321 PROPERTIES_LOCATION = { 322 **generator.Generator.PROPERTIES_LOCATION, 323 exp.LocationProperty: exp.Properties.Location.UNSUPPORTED, 324 exp.VolatileProperty: exp.Properties.Location.UNSUPPORTED, 325 } 326 327 TYPE_MAPPING = { 328 **generator.Generator.TYPE_MAPPING, 329 exp.DataType.Type.BINARY: "VARBINARY", 330 exp.DataType.Type.BIT: "BOOLEAN", 331 exp.DataType.Type.DATETIME: "TIMESTAMP", 332 exp.DataType.Type.DATETIME64: "TIMESTAMP", 333 exp.DataType.Type.FLOAT: "REAL", 334 exp.DataType.Type.HLLSKETCH: "HYPERLOGLOG", 335 exp.DataType.Type.INT: "INTEGER", 336 exp.DataType.Type.STRUCT: "ROW", 337 exp.DataType.Type.TEXT: "VARCHAR", 338 exp.DataType.Type.TIMESTAMPTZ: "TIMESTAMP", 339 exp.DataType.Type.TIMETZ: "TIME", 340 } 341 342 TRANSFORMS = { 343 **generator.Generator.TRANSFORMS, 344 exp.AnyValue: rename_func("ARBITRARY"), 345 exp.ApproxQuantile: rename_func("APPROX_PERCENTILE"), 346 exp.ArgMax: rename_func("MAX_BY"), 347 exp.ArgMin: rename_func("MIN_BY"), 348 exp.Array: lambda self, e: f"ARRAY[{self.expressions(e, flat=True)}]", 349 exp.ArrayAny: rename_func("ANY_MATCH"), 350 exp.ArrayConcat: rename_func("CONCAT"), 351 exp.ArrayContains: rename_func("CONTAINS"), 352 exp.ArraySize: rename_func("CARDINALITY"), 353 exp.ArrayToString: rename_func("ARRAY_JOIN"), 354 exp.ArrayUniqueAgg: rename_func("SET_AGG"), 355 exp.AtTimeZone: rename_func("AT_TIMEZONE"), 356 exp.BitwiseAnd: lambda self, e: self.func("BITWISE_AND", e.this, e.expression), 357 exp.BitwiseLeftShift: lambda self, e: self.func( 358 "BITWISE_ARITHMETIC_SHIFT_LEFT", e.this, e.expression 359 ), 360 exp.BitwiseNot: lambda self, e: self.func("BITWISE_NOT", e.this), 361 exp.BitwiseOr: lambda self, e: self.func("BITWISE_OR", e.this, e.expression), 362 exp.BitwiseRightShift: lambda self, e: self.func( 363 "BITWISE_ARITHMETIC_SHIFT_RIGHT", e.this, e.expression 364 ), 365 exp.BitwiseXor: lambda self, e: self.func("BITWISE_XOR", e.this, e.expression), 366 exp.Cast: transforms.preprocess([transforms.epoch_cast_to_ts]), 367 exp.CurrentTimestamp: lambda *_: "CURRENT_TIMESTAMP", 368 exp.DateAdd: _date_delta_sql("DATE_ADD"), 369 exp.DateDiff: lambda self, e: self.func( 370 "DATE_DIFF", unit_to_str(e), e.expression, e.this 371 ), 372 exp.DateStrToDate: datestrtodate_sql, 373 exp.DateToDi: lambda self, 374 e: f"CAST(DATE_FORMAT({self.sql(e, 'this')}, {Presto.DATEINT_FORMAT}) AS INT)", 375 exp.DateSub: _date_delta_sql("DATE_ADD", negate_interval=True), 376 exp.DayOfWeek: lambda self, e: f"(({self.func('DAY_OF_WEEK', e.this)} % 7) + 1)", 377 exp.DayOfWeekIso: rename_func("DAY_OF_WEEK"), 378 exp.Decode: lambda self, e: encode_decode_sql(self, e, "FROM_UTF8"), 379 exp.DiToDate: lambda self, 380 e: f"CAST(DATE_PARSE(CAST({self.sql(e, 'this')} AS VARCHAR), {Presto.DATEINT_FORMAT}) AS DATE)", 381 exp.Encode: lambda self, e: encode_decode_sql(self, e, "TO_UTF8"), 382 exp.FileFormatProperty: lambda self, e: f"FORMAT='{e.name.upper()}'", 383 exp.First: _first_last_sql, 384 exp.FirstValue: _first_last_sql, 385 exp.FromTimeZone: lambda self, 386 e: f"WITH_TIMEZONE({self.sql(e, 'this')}, {self.sql(e, 'zone')}) AT TIME ZONE 'UTC'", 387 exp.GenerateSeries: sequence_sql, 388 exp.GenerateDateArray: sequence_sql, 389 exp.Group: transforms.preprocess([transforms.unalias_group]), 390 exp.If: if_sql(), 391 exp.ILike: no_ilike_sql, 392 exp.Initcap: _initcap_sql, 393 exp.JSONExtract: lambda self, e: self.jsonextract_sql(e), 394 exp.Last: _first_last_sql, 395 exp.LastValue: _first_last_sql, 396 exp.LastDay: lambda self, e: self.func("LAST_DAY_OF_MONTH", e.this), 397 exp.Lateral: explode_to_unnest_sql, 398 exp.Left: left_to_substring_sql, 399 exp.Levenshtein: rename_func("LEVENSHTEIN_DISTANCE"), 400 exp.LogicalAnd: rename_func("BOOL_AND"), 401 exp.LogicalOr: rename_func("BOOL_OR"), 402 exp.Pivot: no_pivot_sql, 403 exp.Quantile: _quantile_sql, 404 exp.RegexpExtract: regexp_extract_sql, 405 exp.Right: right_to_substring_sql, 406 exp.SafeDivide: no_safe_divide_sql, 407 exp.Schema: _schema_sql, 408 exp.SchemaCommentProperty: lambda self, e: self.naked_property(e), 409 exp.Select: transforms.preprocess( 410 [ 411 transforms.eliminate_qualify, 412 transforms.eliminate_distinct_on, 413 transforms.explode_to_unnest(1), 414 transforms.eliminate_semi_and_anti_joins, 415 ] 416 ), 417 exp.SortArray: _no_sort_array, 418 exp.StrPosition: lambda self, e: str_position_sql(self, e, generate_instance=True), 419 exp.StrToDate: lambda self, e: f"CAST({_str_to_time_sql(self, e)} AS DATE)", 420 exp.StrToMap: rename_func("SPLIT_TO_MAP"), 421 exp.StrToTime: _str_to_time_sql, 422 exp.StructExtract: struct_extract_sql, 423 exp.Table: transforms.preprocess([transforms.unnest_generate_series]), 424 exp.Timestamp: no_timestamp_sql, 425 exp.TimestampAdd: _date_delta_sql("DATE_ADD"), 426 exp.TimestampTrunc: timestamptrunc_sql(), 427 exp.TimeStrToDate: timestrtotime_sql, 428 exp.TimeStrToTime: timestrtotime_sql, 429 exp.TimeStrToUnix: lambda self, e: self.func( 430 "TO_UNIXTIME", self.func("DATE_PARSE", e.this, Presto.TIME_FORMAT) 431 ), 432 exp.TimeToStr: lambda self, e: self.func("DATE_FORMAT", e.this, self.format_time(e)), 433 exp.TimeToUnix: rename_func("TO_UNIXTIME"), 434 exp.ToChar: lambda self, e: self.func("DATE_FORMAT", e.this, self.format_time(e)), 435 exp.TryCast: transforms.preprocess([transforms.epoch_cast_to_ts]), 436 exp.TsOrDiToDi: lambda self, 437 e: f"CAST(SUBSTR(REPLACE(CAST({self.sql(e, 'this')} AS VARCHAR), '-', ''), 1, 8) AS INT)", 438 exp.TsOrDsAdd: _ts_or_ds_add_sql, 439 exp.TsOrDsDiff: _ts_or_ds_diff_sql, 440 exp.TsOrDsToDate: _ts_or_ds_to_date_sql, 441 exp.Unhex: rename_func("FROM_HEX"), 442 exp.UnixToStr: lambda self, 443 e: f"DATE_FORMAT(FROM_UNIXTIME({self.sql(e, 'this')}), {self.format_time(e)})", 444 exp.UnixToTime: _unix_to_time_sql, 445 exp.UnixToTimeStr: lambda self, 446 e: f"CAST(FROM_UNIXTIME({self.sql(e, 'this')}) AS VARCHAR)", 447 exp.VariancePop: rename_func("VAR_POP"), 448 exp.With: transforms.preprocess([transforms.add_recursive_cte_column_names]), 449 exp.WithinGroup: transforms.preprocess( 450 [transforms.remove_within_group_for_percentiles] 451 ), 452 exp.Xor: bool_xor_sql, 453 exp.MD5Digest: rename_func("MD5"), 454 exp.SHA: rename_func("SHA1"), 455 exp.SHA2: sha256_sql, 456 } 457 458 RESERVED_KEYWORDS = { 459 "alter", 460 "and", 461 "as", 462 "between", 463 "by", 464 "case", 465 "cast", 466 "constraint", 467 "create", 468 "cross", 469 "current_time", 470 "current_timestamp", 471 "deallocate", 472 "delete", 473 "describe", 474 "distinct", 475 "drop", 476 "else", 477 "end", 478 "escape", 479 "except", 480 "execute", 481 "exists", 482 "extract", 483 "false", 484 "for", 485 "from", 486 "full", 487 "group", 488 "having", 489 "in", 490 "inner", 491 "insert", 492 "intersect", 493 "into", 494 "is", 495 "join", 496 "left", 497 "like", 498 "natural", 499 "not", 500 "null", 501 "on", 502 "or", 503 "order", 504 "outer", 505 "prepare", 506 "right", 507 "select", 508 "table", 509 "then", 510 "true", 511 "union", 512 "using", 513 "values", 514 "when", 515 "where", 516 "with", 517 } 518 519 def md5_sql(self, expression: exp.MD5) -> str: 520 this = expression.this 521 522 if not this.type: 523 from sqlglot.optimizer.annotate_types import annotate_types 524 525 this = annotate_types(this) 526 527 if this.is_type(*exp.DataType.TEXT_TYPES): 528 this = exp.Encode(this=this, charset=exp.Literal.string("utf-8")) 529 530 return self.func("LOWER", self.func("TO_HEX", self.func("MD5", self.sql(this)))) 531 532 def strtounix_sql(self, expression: exp.StrToUnix) -> str: 533 # Since `TO_UNIXTIME` requires a `TIMESTAMP`, we need to parse the argument into one. 534 # To do this, we first try to `DATE_PARSE` it, but since this can fail when there's a 535 # timezone involved, we wrap it in a `TRY` call and use `PARSE_DATETIME` as a fallback, 536 # which seems to be using the same time mapping as Hive, as per: 537 # https://joda-time.sourceforge.net/apidocs/org/joda/time/format/DateTimeFormat.html 538 this = expression.this 539 value_as_text = exp.cast(this, exp.DataType.Type.TEXT) 540 value_as_timestamp = ( 541 exp.cast(this, exp.DataType.Type.TIMESTAMP) if this.is_string else this 542 ) 543 544 parse_without_tz = self.func("DATE_PARSE", value_as_text, self.format_time(expression)) 545 546 formatted_value = self.func( 547 "DATE_FORMAT", value_as_timestamp, self.format_time(expression) 548 ) 549 parse_with_tz = self.func( 550 "PARSE_DATETIME", 551 formatted_value, 552 self.format_time(expression, Hive.INVERSE_TIME_MAPPING, Hive.INVERSE_TIME_TRIE), 553 ) 554 coalesced = self.func("COALESCE", self.func("TRY", parse_without_tz), parse_with_tz) 555 return self.func("TO_UNIXTIME", coalesced) 556 557 def bracket_sql(self, expression: exp.Bracket) -> str: 558 if expression.args.get("safe"): 559 return self.func( 560 "ELEMENT_AT", 561 expression.this, 562 seq_get( 563 apply_index_offset( 564 expression.this, 565 expression.expressions, 566 1 - expression.args.get("offset", 0), 567 ), 568 0, 569 ), 570 ) 571 return super().bracket_sql(expression) 572 573 def struct_sql(self, expression: exp.Struct) -> str: 574 from sqlglot.optimizer.annotate_types import annotate_types 575 576 expression = annotate_types(expression) 577 values: t.List[str] = [] 578 schema: t.List[str] = [] 579 unknown_type = False 580 581 for e in expression.expressions: 582 if isinstance(e, exp.PropertyEQ): 583 if e.type and e.type.is_type(exp.DataType.Type.UNKNOWN): 584 unknown_type = True 585 else: 586 schema.append(f"{self.sql(e, 'this')} {self.sql(e.type)}") 587 values.append(self.sql(e, "expression")) 588 else: 589 values.append(self.sql(e)) 590 591 size = len(expression.expressions) 592 593 if not size or len(schema) != size: 594 if unknown_type: 595 self.unsupported( 596 "Cannot convert untyped key-value definitions (try annotate_types)." 597 ) 598 return self.func("ROW", *values) 599 return f"CAST(ROW({', '.join(values)}) AS ROW({', '.join(schema)}))" 600 601 def interval_sql(self, expression: exp.Interval) -> str: 602 if expression.this and expression.text("unit").upper().startswith("WEEK"): 603 return f"({expression.this.name} * INTERVAL '7' DAY)" 604 return super().interval_sql(expression) 605 606 def transaction_sql(self, expression: exp.Transaction) -> str: 607 modes = expression.args.get("modes") 608 modes = f" {', '.join(modes)}" if modes else "" 609 return f"START TRANSACTION{modes}" 610 611 def offset_limit_modifiers( 612 self, expression: exp.Expression, fetch: bool, limit: t.Optional[exp.Fetch | exp.Limit] 613 ) -> t.List[str]: 614 return [ 615 self.sql(expression, "offset"), 616 self.sql(limit), 617 ] 618 619 def create_sql(self, expression: exp.Create) -> str: 620 """ 621 Presto doesn't support CREATE VIEW with expressions (ex: `CREATE VIEW x (cola)` then `(cola)` is the expression), 622 so we need to remove them 623 """ 624 kind = expression.args["kind"] 625 schema = expression.this 626 if kind == "VIEW" and schema.expressions: 627 expression.this.set("expressions", None) 628 return super().create_sql(expression) 629 630 def delete_sql(self, expression: exp.Delete) -> str: 631 """ 632 Presto only supports DELETE FROM for a single table without an alias, so we need 633 to remove the unnecessary parts. If the original DELETE statement contains more 634 than one table to be deleted, we can't safely map it 1-1 to a Presto statement. 635 """ 636 tables = expression.args.get("tables") or [expression.this] 637 if len(tables) > 1: 638 return super().delete_sql(expression) 639 640 table = tables[0] 641 expression.set("this", table) 642 expression.set("tables", None) 643 644 if isinstance(table, exp.Table): 645 table_alias = table.args.get("alias") 646 if table_alias: 647 table_alias.pop() 648 expression = t.cast(exp.Delete, expression.transform(unqualify_columns)) 649 650 return super().delete_sql(expression) 651 652 def jsonextract_sql(self, expression: exp.JSONExtract) -> str: 653 is_json_extract = self.dialect.settings.get("variant_extract_is_json_extract", True) 654 655 # Generate JSON_EXTRACT unless the user has configured that a Snowflake / Databricks 656 # VARIANT extract (e.g. col:x.y) should map to dot notation (i.e ROW access) in Presto/Trino 657 if not expression.args.get("variant_extract") or is_json_extract: 658 return self.func( 659 "JSON_EXTRACT", expression.this, expression.expression, *expression.expressions 660 ) 661 662 this = self.sql(expression, "this") 663 664 # Convert the JSONPath extraction `JSON_EXTRACT(col, '$.x.y) to a ROW access col.x.y 665 segments = [] 666 for path_key in expression.expression.expressions[1:]: 667 if not isinstance(path_key, exp.JSONPathKey): 668 # Cannot transpile subscripts, wildcards etc to dot notation 669 self.unsupported( 670 f"Cannot transpile JSONPath segment '{path_key}' to ROW access" 671 ) 672 continue 673 key = path_key.this 674 if not exp.SAFE_IDENTIFIER_RE.match(key): 675 key = f'"{key}"' 676 segments.append(f".{key}") 677 678 expr = "".join(segments) 679 680 return f"{this}{expr}" 681 682 def groupconcat_sql(self, expression: exp.GroupConcat) -> str: 683 return self.func( 684 "ARRAY_JOIN", 685 self.func("ARRAY_AGG", expression.this), 686 expression.args.get("separator"), 687 )
192class Presto(Dialect): 193 INDEX_OFFSET = 1 194 NULL_ORDERING = "nulls_are_last" 195 TIME_FORMAT = MySQL.TIME_FORMAT 196 STRICT_STRING_CONCAT = True 197 SUPPORTS_SEMI_ANTI_JOIN = False 198 TYPED_DIVISION = True 199 TABLESAMPLE_SIZE_IS_PERCENT = True 200 LOG_BASE_FIRST: t.Optional[bool] = None 201 202 TIME_MAPPING = MySQL.TIME_MAPPING 203 204 # https://github.com/trinodb/trino/issues/17 205 # https://github.com/trinodb/trino/issues/12289 206 # https://github.com/prestodb/presto/issues/2863 207 NORMALIZATION_STRATEGY = NormalizationStrategy.CASE_INSENSITIVE 208 209 # The result of certain math functions in Presto/Trino is of type 210 # equal to the input type e.g: FLOOR(5.5/2) -> DECIMAL, FLOOR(5/2) -> BIGINT 211 ANNOTATORS = { 212 **Dialect.ANNOTATORS, 213 exp.Floor: lambda self, e: self._annotate_by_args(e, "this"), 214 exp.Ceil: lambda self, e: self._annotate_by_args(e, "this"), 215 exp.Mod: lambda self, e: self._annotate_by_args(e, "this", "expression"), 216 exp.Round: lambda self, e: self._annotate_by_args(e, "this"), 217 exp.Sign: lambda self, e: self._annotate_by_args(e, "this"), 218 exp.Abs: lambda self, e: self._annotate_by_args(e, "this"), 219 exp.Rand: lambda self, e: self._annotate_by_args(e, "this") 220 if e.this 221 else self._set_type(e, exp.DataType.Type.DOUBLE), 222 } 223 224 class Tokenizer(tokens.Tokenizer): 225 UNICODE_STRINGS = [ 226 (prefix + q, q) 227 for q in t.cast(t.List[str], tokens.Tokenizer.QUOTES) 228 for prefix in ("U&", "u&") 229 ] 230 231 KEYWORDS = { 232 **tokens.Tokenizer.KEYWORDS, 233 "START": TokenType.BEGIN, 234 "MATCH_RECOGNIZE": TokenType.MATCH_RECOGNIZE, 235 "ROW": TokenType.STRUCT, 236 "IPADDRESS": TokenType.IPADDRESS, 237 "IPPREFIX": TokenType.IPPREFIX, 238 "TDIGEST": TokenType.TDIGEST, 239 "HYPERLOGLOG": TokenType.HLLSKETCH, 240 } 241 KEYWORDS.pop("/*+") 242 KEYWORDS.pop("QUALIFY") 243 244 class Parser(parser.Parser): 245 VALUES_FOLLOWED_BY_PAREN = False 246 247 FUNCTIONS = { 248 **parser.Parser.FUNCTIONS, 249 "ARBITRARY": exp.AnyValue.from_arg_list, 250 "APPROX_DISTINCT": exp.ApproxDistinct.from_arg_list, 251 "APPROX_PERCENTILE": _build_approx_percentile, 252 "BITWISE_AND": binary_from_function(exp.BitwiseAnd), 253 "BITWISE_NOT": lambda args: exp.BitwiseNot(this=seq_get(args, 0)), 254 "BITWISE_OR": binary_from_function(exp.BitwiseOr), 255 "BITWISE_XOR": binary_from_function(exp.BitwiseXor), 256 "CARDINALITY": exp.ArraySize.from_arg_list, 257 "CONTAINS": exp.ArrayContains.from_arg_list, 258 "DATE_ADD": lambda args: exp.DateAdd( 259 this=seq_get(args, 2), expression=seq_get(args, 1), unit=seq_get(args, 0) 260 ), 261 "DATE_DIFF": lambda args: exp.DateDiff( 262 this=seq_get(args, 2), expression=seq_get(args, 1), unit=seq_get(args, 0) 263 ), 264 "DATE_FORMAT": build_formatted_time(exp.TimeToStr, "presto"), 265 "DATE_PARSE": build_formatted_time(exp.StrToTime, "presto"), 266 "DATE_TRUNC": date_trunc_to_time, 267 "DAY_OF_WEEK": exp.DayOfWeekIso.from_arg_list, 268 "ELEMENT_AT": lambda args: exp.Bracket( 269 this=seq_get(args, 0), expressions=[seq_get(args, 1)], offset=1, safe=True 270 ), 271 "FROM_HEX": exp.Unhex.from_arg_list, 272 "FROM_UNIXTIME": _build_from_unixtime, 273 "FROM_UTF8": lambda args: exp.Decode( 274 this=seq_get(args, 0), replace=seq_get(args, 1), charset=exp.Literal.string("utf-8") 275 ), 276 "NOW": exp.CurrentTimestamp.from_arg_list, 277 "REGEXP_EXTRACT": build_regexp_extract, 278 "REGEXP_REPLACE": lambda args: exp.RegexpReplace( 279 this=seq_get(args, 0), 280 expression=seq_get(args, 1), 281 replacement=seq_get(args, 2) or exp.Literal.string(""), 282 ), 283 "ROW": exp.Struct.from_arg_list, 284 "SEQUENCE": exp.GenerateSeries.from_arg_list, 285 "SET_AGG": exp.ArrayUniqueAgg.from_arg_list, 286 "SPLIT_TO_MAP": exp.StrToMap.from_arg_list, 287 "STRPOS": lambda args: exp.StrPosition( 288 this=seq_get(args, 0), substr=seq_get(args, 1), instance=seq_get(args, 2) 289 ), 290 "TO_CHAR": _build_to_char, 291 "TO_UNIXTIME": exp.TimeToUnix.from_arg_list, 292 "TO_UTF8": lambda args: exp.Encode( 293 this=seq_get(args, 0), charset=exp.Literal.string("utf-8") 294 ), 295 "MD5": exp.MD5Digest.from_arg_list, 296 "SHA256": lambda args: exp.SHA2(this=seq_get(args, 0), length=exp.Literal.number(256)), 297 "SHA512": lambda args: exp.SHA2(this=seq_get(args, 0), length=exp.Literal.number(512)), 298 } 299 300 FUNCTION_PARSERS = parser.Parser.FUNCTION_PARSERS.copy() 301 FUNCTION_PARSERS.pop("TRIM") 302 303 class Generator(generator.Generator): 304 INTERVAL_ALLOWS_PLURAL_FORM = False 305 JOIN_HINTS = False 306 TABLE_HINTS = False 307 QUERY_HINTS = False 308 IS_BOOL_ALLOWED = False 309 TZ_TO_WITH_TIME_ZONE = True 310 NVL2_SUPPORTED = False 311 STRUCT_DELIMITER = ("(", ")") 312 LIMIT_ONLY_LITERALS = True 313 SUPPORTS_SINGLE_ARG_CONCAT = False 314 LIKE_PROPERTY_INSIDE_SCHEMA = True 315 MULTI_ARG_DISTINCT = False 316 SUPPORTS_TO_NUMBER = False 317 HEX_FUNC = "TO_HEX" 318 PARSE_JSON_NAME = "JSON_PARSE" 319 PAD_FILL_PATTERN_IS_REQUIRED = True 320 EXCEPT_INTERSECT_SUPPORT_ALL_CLAUSE = False 321 322 PROPERTIES_LOCATION = { 323 **generator.Generator.PROPERTIES_LOCATION, 324 exp.LocationProperty: exp.Properties.Location.UNSUPPORTED, 325 exp.VolatileProperty: exp.Properties.Location.UNSUPPORTED, 326 } 327 328 TYPE_MAPPING = { 329 **generator.Generator.TYPE_MAPPING, 330 exp.DataType.Type.BINARY: "VARBINARY", 331 exp.DataType.Type.BIT: "BOOLEAN", 332 exp.DataType.Type.DATETIME: "TIMESTAMP", 333 exp.DataType.Type.DATETIME64: "TIMESTAMP", 334 exp.DataType.Type.FLOAT: "REAL", 335 exp.DataType.Type.HLLSKETCH: "HYPERLOGLOG", 336 exp.DataType.Type.INT: "INTEGER", 337 exp.DataType.Type.STRUCT: "ROW", 338 exp.DataType.Type.TEXT: "VARCHAR", 339 exp.DataType.Type.TIMESTAMPTZ: "TIMESTAMP", 340 exp.DataType.Type.TIMETZ: "TIME", 341 } 342 343 TRANSFORMS = { 344 **generator.Generator.TRANSFORMS, 345 exp.AnyValue: rename_func("ARBITRARY"), 346 exp.ApproxQuantile: rename_func("APPROX_PERCENTILE"), 347 exp.ArgMax: rename_func("MAX_BY"), 348 exp.ArgMin: rename_func("MIN_BY"), 349 exp.Array: lambda self, e: f"ARRAY[{self.expressions(e, flat=True)}]", 350 exp.ArrayAny: rename_func("ANY_MATCH"), 351 exp.ArrayConcat: rename_func("CONCAT"), 352 exp.ArrayContains: rename_func("CONTAINS"), 353 exp.ArraySize: rename_func("CARDINALITY"), 354 exp.ArrayToString: rename_func("ARRAY_JOIN"), 355 exp.ArrayUniqueAgg: rename_func("SET_AGG"), 356 exp.AtTimeZone: rename_func("AT_TIMEZONE"), 357 exp.BitwiseAnd: lambda self, e: self.func("BITWISE_AND", e.this, e.expression), 358 exp.BitwiseLeftShift: lambda self, e: self.func( 359 "BITWISE_ARITHMETIC_SHIFT_LEFT", e.this, e.expression 360 ), 361 exp.BitwiseNot: lambda self, e: self.func("BITWISE_NOT", e.this), 362 exp.BitwiseOr: lambda self, e: self.func("BITWISE_OR", e.this, e.expression), 363 exp.BitwiseRightShift: lambda self, e: self.func( 364 "BITWISE_ARITHMETIC_SHIFT_RIGHT", e.this, e.expression 365 ), 366 exp.BitwiseXor: lambda self, e: self.func("BITWISE_XOR", e.this, e.expression), 367 exp.Cast: transforms.preprocess([transforms.epoch_cast_to_ts]), 368 exp.CurrentTimestamp: lambda *_: "CURRENT_TIMESTAMP", 369 exp.DateAdd: _date_delta_sql("DATE_ADD"), 370 exp.DateDiff: lambda self, e: self.func( 371 "DATE_DIFF", unit_to_str(e), e.expression, e.this 372 ), 373 exp.DateStrToDate: datestrtodate_sql, 374 exp.DateToDi: lambda self, 375 e: f"CAST(DATE_FORMAT({self.sql(e, 'this')}, {Presto.DATEINT_FORMAT}) AS INT)", 376 exp.DateSub: _date_delta_sql("DATE_ADD", negate_interval=True), 377 exp.DayOfWeek: lambda self, e: f"(({self.func('DAY_OF_WEEK', e.this)} % 7) + 1)", 378 exp.DayOfWeekIso: rename_func("DAY_OF_WEEK"), 379 exp.Decode: lambda self, e: encode_decode_sql(self, e, "FROM_UTF8"), 380 exp.DiToDate: lambda self, 381 e: f"CAST(DATE_PARSE(CAST({self.sql(e, 'this')} AS VARCHAR), {Presto.DATEINT_FORMAT}) AS DATE)", 382 exp.Encode: lambda self, e: encode_decode_sql(self, e, "TO_UTF8"), 383 exp.FileFormatProperty: lambda self, e: f"FORMAT='{e.name.upper()}'", 384 exp.First: _first_last_sql, 385 exp.FirstValue: _first_last_sql, 386 exp.FromTimeZone: lambda self, 387 e: f"WITH_TIMEZONE({self.sql(e, 'this')}, {self.sql(e, 'zone')}) AT TIME ZONE 'UTC'", 388 exp.GenerateSeries: sequence_sql, 389 exp.GenerateDateArray: sequence_sql, 390 exp.Group: transforms.preprocess([transforms.unalias_group]), 391 exp.If: if_sql(), 392 exp.ILike: no_ilike_sql, 393 exp.Initcap: _initcap_sql, 394 exp.JSONExtract: lambda self, e: self.jsonextract_sql(e), 395 exp.Last: _first_last_sql, 396 exp.LastValue: _first_last_sql, 397 exp.LastDay: lambda self, e: self.func("LAST_DAY_OF_MONTH", e.this), 398 exp.Lateral: explode_to_unnest_sql, 399 exp.Left: left_to_substring_sql, 400 exp.Levenshtein: rename_func("LEVENSHTEIN_DISTANCE"), 401 exp.LogicalAnd: rename_func("BOOL_AND"), 402 exp.LogicalOr: rename_func("BOOL_OR"), 403 exp.Pivot: no_pivot_sql, 404 exp.Quantile: _quantile_sql, 405 exp.RegexpExtract: regexp_extract_sql, 406 exp.Right: right_to_substring_sql, 407 exp.SafeDivide: no_safe_divide_sql, 408 exp.Schema: _schema_sql, 409 exp.SchemaCommentProperty: lambda self, e: self.naked_property(e), 410 exp.Select: transforms.preprocess( 411 [ 412 transforms.eliminate_qualify, 413 transforms.eliminate_distinct_on, 414 transforms.explode_to_unnest(1), 415 transforms.eliminate_semi_and_anti_joins, 416 ] 417 ), 418 exp.SortArray: _no_sort_array, 419 exp.StrPosition: lambda self, e: str_position_sql(self, e, generate_instance=True), 420 exp.StrToDate: lambda self, e: f"CAST({_str_to_time_sql(self, e)} AS DATE)", 421 exp.StrToMap: rename_func("SPLIT_TO_MAP"), 422 exp.StrToTime: _str_to_time_sql, 423 exp.StructExtract: struct_extract_sql, 424 exp.Table: transforms.preprocess([transforms.unnest_generate_series]), 425 exp.Timestamp: no_timestamp_sql, 426 exp.TimestampAdd: _date_delta_sql("DATE_ADD"), 427 exp.TimestampTrunc: timestamptrunc_sql(), 428 exp.TimeStrToDate: timestrtotime_sql, 429 exp.TimeStrToTime: timestrtotime_sql, 430 exp.TimeStrToUnix: lambda self, e: self.func( 431 "TO_UNIXTIME", self.func("DATE_PARSE", e.this, Presto.TIME_FORMAT) 432 ), 433 exp.TimeToStr: lambda self, e: self.func("DATE_FORMAT", e.this, self.format_time(e)), 434 exp.TimeToUnix: rename_func("TO_UNIXTIME"), 435 exp.ToChar: lambda self, e: self.func("DATE_FORMAT", e.this, self.format_time(e)), 436 exp.TryCast: transforms.preprocess([transforms.epoch_cast_to_ts]), 437 exp.TsOrDiToDi: lambda self, 438 e: f"CAST(SUBSTR(REPLACE(CAST({self.sql(e, 'this')} AS VARCHAR), '-', ''), 1, 8) AS INT)", 439 exp.TsOrDsAdd: _ts_or_ds_add_sql, 440 exp.TsOrDsDiff: _ts_or_ds_diff_sql, 441 exp.TsOrDsToDate: _ts_or_ds_to_date_sql, 442 exp.Unhex: rename_func("FROM_HEX"), 443 exp.UnixToStr: lambda self, 444 e: f"DATE_FORMAT(FROM_UNIXTIME({self.sql(e, 'this')}), {self.format_time(e)})", 445 exp.UnixToTime: _unix_to_time_sql, 446 exp.UnixToTimeStr: lambda self, 447 e: f"CAST(FROM_UNIXTIME({self.sql(e, 'this')}) AS VARCHAR)", 448 exp.VariancePop: rename_func("VAR_POP"), 449 exp.With: transforms.preprocess([transforms.add_recursive_cte_column_names]), 450 exp.WithinGroup: transforms.preprocess( 451 [transforms.remove_within_group_for_percentiles] 452 ), 453 exp.Xor: bool_xor_sql, 454 exp.MD5Digest: rename_func("MD5"), 455 exp.SHA: rename_func("SHA1"), 456 exp.SHA2: sha256_sql, 457 } 458 459 RESERVED_KEYWORDS = { 460 "alter", 461 "and", 462 "as", 463 "between", 464 "by", 465 "case", 466 "cast", 467 "constraint", 468 "create", 469 "cross", 470 "current_time", 471 "current_timestamp", 472 "deallocate", 473 "delete", 474 "describe", 475 "distinct", 476 "drop", 477 "else", 478 "end", 479 "escape", 480 "except", 481 "execute", 482 "exists", 483 "extract", 484 "false", 485 "for", 486 "from", 487 "full", 488 "group", 489 "having", 490 "in", 491 "inner", 492 "insert", 493 "intersect", 494 "into", 495 "is", 496 "join", 497 "left", 498 "like", 499 "natural", 500 "not", 501 "null", 502 "on", 503 "or", 504 "order", 505 "outer", 506 "prepare", 507 "right", 508 "select", 509 "table", 510 "then", 511 "true", 512 "union", 513 "using", 514 "values", 515 "when", 516 "where", 517 "with", 518 } 519 520 def md5_sql(self, expression: exp.MD5) -> str: 521 this = expression.this 522 523 if not this.type: 524 from sqlglot.optimizer.annotate_types import annotate_types 525 526 this = annotate_types(this) 527 528 if this.is_type(*exp.DataType.TEXT_TYPES): 529 this = exp.Encode(this=this, charset=exp.Literal.string("utf-8")) 530 531 return self.func("LOWER", self.func("TO_HEX", self.func("MD5", self.sql(this)))) 532 533 def strtounix_sql(self, expression: exp.StrToUnix) -> str: 534 # Since `TO_UNIXTIME` requires a `TIMESTAMP`, we need to parse the argument into one. 535 # To do this, we first try to `DATE_PARSE` it, but since this can fail when there's a 536 # timezone involved, we wrap it in a `TRY` call and use `PARSE_DATETIME` as a fallback, 537 # which seems to be using the same time mapping as Hive, as per: 538 # https://joda-time.sourceforge.net/apidocs/org/joda/time/format/DateTimeFormat.html 539 this = expression.this 540 value_as_text = exp.cast(this, exp.DataType.Type.TEXT) 541 value_as_timestamp = ( 542 exp.cast(this, exp.DataType.Type.TIMESTAMP) if this.is_string else this 543 ) 544 545 parse_without_tz = self.func("DATE_PARSE", value_as_text, self.format_time(expression)) 546 547 formatted_value = self.func( 548 "DATE_FORMAT", value_as_timestamp, self.format_time(expression) 549 ) 550 parse_with_tz = self.func( 551 "PARSE_DATETIME", 552 formatted_value, 553 self.format_time(expression, Hive.INVERSE_TIME_MAPPING, Hive.INVERSE_TIME_TRIE), 554 ) 555 coalesced = self.func("COALESCE", self.func("TRY", parse_without_tz), parse_with_tz) 556 return self.func("TO_UNIXTIME", coalesced) 557 558 def bracket_sql(self, expression: exp.Bracket) -> str: 559 if expression.args.get("safe"): 560 return self.func( 561 "ELEMENT_AT", 562 expression.this, 563 seq_get( 564 apply_index_offset( 565 expression.this, 566 expression.expressions, 567 1 - expression.args.get("offset", 0), 568 ), 569 0, 570 ), 571 ) 572 return super().bracket_sql(expression) 573 574 def struct_sql(self, expression: exp.Struct) -> str: 575 from sqlglot.optimizer.annotate_types import annotate_types 576 577 expression = annotate_types(expression) 578 values: t.List[str] = [] 579 schema: t.List[str] = [] 580 unknown_type = False 581 582 for e in expression.expressions: 583 if isinstance(e, exp.PropertyEQ): 584 if e.type and e.type.is_type(exp.DataType.Type.UNKNOWN): 585 unknown_type = True 586 else: 587 schema.append(f"{self.sql(e, 'this')} {self.sql(e.type)}") 588 values.append(self.sql(e, "expression")) 589 else: 590 values.append(self.sql(e)) 591 592 size = len(expression.expressions) 593 594 if not size or len(schema) != size: 595 if unknown_type: 596 self.unsupported( 597 "Cannot convert untyped key-value definitions (try annotate_types)." 598 ) 599 return self.func("ROW", *values) 600 return f"CAST(ROW({', '.join(values)}) AS ROW({', '.join(schema)}))" 601 602 def interval_sql(self, expression: exp.Interval) -> str: 603 if expression.this and expression.text("unit").upper().startswith("WEEK"): 604 return f"({expression.this.name} * INTERVAL '7' DAY)" 605 return super().interval_sql(expression) 606 607 def transaction_sql(self, expression: exp.Transaction) -> str: 608 modes = expression.args.get("modes") 609 modes = f" {', '.join(modes)}" if modes else "" 610 return f"START TRANSACTION{modes}" 611 612 def offset_limit_modifiers( 613 self, expression: exp.Expression, fetch: bool, limit: t.Optional[exp.Fetch | exp.Limit] 614 ) -> t.List[str]: 615 return [ 616 self.sql(expression, "offset"), 617 self.sql(limit), 618 ] 619 620 def create_sql(self, expression: exp.Create) -> str: 621 """ 622 Presto doesn't support CREATE VIEW with expressions (ex: `CREATE VIEW x (cola)` then `(cola)` is the expression), 623 so we need to remove them 624 """ 625 kind = expression.args["kind"] 626 schema = expression.this 627 if kind == "VIEW" and schema.expressions: 628 expression.this.set("expressions", None) 629 return super().create_sql(expression) 630 631 def delete_sql(self, expression: exp.Delete) -> str: 632 """ 633 Presto only supports DELETE FROM for a single table without an alias, so we need 634 to remove the unnecessary parts. If the original DELETE statement contains more 635 than one table to be deleted, we can't safely map it 1-1 to a Presto statement. 636 """ 637 tables = expression.args.get("tables") or [expression.this] 638 if len(tables) > 1: 639 return super().delete_sql(expression) 640 641 table = tables[0] 642 expression.set("this", table) 643 expression.set("tables", None) 644 645 if isinstance(table, exp.Table): 646 table_alias = table.args.get("alias") 647 if table_alias: 648 table_alias.pop() 649 expression = t.cast(exp.Delete, expression.transform(unqualify_columns)) 650 651 return super().delete_sql(expression) 652 653 def jsonextract_sql(self, expression: exp.JSONExtract) -> str: 654 is_json_extract = self.dialect.settings.get("variant_extract_is_json_extract", True) 655 656 # Generate JSON_EXTRACT unless the user has configured that a Snowflake / Databricks 657 # VARIANT extract (e.g. col:x.y) should map to dot notation (i.e ROW access) in Presto/Trino 658 if not expression.args.get("variant_extract") or is_json_extract: 659 return self.func( 660 "JSON_EXTRACT", expression.this, expression.expression, *expression.expressions 661 ) 662 663 this = self.sql(expression, "this") 664 665 # Convert the JSONPath extraction `JSON_EXTRACT(col, '$.x.y) to a ROW access col.x.y 666 segments = [] 667 for path_key in expression.expression.expressions[1:]: 668 if not isinstance(path_key, exp.JSONPathKey): 669 # Cannot transpile subscripts, wildcards etc to dot notation 670 self.unsupported( 671 f"Cannot transpile JSONPath segment '{path_key}' to ROW access" 672 ) 673 continue 674 key = path_key.this 675 if not exp.SAFE_IDENTIFIER_RE.match(key): 676 key = f'"{key}"' 677 segments.append(f".{key}") 678 679 expr = "".join(segments) 680 681 return f"{this}{expr}" 682 683 def groupconcat_sql(self, expression: exp.GroupConcat) -> str: 684 return self.func( 685 "ARRAY_JOIN", 686 self.func("ARRAY_AGG", expression.this), 687 expression.args.get("separator"), 688 )
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
224 class Tokenizer(tokens.Tokenizer): 225 UNICODE_STRINGS = [ 226 (prefix + q, q) 227 for q in t.cast(t.List[str], tokens.Tokenizer.QUOTES) 228 for prefix in ("U&", "u&") 229 ] 230 231 KEYWORDS = { 232 **tokens.Tokenizer.KEYWORDS, 233 "START": TokenType.BEGIN, 234 "MATCH_RECOGNIZE": TokenType.MATCH_RECOGNIZE, 235 "ROW": TokenType.STRUCT, 236 "IPADDRESS": TokenType.IPADDRESS, 237 "IPPREFIX": TokenType.IPPREFIX, 238 "TDIGEST": TokenType.TDIGEST, 239 "HYPERLOGLOG": TokenType.HLLSKETCH, 240 } 241 KEYWORDS.pop("/*+") 242 KEYWORDS.pop("QUALIFY")
Inherited Members
- sqlglot.tokens.Tokenizer
- Tokenizer
- SINGLE_TOKENS
- BIT_STRINGS
- BYTE_STRINGS
- HEX_STRINGS
- RAW_STRINGS
- HEREDOC_STRINGS
- IDENTIFIERS
- QUOTES
- STRING_ESCAPES
- VAR_SINGLE_TOKENS
- IDENTIFIER_ESCAPES
- 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
244 class Parser(parser.Parser): 245 VALUES_FOLLOWED_BY_PAREN = False 246 247 FUNCTIONS = { 248 **parser.Parser.FUNCTIONS, 249 "ARBITRARY": exp.AnyValue.from_arg_list, 250 "APPROX_DISTINCT": exp.ApproxDistinct.from_arg_list, 251 "APPROX_PERCENTILE": _build_approx_percentile, 252 "BITWISE_AND": binary_from_function(exp.BitwiseAnd), 253 "BITWISE_NOT": lambda args: exp.BitwiseNot(this=seq_get(args, 0)), 254 "BITWISE_OR": binary_from_function(exp.BitwiseOr), 255 "BITWISE_XOR": binary_from_function(exp.BitwiseXor), 256 "CARDINALITY": exp.ArraySize.from_arg_list, 257 "CONTAINS": exp.ArrayContains.from_arg_list, 258 "DATE_ADD": lambda args: exp.DateAdd( 259 this=seq_get(args, 2), expression=seq_get(args, 1), unit=seq_get(args, 0) 260 ), 261 "DATE_DIFF": lambda args: exp.DateDiff( 262 this=seq_get(args, 2), expression=seq_get(args, 1), unit=seq_get(args, 0) 263 ), 264 "DATE_FORMAT": build_formatted_time(exp.TimeToStr, "presto"), 265 "DATE_PARSE": build_formatted_time(exp.StrToTime, "presto"), 266 "DATE_TRUNC": date_trunc_to_time, 267 "DAY_OF_WEEK": exp.DayOfWeekIso.from_arg_list, 268 "ELEMENT_AT": lambda args: exp.Bracket( 269 this=seq_get(args, 0), expressions=[seq_get(args, 1)], offset=1, safe=True 270 ), 271 "FROM_HEX": exp.Unhex.from_arg_list, 272 "FROM_UNIXTIME": _build_from_unixtime, 273 "FROM_UTF8": lambda args: exp.Decode( 274 this=seq_get(args, 0), replace=seq_get(args, 1), charset=exp.Literal.string("utf-8") 275 ), 276 "NOW": exp.CurrentTimestamp.from_arg_list, 277 "REGEXP_EXTRACT": build_regexp_extract, 278 "REGEXP_REPLACE": lambda args: exp.RegexpReplace( 279 this=seq_get(args, 0), 280 expression=seq_get(args, 1), 281 replacement=seq_get(args, 2) or exp.Literal.string(""), 282 ), 283 "ROW": exp.Struct.from_arg_list, 284 "SEQUENCE": exp.GenerateSeries.from_arg_list, 285 "SET_AGG": exp.ArrayUniqueAgg.from_arg_list, 286 "SPLIT_TO_MAP": exp.StrToMap.from_arg_list, 287 "STRPOS": lambda args: exp.StrPosition( 288 this=seq_get(args, 0), substr=seq_get(args, 1), instance=seq_get(args, 2) 289 ), 290 "TO_CHAR": _build_to_char, 291 "TO_UNIXTIME": exp.TimeToUnix.from_arg_list, 292 "TO_UTF8": lambda args: exp.Encode( 293 this=seq_get(args, 0), charset=exp.Literal.string("utf-8") 294 ), 295 "MD5": exp.MD5Digest.from_arg_list, 296 "SHA256": lambda args: exp.SHA2(this=seq_get(args, 0), length=exp.Literal.number(256)), 297 "SHA512": lambda args: exp.SHA2(this=seq_get(args, 0), length=exp.Literal.number(512)), 298 } 299 300 FUNCTION_PARSERS = parser.Parser.FUNCTION_PARSERS.copy() 301 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
- PROCEDURE_OPTIONS
- EXECUTE_AS_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
303 class Generator(generator.Generator): 304 INTERVAL_ALLOWS_PLURAL_FORM = False 305 JOIN_HINTS = False 306 TABLE_HINTS = False 307 QUERY_HINTS = False 308 IS_BOOL_ALLOWED = False 309 TZ_TO_WITH_TIME_ZONE = True 310 NVL2_SUPPORTED = False 311 STRUCT_DELIMITER = ("(", ")") 312 LIMIT_ONLY_LITERALS = True 313 SUPPORTS_SINGLE_ARG_CONCAT = False 314 LIKE_PROPERTY_INSIDE_SCHEMA = True 315 MULTI_ARG_DISTINCT = False 316 SUPPORTS_TO_NUMBER = False 317 HEX_FUNC = "TO_HEX" 318 PARSE_JSON_NAME = "JSON_PARSE" 319 PAD_FILL_PATTERN_IS_REQUIRED = True 320 EXCEPT_INTERSECT_SUPPORT_ALL_CLAUSE = False 321 322 PROPERTIES_LOCATION = { 323 **generator.Generator.PROPERTIES_LOCATION, 324 exp.LocationProperty: exp.Properties.Location.UNSUPPORTED, 325 exp.VolatileProperty: exp.Properties.Location.UNSUPPORTED, 326 } 327 328 TYPE_MAPPING = { 329 **generator.Generator.TYPE_MAPPING, 330 exp.DataType.Type.BINARY: "VARBINARY", 331 exp.DataType.Type.BIT: "BOOLEAN", 332 exp.DataType.Type.DATETIME: "TIMESTAMP", 333 exp.DataType.Type.DATETIME64: "TIMESTAMP", 334 exp.DataType.Type.FLOAT: "REAL", 335 exp.DataType.Type.HLLSKETCH: "HYPERLOGLOG", 336 exp.DataType.Type.INT: "INTEGER", 337 exp.DataType.Type.STRUCT: "ROW", 338 exp.DataType.Type.TEXT: "VARCHAR", 339 exp.DataType.Type.TIMESTAMPTZ: "TIMESTAMP", 340 exp.DataType.Type.TIMETZ: "TIME", 341 } 342 343 TRANSFORMS = { 344 **generator.Generator.TRANSFORMS, 345 exp.AnyValue: rename_func("ARBITRARY"), 346 exp.ApproxQuantile: rename_func("APPROX_PERCENTILE"), 347 exp.ArgMax: rename_func("MAX_BY"), 348 exp.ArgMin: rename_func("MIN_BY"), 349 exp.Array: lambda self, e: f"ARRAY[{self.expressions(e, flat=True)}]", 350 exp.ArrayAny: rename_func("ANY_MATCH"), 351 exp.ArrayConcat: rename_func("CONCAT"), 352 exp.ArrayContains: rename_func("CONTAINS"), 353 exp.ArraySize: rename_func("CARDINALITY"), 354 exp.ArrayToString: rename_func("ARRAY_JOIN"), 355 exp.ArrayUniqueAgg: rename_func("SET_AGG"), 356 exp.AtTimeZone: rename_func("AT_TIMEZONE"), 357 exp.BitwiseAnd: lambda self, e: self.func("BITWISE_AND", e.this, e.expression), 358 exp.BitwiseLeftShift: lambda self, e: self.func( 359 "BITWISE_ARITHMETIC_SHIFT_LEFT", e.this, e.expression 360 ), 361 exp.BitwiseNot: lambda self, e: self.func("BITWISE_NOT", e.this), 362 exp.BitwiseOr: lambda self, e: self.func("BITWISE_OR", e.this, e.expression), 363 exp.BitwiseRightShift: lambda self, e: self.func( 364 "BITWISE_ARITHMETIC_SHIFT_RIGHT", e.this, e.expression 365 ), 366 exp.BitwiseXor: lambda self, e: self.func("BITWISE_XOR", e.this, e.expression), 367 exp.Cast: transforms.preprocess([transforms.epoch_cast_to_ts]), 368 exp.CurrentTimestamp: lambda *_: "CURRENT_TIMESTAMP", 369 exp.DateAdd: _date_delta_sql("DATE_ADD"), 370 exp.DateDiff: lambda self, e: self.func( 371 "DATE_DIFF", unit_to_str(e), e.expression, e.this 372 ), 373 exp.DateStrToDate: datestrtodate_sql, 374 exp.DateToDi: lambda self, 375 e: f"CAST(DATE_FORMAT({self.sql(e, 'this')}, {Presto.DATEINT_FORMAT}) AS INT)", 376 exp.DateSub: _date_delta_sql("DATE_ADD", negate_interval=True), 377 exp.DayOfWeek: lambda self, e: f"(({self.func('DAY_OF_WEEK', e.this)} % 7) + 1)", 378 exp.DayOfWeekIso: rename_func("DAY_OF_WEEK"), 379 exp.Decode: lambda self, e: encode_decode_sql(self, e, "FROM_UTF8"), 380 exp.DiToDate: lambda self, 381 e: f"CAST(DATE_PARSE(CAST({self.sql(e, 'this')} AS VARCHAR), {Presto.DATEINT_FORMAT}) AS DATE)", 382 exp.Encode: lambda self, e: encode_decode_sql(self, e, "TO_UTF8"), 383 exp.FileFormatProperty: lambda self, e: f"FORMAT='{e.name.upper()}'", 384 exp.First: _first_last_sql, 385 exp.FirstValue: _first_last_sql, 386 exp.FromTimeZone: lambda self, 387 e: f"WITH_TIMEZONE({self.sql(e, 'this')}, {self.sql(e, 'zone')}) AT TIME ZONE 'UTC'", 388 exp.GenerateSeries: sequence_sql, 389 exp.GenerateDateArray: sequence_sql, 390 exp.Group: transforms.preprocess([transforms.unalias_group]), 391 exp.If: if_sql(), 392 exp.ILike: no_ilike_sql, 393 exp.Initcap: _initcap_sql, 394 exp.JSONExtract: lambda self, e: self.jsonextract_sql(e), 395 exp.Last: _first_last_sql, 396 exp.LastValue: _first_last_sql, 397 exp.LastDay: lambda self, e: self.func("LAST_DAY_OF_MONTH", e.this), 398 exp.Lateral: explode_to_unnest_sql, 399 exp.Left: left_to_substring_sql, 400 exp.Levenshtein: rename_func("LEVENSHTEIN_DISTANCE"), 401 exp.LogicalAnd: rename_func("BOOL_AND"), 402 exp.LogicalOr: rename_func("BOOL_OR"), 403 exp.Pivot: no_pivot_sql, 404 exp.Quantile: _quantile_sql, 405 exp.RegexpExtract: regexp_extract_sql, 406 exp.Right: right_to_substring_sql, 407 exp.SafeDivide: no_safe_divide_sql, 408 exp.Schema: _schema_sql, 409 exp.SchemaCommentProperty: lambda self, e: self.naked_property(e), 410 exp.Select: transforms.preprocess( 411 [ 412 transforms.eliminate_qualify, 413 transforms.eliminate_distinct_on, 414 transforms.explode_to_unnest(1), 415 transforms.eliminate_semi_and_anti_joins, 416 ] 417 ), 418 exp.SortArray: _no_sort_array, 419 exp.StrPosition: lambda self, e: str_position_sql(self, e, generate_instance=True), 420 exp.StrToDate: lambda self, e: f"CAST({_str_to_time_sql(self, e)} AS DATE)", 421 exp.StrToMap: rename_func("SPLIT_TO_MAP"), 422 exp.StrToTime: _str_to_time_sql, 423 exp.StructExtract: struct_extract_sql, 424 exp.Table: transforms.preprocess([transforms.unnest_generate_series]), 425 exp.Timestamp: no_timestamp_sql, 426 exp.TimestampAdd: _date_delta_sql("DATE_ADD"), 427 exp.TimestampTrunc: timestamptrunc_sql(), 428 exp.TimeStrToDate: timestrtotime_sql, 429 exp.TimeStrToTime: timestrtotime_sql, 430 exp.TimeStrToUnix: lambda self, e: self.func( 431 "TO_UNIXTIME", self.func("DATE_PARSE", e.this, Presto.TIME_FORMAT) 432 ), 433 exp.TimeToStr: lambda self, e: self.func("DATE_FORMAT", e.this, self.format_time(e)), 434 exp.TimeToUnix: rename_func("TO_UNIXTIME"), 435 exp.ToChar: lambda self, e: self.func("DATE_FORMAT", e.this, self.format_time(e)), 436 exp.TryCast: transforms.preprocess([transforms.epoch_cast_to_ts]), 437 exp.TsOrDiToDi: lambda self, 438 e: f"CAST(SUBSTR(REPLACE(CAST({self.sql(e, 'this')} AS VARCHAR), '-', ''), 1, 8) AS INT)", 439 exp.TsOrDsAdd: _ts_or_ds_add_sql, 440 exp.TsOrDsDiff: _ts_or_ds_diff_sql, 441 exp.TsOrDsToDate: _ts_or_ds_to_date_sql, 442 exp.Unhex: rename_func("FROM_HEX"), 443 exp.UnixToStr: lambda self, 444 e: f"DATE_FORMAT(FROM_UNIXTIME({self.sql(e, 'this')}), {self.format_time(e)})", 445 exp.UnixToTime: _unix_to_time_sql, 446 exp.UnixToTimeStr: lambda self, 447 e: f"CAST(FROM_UNIXTIME({self.sql(e, 'this')}) AS VARCHAR)", 448 exp.VariancePop: rename_func("VAR_POP"), 449 exp.With: transforms.preprocess([transforms.add_recursive_cte_column_names]), 450 exp.WithinGroup: transforms.preprocess( 451 [transforms.remove_within_group_for_percentiles] 452 ), 453 exp.Xor: bool_xor_sql, 454 exp.MD5Digest: rename_func("MD5"), 455 exp.SHA: rename_func("SHA1"), 456 exp.SHA2: sha256_sql, 457 } 458 459 RESERVED_KEYWORDS = { 460 "alter", 461 "and", 462 "as", 463 "between", 464 "by", 465 "case", 466 "cast", 467 "constraint", 468 "create", 469 "cross", 470 "current_time", 471 "current_timestamp", 472 "deallocate", 473 "delete", 474 "describe", 475 "distinct", 476 "drop", 477 "else", 478 "end", 479 "escape", 480 "except", 481 "execute", 482 "exists", 483 "extract", 484 "false", 485 "for", 486 "from", 487 "full", 488 "group", 489 "having", 490 "in", 491 "inner", 492 "insert", 493 "intersect", 494 "into", 495 "is", 496 "join", 497 "left", 498 "like", 499 "natural", 500 "not", 501 "null", 502 "on", 503 "or", 504 "order", 505 "outer", 506 "prepare", 507 "right", 508 "select", 509 "table", 510 "then", 511 "true", 512 "union", 513 "using", 514 "values", 515 "when", 516 "where", 517 "with", 518 } 519 520 def md5_sql(self, expression: exp.MD5) -> str: 521 this = expression.this 522 523 if not this.type: 524 from sqlglot.optimizer.annotate_types import annotate_types 525 526 this = annotate_types(this) 527 528 if this.is_type(*exp.DataType.TEXT_TYPES): 529 this = exp.Encode(this=this, charset=exp.Literal.string("utf-8")) 530 531 return self.func("LOWER", self.func("TO_HEX", self.func("MD5", self.sql(this)))) 532 533 def strtounix_sql(self, expression: exp.StrToUnix) -> str: 534 # Since `TO_UNIXTIME` requires a `TIMESTAMP`, we need to parse the argument into one. 535 # To do this, we first try to `DATE_PARSE` it, but since this can fail when there's a 536 # timezone involved, we wrap it in a `TRY` call and use `PARSE_DATETIME` as a fallback, 537 # which seems to be using the same time mapping as Hive, as per: 538 # https://joda-time.sourceforge.net/apidocs/org/joda/time/format/DateTimeFormat.html 539 this = expression.this 540 value_as_text = exp.cast(this, exp.DataType.Type.TEXT) 541 value_as_timestamp = ( 542 exp.cast(this, exp.DataType.Type.TIMESTAMP) if this.is_string else this 543 ) 544 545 parse_without_tz = self.func("DATE_PARSE", value_as_text, self.format_time(expression)) 546 547 formatted_value = self.func( 548 "DATE_FORMAT", value_as_timestamp, self.format_time(expression) 549 ) 550 parse_with_tz = self.func( 551 "PARSE_DATETIME", 552 formatted_value, 553 self.format_time(expression, Hive.INVERSE_TIME_MAPPING, Hive.INVERSE_TIME_TRIE), 554 ) 555 coalesced = self.func("COALESCE", self.func("TRY", parse_without_tz), parse_with_tz) 556 return self.func("TO_UNIXTIME", coalesced) 557 558 def bracket_sql(self, expression: exp.Bracket) -> str: 559 if expression.args.get("safe"): 560 return self.func( 561 "ELEMENT_AT", 562 expression.this, 563 seq_get( 564 apply_index_offset( 565 expression.this, 566 expression.expressions, 567 1 - expression.args.get("offset", 0), 568 ), 569 0, 570 ), 571 ) 572 return super().bracket_sql(expression) 573 574 def struct_sql(self, expression: exp.Struct) -> str: 575 from sqlglot.optimizer.annotate_types import annotate_types 576 577 expression = annotate_types(expression) 578 values: t.List[str] = [] 579 schema: t.List[str] = [] 580 unknown_type = False 581 582 for e in expression.expressions: 583 if isinstance(e, exp.PropertyEQ): 584 if e.type and e.type.is_type(exp.DataType.Type.UNKNOWN): 585 unknown_type = True 586 else: 587 schema.append(f"{self.sql(e, 'this')} {self.sql(e.type)}") 588 values.append(self.sql(e, "expression")) 589 else: 590 values.append(self.sql(e)) 591 592 size = len(expression.expressions) 593 594 if not size or len(schema) != size: 595 if unknown_type: 596 self.unsupported( 597 "Cannot convert untyped key-value definitions (try annotate_types)." 598 ) 599 return self.func("ROW", *values) 600 return f"CAST(ROW({', '.join(values)}) AS ROW({', '.join(schema)}))" 601 602 def interval_sql(self, expression: exp.Interval) -> str: 603 if expression.this and expression.text("unit").upper().startswith("WEEK"): 604 return f"({expression.this.name} * INTERVAL '7' DAY)" 605 return super().interval_sql(expression) 606 607 def transaction_sql(self, expression: exp.Transaction) -> str: 608 modes = expression.args.get("modes") 609 modes = f" {', '.join(modes)}" if modes else "" 610 return f"START TRANSACTION{modes}" 611 612 def offset_limit_modifiers( 613 self, expression: exp.Expression, fetch: bool, limit: t.Optional[exp.Fetch | exp.Limit] 614 ) -> t.List[str]: 615 return [ 616 self.sql(expression, "offset"), 617 self.sql(limit), 618 ] 619 620 def create_sql(self, expression: exp.Create) -> str: 621 """ 622 Presto doesn't support CREATE VIEW with expressions (ex: `CREATE VIEW x (cola)` then `(cola)` is the expression), 623 so we need to remove them 624 """ 625 kind = expression.args["kind"] 626 schema = expression.this 627 if kind == "VIEW" and schema.expressions: 628 expression.this.set("expressions", None) 629 return super().create_sql(expression) 630 631 def delete_sql(self, expression: exp.Delete) -> str: 632 """ 633 Presto only supports DELETE FROM for a single table without an alias, so we need 634 to remove the unnecessary parts. If the original DELETE statement contains more 635 than one table to be deleted, we can't safely map it 1-1 to a Presto statement. 636 """ 637 tables = expression.args.get("tables") or [expression.this] 638 if len(tables) > 1: 639 return super().delete_sql(expression) 640 641 table = tables[0] 642 expression.set("this", table) 643 expression.set("tables", None) 644 645 if isinstance(table, exp.Table): 646 table_alias = table.args.get("alias") 647 if table_alias: 648 table_alias.pop() 649 expression = t.cast(exp.Delete, expression.transform(unqualify_columns)) 650 651 return super().delete_sql(expression) 652 653 def jsonextract_sql(self, expression: exp.JSONExtract) -> str: 654 is_json_extract = self.dialect.settings.get("variant_extract_is_json_extract", True) 655 656 # Generate JSON_EXTRACT unless the user has configured that a Snowflake / Databricks 657 # VARIANT extract (e.g. col:x.y) should map to dot notation (i.e ROW access) in Presto/Trino 658 if not expression.args.get("variant_extract") or is_json_extract: 659 return self.func( 660 "JSON_EXTRACT", expression.this, expression.expression, *expression.expressions 661 ) 662 663 this = self.sql(expression, "this") 664 665 # Convert the JSONPath extraction `JSON_EXTRACT(col, '$.x.y) to a ROW access col.x.y 666 segments = [] 667 for path_key in expression.expression.expressions[1:]: 668 if not isinstance(path_key, exp.JSONPathKey): 669 # Cannot transpile subscripts, wildcards etc to dot notation 670 self.unsupported( 671 f"Cannot transpile JSONPath segment '{path_key}' to ROW access" 672 ) 673 continue 674 key = path_key.this 675 if not exp.SAFE_IDENTIFIER_RE.match(key): 676 key = f'"{key}"' 677 segments.append(f".{key}") 678 679 expr = "".join(segments) 680 681 return f"{this}{expr}" 682 683 def groupconcat_sql(self, expression: exp.GroupConcat) -> str: 684 return self.func( 685 "ARRAY_JOIN", 686 self.func("ARRAY_AGG", expression.this), 687 expression.args.get("separator"), 688 )
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
520 def md5_sql(self, expression: exp.MD5) -> str: 521 this = expression.this 522 523 if not this.type: 524 from sqlglot.optimizer.annotate_types import annotate_types 525 526 this = annotate_types(this) 527 528 if this.is_type(*exp.DataType.TEXT_TYPES): 529 this = exp.Encode(this=this, charset=exp.Literal.string("utf-8")) 530 531 return self.func("LOWER", self.func("TO_HEX", self.func("MD5", self.sql(this))))
533 def strtounix_sql(self, expression: exp.StrToUnix) -> str: 534 # Since `TO_UNIXTIME` requires a `TIMESTAMP`, we need to parse the argument into one. 535 # To do this, we first try to `DATE_PARSE` it, but since this can fail when there's a 536 # timezone involved, we wrap it in a `TRY` call and use `PARSE_DATETIME` as a fallback, 537 # which seems to be using the same time mapping as Hive, as per: 538 # https://joda-time.sourceforge.net/apidocs/org/joda/time/format/DateTimeFormat.html 539 this = expression.this 540 value_as_text = exp.cast(this, exp.DataType.Type.TEXT) 541 value_as_timestamp = ( 542 exp.cast(this, exp.DataType.Type.TIMESTAMP) if this.is_string else this 543 ) 544 545 parse_without_tz = self.func("DATE_PARSE", value_as_text, self.format_time(expression)) 546 547 formatted_value = self.func( 548 "DATE_FORMAT", value_as_timestamp, self.format_time(expression) 549 ) 550 parse_with_tz = self.func( 551 "PARSE_DATETIME", 552 formatted_value, 553 self.format_time(expression, Hive.INVERSE_TIME_MAPPING, Hive.INVERSE_TIME_TRIE), 554 ) 555 coalesced = self.func("COALESCE", self.func("TRY", parse_without_tz), parse_with_tz) 556 return self.func("TO_UNIXTIME", coalesced)
558 def bracket_sql(self, expression: exp.Bracket) -> str: 559 if expression.args.get("safe"): 560 return self.func( 561 "ELEMENT_AT", 562 expression.this, 563 seq_get( 564 apply_index_offset( 565 expression.this, 566 expression.expressions, 567 1 - expression.args.get("offset", 0), 568 ), 569 0, 570 ), 571 ) 572 return super().bracket_sql(expression)
574 def struct_sql(self, expression: exp.Struct) -> str: 575 from sqlglot.optimizer.annotate_types import annotate_types 576 577 expression = annotate_types(expression) 578 values: t.List[str] = [] 579 schema: t.List[str] = [] 580 unknown_type = False 581 582 for e in expression.expressions: 583 if isinstance(e, exp.PropertyEQ): 584 if e.type and e.type.is_type(exp.DataType.Type.UNKNOWN): 585 unknown_type = True 586 else: 587 schema.append(f"{self.sql(e, 'this')} {self.sql(e.type)}") 588 values.append(self.sql(e, "expression")) 589 else: 590 values.append(self.sql(e)) 591 592 size = len(expression.expressions) 593 594 if not size or len(schema) != size: 595 if unknown_type: 596 self.unsupported( 597 "Cannot convert untyped key-value definitions (try annotate_types)." 598 ) 599 return self.func("ROW", *values) 600 return f"CAST(ROW({', '.join(values)}) AS ROW({', '.join(schema)}))"
620 def create_sql(self, expression: exp.Create) -> str: 621 """ 622 Presto doesn't support CREATE VIEW with expressions (ex: `CREATE VIEW x (cola)` then `(cola)` is the expression), 623 so we need to remove them 624 """ 625 kind = expression.args["kind"] 626 schema = expression.this 627 if kind == "VIEW" and schema.expressions: 628 expression.this.set("expressions", None) 629 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
631 def delete_sql(self, expression: exp.Delete) -> str: 632 """ 633 Presto only supports DELETE FROM for a single table without an alias, so we need 634 to remove the unnecessary parts. If the original DELETE statement contains more 635 than one table to be deleted, we can't safely map it 1-1 to a Presto statement. 636 """ 637 tables = expression.args.get("tables") or [expression.this] 638 if len(tables) > 1: 639 return super().delete_sql(expression) 640 641 table = tables[0] 642 expression.set("this", table) 643 expression.set("tables", None) 644 645 if isinstance(table, exp.Table): 646 table_alias = table.args.get("alias") 647 if table_alias: 648 table_alias.pop() 649 expression = t.cast(exp.Delete, expression.transform(unqualify_columns)) 650 651 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.
653 def jsonextract_sql(self, expression: exp.JSONExtract) -> str: 654 is_json_extract = self.dialect.settings.get("variant_extract_is_json_extract", True) 655 656 # Generate JSON_EXTRACT unless the user has configured that a Snowflake / Databricks 657 # VARIANT extract (e.g. col:x.y) should map to dot notation (i.e ROW access) in Presto/Trino 658 if not expression.args.get("variant_extract") or is_json_extract: 659 return self.func( 660 "JSON_EXTRACT", expression.this, expression.expression, *expression.expressions 661 ) 662 663 this = self.sql(expression, "this") 664 665 # Convert the JSONPath extraction `JSON_EXTRACT(col, '$.x.y) to a ROW access col.x.y 666 segments = [] 667 for path_key in expression.expression.expressions[1:]: 668 if not isinstance(path_key, exp.JSONPathKey): 669 # Cannot transpile subscripts, wildcards etc to dot notation 670 self.unsupported( 671 f"Cannot transpile JSONPath segment '{path_key}' to ROW access" 672 ) 673 continue 674 key = path_key.this 675 if not exp.SAFE_IDENTIFIER_RE.match(key): 676 key = f'"{key}"' 677 segments.append(f".{key}") 678 679 expr = "".join(segments) 680 681 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
- todouble_sql
- string_sql