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_timestamp_sql, 20 regexp_extract_sql, 21 rename_func, 22 right_to_substring_sql, 23 sha256_sql, 24 strposition_sql, 25 struct_extract_sql, 26 timestamptrunc_sql, 27 timestrtotime_sql, 28 ts_or_ds_add_cast, 29 unit_to_str, 30 sequence_sql, 31 build_regexp_extract, 32 explode_to_unnest_sql, 33) 34from sqlglot.dialects.hive import Hive 35from sqlglot.dialects.mysql import MySQL 36from sqlglot.helper import apply_index_offset, seq_get 37from sqlglot.tokens import TokenType 38from sqlglot.transforms import unqualify_columns 39from sqlglot.generator import unsupported_args 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 SUPPORTS_VALUES_DEFAULT = False 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 HEX_STRINGS = [("x'", "'"), ("X'", "'")] 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), occurrence=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.CurrentUser: lambda *_: "CURRENT_USER", 380 exp.DateAdd: _date_delta_sql("DATE_ADD"), 381 exp.DateDiff: lambda self, e: self.func( 382 "DATE_DIFF", unit_to_str(e), e.expression, e.this 383 ), 384 exp.DateStrToDate: datestrtodate_sql, 385 exp.DateToDi: lambda self, 386 e: f"CAST(DATE_FORMAT({self.sql(e, 'this')}, {Presto.DATEINT_FORMAT}) AS INT)", 387 exp.DateSub: _date_delta_sql("DATE_ADD", negate_interval=True), 388 exp.DayOfWeek: lambda self, e: f"(({self.func('DAY_OF_WEEK', e.this)} % 7) + 1)", 389 exp.DayOfWeekIso: rename_func("DAY_OF_WEEK"), 390 exp.Decode: lambda self, e: encode_decode_sql(self, e, "FROM_UTF8"), 391 exp.DiToDate: lambda self, 392 e: f"CAST(DATE_PARSE(CAST({self.sql(e, 'this')} AS VARCHAR), {Presto.DATEINT_FORMAT}) AS DATE)", 393 exp.Encode: lambda self, e: encode_decode_sql(self, e, "TO_UTF8"), 394 exp.FileFormatProperty: lambda self, e: f"FORMAT='{e.name.upper()}'", 395 exp.First: _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.Last: _first_last_sql, 405 exp.LastDay: lambda self, e: self.func("LAST_DAY_OF_MONTH", e.this), 406 exp.Lateral: explode_to_unnest_sql, 407 exp.Left: left_to_substring_sql, 408 exp.Levenshtein: unsupported_args("ins_cost", "del_cost", "sub_cost", "max_dist")( 409 rename_func("LEVENSHTEIN_DISTANCE") 410 ), 411 exp.LogicalAnd: rename_func("BOOL_AND"), 412 exp.LogicalOr: rename_func("BOOL_OR"), 413 exp.Pivot: no_pivot_sql, 414 exp.Quantile: _quantile_sql, 415 exp.RegexpExtract: regexp_extract_sql, 416 exp.RegexpExtractAll: regexp_extract_sql, 417 exp.Right: right_to_substring_sql, 418 exp.Schema: _schema_sql, 419 exp.SchemaCommentProperty: lambda self, e: self.naked_property(e), 420 exp.Select: transforms.preprocess( 421 [ 422 transforms.eliminate_qualify, 423 transforms.eliminate_distinct_on, 424 transforms.explode_to_unnest(1), 425 transforms.eliminate_semi_and_anti_joins, 426 ] 427 ), 428 exp.SortArray: _no_sort_array, 429 exp.StrPosition: lambda self, e: strposition_sql(self, e, supports_occurrence=True), 430 exp.StrToDate: lambda self, e: f"CAST({_str_to_time_sql(self, e)} AS DATE)", 431 exp.StrToMap: rename_func("SPLIT_TO_MAP"), 432 exp.StrToTime: _str_to_time_sql, 433 exp.StructExtract: struct_extract_sql, 434 exp.Table: transforms.preprocess([transforms.unnest_generate_series]), 435 exp.Timestamp: no_timestamp_sql, 436 exp.TimestampAdd: _date_delta_sql("DATE_ADD"), 437 exp.TimestampTrunc: timestamptrunc_sql(), 438 exp.TimeStrToDate: timestrtotime_sql, 439 exp.TimeStrToTime: timestrtotime_sql, 440 exp.TimeStrToUnix: lambda self, e: self.func( 441 "TO_UNIXTIME", self.func("DATE_PARSE", e.this, Presto.TIME_FORMAT) 442 ), 443 exp.TimeToStr: lambda self, e: self.func("DATE_FORMAT", e.this, self.format_time(e)), 444 exp.TimeToUnix: rename_func("TO_UNIXTIME"), 445 exp.ToChar: lambda self, e: self.func("DATE_FORMAT", e.this, self.format_time(e)), 446 exp.TryCast: transforms.preprocess([transforms.epoch_cast_to_ts]), 447 exp.TsOrDiToDi: lambda self, 448 e: f"CAST(SUBSTR(REPLACE(CAST({self.sql(e, 'this')} AS VARCHAR), '-', ''), 1, 8) AS INT)", 449 exp.TsOrDsAdd: _ts_or_ds_add_sql, 450 exp.TsOrDsDiff: _ts_or_ds_diff_sql, 451 exp.TsOrDsToDate: _ts_or_ds_to_date_sql, 452 exp.Unhex: rename_func("FROM_HEX"), 453 exp.UnixToStr: lambda self, 454 e: f"DATE_FORMAT(FROM_UNIXTIME({self.sql(e, 'this')}), {self.format_time(e)})", 455 exp.UnixToTime: _unix_to_time_sql, 456 exp.UnixToTimeStr: lambda self, 457 e: f"CAST(FROM_UNIXTIME({self.sql(e, 'this')}) AS VARCHAR)", 458 exp.VariancePop: rename_func("VAR_POP"), 459 exp.With: transforms.preprocess([transforms.add_recursive_cte_column_names]), 460 exp.WithinGroup: transforms.preprocess( 461 [transforms.remove_within_group_for_percentiles] 462 ), 463 exp.Xor: bool_xor_sql, 464 exp.MD5Digest: rename_func("MD5"), 465 exp.SHA: rename_func("SHA1"), 466 exp.SHA2: sha256_sql, 467 } 468 469 RESERVED_KEYWORDS = { 470 "alter", 471 "and", 472 "as", 473 "between", 474 "by", 475 "case", 476 "cast", 477 "constraint", 478 "create", 479 "cross", 480 "current_time", 481 "current_timestamp", 482 "deallocate", 483 "delete", 484 "describe", 485 "distinct", 486 "drop", 487 "else", 488 "end", 489 "escape", 490 "except", 491 "execute", 492 "exists", 493 "extract", 494 "false", 495 "for", 496 "from", 497 "full", 498 "group", 499 "having", 500 "in", 501 "inner", 502 "insert", 503 "intersect", 504 "into", 505 "is", 506 "join", 507 "left", 508 "like", 509 "natural", 510 "not", 511 "null", 512 "on", 513 "or", 514 "order", 515 "outer", 516 "prepare", 517 "right", 518 "select", 519 "table", 520 "then", 521 "true", 522 "union", 523 "using", 524 "values", 525 "when", 526 "where", 527 "with", 528 } 529 530 def md5_sql(self, expression: exp.MD5) -> str: 531 this = expression.this 532 533 if not this.type: 534 from sqlglot.optimizer.annotate_types import annotate_types 535 536 this = annotate_types(this) 537 538 if this.is_type(*exp.DataType.TEXT_TYPES): 539 this = exp.Encode(this=this, charset=exp.Literal.string("utf-8")) 540 541 return self.func("LOWER", self.func("TO_HEX", self.func("MD5", self.sql(this)))) 542 543 def strtounix_sql(self, expression: exp.StrToUnix) -> str: 544 # Since `TO_UNIXTIME` requires a `TIMESTAMP`, we need to parse the argument into one. 545 # To do this, we first try to `DATE_PARSE` it, but since this can fail when there's a 546 # timezone involved, we wrap it in a `TRY` call and use `PARSE_DATETIME` as a fallback, 547 # which seems to be using the same time mapping as Hive, as per: 548 # https://joda-time.sourceforge.net/apidocs/org/joda/time/format/DateTimeFormat.html 549 this = expression.this 550 value_as_text = exp.cast(this, exp.DataType.Type.TEXT) 551 value_as_timestamp = ( 552 exp.cast(this, exp.DataType.Type.TIMESTAMP) if this.is_string else this 553 ) 554 555 parse_without_tz = self.func("DATE_PARSE", value_as_text, self.format_time(expression)) 556 557 formatted_value = self.func( 558 "DATE_FORMAT", value_as_timestamp, self.format_time(expression) 559 ) 560 parse_with_tz = self.func( 561 "PARSE_DATETIME", 562 formatted_value, 563 self.format_time(expression, Hive.INVERSE_TIME_MAPPING, Hive.INVERSE_TIME_TRIE), 564 ) 565 coalesced = self.func("COALESCE", self.func("TRY", parse_without_tz), parse_with_tz) 566 return self.func("TO_UNIXTIME", coalesced) 567 568 def bracket_sql(self, expression: exp.Bracket) -> str: 569 if expression.args.get("safe"): 570 return self.func( 571 "ELEMENT_AT", 572 expression.this, 573 seq_get( 574 apply_index_offset( 575 expression.this, 576 expression.expressions, 577 1 - expression.args.get("offset", 0), 578 ), 579 0, 580 ), 581 ) 582 return super().bracket_sql(expression) 583 584 def struct_sql(self, expression: exp.Struct) -> str: 585 from sqlglot.optimizer.annotate_types import annotate_types 586 587 expression = annotate_types(expression) 588 values: t.List[str] = [] 589 schema: t.List[str] = [] 590 unknown_type = False 591 592 for e in expression.expressions: 593 if isinstance(e, exp.PropertyEQ): 594 if e.type and e.type.is_type(exp.DataType.Type.UNKNOWN): 595 unknown_type = True 596 else: 597 schema.append(f"{self.sql(e, 'this')} {self.sql(e.type)}") 598 values.append(self.sql(e, "expression")) 599 else: 600 values.append(self.sql(e)) 601 602 size = len(expression.expressions) 603 604 if not size or len(schema) != size: 605 if unknown_type: 606 self.unsupported( 607 "Cannot convert untyped key-value definitions (try annotate_types)." 608 ) 609 return self.func("ROW", *values) 610 return f"CAST(ROW({', '.join(values)}) AS ROW({', '.join(schema)}))" 611 612 def interval_sql(self, expression: exp.Interval) -> str: 613 if expression.this and expression.text("unit").upper().startswith("WEEK"): 614 return f"({expression.this.name} * INTERVAL '7' DAY)" 615 return super().interval_sql(expression) 616 617 def transaction_sql(self, expression: exp.Transaction) -> str: 618 modes = expression.args.get("modes") 619 modes = f" {', '.join(modes)}" if modes else "" 620 return f"START TRANSACTION{modes}" 621 622 def offset_limit_modifiers( 623 self, expression: exp.Expression, fetch: bool, limit: t.Optional[exp.Fetch | exp.Limit] 624 ) -> t.List[str]: 625 return [ 626 self.sql(expression, "offset"), 627 self.sql(limit), 628 ] 629 630 def create_sql(self, expression: exp.Create) -> str: 631 """ 632 Presto doesn't support CREATE VIEW with expressions (ex: `CREATE VIEW x (cola)` then `(cola)` is the expression), 633 so we need to remove them 634 """ 635 kind = expression.args["kind"] 636 schema = expression.this 637 if kind == "VIEW" and schema.expressions: 638 expression.this.set("expressions", None) 639 return super().create_sql(expression) 640 641 def delete_sql(self, expression: exp.Delete) -> str: 642 """ 643 Presto only supports DELETE FROM for a single table without an alias, so we need 644 to remove the unnecessary parts. If the original DELETE statement contains more 645 than one table to be deleted, we can't safely map it 1-1 to a Presto statement. 646 """ 647 tables = expression.args.get("tables") or [expression.this] 648 if len(tables) > 1: 649 return super().delete_sql(expression) 650 651 table = tables[0] 652 expression.set("this", table) 653 expression.set("tables", None) 654 655 if isinstance(table, exp.Table): 656 table_alias = table.args.get("alias") 657 if table_alias: 658 table_alias.pop() 659 expression = t.cast(exp.Delete, expression.transform(unqualify_columns)) 660 661 return super().delete_sql(expression) 662 663 def jsonextract_sql(self, expression: exp.JSONExtract) -> str: 664 is_json_extract = self.dialect.settings.get("variant_extract_is_json_extract", True) 665 666 # Generate JSON_EXTRACT unless the user has configured that a Snowflake / Databricks 667 # VARIANT extract (e.g. col:x.y) should map to dot notation (i.e ROW access) in Presto/Trino 668 if not expression.args.get("variant_extract") or is_json_extract: 669 return self.func( 670 "JSON_EXTRACT", expression.this, expression.expression, *expression.expressions 671 ) 672 673 this = self.sql(expression, "this") 674 675 # Convert the JSONPath extraction `JSON_EXTRACT(col, '$.x.y) to a ROW access col.x.y 676 segments = [] 677 for path_key in expression.expression.expressions[1:]: 678 if not isinstance(path_key, exp.JSONPathKey): 679 # Cannot transpile subscripts, wildcards etc to dot notation 680 self.unsupported( 681 f"Cannot transpile JSONPath segment '{path_key}' to ROW access" 682 ) 683 continue 684 key = path_key.this 685 if not exp.SAFE_IDENTIFIER_RE.match(key): 686 key = f'"{key}"' 687 segments.append(f".{key}") 688 689 expr = "".join(segments) 690 691 return f"{this}{expr}" 692 693 def groupconcat_sql(self, expression: exp.GroupConcat) -> str: 694 return self.func( 695 "ARRAY_JOIN", 696 self.func("ARRAY_AGG", expression.this), 697 expression.args.get("separator"), 698 )
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 SUPPORTS_VALUES_DEFAULT = False 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 HEX_STRINGS = [("x'", "'"), ("X'", "'")] 227 UNICODE_STRINGS = [ 228 (prefix + q, q) 229 for q in t.cast(t.List[str], tokens.Tokenizer.QUOTES) 230 for prefix in ("U&", "u&") 231 ] 232 233 KEYWORDS = { 234 **tokens.Tokenizer.KEYWORDS, 235 "DEALLOCATE PREPARE": TokenType.COMMAND, 236 "DESCRIBE INPUT": TokenType.COMMAND, 237 "DESCRIBE OUTPUT": TokenType.COMMAND, 238 "RESET SESSION": TokenType.COMMAND, 239 "START": TokenType.BEGIN, 240 "MATCH_RECOGNIZE": TokenType.MATCH_RECOGNIZE, 241 "ROW": TokenType.STRUCT, 242 "IPADDRESS": TokenType.IPADDRESS, 243 "IPPREFIX": TokenType.IPPREFIX, 244 "TDIGEST": TokenType.TDIGEST, 245 "HYPERLOGLOG": TokenType.HLLSKETCH, 246 } 247 KEYWORDS.pop("/*+") 248 KEYWORDS.pop("QUALIFY") 249 250 class Parser(parser.Parser): 251 VALUES_FOLLOWED_BY_PAREN = False 252 253 FUNCTIONS = { 254 **parser.Parser.FUNCTIONS, 255 "ARBITRARY": exp.AnyValue.from_arg_list, 256 "APPROX_DISTINCT": exp.ApproxDistinct.from_arg_list, 257 "APPROX_PERCENTILE": _build_approx_percentile, 258 "BITWISE_AND": binary_from_function(exp.BitwiseAnd), 259 "BITWISE_NOT": lambda args: exp.BitwiseNot(this=seq_get(args, 0)), 260 "BITWISE_OR": binary_from_function(exp.BitwiseOr), 261 "BITWISE_XOR": binary_from_function(exp.BitwiseXor), 262 "CARDINALITY": exp.ArraySize.from_arg_list, 263 "CONTAINS": exp.ArrayContains.from_arg_list, 264 "DATE_ADD": lambda args: exp.DateAdd( 265 this=seq_get(args, 2), expression=seq_get(args, 1), unit=seq_get(args, 0) 266 ), 267 "DATE_DIFF": lambda args: exp.DateDiff( 268 this=seq_get(args, 2), expression=seq_get(args, 1), unit=seq_get(args, 0) 269 ), 270 "DATE_FORMAT": build_formatted_time(exp.TimeToStr, "presto"), 271 "DATE_PARSE": build_formatted_time(exp.StrToTime, "presto"), 272 "DATE_TRUNC": date_trunc_to_time, 273 "DAY_OF_WEEK": exp.DayOfWeekIso.from_arg_list, 274 "ELEMENT_AT": lambda args: exp.Bracket( 275 this=seq_get(args, 0), expressions=[seq_get(args, 1)], offset=1, safe=True 276 ), 277 "FROM_HEX": exp.Unhex.from_arg_list, 278 "FROM_UNIXTIME": _build_from_unixtime, 279 "FROM_UTF8": lambda args: exp.Decode( 280 this=seq_get(args, 0), replace=seq_get(args, 1), charset=exp.Literal.string("utf-8") 281 ), 282 "LEVENSHTEIN_DISTANCE": exp.Levenshtein.from_arg_list, 283 "NOW": exp.CurrentTimestamp.from_arg_list, 284 "REGEXP_EXTRACT": build_regexp_extract(exp.RegexpExtract), 285 "REGEXP_EXTRACT_ALL": build_regexp_extract(exp.RegexpExtractAll), 286 "REGEXP_REPLACE": lambda args: exp.RegexpReplace( 287 this=seq_get(args, 0), 288 expression=seq_get(args, 1), 289 replacement=seq_get(args, 2) or exp.Literal.string(""), 290 ), 291 "ROW": exp.Struct.from_arg_list, 292 "SEQUENCE": exp.GenerateSeries.from_arg_list, 293 "SET_AGG": exp.ArrayUniqueAgg.from_arg_list, 294 "SPLIT_TO_MAP": exp.StrToMap.from_arg_list, 295 "STRPOS": lambda args: exp.StrPosition( 296 this=seq_get(args, 0), substr=seq_get(args, 1), occurrence=seq_get(args, 2) 297 ), 298 "TO_CHAR": _build_to_char, 299 "TO_UNIXTIME": exp.TimeToUnix.from_arg_list, 300 "TO_UTF8": lambda args: exp.Encode( 301 this=seq_get(args, 0), charset=exp.Literal.string("utf-8") 302 ), 303 "MD5": exp.MD5Digest.from_arg_list, 304 "SHA256": lambda args: exp.SHA2(this=seq_get(args, 0), length=exp.Literal.number(256)), 305 "SHA512": lambda args: exp.SHA2(this=seq_get(args, 0), length=exp.Literal.number(512)), 306 } 307 308 FUNCTION_PARSERS = parser.Parser.FUNCTION_PARSERS.copy() 309 FUNCTION_PARSERS.pop("TRIM") 310 311 class Generator(generator.Generator): 312 INTERVAL_ALLOWS_PLURAL_FORM = False 313 JOIN_HINTS = False 314 TABLE_HINTS = False 315 QUERY_HINTS = False 316 IS_BOOL_ALLOWED = False 317 TZ_TO_WITH_TIME_ZONE = True 318 NVL2_SUPPORTED = False 319 STRUCT_DELIMITER = ("(", ")") 320 LIMIT_ONLY_LITERALS = True 321 SUPPORTS_SINGLE_ARG_CONCAT = False 322 LIKE_PROPERTY_INSIDE_SCHEMA = True 323 MULTI_ARG_DISTINCT = False 324 SUPPORTS_TO_NUMBER = False 325 HEX_FUNC = "TO_HEX" 326 PARSE_JSON_NAME = "JSON_PARSE" 327 PAD_FILL_PATTERN_IS_REQUIRED = True 328 EXCEPT_INTERSECT_SUPPORT_ALL_CLAUSE = False 329 SUPPORTS_MEDIAN = False 330 ARRAY_SIZE_NAME = "CARDINALITY" 331 332 PROPERTIES_LOCATION = { 333 **generator.Generator.PROPERTIES_LOCATION, 334 exp.LocationProperty: exp.Properties.Location.UNSUPPORTED, 335 exp.VolatileProperty: exp.Properties.Location.UNSUPPORTED, 336 } 337 338 TYPE_MAPPING = { 339 **generator.Generator.TYPE_MAPPING, 340 exp.DataType.Type.BINARY: "VARBINARY", 341 exp.DataType.Type.BIT: "BOOLEAN", 342 exp.DataType.Type.DATETIME: "TIMESTAMP", 343 exp.DataType.Type.DATETIME64: "TIMESTAMP", 344 exp.DataType.Type.FLOAT: "REAL", 345 exp.DataType.Type.HLLSKETCH: "HYPERLOGLOG", 346 exp.DataType.Type.INT: "INTEGER", 347 exp.DataType.Type.STRUCT: "ROW", 348 exp.DataType.Type.TEXT: "VARCHAR", 349 exp.DataType.Type.TIMESTAMPTZ: "TIMESTAMP", 350 exp.DataType.Type.TIMESTAMPNTZ: "TIMESTAMP", 351 exp.DataType.Type.TIMETZ: "TIME", 352 } 353 354 TRANSFORMS = { 355 **generator.Generator.TRANSFORMS, 356 exp.AnyValue: rename_func("ARBITRARY"), 357 exp.ApproxQuantile: rename_func("APPROX_PERCENTILE"), 358 exp.ArgMax: rename_func("MAX_BY"), 359 exp.ArgMin: rename_func("MIN_BY"), 360 exp.Array: lambda self, e: f"ARRAY[{self.expressions(e, flat=True)}]", 361 exp.ArrayAny: rename_func("ANY_MATCH"), 362 exp.ArrayConcat: rename_func("CONCAT"), 363 exp.ArrayContains: rename_func("CONTAINS"), 364 exp.ArrayToString: rename_func("ARRAY_JOIN"), 365 exp.ArrayUniqueAgg: rename_func("SET_AGG"), 366 exp.AtTimeZone: rename_func("AT_TIMEZONE"), 367 exp.BitwiseAnd: lambda self, e: self.func("BITWISE_AND", e.this, e.expression), 368 exp.BitwiseLeftShift: lambda self, e: self.func( 369 "BITWISE_ARITHMETIC_SHIFT_LEFT", e.this, e.expression 370 ), 371 exp.BitwiseNot: lambda self, e: self.func("BITWISE_NOT", e.this), 372 exp.BitwiseOr: lambda self, e: self.func("BITWISE_OR", e.this, e.expression), 373 exp.BitwiseRightShift: lambda self, e: self.func( 374 "BITWISE_ARITHMETIC_SHIFT_RIGHT", e.this, e.expression 375 ), 376 exp.BitwiseXor: lambda self, e: self.func("BITWISE_XOR", e.this, e.expression), 377 exp.Cast: transforms.preprocess([transforms.epoch_cast_to_ts]), 378 exp.CurrentTime: lambda *_: "CURRENT_TIME", 379 exp.CurrentTimestamp: lambda *_: "CURRENT_TIMESTAMP", 380 exp.CurrentUser: lambda *_: "CURRENT_USER", 381 exp.DateAdd: _date_delta_sql("DATE_ADD"), 382 exp.DateDiff: lambda self, e: self.func( 383 "DATE_DIFF", unit_to_str(e), e.expression, e.this 384 ), 385 exp.DateStrToDate: datestrtodate_sql, 386 exp.DateToDi: lambda self, 387 e: f"CAST(DATE_FORMAT({self.sql(e, 'this')}, {Presto.DATEINT_FORMAT}) AS INT)", 388 exp.DateSub: _date_delta_sql("DATE_ADD", negate_interval=True), 389 exp.DayOfWeek: lambda self, e: f"(({self.func('DAY_OF_WEEK', e.this)} % 7) + 1)", 390 exp.DayOfWeekIso: rename_func("DAY_OF_WEEK"), 391 exp.Decode: lambda self, e: encode_decode_sql(self, e, "FROM_UTF8"), 392 exp.DiToDate: lambda self, 393 e: f"CAST(DATE_PARSE(CAST({self.sql(e, 'this')} AS VARCHAR), {Presto.DATEINT_FORMAT}) AS DATE)", 394 exp.Encode: lambda self, e: encode_decode_sql(self, e, "TO_UTF8"), 395 exp.FileFormatProperty: lambda self, e: f"FORMAT='{e.name.upper()}'", 396 exp.First: _first_last_sql, 397 exp.FromTimeZone: lambda self, 398 e: f"WITH_TIMEZONE({self.sql(e, 'this')}, {self.sql(e, 'zone')}) AT TIME ZONE 'UTC'", 399 exp.GenerateSeries: sequence_sql, 400 exp.GenerateDateArray: sequence_sql, 401 exp.Group: transforms.preprocess([transforms.unalias_group]), 402 exp.If: if_sql(), 403 exp.ILike: no_ilike_sql, 404 exp.Initcap: _initcap_sql, 405 exp.Last: _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.Schema: _schema_sql, 420 exp.SchemaCommentProperty: lambda self, e: self.naked_property(e), 421 exp.Select: transforms.preprocess( 422 [ 423 transforms.eliminate_qualify, 424 transforms.eliminate_distinct_on, 425 transforms.explode_to_unnest(1), 426 transforms.eliminate_semi_and_anti_joins, 427 ] 428 ), 429 exp.SortArray: _no_sort_array, 430 exp.StrPosition: lambda self, e: strposition_sql(self, e, supports_occurrence=True), 431 exp.StrToDate: lambda self, e: f"CAST({_str_to_time_sql(self, e)} AS DATE)", 432 exp.StrToMap: rename_func("SPLIT_TO_MAP"), 433 exp.StrToTime: _str_to_time_sql, 434 exp.StructExtract: struct_extract_sql, 435 exp.Table: transforms.preprocess([transforms.unnest_generate_series]), 436 exp.Timestamp: no_timestamp_sql, 437 exp.TimestampAdd: _date_delta_sql("DATE_ADD"), 438 exp.TimestampTrunc: timestamptrunc_sql(), 439 exp.TimeStrToDate: timestrtotime_sql, 440 exp.TimeStrToTime: timestrtotime_sql, 441 exp.TimeStrToUnix: lambda self, e: self.func( 442 "TO_UNIXTIME", self.func("DATE_PARSE", e.this, Presto.TIME_FORMAT) 443 ), 444 exp.TimeToStr: lambda self, e: self.func("DATE_FORMAT", e.this, self.format_time(e)), 445 exp.TimeToUnix: rename_func("TO_UNIXTIME"), 446 exp.ToChar: lambda self, e: self.func("DATE_FORMAT", e.this, self.format_time(e)), 447 exp.TryCast: transforms.preprocess([transforms.epoch_cast_to_ts]), 448 exp.TsOrDiToDi: lambda self, 449 e: f"CAST(SUBSTR(REPLACE(CAST({self.sql(e, 'this')} AS VARCHAR), '-', ''), 1, 8) AS INT)", 450 exp.TsOrDsAdd: _ts_or_ds_add_sql, 451 exp.TsOrDsDiff: _ts_or_ds_diff_sql, 452 exp.TsOrDsToDate: _ts_or_ds_to_date_sql, 453 exp.Unhex: rename_func("FROM_HEX"), 454 exp.UnixToStr: lambda self, 455 e: f"DATE_FORMAT(FROM_UNIXTIME({self.sql(e, 'this')}), {self.format_time(e)})", 456 exp.UnixToTime: _unix_to_time_sql, 457 exp.UnixToTimeStr: lambda self, 458 e: f"CAST(FROM_UNIXTIME({self.sql(e, 'this')}) AS VARCHAR)", 459 exp.VariancePop: rename_func("VAR_POP"), 460 exp.With: transforms.preprocess([transforms.add_recursive_cte_column_names]), 461 exp.WithinGroup: transforms.preprocess( 462 [transforms.remove_within_group_for_percentiles] 463 ), 464 exp.Xor: bool_xor_sql, 465 exp.MD5Digest: rename_func("MD5"), 466 exp.SHA: rename_func("SHA1"), 467 exp.SHA2: sha256_sql, 468 } 469 470 RESERVED_KEYWORDS = { 471 "alter", 472 "and", 473 "as", 474 "between", 475 "by", 476 "case", 477 "cast", 478 "constraint", 479 "create", 480 "cross", 481 "current_time", 482 "current_timestamp", 483 "deallocate", 484 "delete", 485 "describe", 486 "distinct", 487 "drop", 488 "else", 489 "end", 490 "escape", 491 "except", 492 "execute", 493 "exists", 494 "extract", 495 "false", 496 "for", 497 "from", 498 "full", 499 "group", 500 "having", 501 "in", 502 "inner", 503 "insert", 504 "intersect", 505 "into", 506 "is", 507 "join", 508 "left", 509 "like", 510 "natural", 511 "not", 512 "null", 513 "on", 514 "or", 515 "order", 516 "outer", 517 "prepare", 518 "right", 519 "select", 520 "table", 521 "then", 522 "true", 523 "union", 524 "using", 525 "values", 526 "when", 527 "where", 528 "with", 529 } 530 531 def md5_sql(self, expression: exp.MD5) -> str: 532 this = expression.this 533 534 if not this.type: 535 from sqlglot.optimizer.annotate_types import annotate_types 536 537 this = annotate_types(this) 538 539 if this.is_type(*exp.DataType.TEXT_TYPES): 540 this = exp.Encode(this=this, charset=exp.Literal.string("utf-8")) 541 542 return self.func("LOWER", self.func("TO_HEX", self.func("MD5", self.sql(this)))) 543 544 def strtounix_sql(self, expression: exp.StrToUnix) -> str: 545 # Since `TO_UNIXTIME` requires a `TIMESTAMP`, we need to parse the argument into one. 546 # To do this, we first try to `DATE_PARSE` it, but since this can fail when there's a 547 # timezone involved, we wrap it in a `TRY` call and use `PARSE_DATETIME` as a fallback, 548 # which seems to be using the same time mapping as Hive, as per: 549 # https://joda-time.sourceforge.net/apidocs/org/joda/time/format/DateTimeFormat.html 550 this = expression.this 551 value_as_text = exp.cast(this, exp.DataType.Type.TEXT) 552 value_as_timestamp = ( 553 exp.cast(this, exp.DataType.Type.TIMESTAMP) if this.is_string else this 554 ) 555 556 parse_without_tz = self.func("DATE_PARSE", value_as_text, self.format_time(expression)) 557 558 formatted_value = self.func( 559 "DATE_FORMAT", value_as_timestamp, self.format_time(expression) 560 ) 561 parse_with_tz = self.func( 562 "PARSE_DATETIME", 563 formatted_value, 564 self.format_time(expression, Hive.INVERSE_TIME_MAPPING, Hive.INVERSE_TIME_TRIE), 565 ) 566 coalesced = self.func("COALESCE", self.func("TRY", parse_without_tz), parse_with_tz) 567 return self.func("TO_UNIXTIME", coalesced) 568 569 def bracket_sql(self, expression: exp.Bracket) -> str: 570 if expression.args.get("safe"): 571 return self.func( 572 "ELEMENT_AT", 573 expression.this, 574 seq_get( 575 apply_index_offset( 576 expression.this, 577 expression.expressions, 578 1 - expression.args.get("offset", 0), 579 ), 580 0, 581 ), 582 ) 583 return super().bracket_sql(expression) 584 585 def struct_sql(self, expression: exp.Struct) -> str: 586 from sqlglot.optimizer.annotate_types import annotate_types 587 588 expression = annotate_types(expression) 589 values: t.List[str] = [] 590 schema: t.List[str] = [] 591 unknown_type = False 592 593 for e in expression.expressions: 594 if isinstance(e, exp.PropertyEQ): 595 if e.type and e.type.is_type(exp.DataType.Type.UNKNOWN): 596 unknown_type = True 597 else: 598 schema.append(f"{self.sql(e, 'this')} {self.sql(e.type)}") 599 values.append(self.sql(e, "expression")) 600 else: 601 values.append(self.sql(e)) 602 603 size = len(expression.expressions) 604 605 if not size or len(schema) != size: 606 if unknown_type: 607 self.unsupported( 608 "Cannot convert untyped key-value definitions (try annotate_types)." 609 ) 610 return self.func("ROW", *values) 611 return f"CAST(ROW({', '.join(values)}) AS ROW({', '.join(schema)}))" 612 613 def interval_sql(self, expression: exp.Interval) -> str: 614 if expression.this and expression.text("unit").upper().startswith("WEEK"): 615 return f"({expression.this.name} * INTERVAL '7' DAY)" 616 return super().interval_sql(expression) 617 618 def transaction_sql(self, expression: exp.Transaction) -> str: 619 modes = expression.args.get("modes") 620 modes = f" {', '.join(modes)}" if modes else "" 621 return f"START TRANSACTION{modes}" 622 623 def offset_limit_modifiers( 624 self, expression: exp.Expression, fetch: bool, limit: t.Optional[exp.Fetch | exp.Limit] 625 ) -> t.List[str]: 626 return [ 627 self.sql(expression, "offset"), 628 self.sql(limit), 629 ] 630 631 def create_sql(self, expression: exp.Create) -> str: 632 """ 633 Presto doesn't support CREATE VIEW with expressions (ex: `CREATE VIEW x (cola)` then `(cola)` is the expression), 634 so we need to remove them 635 """ 636 kind = expression.args["kind"] 637 schema = expression.this 638 if kind == "VIEW" and schema.expressions: 639 expression.this.set("expressions", None) 640 return super().create_sql(expression) 641 642 def delete_sql(self, expression: exp.Delete) -> str: 643 """ 644 Presto only supports DELETE FROM for a single table without an alias, so we need 645 to remove the unnecessary parts. If the original DELETE statement contains more 646 than one table to be deleted, we can't safely map it 1-1 to a Presto statement. 647 """ 648 tables = expression.args.get("tables") or [expression.this] 649 if len(tables) > 1: 650 return super().delete_sql(expression) 651 652 table = tables[0] 653 expression.set("this", table) 654 expression.set("tables", None) 655 656 if isinstance(table, exp.Table): 657 table_alias = table.args.get("alias") 658 if table_alias: 659 table_alias.pop() 660 expression = t.cast(exp.Delete, expression.transform(unqualify_columns)) 661 662 return super().delete_sql(expression) 663 664 def jsonextract_sql(self, expression: exp.JSONExtract) -> str: 665 is_json_extract = self.dialect.settings.get("variant_extract_is_json_extract", True) 666 667 # Generate JSON_EXTRACT unless the user has configured that a Snowflake / Databricks 668 # VARIANT extract (e.g. col:x.y) should map to dot notation (i.e ROW access) in Presto/Trino 669 if not expression.args.get("variant_extract") or is_json_extract: 670 return self.func( 671 "JSON_EXTRACT", expression.this, expression.expression, *expression.expressions 672 ) 673 674 this = self.sql(expression, "this") 675 676 # Convert the JSONPath extraction `JSON_EXTRACT(col, '$.x.y) to a ROW access col.x.y 677 segments = [] 678 for path_key in expression.expression.expressions[1:]: 679 if not isinstance(path_key, exp.JSONPathKey): 680 # Cannot transpile subscripts, wildcards etc to dot notation 681 self.unsupported( 682 f"Cannot transpile JSONPath segment '{path_key}' to ROW access" 683 ) 684 continue 685 key = path_key.this 686 if not exp.SAFE_IDENTIFIER_RE.match(key): 687 key = f'"{key}"' 688 segments.append(f".{key}") 689 690 expr = "".join(segments) 691 692 return f"{this}{expr}" 693 694 def groupconcat_sql(self, expression: exp.GroupConcat) -> str: 695 return self.func( 696 "ARRAY_JOIN", 697 self.func("ARRAY_AGG", expression.this), 698 expression.args.get("separator"), 699 )
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.
225 class Tokenizer(tokens.Tokenizer): 226 HEX_STRINGS = [("x'", "'"), ("X'", "'")] 227 UNICODE_STRINGS = [ 228 (prefix + q, q) 229 for q in t.cast(t.List[str], tokens.Tokenizer.QUOTES) 230 for prefix in ("U&", "u&") 231 ] 232 233 KEYWORDS = { 234 **tokens.Tokenizer.KEYWORDS, 235 "DEALLOCATE PREPARE": TokenType.COMMAND, 236 "DESCRIBE INPUT": TokenType.COMMAND, 237 "DESCRIBE OUTPUT": TokenType.COMMAND, 238 "RESET SESSION": TokenType.COMMAND, 239 "START": TokenType.BEGIN, 240 "MATCH_RECOGNIZE": TokenType.MATCH_RECOGNIZE, 241 "ROW": TokenType.STRUCT, 242 "IPADDRESS": TokenType.IPADDRESS, 243 "IPPREFIX": TokenType.IPPREFIX, 244 "TDIGEST": TokenType.TDIGEST, 245 "HYPERLOGLOG": TokenType.HLLSKETCH, 246 } 247 KEYWORDS.pop("/*+") 248 KEYWORDS.pop("QUALIFY")
Inherited Members
- sqlglot.tokens.Tokenizer
- Tokenizer
- SINGLE_TOKENS
- BIT_STRINGS
- BYTE_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
- use_rs_tokenizer
- reset
- tokenize
- tokenize_rs
- size
- sql
- tokens
250 class Parser(parser.Parser): 251 VALUES_FOLLOWED_BY_PAREN = False 252 253 FUNCTIONS = { 254 **parser.Parser.FUNCTIONS, 255 "ARBITRARY": exp.AnyValue.from_arg_list, 256 "APPROX_DISTINCT": exp.ApproxDistinct.from_arg_list, 257 "APPROX_PERCENTILE": _build_approx_percentile, 258 "BITWISE_AND": binary_from_function(exp.BitwiseAnd), 259 "BITWISE_NOT": lambda args: exp.BitwiseNot(this=seq_get(args, 0)), 260 "BITWISE_OR": binary_from_function(exp.BitwiseOr), 261 "BITWISE_XOR": binary_from_function(exp.BitwiseXor), 262 "CARDINALITY": exp.ArraySize.from_arg_list, 263 "CONTAINS": exp.ArrayContains.from_arg_list, 264 "DATE_ADD": lambda args: exp.DateAdd( 265 this=seq_get(args, 2), expression=seq_get(args, 1), unit=seq_get(args, 0) 266 ), 267 "DATE_DIFF": lambda args: exp.DateDiff( 268 this=seq_get(args, 2), expression=seq_get(args, 1), unit=seq_get(args, 0) 269 ), 270 "DATE_FORMAT": build_formatted_time(exp.TimeToStr, "presto"), 271 "DATE_PARSE": build_formatted_time(exp.StrToTime, "presto"), 272 "DATE_TRUNC": date_trunc_to_time, 273 "DAY_OF_WEEK": exp.DayOfWeekIso.from_arg_list, 274 "ELEMENT_AT": lambda args: exp.Bracket( 275 this=seq_get(args, 0), expressions=[seq_get(args, 1)], offset=1, safe=True 276 ), 277 "FROM_HEX": exp.Unhex.from_arg_list, 278 "FROM_UNIXTIME": _build_from_unixtime, 279 "FROM_UTF8": lambda args: exp.Decode( 280 this=seq_get(args, 0), replace=seq_get(args, 1), charset=exp.Literal.string("utf-8") 281 ), 282 "LEVENSHTEIN_DISTANCE": exp.Levenshtein.from_arg_list, 283 "NOW": exp.CurrentTimestamp.from_arg_list, 284 "REGEXP_EXTRACT": build_regexp_extract(exp.RegexpExtract), 285 "REGEXP_EXTRACT_ALL": build_regexp_extract(exp.RegexpExtractAll), 286 "REGEXP_REPLACE": lambda args: exp.RegexpReplace( 287 this=seq_get(args, 0), 288 expression=seq_get(args, 1), 289 replacement=seq_get(args, 2) or exp.Literal.string(""), 290 ), 291 "ROW": exp.Struct.from_arg_list, 292 "SEQUENCE": exp.GenerateSeries.from_arg_list, 293 "SET_AGG": exp.ArrayUniqueAgg.from_arg_list, 294 "SPLIT_TO_MAP": exp.StrToMap.from_arg_list, 295 "STRPOS": lambda args: exp.StrPosition( 296 this=seq_get(args, 0), substr=seq_get(args, 1), occurrence=seq_get(args, 2) 297 ), 298 "TO_CHAR": _build_to_char, 299 "TO_UNIXTIME": exp.TimeToUnix.from_arg_list, 300 "TO_UTF8": lambda args: exp.Encode( 301 this=seq_get(args, 0), charset=exp.Literal.string("utf-8") 302 ), 303 "MD5": exp.MD5Digest.from_arg_list, 304 "SHA256": lambda args: exp.SHA2(this=seq_get(args, 0), length=exp.Literal.number(256)), 305 "SHA512": lambda args: exp.SHA2(this=seq_get(args, 0), length=exp.Literal.number(512)), 306 } 307 308 FUNCTION_PARSERS = parser.Parser.FUNCTION_PARSERS.copy() 309 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
- 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
- ANALYZE_STYLES
- ANALYZE_EXPRESSION_PARSERS
- PARTITION_KEYWORDS
- AMBIGUOUS_ALIAS_TOKENS
- OPERATION_MODIFIERS
- RECURSIVE_CTE_SEARCH_KIND
- MODIFIABLES
- 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
- OPTIONAL_ALIAS_TOKEN_CTE
- error_level
- error_message_context
- max_errors
- dialect
- reset
- parse
- parse_into
- check_errors
- raise_error
- expression
- validate_expression
- errors
- sql
311 class Generator(generator.Generator): 312 INTERVAL_ALLOWS_PLURAL_FORM = False 313 JOIN_HINTS = False 314 TABLE_HINTS = False 315 QUERY_HINTS = False 316 IS_BOOL_ALLOWED = False 317 TZ_TO_WITH_TIME_ZONE = True 318 NVL2_SUPPORTED = False 319 STRUCT_DELIMITER = ("(", ")") 320 LIMIT_ONLY_LITERALS = True 321 SUPPORTS_SINGLE_ARG_CONCAT = False 322 LIKE_PROPERTY_INSIDE_SCHEMA = True 323 MULTI_ARG_DISTINCT = False 324 SUPPORTS_TO_NUMBER = False 325 HEX_FUNC = "TO_HEX" 326 PARSE_JSON_NAME = "JSON_PARSE" 327 PAD_FILL_PATTERN_IS_REQUIRED = True 328 EXCEPT_INTERSECT_SUPPORT_ALL_CLAUSE = False 329 SUPPORTS_MEDIAN = False 330 ARRAY_SIZE_NAME = "CARDINALITY" 331 332 PROPERTIES_LOCATION = { 333 **generator.Generator.PROPERTIES_LOCATION, 334 exp.LocationProperty: exp.Properties.Location.UNSUPPORTED, 335 exp.VolatileProperty: exp.Properties.Location.UNSUPPORTED, 336 } 337 338 TYPE_MAPPING = { 339 **generator.Generator.TYPE_MAPPING, 340 exp.DataType.Type.BINARY: "VARBINARY", 341 exp.DataType.Type.BIT: "BOOLEAN", 342 exp.DataType.Type.DATETIME: "TIMESTAMP", 343 exp.DataType.Type.DATETIME64: "TIMESTAMP", 344 exp.DataType.Type.FLOAT: "REAL", 345 exp.DataType.Type.HLLSKETCH: "HYPERLOGLOG", 346 exp.DataType.Type.INT: "INTEGER", 347 exp.DataType.Type.STRUCT: "ROW", 348 exp.DataType.Type.TEXT: "VARCHAR", 349 exp.DataType.Type.TIMESTAMPTZ: "TIMESTAMP", 350 exp.DataType.Type.TIMESTAMPNTZ: "TIMESTAMP", 351 exp.DataType.Type.TIMETZ: "TIME", 352 } 353 354 TRANSFORMS = { 355 **generator.Generator.TRANSFORMS, 356 exp.AnyValue: rename_func("ARBITRARY"), 357 exp.ApproxQuantile: rename_func("APPROX_PERCENTILE"), 358 exp.ArgMax: rename_func("MAX_BY"), 359 exp.ArgMin: rename_func("MIN_BY"), 360 exp.Array: lambda self, e: f"ARRAY[{self.expressions(e, flat=True)}]", 361 exp.ArrayAny: rename_func("ANY_MATCH"), 362 exp.ArrayConcat: rename_func("CONCAT"), 363 exp.ArrayContains: rename_func("CONTAINS"), 364 exp.ArrayToString: rename_func("ARRAY_JOIN"), 365 exp.ArrayUniqueAgg: rename_func("SET_AGG"), 366 exp.AtTimeZone: rename_func("AT_TIMEZONE"), 367 exp.BitwiseAnd: lambda self, e: self.func("BITWISE_AND", e.this, e.expression), 368 exp.BitwiseLeftShift: lambda self, e: self.func( 369 "BITWISE_ARITHMETIC_SHIFT_LEFT", e.this, e.expression 370 ), 371 exp.BitwiseNot: lambda self, e: self.func("BITWISE_NOT", e.this), 372 exp.BitwiseOr: lambda self, e: self.func("BITWISE_OR", e.this, e.expression), 373 exp.BitwiseRightShift: lambda self, e: self.func( 374 "BITWISE_ARITHMETIC_SHIFT_RIGHT", e.this, e.expression 375 ), 376 exp.BitwiseXor: lambda self, e: self.func("BITWISE_XOR", e.this, e.expression), 377 exp.Cast: transforms.preprocess([transforms.epoch_cast_to_ts]), 378 exp.CurrentTime: lambda *_: "CURRENT_TIME", 379 exp.CurrentTimestamp: lambda *_: "CURRENT_TIMESTAMP", 380 exp.CurrentUser: lambda *_: "CURRENT_USER", 381 exp.DateAdd: _date_delta_sql("DATE_ADD"), 382 exp.DateDiff: lambda self, e: self.func( 383 "DATE_DIFF", unit_to_str(e), e.expression, e.this 384 ), 385 exp.DateStrToDate: datestrtodate_sql, 386 exp.DateToDi: lambda self, 387 e: f"CAST(DATE_FORMAT({self.sql(e, 'this')}, {Presto.DATEINT_FORMAT}) AS INT)", 388 exp.DateSub: _date_delta_sql("DATE_ADD", negate_interval=True), 389 exp.DayOfWeek: lambda self, e: f"(({self.func('DAY_OF_WEEK', e.this)} % 7) + 1)", 390 exp.DayOfWeekIso: rename_func("DAY_OF_WEEK"), 391 exp.Decode: lambda self, e: encode_decode_sql(self, e, "FROM_UTF8"), 392 exp.DiToDate: lambda self, 393 e: f"CAST(DATE_PARSE(CAST({self.sql(e, 'this')} AS VARCHAR), {Presto.DATEINT_FORMAT}) AS DATE)", 394 exp.Encode: lambda self, e: encode_decode_sql(self, e, "TO_UTF8"), 395 exp.FileFormatProperty: lambda self, e: f"FORMAT='{e.name.upper()}'", 396 exp.First: _first_last_sql, 397 exp.FromTimeZone: lambda self, 398 e: f"WITH_TIMEZONE({self.sql(e, 'this')}, {self.sql(e, 'zone')}) AT TIME ZONE 'UTC'", 399 exp.GenerateSeries: sequence_sql, 400 exp.GenerateDateArray: sequence_sql, 401 exp.Group: transforms.preprocess([transforms.unalias_group]), 402 exp.If: if_sql(), 403 exp.ILike: no_ilike_sql, 404 exp.Initcap: _initcap_sql, 405 exp.Last: _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.Schema: _schema_sql, 420 exp.SchemaCommentProperty: lambda self, e: self.naked_property(e), 421 exp.Select: transforms.preprocess( 422 [ 423 transforms.eliminate_qualify, 424 transforms.eliminate_distinct_on, 425 transforms.explode_to_unnest(1), 426 transforms.eliminate_semi_and_anti_joins, 427 ] 428 ), 429 exp.SortArray: _no_sort_array, 430 exp.StrPosition: lambda self, e: strposition_sql(self, e, supports_occurrence=True), 431 exp.StrToDate: lambda self, e: f"CAST({_str_to_time_sql(self, e)} AS DATE)", 432 exp.StrToMap: rename_func("SPLIT_TO_MAP"), 433 exp.StrToTime: _str_to_time_sql, 434 exp.StructExtract: struct_extract_sql, 435 exp.Table: transforms.preprocess([transforms.unnest_generate_series]), 436 exp.Timestamp: no_timestamp_sql, 437 exp.TimestampAdd: _date_delta_sql("DATE_ADD"), 438 exp.TimestampTrunc: timestamptrunc_sql(), 439 exp.TimeStrToDate: timestrtotime_sql, 440 exp.TimeStrToTime: timestrtotime_sql, 441 exp.TimeStrToUnix: lambda self, e: self.func( 442 "TO_UNIXTIME", self.func("DATE_PARSE", e.this, Presto.TIME_FORMAT) 443 ), 444 exp.TimeToStr: lambda self, e: self.func("DATE_FORMAT", e.this, self.format_time(e)), 445 exp.TimeToUnix: rename_func("TO_UNIXTIME"), 446 exp.ToChar: lambda self, e: self.func("DATE_FORMAT", e.this, self.format_time(e)), 447 exp.TryCast: transforms.preprocess([transforms.epoch_cast_to_ts]), 448 exp.TsOrDiToDi: lambda self, 449 e: f"CAST(SUBSTR(REPLACE(CAST({self.sql(e, 'this')} AS VARCHAR), '-', ''), 1, 8) AS INT)", 450 exp.TsOrDsAdd: _ts_or_ds_add_sql, 451 exp.TsOrDsDiff: _ts_or_ds_diff_sql, 452 exp.TsOrDsToDate: _ts_or_ds_to_date_sql, 453 exp.Unhex: rename_func("FROM_HEX"), 454 exp.UnixToStr: lambda self, 455 e: f"DATE_FORMAT(FROM_UNIXTIME({self.sql(e, 'this')}), {self.format_time(e)})", 456 exp.UnixToTime: _unix_to_time_sql, 457 exp.UnixToTimeStr: lambda self, 458 e: f"CAST(FROM_UNIXTIME({self.sql(e, 'this')}) AS VARCHAR)", 459 exp.VariancePop: rename_func("VAR_POP"), 460 exp.With: transforms.preprocess([transforms.add_recursive_cte_column_names]), 461 exp.WithinGroup: transforms.preprocess( 462 [transforms.remove_within_group_for_percentiles] 463 ), 464 exp.Xor: bool_xor_sql, 465 exp.MD5Digest: rename_func("MD5"), 466 exp.SHA: rename_func("SHA1"), 467 exp.SHA2: sha256_sql, 468 } 469 470 RESERVED_KEYWORDS = { 471 "alter", 472 "and", 473 "as", 474 "between", 475 "by", 476 "case", 477 "cast", 478 "constraint", 479 "create", 480 "cross", 481 "current_time", 482 "current_timestamp", 483 "deallocate", 484 "delete", 485 "describe", 486 "distinct", 487 "drop", 488 "else", 489 "end", 490 "escape", 491 "except", 492 "execute", 493 "exists", 494 "extract", 495 "false", 496 "for", 497 "from", 498 "full", 499 "group", 500 "having", 501 "in", 502 "inner", 503 "insert", 504 "intersect", 505 "into", 506 "is", 507 "join", 508 "left", 509 "like", 510 "natural", 511 "not", 512 "null", 513 "on", 514 "or", 515 "order", 516 "outer", 517 "prepare", 518 "right", 519 "select", 520 "table", 521 "then", 522 "true", 523 "union", 524 "using", 525 "values", 526 "when", 527 "where", 528 "with", 529 } 530 531 def md5_sql(self, expression: exp.MD5) -> str: 532 this = expression.this 533 534 if not this.type: 535 from sqlglot.optimizer.annotate_types import annotate_types 536 537 this = annotate_types(this) 538 539 if this.is_type(*exp.DataType.TEXT_TYPES): 540 this = exp.Encode(this=this, charset=exp.Literal.string("utf-8")) 541 542 return self.func("LOWER", self.func("TO_HEX", self.func("MD5", self.sql(this)))) 543 544 def strtounix_sql(self, expression: exp.StrToUnix) -> str: 545 # Since `TO_UNIXTIME` requires a `TIMESTAMP`, we need to parse the argument into one. 546 # To do this, we first try to `DATE_PARSE` it, but since this can fail when there's a 547 # timezone involved, we wrap it in a `TRY` call and use `PARSE_DATETIME` as a fallback, 548 # which seems to be using the same time mapping as Hive, as per: 549 # https://joda-time.sourceforge.net/apidocs/org/joda/time/format/DateTimeFormat.html 550 this = expression.this 551 value_as_text = exp.cast(this, exp.DataType.Type.TEXT) 552 value_as_timestamp = ( 553 exp.cast(this, exp.DataType.Type.TIMESTAMP) if this.is_string else this 554 ) 555 556 parse_without_tz = self.func("DATE_PARSE", value_as_text, self.format_time(expression)) 557 558 formatted_value = self.func( 559 "DATE_FORMAT", value_as_timestamp, self.format_time(expression) 560 ) 561 parse_with_tz = self.func( 562 "PARSE_DATETIME", 563 formatted_value, 564 self.format_time(expression, Hive.INVERSE_TIME_MAPPING, Hive.INVERSE_TIME_TRIE), 565 ) 566 coalesced = self.func("COALESCE", self.func("TRY", parse_without_tz), parse_with_tz) 567 return self.func("TO_UNIXTIME", coalesced) 568 569 def bracket_sql(self, expression: exp.Bracket) -> str: 570 if expression.args.get("safe"): 571 return self.func( 572 "ELEMENT_AT", 573 expression.this, 574 seq_get( 575 apply_index_offset( 576 expression.this, 577 expression.expressions, 578 1 - expression.args.get("offset", 0), 579 ), 580 0, 581 ), 582 ) 583 return super().bracket_sql(expression) 584 585 def struct_sql(self, expression: exp.Struct) -> str: 586 from sqlglot.optimizer.annotate_types import annotate_types 587 588 expression = annotate_types(expression) 589 values: t.List[str] = [] 590 schema: t.List[str] = [] 591 unknown_type = False 592 593 for e in expression.expressions: 594 if isinstance(e, exp.PropertyEQ): 595 if e.type and e.type.is_type(exp.DataType.Type.UNKNOWN): 596 unknown_type = True 597 else: 598 schema.append(f"{self.sql(e, 'this')} {self.sql(e.type)}") 599 values.append(self.sql(e, "expression")) 600 else: 601 values.append(self.sql(e)) 602 603 size = len(expression.expressions) 604 605 if not size or len(schema) != size: 606 if unknown_type: 607 self.unsupported( 608 "Cannot convert untyped key-value definitions (try annotate_types)." 609 ) 610 return self.func("ROW", *values) 611 return f"CAST(ROW({', '.join(values)}) AS ROW({', '.join(schema)}))" 612 613 def interval_sql(self, expression: exp.Interval) -> str: 614 if expression.this and expression.text("unit").upper().startswith("WEEK"): 615 return f"({expression.this.name} * INTERVAL '7' DAY)" 616 return super().interval_sql(expression) 617 618 def transaction_sql(self, expression: exp.Transaction) -> str: 619 modes = expression.args.get("modes") 620 modes = f" {', '.join(modes)}" if modes else "" 621 return f"START TRANSACTION{modes}" 622 623 def offset_limit_modifiers( 624 self, expression: exp.Expression, fetch: bool, limit: t.Optional[exp.Fetch | exp.Limit] 625 ) -> t.List[str]: 626 return [ 627 self.sql(expression, "offset"), 628 self.sql(limit), 629 ] 630 631 def create_sql(self, expression: exp.Create) -> str: 632 """ 633 Presto doesn't support CREATE VIEW with expressions (ex: `CREATE VIEW x (cola)` then `(cola)` is the expression), 634 so we need to remove them 635 """ 636 kind = expression.args["kind"] 637 schema = expression.this 638 if kind == "VIEW" and schema.expressions: 639 expression.this.set("expressions", None) 640 return super().create_sql(expression) 641 642 def delete_sql(self, expression: exp.Delete) -> str: 643 """ 644 Presto only supports DELETE FROM for a single table without an alias, so we need 645 to remove the unnecessary parts. If the original DELETE statement contains more 646 than one table to be deleted, we can't safely map it 1-1 to a Presto statement. 647 """ 648 tables = expression.args.get("tables") or [expression.this] 649 if len(tables) > 1: 650 return super().delete_sql(expression) 651 652 table = tables[0] 653 expression.set("this", table) 654 expression.set("tables", None) 655 656 if isinstance(table, exp.Table): 657 table_alias = table.args.get("alias") 658 if table_alias: 659 table_alias.pop() 660 expression = t.cast(exp.Delete, expression.transform(unqualify_columns)) 661 662 return super().delete_sql(expression) 663 664 def jsonextract_sql(self, expression: exp.JSONExtract) -> str: 665 is_json_extract = self.dialect.settings.get("variant_extract_is_json_extract", True) 666 667 # Generate JSON_EXTRACT unless the user has configured that a Snowflake / Databricks 668 # VARIANT extract (e.g. col:x.y) should map to dot notation (i.e ROW access) in Presto/Trino 669 if not expression.args.get("variant_extract") or is_json_extract: 670 return self.func( 671 "JSON_EXTRACT", expression.this, expression.expression, *expression.expressions 672 ) 673 674 this = self.sql(expression, "this") 675 676 # Convert the JSONPath extraction `JSON_EXTRACT(col, '$.x.y) to a ROW access col.x.y 677 segments = [] 678 for path_key in expression.expression.expressions[1:]: 679 if not isinstance(path_key, exp.JSONPathKey): 680 # Cannot transpile subscripts, wildcards etc to dot notation 681 self.unsupported( 682 f"Cannot transpile JSONPath segment '{path_key}' to ROW access" 683 ) 684 continue 685 key = path_key.this 686 if not exp.SAFE_IDENTIFIER_RE.match(key): 687 key = f'"{key}"' 688 segments.append(f".{key}") 689 690 expr = "".join(segments) 691 692 return f"{this}{expr}" 693 694 def groupconcat_sql(self, expression: exp.GroupConcat) -> str: 695 return self.func( 696 "ARRAY_JOIN", 697 self.func("ARRAY_AGG", expression.this), 698 expression.args.get("separator"), 699 )
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
531 def md5_sql(self, expression: exp.MD5) -> str: 532 this = expression.this 533 534 if not this.type: 535 from sqlglot.optimizer.annotate_types import annotate_types 536 537 this = annotate_types(this) 538 539 if this.is_type(*exp.DataType.TEXT_TYPES): 540 this = exp.Encode(this=this, charset=exp.Literal.string("utf-8")) 541 542 return self.func("LOWER", self.func("TO_HEX", self.func("MD5", self.sql(this))))
544 def strtounix_sql(self, expression: exp.StrToUnix) -> str: 545 # Since `TO_UNIXTIME` requires a `TIMESTAMP`, we need to parse the argument into one. 546 # To do this, we first try to `DATE_PARSE` it, but since this can fail when there's a 547 # timezone involved, we wrap it in a `TRY` call and use `PARSE_DATETIME` as a fallback, 548 # which seems to be using the same time mapping as Hive, as per: 549 # https://joda-time.sourceforge.net/apidocs/org/joda/time/format/DateTimeFormat.html 550 this = expression.this 551 value_as_text = exp.cast(this, exp.DataType.Type.TEXT) 552 value_as_timestamp = ( 553 exp.cast(this, exp.DataType.Type.TIMESTAMP) if this.is_string else this 554 ) 555 556 parse_without_tz = self.func("DATE_PARSE", value_as_text, self.format_time(expression)) 557 558 formatted_value = self.func( 559 "DATE_FORMAT", value_as_timestamp, self.format_time(expression) 560 ) 561 parse_with_tz = self.func( 562 "PARSE_DATETIME", 563 formatted_value, 564 self.format_time(expression, Hive.INVERSE_TIME_MAPPING, Hive.INVERSE_TIME_TRIE), 565 ) 566 coalesced = self.func("COALESCE", self.func("TRY", parse_without_tz), parse_with_tz) 567 return self.func("TO_UNIXTIME", coalesced)
569 def bracket_sql(self, expression: exp.Bracket) -> str: 570 if expression.args.get("safe"): 571 return self.func( 572 "ELEMENT_AT", 573 expression.this, 574 seq_get( 575 apply_index_offset( 576 expression.this, 577 expression.expressions, 578 1 - expression.args.get("offset", 0), 579 ), 580 0, 581 ), 582 ) 583 return super().bracket_sql(expression)
585 def struct_sql(self, expression: exp.Struct) -> str: 586 from sqlglot.optimizer.annotate_types import annotate_types 587 588 expression = annotate_types(expression) 589 values: t.List[str] = [] 590 schema: t.List[str] = [] 591 unknown_type = False 592 593 for e in expression.expressions: 594 if isinstance(e, exp.PropertyEQ): 595 if e.type and e.type.is_type(exp.DataType.Type.UNKNOWN): 596 unknown_type = True 597 else: 598 schema.append(f"{self.sql(e, 'this')} {self.sql(e.type)}") 599 values.append(self.sql(e, "expression")) 600 else: 601 values.append(self.sql(e)) 602 603 size = len(expression.expressions) 604 605 if not size or len(schema) != size: 606 if unknown_type: 607 self.unsupported( 608 "Cannot convert untyped key-value definitions (try annotate_types)." 609 ) 610 return self.func("ROW", *values) 611 return f"CAST(ROW({', '.join(values)}) AS ROW({', '.join(schema)}))"
631 def create_sql(self, expression: exp.Create) -> str: 632 """ 633 Presto doesn't support CREATE VIEW with expressions (ex: `CREATE VIEW x (cola)` then `(cola)` is the expression), 634 so we need to remove them 635 """ 636 kind = expression.args["kind"] 637 schema = expression.this 638 if kind == "VIEW" and schema.expressions: 639 expression.this.set("expressions", None) 640 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
642 def delete_sql(self, expression: exp.Delete) -> str: 643 """ 644 Presto only supports DELETE FROM for a single table without an alias, so we need 645 to remove the unnecessary parts. If the original DELETE statement contains more 646 than one table to be deleted, we can't safely map it 1-1 to a Presto statement. 647 """ 648 tables = expression.args.get("tables") or [expression.this] 649 if len(tables) > 1: 650 return super().delete_sql(expression) 651 652 table = tables[0] 653 expression.set("this", table) 654 expression.set("tables", None) 655 656 if isinstance(table, exp.Table): 657 table_alias = table.args.get("alias") 658 if table_alias: 659 table_alias.pop() 660 expression = t.cast(exp.Delete, expression.transform(unqualify_columns)) 661 662 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.
664 def jsonextract_sql(self, expression: exp.JSONExtract) -> str: 665 is_json_extract = self.dialect.settings.get("variant_extract_is_json_extract", True) 666 667 # Generate JSON_EXTRACT unless the user has configured that a Snowflake / Databricks 668 # VARIANT extract (e.g. col:x.y) should map to dot notation (i.e ROW access) in Presto/Trino 669 if not expression.args.get("variant_extract") or is_json_extract: 670 return self.func( 671 "JSON_EXTRACT", expression.this, expression.expression, *expression.expressions 672 ) 673 674 this = self.sql(expression, "this") 675 676 # Convert the JSONPath extraction `JSON_EXTRACT(col, '$.x.y) to a ROW access col.x.y 677 segments = [] 678 for path_key in expression.expression.expressions[1:]: 679 if not isinstance(path_key, exp.JSONPathKey): 680 # Cannot transpile subscripts, wildcards etc to dot notation 681 self.unsupported( 682 f"Cannot transpile JSONPath segment '{path_key}' to ROW access" 683 ) 684 continue 685 key = path_key.this 686 if not exp.SAFE_IDENTIFIER_RE.match(key): 687 key = f'"{key}"' 688 segments.append(f".{key}") 689 690 expr = "".join(segments) 691 692 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
- ALTER_SET_TYPE
- 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
- limitoptions_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
- tablefromrows_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
- alterindex_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
- safedivide_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
- jsoncast_sql
- try_sql
- log_sql
- use_sql
- binary
- ceil_floor
- 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
- whens_sql
- merge_sql
- tochar_sql
- tonumber_sql
- dictproperty_sql
- dictrange_sql
- dictsubproperty_sql
- duplicatekeyproperty_sql
- uniquekeyproperty_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
- jsonextractquote_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
- xmlelement_sql
- partitionbyrangeproperty_sql
- partitionbyrangepropertydynamic_sql
- unpivotcolumns_sql
- analyzesample_sql
- analyzestatistics_sql
- analyzehistogram_sql
- analyzedelete_sql
- analyzelistchainedrows_sql
- analyzevalidate_sql
- analyze_sql
- xmltable_sql
- xmlnamespace_sql
- export_sql
- declare_sql
- declareitem_sql
- recursivewithsearch_sql
- parameterizedagg_sql
- anonymousaggfunc_sql
- combinedaggfunc_sql
- combinedparameterizedagg_sql
- show_sql
- put_sql