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