sqlglot.dialects.snowflake
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 date_delta_sql, 11 date_trunc_to_time, 12 datestrtodate_sql, 13 build_formatted_time, 14 if_sql, 15 inline_array_sql, 16 max_or_greatest, 17 min_or_least, 18 rename_func, 19 timestamptrunc_sql, 20 timestrtotime_sql, 21 var_map_sql, 22) 23from sqlglot.helper import flatten, is_float, is_int, seq_get 24from sqlglot.tokens import TokenType 25 26if t.TYPE_CHECKING: 27 from sqlglot._typing import E 28 29 30# from https://docs.snowflake.com/en/sql-reference/functions/to_timestamp.html 31def _build_datetime( 32 name: str, kind: exp.DataType.Type, safe: bool = False 33) -> t.Callable[[t.List], exp.Func]: 34 def _builder(args: t.List) -> exp.Func: 35 value = seq_get(args, 0) 36 int_value = value is not None and is_int(value.name) 37 38 if isinstance(value, exp.Literal): 39 # Converts calls like `TO_TIME('01:02:03')` into casts 40 if len(args) == 1 and value.is_string and not int_value: 41 return exp.cast(value, kind) 42 43 # Handles `TO_TIMESTAMP(str, fmt)` and `TO_TIMESTAMP(num, scale)` as special 44 # cases so we can transpile them, since they're relatively common 45 if kind == exp.DataType.Type.TIMESTAMP: 46 if int_value: 47 return exp.UnixToTime(this=value, scale=seq_get(args, 1)) 48 if not is_float(value.this): 49 return build_formatted_time(exp.StrToTime, "snowflake")(args) 50 51 if kind == exp.DataType.Type.DATE and not int_value: 52 formatted_exp = build_formatted_time(exp.TsOrDsToDate, "snowflake")(args) 53 formatted_exp.set("safe", safe) 54 return formatted_exp 55 56 return exp.Anonymous(this=name, expressions=args) 57 58 return _builder 59 60 61def _build_object_construct(args: t.List) -> t.Union[exp.StarMap, exp.Struct]: 62 expression = parser.build_var_map(args) 63 64 if isinstance(expression, exp.StarMap): 65 return expression 66 67 return exp.Struct( 68 expressions=[ 69 exp.PropertyEQ(this=k, expression=v) for k, v in zip(expression.keys, expression.values) 70 ] 71 ) 72 73 74def _build_datediff(args: t.List) -> exp.DateDiff: 75 return exp.DateDiff( 76 this=seq_get(args, 2), expression=seq_get(args, 1), unit=_map_date_part(seq_get(args, 0)) 77 ) 78 79 80def _build_date_time_add(expr_type: t.Type[E]) -> t.Callable[[t.List], E]: 81 def _builder(args: t.List) -> E: 82 return expr_type( 83 this=seq_get(args, 2), 84 expression=seq_get(args, 1), 85 unit=_map_date_part(seq_get(args, 0)), 86 ) 87 88 return _builder 89 90 91# https://docs.snowflake.com/en/sql-reference/functions/div0 92def _build_if_from_div0(args: t.List) -> exp.If: 93 cond = exp.EQ(this=seq_get(args, 1), expression=exp.Literal.number(0)) 94 true = exp.Literal.number(0) 95 false = exp.Div(this=seq_get(args, 0), expression=seq_get(args, 1)) 96 return exp.If(this=cond, true=true, false=false) 97 98 99# https://docs.snowflake.com/en/sql-reference/functions/zeroifnull 100def _build_if_from_zeroifnull(args: t.List) -> exp.If: 101 cond = exp.Is(this=seq_get(args, 0), expression=exp.Null()) 102 return exp.If(this=cond, true=exp.Literal.number(0), false=seq_get(args, 0)) 103 104 105# https://docs.snowflake.com/en/sql-reference/functions/zeroifnull 106def _build_if_from_nullifzero(args: t.List) -> exp.If: 107 cond = exp.EQ(this=seq_get(args, 0), expression=exp.Literal.number(0)) 108 return exp.If(this=cond, true=exp.Null(), false=seq_get(args, 0)) 109 110 111def _regexpilike_sql(self: Snowflake.Generator, expression: exp.RegexpILike) -> str: 112 flag = expression.text("flag") 113 114 if "i" not in flag: 115 flag += "i" 116 117 return self.func( 118 "REGEXP_LIKE", expression.this, expression.expression, exp.Literal.string(flag) 119 ) 120 121 122def _build_convert_timezone(args: t.List) -> t.Union[exp.Anonymous, exp.AtTimeZone]: 123 if len(args) == 3: 124 return exp.Anonymous(this="CONVERT_TIMEZONE", expressions=args) 125 return exp.AtTimeZone(this=seq_get(args, 1), zone=seq_get(args, 0)) 126 127 128def _build_regexp_replace(args: t.List) -> exp.RegexpReplace: 129 regexp_replace = exp.RegexpReplace.from_arg_list(args) 130 131 if not regexp_replace.args.get("replacement"): 132 regexp_replace.set("replacement", exp.Literal.string("")) 133 134 return regexp_replace 135 136 137def _show_parser(*args: t.Any, **kwargs: t.Any) -> t.Callable[[Snowflake.Parser], exp.Show]: 138 def _parse(self: Snowflake.Parser) -> exp.Show: 139 return self._parse_show_snowflake(*args, **kwargs) 140 141 return _parse 142 143 144DATE_PART_MAPPING = { 145 "Y": "YEAR", 146 "YY": "YEAR", 147 "YYY": "YEAR", 148 "YYYY": "YEAR", 149 "YR": "YEAR", 150 "YEARS": "YEAR", 151 "YRS": "YEAR", 152 "MM": "MONTH", 153 "MON": "MONTH", 154 "MONS": "MONTH", 155 "MONTHS": "MONTH", 156 "D": "DAY", 157 "DD": "DAY", 158 "DAYS": "DAY", 159 "DAYOFMONTH": "DAY", 160 "WEEKDAY": "DAYOFWEEK", 161 "DOW": "DAYOFWEEK", 162 "DW": "DAYOFWEEK", 163 "WEEKDAY_ISO": "DAYOFWEEKISO", 164 "DOW_ISO": "DAYOFWEEKISO", 165 "DW_ISO": "DAYOFWEEKISO", 166 "YEARDAY": "DAYOFYEAR", 167 "DOY": "DAYOFYEAR", 168 "DY": "DAYOFYEAR", 169 "W": "WEEK", 170 "WK": "WEEK", 171 "WEEKOFYEAR": "WEEK", 172 "WOY": "WEEK", 173 "WY": "WEEK", 174 "WEEK_ISO": "WEEKISO", 175 "WEEKOFYEARISO": "WEEKISO", 176 "WEEKOFYEAR_ISO": "WEEKISO", 177 "Q": "QUARTER", 178 "QTR": "QUARTER", 179 "QTRS": "QUARTER", 180 "QUARTERS": "QUARTER", 181 "H": "HOUR", 182 "HH": "HOUR", 183 "HR": "HOUR", 184 "HOURS": "HOUR", 185 "HRS": "HOUR", 186 "M": "MINUTE", 187 "MI": "MINUTE", 188 "MIN": "MINUTE", 189 "MINUTES": "MINUTE", 190 "MINS": "MINUTE", 191 "S": "SECOND", 192 "SEC": "SECOND", 193 "SECONDS": "SECOND", 194 "SECS": "SECOND", 195 "MS": "MILLISECOND", 196 "MSEC": "MILLISECOND", 197 "MILLISECONDS": "MILLISECOND", 198 "US": "MICROSECOND", 199 "USEC": "MICROSECOND", 200 "MICROSECONDS": "MICROSECOND", 201 "NS": "NANOSECOND", 202 "NSEC": "NANOSECOND", 203 "NANOSEC": "NANOSECOND", 204 "NSECOND": "NANOSECOND", 205 "NSECONDS": "NANOSECOND", 206 "NANOSECS": "NANOSECOND", 207 "EPOCH": "EPOCH_SECOND", 208 "EPOCH_SECONDS": "EPOCH_SECOND", 209 "EPOCH_MILLISECONDS": "EPOCH_MILLISECOND", 210 "EPOCH_MICROSECONDS": "EPOCH_MICROSECOND", 211 "EPOCH_NANOSECONDS": "EPOCH_NANOSECOND", 212 "TZH": "TIMEZONE_HOUR", 213 "TZM": "TIMEZONE_MINUTE", 214} 215 216 217@t.overload 218def _map_date_part(part: exp.Expression) -> exp.Var: 219 pass 220 221 222@t.overload 223def _map_date_part(part: t.Optional[exp.Expression]) -> t.Optional[exp.Expression]: 224 pass 225 226 227def _map_date_part(part): 228 mapped = DATE_PART_MAPPING.get(part.name.upper()) if part else None 229 return exp.var(mapped) if mapped else part 230 231 232def _date_trunc_to_time(args: t.List) -> exp.DateTrunc | exp.TimestampTrunc: 233 trunc = date_trunc_to_time(args) 234 trunc.set("unit", _map_date_part(trunc.args["unit"])) 235 return trunc 236 237 238def _build_timestamp_from_parts(args: t.List) -> exp.Func: 239 if len(args) == 2: 240 # Other dialects don't have the TIMESTAMP_FROM_PARTS(date, time) concept, 241 # so we parse this into Anonymous for now instead of introducing complexity 242 return exp.Anonymous(this="TIMESTAMP_FROM_PARTS", expressions=args) 243 244 return exp.TimestampFromParts.from_arg_list(args) 245 246 247def _unqualify_unpivot_columns(expression: exp.Expression) -> exp.Expression: 248 """ 249 Snowflake doesn't allow columns referenced in UNPIVOT to be qualified, 250 so we need to unqualify them. 251 252 Example: 253 >>> from sqlglot import parse_one 254 >>> expr = parse_one("SELECT * FROM m_sales UNPIVOT(sales FOR month IN (m_sales.jan, feb, mar, april))") 255 >>> print(_unqualify_unpivot_columns(expr).sql(dialect="snowflake")) 256 SELECT * FROM m_sales UNPIVOT(sales FOR month IN (jan, feb, mar, april)) 257 """ 258 if isinstance(expression, exp.Pivot) and expression.unpivot: 259 expression = transforms.unqualify_columns(expression) 260 261 return expression 262 263 264def _flatten_structured_types_unless_iceberg(expression: exp.Expression) -> exp.Expression: 265 assert isinstance(expression, exp.Create) 266 267 def _flatten_structured_type(expression: exp.DataType) -> exp.DataType: 268 if expression.this in exp.DataType.NESTED_TYPES: 269 expression.set("expressions", None) 270 return expression 271 272 props = expression.args.get("properties") 273 if isinstance(expression.this, exp.Schema) and not (props and props.find(exp.IcebergProperty)): 274 for schema_expression in expression.this.expressions: 275 if isinstance(schema_expression, exp.ColumnDef): 276 column_type = schema_expression.kind 277 if isinstance(column_type, exp.DataType): 278 column_type.transform(_flatten_structured_type, copy=False) 279 280 return expression 281 282 283class Snowflake(Dialect): 284 # https://docs.snowflake.com/en/sql-reference/identifiers-syntax 285 NORMALIZATION_STRATEGY = NormalizationStrategy.UPPERCASE 286 NULL_ORDERING = "nulls_are_large" 287 TIME_FORMAT = "'YYYY-MM-DD HH24:MI:SS'" 288 SUPPORTS_USER_DEFINED_TYPES = False 289 SUPPORTS_SEMI_ANTI_JOIN = False 290 PREFER_CTE_ALIAS_COLUMN = True 291 TABLESAMPLE_SIZE_IS_PERCENT = True 292 293 TIME_MAPPING = { 294 "YYYY": "%Y", 295 "yyyy": "%Y", 296 "YY": "%y", 297 "yy": "%y", 298 "MMMM": "%B", 299 "mmmm": "%B", 300 "MON": "%b", 301 "mon": "%b", 302 "MM": "%m", 303 "mm": "%m", 304 "DD": "%d", 305 "dd": "%-d", 306 "DY": "%a", 307 "dy": "%w", 308 "HH24": "%H", 309 "hh24": "%H", 310 "HH12": "%I", 311 "hh12": "%I", 312 "MI": "%M", 313 "mi": "%M", 314 "SS": "%S", 315 "ss": "%S", 316 "FF": "%f", 317 "ff": "%f", 318 "FF6": "%f", 319 "ff6": "%f", 320 } 321 322 def quote_identifier(self, expression: E, identify: bool = True) -> E: 323 # This disables quoting DUAL in SELECT ... FROM DUAL, because Snowflake treats an 324 # unquoted DUAL keyword in a special way and does not map it to a user-defined table 325 if ( 326 isinstance(expression, exp.Identifier) 327 and isinstance(expression.parent, exp.Table) 328 and expression.name.lower() == "dual" 329 ): 330 return expression # type: ignore 331 332 return super().quote_identifier(expression, identify=identify) 333 334 class Parser(parser.Parser): 335 IDENTIFY_PIVOT_STRINGS = True 336 337 ID_VAR_TOKENS = { 338 *parser.Parser.ID_VAR_TOKENS, 339 TokenType.MATCH_CONDITION, 340 } 341 342 TABLE_ALIAS_TOKENS = parser.Parser.TABLE_ALIAS_TOKENS | {TokenType.WINDOW} 343 TABLE_ALIAS_TOKENS.discard(TokenType.MATCH_CONDITION) 344 345 FUNCTIONS = { 346 **parser.Parser.FUNCTIONS, 347 "ARRAYAGG": exp.ArrayAgg.from_arg_list, 348 "ARRAY_CONSTRUCT": exp.Array.from_arg_list, 349 "ARRAY_CONTAINS": lambda args: exp.ArrayContains( 350 this=seq_get(args, 1), expression=seq_get(args, 0) 351 ), 352 "ARRAY_GENERATE_RANGE": lambda args: exp.GenerateSeries( 353 # ARRAY_GENERATE_RANGE has an exlusive end; we normalize it to be inclusive 354 start=seq_get(args, 0), 355 end=exp.Sub(this=seq_get(args, 1), expression=exp.Literal.number(1)), 356 step=seq_get(args, 2), 357 ), 358 "BITXOR": binary_from_function(exp.BitwiseXor), 359 "BIT_XOR": binary_from_function(exp.BitwiseXor), 360 "BOOLXOR": binary_from_function(exp.Xor), 361 "CONVERT_TIMEZONE": _build_convert_timezone, 362 "DATE": _build_datetime("DATE", exp.DataType.Type.DATE), 363 "DATE_TRUNC": _date_trunc_to_time, 364 "DATEADD": _build_date_time_add(exp.DateAdd), 365 "DATEDIFF": _build_datediff, 366 "DIV0": _build_if_from_div0, 367 "FLATTEN": exp.Explode.from_arg_list, 368 "GET_PATH": lambda args, dialect: exp.JSONExtract( 369 this=seq_get(args, 0), expression=dialect.to_json_path(seq_get(args, 1)) 370 ), 371 "IFF": exp.If.from_arg_list, 372 "LAST_DAY": lambda args: exp.LastDay( 373 this=seq_get(args, 0), unit=_map_date_part(seq_get(args, 1)) 374 ), 375 "LISTAGG": exp.GroupConcat.from_arg_list, 376 "MEDIAN": lambda args: exp.PercentileCont( 377 this=seq_get(args, 0), expression=exp.Literal.number(0.5) 378 ), 379 "NULLIFZERO": _build_if_from_nullifzero, 380 "OBJECT_CONSTRUCT": _build_object_construct, 381 "REGEXP_REPLACE": _build_regexp_replace, 382 "REGEXP_SUBSTR": exp.RegexpExtract.from_arg_list, 383 "RLIKE": exp.RegexpLike.from_arg_list, 384 "SQUARE": lambda args: exp.Pow(this=seq_get(args, 0), expression=exp.Literal.number(2)), 385 "TIMEADD": _build_date_time_add(exp.TimeAdd), 386 "TIMEDIFF": _build_datediff, 387 "TIMESTAMPADD": _build_date_time_add(exp.DateAdd), 388 "TIMESTAMPDIFF": _build_datediff, 389 "TIMESTAMPFROMPARTS": _build_timestamp_from_parts, 390 "TIMESTAMP_FROM_PARTS": _build_timestamp_from_parts, 391 "TRY_TO_DATE": _build_datetime("TRY_TO_DATE", exp.DataType.Type.DATE, safe=True), 392 "TO_DATE": _build_datetime("TO_DATE", exp.DataType.Type.DATE), 393 "TO_NUMBER": lambda args: exp.ToNumber( 394 this=seq_get(args, 0), 395 format=seq_get(args, 1), 396 precision=seq_get(args, 2), 397 scale=seq_get(args, 3), 398 ), 399 "TO_TIME": _build_datetime("TO_TIME", exp.DataType.Type.TIME), 400 "TO_TIMESTAMP": _build_datetime("TO_TIMESTAMP", exp.DataType.Type.TIMESTAMP), 401 "TO_TIMESTAMP_LTZ": _build_datetime("TO_TIMESTAMP_LTZ", exp.DataType.Type.TIMESTAMPLTZ), 402 "TO_TIMESTAMP_NTZ": _build_datetime("TO_TIMESTAMP_NTZ", exp.DataType.Type.TIMESTAMP), 403 "TO_TIMESTAMP_TZ": _build_datetime("TO_TIMESTAMP_TZ", exp.DataType.Type.TIMESTAMPTZ), 404 "TO_VARCHAR": exp.ToChar.from_arg_list, 405 "ZEROIFNULL": _build_if_from_zeroifnull, 406 } 407 408 FUNCTION_PARSERS = { 409 **parser.Parser.FUNCTION_PARSERS, 410 "DATE_PART": lambda self: self._parse_date_part(), 411 "OBJECT_CONSTRUCT_KEEP_NULL": lambda self: self._parse_json_object(), 412 } 413 FUNCTION_PARSERS.pop("TRIM") 414 415 TIMESTAMPS = parser.Parser.TIMESTAMPS - {TokenType.TIME} 416 417 RANGE_PARSERS = { 418 **parser.Parser.RANGE_PARSERS, 419 TokenType.LIKE_ANY: parser.binary_range_parser(exp.LikeAny), 420 TokenType.ILIKE_ANY: parser.binary_range_parser(exp.ILikeAny), 421 } 422 423 ALTER_PARSERS = { 424 **parser.Parser.ALTER_PARSERS, 425 "SET": lambda self: self._parse_set(tag=self._match_text_seq("TAG")), 426 "UNSET": lambda self: self.expression( 427 exp.Set, 428 tag=self._match_text_seq("TAG"), 429 expressions=self._parse_csv(self._parse_id_var), 430 unset=True, 431 ), 432 "SWAP": lambda self: self._parse_alter_table_swap(), 433 } 434 435 STATEMENT_PARSERS = { 436 **parser.Parser.STATEMENT_PARSERS, 437 TokenType.SHOW: lambda self: self._parse_show(), 438 } 439 440 PROPERTY_PARSERS = { 441 **parser.Parser.PROPERTY_PARSERS, 442 "LOCATION": lambda self: self._parse_location(), 443 } 444 445 SHOW_PARSERS = { 446 "SCHEMAS": _show_parser("SCHEMAS"), 447 "TERSE SCHEMAS": _show_parser("SCHEMAS"), 448 "OBJECTS": _show_parser("OBJECTS"), 449 "TERSE OBJECTS": _show_parser("OBJECTS"), 450 "TABLES": _show_parser("TABLES"), 451 "TERSE TABLES": _show_parser("TABLES"), 452 "VIEWS": _show_parser("VIEWS"), 453 "TERSE VIEWS": _show_parser("VIEWS"), 454 "PRIMARY KEYS": _show_parser("PRIMARY KEYS"), 455 "TERSE PRIMARY KEYS": _show_parser("PRIMARY KEYS"), 456 "IMPORTED KEYS": _show_parser("IMPORTED KEYS"), 457 "TERSE IMPORTED KEYS": _show_parser("IMPORTED KEYS"), 458 "UNIQUE KEYS": _show_parser("UNIQUE KEYS"), 459 "TERSE UNIQUE KEYS": _show_parser("UNIQUE KEYS"), 460 "SEQUENCES": _show_parser("SEQUENCES"), 461 "TERSE SEQUENCES": _show_parser("SEQUENCES"), 462 "COLUMNS": _show_parser("COLUMNS"), 463 "USERS": _show_parser("USERS"), 464 "TERSE USERS": _show_parser("USERS"), 465 } 466 467 STAGED_FILE_SINGLE_TOKENS = { 468 TokenType.DOT, 469 TokenType.MOD, 470 TokenType.SLASH, 471 } 472 473 FLATTEN_COLUMNS = ["SEQ", "KEY", "PATH", "INDEX", "VALUE", "THIS"] 474 475 SCHEMA_KINDS = {"OBJECTS", "TABLES", "VIEWS", "SEQUENCES", "UNIQUE KEYS", "IMPORTED KEYS"} 476 477 def _parse_column_ops(self, this: t.Optional[exp.Expression]) -> t.Optional[exp.Expression]: 478 this = super()._parse_column_ops(this) 479 480 casts = [] 481 json_path = [] 482 483 while self._match(TokenType.COLON): 484 path = super()._parse_column_ops(self._parse_field(any_token=True)) 485 486 # The cast :: operator has a lower precedence than the extraction operator :, so 487 # we rearrange the AST appropriately to avoid casting the 2nd argument of GET_PATH 488 while isinstance(path, exp.Cast): 489 casts.append(path.to) 490 path = path.this 491 492 if path: 493 json_path.append(path.sql(dialect="snowflake", copy=False)) 494 495 if json_path: 496 this = self.expression( 497 exp.JSONExtract, 498 this=this, 499 expression=self.dialect.to_json_path(exp.Literal.string(".".join(json_path))), 500 ) 501 502 while casts: 503 this = self.expression(exp.Cast, this=this, to=casts.pop()) 504 505 return this 506 507 # https://docs.snowflake.com/en/sql-reference/functions/date_part.html 508 # https://docs.snowflake.com/en/sql-reference/functions-date-time.html#label-supported-date-time-parts 509 def _parse_date_part(self: Snowflake.Parser) -> t.Optional[exp.Expression]: 510 this = self._parse_var() or self._parse_type() 511 512 if not this: 513 return None 514 515 self._match(TokenType.COMMA) 516 expression = self._parse_bitwise() 517 this = _map_date_part(this) 518 name = this.name.upper() 519 520 if name.startswith("EPOCH"): 521 if name == "EPOCH_MILLISECOND": 522 scale = 10**3 523 elif name == "EPOCH_MICROSECOND": 524 scale = 10**6 525 elif name == "EPOCH_NANOSECOND": 526 scale = 10**9 527 else: 528 scale = None 529 530 ts = self.expression(exp.Cast, this=expression, to=exp.DataType.build("TIMESTAMP")) 531 to_unix: exp.Expression = self.expression(exp.TimeToUnix, this=ts) 532 533 if scale: 534 to_unix = exp.Mul(this=to_unix, expression=exp.Literal.number(scale)) 535 536 return to_unix 537 538 return self.expression(exp.Extract, this=this, expression=expression) 539 540 def _parse_bracket_key_value(self, is_map: bool = False) -> t.Optional[exp.Expression]: 541 if is_map: 542 # Keys are strings in Snowflake's objects, see also: 543 # - https://docs.snowflake.com/en/sql-reference/data-types-semistructured 544 # - https://docs.snowflake.com/en/sql-reference/functions/object_construct 545 return self._parse_slice(self._parse_string()) 546 547 return self._parse_slice(self._parse_alias(self._parse_conjunction(), explicit=True)) 548 549 def _parse_lateral(self) -> t.Optional[exp.Lateral]: 550 lateral = super()._parse_lateral() 551 if not lateral: 552 return lateral 553 554 if isinstance(lateral.this, exp.Explode): 555 table_alias = lateral.args.get("alias") 556 columns = [exp.to_identifier(col) for col in self.FLATTEN_COLUMNS] 557 if table_alias and not table_alias.args.get("columns"): 558 table_alias.set("columns", columns) 559 elif not table_alias: 560 exp.alias_(lateral, "_flattened", table=columns, copy=False) 561 562 return lateral 563 564 def _parse_at_before(self, table: exp.Table) -> exp.Table: 565 # https://docs.snowflake.com/en/sql-reference/constructs/at-before 566 index = self._index 567 if self._match_texts(("AT", "BEFORE")): 568 this = self._prev.text.upper() 569 kind = ( 570 self._match(TokenType.L_PAREN) 571 and self._match_texts(self.HISTORICAL_DATA_KIND) 572 and self._prev.text.upper() 573 ) 574 expression = self._match(TokenType.FARROW) and self._parse_bitwise() 575 576 if expression: 577 self._match_r_paren() 578 when = self.expression( 579 exp.HistoricalData, this=this, kind=kind, expression=expression 580 ) 581 table.set("when", when) 582 else: 583 self._retreat(index) 584 585 return table 586 587 def _parse_table_parts( 588 self, schema: bool = False, is_db_reference: bool = False, wildcard: bool = False 589 ) -> exp.Table: 590 # https://docs.snowflake.com/en/user-guide/querying-stage 591 if self._match(TokenType.STRING, advance=False): 592 table = self._parse_string() 593 elif self._match_text_seq("@", advance=False): 594 table = self._parse_location_path() 595 else: 596 table = None 597 598 if table: 599 file_format = None 600 pattern = None 601 602 self._match(TokenType.L_PAREN) 603 while self._curr and not self._match(TokenType.R_PAREN): 604 if self._match_text_seq("FILE_FORMAT", "=>"): 605 file_format = self._parse_string() or super()._parse_table_parts( 606 is_db_reference=is_db_reference 607 ) 608 elif self._match_text_seq("PATTERN", "=>"): 609 pattern = self._parse_string() 610 else: 611 break 612 613 self._match(TokenType.COMMA) 614 615 table = self.expression(exp.Table, this=table, format=file_format, pattern=pattern) 616 else: 617 table = super()._parse_table_parts(schema=schema, is_db_reference=is_db_reference) 618 619 return self._parse_at_before(table) 620 621 def _parse_id_var( 622 self, 623 any_token: bool = True, 624 tokens: t.Optional[t.Collection[TokenType]] = None, 625 ) -> t.Optional[exp.Expression]: 626 if self._match_text_seq("IDENTIFIER", "("): 627 identifier = ( 628 super()._parse_id_var(any_token=any_token, tokens=tokens) 629 or self._parse_string() 630 ) 631 self._match_r_paren() 632 return self.expression(exp.Anonymous, this="IDENTIFIER", expressions=[identifier]) 633 634 return super()._parse_id_var(any_token=any_token, tokens=tokens) 635 636 def _parse_show_snowflake(self, this: str) -> exp.Show: 637 scope = None 638 scope_kind = None 639 640 # will identity SHOW TERSE SCHEMAS but not SHOW TERSE PRIMARY KEYS 641 # which is syntactically valid but has no effect on the output 642 terse = self._tokens[self._index - 2].text.upper() == "TERSE" 643 644 history = self._match_text_seq("HISTORY") 645 646 like = self._parse_string() if self._match(TokenType.LIKE) else None 647 648 if self._match(TokenType.IN): 649 if self._match_text_seq("ACCOUNT"): 650 scope_kind = "ACCOUNT" 651 elif self._match_set(self.DB_CREATABLES): 652 scope_kind = self._prev.text.upper() 653 if self._curr: 654 scope = self._parse_table_parts() 655 elif self._curr: 656 scope_kind = "SCHEMA" if this in self.SCHEMA_KINDS else "TABLE" 657 scope = self._parse_table_parts() 658 659 return self.expression( 660 exp.Show, 661 **{ 662 "terse": terse, 663 "this": this, 664 "history": history, 665 "like": like, 666 "scope": scope, 667 "scope_kind": scope_kind, 668 "starts_with": self._match_text_seq("STARTS", "WITH") and self._parse_string(), 669 "limit": self._parse_limit(), 670 "from": self._parse_string() if self._match(TokenType.FROM) else None, 671 }, 672 ) 673 674 def _parse_alter_table_swap(self) -> exp.SwapTable: 675 self._match_text_seq("WITH") 676 return self.expression(exp.SwapTable, this=self._parse_table(schema=True)) 677 678 def _parse_location(self) -> exp.LocationProperty: 679 self._match(TokenType.EQ) 680 return self.expression(exp.LocationProperty, this=self._parse_location_path()) 681 682 def _parse_location_path(self) -> exp.Var: 683 parts = [self._advance_any(ignore_reserved=True)] 684 685 # We avoid consuming a comma token because external tables like @foo and @bar 686 # can be joined in a query with a comma separator. 687 while self._is_connected() and not self._match(TokenType.COMMA, advance=False): 688 parts.append(self._advance_any(ignore_reserved=True)) 689 690 return exp.var("".join(part.text for part in parts if part)) 691 692 class Tokenizer(tokens.Tokenizer): 693 STRING_ESCAPES = ["\\", "'"] 694 HEX_STRINGS = [("x'", "'"), ("X'", "'")] 695 RAW_STRINGS = ["$$"] 696 COMMENTS = ["--", "//", ("/*", "*/")] 697 698 KEYWORDS = { 699 **tokens.Tokenizer.KEYWORDS, 700 "BYTEINT": TokenType.INT, 701 "CHAR VARYING": TokenType.VARCHAR, 702 "CHARACTER VARYING": TokenType.VARCHAR, 703 "EXCLUDE": TokenType.EXCEPT, 704 "ILIKE ANY": TokenType.ILIKE_ANY, 705 "LIKE ANY": TokenType.LIKE_ANY, 706 "MATCH_CONDITION": TokenType.MATCH_CONDITION, 707 "MATCH_RECOGNIZE": TokenType.MATCH_RECOGNIZE, 708 "MINUS": TokenType.EXCEPT, 709 "NCHAR VARYING": TokenType.VARCHAR, 710 "PUT": TokenType.COMMAND, 711 "REMOVE": TokenType.COMMAND, 712 "RENAME": TokenType.REPLACE, 713 "RM": TokenType.COMMAND, 714 "SAMPLE": TokenType.TABLE_SAMPLE, 715 "SQL_DOUBLE": TokenType.DOUBLE, 716 "SQL_VARCHAR": TokenType.VARCHAR, 717 "STORAGE INTEGRATION": TokenType.STORAGE_INTEGRATION, 718 "TIMESTAMP_LTZ": TokenType.TIMESTAMPLTZ, 719 "TIMESTAMP_NTZ": TokenType.TIMESTAMP, 720 "TIMESTAMP_TZ": TokenType.TIMESTAMPTZ, 721 "TIMESTAMPNTZ": TokenType.TIMESTAMP, 722 "TOP": TokenType.TOP, 723 } 724 725 SINGLE_TOKENS = { 726 **tokens.Tokenizer.SINGLE_TOKENS, 727 "$": TokenType.PARAMETER, 728 } 729 730 VAR_SINGLE_TOKENS = {"$"} 731 732 COMMANDS = tokens.Tokenizer.COMMANDS - {TokenType.SHOW} 733 734 class Generator(generator.Generator): 735 PARAMETER_TOKEN = "$" 736 MATCHED_BY_SOURCE = False 737 SINGLE_STRING_INTERVAL = True 738 JOIN_HINTS = False 739 TABLE_HINTS = False 740 QUERY_HINTS = False 741 AGGREGATE_FILTER_SUPPORTED = False 742 SUPPORTS_TABLE_COPY = False 743 COLLATE_IS_FUNC = True 744 LIMIT_ONLY_LITERALS = True 745 JSON_KEY_VALUE_PAIR_SEP = "," 746 INSERT_OVERWRITE = " OVERWRITE INTO" 747 STRUCT_DELIMITER = ("(", ")") 748 749 TRANSFORMS = { 750 **generator.Generator.TRANSFORMS, 751 exp.ApproxDistinct: rename_func("APPROX_COUNT_DISTINCT"), 752 exp.ArgMax: rename_func("MAX_BY"), 753 exp.ArgMin: rename_func("MIN_BY"), 754 exp.Array: inline_array_sql, 755 exp.ArrayConcat: rename_func("ARRAY_CAT"), 756 exp.ArrayContains: lambda self, e: self.func("ARRAY_CONTAINS", e.expression, e.this), 757 exp.AtTimeZone: lambda self, e: self.func( 758 "CONVERT_TIMEZONE", e.args.get("zone"), e.this 759 ), 760 exp.BitwiseXor: rename_func("BITXOR"), 761 exp.Create: transforms.preprocess([_flatten_structured_types_unless_iceberg]), 762 exp.DateAdd: date_delta_sql("DATEADD"), 763 exp.DateDiff: date_delta_sql("DATEDIFF"), 764 exp.DateStrToDate: datestrtodate_sql, 765 exp.DayOfMonth: rename_func("DAYOFMONTH"), 766 exp.DayOfWeek: rename_func("DAYOFWEEK"), 767 exp.DayOfYear: rename_func("DAYOFYEAR"), 768 exp.Explode: rename_func("FLATTEN"), 769 exp.Extract: rename_func("DATE_PART"), 770 exp.FromTimeZone: lambda self, e: self.func( 771 "CONVERT_TIMEZONE", e.args.get("zone"), "'UTC'", e.this 772 ), 773 exp.GenerateSeries: lambda self, e: self.func( 774 "ARRAY_GENERATE_RANGE", e.args["start"], e.args["end"] + 1, e.args.get("step") 775 ), 776 exp.GroupConcat: rename_func("LISTAGG"), 777 exp.If: if_sql(name="IFF", false_value="NULL"), 778 exp.JSONExtract: lambda self, e: self.func("GET_PATH", e.this, e.expression), 779 exp.JSONExtractScalar: lambda self, e: self.func( 780 "JSON_EXTRACT_PATH_TEXT", e.this, e.expression 781 ), 782 exp.JSONObject: lambda self, e: self.func("OBJECT_CONSTRUCT_KEEP_NULL", *e.expressions), 783 exp.JSONPathRoot: lambda *_: "", 784 exp.LogicalAnd: rename_func("BOOLAND_AGG"), 785 exp.LogicalOr: rename_func("BOOLOR_AGG"), 786 exp.Map: lambda self, e: var_map_sql(self, e, "OBJECT_CONSTRUCT"), 787 exp.Max: max_or_greatest, 788 exp.Min: min_or_least, 789 exp.PartitionedByProperty: lambda self, e: f"PARTITION BY {self.sql(e, 'this')}", 790 exp.PercentileCont: transforms.preprocess( 791 [transforms.add_within_group_for_percentiles] 792 ), 793 exp.PercentileDisc: transforms.preprocess( 794 [transforms.add_within_group_for_percentiles] 795 ), 796 exp.Pivot: transforms.preprocess([_unqualify_unpivot_columns]), 797 exp.RegexpILike: _regexpilike_sql, 798 exp.Rand: rename_func("RANDOM"), 799 exp.Select: transforms.preprocess( 800 [ 801 transforms.eliminate_distinct_on, 802 transforms.explode_to_unnest(), 803 transforms.eliminate_semi_and_anti_joins, 804 ] 805 ), 806 exp.SHA: rename_func("SHA1"), 807 exp.StarMap: rename_func("OBJECT_CONSTRUCT"), 808 exp.StartsWith: rename_func("STARTSWITH"), 809 exp.StrPosition: lambda self, e: self.func( 810 "POSITION", e.args.get("substr"), e.this, e.args.get("position") 811 ), 812 exp.StrToTime: lambda self, e: self.func("TO_TIMESTAMP", e.this, self.format_time(e)), 813 exp.Stuff: rename_func("INSERT"), 814 exp.TimeAdd: date_delta_sql("TIMEADD"), 815 exp.TimestampDiff: lambda self, e: self.func( 816 "TIMESTAMPDIFF", e.unit, e.expression, e.this 817 ), 818 exp.TimestampTrunc: timestamptrunc_sql, 819 exp.TimeStrToTime: timestrtotime_sql, 820 exp.TimeToStr: lambda self, e: self.func( 821 "TO_CHAR", exp.cast(e.this, exp.DataType.Type.TIMESTAMP), self.format_time(e) 822 ), 823 exp.TimeToUnix: lambda self, e: f"EXTRACT(epoch_second FROM {self.sql(e, 'this')})", 824 exp.ToArray: rename_func("TO_ARRAY"), 825 exp.ToChar: lambda self, e: self.function_fallback_sql(e), 826 exp.Trim: lambda self, e: self.func("TRIM", e.this, e.expression), 827 exp.TsOrDsAdd: date_delta_sql("DATEADD", cast=True), 828 exp.TsOrDsDiff: date_delta_sql("DATEDIFF"), 829 exp.TsOrDsToDate: lambda self, e: self.func( 830 "TRY_TO_DATE" if e.args.get("safe") else "TO_DATE", e.this, self.format_time(e) 831 ), 832 exp.UnixToTime: rename_func("TO_TIMESTAMP"), 833 exp.VarMap: lambda self, e: var_map_sql(self, e, "OBJECT_CONSTRUCT"), 834 exp.WeekOfYear: rename_func("WEEKOFYEAR"), 835 exp.Xor: rename_func("BOOLXOR"), 836 } 837 838 SUPPORTED_JSON_PATH_PARTS = { 839 exp.JSONPathKey, 840 exp.JSONPathRoot, 841 exp.JSONPathSubscript, 842 } 843 844 TYPE_MAPPING = { 845 **generator.Generator.TYPE_MAPPING, 846 exp.DataType.Type.NESTED: "OBJECT", 847 exp.DataType.Type.STRUCT: "OBJECT", 848 exp.DataType.Type.TIMESTAMP: "TIMESTAMPNTZ", 849 } 850 851 STAR_MAPPING = { 852 "except": "EXCLUDE", 853 "replace": "RENAME", 854 } 855 856 PROPERTIES_LOCATION = { 857 **generator.Generator.PROPERTIES_LOCATION, 858 exp.SetProperty: exp.Properties.Location.UNSUPPORTED, 859 exp.VolatileProperty: exp.Properties.Location.UNSUPPORTED, 860 } 861 862 UNSUPPORTED_VALUES_EXPRESSIONS = { 863 exp.Struct, 864 } 865 866 def values_sql(self, expression: exp.Values, values_as_table: bool = True) -> str: 867 if expression.find(*self.UNSUPPORTED_VALUES_EXPRESSIONS): 868 values_as_table = False 869 870 return super().values_sql(expression, values_as_table=values_as_table) 871 872 def datatype_sql(self, expression: exp.DataType) -> str: 873 expressions = expression.expressions 874 if ( 875 expressions 876 and expression.is_type(*exp.DataType.STRUCT_TYPES) 877 and any(isinstance(field_type, exp.DataType) for field_type in expressions) 878 ): 879 # The correct syntax is OBJECT [ (<key> <value_type [NOT NULL] [, ...]) ] 880 return "OBJECT" 881 882 return super().datatype_sql(expression) 883 884 def tonumber_sql(self, expression: exp.ToNumber) -> str: 885 return self.func( 886 "TO_NUMBER", 887 expression.this, 888 expression.args.get("format"), 889 expression.args.get("precision"), 890 expression.args.get("scale"), 891 ) 892 893 def timestampfromparts_sql(self, expression: exp.TimestampFromParts) -> str: 894 milli = expression.args.get("milli") 895 if milli is not None: 896 milli_to_nano = milli.pop() * exp.Literal.number(1000000) 897 expression.set("nano", milli_to_nano) 898 899 return rename_func("TIMESTAMP_FROM_PARTS")(self, expression) 900 901 def trycast_sql(self, expression: exp.TryCast) -> str: 902 value = expression.this 903 904 if value.type is None: 905 from sqlglot.optimizer.annotate_types import annotate_types 906 907 value = annotate_types(value) 908 909 if value.is_type(*exp.DataType.TEXT_TYPES, exp.DataType.Type.UNKNOWN): 910 return super().trycast_sql(expression) 911 912 # TRY_CAST only works for string values in Snowflake 913 return self.cast_sql(expression) 914 915 def log_sql(self, expression: exp.Log) -> str: 916 if not expression.expression: 917 return self.func("LN", expression.this) 918 919 return super().log_sql(expression) 920 921 def unnest_sql(self, expression: exp.Unnest) -> str: 922 unnest_alias = expression.args.get("alias") 923 offset = expression.args.get("offset") 924 925 columns = [ 926 exp.to_identifier("seq"), 927 exp.to_identifier("key"), 928 exp.to_identifier("path"), 929 offset.pop() if isinstance(offset, exp.Expression) else exp.to_identifier("index"), 930 seq_get(unnest_alias.columns if unnest_alias else [], 0) 931 or exp.to_identifier("value"), 932 exp.to_identifier("this"), 933 ] 934 935 if unnest_alias: 936 unnest_alias.set("columns", columns) 937 else: 938 unnest_alias = exp.TableAlias(this="_u", columns=columns) 939 940 explode = f"TABLE(FLATTEN(INPUT => {self.sql(expression.expressions[0])}))" 941 alias = self.sql(unnest_alias) 942 alias = f" AS {alias}" if alias else "" 943 return f"{explode}{alias}" 944 945 def show_sql(self, expression: exp.Show) -> str: 946 terse = "TERSE " if expression.args.get("terse") else "" 947 history = " HISTORY" if expression.args.get("history") else "" 948 like = self.sql(expression, "like") 949 like = f" LIKE {like}" if like else "" 950 951 scope = self.sql(expression, "scope") 952 scope = f" {scope}" if scope else "" 953 954 scope_kind = self.sql(expression, "scope_kind") 955 if scope_kind: 956 scope_kind = f" IN {scope_kind}" 957 958 starts_with = self.sql(expression, "starts_with") 959 if starts_with: 960 starts_with = f" STARTS WITH {starts_with}" 961 962 limit = self.sql(expression, "limit") 963 964 from_ = self.sql(expression, "from") 965 if from_: 966 from_ = f" FROM {from_}" 967 968 return f"SHOW {terse}{expression.name}{history}{like}{scope_kind}{scope}{starts_with}{limit}{from_}" 969 970 def regexpextract_sql(self, expression: exp.RegexpExtract) -> str: 971 # Other dialects don't support all of the following parameters, so we need to 972 # generate default values as necessary to ensure the transpilation is correct 973 group = expression.args.get("group") 974 parameters = expression.args.get("parameters") or (group and exp.Literal.string("c")) 975 occurrence = expression.args.get("occurrence") or (parameters and exp.Literal.number(1)) 976 position = expression.args.get("position") or (occurrence and exp.Literal.number(1)) 977 978 return self.func( 979 "REGEXP_SUBSTR", 980 expression.this, 981 expression.expression, 982 position, 983 occurrence, 984 parameters, 985 group, 986 ) 987 988 def except_op(self, expression: exp.Except) -> str: 989 if not expression.args.get("distinct"): 990 self.unsupported("EXCEPT with All is not supported in Snowflake") 991 return super().except_op(expression) 992 993 def intersect_op(self, expression: exp.Intersect) -> str: 994 if not expression.args.get("distinct"): 995 self.unsupported("INTERSECT with All is not supported in Snowflake") 996 return super().intersect_op(expression) 997 998 def describe_sql(self, expression: exp.Describe) -> str: 999 # Default to table if kind is unknown 1000 kind_value = expression.args.get("kind") or "TABLE" 1001 kind = f" {kind_value}" if kind_value else "" 1002 this = f" {self.sql(expression, 'this')}" 1003 expressions = self.expressions(expression, flat=True) 1004 expressions = f" {expressions}" if expressions else "" 1005 return f"DESCRIBE{kind}{this}{expressions}" 1006 1007 def generatedasidentitycolumnconstraint_sql( 1008 self, expression: exp.GeneratedAsIdentityColumnConstraint 1009 ) -> str: 1010 start = expression.args.get("start") 1011 start = f" START {start}" if start else "" 1012 increment = expression.args.get("increment") 1013 increment = f" INCREMENT {increment}" if increment else "" 1014 return f"AUTOINCREMENT{start}{increment}" 1015 1016 def swaptable_sql(self, expression: exp.SwapTable) -> str: 1017 this = self.sql(expression, "this") 1018 return f"SWAP WITH {this}" 1019 1020 def with_properties(self, properties: exp.Properties) -> str: 1021 return self.properties(properties, wrapped=False, prefix=self.seg(""), sep=" ") 1022 1023 def cluster_sql(self, expression: exp.Cluster) -> str: 1024 return f"CLUSTER BY ({self.expressions(expression, flat=True)})" 1025 1026 def struct_sql(self, expression: exp.Struct) -> str: 1027 keys = [] 1028 values = [] 1029 1030 for i, e in enumerate(expression.expressions): 1031 if isinstance(e, exp.PropertyEQ): 1032 keys.append( 1033 exp.Literal.string(e.name) if isinstance(e.this, exp.Identifier) else e.this 1034 ) 1035 values.append(e.expression) 1036 else: 1037 keys.append(exp.Literal.string(f"_{i}")) 1038 values.append(e) 1039 1040 return self.func("OBJECT_CONSTRUCT", *flatten(zip(keys, values)))
284class Snowflake(Dialect): 285 # https://docs.snowflake.com/en/sql-reference/identifiers-syntax 286 NORMALIZATION_STRATEGY = NormalizationStrategy.UPPERCASE 287 NULL_ORDERING = "nulls_are_large" 288 TIME_FORMAT = "'YYYY-MM-DD HH24:MI:SS'" 289 SUPPORTS_USER_DEFINED_TYPES = False 290 SUPPORTS_SEMI_ANTI_JOIN = False 291 PREFER_CTE_ALIAS_COLUMN = True 292 TABLESAMPLE_SIZE_IS_PERCENT = True 293 294 TIME_MAPPING = { 295 "YYYY": "%Y", 296 "yyyy": "%Y", 297 "YY": "%y", 298 "yy": "%y", 299 "MMMM": "%B", 300 "mmmm": "%B", 301 "MON": "%b", 302 "mon": "%b", 303 "MM": "%m", 304 "mm": "%m", 305 "DD": "%d", 306 "dd": "%-d", 307 "DY": "%a", 308 "dy": "%w", 309 "HH24": "%H", 310 "hh24": "%H", 311 "HH12": "%I", 312 "hh12": "%I", 313 "MI": "%M", 314 "mi": "%M", 315 "SS": "%S", 316 "ss": "%S", 317 "FF": "%f", 318 "ff": "%f", 319 "FF6": "%f", 320 "ff6": "%f", 321 } 322 323 def quote_identifier(self, expression: E, identify: bool = True) -> E: 324 # This disables quoting DUAL in SELECT ... FROM DUAL, because Snowflake treats an 325 # unquoted DUAL keyword in a special way and does not map it to a user-defined table 326 if ( 327 isinstance(expression, exp.Identifier) 328 and isinstance(expression.parent, exp.Table) 329 and expression.name.lower() == "dual" 330 ): 331 return expression # type: ignore 332 333 return super().quote_identifier(expression, identify=identify) 334 335 class Parser(parser.Parser): 336 IDENTIFY_PIVOT_STRINGS = True 337 338 ID_VAR_TOKENS = { 339 *parser.Parser.ID_VAR_TOKENS, 340 TokenType.MATCH_CONDITION, 341 } 342 343 TABLE_ALIAS_TOKENS = parser.Parser.TABLE_ALIAS_TOKENS | {TokenType.WINDOW} 344 TABLE_ALIAS_TOKENS.discard(TokenType.MATCH_CONDITION) 345 346 FUNCTIONS = { 347 **parser.Parser.FUNCTIONS, 348 "ARRAYAGG": exp.ArrayAgg.from_arg_list, 349 "ARRAY_CONSTRUCT": exp.Array.from_arg_list, 350 "ARRAY_CONTAINS": lambda args: exp.ArrayContains( 351 this=seq_get(args, 1), expression=seq_get(args, 0) 352 ), 353 "ARRAY_GENERATE_RANGE": lambda args: exp.GenerateSeries( 354 # ARRAY_GENERATE_RANGE has an exlusive end; we normalize it to be inclusive 355 start=seq_get(args, 0), 356 end=exp.Sub(this=seq_get(args, 1), expression=exp.Literal.number(1)), 357 step=seq_get(args, 2), 358 ), 359 "BITXOR": binary_from_function(exp.BitwiseXor), 360 "BIT_XOR": binary_from_function(exp.BitwiseXor), 361 "BOOLXOR": binary_from_function(exp.Xor), 362 "CONVERT_TIMEZONE": _build_convert_timezone, 363 "DATE": _build_datetime("DATE", exp.DataType.Type.DATE), 364 "DATE_TRUNC": _date_trunc_to_time, 365 "DATEADD": _build_date_time_add(exp.DateAdd), 366 "DATEDIFF": _build_datediff, 367 "DIV0": _build_if_from_div0, 368 "FLATTEN": exp.Explode.from_arg_list, 369 "GET_PATH": lambda args, dialect: exp.JSONExtract( 370 this=seq_get(args, 0), expression=dialect.to_json_path(seq_get(args, 1)) 371 ), 372 "IFF": exp.If.from_arg_list, 373 "LAST_DAY": lambda args: exp.LastDay( 374 this=seq_get(args, 0), unit=_map_date_part(seq_get(args, 1)) 375 ), 376 "LISTAGG": exp.GroupConcat.from_arg_list, 377 "MEDIAN": lambda args: exp.PercentileCont( 378 this=seq_get(args, 0), expression=exp.Literal.number(0.5) 379 ), 380 "NULLIFZERO": _build_if_from_nullifzero, 381 "OBJECT_CONSTRUCT": _build_object_construct, 382 "REGEXP_REPLACE": _build_regexp_replace, 383 "REGEXP_SUBSTR": exp.RegexpExtract.from_arg_list, 384 "RLIKE": exp.RegexpLike.from_arg_list, 385 "SQUARE": lambda args: exp.Pow(this=seq_get(args, 0), expression=exp.Literal.number(2)), 386 "TIMEADD": _build_date_time_add(exp.TimeAdd), 387 "TIMEDIFF": _build_datediff, 388 "TIMESTAMPADD": _build_date_time_add(exp.DateAdd), 389 "TIMESTAMPDIFF": _build_datediff, 390 "TIMESTAMPFROMPARTS": _build_timestamp_from_parts, 391 "TIMESTAMP_FROM_PARTS": _build_timestamp_from_parts, 392 "TRY_TO_DATE": _build_datetime("TRY_TO_DATE", exp.DataType.Type.DATE, safe=True), 393 "TO_DATE": _build_datetime("TO_DATE", exp.DataType.Type.DATE), 394 "TO_NUMBER": lambda args: exp.ToNumber( 395 this=seq_get(args, 0), 396 format=seq_get(args, 1), 397 precision=seq_get(args, 2), 398 scale=seq_get(args, 3), 399 ), 400 "TO_TIME": _build_datetime("TO_TIME", exp.DataType.Type.TIME), 401 "TO_TIMESTAMP": _build_datetime("TO_TIMESTAMP", exp.DataType.Type.TIMESTAMP), 402 "TO_TIMESTAMP_LTZ": _build_datetime("TO_TIMESTAMP_LTZ", exp.DataType.Type.TIMESTAMPLTZ), 403 "TO_TIMESTAMP_NTZ": _build_datetime("TO_TIMESTAMP_NTZ", exp.DataType.Type.TIMESTAMP), 404 "TO_TIMESTAMP_TZ": _build_datetime("TO_TIMESTAMP_TZ", exp.DataType.Type.TIMESTAMPTZ), 405 "TO_VARCHAR": exp.ToChar.from_arg_list, 406 "ZEROIFNULL": _build_if_from_zeroifnull, 407 } 408 409 FUNCTION_PARSERS = { 410 **parser.Parser.FUNCTION_PARSERS, 411 "DATE_PART": lambda self: self._parse_date_part(), 412 "OBJECT_CONSTRUCT_KEEP_NULL": lambda self: self._parse_json_object(), 413 } 414 FUNCTION_PARSERS.pop("TRIM") 415 416 TIMESTAMPS = parser.Parser.TIMESTAMPS - {TokenType.TIME} 417 418 RANGE_PARSERS = { 419 **parser.Parser.RANGE_PARSERS, 420 TokenType.LIKE_ANY: parser.binary_range_parser(exp.LikeAny), 421 TokenType.ILIKE_ANY: parser.binary_range_parser(exp.ILikeAny), 422 } 423 424 ALTER_PARSERS = { 425 **parser.Parser.ALTER_PARSERS, 426 "SET": lambda self: self._parse_set(tag=self._match_text_seq("TAG")), 427 "UNSET": lambda self: self.expression( 428 exp.Set, 429 tag=self._match_text_seq("TAG"), 430 expressions=self._parse_csv(self._parse_id_var), 431 unset=True, 432 ), 433 "SWAP": lambda self: self._parse_alter_table_swap(), 434 } 435 436 STATEMENT_PARSERS = { 437 **parser.Parser.STATEMENT_PARSERS, 438 TokenType.SHOW: lambda self: self._parse_show(), 439 } 440 441 PROPERTY_PARSERS = { 442 **parser.Parser.PROPERTY_PARSERS, 443 "LOCATION": lambda self: self._parse_location(), 444 } 445 446 SHOW_PARSERS = { 447 "SCHEMAS": _show_parser("SCHEMAS"), 448 "TERSE SCHEMAS": _show_parser("SCHEMAS"), 449 "OBJECTS": _show_parser("OBJECTS"), 450 "TERSE OBJECTS": _show_parser("OBJECTS"), 451 "TABLES": _show_parser("TABLES"), 452 "TERSE TABLES": _show_parser("TABLES"), 453 "VIEWS": _show_parser("VIEWS"), 454 "TERSE VIEWS": _show_parser("VIEWS"), 455 "PRIMARY KEYS": _show_parser("PRIMARY KEYS"), 456 "TERSE PRIMARY KEYS": _show_parser("PRIMARY KEYS"), 457 "IMPORTED KEYS": _show_parser("IMPORTED KEYS"), 458 "TERSE IMPORTED KEYS": _show_parser("IMPORTED KEYS"), 459 "UNIQUE KEYS": _show_parser("UNIQUE KEYS"), 460 "TERSE UNIQUE KEYS": _show_parser("UNIQUE KEYS"), 461 "SEQUENCES": _show_parser("SEQUENCES"), 462 "TERSE SEQUENCES": _show_parser("SEQUENCES"), 463 "COLUMNS": _show_parser("COLUMNS"), 464 "USERS": _show_parser("USERS"), 465 "TERSE USERS": _show_parser("USERS"), 466 } 467 468 STAGED_FILE_SINGLE_TOKENS = { 469 TokenType.DOT, 470 TokenType.MOD, 471 TokenType.SLASH, 472 } 473 474 FLATTEN_COLUMNS = ["SEQ", "KEY", "PATH", "INDEX", "VALUE", "THIS"] 475 476 SCHEMA_KINDS = {"OBJECTS", "TABLES", "VIEWS", "SEQUENCES", "UNIQUE KEYS", "IMPORTED KEYS"} 477 478 def _parse_column_ops(self, this: t.Optional[exp.Expression]) -> t.Optional[exp.Expression]: 479 this = super()._parse_column_ops(this) 480 481 casts = [] 482 json_path = [] 483 484 while self._match(TokenType.COLON): 485 path = super()._parse_column_ops(self._parse_field(any_token=True)) 486 487 # The cast :: operator has a lower precedence than the extraction operator :, so 488 # we rearrange the AST appropriately to avoid casting the 2nd argument of GET_PATH 489 while isinstance(path, exp.Cast): 490 casts.append(path.to) 491 path = path.this 492 493 if path: 494 json_path.append(path.sql(dialect="snowflake", copy=False)) 495 496 if json_path: 497 this = self.expression( 498 exp.JSONExtract, 499 this=this, 500 expression=self.dialect.to_json_path(exp.Literal.string(".".join(json_path))), 501 ) 502 503 while casts: 504 this = self.expression(exp.Cast, this=this, to=casts.pop()) 505 506 return this 507 508 # https://docs.snowflake.com/en/sql-reference/functions/date_part.html 509 # https://docs.snowflake.com/en/sql-reference/functions-date-time.html#label-supported-date-time-parts 510 def _parse_date_part(self: Snowflake.Parser) -> t.Optional[exp.Expression]: 511 this = self._parse_var() or self._parse_type() 512 513 if not this: 514 return None 515 516 self._match(TokenType.COMMA) 517 expression = self._parse_bitwise() 518 this = _map_date_part(this) 519 name = this.name.upper() 520 521 if name.startswith("EPOCH"): 522 if name == "EPOCH_MILLISECOND": 523 scale = 10**3 524 elif name == "EPOCH_MICROSECOND": 525 scale = 10**6 526 elif name == "EPOCH_NANOSECOND": 527 scale = 10**9 528 else: 529 scale = None 530 531 ts = self.expression(exp.Cast, this=expression, to=exp.DataType.build("TIMESTAMP")) 532 to_unix: exp.Expression = self.expression(exp.TimeToUnix, this=ts) 533 534 if scale: 535 to_unix = exp.Mul(this=to_unix, expression=exp.Literal.number(scale)) 536 537 return to_unix 538 539 return self.expression(exp.Extract, this=this, expression=expression) 540 541 def _parse_bracket_key_value(self, is_map: bool = False) -> t.Optional[exp.Expression]: 542 if is_map: 543 # Keys are strings in Snowflake's objects, see also: 544 # - https://docs.snowflake.com/en/sql-reference/data-types-semistructured 545 # - https://docs.snowflake.com/en/sql-reference/functions/object_construct 546 return self._parse_slice(self._parse_string()) 547 548 return self._parse_slice(self._parse_alias(self._parse_conjunction(), explicit=True)) 549 550 def _parse_lateral(self) -> t.Optional[exp.Lateral]: 551 lateral = super()._parse_lateral() 552 if not lateral: 553 return lateral 554 555 if isinstance(lateral.this, exp.Explode): 556 table_alias = lateral.args.get("alias") 557 columns = [exp.to_identifier(col) for col in self.FLATTEN_COLUMNS] 558 if table_alias and not table_alias.args.get("columns"): 559 table_alias.set("columns", columns) 560 elif not table_alias: 561 exp.alias_(lateral, "_flattened", table=columns, copy=False) 562 563 return lateral 564 565 def _parse_at_before(self, table: exp.Table) -> exp.Table: 566 # https://docs.snowflake.com/en/sql-reference/constructs/at-before 567 index = self._index 568 if self._match_texts(("AT", "BEFORE")): 569 this = self._prev.text.upper() 570 kind = ( 571 self._match(TokenType.L_PAREN) 572 and self._match_texts(self.HISTORICAL_DATA_KIND) 573 and self._prev.text.upper() 574 ) 575 expression = self._match(TokenType.FARROW) and self._parse_bitwise() 576 577 if expression: 578 self._match_r_paren() 579 when = self.expression( 580 exp.HistoricalData, this=this, kind=kind, expression=expression 581 ) 582 table.set("when", when) 583 else: 584 self._retreat(index) 585 586 return table 587 588 def _parse_table_parts( 589 self, schema: bool = False, is_db_reference: bool = False, wildcard: bool = False 590 ) -> exp.Table: 591 # https://docs.snowflake.com/en/user-guide/querying-stage 592 if self._match(TokenType.STRING, advance=False): 593 table = self._parse_string() 594 elif self._match_text_seq("@", advance=False): 595 table = self._parse_location_path() 596 else: 597 table = None 598 599 if table: 600 file_format = None 601 pattern = None 602 603 self._match(TokenType.L_PAREN) 604 while self._curr and not self._match(TokenType.R_PAREN): 605 if self._match_text_seq("FILE_FORMAT", "=>"): 606 file_format = self._parse_string() or super()._parse_table_parts( 607 is_db_reference=is_db_reference 608 ) 609 elif self._match_text_seq("PATTERN", "=>"): 610 pattern = self._parse_string() 611 else: 612 break 613 614 self._match(TokenType.COMMA) 615 616 table = self.expression(exp.Table, this=table, format=file_format, pattern=pattern) 617 else: 618 table = super()._parse_table_parts(schema=schema, is_db_reference=is_db_reference) 619 620 return self._parse_at_before(table) 621 622 def _parse_id_var( 623 self, 624 any_token: bool = True, 625 tokens: t.Optional[t.Collection[TokenType]] = None, 626 ) -> t.Optional[exp.Expression]: 627 if self._match_text_seq("IDENTIFIER", "("): 628 identifier = ( 629 super()._parse_id_var(any_token=any_token, tokens=tokens) 630 or self._parse_string() 631 ) 632 self._match_r_paren() 633 return self.expression(exp.Anonymous, this="IDENTIFIER", expressions=[identifier]) 634 635 return super()._parse_id_var(any_token=any_token, tokens=tokens) 636 637 def _parse_show_snowflake(self, this: str) -> exp.Show: 638 scope = None 639 scope_kind = None 640 641 # will identity SHOW TERSE SCHEMAS but not SHOW TERSE PRIMARY KEYS 642 # which is syntactically valid but has no effect on the output 643 terse = self._tokens[self._index - 2].text.upper() == "TERSE" 644 645 history = self._match_text_seq("HISTORY") 646 647 like = self._parse_string() if self._match(TokenType.LIKE) else None 648 649 if self._match(TokenType.IN): 650 if self._match_text_seq("ACCOUNT"): 651 scope_kind = "ACCOUNT" 652 elif self._match_set(self.DB_CREATABLES): 653 scope_kind = self._prev.text.upper() 654 if self._curr: 655 scope = self._parse_table_parts() 656 elif self._curr: 657 scope_kind = "SCHEMA" if this in self.SCHEMA_KINDS else "TABLE" 658 scope = self._parse_table_parts() 659 660 return self.expression( 661 exp.Show, 662 **{ 663 "terse": terse, 664 "this": this, 665 "history": history, 666 "like": like, 667 "scope": scope, 668 "scope_kind": scope_kind, 669 "starts_with": self._match_text_seq("STARTS", "WITH") and self._parse_string(), 670 "limit": self._parse_limit(), 671 "from": self._parse_string() if self._match(TokenType.FROM) else None, 672 }, 673 ) 674 675 def _parse_alter_table_swap(self) -> exp.SwapTable: 676 self._match_text_seq("WITH") 677 return self.expression(exp.SwapTable, this=self._parse_table(schema=True)) 678 679 def _parse_location(self) -> exp.LocationProperty: 680 self._match(TokenType.EQ) 681 return self.expression(exp.LocationProperty, this=self._parse_location_path()) 682 683 def _parse_location_path(self) -> exp.Var: 684 parts = [self._advance_any(ignore_reserved=True)] 685 686 # We avoid consuming a comma token because external tables like @foo and @bar 687 # can be joined in a query with a comma separator. 688 while self._is_connected() and not self._match(TokenType.COMMA, advance=False): 689 parts.append(self._advance_any(ignore_reserved=True)) 690 691 return exp.var("".join(part.text for part in parts if part)) 692 693 class Tokenizer(tokens.Tokenizer): 694 STRING_ESCAPES = ["\\", "'"] 695 HEX_STRINGS = [("x'", "'"), ("X'", "'")] 696 RAW_STRINGS = ["$$"] 697 COMMENTS = ["--", "//", ("/*", "*/")] 698 699 KEYWORDS = { 700 **tokens.Tokenizer.KEYWORDS, 701 "BYTEINT": TokenType.INT, 702 "CHAR VARYING": TokenType.VARCHAR, 703 "CHARACTER VARYING": TokenType.VARCHAR, 704 "EXCLUDE": TokenType.EXCEPT, 705 "ILIKE ANY": TokenType.ILIKE_ANY, 706 "LIKE ANY": TokenType.LIKE_ANY, 707 "MATCH_CONDITION": TokenType.MATCH_CONDITION, 708 "MATCH_RECOGNIZE": TokenType.MATCH_RECOGNIZE, 709 "MINUS": TokenType.EXCEPT, 710 "NCHAR VARYING": TokenType.VARCHAR, 711 "PUT": TokenType.COMMAND, 712 "REMOVE": TokenType.COMMAND, 713 "RENAME": TokenType.REPLACE, 714 "RM": TokenType.COMMAND, 715 "SAMPLE": TokenType.TABLE_SAMPLE, 716 "SQL_DOUBLE": TokenType.DOUBLE, 717 "SQL_VARCHAR": TokenType.VARCHAR, 718 "STORAGE INTEGRATION": TokenType.STORAGE_INTEGRATION, 719 "TIMESTAMP_LTZ": TokenType.TIMESTAMPLTZ, 720 "TIMESTAMP_NTZ": TokenType.TIMESTAMP, 721 "TIMESTAMP_TZ": TokenType.TIMESTAMPTZ, 722 "TIMESTAMPNTZ": TokenType.TIMESTAMP, 723 "TOP": TokenType.TOP, 724 } 725 726 SINGLE_TOKENS = { 727 **tokens.Tokenizer.SINGLE_TOKENS, 728 "$": TokenType.PARAMETER, 729 } 730 731 VAR_SINGLE_TOKENS = {"$"} 732 733 COMMANDS = tokens.Tokenizer.COMMANDS - {TokenType.SHOW} 734 735 class Generator(generator.Generator): 736 PARAMETER_TOKEN = "$" 737 MATCHED_BY_SOURCE = False 738 SINGLE_STRING_INTERVAL = True 739 JOIN_HINTS = False 740 TABLE_HINTS = False 741 QUERY_HINTS = False 742 AGGREGATE_FILTER_SUPPORTED = False 743 SUPPORTS_TABLE_COPY = False 744 COLLATE_IS_FUNC = True 745 LIMIT_ONLY_LITERALS = True 746 JSON_KEY_VALUE_PAIR_SEP = "," 747 INSERT_OVERWRITE = " OVERWRITE INTO" 748 STRUCT_DELIMITER = ("(", ")") 749 750 TRANSFORMS = { 751 **generator.Generator.TRANSFORMS, 752 exp.ApproxDistinct: rename_func("APPROX_COUNT_DISTINCT"), 753 exp.ArgMax: rename_func("MAX_BY"), 754 exp.ArgMin: rename_func("MIN_BY"), 755 exp.Array: inline_array_sql, 756 exp.ArrayConcat: rename_func("ARRAY_CAT"), 757 exp.ArrayContains: lambda self, e: self.func("ARRAY_CONTAINS", e.expression, e.this), 758 exp.AtTimeZone: lambda self, e: self.func( 759 "CONVERT_TIMEZONE", e.args.get("zone"), e.this 760 ), 761 exp.BitwiseXor: rename_func("BITXOR"), 762 exp.Create: transforms.preprocess([_flatten_structured_types_unless_iceberg]), 763 exp.DateAdd: date_delta_sql("DATEADD"), 764 exp.DateDiff: date_delta_sql("DATEDIFF"), 765 exp.DateStrToDate: datestrtodate_sql, 766 exp.DayOfMonth: rename_func("DAYOFMONTH"), 767 exp.DayOfWeek: rename_func("DAYOFWEEK"), 768 exp.DayOfYear: rename_func("DAYOFYEAR"), 769 exp.Explode: rename_func("FLATTEN"), 770 exp.Extract: rename_func("DATE_PART"), 771 exp.FromTimeZone: lambda self, e: self.func( 772 "CONVERT_TIMEZONE", e.args.get("zone"), "'UTC'", e.this 773 ), 774 exp.GenerateSeries: lambda self, e: self.func( 775 "ARRAY_GENERATE_RANGE", e.args["start"], e.args["end"] + 1, e.args.get("step") 776 ), 777 exp.GroupConcat: rename_func("LISTAGG"), 778 exp.If: if_sql(name="IFF", false_value="NULL"), 779 exp.JSONExtract: lambda self, e: self.func("GET_PATH", e.this, e.expression), 780 exp.JSONExtractScalar: lambda self, e: self.func( 781 "JSON_EXTRACT_PATH_TEXT", e.this, e.expression 782 ), 783 exp.JSONObject: lambda self, e: self.func("OBJECT_CONSTRUCT_KEEP_NULL", *e.expressions), 784 exp.JSONPathRoot: lambda *_: "", 785 exp.LogicalAnd: rename_func("BOOLAND_AGG"), 786 exp.LogicalOr: rename_func("BOOLOR_AGG"), 787 exp.Map: lambda self, e: var_map_sql(self, e, "OBJECT_CONSTRUCT"), 788 exp.Max: max_or_greatest, 789 exp.Min: min_or_least, 790 exp.PartitionedByProperty: lambda self, e: f"PARTITION BY {self.sql(e, 'this')}", 791 exp.PercentileCont: transforms.preprocess( 792 [transforms.add_within_group_for_percentiles] 793 ), 794 exp.PercentileDisc: transforms.preprocess( 795 [transforms.add_within_group_for_percentiles] 796 ), 797 exp.Pivot: transforms.preprocess([_unqualify_unpivot_columns]), 798 exp.RegexpILike: _regexpilike_sql, 799 exp.Rand: rename_func("RANDOM"), 800 exp.Select: transforms.preprocess( 801 [ 802 transforms.eliminate_distinct_on, 803 transforms.explode_to_unnest(), 804 transforms.eliminate_semi_and_anti_joins, 805 ] 806 ), 807 exp.SHA: rename_func("SHA1"), 808 exp.StarMap: rename_func("OBJECT_CONSTRUCT"), 809 exp.StartsWith: rename_func("STARTSWITH"), 810 exp.StrPosition: lambda self, e: self.func( 811 "POSITION", e.args.get("substr"), e.this, e.args.get("position") 812 ), 813 exp.StrToTime: lambda self, e: self.func("TO_TIMESTAMP", e.this, self.format_time(e)), 814 exp.Stuff: rename_func("INSERT"), 815 exp.TimeAdd: date_delta_sql("TIMEADD"), 816 exp.TimestampDiff: lambda self, e: self.func( 817 "TIMESTAMPDIFF", e.unit, e.expression, e.this 818 ), 819 exp.TimestampTrunc: timestamptrunc_sql, 820 exp.TimeStrToTime: timestrtotime_sql, 821 exp.TimeToStr: lambda self, e: self.func( 822 "TO_CHAR", exp.cast(e.this, exp.DataType.Type.TIMESTAMP), self.format_time(e) 823 ), 824 exp.TimeToUnix: lambda self, e: f"EXTRACT(epoch_second FROM {self.sql(e, 'this')})", 825 exp.ToArray: rename_func("TO_ARRAY"), 826 exp.ToChar: lambda self, e: self.function_fallback_sql(e), 827 exp.Trim: lambda self, e: self.func("TRIM", e.this, e.expression), 828 exp.TsOrDsAdd: date_delta_sql("DATEADD", cast=True), 829 exp.TsOrDsDiff: date_delta_sql("DATEDIFF"), 830 exp.TsOrDsToDate: lambda self, e: self.func( 831 "TRY_TO_DATE" if e.args.get("safe") else "TO_DATE", e.this, self.format_time(e) 832 ), 833 exp.UnixToTime: rename_func("TO_TIMESTAMP"), 834 exp.VarMap: lambda self, e: var_map_sql(self, e, "OBJECT_CONSTRUCT"), 835 exp.WeekOfYear: rename_func("WEEKOFYEAR"), 836 exp.Xor: rename_func("BOOLXOR"), 837 } 838 839 SUPPORTED_JSON_PATH_PARTS = { 840 exp.JSONPathKey, 841 exp.JSONPathRoot, 842 exp.JSONPathSubscript, 843 } 844 845 TYPE_MAPPING = { 846 **generator.Generator.TYPE_MAPPING, 847 exp.DataType.Type.NESTED: "OBJECT", 848 exp.DataType.Type.STRUCT: "OBJECT", 849 exp.DataType.Type.TIMESTAMP: "TIMESTAMPNTZ", 850 } 851 852 STAR_MAPPING = { 853 "except": "EXCLUDE", 854 "replace": "RENAME", 855 } 856 857 PROPERTIES_LOCATION = { 858 **generator.Generator.PROPERTIES_LOCATION, 859 exp.SetProperty: exp.Properties.Location.UNSUPPORTED, 860 exp.VolatileProperty: exp.Properties.Location.UNSUPPORTED, 861 } 862 863 UNSUPPORTED_VALUES_EXPRESSIONS = { 864 exp.Struct, 865 } 866 867 def values_sql(self, expression: exp.Values, values_as_table: bool = True) -> str: 868 if expression.find(*self.UNSUPPORTED_VALUES_EXPRESSIONS): 869 values_as_table = False 870 871 return super().values_sql(expression, values_as_table=values_as_table) 872 873 def datatype_sql(self, expression: exp.DataType) -> str: 874 expressions = expression.expressions 875 if ( 876 expressions 877 and expression.is_type(*exp.DataType.STRUCT_TYPES) 878 and any(isinstance(field_type, exp.DataType) for field_type in expressions) 879 ): 880 # The correct syntax is OBJECT [ (<key> <value_type [NOT NULL] [, ...]) ] 881 return "OBJECT" 882 883 return super().datatype_sql(expression) 884 885 def tonumber_sql(self, expression: exp.ToNumber) -> str: 886 return self.func( 887 "TO_NUMBER", 888 expression.this, 889 expression.args.get("format"), 890 expression.args.get("precision"), 891 expression.args.get("scale"), 892 ) 893 894 def timestampfromparts_sql(self, expression: exp.TimestampFromParts) -> str: 895 milli = expression.args.get("milli") 896 if milli is not None: 897 milli_to_nano = milli.pop() * exp.Literal.number(1000000) 898 expression.set("nano", milli_to_nano) 899 900 return rename_func("TIMESTAMP_FROM_PARTS")(self, expression) 901 902 def trycast_sql(self, expression: exp.TryCast) -> str: 903 value = expression.this 904 905 if value.type is None: 906 from sqlglot.optimizer.annotate_types import annotate_types 907 908 value = annotate_types(value) 909 910 if value.is_type(*exp.DataType.TEXT_TYPES, exp.DataType.Type.UNKNOWN): 911 return super().trycast_sql(expression) 912 913 # TRY_CAST only works for string values in Snowflake 914 return self.cast_sql(expression) 915 916 def log_sql(self, expression: exp.Log) -> str: 917 if not expression.expression: 918 return self.func("LN", expression.this) 919 920 return super().log_sql(expression) 921 922 def unnest_sql(self, expression: exp.Unnest) -> str: 923 unnest_alias = expression.args.get("alias") 924 offset = expression.args.get("offset") 925 926 columns = [ 927 exp.to_identifier("seq"), 928 exp.to_identifier("key"), 929 exp.to_identifier("path"), 930 offset.pop() if isinstance(offset, exp.Expression) else exp.to_identifier("index"), 931 seq_get(unnest_alias.columns if unnest_alias else [], 0) 932 or exp.to_identifier("value"), 933 exp.to_identifier("this"), 934 ] 935 936 if unnest_alias: 937 unnest_alias.set("columns", columns) 938 else: 939 unnest_alias = exp.TableAlias(this="_u", columns=columns) 940 941 explode = f"TABLE(FLATTEN(INPUT => {self.sql(expression.expressions[0])}))" 942 alias = self.sql(unnest_alias) 943 alias = f" AS {alias}" if alias else "" 944 return f"{explode}{alias}" 945 946 def show_sql(self, expression: exp.Show) -> str: 947 terse = "TERSE " if expression.args.get("terse") else "" 948 history = " HISTORY" if expression.args.get("history") else "" 949 like = self.sql(expression, "like") 950 like = f" LIKE {like}" if like else "" 951 952 scope = self.sql(expression, "scope") 953 scope = f" {scope}" if scope else "" 954 955 scope_kind = self.sql(expression, "scope_kind") 956 if scope_kind: 957 scope_kind = f" IN {scope_kind}" 958 959 starts_with = self.sql(expression, "starts_with") 960 if starts_with: 961 starts_with = f" STARTS WITH {starts_with}" 962 963 limit = self.sql(expression, "limit") 964 965 from_ = self.sql(expression, "from") 966 if from_: 967 from_ = f" FROM {from_}" 968 969 return f"SHOW {terse}{expression.name}{history}{like}{scope_kind}{scope}{starts_with}{limit}{from_}" 970 971 def regexpextract_sql(self, expression: exp.RegexpExtract) -> str: 972 # Other dialects don't support all of the following parameters, so we need to 973 # generate default values as necessary to ensure the transpilation is correct 974 group = expression.args.get("group") 975 parameters = expression.args.get("parameters") or (group and exp.Literal.string("c")) 976 occurrence = expression.args.get("occurrence") or (parameters and exp.Literal.number(1)) 977 position = expression.args.get("position") or (occurrence and exp.Literal.number(1)) 978 979 return self.func( 980 "REGEXP_SUBSTR", 981 expression.this, 982 expression.expression, 983 position, 984 occurrence, 985 parameters, 986 group, 987 ) 988 989 def except_op(self, expression: exp.Except) -> str: 990 if not expression.args.get("distinct"): 991 self.unsupported("EXCEPT with All is not supported in Snowflake") 992 return super().except_op(expression) 993 994 def intersect_op(self, expression: exp.Intersect) -> str: 995 if not expression.args.get("distinct"): 996 self.unsupported("INTERSECT with All is not supported in Snowflake") 997 return super().intersect_op(expression) 998 999 def describe_sql(self, expression: exp.Describe) -> str: 1000 # Default to table if kind is unknown 1001 kind_value = expression.args.get("kind") or "TABLE" 1002 kind = f" {kind_value}" if kind_value else "" 1003 this = f" {self.sql(expression, 'this')}" 1004 expressions = self.expressions(expression, flat=True) 1005 expressions = f" {expressions}" if expressions else "" 1006 return f"DESCRIBE{kind}{this}{expressions}" 1007 1008 def generatedasidentitycolumnconstraint_sql( 1009 self, expression: exp.GeneratedAsIdentityColumnConstraint 1010 ) -> str: 1011 start = expression.args.get("start") 1012 start = f" START {start}" if start else "" 1013 increment = expression.args.get("increment") 1014 increment = f" INCREMENT {increment}" if increment else "" 1015 return f"AUTOINCREMENT{start}{increment}" 1016 1017 def swaptable_sql(self, expression: exp.SwapTable) -> str: 1018 this = self.sql(expression, "this") 1019 return f"SWAP WITH {this}" 1020 1021 def with_properties(self, properties: exp.Properties) -> str: 1022 return self.properties(properties, wrapped=False, prefix=self.seg(""), sep=" ") 1023 1024 def cluster_sql(self, expression: exp.Cluster) -> str: 1025 return f"CLUSTER BY ({self.expressions(expression, flat=True)})" 1026 1027 def struct_sql(self, expression: exp.Struct) -> str: 1028 keys = [] 1029 values = [] 1030 1031 for i, e in enumerate(expression.expressions): 1032 if isinstance(e, exp.PropertyEQ): 1033 keys.append( 1034 exp.Literal.string(e.name) if isinstance(e.this, exp.Identifier) else e.this 1035 ) 1036 values.append(e.expression) 1037 else: 1038 keys.append(exp.Literal.string(f"_{i}")) 1039 values.append(e) 1040 1041 return self.func("OBJECT_CONSTRUCT", *flatten(zip(keys, values)))
Specifies the strategy according to which identifiers should be normalized.
Default NULL
ordering method to use if not explicitly set.
Possible values: "nulls_are_small"
, "nulls_are_large"
, "nulls_are_last"
Some dialects, such as Snowflake, allow you to reference a CTE column alias in the HAVING clause of the CTE. This flag will cause the CTE alias columns to override any projection aliases in the subquery.
For example, WITH y(c) AS ( SELECT SUM(a) FROM (SELECT 1 a) AS x HAVING c > 0 ) SELECT c FROM y;
will be rewritten as
WITH y(c) AS (
SELECT SUM(a) AS c FROM (SELECT 1 AS a) AS x HAVING c > 0
) SELECT c FROM y;
Associates this dialect's time formats with their equivalent Python strftime
formats.
323 def quote_identifier(self, expression: E, identify: bool = True) -> E: 324 # This disables quoting DUAL in SELECT ... FROM DUAL, because Snowflake treats an 325 # unquoted DUAL keyword in a special way and does not map it to a user-defined table 326 if ( 327 isinstance(expression, exp.Identifier) 328 and isinstance(expression.parent, exp.Table) 329 and expression.name.lower() == "dual" 330 ): 331 return expression # type: ignore 332 333 return super().quote_identifier(expression, identify=identify)
Adds quotes to a given identifier.
Arguments:
- expression: The expression of interest. If it's not an
Identifier
, this method is a no-op. - identify: If set to
False
, the quotes will only be added if the identifier is deemed "unsafe", with respect to its characters and this dialect's normalization strategy.
Mapping of an escaped sequence (\n
) to its unescaped version (
).
Inherited Members
- sqlglot.dialects.dialect.Dialect
- Dialect
- INDEX_OFFSET
- WEEK_OFFSET
- UNNEST_COLUMN_ONLY
- ALIAS_POST_TABLESAMPLE
- IDENTIFIERS_CAN_START_WITH_DIGIT
- DPIPE_IS_STRING_CONCAT
- STRICT_STRING_CONCAT
- NORMALIZE_FUNCTIONS
- LOG_BASE_FIRST
- TYPED_DIVISION
- SAFE_DIVISION
- CONCAT_COALESCE
- DATE_FORMAT
- DATEINT_FORMAT
- FORMAT_MAPPING
- PSEUDOCOLUMNS
- get_or_raise
- format_time
- normalize_identifier
- case_sensitive
- can_identify
- to_json_path
- parse
- parse_into
- generate
- transpile
- tokenize
- tokenizer
- parser
- generator
335 class Parser(parser.Parser): 336 IDENTIFY_PIVOT_STRINGS = True 337 338 ID_VAR_TOKENS = { 339 *parser.Parser.ID_VAR_TOKENS, 340 TokenType.MATCH_CONDITION, 341 } 342 343 TABLE_ALIAS_TOKENS = parser.Parser.TABLE_ALIAS_TOKENS | {TokenType.WINDOW} 344 TABLE_ALIAS_TOKENS.discard(TokenType.MATCH_CONDITION) 345 346 FUNCTIONS = { 347 **parser.Parser.FUNCTIONS, 348 "ARRAYAGG": exp.ArrayAgg.from_arg_list, 349 "ARRAY_CONSTRUCT": exp.Array.from_arg_list, 350 "ARRAY_CONTAINS": lambda args: exp.ArrayContains( 351 this=seq_get(args, 1), expression=seq_get(args, 0) 352 ), 353 "ARRAY_GENERATE_RANGE": lambda args: exp.GenerateSeries( 354 # ARRAY_GENERATE_RANGE has an exlusive end; we normalize it to be inclusive 355 start=seq_get(args, 0), 356 end=exp.Sub(this=seq_get(args, 1), expression=exp.Literal.number(1)), 357 step=seq_get(args, 2), 358 ), 359 "BITXOR": binary_from_function(exp.BitwiseXor), 360 "BIT_XOR": binary_from_function(exp.BitwiseXor), 361 "BOOLXOR": binary_from_function(exp.Xor), 362 "CONVERT_TIMEZONE": _build_convert_timezone, 363 "DATE": _build_datetime("DATE", exp.DataType.Type.DATE), 364 "DATE_TRUNC": _date_trunc_to_time, 365 "DATEADD": _build_date_time_add(exp.DateAdd), 366 "DATEDIFF": _build_datediff, 367 "DIV0": _build_if_from_div0, 368 "FLATTEN": exp.Explode.from_arg_list, 369 "GET_PATH": lambda args, dialect: exp.JSONExtract( 370 this=seq_get(args, 0), expression=dialect.to_json_path(seq_get(args, 1)) 371 ), 372 "IFF": exp.If.from_arg_list, 373 "LAST_DAY": lambda args: exp.LastDay( 374 this=seq_get(args, 0), unit=_map_date_part(seq_get(args, 1)) 375 ), 376 "LISTAGG": exp.GroupConcat.from_arg_list, 377 "MEDIAN": lambda args: exp.PercentileCont( 378 this=seq_get(args, 0), expression=exp.Literal.number(0.5) 379 ), 380 "NULLIFZERO": _build_if_from_nullifzero, 381 "OBJECT_CONSTRUCT": _build_object_construct, 382 "REGEXP_REPLACE": _build_regexp_replace, 383 "REGEXP_SUBSTR": exp.RegexpExtract.from_arg_list, 384 "RLIKE": exp.RegexpLike.from_arg_list, 385 "SQUARE": lambda args: exp.Pow(this=seq_get(args, 0), expression=exp.Literal.number(2)), 386 "TIMEADD": _build_date_time_add(exp.TimeAdd), 387 "TIMEDIFF": _build_datediff, 388 "TIMESTAMPADD": _build_date_time_add(exp.DateAdd), 389 "TIMESTAMPDIFF": _build_datediff, 390 "TIMESTAMPFROMPARTS": _build_timestamp_from_parts, 391 "TIMESTAMP_FROM_PARTS": _build_timestamp_from_parts, 392 "TRY_TO_DATE": _build_datetime("TRY_TO_DATE", exp.DataType.Type.DATE, safe=True), 393 "TO_DATE": _build_datetime("TO_DATE", exp.DataType.Type.DATE), 394 "TO_NUMBER": lambda args: exp.ToNumber( 395 this=seq_get(args, 0), 396 format=seq_get(args, 1), 397 precision=seq_get(args, 2), 398 scale=seq_get(args, 3), 399 ), 400 "TO_TIME": _build_datetime("TO_TIME", exp.DataType.Type.TIME), 401 "TO_TIMESTAMP": _build_datetime("TO_TIMESTAMP", exp.DataType.Type.TIMESTAMP), 402 "TO_TIMESTAMP_LTZ": _build_datetime("TO_TIMESTAMP_LTZ", exp.DataType.Type.TIMESTAMPLTZ), 403 "TO_TIMESTAMP_NTZ": _build_datetime("TO_TIMESTAMP_NTZ", exp.DataType.Type.TIMESTAMP), 404 "TO_TIMESTAMP_TZ": _build_datetime("TO_TIMESTAMP_TZ", exp.DataType.Type.TIMESTAMPTZ), 405 "TO_VARCHAR": exp.ToChar.from_arg_list, 406 "ZEROIFNULL": _build_if_from_zeroifnull, 407 } 408 409 FUNCTION_PARSERS = { 410 **parser.Parser.FUNCTION_PARSERS, 411 "DATE_PART": lambda self: self._parse_date_part(), 412 "OBJECT_CONSTRUCT_KEEP_NULL": lambda self: self._parse_json_object(), 413 } 414 FUNCTION_PARSERS.pop("TRIM") 415 416 TIMESTAMPS = parser.Parser.TIMESTAMPS - {TokenType.TIME} 417 418 RANGE_PARSERS = { 419 **parser.Parser.RANGE_PARSERS, 420 TokenType.LIKE_ANY: parser.binary_range_parser(exp.LikeAny), 421 TokenType.ILIKE_ANY: parser.binary_range_parser(exp.ILikeAny), 422 } 423 424 ALTER_PARSERS = { 425 **parser.Parser.ALTER_PARSERS, 426 "SET": lambda self: self._parse_set(tag=self._match_text_seq("TAG")), 427 "UNSET": lambda self: self.expression( 428 exp.Set, 429 tag=self._match_text_seq("TAG"), 430 expressions=self._parse_csv(self._parse_id_var), 431 unset=True, 432 ), 433 "SWAP": lambda self: self._parse_alter_table_swap(), 434 } 435 436 STATEMENT_PARSERS = { 437 **parser.Parser.STATEMENT_PARSERS, 438 TokenType.SHOW: lambda self: self._parse_show(), 439 } 440 441 PROPERTY_PARSERS = { 442 **parser.Parser.PROPERTY_PARSERS, 443 "LOCATION": lambda self: self._parse_location(), 444 } 445 446 SHOW_PARSERS = { 447 "SCHEMAS": _show_parser("SCHEMAS"), 448 "TERSE SCHEMAS": _show_parser("SCHEMAS"), 449 "OBJECTS": _show_parser("OBJECTS"), 450 "TERSE OBJECTS": _show_parser("OBJECTS"), 451 "TABLES": _show_parser("TABLES"), 452 "TERSE TABLES": _show_parser("TABLES"), 453 "VIEWS": _show_parser("VIEWS"), 454 "TERSE VIEWS": _show_parser("VIEWS"), 455 "PRIMARY KEYS": _show_parser("PRIMARY KEYS"), 456 "TERSE PRIMARY KEYS": _show_parser("PRIMARY KEYS"), 457 "IMPORTED KEYS": _show_parser("IMPORTED KEYS"), 458 "TERSE IMPORTED KEYS": _show_parser("IMPORTED KEYS"), 459 "UNIQUE KEYS": _show_parser("UNIQUE KEYS"), 460 "TERSE UNIQUE KEYS": _show_parser("UNIQUE KEYS"), 461 "SEQUENCES": _show_parser("SEQUENCES"), 462 "TERSE SEQUENCES": _show_parser("SEQUENCES"), 463 "COLUMNS": _show_parser("COLUMNS"), 464 "USERS": _show_parser("USERS"), 465 "TERSE USERS": _show_parser("USERS"), 466 } 467 468 STAGED_FILE_SINGLE_TOKENS = { 469 TokenType.DOT, 470 TokenType.MOD, 471 TokenType.SLASH, 472 } 473 474 FLATTEN_COLUMNS = ["SEQ", "KEY", "PATH", "INDEX", "VALUE", "THIS"] 475 476 SCHEMA_KINDS = {"OBJECTS", "TABLES", "VIEWS", "SEQUENCES", "UNIQUE KEYS", "IMPORTED KEYS"} 477 478 def _parse_column_ops(self, this: t.Optional[exp.Expression]) -> t.Optional[exp.Expression]: 479 this = super()._parse_column_ops(this) 480 481 casts = [] 482 json_path = [] 483 484 while self._match(TokenType.COLON): 485 path = super()._parse_column_ops(self._parse_field(any_token=True)) 486 487 # The cast :: operator has a lower precedence than the extraction operator :, so 488 # we rearrange the AST appropriately to avoid casting the 2nd argument of GET_PATH 489 while isinstance(path, exp.Cast): 490 casts.append(path.to) 491 path = path.this 492 493 if path: 494 json_path.append(path.sql(dialect="snowflake", copy=False)) 495 496 if json_path: 497 this = self.expression( 498 exp.JSONExtract, 499 this=this, 500 expression=self.dialect.to_json_path(exp.Literal.string(".".join(json_path))), 501 ) 502 503 while casts: 504 this = self.expression(exp.Cast, this=this, to=casts.pop()) 505 506 return this 507 508 # https://docs.snowflake.com/en/sql-reference/functions/date_part.html 509 # https://docs.snowflake.com/en/sql-reference/functions-date-time.html#label-supported-date-time-parts 510 def _parse_date_part(self: Snowflake.Parser) -> t.Optional[exp.Expression]: 511 this = self._parse_var() or self._parse_type() 512 513 if not this: 514 return None 515 516 self._match(TokenType.COMMA) 517 expression = self._parse_bitwise() 518 this = _map_date_part(this) 519 name = this.name.upper() 520 521 if name.startswith("EPOCH"): 522 if name == "EPOCH_MILLISECOND": 523 scale = 10**3 524 elif name == "EPOCH_MICROSECOND": 525 scale = 10**6 526 elif name == "EPOCH_NANOSECOND": 527 scale = 10**9 528 else: 529 scale = None 530 531 ts = self.expression(exp.Cast, this=expression, to=exp.DataType.build("TIMESTAMP")) 532 to_unix: exp.Expression = self.expression(exp.TimeToUnix, this=ts) 533 534 if scale: 535 to_unix = exp.Mul(this=to_unix, expression=exp.Literal.number(scale)) 536 537 return to_unix 538 539 return self.expression(exp.Extract, this=this, expression=expression) 540 541 def _parse_bracket_key_value(self, is_map: bool = False) -> t.Optional[exp.Expression]: 542 if is_map: 543 # Keys are strings in Snowflake's objects, see also: 544 # - https://docs.snowflake.com/en/sql-reference/data-types-semistructured 545 # - https://docs.snowflake.com/en/sql-reference/functions/object_construct 546 return self._parse_slice(self._parse_string()) 547 548 return self._parse_slice(self._parse_alias(self._parse_conjunction(), explicit=True)) 549 550 def _parse_lateral(self) -> t.Optional[exp.Lateral]: 551 lateral = super()._parse_lateral() 552 if not lateral: 553 return lateral 554 555 if isinstance(lateral.this, exp.Explode): 556 table_alias = lateral.args.get("alias") 557 columns = [exp.to_identifier(col) for col in self.FLATTEN_COLUMNS] 558 if table_alias and not table_alias.args.get("columns"): 559 table_alias.set("columns", columns) 560 elif not table_alias: 561 exp.alias_(lateral, "_flattened", table=columns, copy=False) 562 563 return lateral 564 565 def _parse_at_before(self, table: exp.Table) -> exp.Table: 566 # https://docs.snowflake.com/en/sql-reference/constructs/at-before 567 index = self._index 568 if self._match_texts(("AT", "BEFORE")): 569 this = self._prev.text.upper() 570 kind = ( 571 self._match(TokenType.L_PAREN) 572 and self._match_texts(self.HISTORICAL_DATA_KIND) 573 and self._prev.text.upper() 574 ) 575 expression = self._match(TokenType.FARROW) and self._parse_bitwise() 576 577 if expression: 578 self._match_r_paren() 579 when = self.expression( 580 exp.HistoricalData, this=this, kind=kind, expression=expression 581 ) 582 table.set("when", when) 583 else: 584 self._retreat(index) 585 586 return table 587 588 def _parse_table_parts( 589 self, schema: bool = False, is_db_reference: bool = False, wildcard: bool = False 590 ) -> exp.Table: 591 # https://docs.snowflake.com/en/user-guide/querying-stage 592 if self._match(TokenType.STRING, advance=False): 593 table = self._parse_string() 594 elif self._match_text_seq("@", advance=False): 595 table = self._parse_location_path() 596 else: 597 table = None 598 599 if table: 600 file_format = None 601 pattern = None 602 603 self._match(TokenType.L_PAREN) 604 while self._curr and not self._match(TokenType.R_PAREN): 605 if self._match_text_seq("FILE_FORMAT", "=>"): 606 file_format = self._parse_string() or super()._parse_table_parts( 607 is_db_reference=is_db_reference 608 ) 609 elif self._match_text_seq("PATTERN", "=>"): 610 pattern = self._parse_string() 611 else: 612 break 613 614 self._match(TokenType.COMMA) 615 616 table = self.expression(exp.Table, this=table, format=file_format, pattern=pattern) 617 else: 618 table = super()._parse_table_parts(schema=schema, is_db_reference=is_db_reference) 619 620 return self._parse_at_before(table) 621 622 def _parse_id_var( 623 self, 624 any_token: bool = True, 625 tokens: t.Optional[t.Collection[TokenType]] = None, 626 ) -> t.Optional[exp.Expression]: 627 if self._match_text_seq("IDENTIFIER", "("): 628 identifier = ( 629 super()._parse_id_var(any_token=any_token, tokens=tokens) 630 or self._parse_string() 631 ) 632 self._match_r_paren() 633 return self.expression(exp.Anonymous, this="IDENTIFIER", expressions=[identifier]) 634 635 return super()._parse_id_var(any_token=any_token, tokens=tokens) 636 637 def _parse_show_snowflake(self, this: str) -> exp.Show: 638 scope = None 639 scope_kind = None 640 641 # will identity SHOW TERSE SCHEMAS but not SHOW TERSE PRIMARY KEYS 642 # which is syntactically valid but has no effect on the output 643 terse = self._tokens[self._index - 2].text.upper() == "TERSE" 644 645 history = self._match_text_seq("HISTORY") 646 647 like = self._parse_string() if self._match(TokenType.LIKE) else None 648 649 if self._match(TokenType.IN): 650 if self._match_text_seq("ACCOUNT"): 651 scope_kind = "ACCOUNT" 652 elif self._match_set(self.DB_CREATABLES): 653 scope_kind = self._prev.text.upper() 654 if self._curr: 655 scope = self._parse_table_parts() 656 elif self._curr: 657 scope_kind = "SCHEMA" if this in self.SCHEMA_KINDS else "TABLE" 658 scope = self._parse_table_parts() 659 660 return self.expression( 661 exp.Show, 662 **{ 663 "terse": terse, 664 "this": this, 665 "history": history, 666 "like": like, 667 "scope": scope, 668 "scope_kind": scope_kind, 669 "starts_with": self._match_text_seq("STARTS", "WITH") and self._parse_string(), 670 "limit": self._parse_limit(), 671 "from": self._parse_string() if self._match(TokenType.FROM) else None, 672 }, 673 ) 674 675 def _parse_alter_table_swap(self) -> exp.SwapTable: 676 self._match_text_seq("WITH") 677 return self.expression(exp.SwapTable, this=self._parse_table(schema=True)) 678 679 def _parse_location(self) -> exp.LocationProperty: 680 self._match(TokenType.EQ) 681 return self.expression(exp.LocationProperty, this=self._parse_location_path()) 682 683 def _parse_location_path(self) -> exp.Var: 684 parts = [self._advance_any(ignore_reserved=True)] 685 686 # We avoid consuming a comma token because external tables like @foo and @bar 687 # can be joined in a query with a comma separator. 688 while self._is_connected() and not self._match(TokenType.COMMA, advance=False): 689 parts.append(self._advance_any(ignore_reserved=True)) 690 691 return exp.var("".join(part.text for part in parts if part))
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
- INTERVAL_VARS
- ALIAS_TOKENS
- COMMENT_TABLE_ALIAS_TOKENS
- UPDATE_ALIAS_TOKENS
- TRIM_TYPES
- FUNC_TOKENS
- CONJUNCTION
- EQUALITY
- COMPARISON
- BITWISE
- TERM
- FACTOR
- EXPONENT
- TIMES
- SET_OPERATIONS
- JOIN_METHODS
- JOIN_SIDES
- JOIN_KINDS
- JOIN_HINTS
- LAMBDAS
- COLUMN_OPERATORS
- EXPRESSION_PARSERS
- UNARY_PARSERS
- STRING_PARSERS
- NUMERIC_PARSERS
- PRIMARY_PARSERS
- PLACEHOLDER_PARSERS
- CONSTRAINT_PARSERS
- SCHEMA_UNNAMED_CONSTRAINTS
- NO_PAREN_FUNCTION_PARSERS
- INVALID_FUNC_NAME_TOKENS
- FUNCTIONS_WITH_ALIASED_ARGS
- KEY_VALUE_DEFINITIONS
- QUERY_MODIFIER_PARSERS
- SET_PARSERS
- TYPE_LITERAL_PARSERS
- DDL_SELECT_TOKENS
- PRE_VOLATILE_TOKENS
- TRANSACTION_KIND
- TRANSACTION_CHARACTERISTICS
- CONFLICT_ACTIONS
- CREATE_SEQUENCE
- ISOLATED_LOADING_OPTIONS
- USABLES
- CAST_ACTIONS
- INSERT_ALTERNATIVES
- CLONE_KEYWORDS
- 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
- STRICT_CAST
- PREFIXED_PIVOT_COLUMNS
- LOG_DEFAULTS_TO_LN
- ALTER_TABLE_ADD_REQUIRED_FOR_EACH_COLUMN
- TABLESAMPLE_CSV
- SET_REQUIRES_ASSIGNMENT_DELIMITER
- TRIM_PATTERN_FIRST
- STRING_ALIASES
- MODIFIERS_ATTACHED_TO_UNION
- UNION_MODIFIERS
- NO_PAREN_IF_COMMANDS
- JSON_ARROWS_REQUIRE_JSON_TYPE
- VALUES_FOLLOWED_BY_PAREN
- SUPPORTS_IMPLICIT_UNNEST
- INTERVAL_SPANS
- SUPPORTS_PARTITION_SELECTION
- error_level
- error_message_context
- max_errors
- dialect
- reset
- parse
- parse_into
- check_errors
- raise_error
- expression
- validate_expression
- errors
- sql
693 class Tokenizer(tokens.Tokenizer): 694 STRING_ESCAPES = ["\\", "'"] 695 HEX_STRINGS = [("x'", "'"), ("X'", "'")] 696 RAW_STRINGS = ["$$"] 697 COMMENTS = ["--", "//", ("/*", "*/")] 698 699 KEYWORDS = { 700 **tokens.Tokenizer.KEYWORDS, 701 "BYTEINT": TokenType.INT, 702 "CHAR VARYING": TokenType.VARCHAR, 703 "CHARACTER VARYING": TokenType.VARCHAR, 704 "EXCLUDE": TokenType.EXCEPT, 705 "ILIKE ANY": TokenType.ILIKE_ANY, 706 "LIKE ANY": TokenType.LIKE_ANY, 707 "MATCH_CONDITION": TokenType.MATCH_CONDITION, 708 "MATCH_RECOGNIZE": TokenType.MATCH_RECOGNIZE, 709 "MINUS": TokenType.EXCEPT, 710 "NCHAR VARYING": TokenType.VARCHAR, 711 "PUT": TokenType.COMMAND, 712 "REMOVE": TokenType.COMMAND, 713 "RENAME": TokenType.REPLACE, 714 "RM": TokenType.COMMAND, 715 "SAMPLE": TokenType.TABLE_SAMPLE, 716 "SQL_DOUBLE": TokenType.DOUBLE, 717 "SQL_VARCHAR": TokenType.VARCHAR, 718 "STORAGE INTEGRATION": TokenType.STORAGE_INTEGRATION, 719 "TIMESTAMP_LTZ": TokenType.TIMESTAMPLTZ, 720 "TIMESTAMP_NTZ": TokenType.TIMESTAMP, 721 "TIMESTAMP_TZ": TokenType.TIMESTAMPTZ, 722 "TIMESTAMPNTZ": TokenType.TIMESTAMP, 723 "TOP": TokenType.TOP, 724 } 725 726 SINGLE_TOKENS = { 727 **tokens.Tokenizer.SINGLE_TOKENS, 728 "$": TokenType.PARAMETER, 729 } 730 731 VAR_SINGLE_TOKENS = {"$"} 732 733 COMMANDS = tokens.Tokenizer.COMMANDS - {TokenType.SHOW}
Inherited Members
735 class Generator(generator.Generator): 736 PARAMETER_TOKEN = "$" 737 MATCHED_BY_SOURCE = False 738 SINGLE_STRING_INTERVAL = True 739 JOIN_HINTS = False 740 TABLE_HINTS = False 741 QUERY_HINTS = False 742 AGGREGATE_FILTER_SUPPORTED = False 743 SUPPORTS_TABLE_COPY = False 744 COLLATE_IS_FUNC = True 745 LIMIT_ONLY_LITERALS = True 746 JSON_KEY_VALUE_PAIR_SEP = "," 747 INSERT_OVERWRITE = " OVERWRITE INTO" 748 STRUCT_DELIMITER = ("(", ")") 749 750 TRANSFORMS = { 751 **generator.Generator.TRANSFORMS, 752 exp.ApproxDistinct: rename_func("APPROX_COUNT_DISTINCT"), 753 exp.ArgMax: rename_func("MAX_BY"), 754 exp.ArgMin: rename_func("MIN_BY"), 755 exp.Array: inline_array_sql, 756 exp.ArrayConcat: rename_func("ARRAY_CAT"), 757 exp.ArrayContains: lambda self, e: self.func("ARRAY_CONTAINS", e.expression, e.this), 758 exp.AtTimeZone: lambda self, e: self.func( 759 "CONVERT_TIMEZONE", e.args.get("zone"), e.this 760 ), 761 exp.BitwiseXor: rename_func("BITXOR"), 762 exp.Create: transforms.preprocess([_flatten_structured_types_unless_iceberg]), 763 exp.DateAdd: date_delta_sql("DATEADD"), 764 exp.DateDiff: date_delta_sql("DATEDIFF"), 765 exp.DateStrToDate: datestrtodate_sql, 766 exp.DayOfMonth: rename_func("DAYOFMONTH"), 767 exp.DayOfWeek: rename_func("DAYOFWEEK"), 768 exp.DayOfYear: rename_func("DAYOFYEAR"), 769 exp.Explode: rename_func("FLATTEN"), 770 exp.Extract: rename_func("DATE_PART"), 771 exp.FromTimeZone: lambda self, e: self.func( 772 "CONVERT_TIMEZONE", e.args.get("zone"), "'UTC'", e.this 773 ), 774 exp.GenerateSeries: lambda self, e: self.func( 775 "ARRAY_GENERATE_RANGE", e.args["start"], e.args["end"] + 1, e.args.get("step") 776 ), 777 exp.GroupConcat: rename_func("LISTAGG"), 778 exp.If: if_sql(name="IFF", false_value="NULL"), 779 exp.JSONExtract: lambda self, e: self.func("GET_PATH", e.this, e.expression), 780 exp.JSONExtractScalar: lambda self, e: self.func( 781 "JSON_EXTRACT_PATH_TEXT", e.this, e.expression 782 ), 783 exp.JSONObject: lambda self, e: self.func("OBJECT_CONSTRUCT_KEEP_NULL", *e.expressions), 784 exp.JSONPathRoot: lambda *_: "", 785 exp.LogicalAnd: rename_func("BOOLAND_AGG"), 786 exp.LogicalOr: rename_func("BOOLOR_AGG"), 787 exp.Map: lambda self, e: var_map_sql(self, e, "OBJECT_CONSTRUCT"), 788 exp.Max: max_or_greatest, 789 exp.Min: min_or_least, 790 exp.PartitionedByProperty: lambda self, e: f"PARTITION BY {self.sql(e, 'this')}", 791 exp.PercentileCont: transforms.preprocess( 792 [transforms.add_within_group_for_percentiles] 793 ), 794 exp.PercentileDisc: transforms.preprocess( 795 [transforms.add_within_group_for_percentiles] 796 ), 797 exp.Pivot: transforms.preprocess([_unqualify_unpivot_columns]), 798 exp.RegexpILike: _regexpilike_sql, 799 exp.Rand: rename_func("RANDOM"), 800 exp.Select: transforms.preprocess( 801 [ 802 transforms.eliminate_distinct_on, 803 transforms.explode_to_unnest(), 804 transforms.eliminate_semi_and_anti_joins, 805 ] 806 ), 807 exp.SHA: rename_func("SHA1"), 808 exp.StarMap: rename_func("OBJECT_CONSTRUCT"), 809 exp.StartsWith: rename_func("STARTSWITH"), 810 exp.StrPosition: lambda self, e: self.func( 811 "POSITION", e.args.get("substr"), e.this, e.args.get("position") 812 ), 813 exp.StrToTime: lambda self, e: self.func("TO_TIMESTAMP", e.this, self.format_time(e)), 814 exp.Stuff: rename_func("INSERT"), 815 exp.TimeAdd: date_delta_sql("TIMEADD"), 816 exp.TimestampDiff: lambda self, e: self.func( 817 "TIMESTAMPDIFF", e.unit, e.expression, e.this 818 ), 819 exp.TimestampTrunc: timestamptrunc_sql, 820 exp.TimeStrToTime: timestrtotime_sql, 821 exp.TimeToStr: lambda self, e: self.func( 822 "TO_CHAR", exp.cast(e.this, exp.DataType.Type.TIMESTAMP), self.format_time(e) 823 ), 824 exp.TimeToUnix: lambda self, e: f"EXTRACT(epoch_second FROM {self.sql(e, 'this')})", 825 exp.ToArray: rename_func("TO_ARRAY"), 826 exp.ToChar: lambda self, e: self.function_fallback_sql(e), 827 exp.Trim: lambda self, e: self.func("TRIM", e.this, e.expression), 828 exp.TsOrDsAdd: date_delta_sql("DATEADD", cast=True), 829 exp.TsOrDsDiff: date_delta_sql("DATEDIFF"), 830 exp.TsOrDsToDate: lambda self, e: self.func( 831 "TRY_TO_DATE" if e.args.get("safe") else "TO_DATE", e.this, self.format_time(e) 832 ), 833 exp.UnixToTime: rename_func("TO_TIMESTAMP"), 834 exp.VarMap: lambda self, e: var_map_sql(self, e, "OBJECT_CONSTRUCT"), 835 exp.WeekOfYear: rename_func("WEEKOFYEAR"), 836 exp.Xor: rename_func("BOOLXOR"), 837 } 838 839 SUPPORTED_JSON_PATH_PARTS = { 840 exp.JSONPathKey, 841 exp.JSONPathRoot, 842 exp.JSONPathSubscript, 843 } 844 845 TYPE_MAPPING = { 846 **generator.Generator.TYPE_MAPPING, 847 exp.DataType.Type.NESTED: "OBJECT", 848 exp.DataType.Type.STRUCT: "OBJECT", 849 exp.DataType.Type.TIMESTAMP: "TIMESTAMPNTZ", 850 } 851 852 STAR_MAPPING = { 853 "except": "EXCLUDE", 854 "replace": "RENAME", 855 } 856 857 PROPERTIES_LOCATION = { 858 **generator.Generator.PROPERTIES_LOCATION, 859 exp.SetProperty: exp.Properties.Location.UNSUPPORTED, 860 exp.VolatileProperty: exp.Properties.Location.UNSUPPORTED, 861 } 862 863 UNSUPPORTED_VALUES_EXPRESSIONS = { 864 exp.Struct, 865 } 866 867 def values_sql(self, expression: exp.Values, values_as_table: bool = True) -> str: 868 if expression.find(*self.UNSUPPORTED_VALUES_EXPRESSIONS): 869 values_as_table = False 870 871 return super().values_sql(expression, values_as_table=values_as_table) 872 873 def datatype_sql(self, expression: exp.DataType) -> str: 874 expressions = expression.expressions 875 if ( 876 expressions 877 and expression.is_type(*exp.DataType.STRUCT_TYPES) 878 and any(isinstance(field_type, exp.DataType) for field_type in expressions) 879 ): 880 # The correct syntax is OBJECT [ (<key> <value_type [NOT NULL] [, ...]) ] 881 return "OBJECT" 882 883 return super().datatype_sql(expression) 884 885 def tonumber_sql(self, expression: exp.ToNumber) -> str: 886 return self.func( 887 "TO_NUMBER", 888 expression.this, 889 expression.args.get("format"), 890 expression.args.get("precision"), 891 expression.args.get("scale"), 892 ) 893 894 def timestampfromparts_sql(self, expression: exp.TimestampFromParts) -> str: 895 milli = expression.args.get("milli") 896 if milli is not None: 897 milli_to_nano = milli.pop() * exp.Literal.number(1000000) 898 expression.set("nano", milli_to_nano) 899 900 return rename_func("TIMESTAMP_FROM_PARTS")(self, expression) 901 902 def trycast_sql(self, expression: exp.TryCast) -> str: 903 value = expression.this 904 905 if value.type is None: 906 from sqlglot.optimizer.annotate_types import annotate_types 907 908 value = annotate_types(value) 909 910 if value.is_type(*exp.DataType.TEXT_TYPES, exp.DataType.Type.UNKNOWN): 911 return super().trycast_sql(expression) 912 913 # TRY_CAST only works for string values in Snowflake 914 return self.cast_sql(expression) 915 916 def log_sql(self, expression: exp.Log) -> str: 917 if not expression.expression: 918 return self.func("LN", expression.this) 919 920 return super().log_sql(expression) 921 922 def unnest_sql(self, expression: exp.Unnest) -> str: 923 unnest_alias = expression.args.get("alias") 924 offset = expression.args.get("offset") 925 926 columns = [ 927 exp.to_identifier("seq"), 928 exp.to_identifier("key"), 929 exp.to_identifier("path"), 930 offset.pop() if isinstance(offset, exp.Expression) else exp.to_identifier("index"), 931 seq_get(unnest_alias.columns if unnest_alias else [], 0) 932 or exp.to_identifier("value"), 933 exp.to_identifier("this"), 934 ] 935 936 if unnest_alias: 937 unnest_alias.set("columns", columns) 938 else: 939 unnest_alias = exp.TableAlias(this="_u", columns=columns) 940 941 explode = f"TABLE(FLATTEN(INPUT => {self.sql(expression.expressions[0])}))" 942 alias = self.sql(unnest_alias) 943 alias = f" AS {alias}" if alias else "" 944 return f"{explode}{alias}" 945 946 def show_sql(self, expression: exp.Show) -> str: 947 terse = "TERSE " if expression.args.get("terse") else "" 948 history = " HISTORY" if expression.args.get("history") else "" 949 like = self.sql(expression, "like") 950 like = f" LIKE {like}" if like else "" 951 952 scope = self.sql(expression, "scope") 953 scope = f" {scope}" if scope else "" 954 955 scope_kind = self.sql(expression, "scope_kind") 956 if scope_kind: 957 scope_kind = f" IN {scope_kind}" 958 959 starts_with = self.sql(expression, "starts_with") 960 if starts_with: 961 starts_with = f" STARTS WITH {starts_with}" 962 963 limit = self.sql(expression, "limit") 964 965 from_ = self.sql(expression, "from") 966 if from_: 967 from_ = f" FROM {from_}" 968 969 return f"SHOW {terse}{expression.name}{history}{like}{scope_kind}{scope}{starts_with}{limit}{from_}" 970 971 def regexpextract_sql(self, expression: exp.RegexpExtract) -> str: 972 # Other dialects don't support all of the following parameters, so we need to 973 # generate default values as necessary to ensure the transpilation is correct 974 group = expression.args.get("group") 975 parameters = expression.args.get("parameters") or (group and exp.Literal.string("c")) 976 occurrence = expression.args.get("occurrence") or (parameters and exp.Literal.number(1)) 977 position = expression.args.get("position") or (occurrence and exp.Literal.number(1)) 978 979 return self.func( 980 "REGEXP_SUBSTR", 981 expression.this, 982 expression.expression, 983 position, 984 occurrence, 985 parameters, 986 group, 987 ) 988 989 def except_op(self, expression: exp.Except) -> str: 990 if not expression.args.get("distinct"): 991 self.unsupported("EXCEPT with All is not supported in Snowflake") 992 return super().except_op(expression) 993 994 def intersect_op(self, expression: exp.Intersect) -> str: 995 if not expression.args.get("distinct"): 996 self.unsupported("INTERSECT with All is not supported in Snowflake") 997 return super().intersect_op(expression) 998 999 def describe_sql(self, expression: exp.Describe) -> str: 1000 # Default to table if kind is unknown 1001 kind_value = expression.args.get("kind") or "TABLE" 1002 kind = f" {kind_value}" if kind_value else "" 1003 this = f" {self.sql(expression, 'this')}" 1004 expressions = self.expressions(expression, flat=True) 1005 expressions = f" {expressions}" if expressions else "" 1006 return f"DESCRIBE{kind}{this}{expressions}" 1007 1008 def generatedasidentitycolumnconstraint_sql( 1009 self, expression: exp.GeneratedAsIdentityColumnConstraint 1010 ) -> str: 1011 start = expression.args.get("start") 1012 start = f" START {start}" if start else "" 1013 increment = expression.args.get("increment") 1014 increment = f" INCREMENT {increment}" if increment else "" 1015 return f"AUTOINCREMENT{start}{increment}" 1016 1017 def swaptable_sql(self, expression: exp.SwapTable) -> str: 1018 this = self.sql(expression, "this") 1019 return f"SWAP WITH {this}" 1020 1021 def with_properties(self, properties: exp.Properties) -> str: 1022 return self.properties(properties, wrapped=False, prefix=self.seg(""), sep=" ") 1023 1024 def cluster_sql(self, expression: exp.Cluster) -> str: 1025 return f"CLUSTER BY ({self.expressions(expression, flat=True)})" 1026 1027 def struct_sql(self, expression: exp.Struct) -> str: 1028 keys = [] 1029 values = [] 1030 1031 for i, e in enumerate(expression.expressions): 1032 if isinstance(e, exp.PropertyEQ): 1033 keys.append( 1034 exp.Literal.string(e.name) if isinstance(e.this, exp.Identifier) else e.this 1035 ) 1036 values.append(e.expression) 1037 else: 1038 keys.append(exp.Literal.string(f"_{i}")) 1039 values.append(e) 1040 1041 return self.func("OBJECT_CONSTRUCT", *flatten(zip(keys, values)))
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
873 def datatype_sql(self, expression: exp.DataType) -> str: 874 expressions = expression.expressions 875 if ( 876 expressions 877 and expression.is_type(*exp.DataType.STRUCT_TYPES) 878 and any(isinstance(field_type, exp.DataType) for field_type in expressions) 879 ): 880 # The correct syntax is OBJECT [ (<key> <value_type [NOT NULL] [, ...]) ] 881 return "OBJECT" 882 883 return super().datatype_sql(expression)
894 def timestampfromparts_sql(self, expression: exp.TimestampFromParts) -> str: 895 milli = expression.args.get("milli") 896 if milli is not None: 897 milli_to_nano = milli.pop() * exp.Literal.number(1000000) 898 expression.set("nano", milli_to_nano) 899 900 return rename_func("TIMESTAMP_FROM_PARTS")(self, expression)
902 def trycast_sql(self, expression: exp.TryCast) -> str: 903 value = expression.this 904 905 if value.type is None: 906 from sqlglot.optimizer.annotate_types import annotate_types 907 908 value = annotate_types(value) 909 910 if value.is_type(*exp.DataType.TEXT_TYPES, exp.DataType.Type.UNKNOWN): 911 return super().trycast_sql(expression) 912 913 # TRY_CAST only works for string values in Snowflake 914 return self.cast_sql(expression)
922 def unnest_sql(self, expression: exp.Unnest) -> str: 923 unnest_alias = expression.args.get("alias") 924 offset = expression.args.get("offset") 925 926 columns = [ 927 exp.to_identifier("seq"), 928 exp.to_identifier("key"), 929 exp.to_identifier("path"), 930 offset.pop() if isinstance(offset, exp.Expression) else exp.to_identifier("index"), 931 seq_get(unnest_alias.columns if unnest_alias else [], 0) 932 or exp.to_identifier("value"), 933 exp.to_identifier("this"), 934 ] 935 936 if unnest_alias: 937 unnest_alias.set("columns", columns) 938 else: 939 unnest_alias = exp.TableAlias(this="_u", columns=columns) 940 941 explode = f"TABLE(FLATTEN(INPUT => {self.sql(expression.expressions[0])}))" 942 alias = self.sql(unnest_alias) 943 alias = f" AS {alias}" if alias else "" 944 return f"{explode}{alias}"
946 def show_sql(self, expression: exp.Show) -> str: 947 terse = "TERSE " if expression.args.get("terse") else "" 948 history = " HISTORY" if expression.args.get("history") else "" 949 like = self.sql(expression, "like") 950 like = f" LIKE {like}" if like else "" 951 952 scope = self.sql(expression, "scope") 953 scope = f" {scope}" if scope else "" 954 955 scope_kind = self.sql(expression, "scope_kind") 956 if scope_kind: 957 scope_kind = f" IN {scope_kind}" 958 959 starts_with = self.sql(expression, "starts_with") 960 if starts_with: 961 starts_with = f" STARTS WITH {starts_with}" 962 963 limit = self.sql(expression, "limit") 964 965 from_ = self.sql(expression, "from") 966 if from_: 967 from_ = f" FROM {from_}" 968 969 return f"SHOW {terse}{expression.name}{history}{like}{scope_kind}{scope}{starts_with}{limit}{from_}"
971 def regexpextract_sql(self, expression: exp.RegexpExtract) -> str: 972 # Other dialects don't support all of the following parameters, so we need to 973 # generate default values as necessary to ensure the transpilation is correct 974 group = expression.args.get("group") 975 parameters = expression.args.get("parameters") or (group and exp.Literal.string("c")) 976 occurrence = expression.args.get("occurrence") or (parameters and exp.Literal.number(1)) 977 position = expression.args.get("position") or (occurrence and exp.Literal.number(1)) 978 979 return self.func( 980 "REGEXP_SUBSTR", 981 expression.this, 982 expression.expression, 983 position, 984 occurrence, 985 parameters, 986 group, 987 )
999 def describe_sql(self, expression: exp.Describe) -> str: 1000 # Default to table if kind is unknown 1001 kind_value = expression.args.get("kind") or "TABLE" 1002 kind = f" {kind_value}" if kind_value else "" 1003 this = f" {self.sql(expression, 'this')}" 1004 expressions = self.expressions(expression, flat=True) 1005 expressions = f" {expressions}" if expressions else "" 1006 return f"DESCRIBE{kind}{this}{expressions}"
1008 def generatedasidentitycolumnconstraint_sql( 1009 self, expression: exp.GeneratedAsIdentityColumnConstraint 1010 ) -> str: 1011 start = expression.args.get("start") 1012 start = f" START {start}" if start else "" 1013 increment = expression.args.get("increment") 1014 increment = f" INCREMENT {increment}" if increment else "" 1015 return f"AUTOINCREMENT{start}{increment}"
1027 def struct_sql(self, expression: exp.Struct) -> str: 1028 keys = [] 1029 values = [] 1030 1031 for i, e in enumerate(expression.expressions): 1032 if isinstance(e, exp.PropertyEQ): 1033 keys.append( 1034 exp.Literal.string(e.name) if isinstance(e.this, exp.Identifier) else e.this 1035 ) 1036 values.append(e.expression) 1037 else: 1038 keys.append(exp.Literal.string(f"_{i}")) 1039 values.append(e) 1040 1041 return self.func("OBJECT_CONSTRUCT", *flatten(zip(keys, values)))
Inherited Members
- sqlglot.generator.Generator
- Generator
- NULL_ORDERING_SUPPORTED
- IGNORE_NULLS_IN_FUNC
- LOCKING_READS_SUPPORTED
- EXPLICIT_UNION
- WRAP_DERIVED_VALUES
- CREATE_FUNCTION_RETURN_AS
- INTERVAL_ALLOWS_PLURAL_FORM
- LIMIT_FETCH
- RENAME_TABLE_WITH_DB
- GROUPINGS_SEP
- INDEX_ON
- QUERY_HINT_SEP
- IS_BOOL_ALLOWED
- DUPLICATE_KEY_UPDATE_WITH_SET
- LIMIT_IS_TOP
- RETURNING_END
- COLUMN_JOIN_MARKS_SUPPORTED
- EXTRACT_ALLOWS_QUOTES
- TZ_TO_WITH_TIME_ZONE
- NVL2_SUPPORTED
- VALUES_AS_TABLE
- ALTER_TABLE_INCLUDE_COLUMN_KEYWORD
- UNNEST_WITH_ORDINALITY
- SEMI_ANTI_JOIN_WITH_SIDE
- COMPUTED_COLUMN_WITH_TYPE
- TABLESAMPLE_REQUIRES_PARENS
- TABLESAMPLE_SIZE_IS_ROWS
- TABLESAMPLE_KEYWORDS
- TABLESAMPLE_WITH_METHOD
- TABLESAMPLE_SEED_KEYWORD
- DATA_TYPE_SPECIFIERS_ALLOWED
- ENSURE_BOOLS
- CTE_RECURSIVE_KEYWORD_REQUIRED
- SUPPORTS_SINGLE_ARG_CONCAT
- LAST_DAY_SUPPORTS_DATE_PART
- SUPPORTS_TABLE_ALIAS_COLUMNS
- UNPIVOT_ALIASES_ARE_IDENTIFIERS
- SUPPORTS_SELECT_INTO
- SUPPORTS_UNLOGGED_TABLES
- SUPPORTS_CREATE_TABLE_LIKE
- LIKE_PROPERTY_INSIDE_SCHEMA
- MULTI_ARG_DISTINCT
- JSON_TYPE_REQUIRED_FOR_EXTRACTION
- JSON_PATH_BRACKETED_KEY_SUPPORTED
- JSON_PATH_SINGLE_QUOTE_ESCAPE
- CAN_IMPLEMENT_ARRAY_ANY
- SUPPORTS_TO_NUMBER
- OUTER_UNION_MODIFIERS
- TIME_PART_SINGULARS
- TOKEN_MAPPING
- NAMED_PLACEHOLDER_TOKEN
- RESERVED_KEYWORDS
- 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
- generatedasrowcolumnconstraint_sql
- periodforsystemtimeconstraint_sql
- notnullcolumnconstraint_sql
- transformcolumnconstraint_sql
- primarykeycolumnconstraint_sql
- uniquecolumnconstraint_sql
- createable_sql
- create_sql
- sequenceproperties_sql
- clone_sql
- heredoc_sql
- prepend_ctes
- with_sql
- cte_sql
- tablealias_sql
- bitstring_sql
- hexstring_sql
- bytestring_sql
- unicodestring_sql
- rawstring_sql
- datatypeparam_sql
- directory_sql
- delete_sql
- drop_sql
- except_sql
- fetch_sql
- filter_sql
- hint_sql
- indexparameters_sql
- index_sql
- identifier_sql
- inputoutputformat_sql
- national_sql
- partition_sql
- properties_sql
- root_properties
- 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
- intersect_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
- var_sql
- into_sql
- from_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
- distribute_sql
- sort_sql
- ordered_sql
- matchrecognizemeasure_sql
- matchrecognize_sql
- query_modifiers
- queryoption_sql
- offset_limit_modifiers
- after_limit_modifiers
- select_sql
- schema_sql
- schema_columns_sql
- star_sql
- parameter_sql
- sessionparameter_sql
- placeholder_sql
- subquery_sql
- qualify_sql
- set_operations
- union_sql
- union_op
- prewhere_sql
- where_sql
- window_sql
- partition_by_sql
- windowspec_sql
- withingroup_sql
- between_sql
- bracket_offset_expressions
- bracket_sql
- 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
- interval_sql
- 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
- currenttimestamp_sql
- collate_sql
- command_sql
- comment_sql
- mergetreettlaction_sql
- mergetreettl_sql
- transaction_sql
- commit_sql
- rollback_sql
- altercolumn_sql
- renametable_sql
- renamecolumn_sql
- altertable_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
- 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
- dictproperty_sql
- dictrange_sql
- dictsubproperty_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
- operator_sql
- toarray_sql
- tsordstotime_sql
- tsordstotimestamp_sql
- tsordstodate_sql
- unixdate_sql
- lastday_sql
- dateadd_sql
- arrayany_sql
- generateseries_sql
- partitionrange_sql
- truncatetable_sql
- convert_sql