sqlglot.dialects.snowflake
1from __future__ import annotations 2 3import typing as t 4 5from sqlglot import exp, generator, jsonpath, parser, tokens, transforms 6from sqlglot.dialects.dialect import ( 7 Dialect, 8 NormalizationStrategy, 9 build_timetostr_or_tochar, 10 binary_from_function, 11 build_default_decimal_type, 12 build_timestamp_from_parts, 13 date_delta_sql, 14 date_trunc_to_time, 15 datestrtodate_sql, 16 build_formatted_time, 17 if_sql, 18 inline_array_sql, 19 max_or_greatest, 20 min_or_least, 21 rename_func, 22 timestamptrunc_sql, 23 timestrtotime_sql, 24 var_map_sql, 25 map_date_part, 26 no_timestamp_sql, 27 strposition_sql, 28 timestampdiff_sql, 29 no_make_interval_sql, 30 groupconcat_sql, 31) 32from sqlglot.generator import unsupported_args 33from sqlglot.helper import flatten, is_float, is_int, seq_get 34from sqlglot.tokens import TokenType 35 36if t.TYPE_CHECKING: 37 from sqlglot._typing import E, B 38 39 40# from https://docs.snowflake.com/en/sql-reference/functions/to_timestamp.html 41def _build_datetime( 42 name: str, kind: exp.DataType.Type, safe: bool = False 43) -> t.Callable[[t.List], exp.Func]: 44 def _builder(args: t.List) -> exp.Func: 45 value = seq_get(args, 0) 46 scale_or_fmt = seq_get(args, 1) 47 48 int_value = value is not None and is_int(value.name) 49 int_scale_or_fmt = scale_or_fmt is not None and scale_or_fmt.is_int 50 51 if isinstance(value, exp.Literal) or (value and scale_or_fmt): 52 # Converts calls like `TO_TIME('01:02:03')` into casts 53 if len(args) == 1 and value.is_string and not int_value: 54 return ( 55 exp.TryCast(this=value, to=exp.DataType.build(kind)) 56 if safe 57 else exp.cast(value, kind) 58 ) 59 60 # Handles `TO_TIMESTAMP(str, fmt)` and `TO_TIMESTAMP(num, scale)` as special 61 # cases so we can transpile them, since they're relatively common 62 if kind == exp.DataType.Type.TIMESTAMP: 63 if not safe and (int_value or int_scale_or_fmt): 64 # TRY_TO_TIMESTAMP('integer') is not parsed into exp.UnixToTime as 65 # it's not easily transpilable 66 return exp.UnixToTime(this=value, scale=scale_or_fmt) 67 if not int_scale_or_fmt and not is_float(value.name): 68 expr = build_formatted_time(exp.StrToTime, "snowflake")(args) 69 expr.set("safe", safe) 70 return expr 71 72 if kind in (exp.DataType.Type.DATE, exp.DataType.Type.TIME) and not int_value: 73 klass = exp.TsOrDsToDate if kind == exp.DataType.Type.DATE else exp.TsOrDsToTime 74 formatted_exp = build_formatted_time(klass, "snowflake")(args) 75 formatted_exp.set("safe", safe) 76 return formatted_exp 77 78 return exp.Anonymous(this=name, expressions=args) 79 80 return _builder 81 82 83def _build_object_construct(args: t.List) -> t.Union[exp.StarMap, exp.Struct]: 84 expression = parser.build_var_map(args) 85 86 if isinstance(expression, exp.StarMap): 87 return expression 88 89 return exp.Struct( 90 expressions=[ 91 exp.PropertyEQ(this=k, expression=v) for k, v in zip(expression.keys, expression.values) 92 ] 93 ) 94 95 96def _build_datediff(args: t.List) -> exp.DateDiff: 97 return exp.DateDiff( 98 this=seq_get(args, 2), expression=seq_get(args, 1), unit=map_date_part(seq_get(args, 0)) 99 ) 100 101 102def _build_date_time_add(expr_type: t.Type[E]) -> t.Callable[[t.List], E]: 103 def _builder(args: t.List) -> E: 104 return expr_type( 105 this=seq_get(args, 2), 106 expression=seq_get(args, 1), 107 unit=map_date_part(seq_get(args, 0)), 108 ) 109 110 return _builder 111 112 113def _build_bitwise(expr_type: t.Type[B], name: str) -> t.Callable[[t.List], B | exp.Anonymous]: 114 def _builder(args: t.List) -> B | exp.Anonymous: 115 if len(args) == 3: 116 return exp.Anonymous(this=name, expressions=args) 117 118 return binary_from_function(expr_type)(args) 119 120 return _builder 121 122 123# https://docs.snowflake.com/en/sql-reference/functions/div0 124def _build_if_from_div0(args: t.List) -> exp.If: 125 lhs = exp._wrap(seq_get(args, 0), exp.Binary) 126 rhs = exp._wrap(seq_get(args, 1), exp.Binary) 127 128 cond = exp.EQ(this=rhs, expression=exp.Literal.number(0)).and_( 129 exp.Is(this=lhs, expression=exp.null()).not_() 130 ) 131 true = exp.Literal.number(0) 132 false = exp.Div(this=lhs, expression=rhs) 133 return exp.If(this=cond, true=true, false=false) 134 135 136# https://docs.snowflake.com/en/sql-reference/functions/zeroifnull 137def _build_if_from_zeroifnull(args: t.List) -> exp.If: 138 cond = exp.Is(this=seq_get(args, 0), expression=exp.Null()) 139 return exp.If(this=cond, true=exp.Literal.number(0), false=seq_get(args, 0)) 140 141 142# https://docs.snowflake.com/en/sql-reference/functions/zeroifnull 143def _build_if_from_nullifzero(args: t.List) -> exp.If: 144 cond = exp.EQ(this=seq_get(args, 0), expression=exp.Literal.number(0)) 145 return exp.If(this=cond, true=exp.Null(), false=seq_get(args, 0)) 146 147 148def _regexpilike_sql(self: Snowflake.Generator, expression: exp.RegexpILike) -> str: 149 flag = expression.text("flag") 150 151 if "i" not in flag: 152 flag += "i" 153 154 return self.func( 155 "REGEXP_LIKE", expression.this, expression.expression, exp.Literal.string(flag) 156 ) 157 158 159def _build_regexp_replace(args: t.List) -> exp.RegexpReplace: 160 regexp_replace = exp.RegexpReplace.from_arg_list(args) 161 162 if not regexp_replace.args.get("replacement"): 163 regexp_replace.set("replacement", exp.Literal.string("")) 164 165 return regexp_replace 166 167 168def _show_parser(*args: t.Any, **kwargs: t.Any) -> t.Callable[[Snowflake.Parser], exp.Show]: 169 def _parse(self: Snowflake.Parser) -> exp.Show: 170 return self._parse_show_snowflake(*args, **kwargs) 171 172 return _parse 173 174 175def _date_trunc_to_time(args: t.List) -> exp.DateTrunc | exp.TimestampTrunc: 176 trunc = date_trunc_to_time(args) 177 trunc.set("unit", map_date_part(trunc.args["unit"])) 178 return trunc 179 180 181def _unqualify_pivot_columns(expression: exp.Expression) -> exp.Expression: 182 """ 183 Snowflake doesn't allow columns referenced in UNPIVOT to be qualified, 184 so we need to unqualify them. Same goes for ANY ORDER BY <column>. 185 186 Example: 187 >>> from sqlglot import parse_one 188 >>> expr = parse_one("SELECT * FROM m_sales UNPIVOT(sales FOR month IN (m_sales.jan, feb, mar, april))") 189 >>> print(_unqualify_pivot_columns(expr).sql(dialect="snowflake")) 190 SELECT * FROM m_sales UNPIVOT(sales FOR month IN (jan, feb, mar, april)) 191 """ 192 if isinstance(expression, exp.Pivot): 193 if expression.unpivot: 194 expression = transforms.unqualify_columns(expression) 195 else: 196 field = expression.args.get("field") 197 field_expr = seq_get(field.expressions if field else [], 0) 198 199 if isinstance(field_expr, exp.PivotAny): 200 unqualified_field_expr = transforms.unqualify_columns(field_expr) 201 t.cast(exp.Expression, field).set("expressions", unqualified_field_expr, 0) 202 203 return expression 204 205 206def _flatten_structured_types_unless_iceberg(expression: exp.Expression) -> exp.Expression: 207 assert isinstance(expression, exp.Create) 208 209 def _flatten_structured_type(expression: exp.DataType) -> exp.DataType: 210 if expression.this in exp.DataType.NESTED_TYPES: 211 expression.set("expressions", None) 212 return expression 213 214 props = expression.args.get("properties") 215 if isinstance(expression.this, exp.Schema) and not (props and props.find(exp.IcebergProperty)): 216 for schema_expression in expression.this.expressions: 217 if isinstance(schema_expression, exp.ColumnDef): 218 column_type = schema_expression.kind 219 if isinstance(column_type, exp.DataType): 220 column_type.transform(_flatten_structured_type, copy=False) 221 222 return expression 223 224 225def _unnest_generate_date_array(unnest: exp.Unnest) -> None: 226 generate_date_array = unnest.expressions[0] 227 start = generate_date_array.args.get("start") 228 end = generate_date_array.args.get("end") 229 step = generate_date_array.args.get("step") 230 231 if not start or not end or not isinstance(step, exp.Interval) or step.name != "1": 232 return 233 234 unit = step.args.get("unit") 235 236 unnest_alias = unnest.args.get("alias") 237 if unnest_alias: 238 unnest_alias = unnest_alias.copy() 239 sequence_value_name = seq_get(unnest_alias.columns, 0) or "value" 240 else: 241 sequence_value_name = "value" 242 243 # We'll add the next sequence value to the starting date and project the result 244 date_add = _build_date_time_add(exp.DateAdd)( 245 [unit, exp.cast(sequence_value_name, "int"), exp.cast(start, "date")] 246 ).as_(sequence_value_name) 247 248 # We use DATEDIFF to compute the number of sequence values needed 249 number_sequence = Snowflake.Parser.FUNCTIONS["ARRAY_GENERATE_RANGE"]( 250 [exp.Literal.number(0), _build_datediff([unit, start, end]) + 1] 251 ) 252 253 unnest.set("expressions", [number_sequence]) 254 unnest.replace(exp.select(date_add).from_(unnest.copy()).subquery(unnest_alias)) 255 256 257def _transform_generate_date_array(expression: exp.Expression) -> exp.Expression: 258 if isinstance(expression, exp.Select): 259 for generate_date_array in expression.find_all(exp.GenerateDateArray): 260 parent = generate_date_array.parent 261 262 # If GENERATE_DATE_ARRAY is used directly as an array (e.g passed into ARRAY_LENGTH), the transformed Snowflake 263 # query is the following (it'll be unnested properly on the next iteration due to copy): 264 # SELECT ref(GENERATE_DATE_ARRAY(...)) -> SELECT ref((SELECT ARRAY_AGG(*) FROM UNNEST(GENERATE_DATE_ARRAY(...)))) 265 if not isinstance(parent, exp.Unnest): 266 unnest = exp.Unnest(expressions=[generate_date_array.copy()]) 267 generate_date_array.replace( 268 exp.select(exp.ArrayAgg(this=exp.Star())).from_(unnest).subquery() 269 ) 270 271 if ( 272 isinstance(parent, exp.Unnest) 273 and isinstance(parent.parent, (exp.From, exp.Join)) 274 and len(parent.expressions) == 1 275 ): 276 _unnest_generate_date_array(parent) 277 278 return expression 279 280 281def _build_regexp_extract(expr_type: t.Type[E]) -> t.Callable[[t.List], E]: 282 def _builder(args: t.List) -> E: 283 return expr_type( 284 this=seq_get(args, 0), 285 expression=seq_get(args, 1), 286 position=seq_get(args, 2), 287 occurrence=seq_get(args, 3), 288 parameters=seq_get(args, 4), 289 group=seq_get(args, 5) or exp.Literal.number(0), 290 ) 291 292 return _builder 293 294 295def _regexpextract_sql(self, expression: exp.RegexpExtract | exp.RegexpExtractAll) -> str: 296 # Other dialects don't support all of the following parameters, so we need to 297 # generate default values as necessary to ensure the transpilation is correct 298 group = expression.args.get("group") 299 300 # To avoid generating all these default values, we set group to None if 301 # it's 0 (also default value) which doesn't trigger the following chain 302 if group and group.name == "0": 303 group = None 304 305 parameters = expression.args.get("parameters") or (group and exp.Literal.string("c")) 306 occurrence = expression.args.get("occurrence") or (parameters and exp.Literal.number(1)) 307 position = expression.args.get("position") or (occurrence and exp.Literal.number(1)) 308 309 return self.func( 310 "REGEXP_SUBSTR" if isinstance(expression, exp.RegexpExtract) else "REGEXP_EXTRACT_ALL", 311 expression.this, 312 expression.expression, 313 position, 314 occurrence, 315 parameters, 316 group, 317 ) 318 319 320def _json_extract_value_array_sql( 321 self: Snowflake.Generator, expression: exp.JSONValueArray | exp.JSONExtractArray 322) -> str: 323 json_extract = exp.JSONExtract(this=expression.this, expression=expression.expression) 324 ident = exp.to_identifier("x") 325 326 if isinstance(expression, exp.JSONValueArray): 327 this: exp.Expression = exp.cast(ident, to=exp.DataType.Type.VARCHAR) 328 else: 329 this = exp.ParseJSON(this=f"TO_JSON({ident})") 330 331 transform_lambda = exp.Lambda(expressions=[ident], this=this) 332 333 return self.func("TRANSFORM", json_extract, transform_lambda) 334 335 336class Snowflake(Dialect): 337 # https://docs.snowflake.com/en/sql-reference/identifiers-syntax 338 NORMALIZATION_STRATEGY = NormalizationStrategy.UPPERCASE 339 NULL_ORDERING = "nulls_are_large" 340 TIME_FORMAT = "'YYYY-MM-DD HH24:MI:SS'" 341 SUPPORTS_USER_DEFINED_TYPES = False 342 SUPPORTS_SEMI_ANTI_JOIN = False 343 PREFER_CTE_ALIAS_COLUMN = True 344 TABLESAMPLE_SIZE_IS_PERCENT = True 345 COPY_PARAMS_ARE_CSV = False 346 ARRAY_AGG_INCLUDES_NULLS = None 347 348 TIME_MAPPING = { 349 "YYYY": "%Y", 350 "yyyy": "%Y", 351 "YY": "%y", 352 "yy": "%y", 353 "MMMM": "%B", 354 "mmmm": "%B", 355 "MON": "%b", 356 "mon": "%b", 357 "MM": "%m", 358 "mm": "%m", 359 "DD": "%d", 360 "dd": "%-d", 361 "DY": "%a", 362 "dy": "%w", 363 "HH24": "%H", 364 "hh24": "%H", 365 "HH12": "%I", 366 "hh12": "%I", 367 "MI": "%M", 368 "mi": "%M", 369 "SS": "%S", 370 "ss": "%S", 371 "FF6": "%f", 372 "ff6": "%f", 373 } 374 375 def quote_identifier(self, expression: E, identify: bool = True) -> E: 376 # This disables quoting DUAL in SELECT ... FROM DUAL, because Snowflake treats an 377 # unquoted DUAL keyword in a special way and does not map it to a user-defined table 378 if ( 379 isinstance(expression, exp.Identifier) 380 and isinstance(expression.parent, exp.Table) 381 and expression.name.lower() == "dual" 382 ): 383 return expression # type: ignore 384 385 return super().quote_identifier(expression, identify=identify) 386 387 class JSONPathTokenizer(jsonpath.JSONPathTokenizer): 388 SINGLE_TOKENS = jsonpath.JSONPathTokenizer.SINGLE_TOKENS.copy() 389 SINGLE_TOKENS.pop("$") 390 391 class Parser(parser.Parser): 392 IDENTIFY_PIVOT_STRINGS = True 393 DEFAULT_SAMPLING_METHOD = "BERNOULLI" 394 COLON_IS_VARIANT_EXTRACT = True 395 396 ID_VAR_TOKENS = { 397 *parser.Parser.ID_VAR_TOKENS, 398 TokenType.MATCH_CONDITION, 399 } 400 401 TABLE_ALIAS_TOKENS = parser.Parser.TABLE_ALIAS_TOKENS | {TokenType.WINDOW} 402 TABLE_ALIAS_TOKENS.discard(TokenType.MATCH_CONDITION) 403 404 FUNCTIONS = { 405 **parser.Parser.FUNCTIONS, 406 "APPROX_PERCENTILE": exp.ApproxQuantile.from_arg_list, 407 "ARRAY_CONSTRUCT": lambda args: exp.Array(expressions=args), 408 "ARRAY_CONTAINS": lambda args: exp.ArrayContains( 409 this=seq_get(args, 1), expression=seq_get(args, 0) 410 ), 411 "ARRAY_GENERATE_RANGE": lambda args: exp.GenerateSeries( 412 # ARRAY_GENERATE_RANGE has an exlusive end; we normalize it to be inclusive 413 start=seq_get(args, 0), 414 end=exp.Sub(this=seq_get(args, 1), expression=exp.Literal.number(1)), 415 step=seq_get(args, 2), 416 ), 417 "BITXOR": _build_bitwise(exp.BitwiseXor, "BITXOR"), 418 "BIT_XOR": _build_bitwise(exp.BitwiseXor, "BITXOR"), 419 "BITOR": _build_bitwise(exp.BitwiseOr, "BITOR"), 420 "BIT_OR": _build_bitwise(exp.BitwiseOr, "BITOR"), 421 "BITSHIFTLEFT": _build_bitwise(exp.BitwiseLeftShift, "BITSHIFTLEFT"), 422 "BIT_SHIFTLEFT": _build_bitwise(exp.BitwiseLeftShift, "BIT_SHIFTLEFT"), 423 "BITSHIFTRIGHT": _build_bitwise(exp.BitwiseRightShift, "BITSHIFTRIGHT"), 424 "BIT_SHIFTRIGHT": _build_bitwise(exp.BitwiseRightShift, "BIT_SHIFTRIGHT"), 425 "BOOLXOR": _build_bitwise(exp.Xor, "BOOLXOR"), 426 "DATE": _build_datetime("DATE", exp.DataType.Type.DATE), 427 "DATE_TRUNC": _date_trunc_to_time, 428 "DATEADD": _build_date_time_add(exp.DateAdd), 429 "DATEDIFF": _build_datediff, 430 "DIV0": _build_if_from_div0, 431 "EDITDISTANCE": lambda args: exp.Levenshtein( 432 this=seq_get(args, 0), expression=seq_get(args, 1), max_dist=seq_get(args, 2) 433 ), 434 "FLATTEN": exp.Explode.from_arg_list, 435 "GET_PATH": lambda args, dialect: exp.JSONExtract( 436 this=seq_get(args, 0), expression=dialect.to_json_path(seq_get(args, 1)) 437 ), 438 "HEX_DECODE_BINARY": exp.Unhex.from_arg_list, 439 "IFF": exp.If.from_arg_list, 440 "LAST_DAY": lambda args: exp.LastDay( 441 this=seq_get(args, 0), unit=map_date_part(seq_get(args, 1)) 442 ), 443 "LEN": lambda args: exp.Length(this=seq_get(args, 0), binary=True), 444 "LENGTH": lambda args: exp.Length(this=seq_get(args, 0), binary=True), 445 "NULLIFZERO": _build_if_from_nullifzero, 446 "OBJECT_CONSTRUCT": _build_object_construct, 447 "REGEXP_EXTRACT_ALL": _build_regexp_extract(exp.RegexpExtractAll), 448 "REGEXP_REPLACE": _build_regexp_replace, 449 "REGEXP_SUBSTR": _build_regexp_extract(exp.RegexpExtract), 450 "REGEXP_SUBSTR_ALL": _build_regexp_extract(exp.RegexpExtractAll), 451 "RLIKE": exp.RegexpLike.from_arg_list, 452 "SQUARE": lambda args: exp.Pow(this=seq_get(args, 0), expression=exp.Literal.number(2)), 453 "TABLE": lambda args: exp.TableFromRows(this=seq_get(args, 0)), 454 "TIMEADD": _build_date_time_add(exp.TimeAdd), 455 "TIMEDIFF": _build_datediff, 456 "TIMESTAMPADD": _build_date_time_add(exp.DateAdd), 457 "TIMESTAMPDIFF": _build_datediff, 458 "TIMESTAMPFROMPARTS": build_timestamp_from_parts, 459 "TIMESTAMP_FROM_PARTS": build_timestamp_from_parts, 460 "TIMESTAMPNTZFROMPARTS": build_timestamp_from_parts, 461 "TIMESTAMP_NTZ_FROM_PARTS": build_timestamp_from_parts, 462 "TRY_PARSE_JSON": lambda args: exp.ParseJSON(this=seq_get(args, 0), safe=True), 463 "TRY_TO_DATE": _build_datetime("TRY_TO_DATE", exp.DataType.Type.DATE, safe=True), 464 "TRY_TO_TIME": _build_datetime("TRY_TO_TIME", exp.DataType.Type.TIME, safe=True), 465 "TRY_TO_TIMESTAMP": _build_datetime( 466 "TRY_TO_TIMESTAMP", exp.DataType.Type.TIMESTAMP, safe=True 467 ), 468 "TO_CHAR": build_timetostr_or_tochar, 469 "TO_DATE": _build_datetime("TO_DATE", exp.DataType.Type.DATE), 470 "TO_NUMBER": lambda args: exp.ToNumber( 471 this=seq_get(args, 0), 472 format=seq_get(args, 1), 473 precision=seq_get(args, 2), 474 scale=seq_get(args, 3), 475 ), 476 "TO_TIME": _build_datetime("TO_TIME", exp.DataType.Type.TIME), 477 "TO_TIMESTAMP": _build_datetime("TO_TIMESTAMP", exp.DataType.Type.TIMESTAMP), 478 "TO_TIMESTAMP_LTZ": _build_datetime("TO_TIMESTAMP_LTZ", exp.DataType.Type.TIMESTAMPLTZ), 479 "TO_TIMESTAMP_NTZ": _build_datetime("TO_TIMESTAMP_NTZ", exp.DataType.Type.TIMESTAMP), 480 "TO_TIMESTAMP_TZ": _build_datetime("TO_TIMESTAMP_TZ", exp.DataType.Type.TIMESTAMPTZ), 481 "TO_VARCHAR": exp.ToChar.from_arg_list, 482 "ZEROIFNULL": _build_if_from_zeroifnull, 483 } 484 485 FUNCTION_PARSERS = { 486 **parser.Parser.FUNCTION_PARSERS, 487 "DATE_PART": lambda self: self._parse_date_part(), 488 "OBJECT_CONSTRUCT_KEEP_NULL": lambda self: self._parse_json_object(), 489 "LISTAGG": lambda self: self._parse_string_agg(), 490 } 491 FUNCTION_PARSERS.pop("TRIM") 492 493 TIMESTAMPS = parser.Parser.TIMESTAMPS - {TokenType.TIME} 494 495 RANGE_PARSERS = { 496 **parser.Parser.RANGE_PARSERS, 497 TokenType.LIKE_ANY: parser.binary_range_parser(exp.LikeAny), 498 TokenType.ILIKE_ANY: parser.binary_range_parser(exp.ILikeAny), 499 } 500 501 ALTER_PARSERS = { 502 **parser.Parser.ALTER_PARSERS, 503 "UNSET": lambda self: self.expression( 504 exp.Set, 505 tag=self._match_text_seq("TAG"), 506 expressions=self._parse_csv(self._parse_id_var), 507 unset=True, 508 ), 509 } 510 511 STATEMENT_PARSERS = { 512 **parser.Parser.STATEMENT_PARSERS, 513 TokenType.PUT: lambda self: self._parse_put(), 514 TokenType.SHOW: lambda self: self._parse_show(), 515 } 516 517 PROPERTY_PARSERS = { 518 **parser.Parser.PROPERTY_PARSERS, 519 "LOCATION": lambda self: self._parse_location_property(), 520 "TAG": lambda self: self._parse_tag(), 521 } 522 523 TYPE_CONVERTERS = { 524 # https://docs.snowflake.com/en/sql-reference/data-types-numeric#number 525 exp.DataType.Type.DECIMAL: build_default_decimal_type(precision=38, scale=0), 526 } 527 528 SHOW_PARSERS = { 529 "DATABASES": _show_parser("DATABASES"), 530 "TERSE DATABASES": _show_parser("DATABASES"), 531 "SCHEMAS": _show_parser("SCHEMAS"), 532 "TERSE SCHEMAS": _show_parser("SCHEMAS"), 533 "OBJECTS": _show_parser("OBJECTS"), 534 "TERSE OBJECTS": _show_parser("OBJECTS"), 535 "TABLES": _show_parser("TABLES"), 536 "TERSE TABLES": _show_parser("TABLES"), 537 "VIEWS": _show_parser("VIEWS"), 538 "TERSE VIEWS": _show_parser("VIEWS"), 539 "PRIMARY KEYS": _show_parser("PRIMARY KEYS"), 540 "TERSE PRIMARY KEYS": _show_parser("PRIMARY KEYS"), 541 "IMPORTED KEYS": _show_parser("IMPORTED KEYS"), 542 "TERSE IMPORTED KEYS": _show_parser("IMPORTED KEYS"), 543 "UNIQUE KEYS": _show_parser("UNIQUE KEYS"), 544 "TERSE UNIQUE KEYS": _show_parser("UNIQUE KEYS"), 545 "SEQUENCES": _show_parser("SEQUENCES"), 546 "TERSE SEQUENCES": _show_parser("SEQUENCES"), 547 "COLUMNS": _show_parser("COLUMNS"), 548 "USERS": _show_parser("USERS"), 549 "TERSE USERS": _show_parser("USERS"), 550 "FUNCTIONS": _show_parser("FUNCTIONS"), 551 "PROCEDURES": _show_parser("PROCEDURES"), 552 "WAREHOUSES": _show_parser("WAREHOUSES"), 553 } 554 555 CONSTRAINT_PARSERS = { 556 **parser.Parser.CONSTRAINT_PARSERS, 557 "WITH": lambda self: self._parse_with_constraint(), 558 "MASKING": lambda self: self._parse_with_constraint(), 559 "PROJECTION": lambda self: self._parse_with_constraint(), 560 "TAG": lambda self: self._parse_with_constraint(), 561 } 562 563 STAGED_FILE_SINGLE_TOKENS = { 564 TokenType.DOT, 565 TokenType.MOD, 566 TokenType.SLASH, 567 } 568 569 FLATTEN_COLUMNS = ["SEQ", "KEY", "PATH", "INDEX", "VALUE", "THIS"] 570 571 SCHEMA_KINDS = {"OBJECTS", "TABLES", "VIEWS", "SEQUENCES", "UNIQUE KEYS", "IMPORTED KEYS"} 572 573 NON_TABLE_CREATABLES = {"STORAGE INTEGRATION", "TAG", "WAREHOUSE", "STREAMLIT"} 574 575 LAMBDAS = { 576 **parser.Parser.LAMBDAS, 577 TokenType.ARROW: lambda self, expressions: self.expression( 578 exp.Lambda, 579 this=self._replace_lambda( 580 self._parse_assignment(), 581 expressions, 582 ), 583 expressions=[e.this if isinstance(e, exp.Cast) else e for e in expressions], 584 ), 585 } 586 587 def _parse_use(self) -> exp.Use: 588 if self._match_text_seq("SECONDARY", "ROLES"): 589 this = self._match_texts(("ALL", "NONE")) and exp.var(self._prev.text.upper()) 590 roles = None if this else self._parse_csv(lambda: self._parse_table(schema=False)) 591 return self.expression( 592 exp.Use, kind="SECONDARY ROLES", this=this, expressions=roles 593 ) 594 595 return super()._parse_use() 596 597 def _negate_range( 598 self, this: t.Optional[exp.Expression] = None 599 ) -> t.Optional[exp.Expression]: 600 if not this: 601 return this 602 603 query = this.args.get("query") 604 if isinstance(this, exp.In) and isinstance(query, exp.Query): 605 # Snowflake treats `value NOT IN (subquery)` as `VALUE <> ALL (subquery)`, so 606 # we do this conversion here to avoid parsing it into `NOT value IN (subquery)` 607 # which can produce different results (most likely a SnowFlake bug). 608 # 609 # https://docs.snowflake.com/en/sql-reference/functions/in 610 # Context: https://github.com/tobymao/sqlglot/issues/3890 611 return self.expression( 612 exp.NEQ, this=this.this, expression=exp.All(this=query.unnest()) 613 ) 614 615 return self.expression(exp.Not, this=this) 616 617 def _parse_tag(self) -> exp.Tags: 618 return self.expression( 619 exp.Tags, 620 expressions=self._parse_wrapped_csv(self._parse_property), 621 ) 622 623 def _parse_with_constraint(self) -> t.Optional[exp.Expression]: 624 if self._prev.token_type != TokenType.WITH: 625 self._retreat(self._index - 1) 626 627 if self._match_text_seq("MASKING", "POLICY"): 628 policy = self._parse_column() 629 return self.expression( 630 exp.MaskingPolicyColumnConstraint, 631 this=policy.to_dot() if isinstance(policy, exp.Column) else policy, 632 expressions=self._match(TokenType.USING) 633 and self._parse_wrapped_csv(self._parse_id_var), 634 ) 635 if self._match_text_seq("PROJECTION", "POLICY"): 636 policy = self._parse_column() 637 return self.expression( 638 exp.ProjectionPolicyColumnConstraint, 639 this=policy.to_dot() if isinstance(policy, exp.Column) else policy, 640 ) 641 if self._match(TokenType.TAG): 642 return self._parse_tag() 643 644 return None 645 646 def _parse_with_property(self) -> t.Optional[exp.Expression] | t.List[exp.Expression]: 647 if self._match(TokenType.TAG): 648 return self._parse_tag() 649 650 return super()._parse_with_property() 651 652 def _parse_create(self) -> exp.Create | exp.Command: 653 expression = super()._parse_create() 654 if isinstance(expression, exp.Create) and expression.kind in self.NON_TABLE_CREATABLES: 655 # Replace the Table node with the enclosed Identifier 656 expression.this.replace(expression.this.this) 657 658 return expression 659 660 # https://docs.snowflake.com/en/sql-reference/functions/date_part.html 661 # https://docs.snowflake.com/en/sql-reference/functions-date-time.html#label-supported-date-time-parts 662 def _parse_date_part(self: Snowflake.Parser) -> t.Optional[exp.Expression]: 663 this = self._parse_var() or self._parse_type() 664 665 if not this: 666 return None 667 668 self._match(TokenType.COMMA) 669 expression = self._parse_bitwise() 670 this = map_date_part(this) 671 name = this.name.upper() 672 673 if name.startswith("EPOCH"): 674 if name == "EPOCH_MILLISECOND": 675 scale = 10**3 676 elif name == "EPOCH_MICROSECOND": 677 scale = 10**6 678 elif name == "EPOCH_NANOSECOND": 679 scale = 10**9 680 else: 681 scale = None 682 683 ts = self.expression(exp.Cast, this=expression, to=exp.DataType.build("TIMESTAMP")) 684 to_unix: exp.Expression = self.expression(exp.TimeToUnix, this=ts) 685 686 if scale: 687 to_unix = exp.Mul(this=to_unix, expression=exp.Literal.number(scale)) 688 689 return to_unix 690 691 return self.expression(exp.Extract, this=this, expression=expression) 692 693 def _parse_bracket_key_value(self, is_map: bool = False) -> t.Optional[exp.Expression]: 694 if is_map: 695 # Keys are strings in Snowflake's objects, see also: 696 # - https://docs.snowflake.com/en/sql-reference/data-types-semistructured 697 # - https://docs.snowflake.com/en/sql-reference/functions/object_construct 698 return self._parse_slice(self._parse_string()) 699 700 return self._parse_slice(self._parse_alias(self._parse_assignment(), explicit=True)) 701 702 def _parse_lateral(self) -> t.Optional[exp.Lateral]: 703 lateral = super()._parse_lateral() 704 if not lateral: 705 return lateral 706 707 if isinstance(lateral.this, exp.Explode): 708 table_alias = lateral.args.get("alias") 709 columns = [exp.to_identifier(col) for col in self.FLATTEN_COLUMNS] 710 if table_alias and not table_alias.args.get("columns"): 711 table_alias.set("columns", columns) 712 elif not table_alias: 713 exp.alias_(lateral, "_flattened", table=columns, copy=False) 714 715 return lateral 716 717 def _parse_table_parts( 718 self, schema: bool = False, is_db_reference: bool = False, wildcard: bool = False 719 ) -> exp.Table: 720 # https://docs.snowflake.com/en/user-guide/querying-stage 721 if self._match(TokenType.STRING, advance=False): 722 table = self._parse_string() 723 elif self._match_text_seq("@", advance=False): 724 table = self._parse_location_path() 725 else: 726 table = None 727 728 if table: 729 file_format = None 730 pattern = None 731 732 wrapped = self._match(TokenType.L_PAREN) 733 while self._curr and wrapped and not self._match(TokenType.R_PAREN): 734 if self._match_text_seq("FILE_FORMAT", "=>"): 735 file_format = self._parse_string() or super()._parse_table_parts( 736 is_db_reference=is_db_reference 737 ) 738 elif self._match_text_seq("PATTERN", "=>"): 739 pattern = self._parse_string() 740 else: 741 break 742 743 self._match(TokenType.COMMA) 744 745 table = self.expression(exp.Table, this=table, format=file_format, pattern=pattern) 746 else: 747 table = super()._parse_table_parts(schema=schema, is_db_reference=is_db_reference) 748 749 return table 750 751 def _parse_table( 752 self, 753 schema: bool = False, 754 joins: bool = False, 755 alias_tokens: t.Optional[t.Collection[TokenType]] = None, 756 parse_bracket: bool = False, 757 is_db_reference: bool = False, 758 parse_partition: bool = False, 759 ) -> t.Optional[exp.Expression]: 760 table = super()._parse_table( 761 schema=schema, 762 joins=joins, 763 alias_tokens=alias_tokens, 764 parse_bracket=parse_bracket, 765 is_db_reference=is_db_reference, 766 parse_partition=parse_partition, 767 ) 768 if isinstance(table, exp.Table) and isinstance(table.this, exp.TableFromRows): 769 table_from_rows = table.this 770 for arg in exp.TableFromRows.arg_types: 771 if arg != "this": 772 table_from_rows.set(arg, table.args.get(arg)) 773 774 table = table_from_rows 775 776 return table 777 778 def _parse_id_var( 779 self, 780 any_token: bool = True, 781 tokens: t.Optional[t.Collection[TokenType]] = None, 782 ) -> t.Optional[exp.Expression]: 783 if self._match_text_seq("IDENTIFIER", "("): 784 identifier = ( 785 super()._parse_id_var(any_token=any_token, tokens=tokens) 786 or self._parse_string() 787 ) 788 self._match_r_paren() 789 return self.expression(exp.Anonymous, this="IDENTIFIER", expressions=[identifier]) 790 791 return super()._parse_id_var(any_token=any_token, tokens=tokens) 792 793 def _parse_show_snowflake(self, this: str) -> exp.Show: 794 scope = None 795 scope_kind = None 796 797 # will identity SHOW TERSE SCHEMAS but not SHOW TERSE PRIMARY KEYS 798 # which is syntactically valid but has no effect on the output 799 terse = self._tokens[self._index - 2].text.upper() == "TERSE" 800 801 history = self._match_text_seq("HISTORY") 802 803 like = self._parse_string() if self._match(TokenType.LIKE) else None 804 805 if self._match(TokenType.IN): 806 if self._match_text_seq("ACCOUNT"): 807 scope_kind = "ACCOUNT" 808 elif self._match_text_seq("CLASS"): 809 scope_kind = "CLASS" 810 scope = self._parse_table_parts() 811 elif self._match_text_seq("APPLICATION"): 812 scope_kind = "APPLICATION" 813 if self._match_text_seq("PACKAGE"): 814 scope_kind += " PACKAGE" 815 scope = self._parse_table_parts() 816 elif self._match_set(self.DB_CREATABLES): 817 scope_kind = self._prev.text.upper() 818 if self._curr: 819 scope = self._parse_table_parts() 820 elif self._curr: 821 scope_kind = "SCHEMA" if this in self.SCHEMA_KINDS else "TABLE" 822 scope = self._parse_table_parts() 823 824 return self.expression( 825 exp.Show, 826 **{ 827 "terse": terse, 828 "this": this, 829 "history": history, 830 "like": like, 831 "scope": scope, 832 "scope_kind": scope_kind, 833 "starts_with": self._match_text_seq("STARTS", "WITH") and self._parse_string(), 834 "limit": self._parse_limit(), 835 "from": self._parse_string() if self._match(TokenType.FROM) else None, 836 "privileges": self._match_text_seq("WITH", "PRIVILEGES") 837 and self._parse_csv(lambda: self._parse_var(any_token=True, upper=True)), 838 }, 839 ) 840 841 def _parse_put(self) -> exp.Put | exp.Command: 842 if self._curr.token_type != TokenType.STRING: 843 return self._parse_as_command(self._prev) 844 845 return self.expression( 846 exp.Put, 847 this=self._parse_string(), 848 target=self._parse_location_path(), 849 properties=self._parse_properties(), 850 ) 851 852 def _parse_location_property(self) -> exp.LocationProperty: 853 self._match(TokenType.EQ) 854 return self.expression(exp.LocationProperty, this=self._parse_location_path()) 855 856 def _parse_file_location(self) -> t.Optional[exp.Expression]: 857 # Parse either a subquery or a staged file 858 return ( 859 self._parse_select(table=True, parse_subquery_alias=False) 860 if self._match(TokenType.L_PAREN, advance=False) 861 else self._parse_table_parts() 862 ) 863 864 def _parse_location_path(self) -> exp.Var: 865 parts = [self._advance_any(ignore_reserved=True)] 866 867 # We avoid consuming a comma token because external tables like @foo and @bar 868 # can be joined in a query with a comma separator, as well as closing paren 869 # in case of subqueries 870 while self._is_connected() and not self._match_set( 871 (TokenType.COMMA, TokenType.L_PAREN, TokenType.R_PAREN), advance=False 872 ): 873 parts.append(self._advance_any(ignore_reserved=True)) 874 875 return exp.var("".join(part.text for part in parts if part)) 876 877 def _parse_lambda_arg(self) -> t.Optional[exp.Expression]: 878 this = super()._parse_lambda_arg() 879 880 if not this: 881 return this 882 883 typ = self._parse_types() 884 885 if typ: 886 return self.expression(exp.Cast, this=this, to=typ) 887 888 return this 889 890 def _parse_foreign_key(self) -> exp.ForeignKey: 891 # inlineFK, the REFERENCES columns are implied 892 if self._match(TokenType.REFERENCES, advance=False): 893 return self.expression(exp.ForeignKey) 894 895 # outoflineFK, explicitly names the columns 896 return super()._parse_foreign_key() 897 898 class Tokenizer(tokens.Tokenizer): 899 STRING_ESCAPES = ["\\", "'"] 900 HEX_STRINGS = [("x'", "'"), ("X'", "'")] 901 RAW_STRINGS = ["$$"] 902 COMMENTS = ["--", "//", ("/*", "*/")] 903 NESTED_COMMENTS = False 904 905 KEYWORDS = { 906 **tokens.Tokenizer.KEYWORDS, 907 "FILE://": TokenType.URI_START, 908 "BYTEINT": TokenType.INT, 909 "CHAR VARYING": TokenType.VARCHAR, 910 "CHARACTER VARYING": TokenType.VARCHAR, 911 "EXCLUDE": TokenType.EXCEPT, 912 "ILIKE ANY": TokenType.ILIKE_ANY, 913 "LIKE ANY": TokenType.LIKE_ANY, 914 "MATCH_CONDITION": TokenType.MATCH_CONDITION, 915 "MATCH_RECOGNIZE": TokenType.MATCH_RECOGNIZE, 916 "MINUS": TokenType.EXCEPT, 917 "NCHAR VARYING": TokenType.VARCHAR, 918 "PUT": TokenType.PUT, 919 "REMOVE": TokenType.COMMAND, 920 "RM": TokenType.COMMAND, 921 "SAMPLE": TokenType.TABLE_SAMPLE, 922 "SQL_DOUBLE": TokenType.DOUBLE, 923 "SQL_VARCHAR": TokenType.VARCHAR, 924 "STORAGE INTEGRATION": TokenType.STORAGE_INTEGRATION, 925 "TAG": TokenType.TAG, 926 "TIMESTAMP_TZ": TokenType.TIMESTAMPTZ, 927 "TOP": TokenType.TOP, 928 "WAREHOUSE": TokenType.WAREHOUSE, 929 "STREAMLIT": TokenType.STREAMLIT, 930 } 931 KEYWORDS.pop("/*+") 932 933 SINGLE_TOKENS = { 934 **tokens.Tokenizer.SINGLE_TOKENS, 935 "$": TokenType.PARAMETER, 936 } 937 938 VAR_SINGLE_TOKENS = {"$"} 939 940 COMMANDS = tokens.Tokenizer.COMMANDS - {TokenType.SHOW} 941 942 class Generator(generator.Generator): 943 PARAMETER_TOKEN = "$" 944 MATCHED_BY_SOURCE = False 945 SINGLE_STRING_INTERVAL = True 946 JOIN_HINTS = False 947 TABLE_HINTS = False 948 QUERY_HINTS = False 949 AGGREGATE_FILTER_SUPPORTED = False 950 SUPPORTS_TABLE_COPY = False 951 COLLATE_IS_FUNC = True 952 LIMIT_ONLY_LITERALS = True 953 JSON_KEY_VALUE_PAIR_SEP = "," 954 INSERT_OVERWRITE = " OVERWRITE INTO" 955 STRUCT_DELIMITER = ("(", ")") 956 COPY_PARAMS_ARE_WRAPPED = False 957 COPY_PARAMS_EQ_REQUIRED = True 958 STAR_EXCEPT = "EXCLUDE" 959 SUPPORTS_EXPLODING_PROJECTIONS = False 960 ARRAY_CONCAT_IS_VAR_LEN = False 961 SUPPORTS_CONVERT_TIMEZONE = True 962 EXCEPT_INTERSECT_SUPPORT_ALL_CLAUSE = False 963 SUPPORTS_MEDIAN = True 964 ARRAY_SIZE_NAME = "ARRAY_SIZE" 965 966 TRANSFORMS = { 967 **generator.Generator.TRANSFORMS, 968 exp.ApproxDistinct: rename_func("APPROX_COUNT_DISTINCT"), 969 exp.ArgMax: rename_func("MAX_BY"), 970 exp.ArgMin: rename_func("MIN_BY"), 971 exp.Array: inline_array_sql, 972 exp.ArrayConcat: lambda self, e: self.arrayconcat_sql(e, name="ARRAY_CAT"), 973 exp.ArrayContains: lambda self, e: self.func("ARRAY_CONTAINS", e.expression, e.this), 974 exp.AtTimeZone: lambda self, e: self.func( 975 "CONVERT_TIMEZONE", e.args.get("zone"), e.this 976 ), 977 exp.BitwiseOr: rename_func("BITOR"), 978 exp.BitwiseXor: rename_func("BITXOR"), 979 exp.BitwiseLeftShift: rename_func("BITSHIFTLEFT"), 980 exp.BitwiseRightShift: rename_func("BITSHIFTRIGHT"), 981 exp.Create: transforms.preprocess([_flatten_structured_types_unless_iceberg]), 982 exp.DateAdd: date_delta_sql("DATEADD"), 983 exp.DateDiff: date_delta_sql("DATEDIFF"), 984 exp.DatetimeAdd: date_delta_sql("TIMESTAMPADD"), 985 exp.DatetimeDiff: timestampdiff_sql, 986 exp.DateStrToDate: datestrtodate_sql, 987 exp.DayOfMonth: rename_func("DAYOFMONTH"), 988 exp.DayOfWeek: rename_func("DAYOFWEEK"), 989 exp.DayOfYear: rename_func("DAYOFYEAR"), 990 exp.Explode: rename_func("FLATTEN"), 991 exp.Extract: rename_func("DATE_PART"), 992 exp.FromTimeZone: lambda self, e: self.func( 993 "CONVERT_TIMEZONE", e.args.get("zone"), "'UTC'", e.this 994 ), 995 exp.GenerateSeries: lambda self, e: self.func( 996 "ARRAY_GENERATE_RANGE", e.args["start"], e.args["end"] + 1, e.args.get("step") 997 ), 998 exp.GroupConcat: lambda self, e: groupconcat_sql(self, e, sep=""), 999 exp.If: if_sql(name="IFF", false_value="NULL"), 1000 exp.JSONExtractArray: _json_extract_value_array_sql, 1001 exp.JSONExtractScalar: lambda self, e: self.func( 1002 "JSON_EXTRACT_PATH_TEXT", e.this, e.expression 1003 ), 1004 exp.JSONObject: lambda self, e: self.func("OBJECT_CONSTRUCT_KEEP_NULL", *e.expressions), 1005 exp.JSONPathRoot: lambda *_: "", 1006 exp.JSONValueArray: _json_extract_value_array_sql, 1007 exp.LogicalAnd: rename_func("BOOLAND_AGG"), 1008 exp.LogicalOr: rename_func("BOOLOR_AGG"), 1009 exp.Map: lambda self, e: var_map_sql(self, e, "OBJECT_CONSTRUCT"), 1010 exp.MakeInterval: no_make_interval_sql, 1011 exp.Max: max_or_greatest, 1012 exp.Min: min_or_least, 1013 exp.ParseJSON: lambda self, e: self.func( 1014 "TRY_PARSE_JSON" if e.args.get("safe") else "PARSE_JSON", e.this 1015 ), 1016 exp.PartitionedByProperty: lambda self, e: f"PARTITION BY {self.sql(e, 'this')}", 1017 exp.PercentileCont: transforms.preprocess( 1018 [transforms.add_within_group_for_percentiles] 1019 ), 1020 exp.PercentileDisc: transforms.preprocess( 1021 [transforms.add_within_group_for_percentiles] 1022 ), 1023 exp.Pivot: transforms.preprocess([_unqualify_pivot_columns]), 1024 exp.RegexpExtract: _regexpextract_sql, 1025 exp.RegexpExtractAll: _regexpextract_sql, 1026 exp.RegexpILike: _regexpilike_sql, 1027 exp.Rand: rename_func("RANDOM"), 1028 exp.Select: transforms.preprocess( 1029 [ 1030 transforms.eliminate_distinct_on, 1031 transforms.explode_to_unnest(), 1032 transforms.eliminate_semi_and_anti_joins, 1033 _transform_generate_date_array, 1034 ] 1035 ), 1036 exp.SHA: rename_func("SHA1"), 1037 exp.StarMap: rename_func("OBJECT_CONSTRUCT"), 1038 exp.StartsWith: rename_func("STARTSWITH"), 1039 exp.StrPosition: lambda self, e: strposition_sql( 1040 self, e, func_name="CHARINDEX", supports_position=True 1041 ), 1042 exp.StrToDate: lambda self, e: self.func("DATE", e.this, self.format_time(e)), 1043 exp.Stuff: rename_func("INSERT"), 1044 exp.TimeAdd: date_delta_sql("TIMEADD"), 1045 exp.Timestamp: no_timestamp_sql, 1046 exp.TimestampAdd: date_delta_sql("TIMESTAMPADD"), 1047 exp.TimestampDiff: lambda self, e: self.func( 1048 "TIMESTAMPDIFF", e.unit, e.expression, e.this 1049 ), 1050 exp.TimestampTrunc: timestamptrunc_sql(), 1051 exp.TimeStrToTime: timestrtotime_sql, 1052 exp.TimeToUnix: lambda self, e: f"EXTRACT(epoch_second FROM {self.sql(e, 'this')})", 1053 exp.ToArray: rename_func("TO_ARRAY"), 1054 exp.ToChar: lambda self, e: self.function_fallback_sql(e), 1055 exp.ToDouble: rename_func("TO_DOUBLE"), 1056 exp.TsOrDsAdd: date_delta_sql("DATEADD", cast=True), 1057 exp.TsOrDsDiff: date_delta_sql("DATEDIFF"), 1058 exp.TsOrDsToDate: lambda self, e: self.func( 1059 "TRY_TO_DATE" if e.args.get("safe") else "TO_DATE", e.this, self.format_time(e) 1060 ), 1061 exp.TsOrDsToTime: lambda self, e: self.func( 1062 "TRY_TO_TIME" if e.args.get("safe") else "TO_TIME", e.this, self.format_time(e) 1063 ), 1064 exp.Unhex: rename_func("HEX_DECODE_BINARY"), 1065 exp.UnixToTime: rename_func("TO_TIMESTAMP"), 1066 exp.Uuid: rename_func("UUID_STRING"), 1067 exp.VarMap: lambda self, e: var_map_sql(self, e, "OBJECT_CONSTRUCT"), 1068 exp.WeekOfYear: rename_func("WEEKOFYEAR"), 1069 exp.Xor: rename_func("BOOLXOR"), 1070 exp.Levenshtein: unsupported_args("ins_cost", "del_cost", "sub_cost")( 1071 rename_func("EDITDISTANCE") 1072 ), 1073 } 1074 1075 SUPPORTED_JSON_PATH_PARTS = { 1076 exp.JSONPathKey, 1077 exp.JSONPathRoot, 1078 exp.JSONPathSubscript, 1079 } 1080 1081 TYPE_MAPPING = { 1082 **generator.Generator.TYPE_MAPPING, 1083 exp.DataType.Type.NESTED: "OBJECT", 1084 exp.DataType.Type.STRUCT: "OBJECT", 1085 } 1086 1087 TOKEN_MAPPING = { 1088 TokenType.AUTO_INCREMENT: "AUTOINCREMENT", 1089 } 1090 1091 PROPERTIES_LOCATION = { 1092 **generator.Generator.PROPERTIES_LOCATION, 1093 exp.SetProperty: exp.Properties.Location.UNSUPPORTED, 1094 exp.VolatileProperty: exp.Properties.Location.UNSUPPORTED, 1095 } 1096 1097 UNSUPPORTED_VALUES_EXPRESSIONS = { 1098 exp.Map, 1099 exp.StarMap, 1100 exp.Struct, 1101 exp.VarMap, 1102 } 1103 1104 def with_properties(self, properties: exp.Properties) -> str: 1105 return self.properties(properties, wrapped=False, prefix=self.sep(""), sep=" ") 1106 1107 def values_sql(self, expression: exp.Values, values_as_table: bool = True) -> str: 1108 if expression.find(*self.UNSUPPORTED_VALUES_EXPRESSIONS): 1109 values_as_table = False 1110 1111 return super().values_sql(expression, values_as_table=values_as_table) 1112 1113 def datatype_sql(self, expression: exp.DataType) -> str: 1114 expressions = expression.expressions 1115 if ( 1116 expressions 1117 and expression.is_type(*exp.DataType.STRUCT_TYPES) 1118 and any(isinstance(field_type, exp.DataType) for field_type in expressions) 1119 ): 1120 # The correct syntax is OBJECT [ (<key> <value_type [NOT NULL] [, ...]) ] 1121 return "OBJECT" 1122 1123 return super().datatype_sql(expression) 1124 1125 def tonumber_sql(self, expression: exp.ToNumber) -> str: 1126 return self.func( 1127 "TO_NUMBER", 1128 expression.this, 1129 expression.args.get("format"), 1130 expression.args.get("precision"), 1131 expression.args.get("scale"), 1132 ) 1133 1134 def timestampfromparts_sql(self, expression: exp.TimestampFromParts) -> str: 1135 milli = expression.args.get("milli") 1136 if milli is not None: 1137 milli_to_nano = milli.pop() * exp.Literal.number(1000000) 1138 expression.set("nano", milli_to_nano) 1139 1140 return rename_func("TIMESTAMP_FROM_PARTS")(self, expression) 1141 1142 def cast_sql(self, expression: exp.Cast, safe_prefix: t.Optional[str] = None) -> str: 1143 if expression.is_type(exp.DataType.Type.GEOGRAPHY): 1144 return self.func("TO_GEOGRAPHY", expression.this) 1145 if expression.is_type(exp.DataType.Type.GEOMETRY): 1146 return self.func("TO_GEOMETRY", expression.this) 1147 1148 return super().cast_sql(expression, safe_prefix=safe_prefix) 1149 1150 def trycast_sql(self, expression: exp.TryCast) -> str: 1151 value = expression.this 1152 1153 if value.type is None: 1154 from sqlglot.optimizer.annotate_types import annotate_types 1155 1156 value = annotate_types(value) 1157 1158 if value.is_type(*exp.DataType.TEXT_TYPES, exp.DataType.Type.UNKNOWN): 1159 return super().trycast_sql(expression) 1160 1161 # TRY_CAST only works for string values in Snowflake 1162 return self.cast_sql(expression) 1163 1164 def log_sql(self, expression: exp.Log) -> str: 1165 if not expression.expression: 1166 return self.func("LN", expression.this) 1167 1168 return super().log_sql(expression) 1169 1170 def unnest_sql(self, expression: exp.Unnest) -> str: 1171 unnest_alias = expression.args.get("alias") 1172 offset = expression.args.get("offset") 1173 1174 columns = [ 1175 exp.to_identifier("seq"), 1176 exp.to_identifier("key"), 1177 exp.to_identifier("path"), 1178 offset.pop() if isinstance(offset, exp.Expression) else exp.to_identifier("index"), 1179 seq_get(unnest_alias.columns if unnest_alias else [], 0) 1180 or exp.to_identifier("value"), 1181 exp.to_identifier("this"), 1182 ] 1183 1184 if unnest_alias: 1185 unnest_alias.set("columns", columns) 1186 else: 1187 unnest_alias = exp.TableAlias(this="_u", columns=columns) 1188 1189 table_input = self.sql(expression.expressions[0]) 1190 if not table_input.startswith("INPUT =>"): 1191 table_input = f"INPUT => {table_input}" 1192 1193 explode = f"TABLE(FLATTEN({table_input}))" 1194 alias = self.sql(unnest_alias) 1195 alias = f" AS {alias}" if alias else "" 1196 return f"{explode}{alias}" 1197 1198 def show_sql(self, expression: exp.Show) -> str: 1199 terse = "TERSE " if expression.args.get("terse") else "" 1200 history = " HISTORY" if expression.args.get("history") else "" 1201 like = self.sql(expression, "like") 1202 like = f" LIKE {like}" if like else "" 1203 1204 scope = self.sql(expression, "scope") 1205 scope = f" {scope}" if scope else "" 1206 1207 scope_kind = self.sql(expression, "scope_kind") 1208 if scope_kind: 1209 scope_kind = f" IN {scope_kind}" 1210 1211 starts_with = self.sql(expression, "starts_with") 1212 if starts_with: 1213 starts_with = f" STARTS WITH {starts_with}" 1214 1215 limit = self.sql(expression, "limit") 1216 1217 from_ = self.sql(expression, "from") 1218 if from_: 1219 from_ = f" FROM {from_}" 1220 1221 privileges = self.expressions(expression, key="privileges", flat=True) 1222 privileges = f" WITH PRIVILEGES {privileges}" if privileges else "" 1223 1224 return f"SHOW {terse}{expression.name}{history}{like}{scope_kind}{scope}{starts_with}{limit}{from_}{privileges}" 1225 1226 def describe_sql(self, expression: exp.Describe) -> str: 1227 # Default to table if kind is unknown 1228 kind_value = expression.args.get("kind") or "TABLE" 1229 kind = f" {kind_value}" if kind_value else "" 1230 this = f" {self.sql(expression, 'this')}" 1231 expressions = self.expressions(expression, flat=True) 1232 expressions = f" {expressions}" if expressions else "" 1233 return f"DESCRIBE{kind}{this}{expressions}" 1234 1235 def generatedasidentitycolumnconstraint_sql( 1236 self, expression: exp.GeneratedAsIdentityColumnConstraint 1237 ) -> str: 1238 start = expression.args.get("start") 1239 start = f" START {start}" if start else "" 1240 increment = expression.args.get("increment") 1241 increment = f" INCREMENT {increment}" if increment else "" 1242 return f"AUTOINCREMENT{start}{increment}" 1243 1244 def cluster_sql(self, expression: exp.Cluster) -> str: 1245 return f"CLUSTER BY ({self.expressions(expression, flat=True)})" 1246 1247 def struct_sql(self, expression: exp.Struct) -> str: 1248 keys = [] 1249 values = [] 1250 1251 for i, e in enumerate(expression.expressions): 1252 if isinstance(e, exp.PropertyEQ): 1253 keys.append( 1254 exp.Literal.string(e.name) if isinstance(e.this, exp.Identifier) else e.this 1255 ) 1256 values.append(e.expression) 1257 else: 1258 keys.append(exp.Literal.string(f"_{i}")) 1259 values.append(e) 1260 1261 return self.func("OBJECT_CONSTRUCT", *flatten(zip(keys, values))) 1262 1263 @unsupported_args("weight", "accuracy") 1264 def approxquantile_sql(self, expression: exp.ApproxQuantile) -> str: 1265 return self.func("APPROX_PERCENTILE", expression.this, expression.args.get("quantile")) 1266 1267 def alterset_sql(self, expression: exp.AlterSet) -> str: 1268 exprs = self.expressions(expression, flat=True) 1269 exprs = f" {exprs}" if exprs else "" 1270 file_format = self.expressions(expression, key="file_format", flat=True, sep=" ") 1271 file_format = f" STAGE_FILE_FORMAT = ({file_format})" if file_format else "" 1272 copy_options = self.expressions(expression, key="copy_options", flat=True, sep=" ") 1273 copy_options = f" STAGE_COPY_OPTIONS = ({copy_options})" if copy_options else "" 1274 tag = self.expressions(expression, key="tag", flat=True) 1275 tag = f" TAG {tag}" if tag else "" 1276 1277 return f"SET{exprs}{file_format}{copy_options}{tag}" 1278 1279 def strtotime_sql(self, expression: exp.StrToTime): 1280 safe_prefix = "TRY_" if expression.args.get("safe") else "" 1281 return self.func( 1282 f"{safe_prefix}TO_TIMESTAMP", expression.this, self.format_time(expression) 1283 ) 1284 1285 def timestampsub_sql(self, expression: exp.TimestampSub): 1286 return self.sql( 1287 exp.TimestampAdd( 1288 this=expression.this, 1289 expression=expression.expression * -1, 1290 unit=expression.unit, 1291 ) 1292 ) 1293 1294 def jsonextract_sql(self, expression: exp.JSONExtract): 1295 this = expression.this 1296 1297 # JSON strings are valid coming from other dialects such as BQ 1298 return self.func( 1299 "GET_PATH", 1300 exp.ParseJSON(this=this) if this.is_string else this, 1301 expression.expression, 1302 ) 1303 1304 def timetostr_sql(self, expression: exp.TimeToStr) -> str: 1305 this = expression.this 1306 if not isinstance(this, exp.TsOrDsToTimestamp): 1307 this = exp.cast(this, exp.DataType.Type.TIMESTAMP) 1308 1309 return self.func("TO_CHAR", this, self.format_time(expression)) 1310 1311 def datesub_sql(self, expression: exp.DateSub) -> str: 1312 value = expression.expression 1313 if value: 1314 value.replace(value * (-1)) 1315 else: 1316 self.unsupported("DateSub cannot be transpiled if the subtracted count is unknown") 1317 1318 return date_delta_sql("DATEADD")(self, expression) 1319 1320 def select_sql(self, expression: exp.Select) -> str: 1321 limit = expression.args.get("limit") 1322 offset = expression.args.get("offset") 1323 if offset and not limit: 1324 expression.limit(exp.Null(), copy=False) 1325 return super().select_sql(expression)
337class Snowflake(Dialect): 338 # https://docs.snowflake.com/en/sql-reference/identifiers-syntax 339 NORMALIZATION_STRATEGY = NormalizationStrategy.UPPERCASE 340 NULL_ORDERING = "nulls_are_large" 341 TIME_FORMAT = "'YYYY-MM-DD HH24:MI:SS'" 342 SUPPORTS_USER_DEFINED_TYPES = False 343 SUPPORTS_SEMI_ANTI_JOIN = False 344 PREFER_CTE_ALIAS_COLUMN = True 345 TABLESAMPLE_SIZE_IS_PERCENT = True 346 COPY_PARAMS_ARE_CSV = False 347 ARRAY_AGG_INCLUDES_NULLS = None 348 349 TIME_MAPPING = { 350 "YYYY": "%Y", 351 "yyyy": "%Y", 352 "YY": "%y", 353 "yy": "%y", 354 "MMMM": "%B", 355 "mmmm": "%B", 356 "MON": "%b", 357 "mon": "%b", 358 "MM": "%m", 359 "mm": "%m", 360 "DD": "%d", 361 "dd": "%-d", 362 "DY": "%a", 363 "dy": "%w", 364 "HH24": "%H", 365 "hh24": "%H", 366 "HH12": "%I", 367 "hh12": "%I", 368 "MI": "%M", 369 "mi": "%M", 370 "SS": "%S", 371 "ss": "%S", 372 "FF6": "%f", 373 "ff6": "%f", 374 } 375 376 def quote_identifier(self, expression: E, identify: bool = True) -> E: 377 # This disables quoting DUAL in SELECT ... FROM DUAL, because Snowflake treats an 378 # unquoted DUAL keyword in a special way and does not map it to a user-defined table 379 if ( 380 isinstance(expression, exp.Identifier) 381 and isinstance(expression.parent, exp.Table) 382 and expression.name.lower() == "dual" 383 ): 384 return expression # type: ignore 385 386 return super().quote_identifier(expression, identify=identify) 387 388 class JSONPathTokenizer(jsonpath.JSONPathTokenizer): 389 SINGLE_TOKENS = jsonpath.JSONPathTokenizer.SINGLE_TOKENS.copy() 390 SINGLE_TOKENS.pop("$") 391 392 class Parser(parser.Parser): 393 IDENTIFY_PIVOT_STRINGS = True 394 DEFAULT_SAMPLING_METHOD = "BERNOULLI" 395 COLON_IS_VARIANT_EXTRACT = True 396 397 ID_VAR_TOKENS = { 398 *parser.Parser.ID_VAR_TOKENS, 399 TokenType.MATCH_CONDITION, 400 } 401 402 TABLE_ALIAS_TOKENS = parser.Parser.TABLE_ALIAS_TOKENS | {TokenType.WINDOW} 403 TABLE_ALIAS_TOKENS.discard(TokenType.MATCH_CONDITION) 404 405 FUNCTIONS = { 406 **parser.Parser.FUNCTIONS, 407 "APPROX_PERCENTILE": exp.ApproxQuantile.from_arg_list, 408 "ARRAY_CONSTRUCT": lambda args: exp.Array(expressions=args), 409 "ARRAY_CONTAINS": lambda args: exp.ArrayContains( 410 this=seq_get(args, 1), expression=seq_get(args, 0) 411 ), 412 "ARRAY_GENERATE_RANGE": lambda args: exp.GenerateSeries( 413 # ARRAY_GENERATE_RANGE has an exlusive end; we normalize it to be inclusive 414 start=seq_get(args, 0), 415 end=exp.Sub(this=seq_get(args, 1), expression=exp.Literal.number(1)), 416 step=seq_get(args, 2), 417 ), 418 "BITXOR": _build_bitwise(exp.BitwiseXor, "BITXOR"), 419 "BIT_XOR": _build_bitwise(exp.BitwiseXor, "BITXOR"), 420 "BITOR": _build_bitwise(exp.BitwiseOr, "BITOR"), 421 "BIT_OR": _build_bitwise(exp.BitwiseOr, "BITOR"), 422 "BITSHIFTLEFT": _build_bitwise(exp.BitwiseLeftShift, "BITSHIFTLEFT"), 423 "BIT_SHIFTLEFT": _build_bitwise(exp.BitwiseLeftShift, "BIT_SHIFTLEFT"), 424 "BITSHIFTRIGHT": _build_bitwise(exp.BitwiseRightShift, "BITSHIFTRIGHT"), 425 "BIT_SHIFTRIGHT": _build_bitwise(exp.BitwiseRightShift, "BIT_SHIFTRIGHT"), 426 "BOOLXOR": _build_bitwise(exp.Xor, "BOOLXOR"), 427 "DATE": _build_datetime("DATE", exp.DataType.Type.DATE), 428 "DATE_TRUNC": _date_trunc_to_time, 429 "DATEADD": _build_date_time_add(exp.DateAdd), 430 "DATEDIFF": _build_datediff, 431 "DIV0": _build_if_from_div0, 432 "EDITDISTANCE": lambda args: exp.Levenshtein( 433 this=seq_get(args, 0), expression=seq_get(args, 1), max_dist=seq_get(args, 2) 434 ), 435 "FLATTEN": exp.Explode.from_arg_list, 436 "GET_PATH": lambda args, dialect: exp.JSONExtract( 437 this=seq_get(args, 0), expression=dialect.to_json_path(seq_get(args, 1)) 438 ), 439 "HEX_DECODE_BINARY": exp.Unhex.from_arg_list, 440 "IFF": exp.If.from_arg_list, 441 "LAST_DAY": lambda args: exp.LastDay( 442 this=seq_get(args, 0), unit=map_date_part(seq_get(args, 1)) 443 ), 444 "LEN": lambda args: exp.Length(this=seq_get(args, 0), binary=True), 445 "LENGTH": lambda args: exp.Length(this=seq_get(args, 0), binary=True), 446 "NULLIFZERO": _build_if_from_nullifzero, 447 "OBJECT_CONSTRUCT": _build_object_construct, 448 "REGEXP_EXTRACT_ALL": _build_regexp_extract(exp.RegexpExtractAll), 449 "REGEXP_REPLACE": _build_regexp_replace, 450 "REGEXP_SUBSTR": _build_regexp_extract(exp.RegexpExtract), 451 "REGEXP_SUBSTR_ALL": _build_regexp_extract(exp.RegexpExtractAll), 452 "RLIKE": exp.RegexpLike.from_arg_list, 453 "SQUARE": lambda args: exp.Pow(this=seq_get(args, 0), expression=exp.Literal.number(2)), 454 "TABLE": lambda args: exp.TableFromRows(this=seq_get(args, 0)), 455 "TIMEADD": _build_date_time_add(exp.TimeAdd), 456 "TIMEDIFF": _build_datediff, 457 "TIMESTAMPADD": _build_date_time_add(exp.DateAdd), 458 "TIMESTAMPDIFF": _build_datediff, 459 "TIMESTAMPFROMPARTS": build_timestamp_from_parts, 460 "TIMESTAMP_FROM_PARTS": build_timestamp_from_parts, 461 "TIMESTAMPNTZFROMPARTS": build_timestamp_from_parts, 462 "TIMESTAMP_NTZ_FROM_PARTS": build_timestamp_from_parts, 463 "TRY_PARSE_JSON": lambda args: exp.ParseJSON(this=seq_get(args, 0), safe=True), 464 "TRY_TO_DATE": _build_datetime("TRY_TO_DATE", exp.DataType.Type.DATE, safe=True), 465 "TRY_TO_TIME": _build_datetime("TRY_TO_TIME", exp.DataType.Type.TIME, safe=True), 466 "TRY_TO_TIMESTAMP": _build_datetime( 467 "TRY_TO_TIMESTAMP", exp.DataType.Type.TIMESTAMP, safe=True 468 ), 469 "TO_CHAR": build_timetostr_or_tochar, 470 "TO_DATE": _build_datetime("TO_DATE", exp.DataType.Type.DATE), 471 "TO_NUMBER": lambda args: exp.ToNumber( 472 this=seq_get(args, 0), 473 format=seq_get(args, 1), 474 precision=seq_get(args, 2), 475 scale=seq_get(args, 3), 476 ), 477 "TO_TIME": _build_datetime("TO_TIME", exp.DataType.Type.TIME), 478 "TO_TIMESTAMP": _build_datetime("TO_TIMESTAMP", exp.DataType.Type.TIMESTAMP), 479 "TO_TIMESTAMP_LTZ": _build_datetime("TO_TIMESTAMP_LTZ", exp.DataType.Type.TIMESTAMPLTZ), 480 "TO_TIMESTAMP_NTZ": _build_datetime("TO_TIMESTAMP_NTZ", exp.DataType.Type.TIMESTAMP), 481 "TO_TIMESTAMP_TZ": _build_datetime("TO_TIMESTAMP_TZ", exp.DataType.Type.TIMESTAMPTZ), 482 "TO_VARCHAR": exp.ToChar.from_arg_list, 483 "ZEROIFNULL": _build_if_from_zeroifnull, 484 } 485 486 FUNCTION_PARSERS = { 487 **parser.Parser.FUNCTION_PARSERS, 488 "DATE_PART": lambda self: self._parse_date_part(), 489 "OBJECT_CONSTRUCT_KEEP_NULL": lambda self: self._parse_json_object(), 490 "LISTAGG": lambda self: self._parse_string_agg(), 491 } 492 FUNCTION_PARSERS.pop("TRIM") 493 494 TIMESTAMPS = parser.Parser.TIMESTAMPS - {TokenType.TIME} 495 496 RANGE_PARSERS = { 497 **parser.Parser.RANGE_PARSERS, 498 TokenType.LIKE_ANY: parser.binary_range_parser(exp.LikeAny), 499 TokenType.ILIKE_ANY: parser.binary_range_parser(exp.ILikeAny), 500 } 501 502 ALTER_PARSERS = { 503 **parser.Parser.ALTER_PARSERS, 504 "UNSET": lambda self: self.expression( 505 exp.Set, 506 tag=self._match_text_seq("TAG"), 507 expressions=self._parse_csv(self._parse_id_var), 508 unset=True, 509 ), 510 } 511 512 STATEMENT_PARSERS = { 513 **parser.Parser.STATEMENT_PARSERS, 514 TokenType.PUT: lambda self: self._parse_put(), 515 TokenType.SHOW: lambda self: self._parse_show(), 516 } 517 518 PROPERTY_PARSERS = { 519 **parser.Parser.PROPERTY_PARSERS, 520 "LOCATION": lambda self: self._parse_location_property(), 521 "TAG": lambda self: self._parse_tag(), 522 } 523 524 TYPE_CONVERTERS = { 525 # https://docs.snowflake.com/en/sql-reference/data-types-numeric#number 526 exp.DataType.Type.DECIMAL: build_default_decimal_type(precision=38, scale=0), 527 } 528 529 SHOW_PARSERS = { 530 "DATABASES": _show_parser("DATABASES"), 531 "TERSE DATABASES": _show_parser("DATABASES"), 532 "SCHEMAS": _show_parser("SCHEMAS"), 533 "TERSE SCHEMAS": _show_parser("SCHEMAS"), 534 "OBJECTS": _show_parser("OBJECTS"), 535 "TERSE OBJECTS": _show_parser("OBJECTS"), 536 "TABLES": _show_parser("TABLES"), 537 "TERSE TABLES": _show_parser("TABLES"), 538 "VIEWS": _show_parser("VIEWS"), 539 "TERSE VIEWS": _show_parser("VIEWS"), 540 "PRIMARY KEYS": _show_parser("PRIMARY KEYS"), 541 "TERSE PRIMARY KEYS": _show_parser("PRIMARY KEYS"), 542 "IMPORTED KEYS": _show_parser("IMPORTED KEYS"), 543 "TERSE IMPORTED KEYS": _show_parser("IMPORTED KEYS"), 544 "UNIQUE KEYS": _show_parser("UNIQUE KEYS"), 545 "TERSE UNIQUE KEYS": _show_parser("UNIQUE KEYS"), 546 "SEQUENCES": _show_parser("SEQUENCES"), 547 "TERSE SEQUENCES": _show_parser("SEQUENCES"), 548 "COLUMNS": _show_parser("COLUMNS"), 549 "USERS": _show_parser("USERS"), 550 "TERSE USERS": _show_parser("USERS"), 551 "FUNCTIONS": _show_parser("FUNCTIONS"), 552 "PROCEDURES": _show_parser("PROCEDURES"), 553 "WAREHOUSES": _show_parser("WAREHOUSES"), 554 } 555 556 CONSTRAINT_PARSERS = { 557 **parser.Parser.CONSTRAINT_PARSERS, 558 "WITH": lambda self: self._parse_with_constraint(), 559 "MASKING": lambda self: self._parse_with_constraint(), 560 "PROJECTION": lambda self: self._parse_with_constraint(), 561 "TAG": lambda self: self._parse_with_constraint(), 562 } 563 564 STAGED_FILE_SINGLE_TOKENS = { 565 TokenType.DOT, 566 TokenType.MOD, 567 TokenType.SLASH, 568 } 569 570 FLATTEN_COLUMNS = ["SEQ", "KEY", "PATH", "INDEX", "VALUE", "THIS"] 571 572 SCHEMA_KINDS = {"OBJECTS", "TABLES", "VIEWS", "SEQUENCES", "UNIQUE KEYS", "IMPORTED KEYS"} 573 574 NON_TABLE_CREATABLES = {"STORAGE INTEGRATION", "TAG", "WAREHOUSE", "STREAMLIT"} 575 576 LAMBDAS = { 577 **parser.Parser.LAMBDAS, 578 TokenType.ARROW: lambda self, expressions: self.expression( 579 exp.Lambda, 580 this=self._replace_lambda( 581 self._parse_assignment(), 582 expressions, 583 ), 584 expressions=[e.this if isinstance(e, exp.Cast) else e for e in expressions], 585 ), 586 } 587 588 def _parse_use(self) -> exp.Use: 589 if self._match_text_seq("SECONDARY", "ROLES"): 590 this = self._match_texts(("ALL", "NONE")) and exp.var(self._prev.text.upper()) 591 roles = None if this else self._parse_csv(lambda: self._parse_table(schema=False)) 592 return self.expression( 593 exp.Use, kind="SECONDARY ROLES", this=this, expressions=roles 594 ) 595 596 return super()._parse_use() 597 598 def _negate_range( 599 self, this: t.Optional[exp.Expression] = None 600 ) -> t.Optional[exp.Expression]: 601 if not this: 602 return this 603 604 query = this.args.get("query") 605 if isinstance(this, exp.In) and isinstance(query, exp.Query): 606 # Snowflake treats `value NOT IN (subquery)` as `VALUE <> ALL (subquery)`, so 607 # we do this conversion here to avoid parsing it into `NOT value IN (subquery)` 608 # which can produce different results (most likely a SnowFlake bug). 609 # 610 # https://docs.snowflake.com/en/sql-reference/functions/in 611 # Context: https://github.com/tobymao/sqlglot/issues/3890 612 return self.expression( 613 exp.NEQ, this=this.this, expression=exp.All(this=query.unnest()) 614 ) 615 616 return self.expression(exp.Not, this=this) 617 618 def _parse_tag(self) -> exp.Tags: 619 return self.expression( 620 exp.Tags, 621 expressions=self._parse_wrapped_csv(self._parse_property), 622 ) 623 624 def _parse_with_constraint(self) -> t.Optional[exp.Expression]: 625 if self._prev.token_type != TokenType.WITH: 626 self._retreat(self._index - 1) 627 628 if self._match_text_seq("MASKING", "POLICY"): 629 policy = self._parse_column() 630 return self.expression( 631 exp.MaskingPolicyColumnConstraint, 632 this=policy.to_dot() if isinstance(policy, exp.Column) else policy, 633 expressions=self._match(TokenType.USING) 634 and self._parse_wrapped_csv(self._parse_id_var), 635 ) 636 if self._match_text_seq("PROJECTION", "POLICY"): 637 policy = self._parse_column() 638 return self.expression( 639 exp.ProjectionPolicyColumnConstraint, 640 this=policy.to_dot() if isinstance(policy, exp.Column) else policy, 641 ) 642 if self._match(TokenType.TAG): 643 return self._parse_tag() 644 645 return None 646 647 def _parse_with_property(self) -> t.Optional[exp.Expression] | t.List[exp.Expression]: 648 if self._match(TokenType.TAG): 649 return self._parse_tag() 650 651 return super()._parse_with_property() 652 653 def _parse_create(self) -> exp.Create | exp.Command: 654 expression = super()._parse_create() 655 if isinstance(expression, exp.Create) and expression.kind in self.NON_TABLE_CREATABLES: 656 # Replace the Table node with the enclosed Identifier 657 expression.this.replace(expression.this.this) 658 659 return expression 660 661 # https://docs.snowflake.com/en/sql-reference/functions/date_part.html 662 # https://docs.snowflake.com/en/sql-reference/functions-date-time.html#label-supported-date-time-parts 663 def _parse_date_part(self: Snowflake.Parser) -> t.Optional[exp.Expression]: 664 this = self._parse_var() or self._parse_type() 665 666 if not this: 667 return None 668 669 self._match(TokenType.COMMA) 670 expression = self._parse_bitwise() 671 this = map_date_part(this) 672 name = this.name.upper() 673 674 if name.startswith("EPOCH"): 675 if name == "EPOCH_MILLISECOND": 676 scale = 10**3 677 elif name == "EPOCH_MICROSECOND": 678 scale = 10**6 679 elif name == "EPOCH_NANOSECOND": 680 scale = 10**9 681 else: 682 scale = None 683 684 ts = self.expression(exp.Cast, this=expression, to=exp.DataType.build("TIMESTAMP")) 685 to_unix: exp.Expression = self.expression(exp.TimeToUnix, this=ts) 686 687 if scale: 688 to_unix = exp.Mul(this=to_unix, expression=exp.Literal.number(scale)) 689 690 return to_unix 691 692 return self.expression(exp.Extract, this=this, expression=expression) 693 694 def _parse_bracket_key_value(self, is_map: bool = False) -> t.Optional[exp.Expression]: 695 if is_map: 696 # Keys are strings in Snowflake's objects, see also: 697 # - https://docs.snowflake.com/en/sql-reference/data-types-semistructured 698 # - https://docs.snowflake.com/en/sql-reference/functions/object_construct 699 return self._parse_slice(self._parse_string()) 700 701 return self._parse_slice(self._parse_alias(self._parse_assignment(), explicit=True)) 702 703 def _parse_lateral(self) -> t.Optional[exp.Lateral]: 704 lateral = super()._parse_lateral() 705 if not lateral: 706 return lateral 707 708 if isinstance(lateral.this, exp.Explode): 709 table_alias = lateral.args.get("alias") 710 columns = [exp.to_identifier(col) for col in self.FLATTEN_COLUMNS] 711 if table_alias and not table_alias.args.get("columns"): 712 table_alias.set("columns", columns) 713 elif not table_alias: 714 exp.alias_(lateral, "_flattened", table=columns, copy=False) 715 716 return lateral 717 718 def _parse_table_parts( 719 self, schema: bool = False, is_db_reference: bool = False, wildcard: bool = False 720 ) -> exp.Table: 721 # https://docs.snowflake.com/en/user-guide/querying-stage 722 if self._match(TokenType.STRING, advance=False): 723 table = self._parse_string() 724 elif self._match_text_seq("@", advance=False): 725 table = self._parse_location_path() 726 else: 727 table = None 728 729 if table: 730 file_format = None 731 pattern = None 732 733 wrapped = self._match(TokenType.L_PAREN) 734 while self._curr and wrapped and not self._match(TokenType.R_PAREN): 735 if self._match_text_seq("FILE_FORMAT", "=>"): 736 file_format = self._parse_string() or super()._parse_table_parts( 737 is_db_reference=is_db_reference 738 ) 739 elif self._match_text_seq("PATTERN", "=>"): 740 pattern = self._parse_string() 741 else: 742 break 743 744 self._match(TokenType.COMMA) 745 746 table = self.expression(exp.Table, this=table, format=file_format, pattern=pattern) 747 else: 748 table = super()._parse_table_parts(schema=schema, is_db_reference=is_db_reference) 749 750 return table 751 752 def _parse_table( 753 self, 754 schema: bool = False, 755 joins: bool = False, 756 alias_tokens: t.Optional[t.Collection[TokenType]] = None, 757 parse_bracket: bool = False, 758 is_db_reference: bool = False, 759 parse_partition: bool = False, 760 ) -> t.Optional[exp.Expression]: 761 table = super()._parse_table( 762 schema=schema, 763 joins=joins, 764 alias_tokens=alias_tokens, 765 parse_bracket=parse_bracket, 766 is_db_reference=is_db_reference, 767 parse_partition=parse_partition, 768 ) 769 if isinstance(table, exp.Table) and isinstance(table.this, exp.TableFromRows): 770 table_from_rows = table.this 771 for arg in exp.TableFromRows.arg_types: 772 if arg != "this": 773 table_from_rows.set(arg, table.args.get(arg)) 774 775 table = table_from_rows 776 777 return table 778 779 def _parse_id_var( 780 self, 781 any_token: bool = True, 782 tokens: t.Optional[t.Collection[TokenType]] = None, 783 ) -> t.Optional[exp.Expression]: 784 if self._match_text_seq("IDENTIFIER", "("): 785 identifier = ( 786 super()._parse_id_var(any_token=any_token, tokens=tokens) 787 or self._parse_string() 788 ) 789 self._match_r_paren() 790 return self.expression(exp.Anonymous, this="IDENTIFIER", expressions=[identifier]) 791 792 return super()._parse_id_var(any_token=any_token, tokens=tokens) 793 794 def _parse_show_snowflake(self, this: str) -> exp.Show: 795 scope = None 796 scope_kind = None 797 798 # will identity SHOW TERSE SCHEMAS but not SHOW TERSE PRIMARY KEYS 799 # which is syntactically valid but has no effect on the output 800 terse = self._tokens[self._index - 2].text.upper() == "TERSE" 801 802 history = self._match_text_seq("HISTORY") 803 804 like = self._parse_string() if self._match(TokenType.LIKE) else None 805 806 if self._match(TokenType.IN): 807 if self._match_text_seq("ACCOUNT"): 808 scope_kind = "ACCOUNT" 809 elif self._match_text_seq("CLASS"): 810 scope_kind = "CLASS" 811 scope = self._parse_table_parts() 812 elif self._match_text_seq("APPLICATION"): 813 scope_kind = "APPLICATION" 814 if self._match_text_seq("PACKAGE"): 815 scope_kind += " PACKAGE" 816 scope = self._parse_table_parts() 817 elif self._match_set(self.DB_CREATABLES): 818 scope_kind = self._prev.text.upper() 819 if self._curr: 820 scope = self._parse_table_parts() 821 elif self._curr: 822 scope_kind = "SCHEMA" if this in self.SCHEMA_KINDS else "TABLE" 823 scope = self._parse_table_parts() 824 825 return self.expression( 826 exp.Show, 827 **{ 828 "terse": terse, 829 "this": this, 830 "history": history, 831 "like": like, 832 "scope": scope, 833 "scope_kind": scope_kind, 834 "starts_with": self._match_text_seq("STARTS", "WITH") and self._parse_string(), 835 "limit": self._parse_limit(), 836 "from": self._parse_string() if self._match(TokenType.FROM) else None, 837 "privileges": self._match_text_seq("WITH", "PRIVILEGES") 838 and self._parse_csv(lambda: self._parse_var(any_token=True, upper=True)), 839 }, 840 ) 841 842 def _parse_put(self) -> exp.Put | exp.Command: 843 if self._curr.token_type != TokenType.STRING: 844 return self._parse_as_command(self._prev) 845 846 return self.expression( 847 exp.Put, 848 this=self._parse_string(), 849 target=self._parse_location_path(), 850 properties=self._parse_properties(), 851 ) 852 853 def _parse_location_property(self) -> exp.LocationProperty: 854 self._match(TokenType.EQ) 855 return self.expression(exp.LocationProperty, this=self._parse_location_path()) 856 857 def _parse_file_location(self) -> t.Optional[exp.Expression]: 858 # Parse either a subquery or a staged file 859 return ( 860 self._parse_select(table=True, parse_subquery_alias=False) 861 if self._match(TokenType.L_PAREN, advance=False) 862 else self._parse_table_parts() 863 ) 864 865 def _parse_location_path(self) -> exp.Var: 866 parts = [self._advance_any(ignore_reserved=True)] 867 868 # We avoid consuming a comma token because external tables like @foo and @bar 869 # can be joined in a query with a comma separator, as well as closing paren 870 # in case of subqueries 871 while self._is_connected() and not self._match_set( 872 (TokenType.COMMA, TokenType.L_PAREN, TokenType.R_PAREN), advance=False 873 ): 874 parts.append(self._advance_any(ignore_reserved=True)) 875 876 return exp.var("".join(part.text for part in parts if part)) 877 878 def _parse_lambda_arg(self) -> t.Optional[exp.Expression]: 879 this = super()._parse_lambda_arg() 880 881 if not this: 882 return this 883 884 typ = self._parse_types() 885 886 if typ: 887 return self.expression(exp.Cast, this=this, to=typ) 888 889 return this 890 891 def _parse_foreign_key(self) -> exp.ForeignKey: 892 # inlineFK, the REFERENCES columns are implied 893 if self._match(TokenType.REFERENCES, advance=False): 894 return self.expression(exp.ForeignKey) 895 896 # outoflineFK, explicitly names the columns 897 return super()._parse_foreign_key() 898 899 class Tokenizer(tokens.Tokenizer): 900 STRING_ESCAPES = ["\\", "'"] 901 HEX_STRINGS = [("x'", "'"), ("X'", "'")] 902 RAW_STRINGS = ["$$"] 903 COMMENTS = ["--", "//", ("/*", "*/")] 904 NESTED_COMMENTS = False 905 906 KEYWORDS = { 907 **tokens.Tokenizer.KEYWORDS, 908 "FILE://": TokenType.URI_START, 909 "BYTEINT": TokenType.INT, 910 "CHAR VARYING": TokenType.VARCHAR, 911 "CHARACTER VARYING": TokenType.VARCHAR, 912 "EXCLUDE": TokenType.EXCEPT, 913 "ILIKE ANY": TokenType.ILIKE_ANY, 914 "LIKE ANY": TokenType.LIKE_ANY, 915 "MATCH_CONDITION": TokenType.MATCH_CONDITION, 916 "MATCH_RECOGNIZE": TokenType.MATCH_RECOGNIZE, 917 "MINUS": TokenType.EXCEPT, 918 "NCHAR VARYING": TokenType.VARCHAR, 919 "PUT": TokenType.PUT, 920 "REMOVE": TokenType.COMMAND, 921 "RM": TokenType.COMMAND, 922 "SAMPLE": TokenType.TABLE_SAMPLE, 923 "SQL_DOUBLE": TokenType.DOUBLE, 924 "SQL_VARCHAR": TokenType.VARCHAR, 925 "STORAGE INTEGRATION": TokenType.STORAGE_INTEGRATION, 926 "TAG": TokenType.TAG, 927 "TIMESTAMP_TZ": TokenType.TIMESTAMPTZ, 928 "TOP": TokenType.TOP, 929 "WAREHOUSE": TokenType.WAREHOUSE, 930 "STREAMLIT": TokenType.STREAMLIT, 931 } 932 KEYWORDS.pop("/*+") 933 934 SINGLE_TOKENS = { 935 **tokens.Tokenizer.SINGLE_TOKENS, 936 "$": TokenType.PARAMETER, 937 } 938 939 VAR_SINGLE_TOKENS = {"$"} 940 941 COMMANDS = tokens.Tokenizer.COMMANDS - {TokenType.SHOW} 942 943 class Generator(generator.Generator): 944 PARAMETER_TOKEN = "$" 945 MATCHED_BY_SOURCE = False 946 SINGLE_STRING_INTERVAL = True 947 JOIN_HINTS = False 948 TABLE_HINTS = False 949 QUERY_HINTS = False 950 AGGREGATE_FILTER_SUPPORTED = False 951 SUPPORTS_TABLE_COPY = False 952 COLLATE_IS_FUNC = True 953 LIMIT_ONLY_LITERALS = True 954 JSON_KEY_VALUE_PAIR_SEP = "," 955 INSERT_OVERWRITE = " OVERWRITE INTO" 956 STRUCT_DELIMITER = ("(", ")") 957 COPY_PARAMS_ARE_WRAPPED = False 958 COPY_PARAMS_EQ_REQUIRED = True 959 STAR_EXCEPT = "EXCLUDE" 960 SUPPORTS_EXPLODING_PROJECTIONS = False 961 ARRAY_CONCAT_IS_VAR_LEN = False 962 SUPPORTS_CONVERT_TIMEZONE = True 963 EXCEPT_INTERSECT_SUPPORT_ALL_CLAUSE = False 964 SUPPORTS_MEDIAN = True 965 ARRAY_SIZE_NAME = "ARRAY_SIZE" 966 967 TRANSFORMS = { 968 **generator.Generator.TRANSFORMS, 969 exp.ApproxDistinct: rename_func("APPROX_COUNT_DISTINCT"), 970 exp.ArgMax: rename_func("MAX_BY"), 971 exp.ArgMin: rename_func("MIN_BY"), 972 exp.Array: inline_array_sql, 973 exp.ArrayConcat: lambda self, e: self.arrayconcat_sql(e, name="ARRAY_CAT"), 974 exp.ArrayContains: lambda self, e: self.func("ARRAY_CONTAINS", e.expression, e.this), 975 exp.AtTimeZone: lambda self, e: self.func( 976 "CONVERT_TIMEZONE", e.args.get("zone"), e.this 977 ), 978 exp.BitwiseOr: rename_func("BITOR"), 979 exp.BitwiseXor: rename_func("BITXOR"), 980 exp.BitwiseLeftShift: rename_func("BITSHIFTLEFT"), 981 exp.BitwiseRightShift: rename_func("BITSHIFTRIGHT"), 982 exp.Create: transforms.preprocess([_flatten_structured_types_unless_iceberg]), 983 exp.DateAdd: date_delta_sql("DATEADD"), 984 exp.DateDiff: date_delta_sql("DATEDIFF"), 985 exp.DatetimeAdd: date_delta_sql("TIMESTAMPADD"), 986 exp.DatetimeDiff: timestampdiff_sql, 987 exp.DateStrToDate: datestrtodate_sql, 988 exp.DayOfMonth: rename_func("DAYOFMONTH"), 989 exp.DayOfWeek: rename_func("DAYOFWEEK"), 990 exp.DayOfYear: rename_func("DAYOFYEAR"), 991 exp.Explode: rename_func("FLATTEN"), 992 exp.Extract: rename_func("DATE_PART"), 993 exp.FromTimeZone: lambda self, e: self.func( 994 "CONVERT_TIMEZONE", e.args.get("zone"), "'UTC'", e.this 995 ), 996 exp.GenerateSeries: lambda self, e: self.func( 997 "ARRAY_GENERATE_RANGE", e.args["start"], e.args["end"] + 1, e.args.get("step") 998 ), 999 exp.GroupConcat: lambda self, e: groupconcat_sql(self, e, sep=""), 1000 exp.If: if_sql(name="IFF", false_value="NULL"), 1001 exp.JSONExtractArray: _json_extract_value_array_sql, 1002 exp.JSONExtractScalar: lambda self, e: self.func( 1003 "JSON_EXTRACT_PATH_TEXT", e.this, e.expression 1004 ), 1005 exp.JSONObject: lambda self, e: self.func("OBJECT_CONSTRUCT_KEEP_NULL", *e.expressions), 1006 exp.JSONPathRoot: lambda *_: "", 1007 exp.JSONValueArray: _json_extract_value_array_sql, 1008 exp.LogicalAnd: rename_func("BOOLAND_AGG"), 1009 exp.LogicalOr: rename_func("BOOLOR_AGG"), 1010 exp.Map: lambda self, e: var_map_sql(self, e, "OBJECT_CONSTRUCT"), 1011 exp.MakeInterval: no_make_interval_sql, 1012 exp.Max: max_or_greatest, 1013 exp.Min: min_or_least, 1014 exp.ParseJSON: lambda self, e: self.func( 1015 "TRY_PARSE_JSON" if e.args.get("safe") else "PARSE_JSON", e.this 1016 ), 1017 exp.PartitionedByProperty: lambda self, e: f"PARTITION BY {self.sql(e, 'this')}", 1018 exp.PercentileCont: transforms.preprocess( 1019 [transforms.add_within_group_for_percentiles] 1020 ), 1021 exp.PercentileDisc: transforms.preprocess( 1022 [transforms.add_within_group_for_percentiles] 1023 ), 1024 exp.Pivot: transforms.preprocess([_unqualify_pivot_columns]), 1025 exp.RegexpExtract: _regexpextract_sql, 1026 exp.RegexpExtractAll: _regexpextract_sql, 1027 exp.RegexpILike: _regexpilike_sql, 1028 exp.Rand: rename_func("RANDOM"), 1029 exp.Select: transforms.preprocess( 1030 [ 1031 transforms.eliminate_distinct_on, 1032 transforms.explode_to_unnest(), 1033 transforms.eliminate_semi_and_anti_joins, 1034 _transform_generate_date_array, 1035 ] 1036 ), 1037 exp.SHA: rename_func("SHA1"), 1038 exp.StarMap: rename_func("OBJECT_CONSTRUCT"), 1039 exp.StartsWith: rename_func("STARTSWITH"), 1040 exp.StrPosition: lambda self, e: strposition_sql( 1041 self, e, func_name="CHARINDEX", supports_position=True 1042 ), 1043 exp.StrToDate: lambda self, e: self.func("DATE", e.this, self.format_time(e)), 1044 exp.Stuff: rename_func("INSERT"), 1045 exp.TimeAdd: date_delta_sql("TIMEADD"), 1046 exp.Timestamp: no_timestamp_sql, 1047 exp.TimestampAdd: date_delta_sql("TIMESTAMPADD"), 1048 exp.TimestampDiff: lambda self, e: self.func( 1049 "TIMESTAMPDIFF", e.unit, e.expression, e.this 1050 ), 1051 exp.TimestampTrunc: timestamptrunc_sql(), 1052 exp.TimeStrToTime: timestrtotime_sql, 1053 exp.TimeToUnix: lambda self, e: f"EXTRACT(epoch_second FROM {self.sql(e, 'this')})", 1054 exp.ToArray: rename_func("TO_ARRAY"), 1055 exp.ToChar: lambda self, e: self.function_fallback_sql(e), 1056 exp.ToDouble: rename_func("TO_DOUBLE"), 1057 exp.TsOrDsAdd: date_delta_sql("DATEADD", cast=True), 1058 exp.TsOrDsDiff: date_delta_sql("DATEDIFF"), 1059 exp.TsOrDsToDate: lambda self, e: self.func( 1060 "TRY_TO_DATE" if e.args.get("safe") else "TO_DATE", e.this, self.format_time(e) 1061 ), 1062 exp.TsOrDsToTime: lambda self, e: self.func( 1063 "TRY_TO_TIME" if e.args.get("safe") else "TO_TIME", e.this, self.format_time(e) 1064 ), 1065 exp.Unhex: rename_func("HEX_DECODE_BINARY"), 1066 exp.UnixToTime: rename_func("TO_TIMESTAMP"), 1067 exp.Uuid: rename_func("UUID_STRING"), 1068 exp.VarMap: lambda self, e: var_map_sql(self, e, "OBJECT_CONSTRUCT"), 1069 exp.WeekOfYear: rename_func("WEEKOFYEAR"), 1070 exp.Xor: rename_func("BOOLXOR"), 1071 exp.Levenshtein: unsupported_args("ins_cost", "del_cost", "sub_cost")( 1072 rename_func("EDITDISTANCE") 1073 ), 1074 } 1075 1076 SUPPORTED_JSON_PATH_PARTS = { 1077 exp.JSONPathKey, 1078 exp.JSONPathRoot, 1079 exp.JSONPathSubscript, 1080 } 1081 1082 TYPE_MAPPING = { 1083 **generator.Generator.TYPE_MAPPING, 1084 exp.DataType.Type.NESTED: "OBJECT", 1085 exp.DataType.Type.STRUCT: "OBJECT", 1086 } 1087 1088 TOKEN_MAPPING = { 1089 TokenType.AUTO_INCREMENT: "AUTOINCREMENT", 1090 } 1091 1092 PROPERTIES_LOCATION = { 1093 **generator.Generator.PROPERTIES_LOCATION, 1094 exp.SetProperty: exp.Properties.Location.UNSUPPORTED, 1095 exp.VolatileProperty: exp.Properties.Location.UNSUPPORTED, 1096 } 1097 1098 UNSUPPORTED_VALUES_EXPRESSIONS = { 1099 exp.Map, 1100 exp.StarMap, 1101 exp.Struct, 1102 exp.VarMap, 1103 } 1104 1105 def with_properties(self, properties: exp.Properties) -> str: 1106 return self.properties(properties, wrapped=False, prefix=self.sep(""), sep=" ") 1107 1108 def values_sql(self, expression: exp.Values, values_as_table: bool = True) -> str: 1109 if expression.find(*self.UNSUPPORTED_VALUES_EXPRESSIONS): 1110 values_as_table = False 1111 1112 return super().values_sql(expression, values_as_table=values_as_table) 1113 1114 def datatype_sql(self, expression: exp.DataType) -> str: 1115 expressions = expression.expressions 1116 if ( 1117 expressions 1118 and expression.is_type(*exp.DataType.STRUCT_TYPES) 1119 and any(isinstance(field_type, exp.DataType) for field_type in expressions) 1120 ): 1121 # The correct syntax is OBJECT [ (<key> <value_type [NOT NULL] [, ...]) ] 1122 return "OBJECT" 1123 1124 return super().datatype_sql(expression) 1125 1126 def tonumber_sql(self, expression: exp.ToNumber) -> str: 1127 return self.func( 1128 "TO_NUMBER", 1129 expression.this, 1130 expression.args.get("format"), 1131 expression.args.get("precision"), 1132 expression.args.get("scale"), 1133 ) 1134 1135 def timestampfromparts_sql(self, expression: exp.TimestampFromParts) -> str: 1136 milli = expression.args.get("milli") 1137 if milli is not None: 1138 milli_to_nano = milli.pop() * exp.Literal.number(1000000) 1139 expression.set("nano", milli_to_nano) 1140 1141 return rename_func("TIMESTAMP_FROM_PARTS")(self, expression) 1142 1143 def cast_sql(self, expression: exp.Cast, safe_prefix: t.Optional[str] = None) -> str: 1144 if expression.is_type(exp.DataType.Type.GEOGRAPHY): 1145 return self.func("TO_GEOGRAPHY", expression.this) 1146 if expression.is_type(exp.DataType.Type.GEOMETRY): 1147 return self.func("TO_GEOMETRY", expression.this) 1148 1149 return super().cast_sql(expression, safe_prefix=safe_prefix) 1150 1151 def trycast_sql(self, expression: exp.TryCast) -> str: 1152 value = expression.this 1153 1154 if value.type is None: 1155 from sqlglot.optimizer.annotate_types import annotate_types 1156 1157 value = annotate_types(value) 1158 1159 if value.is_type(*exp.DataType.TEXT_TYPES, exp.DataType.Type.UNKNOWN): 1160 return super().trycast_sql(expression) 1161 1162 # TRY_CAST only works for string values in Snowflake 1163 return self.cast_sql(expression) 1164 1165 def log_sql(self, expression: exp.Log) -> str: 1166 if not expression.expression: 1167 return self.func("LN", expression.this) 1168 1169 return super().log_sql(expression) 1170 1171 def unnest_sql(self, expression: exp.Unnest) -> str: 1172 unnest_alias = expression.args.get("alias") 1173 offset = expression.args.get("offset") 1174 1175 columns = [ 1176 exp.to_identifier("seq"), 1177 exp.to_identifier("key"), 1178 exp.to_identifier("path"), 1179 offset.pop() if isinstance(offset, exp.Expression) else exp.to_identifier("index"), 1180 seq_get(unnest_alias.columns if unnest_alias else [], 0) 1181 or exp.to_identifier("value"), 1182 exp.to_identifier("this"), 1183 ] 1184 1185 if unnest_alias: 1186 unnest_alias.set("columns", columns) 1187 else: 1188 unnest_alias = exp.TableAlias(this="_u", columns=columns) 1189 1190 table_input = self.sql(expression.expressions[0]) 1191 if not table_input.startswith("INPUT =>"): 1192 table_input = f"INPUT => {table_input}" 1193 1194 explode = f"TABLE(FLATTEN({table_input}))" 1195 alias = self.sql(unnest_alias) 1196 alias = f" AS {alias}" if alias else "" 1197 return f"{explode}{alias}" 1198 1199 def show_sql(self, expression: exp.Show) -> str: 1200 terse = "TERSE " if expression.args.get("terse") else "" 1201 history = " HISTORY" if expression.args.get("history") else "" 1202 like = self.sql(expression, "like") 1203 like = f" LIKE {like}" if like else "" 1204 1205 scope = self.sql(expression, "scope") 1206 scope = f" {scope}" if scope else "" 1207 1208 scope_kind = self.sql(expression, "scope_kind") 1209 if scope_kind: 1210 scope_kind = f" IN {scope_kind}" 1211 1212 starts_with = self.sql(expression, "starts_with") 1213 if starts_with: 1214 starts_with = f" STARTS WITH {starts_with}" 1215 1216 limit = self.sql(expression, "limit") 1217 1218 from_ = self.sql(expression, "from") 1219 if from_: 1220 from_ = f" FROM {from_}" 1221 1222 privileges = self.expressions(expression, key="privileges", flat=True) 1223 privileges = f" WITH PRIVILEGES {privileges}" if privileges else "" 1224 1225 return f"SHOW {terse}{expression.name}{history}{like}{scope_kind}{scope}{starts_with}{limit}{from_}{privileges}" 1226 1227 def describe_sql(self, expression: exp.Describe) -> str: 1228 # Default to table if kind is unknown 1229 kind_value = expression.args.get("kind") or "TABLE" 1230 kind = f" {kind_value}" if kind_value else "" 1231 this = f" {self.sql(expression, 'this')}" 1232 expressions = self.expressions(expression, flat=True) 1233 expressions = f" {expressions}" if expressions else "" 1234 return f"DESCRIBE{kind}{this}{expressions}" 1235 1236 def generatedasidentitycolumnconstraint_sql( 1237 self, expression: exp.GeneratedAsIdentityColumnConstraint 1238 ) -> str: 1239 start = expression.args.get("start") 1240 start = f" START {start}" if start else "" 1241 increment = expression.args.get("increment") 1242 increment = f" INCREMENT {increment}" if increment else "" 1243 return f"AUTOINCREMENT{start}{increment}" 1244 1245 def cluster_sql(self, expression: exp.Cluster) -> str: 1246 return f"CLUSTER BY ({self.expressions(expression, flat=True)})" 1247 1248 def struct_sql(self, expression: exp.Struct) -> str: 1249 keys = [] 1250 values = [] 1251 1252 for i, e in enumerate(expression.expressions): 1253 if isinstance(e, exp.PropertyEQ): 1254 keys.append( 1255 exp.Literal.string(e.name) if isinstance(e.this, exp.Identifier) else e.this 1256 ) 1257 values.append(e.expression) 1258 else: 1259 keys.append(exp.Literal.string(f"_{i}")) 1260 values.append(e) 1261 1262 return self.func("OBJECT_CONSTRUCT", *flatten(zip(keys, values))) 1263 1264 @unsupported_args("weight", "accuracy") 1265 def approxquantile_sql(self, expression: exp.ApproxQuantile) -> str: 1266 return self.func("APPROX_PERCENTILE", expression.this, expression.args.get("quantile")) 1267 1268 def alterset_sql(self, expression: exp.AlterSet) -> str: 1269 exprs = self.expressions(expression, flat=True) 1270 exprs = f" {exprs}" if exprs else "" 1271 file_format = self.expressions(expression, key="file_format", flat=True, sep=" ") 1272 file_format = f" STAGE_FILE_FORMAT = ({file_format})" if file_format else "" 1273 copy_options = self.expressions(expression, key="copy_options", flat=True, sep=" ") 1274 copy_options = f" STAGE_COPY_OPTIONS = ({copy_options})" if copy_options else "" 1275 tag = self.expressions(expression, key="tag", flat=True) 1276 tag = f" TAG {tag}" if tag else "" 1277 1278 return f"SET{exprs}{file_format}{copy_options}{tag}" 1279 1280 def strtotime_sql(self, expression: exp.StrToTime): 1281 safe_prefix = "TRY_" if expression.args.get("safe") else "" 1282 return self.func( 1283 f"{safe_prefix}TO_TIMESTAMP", expression.this, self.format_time(expression) 1284 ) 1285 1286 def timestampsub_sql(self, expression: exp.TimestampSub): 1287 return self.sql( 1288 exp.TimestampAdd( 1289 this=expression.this, 1290 expression=expression.expression * -1, 1291 unit=expression.unit, 1292 ) 1293 ) 1294 1295 def jsonextract_sql(self, expression: exp.JSONExtract): 1296 this = expression.this 1297 1298 # JSON strings are valid coming from other dialects such as BQ 1299 return self.func( 1300 "GET_PATH", 1301 exp.ParseJSON(this=this) if this.is_string else this, 1302 expression.expression, 1303 ) 1304 1305 def timetostr_sql(self, expression: exp.TimeToStr) -> str: 1306 this = expression.this 1307 if not isinstance(this, exp.TsOrDsToTimestamp): 1308 this = exp.cast(this, exp.DataType.Type.TIMESTAMP) 1309 1310 return self.func("TO_CHAR", this, self.format_time(expression)) 1311 1312 def datesub_sql(self, expression: exp.DateSub) -> str: 1313 value = expression.expression 1314 if value: 1315 value.replace(value * (-1)) 1316 else: 1317 self.unsupported("DateSub cannot be transpiled if the subtracted count is unknown") 1318 1319 return date_delta_sql("DATEADD")(self, expression) 1320 1321 def select_sql(self, expression: exp.Select) -> str: 1322 limit = expression.args.get("limit") 1323 offset = expression.args.get("offset") 1324 if offset and not limit: 1325 expression.limit(exp.Null(), copy=False) 1326 return super().select_sql(expression)
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.
376 def quote_identifier(self, expression: E, identify: bool = True) -> E: 377 # This disables quoting DUAL in SELECT ... FROM DUAL, because Snowflake treats an 378 # unquoted DUAL keyword in a special way and does not map it to a user-defined table 379 if ( 380 isinstance(expression, exp.Identifier) 381 and isinstance(expression.parent, exp.Table) 382 and expression.name.lower() == "dual" 383 ): 384 return expression # type: ignore 385 386 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 (
).
388 class JSONPathTokenizer(jsonpath.JSONPathTokenizer): 389 SINGLE_TOKENS = jsonpath.JSONPathTokenizer.SINGLE_TOKENS.copy() 390 SINGLE_TOKENS.pop("$")
Inherited Members
- sqlglot.tokens.Tokenizer
- Tokenizer
- BIT_STRINGS
- BYTE_STRINGS
- HEX_STRINGS
- RAW_STRINGS
- HEREDOC_STRINGS
- UNICODE_STRINGS
- IDENTIFIERS
- QUOTES
- VAR_SINGLE_TOKENS
- HEREDOC_TAG_IS_IDENTIFIER
- HEREDOC_STRING_ALTERNATIVE
- STRING_ESCAPES_ALLOWED_IN_RAW_STRINGS
- NESTED_COMMENTS
- HINT_START
- TOKENS_PRECEDING_HINT
- WHITE_SPACE
- COMMANDS
- COMMAND_PREFIX_TOKENS
- NUMERIC_LITERALS
- COMMENTS
- dialect
- use_rs_tokenizer
- reset
- tokenize
- tokenize_rs
- size
- sql
- tokens
392 class Parser(parser.Parser): 393 IDENTIFY_PIVOT_STRINGS = True 394 DEFAULT_SAMPLING_METHOD = "BERNOULLI" 395 COLON_IS_VARIANT_EXTRACT = True 396 397 ID_VAR_TOKENS = { 398 *parser.Parser.ID_VAR_TOKENS, 399 TokenType.MATCH_CONDITION, 400 } 401 402 TABLE_ALIAS_TOKENS = parser.Parser.TABLE_ALIAS_TOKENS | {TokenType.WINDOW} 403 TABLE_ALIAS_TOKENS.discard(TokenType.MATCH_CONDITION) 404 405 FUNCTIONS = { 406 **parser.Parser.FUNCTIONS, 407 "APPROX_PERCENTILE": exp.ApproxQuantile.from_arg_list, 408 "ARRAY_CONSTRUCT": lambda args: exp.Array(expressions=args), 409 "ARRAY_CONTAINS": lambda args: exp.ArrayContains( 410 this=seq_get(args, 1), expression=seq_get(args, 0) 411 ), 412 "ARRAY_GENERATE_RANGE": lambda args: exp.GenerateSeries( 413 # ARRAY_GENERATE_RANGE has an exlusive end; we normalize it to be inclusive 414 start=seq_get(args, 0), 415 end=exp.Sub(this=seq_get(args, 1), expression=exp.Literal.number(1)), 416 step=seq_get(args, 2), 417 ), 418 "BITXOR": _build_bitwise(exp.BitwiseXor, "BITXOR"), 419 "BIT_XOR": _build_bitwise(exp.BitwiseXor, "BITXOR"), 420 "BITOR": _build_bitwise(exp.BitwiseOr, "BITOR"), 421 "BIT_OR": _build_bitwise(exp.BitwiseOr, "BITOR"), 422 "BITSHIFTLEFT": _build_bitwise(exp.BitwiseLeftShift, "BITSHIFTLEFT"), 423 "BIT_SHIFTLEFT": _build_bitwise(exp.BitwiseLeftShift, "BIT_SHIFTLEFT"), 424 "BITSHIFTRIGHT": _build_bitwise(exp.BitwiseRightShift, "BITSHIFTRIGHT"), 425 "BIT_SHIFTRIGHT": _build_bitwise(exp.BitwiseRightShift, "BIT_SHIFTRIGHT"), 426 "BOOLXOR": _build_bitwise(exp.Xor, "BOOLXOR"), 427 "DATE": _build_datetime("DATE", exp.DataType.Type.DATE), 428 "DATE_TRUNC": _date_trunc_to_time, 429 "DATEADD": _build_date_time_add(exp.DateAdd), 430 "DATEDIFF": _build_datediff, 431 "DIV0": _build_if_from_div0, 432 "EDITDISTANCE": lambda args: exp.Levenshtein( 433 this=seq_get(args, 0), expression=seq_get(args, 1), max_dist=seq_get(args, 2) 434 ), 435 "FLATTEN": exp.Explode.from_arg_list, 436 "GET_PATH": lambda args, dialect: exp.JSONExtract( 437 this=seq_get(args, 0), expression=dialect.to_json_path(seq_get(args, 1)) 438 ), 439 "HEX_DECODE_BINARY": exp.Unhex.from_arg_list, 440 "IFF": exp.If.from_arg_list, 441 "LAST_DAY": lambda args: exp.LastDay( 442 this=seq_get(args, 0), unit=map_date_part(seq_get(args, 1)) 443 ), 444 "LEN": lambda args: exp.Length(this=seq_get(args, 0), binary=True), 445 "LENGTH": lambda args: exp.Length(this=seq_get(args, 0), binary=True), 446 "NULLIFZERO": _build_if_from_nullifzero, 447 "OBJECT_CONSTRUCT": _build_object_construct, 448 "REGEXP_EXTRACT_ALL": _build_regexp_extract(exp.RegexpExtractAll), 449 "REGEXP_REPLACE": _build_regexp_replace, 450 "REGEXP_SUBSTR": _build_regexp_extract(exp.RegexpExtract), 451 "REGEXP_SUBSTR_ALL": _build_regexp_extract(exp.RegexpExtractAll), 452 "RLIKE": exp.RegexpLike.from_arg_list, 453 "SQUARE": lambda args: exp.Pow(this=seq_get(args, 0), expression=exp.Literal.number(2)), 454 "TABLE": lambda args: exp.TableFromRows(this=seq_get(args, 0)), 455 "TIMEADD": _build_date_time_add(exp.TimeAdd), 456 "TIMEDIFF": _build_datediff, 457 "TIMESTAMPADD": _build_date_time_add(exp.DateAdd), 458 "TIMESTAMPDIFF": _build_datediff, 459 "TIMESTAMPFROMPARTS": build_timestamp_from_parts, 460 "TIMESTAMP_FROM_PARTS": build_timestamp_from_parts, 461 "TIMESTAMPNTZFROMPARTS": build_timestamp_from_parts, 462 "TIMESTAMP_NTZ_FROM_PARTS": build_timestamp_from_parts, 463 "TRY_PARSE_JSON": lambda args: exp.ParseJSON(this=seq_get(args, 0), safe=True), 464 "TRY_TO_DATE": _build_datetime("TRY_TO_DATE", exp.DataType.Type.DATE, safe=True), 465 "TRY_TO_TIME": _build_datetime("TRY_TO_TIME", exp.DataType.Type.TIME, safe=True), 466 "TRY_TO_TIMESTAMP": _build_datetime( 467 "TRY_TO_TIMESTAMP", exp.DataType.Type.TIMESTAMP, safe=True 468 ), 469 "TO_CHAR": build_timetostr_or_tochar, 470 "TO_DATE": _build_datetime("TO_DATE", exp.DataType.Type.DATE), 471 "TO_NUMBER": lambda args: exp.ToNumber( 472 this=seq_get(args, 0), 473 format=seq_get(args, 1), 474 precision=seq_get(args, 2), 475 scale=seq_get(args, 3), 476 ), 477 "TO_TIME": _build_datetime("TO_TIME", exp.DataType.Type.TIME), 478 "TO_TIMESTAMP": _build_datetime("TO_TIMESTAMP", exp.DataType.Type.TIMESTAMP), 479 "TO_TIMESTAMP_LTZ": _build_datetime("TO_TIMESTAMP_LTZ", exp.DataType.Type.TIMESTAMPLTZ), 480 "TO_TIMESTAMP_NTZ": _build_datetime("TO_TIMESTAMP_NTZ", exp.DataType.Type.TIMESTAMP), 481 "TO_TIMESTAMP_TZ": _build_datetime("TO_TIMESTAMP_TZ", exp.DataType.Type.TIMESTAMPTZ), 482 "TO_VARCHAR": exp.ToChar.from_arg_list, 483 "ZEROIFNULL": _build_if_from_zeroifnull, 484 } 485 486 FUNCTION_PARSERS = { 487 **parser.Parser.FUNCTION_PARSERS, 488 "DATE_PART": lambda self: self._parse_date_part(), 489 "OBJECT_CONSTRUCT_KEEP_NULL": lambda self: self._parse_json_object(), 490 "LISTAGG": lambda self: self._parse_string_agg(), 491 } 492 FUNCTION_PARSERS.pop("TRIM") 493 494 TIMESTAMPS = parser.Parser.TIMESTAMPS - {TokenType.TIME} 495 496 RANGE_PARSERS = { 497 **parser.Parser.RANGE_PARSERS, 498 TokenType.LIKE_ANY: parser.binary_range_parser(exp.LikeAny), 499 TokenType.ILIKE_ANY: parser.binary_range_parser(exp.ILikeAny), 500 } 501 502 ALTER_PARSERS = { 503 **parser.Parser.ALTER_PARSERS, 504 "UNSET": lambda self: self.expression( 505 exp.Set, 506 tag=self._match_text_seq("TAG"), 507 expressions=self._parse_csv(self._parse_id_var), 508 unset=True, 509 ), 510 } 511 512 STATEMENT_PARSERS = { 513 **parser.Parser.STATEMENT_PARSERS, 514 TokenType.PUT: lambda self: self._parse_put(), 515 TokenType.SHOW: lambda self: self._parse_show(), 516 } 517 518 PROPERTY_PARSERS = { 519 **parser.Parser.PROPERTY_PARSERS, 520 "LOCATION": lambda self: self._parse_location_property(), 521 "TAG": lambda self: self._parse_tag(), 522 } 523 524 TYPE_CONVERTERS = { 525 # https://docs.snowflake.com/en/sql-reference/data-types-numeric#number 526 exp.DataType.Type.DECIMAL: build_default_decimal_type(precision=38, scale=0), 527 } 528 529 SHOW_PARSERS = { 530 "DATABASES": _show_parser("DATABASES"), 531 "TERSE DATABASES": _show_parser("DATABASES"), 532 "SCHEMAS": _show_parser("SCHEMAS"), 533 "TERSE SCHEMAS": _show_parser("SCHEMAS"), 534 "OBJECTS": _show_parser("OBJECTS"), 535 "TERSE OBJECTS": _show_parser("OBJECTS"), 536 "TABLES": _show_parser("TABLES"), 537 "TERSE TABLES": _show_parser("TABLES"), 538 "VIEWS": _show_parser("VIEWS"), 539 "TERSE VIEWS": _show_parser("VIEWS"), 540 "PRIMARY KEYS": _show_parser("PRIMARY KEYS"), 541 "TERSE PRIMARY KEYS": _show_parser("PRIMARY KEYS"), 542 "IMPORTED KEYS": _show_parser("IMPORTED KEYS"), 543 "TERSE IMPORTED KEYS": _show_parser("IMPORTED KEYS"), 544 "UNIQUE KEYS": _show_parser("UNIQUE KEYS"), 545 "TERSE UNIQUE KEYS": _show_parser("UNIQUE KEYS"), 546 "SEQUENCES": _show_parser("SEQUENCES"), 547 "TERSE SEQUENCES": _show_parser("SEQUENCES"), 548 "COLUMNS": _show_parser("COLUMNS"), 549 "USERS": _show_parser("USERS"), 550 "TERSE USERS": _show_parser("USERS"), 551 "FUNCTIONS": _show_parser("FUNCTIONS"), 552 "PROCEDURES": _show_parser("PROCEDURES"), 553 "WAREHOUSES": _show_parser("WAREHOUSES"), 554 } 555 556 CONSTRAINT_PARSERS = { 557 **parser.Parser.CONSTRAINT_PARSERS, 558 "WITH": lambda self: self._parse_with_constraint(), 559 "MASKING": lambda self: self._parse_with_constraint(), 560 "PROJECTION": lambda self: self._parse_with_constraint(), 561 "TAG": lambda self: self._parse_with_constraint(), 562 } 563 564 STAGED_FILE_SINGLE_TOKENS = { 565 TokenType.DOT, 566 TokenType.MOD, 567 TokenType.SLASH, 568 } 569 570 FLATTEN_COLUMNS = ["SEQ", "KEY", "PATH", "INDEX", "VALUE", "THIS"] 571 572 SCHEMA_KINDS = {"OBJECTS", "TABLES", "VIEWS", "SEQUENCES", "UNIQUE KEYS", "IMPORTED KEYS"} 573 574 NON_TABLE_CREATABLES = {"STORAGE INTEGRATION", "TAG", "WAREHOUSE", "STREAMLIT"} 575 576 LAMBDAS = { 577 **parser.Parser.LAMBDAS, 578 TokenType.ARROW: lambda self, expressions: self.expression( 579 exp.Lambda, 580 this=self._replace_lambda( 581 self._parse_assignment(), 582 expressions, 583 ), 584 expressions=[e.this if isinstance(e, exp.Cast) else e for e in expressions], 585 ), 586 } 587 588 def _parse_use(self) -> exp.Use: 589 if self._match_text_seq("SECONDARY", "ROLES"): 590 this = self._match_texts(("ALL", "NONE")) and exp.var(self._prev.text.upper()) 591 roles = None if this else self._parse_csv(lambda: self._parse_table(schema=False)) 592 return self.expression( 593 exp.Use, kind="SECONDARY ROLES", this=this, expressions=roles 594 ) 595 596 return super()._parse_use() 597 598 def _negate_range( 599 self, this: t.Optional[exp.Expression] = None 600 ) -> t.Optional[exp.Expression]: 601 if not this: 602 return this 603 604 query = this.args.get("query") 605 if isinstance(this, exp.In) and isinstance(query, exp.Query): 606 # Snowflake treats `value NOT IN (subquery)` as `VALUE <> ALL (subquery)`, so 607 # we do this conversion here to avoid parsing it into `NOT value IN (subquery)` 608 # which can produce different results (most likely a SnowFlake bug). 609 # 610 # https://docs.snowflake.com/en/sql-reference/functions/in 611 # Context: https://github.com/tobymao/sqlglot/issues/3890 612 return self.expression( 613 exp.NEQ, this=this.this, expression=exp.All(this=query.unnest()) 614 ) 615 616 return self.expression(exp.Not, this=this) 617 618 def _parse_tag(self) -> exp.Tags: 619 return self.expression( 620 exp.Tags, 621 expressions=self._parse_wrapped_csv(self._parse_property), 622 ) 623 624 def _parse_with_constraint(self) -> t.Optional[exp.Expression]: 625 if self._prev.token_type != TokenType.WITH: 626 self._retreat(self._index - 1) 627 628 if self._match_text_seq("MASKING", "POLICY"): 629 policy = self._parse_column() 630 return self.expression( 631 exp.MaskingPolicyColumnConstraint, 632 this=policy.to_dot() if isinstance(policy, exp.Column) else policy, 633 expressions=self._match(TokenType.USING) 634 and self._parse_wrapped_csv(self._parse_id_var), 635 ) 636 if self._match_text_seq("PROJECTION", "POLICY"): 637 policy = self._parse_column() 638 return self.expression( 639 exp.ProjectionPolicyColumnConstraint, 640 this=policy.to_dot() if isinstance(policy, exp.Column) else policy, 641 ) 642 if self._match(TokenType.TAG): 643 return self._parse_tag() 644 645 return None 646 647 def _parse_with_property(self) -> t.Optional[exp.Expression] | t.List[exp.Expression]: 648 if self._match(TokenType.TAG): 649 return self._parse_tag() 650 651 return super()._parse_with_property() 652 653 def _parse_create(self) -> exp.Create | exp.Command: 654 expression = super()._parse_create() 655 if isinstance(expression, exp.Create) and expression.kind in self.NON_TABLE_CREATABLES: 656 # Replace the Table node with the enclosed Identifier 657 expression.this.replace(expression.this.this) 658 659 return expression 660 661 # https://docs.snowflake.com/en/sql-reference/functions/date_part.html 662 # https://docs.snowflake.com/en/sql-reference/functions-date-time.html#label-supported-date-time-parts 663 def _parse_date_part(self: Snowflake.Parser) -> t.Optional[exp.Expression]: 664 this = self._parse_var() or self._parse_type() 665 666 if not this: 667 return None 668 669 self._match(TokenType.COMMA) 670 expression = self._parse_bitwise() 671 this = map_date_part(this) 672 name = this.name.upper() 673 674 if name.startswith("EPOCH"): 675 if name == "EPOCH_MILLISECOND": 676 scale = 10**3 677 elif name == "EPOCH_MICROSECOND": 678 scale = 10**6 679 elif name == "EPOCH_NANOSECOND": 680 scale = 10**9 681 else: 682 scale = None 683 684 ts = self.expression(exp.Cast, this=expression, to=exp.DataType.build("TIMESTAMP")) 685 to_unix: exp.Expression = self.expression(exp.TimeToUnix, this=ts) 686 687 if scale: 688 to_unix = exp.Mul(this=to_unix, expression=exp.Literal.number(scale)) 689 690 return to_unix 691 692 return self.expression(exp.Extract, this=this, expression=expression) 693 694 def _parse_bracket_key_value(self, is_map: bool = False) -> t.Optional[exp.Expression]: 695 if is_map: 696 # Keys are strings in Snowflake's objects, see also: 697 # - https://docs.snowflake.com/en/sql-reference/data-types-semistructured 698 # - https://docs.snowflake.com/en/sql-reference/functions/object_construct 699 return self._parse_slice(self._parse_string()) 700 701 return self._parse_slice(self._parse_alias(self._parse_assignment(), explicit=True)) 702 703 def _parse_lateral(self) -> t.Optional[exp.Lateral]: 704 lateral = super()._parse_lateral() 705 if not lateral: 706 return lateral 707 708 if isinstance(lateral.this, exp.Explode): 709 table_alias = lateral.args.get("alias") 710 columns = [exp.to_identifier(col) for col in self.FLATTEN_COLUMNS] 711 if table_alias and not table_alias.args.get("columns"): 712 table_alias.set("columns", columns) 713 elif not table_alias: 714 exp.alias_(lateral, "_flattened", table=columns, copy=False) 715 716 return lateral 717 718 def _parse_table_parts( 719 self, schema: bool = False, is_db_reference: bool = False, wildcard: bool = False 720 ) -> exp.Table: 721 # https://docs.snowflake.com/en/user-guide/querying-stage 722 if self._match(TokenType.STRING, advance=False): 723 table = self._parse_string() 724 elif self._match_text_seq("@", advance=False): 725 table = self._parse_location_path() 726 else: 727 table = None 728 729 if table: 730 file_format = None 731 pattern = None 732 733 wrapped = self._match(TokenType.L_PAREN) 734 while self._curr and wrapped and not self._match(TokenType.R_PAREN): 735 if self._match_text_seq("FILE_FORMAT", "=>"): 736 file_format = self._parse_string() or super()._parse_table_parts( 737 is_db_reference=is_db_reference 738 ) 739 elif self._match_text_seq("PATTERN", "=>"): 740 pattern = self._parse_string() 741 else: 742 break 743 744 self._match(TokenType.COMMA) 745 746 table = self.expression(exp.Table, this=table, format=file_format, pattern=pattern) 747 else: 748 table = super()._parse_table_parts(schema=schema, is_db_reference=is_db_reference) 749 750 return table 751 752 def _parse_table( 753 self, 754 schema: bool = False, 755 joins: bool = False, 756 alias_tokens: t.Optional[t.Collection[TokenType]] = None, 757 parse_bracket: bool = False, 758 is_db_reference: bool = False, 759 parse_partition: bool = False, 760 ) -> t.Optional[exp.Expression]: 761 table = super()._parse_table( 762 schema=schema, 763 joins=joins, 764 alias_tokens=alias_tokens, 765 parse_bracket=parse_bracket, 766 is_db_reference=is_db_reference, 767 parse_partition=parse_partition, 768 ) 769 if isinstance(table, exp.Table) and isinstance(table.this, exp.TableFromRows): 770 table_from_rows = table.this 771 for arg in exp.TableFromRows.arg_types: 772 if arg != "this": 773 table_from_rows.set(arg, table.args.get(arg)) 774 775 table = table_from_rows 776 777 return table 778 779 def _parse_id_var( 780 self, 781 any_token: bool = True, 782 tokens: t.Optional[t.Collection[TokenType]] = None, 783 ) -> t.Optional[exp.Expression]: 784 if self._match_text_seq("IDENTIFIER", "("): 785 identifier = ( 786 super()._parse_id_var(any_token=any_token, tokens=tokens) 787 or self._parse_string() 788 ) 789 self._match_r_paren() 790 return self.expression(exp.Anonymous, this="IDENTIFIER", expressions=[identifier]) 791 792 return super()._parse_id_var(any_token=any_token, tokens=tokens) 793 794 def _parse_show_snowflake(self, this: str) -> exp.Show: 795 scope = None 796 scope_kind = None 797 798 # will identity SHOW TERSE SCHEMAS but not SHOW TERSE PRIMARY KEYS 799 # which is syntactically valid but has no effect on the output 800 terse = self._tokens[self._index - 2].text.upper() == "TERSE" 801 802 history = self._match_text_seq("HISTORY") 803 804 like = self._parse_string() if self._match(TokenType.LIKE) else None 805 806 if self._match(TokenType.IN): 807 if self._match_text_seq("ACCOUNT"): 808 scope_kind = "ACCOUNT" 809 elif self._match_text_seq("CLASS"): 810 scope_kind = "CLASS" 811 scope = self._parse_table_parts() 812 elif self._match_text_seq("APPLICATION"): 813 scope_kind = "APPLICATION" 814 if self._match_text_seq("PACKAGE"): 815 scope_kind += " PACKAGE" 816 scope = self._parse_table_parts() 817 elif self._match_set(self.DB_CREATABLES): 818 scope_kind = self._prev.text.upper() 819 if self._curr: 820 scope = self._parse_table_parts() 821 elif self._curr: 822 scope_kind = "SCHEMA" if this in self.SCHEMA_KINDS else "TABLE" 823 scope = self._parse_table_parts() 824 825 return self.expression( 826 exp.Show, 827 **{ 828 "terse": terse, 829 "this": this, 830 "history": history, 831 "like": like, 832 "scope": scope, 833 "scope_kind": scope_kind, 834 "starts_with": self._match_text_seq("STARTS", "WITH") and self._parse_string(), 835 "limit": self._parse_limit(), 836 "from": self._parse_string() if self._match(TokenType.FROM) else None, 837 "privileges": self._match_text_seq("WITH", "PRIVILEGES") 838 and self._parse_csv(lambda: self._parse_var(any_token=True, upper=True)), 839 }, 840 ) 841 842 def _parse_put(self) -> exp.Put | exp.Command: 843 if self._curr.token_type != TokenType.STRING: 844 return self._parse_as_command(self._prev) 845 846 return self.expression( 847 exp.Put, 848 this=self._parse_string(), 849 target=self._parse_location_path(), 850 properties=self._parse_properties(), 851 ) 852 853 def _parse_location_property(self) -> exp.LocationProperty: 854 self._match(TokenType.EQ) 855 return self.expression(exp.LocationProperty, this=self._parse_location_path()) 856 857 def _parse_file_location(self) -> t.Optional[exp.Expression]: 858 # Parse either a subquery or a staged file 859 return ( 860 self._parse_select(table=True, parse_subquery_alias=False) 861 if self._match(TokenType.L_PAREN, advance=False) 862 else self._parse_table_parts() 863 ) 864 865 def _parse_location_path(self) -> exp.Var: 866 parts = [self._advance_any(ignore_reserved=True)] 867 868 # We avoid consuming a comma token because external tables like @foo and @bar 869 # can be joined in a query with a comma separator, as well as closing paren 870 # in case of subqueries 871 while self._is_connected() and not self._match_set( 872 (TokenType.COMMA, TokenType.L_PAREN, TokenType.R_PAREN), advance=False 873 ): 874 parts.append(self._advance_any(ignore_reserved=True)) 875 876 return exp.var("".join(part.text for part in parts if part)) 877 878 def _parse_lambda_arg(self) -> t.Optional[exp.Expression]: 879 this = super()._parse_lambda_arg() 880 881 if not this: 882 return this 883 884 typ = self._parse_types() 885 886 if typ: 887 return self.expression(exp.Cast, this=this, to=typ) 888 889 return this 890 891 def _parse_foreign_key(self) -> exp.ForeignKey: 892 # inlineFK, the REFERENCES columns are implied 893 if self._match(TokenType.REFERENCES, advance=False): 894 return self.expression(exp.ForeignKey) 895 896 # outoflineFK, explicitly names the columns 897 return super()._parse_foreign_key()
Parser consumes a list of tokens produced by the Tokenizer and produces a parsed syntax tree.
Arguments:
- error_level: The desired error level. Default: ErrorLevel.IMMEDIATE
- error_message_context: The amount of context to capture from a query string when displaying the error message (in number of characters). Default: 100
- max_errors: Maximum number of error messages to include in a raised ParseError. This is only relevant if error_level is ErrorLevel.RAISE. Default: 3
Inherited Members
- sqlglot.parser.Parser
- Parser
- NO_PAREN_FUNCTIONS
- STRUCT_TYPE_TOKENS
- NESTED_TYPE_TOKENS
- ENUM_TYPE_TOKENS
- AGGREGATE_TYPE_TOKENS
- TYPE_TOKENS
- SIGNED_TO_UNSIGNED_TYPE_TOKEN
- SUBQUERY_PREDICATES
- RESERVED_TOKENS
- DB_CREATABLES
- CREATABLES
- ALTERABLES
- ALIAS_TOKENS
- ARRAY_CONSTRUCTORS
- COMMENT_TABLE_ALIAS_TOKENS
- UPDATE_ALIAS_TOKENS
- TRIM_TYPES
- FUNC_TOKENS
- CONJUNCTION
- ASSIGNMENT
- DISJUNCTION
- EQUALITY
- COMPARISON
- BITWISE
- TERM
- FACTOR
- EXPONENT
- TIMES
- SET_OPERATIONS
- JOIN_METHODS
- JOIN_SIDES
- JOIN_KINDS
- JOIN_HINTS
- COLUMN_OPERATORS
- EXPRESSION_PARSERS
- UNARY_PARSERS
- STRING_PARSERS
- NUMERIC_PARSERS
- PRIMARY_PARSERS
- PLACEHOLDER_PARSERS
- ALTER_ALTER_PARSERS
- SCHEMA_UNNAMED_CONSTRAINTS
- NO_PAREN_FUNCTION_PARSERS
- INVALID_FUNC_NAME_TOKENS
- FUNCTIONS_WITH_ALIASED_ARGS
- KEY_VALUE_DEFINITIONS
- QUERY_MODIFIER_PARSERS
- SET_PARSERS
- TYPE_LITERAL_PARSERS
- DDL_SELECT_TOKENS
- PRE_VOLATILE_TOKENS
- TRANSACTION_KIND
- TRANSACTION_CHARACTERISTICS
- CONFLICT_ACTIONS
- CREATE_SEQUENCE
- ISOLATED_LOADING_OPTIONS
- USABLES
- CAST_ACTIONS
- SCHEMA_BINDING_OPTIONS
- PROCEDURE_OPTIONS
- EXECUTE_AS_OPTIONS
- KEY_CONSTRAINT_OPTIONS
- INSERT_ALTERNATIVES
- CLONE_KEYWORDS
- HISTORICAL_DATA_PREFIX
- HISTORICAL_DATA_KIND
- OPCLASS_FOLLOW_KEYWORDS
- OPTYPE_FOLLOW_TOKENS
- TABLE_INDEX_HINT_TOKENS
- VIEW_ATTRIBUTES
- WINDOW_ALIAS_TOKENS
- WINDOW_BEFORE_PAREN_TOKENS
- WINDOW_SIDES
- JSON_KEY_VALUE_SEPARATOR_TOKENS
- FETCH_TOKENS
- ADD_CONSTRAINT_TOKENS
- DISTINCT_TOKENS
- NULL_TOKENS
- UNNEST_OFFSET_ALIAS_TOKENS
- SELECT_START_TOKENS
- COPY_INTO_VARLEN_OPTIONS
- IS_JSON_PREDICATE_KIND
- ODBC_DATETIME_LITERALS
- ON_CONDITION_TOKENS
- PRIVILEGE_FOLLOW_TOKENS
- DESCRIBE_STYLES
- ANALYZE_STYLES
- ANALYZE_EXPRESSION_PARSERS
- PARTITION_KEYWORDS
- AMBIGUOUS_ALIAS_TOKENS
- OPERATION_MODIFIERS
- RECURSIVE_CTE_SEARCH_KIND
- MODIFIABLES
- STRICT_CAST
- PREFIXED_PIVOT_COLUMNS
- 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_SET_OP
- SET_OP_MODIFIERS
- NO_PAREN_IF_COMMANDS
- JSON_ARROWS_REQUIRE_JSON_TYPE
- VALUES_FOLLOWED_BY_PAREN
- SUPPORTS_IMPLICIT_UNNEST
- INTERVAL_SPANS
- SUPPORTS_PARTITION_SELECTION
- WRAPPED_TRANSFORM_COLUMN_CONSTRAINT
- OPTIONAL_ALIAS_TOKEN_CTE
- error_level
- error_message_context
- max_errors
- dialect
- reset
- parse
- parse_into
- check_errors
- raise_error
- expression
- validate_expression
- errors
- sql
899 class Tokenizer(tokens.Tokenizer): 900 STRING_ESCAPES = ["\\", "'"] 901 HEX_STRINGS = [("x'", "'"), ("X'", "'")] 902 RAW_STRINGS = ["$$"] 903 COMMENTS = ["--", "//", ("/*", "*/")] 904 NESTED_COMMENTS = False 905 906 KEYWORDS = { 907 **tokens.Tokenizer.KEYWORDS, 908 "FILE://": TokenType.URI_START, 909 "BYTEINT": TokenType.INT, 910 "CHAR VARYING": TokenType.VARCHAR, 911 "CHARACTER VARYING": TokenType.VARCHAR, 912 "EXCLUDE": TokenType.EXCEPT, 913 "ILIKE ANY": TokenType.ILIKE_ANY, 914 "LIKE ANY": TokenType.LIKE_ANY, 915 "MATCH_CONDITION": TokenType.MATCH_CONDITION, 916 "MATCH_RECOGNIZE": TokenType.MATCH_RECOGNIZE, 917 "MINUS": TokenType.EXCEPT, 918 "NCHAR VARYING": TokenType.VARCHAR, 919 "PUT": TokenType.PUT, 920 "REMOVE": TokenType.COMMAND, 921 "RM": TokenType.COMMAND, 922 "SAMPLE": TokenType.TABLE_SAMPLE, 923 "SQL_DOUBLE": TokenType.DOUBLE, 924 "SQL_VARCHAR": TokenType.VARCHAR, 925 "STORAGE INTEGRATION": TokenType.STORAGE_INTEGRATION, 926 "TAG": TokenType.TAG, 927 "TIMESTAMP_TZ": TokenType.TIMESTAMPTZ, 928 "TOP": TokenType.TOP, 929 "WAREHOUSE": TokenType.WAREHOUSE, 930 "STREAMLIT": TokenType.STREAMLIT, 931 } 932 KEYWORDS.pop("/*+") 933 934 SINGLE_TOKENS = { 935 **tokens.Tokenizer.SINGLE_TOKENS, 936 "$": TokenType.PARAMETER, 937 } 938 939 VAR_SINGLE_TOKENS = {"$"} 940 941 COMMANDS = tokens.Tokenizer.COMMANDS - {TokenType.SHOW}
Inherited Members
- sqlglot.tokens.Tokenizer
- Tokenizer
- BIT_STRINGS
- BYTE_STRINGS
- HEREDOC_STRINGS
- UNICODE_STRINGS
- IDENTIFIERS
- QUOTES
- IDENTIFIER_ESCAPES
- HEREDOC_TAG_IS_IDENTIFIER
- HEREDOC_STRING_ALTERNATIVE
- STRING_ESCAPES_ALLOWED_IN_RAW_STRINGS
- HINT_START
- TOKENS_PRECEDING_HINT
- WHITE_SPACE
- COMMAND_PREFIX_TOKENS
- NUMERIC_LITERALS
- dialect
- use_rs_tokenizer
- reset
- tokenize
- tokenize_rs
- size
- sql
- tokens
943 class Generator(generator.Generator): 944 PARAMETER_TOKEN = "$" 945 MATCHED_BY_SOURCE = False 946 SINGLE_STRING_INTERVAL = True 947 JOIN_HINTS = False 948 TABLE_HINTS = False 949 QUERY_HINTS = False 950 AGGREGATE_FILTER_SUPPORTED = False 951 SUPPORTS_TABLE_COPY = False 952 COLLATE_IS_FUNC = True 953 LIMIT_ONLY_LITERALS = True 954 JSON_KEY_VALUE_PAIR_SEP = "," 955 INSERT_OVERWRITE = " OVERWRITE INTO" 956 STRUCT_DELIMITER = ("(", ")") 957 COPY_PARAMS_ARE_WRAPPED = False 958 COPY_PARAMS_EQ_REQUIRED = True 959 STAR_EXCEPT = "EXCLUDE" 960 SUPPORTS_EXPLODING_PROJECTIONS = False 961 ARRAY_CONCAT_IS_VAR_LEN = False 962 SUPPORTS_CONVERT_TIMEZONE = True 963 EXCEPT_INTERSECT_SUPPORT_ALL_CLAUSE = False 964 SUPPORTS_MEDIAN = True 965 ARRAY_SIZE_NAME = "ARRAY_SIZE" 966 967 TRANSFORMS = { 968 **generator.Generator.TRANSFORMS, 969 exp.ApproxDistinct: rename_func("APPROX_COUNT_DISTINCT"), 970 exp.ArgMax: rename_func("MAX_BY"), 971 exp.ArgMin: rename_func("MIN_BY"), 972 exp.Array: inline_array_sql, 973 exp.ArrayConcat: lambda self, e: self.arrayconcat_sql(e, name="ARRAY_CAT"), 974 exp.ArrayContains: lambda self, e: self.func("ARRAY_CONTAINS", e.expression, e.this), 975 exp.AtTimeZone: lambda self, e: self.func( 976 "CONVERT_TIMEZONE", e.args.get("zone"), e.this 977 ), 978 exp.BitwiseOr: rename_func("BITOR"), 979 exp.BitwiseXor: rename_func("BITXOR"), 980 exp.BitwiseLeftShift: rename_func("BITSHIFTLEFT"), 981 exp.BitwiseRightShift: rename_func("BITSHIFTRIGHT"), 982 exp.Create: transforms.preprocess([_flatten_structured_types_unless_iceberg]), 983 exp.DateAdd: date_delta_sql("DATEADD"), 984 exp.DateDiff: date_delta_sql("DATEDIFF"), 985 exp.DatetimeAdd: date_delta_sql("TIMESTAMPADD"), 986 exp.DatetimeDiff: timestampdiff_sql, 987 exp.DateStrToDate: datestrtodate_sql, 988 exp.DayOfMonth: rename_func("DAYOFMONTH"), 989 exp.DayOfWeek: rename_func("DAYOFWEEK"), 990 exp.DayOfYear: rename_func("DAYOFYEAR"), 991 exp.Explode: rename_func("FLATTEN"), 992 exp.Extract: rename_func("DATE_PART"), 993 exp.FromTimeZone: lambda self, e: self.func( 994 "CONVERT_TIMEZONE", e.args.get("zone"), "'UTC'", e.this 995 ), 996 exp.GenerateSeries: lambda self, e: self.func( 997 "ARRAY_GENERATE_RANGE", e.args["start"], e.args["end"] + 1, e.args.get("step") 998 ), 999 exp.GroupConcat: lambda self, e: groupconcat_sql(self, e, sep=""), 1000 exp.If: if_sql(name="IFF", false_value="NULL"), 1001 exp.JSONExtractArray: _json_extract_value_array_sql, 1002 exp.JSONExtractScalar: lambda self, e: self.func( 1003 "JSON_EXTRACT_PATH_TEXT", e.this, e.expression 1004 ), 1005 exp.JSONObject: lambda self, e: self.func("OBJECT_CONSTRUCT_KEEP_NULL", *e.expressions), 1006 exp.JSONPathRoot: lambda *_: "", 1007 exp.JSONValueArray: _json_extract_value_array_sql, 1008 exp.LogicalAnd: rename_func("BOOLAND_AGG"), 1009 exp.LogicalOr: rename_func("BOOLOR_AGG"), 1010 exp.Map: lambda self, e: var_map_sql(self, e, "OBJECT_CONSTRUCT"), 1011 exp.MakeInterval: no_make_interval_sql, 1012 exp.Max: max_or_greatest, 1013 exp.Min: min_or_least, 1014 exp.ParseJSON: lambda self, e: self.func( 1015 "TRY_PARSE_JSON" if e.args.get("safe") else "PARSE_JSON", e.this 1016 ), 1017 exp.PartitionedByProperty: lambda self, e: f"PARTITION BY {self.sql(e, 'this')}", 1018 exp.PercentileCont: transforms.preprocess( 1019 [transforms.add_within_group_for_percentiles] 1020 ), 1021 exp.PercentileDisc: transforms.preprocess( 1022 [transforms.add_within_group_for_percentiles] 1023 ), 1024 exp.Pivot: transforms.preprocess([_unqualify_pivot_columns]), 1025 exp.RegexpExtract: _regexpextract_sql, 1026 exp.RegexpExtractAll: _regexpextract_sql, 1027 exp.RegexpILike: _regexpilike_sql, 1028 exp.Rand: rename_func("RANDOM"), 1029 exp.Select: transforms.preprocess( 1030 [ 1031 transforms.eliminate_distinct_on, 1032 transforms.explode_to_unnest(), 1033 transforms.eliminate_semi_and_anti_joins, 1034 _transform_generate_date_array, 1035 ] 1036 ), 1037 exp.SHA: rename_func("SHA1"), 1038 exp.StarMap: rename_func("OBJECT_CONSTRUCT"), 1039 exp.StartsWith: rename_func("STARTSWITH"), 1040 exp.StrPosition: lambda self, e: strposition_sql( 1041 self, e, func_name="CHARINDEX", supports_position=True 1042 ), 1043 exp.StrToDate: lambda self, e: self.func("DATE", e.this, self.format_time(e)), 1044 exp.Stuff: rename_func("INSERT"), 1045 exp.TimeAdd: date_delta_sql("TIMEADD"), 1046 exp.Timestamp: no_timestamp_sql, 1047 exp.TimestampAdd: date_delta_sql("TIMESTAMPADD"), 1048 exp.TimestampDiff: lambda self, e: self.func( 1049 "TIMESTAMPDIFF", e.unit, e.expression, e.this 1050 ), 1051 exp.TimestampTrunc: timestamptrunc_sql(), 1052 exp.TimeStrToTime: timestrtotime_sql, 1053 exp.TimeToUnix: lambda self, e: f"EXTRACT(epoch_second FROM {self.sql(e, 'this')})", 1054 exp.ToArray: rename_func("TO_ARRAY"), 1055 exp.ToChar: lambda self, e: self.function_fallback_sql(e), 1056 exp.ToDouble: rename_func("TO_DOUBLE"), 1057 exp.TsOrDsAdd: date_delta_sql("DATEADD", cast=True), 1058 exp.TsOrDsDiff: date_delta_sql("DATEDIFF"), 1059 exp.TsOrDsToDate: lambda self, e: self.func( 1060 "TRY_TO_DATE" if e.args.get("safe") else "TO_DATE", e.this, self.format_time(e) 1061 ), 1062 exp.TsOrDsToTime: lambda self, e: self.func( 1063 "TRY_TO_TIME" if e.args.get("safe") else "TO_TIME", e.this, self.format_time(e) 1064 ), 1065 exp.Unhex: rename_func("HEX_DECODE_BINARY"), 1066 exp.UnixToTime: rename_func("TO_TIMESTAMP"), 1067 exp.Uuid: rename_func("UUID_STRING"), 1068 exp.VarMap: lambda self, e: var_map_sql(self, e, "OBJECT_CONSTRUCT"), 1069 exp.WeekOfYear: rename_func("WEEKOFYEAR"), 1070 exp.Xor: rename_func("BOOLXOR"), 1071 exp.Levenshtein: unsupported_args("ins_cost", "del_cost", "sub_cost")( 1072 rename_func("EDITDISTANCE") 1073 ), 1074 } 1075 1076 SUPPORTED_JSON_PATH_PARTS = { 1077 exp.JSONPathKey, 1078 exp.JSONPathRoot, 1079 exp.JSONPathSubscript, 1080 } 1081 1082 TYPE_MAPPING = { 1083 **generator.Generator.TYPE_MAPPING, 1084 exp.DataType.Type.NESTED: "OBJECT", 1085 exp.DataType.Type.STRUCT: "OBJECT", 1086 } 1087 1088 TOKEN_MAPPING = { 1089 TokenType.AUTO_INCREMENT: "AUTOINCREMENT", 1090 } 1091 1092 PROPERTIES_LOCATION = { 1093 **generator.Generator.PROPERTIES_LOCATION, 1094 exp.SetProperty: exp.Properties.Location.UNSUPPORTED, 1095 exp.VolatileProperty: exp.Properties.Location.UNSUPPORTED, 1096 } 1097 1098 UNSUPPORTED_VALUES_EXPRESSIONS = { 1099 exp.Map, 1100 exp.StarMap, 1101 exp.Struct, 1102 exp.VarMap, 1103 } 1104 1105 def with_properties(self, properties: exp.Properties) -> str: 1106 return self.properties(properties, wrapped=False, prefix=self.sep(""), sep=" ") 1107 1108 def values_sql(self, expression: exp.Values, values_as_table: bool = True) -> str: 1109 if expression.find(*self.UNSUPPORTED_VALUES_EXPRESSIONS): 1110 values_as_table = False 1111 1112 return super().values_sql(expression, values_as_table=values_as_table) 1113 1114 def datatype_sql(self, expression: exp.DataType) -> str: 1115 expressions = expression.expressions 1116 if ( 1117 expressions 1118 and expression.is_type(*exp.DataType.STRUCT_TYPES) 1119 and any(isinstance(field_type, exp.DataType) for field_type in expressions) 1120 ): 1121 # The correct syntax is OBJECT [ (<key> <value_type [NOT NULL] [, ...]) ] 1122 return "OBJECT" 1123 1124 return super().datatype_sql(expression) 1125 1126 def tonumber_sql(self, expression: exp.ToNumber) -> str: 1127 return self.func( 1128 "TO_NUMBER", 1129 expression.this, 1130 expression.args.get("format"), 1131 expression.args.get("precision"), 1132 expression.args.get("scale"), 1133 ) 1134 1135 def timestampfromparts_sql(self, expression: exp.TimestampFromParts) -> str: 1136 milli = expression.args.get("milli") 1137 if milli is not None: 1138 milli_to_nano = milli.pop() * exp.Literal.number(1000000) 1139 expression.set("nano", milli_to_nano) 1140 1141 return rename_func("TIMESTAMP_FROM_PARTS")(self, expression) 1142 1143 def cast_sql(self, expression: exp.Cast, safe_prefix: t.Optional[str] = None) -> str: 1144 if expression.is_type(exp.DataType.Type.GEOGRAPHY): 1145 return self.func("TO_GEOGRAPHY", expression.this) 1146 if expression.is_type(exp.DataType.Type.GEOMETRY): 1147 return self.func("TO_GEOMETRY", expression.this) 1148 1149 return super().cast_sql(expression, safe_prefix=safe_prefix) 1150 1151 def trycast_sql(self, expression: exp.TryCast) -> str: 1152 value = expression.this 1153 1154 if value.type is None: 1155 from sqlglot.optimizer.annotate_types import annotate_types 1156 1157 value = annotate_types(value) 1158 1159 if value.is_type(*exp.DataType.TEXT_TYPES, exp.DataType.Type.UNKNOWN): 1160 return super().trycast_sql(expression) 1161 1162 # TRY_CAST only works for string values in Snowflake 1163 return self.cast_sql(expression) 1164 1165 def log_sql(self, expression: exp.Log) -> str: 1166 if not expression.expression: 1167 return self.func("LN", expression.this) 1168 1169 return super().log_sql(expression) 1170 1171 def unnest_sql(self, expression: exp.Unnest) -> str: 1172 unnest_alias = expression.args.get("alias") 1173 offset = expression.args.get("offset") 1174 1175 columns = [ 1176 exp.to_identifier("seq"), 1177 exp.to_identifier("key"), 1178 exp.to_identifier("path"), 1179 offset.pop() if isinstance(offset, exp.Expression) else exp.to_identifier("index"), 1180 seq_get(unnest_alias.columns if unnest_alias else [], 0) 1181 or exp.to_identifier("value"), 1182 exp.to_identifier("this"), 1183 ] 1184 1185 if unnest_alias: 1186 unnest_alias.set("columns", columns) 1187 else: 1188 unnest_alias = exp.TableAlias(this="_u", columns=columns) 1189 1190 table_input = self.sql(expression.expressions[0]) 1191 if not table_input.startswith("INPUT =>"): 1192 table_input = f"INPUT => {table_input}" 1193 1194 explode = f"TABLE(FLATTEN({table_input}))" 1195 alias = self.sql(unnest_alias) 1196 alias = f" AS {alias}" if alias else "" 1197 return f"{explode}{alias}" 1198 1199 def show_sql(self, expression: exp.Show) -> str: 1200 terse = "TERSE " if expression.args.get("terse") else "" 1201 history = " HISTORY" if expression.args.get("history") else "" 1202 like = self.sql(expression, "like") 1203 like = f" LIKE {like}" if like else "" 1204 1205 scope = self.sql(expression, "scope") 1206 scope = f" {scope}" if scope else "" 1207 1208 scope_kind = self.sql(expression, "scope_kind") 1209 if scope_kind: 1210 scope_kind = f" IN {scope_kind}" 1211 1212 starts_with = self.sql(expression, "starts_with") 1213 if starts_with: 1214 starts_with = f" STARTS WITH {starts_with}" 1215 1216 limit = self.sql(expression, "limit") 1217 1218 from_ = self.sql(expression, "from") 1219 if from_: 1220 from_ = f" FROM {from_}" 1221 1222 privileges = self.expressions(expression, key="privileges", flat=True) 1223 privileges = f" WITH PRIVILEGES {privileges}" if privileges else "" 1224 1225 return f"SHOW {terse}{expression.name}{history}{like}{scope_kind}{scope}{starts_with}{limit}{from_}{privileges}" 1226 1227 def describe_sql(self, expression: exp.Describe) -> str: 1228 # Default to table if kind is unknown 1229 kind_value = expression.args.get("kind") or "TABLE" 1230 kind = f" {kind_value}" if kind_value else "" 1231 this = f" {self.sql(expression, 'this')}" 1232 expressions = self.expressions(expression, flat=True) 1233 expressions = f" {expressions}" if expressions else "" 1234 return f"DESCRIBE{kind}{this}{expressions}" 1235 1236 def generatedasidentitycolumnconstraint_sql( 1237 self, expression: exp.GeneratedAsIdentityColumnConstraint 1238 ) -> str: 1239 start = expression.args.get("start") 1240 start = f" START {start}" if start else "" 1241 increment = expression.args.get("increment") 1242 increment = f" INCREMENT {increment}" if increment else "" 1243 return f"AUTOINCREMENT{start}{increment}" 1244 1245 def cluster_sql(self, expression: exp.Cluster) -> str: 1246 return f"CLUSTER BY ({self.expressions(expression, flat=True)})" 1247 1248 def struct_sql(self, expression: exp.Struct) -> str: 1249 keys = [] 1250 values = [] 1251 1252 for i, e in enumerate(expression.expressions): 1253 if isinstance(e, exp.PropertyEQ): 1254 keys.append( 1255 exp.Literal.string(e.name) if isinstance(e.this, exp.Identifier) else e.this 1256 ) 1257 values.append(e.expression) 1258 else: 1259 keys.append(exp.Literal.string(f"_{i}")) 1260 values.append(e) 1261 1262 return self.func("OBJECT_CONSTRUCT", *flatten(zip(keys, values))) 1263 1264 @unsupported_args("weight", "accuracy") 1265 def approxquantile_sql(self, expression: exp.ApproxQuantile) -> str: 1266 return self.func("APPROX_PERCENTILE", expression.this, expression.args.get("quantile")) 1267 1268 def alterset_sql(self, expression: exp.AlterSet) -> str: 1269 exprs = self.expressions(expression, flat=True) 1270 exprs = f" {exprs}" if exprs else "" 1271 file_format = self.expressions(expression, key="file_format", flat=True, sep=" ") 1272 file_format = f" STAGE_FILE_FORMAT = ({file_format})" if file_format else "" 1273 copy_options = self.expressions(expression, key="copy_options", flat=True, sep=" ") 1274 copy_options = f" STAGE_COPY_OPTIONS = ({copy_options})" if copy_options else "" 1275 tag = self.expressions(expression, key="tag", flat=True) 1276 tag = f" TAG {tag}" if tag else "" 1277 1278 return f"SET{exprs}{file_format}{copy_options}{tag}" 1279 1280 def strtotime_sql(self, expression: exp.StrToTime): 1281 safe_prefix = "TRY_" if expression.args.get("safe") else "" 1282 return self.func( 1283 f"{safe_prefix}TO_TIMESTAMP", expression.this, self.format_time(expression) 1284 ) 1285 1286 def timestampsub_sql(self, expression: exp.TimestampSub): 1287 return self.sql( 1288 exp.TimestampAdd( 1289 this=expression.this, 1290 expression=expression.expression * -1, 1291 unit=expression.unit, 1292 ) 1293 ) 1294 1295 def jsonextract_sql(self, expression: exp.JSONExtract): 1296 this = expression.this 1297 1298 # JSON strings are valid coming from other dialects such as BQ 1299 return self.func( 1300 "GET_PATH", 1301 exp.ParseJSON(this=this) if this.is_string else this, 1302 expression.expression, 1303 ) 1304 1305 def timetostr_sql(self, expression: exp.TimeToStr) -> str: 1306 this = expression.this 1307 if not isinstance(this, exp.TsOrDsToTimestamp): 1308 this = exp.cast(this, exp.DataType.Type.TIMESTAMP) 1309 1310 return self.func("TO_CHAR", this, self.format_time(expression)) 1311 1312 def datesub_sql(self, expression: exp.DateSub) -> str: 1313 value = expression.expression 1314 if value: 1315 value.replace(value * (-1)) 1316 else: 1317 self.unsupported("DateSub cannot be transpiled if the subtracted count is unknown") 1318 1319 return date_delta_sql("DATEADD")(self, expression) 1320 1321 def select_sql(self, expression: exp.Select) -> str: 1322 limit = expression.args.get("limit") 1323 offset = expression.args.get("offset") 1324 if offset and not limit: 1325 expression.limit(exp.Null(), copy=False) 1326 return super().select_sql(expression)
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
1114 def datatype_sql(self, expression: exp.DataType) -> str: 1115 expressions = expression.expressions 1116 if ( 1117 expressions 1118 and expression.is_type(*exp.DataType.STRUCT_TYPES) 1119 and any(isinstance(field_type, exp.DataType) for field_type in expressions) 1120 ): 1121 # The correct syntax is OBJECT [ (<key> <value_type [NOT NULL] [, ...]) ] 1122 return "OBJECT" 1123 1124 return super().datatype_sql(expression)
1135 def timestampfromparts_sql(self, expression: exp.TimestampFromParts) -> str: 1136 milli = expression.args.get("milli") 1137 if milli is not None: 1138 milli_to_nano = milli.pop() * exp.Literal.number(1000000) 1139 expression.set("nano", milli_to_nano) 1140 1141 return rename_func("TIMESTAMP_FROM_PARTS")(self, expression)
1143 def cast_sql(self, expression: exp.Cast, safe_prefix: t.Optional[str] = None) -> str: 1144 if expression.is_type(exp.DataType.Type.GEOGRAPHY): 1145 return self.func("TO_GEOGRAPHY", expression.this) 1146 if expression.is_type(exp.DataType.Type.GEOMETRY): 1147 return self.func("TO_GEOMETRY", expression.this) 1148 1149 return super().cast_sql(expression, safe_prefix=safe_prefix)
1151 def trycast_sql(self, expression: exp.TryCast) -> str: 1152 value = expression.this 1153 1154 if value.type is None: 1155 from sqlglot.optimizer.annotate_types import annotate_types 1156 1157 value = annotate_types(value) 1158 1159 if value.is_type(*exp.DataType.TEXT_TYPES, exp.DataType.Type.UNKNOWN): 1160 return super().trycast_sql(expression) 1161 1162 # TRY_CAST only works for string values in Snowflake 1163 return self.cast_sql(expression)
1171 def unnest_sql(self, expression: exp.Unnest) -> str: 1172 unnest_alias = expression.args.get("alias") 1173 offset = expression.args.get("offset") 1174 1175 columns = [ 1176 exp.to_identifier("seq"), 1177 exp.to_identifier("key"), 1178 exp.to_identifier("path"), 1179 offset.pop() if isinstance(offset, exp.Expression) else exp.to_identifier("index"), 1180 seq_get(unnest_alias.columns if unnest_alias else [], 0) 1181 or exp.to_identifier("value"), 1182 exp.to_identifier("this"), 1183 ] 1184 1185 if unnest_alias: 1186 unnest_alias.set("columns", columns) 1187 else: 1188 unnest_alias = exp.TableAlias(this="_u", columns=columns) 1189 1190 table_input = self.sql(expression.expressions[0]) 1191 if not table_input.startswith("INPUT =>"): 1192 table_input = f"INPUT => {table_input}" 1193 1194 explode = f"TABLE(FLATTEN({table_input}))" 1195 alias = self.sql(unnest_alias) 1196 alias = f" AS {alias}" if alias else "" 1197 return f"{explode}{alias}"
1199 def show_sql(self, expression: exp.Show) -> str: 1200 terse = "TERSE " if expression.args.get("terse") else "" 1201 history = " HISTORY" if expression.args.get("history") else "" 1202 like = self.sql(expression, "like") 1203 like = f" LIKE {like}" if like else "" 1204 1205 scope = self.sql(expression, "scope") 1206 scope = f" {scope}" if scope else "" 1207 1208 scope_kind = self.sql(expression, "scope_kind") 1209 if scope_kind: 1210 scope_kind = f" IN {scope_kind}" 1211 1212 starts_with = self.sql(expression, "starts_with") 1213 if starts_with: 1214 starts_with = f" STARTS WITH {starts_with}" 1215 1216 limit = self.sql(expression, "limit") 1217 1218 from_ = self.sql(expression, "from") 1219 if from_: 1220 from_ = f" FROM {from_}" 1221 1222 privileges = self.expressions(expression, key="privileges", flat=True) 1223 privileges = f" WITH PRIVILEGES {privileges}" if privileges else "" 1224 1225 return f"SHOW {terse}{expression.name}{history}{like}{scope_kind}{scope}{starts_with}{limit}{from_}{privileges}"
1227 def describe_sql(self, expression: exp.Describe) -> str: 1228 # Default to table if kind is unknown 1229 kind_value = expression.args.get("kind") or "TABLE" 1230 kind = f" {kind_value}" if kind_value else "" 1231 this = f" {self.sql(expression, 'this')}" 1232 expressions = self.expressions(expression, flat=True) 1233 expressions = f" {expressions}" if expressions else "" 1234 return f"DESCRIBE{kind}{this}{expressions}"
1236 def generatedasidentitycolumnconstraint_sql( 1237 self, expression: exp.GeneratedAsIdentityColumnConstraint 1238 ) -> str: 1239 start = expression.args.get("start") 1240 start = f" START {start}" if start else "" 1241 increment = expression.args.get("increment") 1242 increment = f" INCREMENT {increment}" if increment else "" 1243 return f"AUTOINCREMENT{start}{increment}"
1248 def struct_sql(self, expression: exp.Struct) -> str: 1249 keys = [] 1250 values = [] 1251 1252 for i, e in enumerate(expression.expressions): 1253 if isinstance(e, exp.PropertyEQ): 1254 keys.append( 1255 exp.Literal.string(e.name) if isinstance(e.this, exp.Identifier) else e.this 1256 ) 1257 values.append(e.expression) 1258 else: 1259 keys.append(exp.Literal.string(f"_{i}")) 1260 values.append(e) 1261 1262 return self.func("OBJECT_CONSTRUCT", *flatten(zip(keys, values)))
1268 def alterset_sql(self, expression: exp.AlterSet) -> str: 1269 exprs = self.expressions(expression, flat=True) 1270 exprs = f" {exprs}" if exprs else "" 1271 file_format = self.expressions(expression, key="file_format", flat=True, sep=" ") 1272 file_format = f" STAGE_FILE_FORMAT = ({file_format})" if file_format else "" 1273 copy_options = self.expressions(expression, key="copy_options", flat=True, sep=" ") 1274 copy_options = f" STAGE_COPY_OPTIONS = ({copy_options})" if copy_options else "" 1275 tag = self.expressions(expression, key="tag", flat=True) 1276 tag = f" TAG {tag}" if tag else "" 1277 1278 return f"SET{exprs}{file_format}{copy_options}{tag}"
1312 def datesub_sql(self, expression: exp.DateSub) -> str: 1313 value = expression.expression 1314 if value: 1315 value.replace(value * (-1)) 1316 else: 1317 self.unsupported("DateSub cannot be transpiled if the subtracted count is unknown") 1318 1319 return date_delta_sql("DATEADD")(self, expression)
Inherited Members
- sqlglot.generator.Generator
- Generator
- NULL_ORDERING_SUPPORTED
- IGNORE_NULLS_IN_FUNC
- LOCKING_READS_SUPPORTED
- 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
- 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
- SET_OP_MODIFIERS
- COPY_HAS_INTO_KEYWORD
- HEX_FUNC
- WITH_PROPERTIES_PREFIX
- QUOTE_JSON_PATH
- PAD_FILL_PATTERN_IS_REQUIRED
- SUPPORTS_UNIX_SECONDS
- PARSE_JSON_NAME
- ALTER_SET_TYPE
- ARRAY_SIZE_DIM_REQUIRED
- TIME_PART_SINGULARS
- NAMED_PLACEHOLDER_TOKEN
- EXPRESSION_PRECEDES_PROPERTIES_CREATABLES
- 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
- set_operation
- set_operations
- fetch_sql
- limitoptions_sql
- filter_sql
- hint_sql
- indexparameters_sql
- index_sql
- identifier_sql
- hex_sql
- lowerhex_sql
- inputoutputformat_sql
- national_sql
- partition_sql
- properties_sql
- root_properties
- properties
- locate_properties
- property_name
- property_sql
- likeproperty_sql
- fallbackproperty_sql
- journalproperty_sql
- freespaceproperty_sql
- checksumproperty_sql
- mergeblockratioproperty_sql
- datablocksizeproperty_sql
- blockcompressionproperty_sql
- isolatedloadingproperty_sql
- partitionboundspec_sql
- partitionedofproperty_sql
- lockingproperty_sql
- withdataproperty_sql
- withsystemversioningproperty_sql
- insert_sql
- introducer_sql
- kill_sql
- pseudotype_sql
- objectidentifier_sql
- onconflict_sql
- returning_sql
- rowformatdelimitedproperty_sql
- withtablehint_sql
- indextablehint_sql
- historicaldata_sql
- table_parts
- table_sql
- tablefromrows_sql
- tablesample_sql
- pivot_sql
- version_sql
- tuple_sql
- update_sql
- var_sql
- into_sql
- from_sql
- groupingsets_sql
- rollup_sql
- cube_sql
- group_sql
- having_sql
- connect_sql
- prior_sql
- join_sql
- lambda_sql
- lateral_op
- lateral_sql
- limit_sql
- offset_sql
- setitem_sql
- set_sql
- pragma_sql
- lock_sql
- literal_sql
- escape_str
- loaddata_sql
- null_sql
- boolean_sql
- order_sql
- withfill_sql
- distribute_sql
- sort_sql
- ordered_sql
- matchrecognizemeasure_sql
- matchrecognize_sql
- query_modifiers
- options_modifier
- queryoption_sql
- offset_limit_modifiers
- after_limit_modifiers
- schema_sql
- schema_columns_sql
- star_sql
- parameter_sql
- sessionparameter_sql
- placeholder_sql
- subquery_sql
- qualify_sql
- 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
- currentdate_sql
- collate_sql
- command_sql
- comment_sql
- mergetreettlaction_sql
- mergetreettl_sql
- transaction_sql
- commit_sql
- rollback_sql
- altercolumn_sql
- alterindex_sql
- alterdiststyle_sql
- altersortkey_sql
- alterrename_sql
- renamecolumn_sql
- alter_sql
- add_column_sql
- droppartition_sql
- addconstraint_sql
- distinct_sql
- ignorenulls_sql
- respectnulls_sql
- havingmax_sql
- intdiv_sql
- dpipe_sql
- div_sql
- safedivide_sql
- overlaps_sql
- distance_sql
- dot_sql
- eq_sql
- propertyeq_sql
- escape_sql
- glob_sql
- gt_sql
- gte_sql
- ilike_sql
- ilikeany_sql
- is_sql
- like_sql
- likeany_sql
- similarto_sql
- lt_sql
- lte_sql
- mod_sql
- mul_sql
- neq_sql
- nullsafeeq_sql
- nullsafeneq_sql
- slice_sql
- sub_sql
- jsoncast_sql
- try_sql
- use_sql
- binary
- ceil_floor
- function_fallback_sql
- func
- format_args
- too_wide
- format_time
- expressions
- op_expressions
- naked_property
- tag_sql
- token_sql
- userdefinedfunction_sql
- joinhint_sql
- kwarg_sql
- when_sql
- whens_sql
- merge_sql
- tochar_sql
- dictproperty_sql
- dictrange_sql
- dictsubproperty_sql
- duplicatekeyproperty_sql
- uniquekeyproperty_sql
- distributedbyproperty_sql
- oncluster_sql
- clusteredbyproperty_sql
- anyvalue_sql
- querytransform_sql
- indexconstraintoption_sql
- checkcolumnconstraint_sql
- indexcolumnconstraint_sql
- nvl2_sql
- comprehension_sql
- columnprefix_sql
- opclass_sql
- predict_sql
- forin_sql
- refresh_sql
- toarray_sql
- tsordstotime_sql
- tsordstotimestamp_sql
- tsordstodatetime_sql
- tsordstodate_sql
- unixdate_sql
- lastday_sql
- dateadd_sql
- arrayany_sql
- partitionrange_sql
- truncatetable_sql
- convert_sql
- copyparameter_sql
- credentials_sql
- copy_sql
- semicolon_sql
- datadeletionproperty_sql
- maskingpolicycolumnconstraint_sql
- gapfill_sql
- scope_resolution
- scoperesolution_sql
- parsejson_sql
- rand_sql
- changes_sql
- pad_sql
- summarize_sql
- explodinggenerateseries_sql
- arrayconcat_sql
- converttimezone_sql
- json_sql
- jsonvalue_sql
- conditionalinsert_sql
- multitableinserts_sql
- oncondition_sql
- jsonextractquote_sql
- jsonexists_sql
- arrayagg_sql
- apply_sql
- grant_sql
- grantprivilege_sql
- grantprincipal_sql
- columns_sql
- overlay_sql
- todouble_sql
- string_sql
- median_sql
- overflowtruncatebehavior_sql
- unixseconds_sql
- arraysize_sql
- attach_sql
- detach_sql
- attachoption_sql
- featuresattime_sql
- watermarkcolumnconstraint_sql
- encodeproperty_sql
- includeproperty_sql
- xmlelement_sql
- partitionbyrangeproperty_sql
- partitionbyrangepropertydynamic_sql
- unpivotcolumns_sql
- analyzesample_sql
- analyzestatistics_sql
- analyzehistogram_sql
- analyzedelete_sql
- analyzelistchainedrows_sql
- analyzevalidate_sql
- analyze_sql
- xmltable_sql
- xmlnamespace_sql
- export_sql
- declare_sql
- declareitem_sql
- recursivewithsearch_sql
- parameterizedagg_sql
- anonymousaggfunc_sql
- combinedaggfunc_sql
- combinedparameterizedagg_sql
- put_sql