sqlparser/dialect/
snowflake.rs

1// Licensed to the Apache Software Foundation (ASF) under one
2// or more contributor license agreements.  See the NOTICE file
3// distributed with this work for additional information
4// regarding copyright ownership.  The ASF licenses this file
5// to you under the Apache License, Version 2.0 (the
6// "License"); you may not use this file except in compliance
7// with the License.  You may obtain a copy of the License at
8//
9//   http://www.apache.org/licenses/LICENSE-2.0
10//
11// Unless required by applicable law or agreed to in writing,
12// software distributed under the License is distributed on an
13// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
14// KIND, either express or implied.  See the License for the
15// specific language governing permissions and limitations
16// under the License.
17
18#[cfg(not(feature = "std"))]
19use crate::alloc::string::ToString;
20use crate::ast::helpers::key_value_options::{KeyValueOption, KeyValueOptionType, KeyValueOptions};
21use crate::ast::helpers::stmt_create_table::CreateTableBuilder;
22use crate::ast::helpers::stmt_data_loading::{
23    FileStagingCommand, StageLoadSelectItem, StageParamsObject,
24};
25use crate::ast::{
26    ColumnOption, ColumnPolicy, ColumnPolicyProperty, CopyIntoSnowflakeKind, Ident,
27    IdentityParameters, IdentityProperty, IdentityPropertyFormatKind, IdentityPropertyKind,
28    IdentityPropertyOrder, ObjectName, RowAccessPolicy, ShowObjects, Statement, TagsColumnOption,
29    WrappedCollection,
30};
31use crate::dialect::{Dialect, Precedence};
32use crate::keywords::Keyword;
33use crate::parser::{Parser, ParserError};
34use crate::tokenizer::{Token, Word};
35#[cfg(not(feature = "std"))]
36use alloc::boxed::Box;
37#[cfg(not(feature = "std"))]
38use alloc::string::String;
39#[cfg(not(feature = "std"))]
40use alloc::vec::Vec;
41#[cfg(not(feature = "std"))]
42use alloc::{format, vec};
43
44use super::keywords::RESERVED_FOR_IDENTIFIER;
45use sqlparser::ast::StorageSerializationPolicy;
46
47/// A [`Dialect`] for [Snowflake](https://www.snowflake.com/)
48#[derive(Debug, Default)]
49pub struct SnowflakeDialect;
50
51impl Dialect for SnowflakeDialect {
52    // see https://docs.snowflake.com/en/sql-reference/identifiers-syntax.html
53    fn is_identifier_start(&self, ch: char) -> bool {
54        ch.is_ascii_lowercase() || ch.is_ascii_uppercase() || ch == '_'
55    }
56
57    fn supports_projection_trailing_commas(&self) -> bool {
58        true
59    }
60
61    fn supports_from_trailing_commas(&self) -> bool {
62        true
63    }
64
65    // Snowflake supports double-dot notation when the schema name is not specified
66    // In this case the default PUBLIC schema is used
67    //
68    // see https://docs.snowflake.com/en/sql-reference/name-resolution#resolution-when-schema-omitted-double-dot-notation
69    fn supports_object_name_double_dot_notation(&self) -> bool {
70        true
71    }
72
73    fn is_identifier_part(&self, ch: char) -> bool {
74        ch.is_ascii_lowercase()
75            || ch.is_ascii_uppercase()
76            || ch.is_ascii_digit()
77            || ch == '$'
78            || ch == '_'
79    }
80
81    // See https://cloud.google.com/bigquery/docs/reference/standard-sql/lexical#escape_sequences
82    fn supports_string_literal_backslash_escape(&self) -> bool {
83        true
84    }
85
86    fn supports_within_after_array_aggregation(&self) -> bool {
87        true
88    }
89
90    /// See <https://docs.snowflake.com/en/sql-reference/constructs/where#joins-in-the-where-clause>
91    fn supports_outer_join_operator(&self) -> bool {
92        true
93    }
94
95    fn supports_connect_by(&self) -> bool {
96        true
97    }
98
99    /// See <https://docs.snowflake.com/en/sql-reference/sql/execute-immediate>
100    fn supports_execute_immediate(&self) -> bool {
101        true
102    }
103
104    fn supports_match_recognize(&self) -> bool {
105        true
106    }
107
108    // Snowflake uses this syntax for "object constants" (the values of which
109    // are not actually required to be constants).
110    //
111    // https://docs.snowflake.com/en/sql-reference/data-types-semistructured#label-object-constant
112    fn supports_dictionary_syntax(&self) -> bool {
113        true
114    }
115
116    // Snowflake doesn't document this but `FIRST_VALUE(arg, { IGNORE | RESPECT } NULLS)`
117    // works (i.e. inside the argument list instead of after).
118    fn supports_window_function_null_treatment_arg(&self) -> bool {
119        true
120    }
121
122    /// See [doc](https://docs.snowflake.com/en/sql-reference/sql/set#syntax)
123    fn supports_parenthesized_set_variables(&self) -> bool {
124        true
125    }
126
127    /// See [doc](https://docs.snowflake.com/en/sql-reference/sql/comment)
128    fn supports_comment_on(&self) -> bool {
129        true
130    }
131
132    fn parse_statement(&self, parser: &mut Parser) -> Option<Result<Statement, ParserError>> {
133        if parser.parse_keywords(&[Keyword::ALTER, Keyword::SESSION]) {
134            // ALTER SESSION
135            let set = match parser.parse_one_of_keywords(&[Keyword::SET, Keyword::UNSET]) {
136                Some(Keyword::SET) => true,
137                Some(Keyword::UNSET) => false,
138                _ => return Some(parser.expected("SET or UNSET", parser.peek_token())),
139            };
140            return Some(parse_alter_session(parser, set));
141        }
142
143        if parser.parse_keyword(Keyword::CREATE) {
144            // possibly CREATE STAGE
145            //[ OR  REPLACE ]
146            let or_replace = parser.parse_keywords(&[Keyword::OR, Keyword::REPLACE]);
147            // LOCAL | GLOBAL
148            let global = match parser.parse_one_of_keywords(&[Keyword::LOCAL, Keyword::GLOBAL]) {
149                Some(Keyword::LOCAL) => Some(false),
150                Some(Keyword::GLOBAL) => Some(true),
151                _ => None,
152            };
153
154            let mut temporary = false;
155            let mut volatile = false;
156            let mut transient = false;
157            let mut iceberg = false;
158
159            match parser.parse_one_of_keywords(&[
160                Keyword::TEMP,
161                Keyword::TEMPORARY,
162                Keyword::VOLATILE,
163                Keyword::TRANSIENT,
164                Keyword::ICEBERG,
165            ]) {
166                Some(Keyword::TEMP | Keyword::TEMPORARY) => temporary = true,
167                Some(Keyword::VOLATILE) => volatile = true,
168                Some(Keyword::TRANSIENT) => transient = true,
169                Some(Keyword::ICEBERG) => iceberg = true,
170                _ => {}
171            }
172
173            if parser.parse_keyword(Keyword::STAGE) {
174                // OK - this is CREATE STAGE statement
175                return Some(parse_create_stage(or_replace, temporary, parser));
176            } else if parser.parse_keyword(Keyword::TABLE) {
177                return Some(parse_create_table(
178                    or_replace, global, temporary, volatile, transient, iceberg, parser,
179                ));
180            } else {
181                // need to go back with the cursor
182                let mut back = 1;
183                if or_replace {
184                    back += 2
185                }
186                if temporary {
187                    back += 1
188                }
189                for _i in 0..back {
190                    parser.prev_token();
191                }
192            }
193        }
194        if parser.parse_keywords(&[Keyword::COPY, Keyword::INTO]) {
195            // COPY INTO
196            return Some(parse_copy_into(parser));
197        }
198
199        if let Some(kw) = parser.parse_one_of_keywords(&[
200            Keyword::LIST,
201            Keyword::LS,
202            Keyword::REMOVE,
203            Keyword::RM,
204        ]) {
205            return Some(parse_file_staging_command(kw, parser));
206        }
207
208        if parser.parse_keyword(Keyword::SHOW) {
209            let terse = parser.parse_keyword(Keyword::TERSE);
210            if parser.parse_keyword(Keyword::OBJECTS) {
211                return Some(parse_show_objects(terse, parser));
212            }
213            //Give back Keyword::TERSE
214            if terse {
215                parser.prev_token();
216            }
217            //Give back Keyword::SHOW
218            parser.prev_token();
219        }
220
221        None
222    }
223
224    fn parse_column_option(
225        &self,
226        parser: &mut Parser,
227    ) -> Result<Option<Result<Option<ColumnOption>, ParserError>>, ParserError> {
228        parser.maybe_parse(|parser| {
229            let with = parser.parse_keyword(Keyword::WITH);
230
231            if parser.parse_keyword(Keyword::IDENTITY) {
232                Ok(parse_identity_property(parser)
233                    .map(|p| Some(ColumnOption::Identity(IdentityPropertyKind::Identity(p)))))
234            } else if parser.parse_keyword(Keyword::AUTOINCREMENT) {
235                Ok(parse_identity_property(parser).map(|p| {
236                    Some(ColumnOption::Identity(IdentityPropertyKind::Autoincrement(
237                        p,
238                    )))
239                }))
240            } else if parser.parse_keywords(&[Keyword::MASKING, Keyword::POLICY]) {
241                Ok(parse_column_policy_property(parser, with)
242                    .map(|p| Some(ColumnOption::Policy(ColumnPolicy::MaskingPolicy(p)))))
243            } else if parser.parse_keywords(&[Keyword::PROJECTION, Keyword::POLICY]) {
244                Ok(parse_column_policy_property(parser, with)
245                    .map(|p| Some(ColumnOption::Policy(ColumnPolicy::ProjectionPolicy(p)))))
246            } else if parser.parse_keywords(&[Keyword::TAG]) {
247                Ok(parse_column_tags(parser, with).map(|p| Some(ColumnOption::Tags(p))))
248            } else {
249                Err(ParserError::ParserError("not found match".to_string()))
250            }
251        })
252    }
253
254    fn get_next_precedence(&self, parser: &Parser) -> Option<Result<u8, ParserError>> {
255        let token = parser.peek_token();
256        // Snowflake supports the `:` cast operator unlike other dialects
257        match token.token {
258            Token::Colon => Some(Ok(self.prec_value(Precedence::DoubleColon))),
259            _ => None,
260        }
261    }
262
263    fn describe_requires_table_keyword(&self) -> bool {
264        true
265    }
266
267    fn allow_extract_custom(&self) -> bool {
268        true
269    }
270
271    fn allow_extract_single_quotes(&self) -> bool {
272        true
273    }
274
275    /// Snowflake expects the `LIKE` option before the `IN` option,
276    /// for example: <https://docs.snowflake.com/en/sql-reference/sql/show-views#syntax>
277    fn supports_show_like_before_in(&self) -> bool {
278        true
279    }
280
281    fn is_reserved_for_identifier(&self, kw: Keyword) -> bool {
282        // Unreserve some keywords that Snowflake accepts as identifiers
283        // See: https://docs.snowflake.com/en/sql-reference/reserved-keywords
284        if matches!(kw, Keyword::INTERVAL) {
285            false
286        } else {
287            RESERVED_FOR_IDENTIFIER.contains(&kw)
288        }
289    }
290
291    fn supports_partiql(&self) -> bool {
292        true
293    }
294
295    fn is_select_item_alias(&self, explicit: bool, kw: &Keyword, parser: &mut Parser) -> bool {
296        explicit
297            || match kw {
298            // The following keywords can be considered an alias as long as 
299            // they are not followed by other tokens that may change their meaning
300            // e.g. `SELECT * EXCEPT (col1) FROM tbl`
301            Keyword::EXCEPT
302            // e.g. `SELECT 1 LIMIT 5`
303            | Keyword::LIMIT
304            // e.g. `SELECT 1 OFFSET 5 ROWS`
305            | Keyword::OFFSET
306            // e.g. `INSERT INTO t SELECT 1 RETURNING *`
307            | Keyword::RETURNING if !matches!(parser.peek_token_ref().token, Token::Comma | Token::EOF) =>
308            {
309                false
310            }
311
312            // `FETCH` can be considered an alias as long as it's not followed by `FIRST`` or `NEXT`
313            // which would give it a different meanins, for example: `SELECT 1 FETCH FIRST 10 ROWS` - not an alias
314            Keyword::FETCH
315                if parser.peek_keyword(Keyword::FIRST) || parser.peek_keyword(Keyword::NEXT) =>
316            {
317                false
318            }
319
320            // Reserved keywords by the Snowflake dialect, which seem to be less strictive 
321            // than what is listed in `keywords::RESERVED_FOR_COLUMN_ALIAS`. The following 
322            // keywords were tested with the this statement: `SELECT 1 <KW>`.
323            Keyword::FROM
324            | Keyword::GROUP
325            | Keyword::HAVING
326            | Keyword::INTERSECT
327            | Keyword::INTO
328            | Keyword::MINUS
329            | Keyword::ORDER
330            | Keyword::SELECT
331            | Keyword::UNION
332            | Keyword::WHERE
333            | Keyword::WITH => false,
334
335            // Any other word is considered an alias
336            _ => true,
337        }
338    }
339
340    /// See: <https://docs.snowflake.com/en/sql-reference/constructs/at-before>
341    fn supports_timestamp_versioning(&self) -> bool {
342        true
343    }
344
345    /// See: <https://docs.snowflake.com/en/sql-reference/constructs/group-by>
346    fn supports_group_by_expr(&self) -> bool {
347        true
348    }
349}
350
351fn parse_file_staging_command(kw: Keyword, parser: &mut Parser) -> Result<Statement, ParserError> {
352    let stage = parse_snowflake_stage_name(parser)?;
353    let pattern = if parser.parse_keyword(Keyword::PATTERN) {
354        parser.expect_token(&Token::Eq)?;
355        Some(parser.parse_literal_string()?)
356    } else {
357        None
358    };
359
360    match kw {
361        Keyword::LIST | Keyword::LS => Ok(Statement::List(FileStagingCommand { stage, pattern })),
362        Keyword::REMOVE | Keyword::RM => {
363            Ok(Statement::Remove(FileStagingCommand { stage, pattern }))
364        }
365        _ => Err(ParserError::ParserError(
366            "unexpected stage command, expecting LIST, LS, REMOVE or RM".to_string(),
367        )),
368    }
369}
370
371/// Parse snowflake alter session.
372/// <https://docs.snowflake.com/en/sql-reference/sql/alter-session>
373fn parse_alter_session(parser: &mut Parser, set: bool) -> Result<Statement, ParserError> {
374    let session_options = parse_session_options(parser, set)?;
375    Ok(Statement::AlterSession {
376        set,
377        session_params: KeyValueOptions {
378            options: session_options,
379        },
380    })
381}
382
383/// Parse snowflake create table statement.
384/// <https://docs.snowflake.com/en/sql-reference/sql/create-table>
385/// <https://docs.snowflake.com/en/sql-reference/sql/create-iceberg-table>
386pub fn parse_create_table(
387    or_replace: bool,
388    global: Option<bool>,
389    temporary: bool,
390    volatile: bool,
391    transient: bool,
392    iceberg: bool,
393    parser: &mut Parser,
394) -> Result<Statement, ParserError> {
395    let if_not_exists = parser.parse_keywords(&[Keyword::IF, Keyword::NOT, Keyword::EXISTS]);
396    let table_name = parser.parse_object_name(false)?;
397
398    let mut builder = CreateTableBuilder::new(table_name)
399        .or_replace(or_replace)
400        .if_not_exists(if_not_exists)
401        .temporary(temporary)
402        .transient(transient)
403        .volatile(volatile)
404        .iceberg(iceberg)
405        .global(global)
406        .hive_formats(Some(Default::default()));
407
408    // Snowflake does not enforce order of the parameters in the statement. The parser needs to
409    // parse the statement in a loop.
410    //
411    // "CREATE TABLE x COPY GRANTS (c INT)" and "CREATE TABLE x (c INT) COPY GRANTS" are both
412    // accepted by Snowflake
413
414    loop {
415        let next_token = parser.next_token();
416        match &next_token.token {
417            Token::Word(word) => match word.keyword {
418                Keyword::COPY => {
419                    parser.expect_keyword_is(Keyword::GRANTS)?;
420                    builder = builder.copy_grants(true);
421                }
422                Keyword::COMMENT => {
423                    // Rewind the COMMENT keyword
424                    parser.prev_token();
425                    builder = builder.comment(parser.parse_optional_inline_comment()?);
426                }
427                Keyword::AS => {
428                    let query = parser.parse_query()?;
429                    builder = builder.query(Some(query));
430                    break;
431                }
432                Keyword::CLONE => {
433                    let clone = parser.parse_object_name(false).ok();
434                    builder = builder.clone_clause(clone);
435                    break;
436                }
437                Keyword::LIKE => {
438                    let like = parser.parse_object_name(false).ok();
439                    builder = builder.like(like);
440                    break;
441                }
442                Keyword::CLUSTER => {
443                    parser.expect_keyword_is(Keyword::BY)?;
444                    parser.expect_token(&Token::LParen)?;
445                    let cluster_by = Some(WrappedCollection::Parentheses(
446                        parser.parse_comma_separated(|p| p.parse_identifier())?,
447                    ));
448                    parser.expect_token(&Token::RParen)?;
449
450                    builder = builder.cluster_by(cluster_by)
451                }
452                Keyword::ENABLE_SCHEMA_EVOLUTION => {
453                    parser.expect_token(&Token::Eq)?;
454                    let enable_schema_evolution =
455                        match parser.parse_one_of_keywords(&[Keyword::TRUE, Keyword::FALSE]) {
456                            Some(Keyword::TRUE) => true,
457                            Some(Keyword::FALSE) => false,
458                            _ => {
459                                return parser.expected("TRUE or FALSE", next_token);
460                            }
461                        };
462
463                    builder = builder.enable_schema_evolution(Some(enable_schema_evolution));
464                }
465                Keyword::CHANGE_TRACKING => {
466                    parser.expect_token(&Token::Eq)?;
467                    let change_tracking =
468                        match parser.parse_one_of_keywords(&[Keyword::TRUE, Keyword::FALSE]) {
469                            Some(Keyword::TRUE) => true,
470                            Some(Keyword::FALSE) => false,
471                            _ => {
472                                return parser.expected("TRUE or FALSE", next_token);
473                            }
474                        };
475
476                    builder = builder.change_tracking(Some(change_tracking));
477                }
478                Keyword::DATA_RETENTION_TIME_IN_DAYS => {
479                    parser.expect_token(&Token::Eq)?;
480                    let data_retention_time_in_days = parser.parse_literal_uint()?;
481                    builder =
482                        builder.data_retention_time_in_days(Some(data_retention_time_in_days));
483                }
484                Keyword::MAX_DATA_EXTENSION_TIME_IN_DAYS => {
485                    parser.expect_token(&Token::Eq)?;
486                    let max_data_extension_time_in_days = parser.parse_literal_uint()?;
487                    builder = builder
488                        .max_data_extension_time_in_days(Some(max_data_extension_time_in_days));
489                }
490                Keyword::DEFAULT_DDL_COLLATION => {
491                    parser.expect_token(&Token::Eq)?;
492                    let default_ddl_collation = parser.parse_literal_string()?;
493                    builder = builder.default_ddl_collation(Some(default_ddl_collation));
494                }
495                // WITH is optional, we just verify that next token is one of the expected ones and
496                // fallback to the default match statement
497                Keyword::WITH => {
498                    parser.expect_one_of_keywords(&[
499                        Keyword::AGGREGATION,
500                        Keyword::TAG,
501                        Keyword::ROW,
502                    ])?;
503                    parser.prev_token();
504                }
505                Keyword::AGGREGATION => {
506                    parser.expect_keyword_is(Keyword::POLICY)?;
507                    let aggregation_policy = parser.parse_object_name(false)?;
508                    builder = builder.with_aggregation_policy(Some(aggregation_policy));
509                }
510                Keyword::ROW => {
511                    parser.expect_keywords(&[Keyword::ACCESS, Keyword::POLICY])?;
512                    let policy = parser.parse_object_name(false)?;
513                    parser.expect_keyword_is(Keyword::ON)?;
514                    parser.expect_token(&Token::LParen)?;
515                    let columns = parser.parse_comma_separated(|p| p.parse_identifier())?;
516                    parser.expect_token(&Token::RParen)?;
517
518                    builder =
519                        builder.with_row_access_policy(Some(RowAccessPolicy::new(policy, columns)))
520                }
521                Keyword::TAG => {
522                    parser.expect_token(&Token::LParen)?;
523                    let tags = parser.parse_comma_separated(Parser::parse_tag)?;
524                    parser.expect_token(&Token::RParen)?;
525                    builder = builder.with_tags(Some(tags));
526                }
527                Keyword::ON if parser.parse_keyword(Keyword::COMMIT) => {
528                    let on_commit = Some(parser.parse_create_table_on_commit()?);
529                    builder = builder.on_commit(on_commit);
530                }
531                Keyword::EXTERNAL_VOLUME => {
532                    parser.expect_token(&Token::Eq)?;
533                    builder.external_volume = Some(parser.parse_literal_string()?);
534                }
535                Keyword::CATALOG => {
536                    parser.expect_token(&Token::Eq)?;
537                    builder.catalog = Some(parser.parse_literal_string()?);
538                }
539                Keyword::BASE_LOCATION => {
540                    parser.expect_token(&Token::Eq)?;
541                    builder.base_location = Some(parser.parse_literal_string()?);
542                }
543                Keyword::CATALOG_SYNC => {
544                    parser.expect_token(&Token::Eq)?;
545                    builder.catalog_sync = Some(parser.parse_literal_string()?);
546                }
547                Keyword::STORAGE_SERIALIZATION_POLICY => {
548                    parser.expect_token(&Token::Eq)?;
549
550                    builder.storage_serialization_policy =
551                        Some(parse_storage_serialization_policy(parser)?);
552                }
553                _ => {
554                    return parser.expected("end of statement", next_token);
555                }
556            },
557            Token::LParen => {
558                parser.prev_token();
559                let (columns, constraints) = parser.parse_columns()?;
560                builder = builder.columns(columns).constraints(constraints);
561            }
562            Token::EOF => {
563                if builder.columns.is_empty() {
564                    return Err(ParserError::ParserError(
565                        "unexpected end of input".to_string(),
566                    ));
567                }
568
569                break;
570            }
571            Token::SemiColon => {
572                if builder.columns.is_empty() {
573                    return Err(ParserError::ParserError(
574                        "unexpected end of input".to_string(),
575                    ));
576                }
577
578                parser.prev_token();
579                break;
580            }
581            _ => {
582                return parser.expected("end of statement", next_token);
583            }
584        }
585    }
586
587    if iceberg && builder.base_location.is_none() {
588        return Err(ParserError::ParserError(
589            "BASE_LOCATION is required for ICEBERG tables".to_string(),
590        ));
591    }
592
593    Ok(builder.build())
594}
595
596pub fn parse_storage_serialization_policy(
597    parser: &mut Parser,
598) -> Result<StorageSerializationPolicy, ParserError> {
599    let next_token = parser.next_token();
600    match &next_token.token {
601        Token::Word(w) => match w.keyword {
602            Keyword::COMPATIBLE => Ok(StorageSerializationPolicy::Compatible),
603            Keyword::OPTIMIZED => Ok(StorageSerializationPolicy::Optimized),
604            _ => parser.expected("storage_serialization_policy", next_token),
605        },
606        _ => parser.expected("storage_serialization_policy", next_token),
607    }
608}
609
610pub fn parse_create_stage(
611    or_replace: bool,
612    temporary: bool,
613    parser: &mut Parser,
614) -> Result<Statement, ParserError> {
615    //[ IF NOT EXISTS ]
616    let if_not_exists = parser.parse_keywords(&[Keyword::IF, Keyword::NOT, Keyword::EXISTS]);
617    let name = parser.parse_object_name(false)?;
618    let mut directory_table_params = Vec::new();
619    let mut file_format = Vec::new();
620    let mut copy_options = Vec::new();
621    let mut comment = None;
622
623    // [ internalStageParams | externalStageParams ]
624    let stage_params = parse_stage_params(parser)?;
625
626    // [ directoryTableParams ]
627    if parser.parse_keyword(Keyword::DIRECTORY) {
628        parser.expect_token(&Token::Eq)?;
629        directory_table_params = parse_parentheses_options(parser)?;
630    }
631
632    // [ file_format]
633    if parser.parse_keyword(Keyword::FILE_FORMAT) {
634        parser.expect_token(&Token::Eq)?;
635        file_format = parse_parentheses_options(parser)?;
636    }
637
638    // [ copy_options ]
639    if parser.parse_keyword(Keyword::COPY_OPTIONS) {
640        parser.expect_token(&Token::Eq)?;
641        copy_options = parse_parentheses_options(parser)?;
642    }
643
644    // [ comment ]
645    if parser.parse_keyword(Keyword::COMMENT) {
646        parser.expect_token(&Token::Eq)?;
647        comment = Some(match parser.next_token().token {
648            Token::SingleQuotedString(word) => Ok(word),
649            _ => parser.expected("a comment statement", parser.peek_token()),
650        }?)
651    }
652
653    Ok(Statement::CreateStage {
654        or_replace,
655        temporary,
656        if_not_exists,
657        name,
658        stage_params,
659        directory_table_params: KeyValueOptions {
660            options: directory_table_params,
661        },
662        file_format: KeyValueOptions {
663            options: file_format,
664        },
665        copy_options: KeyValueOptions {
666            options: copy_options,
667        },
668        comment,
669    })
670}
671
672pub fn parse_stage_name_identifier(parser: &mut Parser) -> Result<Ident, ParserError> {
673    let mut ident = String::new();
674    while let Some(next_token) = parser.next_token_no_skip() {
675        match &next_token.token {
676            Token::Whitespace(_) | Token::SemiColon => break,
677            Token::Period => {
678                parser.prev_token();
679                break;
680            }
681            Token::RParen => {
682                parser.prev_token();
683                break;
684            }
685            Token::AtSign => ident.push('@'),
686            Token::Tilde => ident.push('~'),
687            Token::Mod => ident.push('%'),
688            Token::Div => ident.push('/'),
689            Token::Word(w) => ident.push_str(&w.to_string()),
690            _ => return parser.expected("stage name identifier", parser.peek_token()),
691        }
692    }
693    Ok(Ident::new(ident))
694}
695
696pub fn parse_snowflake_stage_name(parser: &mut Parser) -> Result<ObjectName, ParserError> {
697    match parser.next_token().token {
698        Token::AtSign => {
699            parser.prev_token();
700            let mut idents = vec![];
701            loop {
702                idents.push(parse_stage_name_identifier(parser)?);
703                if !parser.consume_token(&Token::Period) {
704                    break;
705                }
706            }
707            Ok(ObjectName::from(idents))
708        }
709        _ => {
710            parser.prev_token();
711            Ok(parser.parse_object_name(false)?)
712        }
713    }
714}
715
716/// Parses a `COPY INTO` statement. Snowflake has two variants, `COPY INTO <table>`
717/// and `COPY INTO <location>` which have different syntax.
718pub fn parse_copy_into(parser: &mut Parser) -> Result<Statement, ParserError> {
719    let kind = match parser.peek_token().token {
720        // Indicates an internal stage
721        Token::AtSign => CopyIntoSnowflakeKind::Location,
722        // Indicates an external stage, i.e. s3://, gcs:// or azure://
723        Token::SingleQuotedString(s) if s.contains("://") => CopyIntoSnowflakeKind::Location,
724        _ => CopyIntoSnowflakeKind::Table,
725    };
726
727    let mut files: Vec<String> = vec![];
728    let mut from_transformations: Option<Vec<StageLoadSelectItem>> = None;
729    let mut from_stage_alias = None;
730    let mut from_stage = None;
731    let mut stage_params = StageParamsObject {
732        url: None,
733        encryption: KeyValueOptions { options: vec![] },
734        endpoint: None,
735        storage_integration: None,
736        credentials: KeyValueOptions { options: vec![] },
737    };
738    let mut from_query = None;
739    let mut partition = None;
740    let mut file_format = Vec::new();
741    let mut pattern = None;
742    let mut validation_mode = None;
743    let mut copy_options = Vec::new();
744
745    let into: ObjectName = parse_snowflake_stage_name(parser)?;
746    if kind == CopyIntoSnowflakeKind::Location {
747        stage_params = parse_stage_params(parser)?;
748    }
749
750    parser.expect_keyword_is(Keyword::FROM)?;
751    match parser.next_token().token {
752        Token::LParen if kind == CopyIntoSnowflakeKind::Table => {
753            // Data load with transformations
754            parser.expect_keyword_is(Keyword::SELECT)?;
755            from_transformations = parse_select_items_for_data_load(parser)?;
756
757            parser.expect_keyword_is(Keyword::FROM)?;
758            from_stage = Some(parse_snowflake_stage_name(parser)?);
759            stage_params = parse_stage_params(parser)?;
760
761            // as
762            from_stage_alias = if parser.parse_keyword(Keyword::AS) {
763                Some(match parser.next_token().token {
764                    Token::Word(w) => Ok(Ident::new(w.value)),
765                    _ => parser.expected("stage alias", parser.peek_token()),
766                }?)
767            } else {
768                None
769            };
770            parser.expect_token(&Token::RParen)?;
771        }
772        Token::LParen if kind == CopyIntoSnowflakeKind::Location => {
773            // Data unload with a query
774            from_query = Some(parser.parse_query()?);
775            parser.expect_token(&Token::RParen)?;
776        }
777        _ => {
778            parser.prev_token();
779            from_stage = Some(parse_snowflake_stage_name(parser)?);
780            stage_params = parse_stage_params(parser)?;
781
782            // as
783            from_stage_alias = if parser.parse_keyword(Keyword::AS) {
784                Some(match parser.next_token().token {
785                    Token::Word(w) => Ok(Ident::new(w.value)),
786                    _ => parser.expected("stage alias", parser.peek_token()),
787                }?)
788            } else {
789                None
790            };
791        }
792    }
793
794    loop {
795        // FILE_FORMAT
796        if parser.parse_keyword(Keyword::FILE_FORMAT) {
797            parser.expect_token(&Token::Eq)?;
798            file_format = parse_parentheses_options(parser)?;
799        // PARTITION BY
800        } else if parser.parse_keywords(&[Keyword::PARTITION, Keyword::BY]) {
801            partition = Some(Box::new(parser.parse_expr()?))
802        // FILES
803        } else if parser.parse_keyword(Keyword::FILES) {
804            parser.expect_token(&Token::Eq)?;
805            parser.expect_token(&Token::LParen)?;
806            let mut continue_loop = true;
807            while continue_loop {
808                continue_loop = false;
809                let next_token = parser.next_token();
810                match next_token.token {
811                    Token::SingleQuotedString(s) => files.push(s),
812                    _ => parser.expected("file token", next_token)?,
813                };
814                if parser.next_token().token.eq(&Token::Comma) {
815                    continue_loop = true;
816                } else {
817                    parser.prev_token(); // not a comma, need to go back
818                }
819            }
820            parser.expect_token(&Token::RParen)?;
821        // PATTERN
822        } else if parser.parse_keyword(Keyword::PATTERN) {
823            parser.expect_token(&Token::Eq)?;
824            let next_token = parser.next_token();
825            pattern = Some(match next_token.token {
826                Token::SingleQuotedString(s) => s,
827                _ => parser.expected("pattern", next_token)?,
828            });
829        // VALIDATION MODE
830        } else if parser.parse_keyword(Keyword::VALIDATION_MODE) {
831            parser.expect_token(&Token::Eq)?;
832            validation_mode = Some(parser.next_token().token.to_string());
833        // COPY OPTIONS
834        } else if parser.parse_keyword(Keyword::COPY_OPTIONS) {
835            parser.expect_token(&Token::Eq)?;
836            copy_options = parse_parentheses_options(parser)?;
837        } else {
838            match parser.next_token().token {
839                Token::SemiColon | Token::EOF => break,
840                Token::Comma => continue,
841                // In `COPY INTO <location>` the copy options do not have a shared key
842                // like in `COPY INTO <table>`
843                Token::Word(key) => copy_options.push(parse_option(parser, key)?),
844                _ => return parser.expected("another copy option, ; or EOF'", parser.peek_token()),
845            }
846        }
847    }
848
849    Ok(Statement::CopyIntoSnowflake {
850        kind,
851        into,
852        from_obj: from_stage,
853        from_obj_alias: from_stage_alias,
854        stage_params,
855        from_transformations,
856        from_query,
857        files: if files.is_empty() { None } else { Some(files) },
858        pattern,
859        file_format: KeyValueOptions {
860            options: file_format,
861        },
862        copy_options: KeyValueOptions {
863            options: copy_options,
864        },
865        validation_mode,
866        partition,
867    })
868}
869
870fn parse_select_items_for_data_load(
871    parser: &mut Parser,
872) -> Result<Option<Vec<StageLoadSelectItem>>, ParserError> {
873    // [<alias>.]$<file_col_num>[.<element>] [ , [<alias>.]$<file_col_num>[.<element>] ... ]
874    let mut select_items: Vec<StageLoadSelectItem> = vec![];
875    loop {
876        let mut alias: Option<Ident> = None;
877        let mut file_col_num: i32 = 0;
878        let mut element: Option<Ident> = None;
879        let mut item_as: Option<Ident> = None;
880
881        let next_token = parser.next_token();
882        match next_token.token {
883            Token::Placeholder(w) => {
884                file_col_num = w.to_string().split_off(1).parse::<i32>().map_err(|e| {
885                    ParserError::ParserError(format!("Could not parse '{w}' as i32: {e}"))
886                })?;
887                Ok(())
888            }
889            Token::Word(w) => {
890                alias = Some(Ident::new(w.value));
891                Ok(())
892            }
893            _ => parser.expected("alias or file_col_num", next_token),
894        }?;
895
896        if alias.is_some() {
897            parser.expect_token(&Token::Period)?;
898            // now we get col_num token
899            let col_num_token = parser.next_token();
900            match col_num_token.token {
901                Token::Placeholder(w) => {
902                    file_col_num = w.to_string().split_off(1).parse::<i32>().map_err(|e| {
903                        ParserError::ParserError(format!("Could not parse '{w}' as i32: {e}"))
904                    })?;
905                    Ok(())
906                }
907                _ => parser.expected("file_col_num", col_num_token),
908            }?;
909        }
910
911        // try extracting optional element
912        match parser.next_token().token {
913            Token::Colon => {
914                // parse element
915                element = Some(Ident::new(match parser.next_token().token {
916                    Token::Word(w) => Ok(w.value),
917                    _ => parser.expected("file_col_num", parser.peek_token()),
918                }?));
919            }
920            _ => {
921                // element not present move back
922                parser.prev_token();
923            }
924        }
925
926        // as
927        if parser.parse_keyword(Keyword::AS) {
928            item_as = Some(match parser.next_token().token {
929                Token::Word(w) => Ok(Ident::new(w.value)),
930                _ => parser.expected("column item alias", parser.peek_token()),
931            }?);
932        }
933
934        select_items.push(StageLoadSelectItem {
935            alias,
936            file_col_num,
937            element,
938            item_as,
939        });
940
941        match parser.next_token().token {
942            Token::Comma => {
943                // continue
944            }
945            _ => {
946                parser.prev_token(); // need to move back
947                break;
948            }
949        }
950    }
951    Ok(Some(select_items))
952}
953
954fn parse_stage_params(parser: &mut Parser) -> Result<StageParamsObject, ParserError> {
955    let (mut url, mut storage_integration, mut endpoint) = (None, None, None);
956    let mut encryption: KeyValueOptions = KeyValueOptions { options: vec![] };
957    let mut credentials: KeyValueOptions = KeyValueOptions { options: vec![] };
958
959    // URL
960    if parser.parse_keyword(Keyword::URL) {
961        parser.expect_token(&Token::Eq)?;
962        url = Some(match parser.next_token().token {
963            Token::SingleQuotedString(word) => Ok(word),
964            _ => parser.expected("a URL statement", parser.peek_token()),
965        }?)
966    }
967
968    // STORAGE INTEGRATION
969    if parser.parse_keyword(Keyword::STORAGE_INTEGRATION) {
970        parser.expect_token(&Token::Eq)?;
971        storage_integration = Some(parser.next_token().token.to_string());
972    }
973
974    // ENDPOINT
975    if parser.parse_keyword(Keyword::ENDPOINT) {
976        parser.expect_token(&Token::Eq)?;
977        endpoint = Some(match parser.next_token().token {
978            Token::SingleQuotedString(word) => Ok(word),
979            _ => parser.expected("an endpoint statement", parser.peek_token()),
980        }?)
981    }
982
983    // CREDENTIALS
984    if parser.parse_keyword(Keyword::CREDENTIALS) {
985        parser.expect_token(&Token::Eq)?;
986        credentials = KeyValueOptions {
987            options: parse_parentheses_options(parser)?,
988        };
989    }
990
991    // ENCRYPTION
992    if parser.parse_keyword(Keyword::ENCRYPTION) {
993        parser.expect_token(&Token::Eq)?;
994        encryption = KeyValueOptions {
995            options: parse_parentheses_options(parser)?,
996        };
997    }
998
999    Ok(StageParamsObject {
1000        url,
1001        encryption,
1002        endpoint,
1003        storage_integration,
1004        credentials,
1005    })
1006}
1007
1008/// Parses options separated by blank spaces, commas, or new lines like:
1009/// ABORT_DETACHED_QUERY = { TRUE | FALSE }
1010///      [ ACTIVE_PYTHON_PROFILER = { 'LINE' | 'MEMORY' } ]
1011///      [ BINARY_INPUT_FORMAT = '\<string\>' ]
1012fn parse_session_options(
1013    parser: &mut Parser,
1014    set: bool,
1015) -> Result<Vec<KeyValueOption>, ParserError> {
1016    let mut options: Vec<KeyValueOption> = Vec::new();
1017    let empty = String::new;
1018    loop {
1019        match parser.next_token().token {
1020            Token::Comma => continue,
1021            Token::Word(key) => {
1022                if set {
1023                    let option = parse_option(parser, key)?;
1024                    options.push(option);
1025                } else {
1026                    options.push(KeyValueOption {
1027                        option_name: key.value,
1028                        option_type: KeyValueOptionType::STRING,
1029                        value: empty(),
1030                    });
1031                }
1032            }
1033            _ => {
1034                if parser.peek_token().token == Token::EOF {
1035                    break;
1036                }
1037                return parser.expected("another option", parser.peek_token());
1038            }
1039        }
1040    }
1041    options
1042        .is_empty()
1043        .then(|| {
1044            Err(ParserError::ParserError(
1045                "expected at least one option".to_string(),
1046            ))
1047        })
1048        .unwrap_or(Ok(options))
1049}
1050
1051/// Parses options provided within parentheses like:
1052/// ( ENABLE = { TRUE | FALSE }
1053///      [ AUTO_REFRESH = { TRUE | FALSE } ]
1054///      [ REFRESH_ON_CREATE =  { TRUE | FALSE } ]
1055///      [ NOTIFICATION_INTEGRATION = '<notification_integration_name>' ] )
1056///
1057fn parse_parentheses_options(parser: &mut Parser) -> Result<Vec<KeyValueOption>, ParserError> {
1058    let mut options: Vec<KeyValueOption> = Vec::new();
1059    parser.expect_token(&Token::LParen)?;
1060    loop {
1061        match parser.next_token().token {
1062            Token::RParen => break,
1063            Token::Comma => continue,
1064            Token::Word(key) => options.push(parse_option(parser, key)?),
1065            _ => return parser.expected("another option or ')'", parser.peek_token()),
1066        };
1067    }
1068    Ok(options)
1069}
1070
1071/// Parses a `KEY = VALUE` construct based on the specified key
1072fn parse_option(parser: &mut Parser, key: Word) -> Result<KeyValueOption, ParserError> {
1073    parser.expect_token(&Token::Eq)?;
1074    if parser.parse_keyword(Keyword::TRUE) {
1075        Ok(KeyValueOption {
1076            option_name: key.value,
1077            option_type: KeyValueOptionType::BOOLEAN,
1078            value: "TRUE".to_string(),
1079        })
1080    } else if parser.parse_keyword(Keyword::FALSE) {
1081        Ok(KeyValueOption {
1082            option_name: key.value,
1083            option_type: KeyValueOptionType::BOOLEAN,
1084            value: "FALSE".to_string(),
1085        })
1086    } else {
1087        match parser.next_token().token {
1088            Token::SingleQuotedString(value) => Ok(KeyValueOption {
1089                option_name: key.value,
1090                option_type: KeyValueOptionType::STRING,
1091                value,
1092            }),
1093            Token::Word(word) => Ok(KeyValueOption {
1094                option_name: key.value,
1095                option_type: KeyValueOptionType::ENUM,
1096                value: word.value,
1097            }),
1098            Token::Number(n, _) => Ok(KeyValueOption {
1099                option_name: key.value,
1100                option_type: KeyValueOptionType::NUMBER,
1101                value: n,
1102            }),
1103            _ => parser.expected("expected option value", parser.peek_token()),
1104        }
1105    }
1106}
1107
1108/// Parsing a property of identity or autoincrement column option
1109/// Syntax:
1110/// ```sql
1111/// [ (seed , increment) | START num INCREMENT num ] [ ORDER | NOORDER ]
1112/// ```
1113/// [Snowflake]: https://docs.snowflake.com/en/sql-reference/sql/create-table
1114fn parse_identity_property(parser: &mut Parser) -> Result<IdentityProperty, ParserError> {
1115    let parameters = if parser.consume_token(&Token::LParen) {
1116        let seed = parser.parse_number()?;
1117        parser.expect_token(&Token::Comma)?;
1118        let increment = parser.parse_number()?;
1119        parser.expect_token(&Token::RParen)?;
1120
1121        Some(IdentityPropertyFormatKind::FunctionCall(
1122            IdentityParameters { seed, increment },
1123        ))
1124    } else if parser.parse_keyword(Keyword::START) {
1125        let seed = parser.parse_number()?;
1126        parser.expect_keyword_is(Keyword::INCREMENT)?;
1127        let increment = parser.parse_number()?;
1128
1129        Some(IdentityPropertyFormatKind::StartAndIncrement(
1130            IdentityParameters { seed, increment },
1131        ))
1132    } else {
1133        None
1134    };
1135    let order = match parser.parse_one_of_keywords(&[Keyword::ORDER, Keyword::NOORDER]) {
1136        Some(Keyword::ORDER) => Some(IdentityPropertyOrder::Order),
1137        Some(Keyword::NOORDER) => Some(IdentityPropertyOrder::NoOrder),
1138        _ => None,
1139    };
1140    Ok(IdentityProperty { parameters, order })
1141}
1142
1143/// Parsing a policy property of column option
1144/// Syntax:
1145/// ```sql
1146/// <policy_name> [ USING ( <col_name> , <cond_col1> , ... )
1147/// ```
1148/// [Snowflake]: https://docs.snowflake.com/en/sql-reference/sql/create-table
1149fn parse_column_policy_property(
1150    parser: &mut Parser,
1151    with: bool,
1152) -> Result<ColumnPolicyProperty, ParserError> {
1153    let policy_name = parser.parse_identifier()?;
1154    let using_columns = if parser.parse_keyword(Keyword::USING) {
1155        parser.expect_token(&Token::LParen)?;
1156        let columns = parser.parse_comma_separated(|p| p.parse_identifier())?;
1157        parser.expect_token(&Token::RParen)?;
1158        Some(columns)
1159    } else {
1160        None
1161    };
1162
1163    Ok(ColumnPolicyProperty {
1164        with,
1165        policy_name,
1166        using_columns,
1167    })
1168}
1169
1170/// Parsing tags list of column
1171/// Syntax:
1172/// ```sql
1173/// ( <tag_name> = '<tag_value>' [ , <tag_name> = '<tag_value>' , ... ] )
1174/// ```
1175/// [Snowflake]: https://docs.snowflake.com/en/sql-reference/sql/create-table
1176fn parse_column_tags(parser: &mut Parser, with: bool) -> Result<TagsColumnOption, ParserError> {
1177    parser.expect_token(&Token::LParen)?;
1178    let tags = parser.parse_comma_separated(Parser::parse_tag)?;
1179    parser.expect_token(&Token::RParen)?;
1180
1181    Ok(TagsColumnOption { with, tags })
1182}
1183
1184/// Parse snowflake show objects.
1185/// <https://docs.snowflake.com/en/sql-reference/sql/show-objects>
1186fn parse_show_objects(terse: bool, parser: &mut Parser) -> Result<Statement, ParserError> {
1187    let show_options = parser.parse_show_stmt_options()?;
1188    Ok(Statement::ShowObjects(ShowObjects {
1189        terse,
1190        show_options,
1191    }))
1192}
sqlparser/dialect/snowflake.rs

sqlparser/dialect/
snowflake.rs
