Skip to content

Commit f2b0c89

Browse files
kawashiroalanmcruickshankWittierDinosaur
authored
Improve Snowflake syntax support (#5770)
Co-authored-by: Alan Cruickshank <[email protected]> Co-authored-by: Alan Cruickshank <[email protected]> Co-authored-by: Danny Jones <[email protected]>
1 parent 26630ce commit f2b0c89

12 files changed

Lines changed: 491 additions & 254 deletions

File tree

‎src/sqlfluff/core/parser/grammar/anyof.py‎

Lines changed: 6 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -157,7 +157,12 @@ def match(
157157
max_idx = trim_to_terminator(
158158
segments,
159159
idx,
160-
terminators=[*self.terminators, *parse_context.terminators],
160+
terminators=(
161+
# Only pass through the context terminators if not resetting.
162+
self.terminators
163+
if self.reset_terminators
164+
else [*self.terminators, *parse_context.terminators]
165+
),
161166
parse_context=parse_context,
162167
)
163168

‎src/sqlfluff/core/parser/grammar/base.py‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -219,7 +219,7 @@ def copy(
219219
self: T,
220220
insert: Optional[List[Matchable]] = None,
221221
at: Optional[int] = None,
222-
before: Optional[Any] = None,
222+
before: Optional[Matchable] = None,
223223
remove: Optional[List[Matchable]] = None,
224224
terminators: List[Union[str, Matchable]] = [],
225225
replace_terminators: bool = False,

‎src/sqlfluff/dialects/dialect_snowflake.py‎

Lines changed: 79 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -618,6 +618,7 @@
618618
OneOf(
619619
Ref("SingleQuotedIdentifierSegment"),
620620
Ref("ReferencedVariableNameSegment"),
621+
Ref("BindVariableSegment"),
621622
),
622623
),
623624
),
@@ -1255,6 +1256,14 @@ class StatementSegment(ansi.StatementSegment):
12551256
"""A generic segment, to any of its child subsegments."""
12561257

12571258
match_grammar = ansi.StatementSegment.match_grammar.copy(
1259+
# NOTE: The Scripting Block segment must be tried before
1260+
# we get to the transaction statement (from the ansi dialect)
1261+
# because they both start with BEGIN.
1262+
insert=[
1263+
Ref("ScriptingBlockStatementSegment"),
1264+
],
1265+
before=Ref("TransactionStatementSegment"),
1266+
).copy(
12581267
insert=[
12591268
Ref("AccessStatementSegment"),
12601269
Ref("CreateStatementSegment"),
@@ -1263,7 +1272,6 @@ class StatementSegment(ansi.StatementSegment):
12631272
Ref("CreateCloneStatementSegment"),
12641273
Ref("CreateProcedureStatementSegment"),
12651274
Ref("AlterProcedureStatementSegment"),
1266-
Ref("ScriptingBlockStatementSegment"),
12671275
Ref("ScriptingLetStatementSegment"),
12681276
Ref("ReturnStatementSegment"),
12691277
Ref("ShowStatementSegment"),
@@ -1326,6 +1334,7 @@ class StatementSegment(ansi.StatementSegment):
13261334
Ref("CreateExternalVolumeStatementSegment"),
13271335
Ref("DropExternalVolumeStatementSegment"),
13281336
Ref("AlterExternalVolumeStatementSegment"),
1337+
Ref("ForInLoopSegment"),
13291338
],
13301339
remove=[
13311340
Ref("CreateIndexStatementSegment"),
@@ -3119,14 +3128,33 @@ class ScriptingBlockStatementSegment(BaseSegment):
31193128
"""
31203129

31213130
type = "scripting_block_statement"
3122-
match_grammar = OneOf(
3131+
match_grammar = Sequence(
31233132
Sequence(
31243133
"BEGIN",
3125-
Delimited(
3134+
Indent,
3135+
Ref("StatementSegment"),
3136+
),
3137+
AnyNumberOf(
3138+
Sequence(
3139+
Ref("DelimiterGrammar"),
31263140
Ref("StatementSegment"),
31273141
),
3142+
terminators=[
3143+
OneOf(
3144+
Sequence(Ref("DelimiterGrammar"), "END"),
3145+
# Don't terminate on an "END FOR", because that's a different
3146+
# expression.
3147+
exclude=Sequence(Ref("DelimiterGrammar"), "END", "FOR"),
3148+
),
3149+
],
3150+
# NOTE: We can't be greedy because there may be nested loops. This
3151+
# does make understanding any failed parsing loops difficult but I
3152+
# don't think there's an easy way around that.
31283153
),
3129-
Sequence("END"),
3154+
Ref("DelimiterGrammar"),
3155+
Dedent,
3156+
"END",
3157+
reset_terminators=True,
31303158
)
31313159

31323160

@@ -7132,6 +7160,8 @@ class TransactionStatementSegment(ansi.TransactionStatementSegment):
71327160
https://docs.snowflake.com/en/sql-reference/sql/begin.html
71337161
https://docs.snowflake.com/en/sql-reference/sql/commit.html
71347162
https://docs.snowflake.com/en/sql-reference/sql/rollback.html
7163+
7164+
NOTE: "END" is not currently a supported keyword here.
71357165
"""
71367166

71377167
match_grammar = OneOf(
@@ -7828,3 +7858,48 @@ class AlterMaskingPolicySegment(BaseSegment):
78287858
Sequence("UNSET", "COMMENT"),
78297859
),
78307860
)
7861+
7862+
7863+
class ForInLoopSegment(BaseSegment):
7864+
"""FOR...IN...DO...END FOR statement.
7865+
7866+
https://docs.snowflake.com/en/developer-guide/snowflake-scripting/loops#for-loop
7867+
"""
7868+
7869+
type = "for_in_statement"
7870+
7871+
match_grammar = Sequence(
7872+
Sequence(
7873+
Sequence(
7874+
"FOR",
7875+
Ref("LocalVariableNameSegment"),
7876+
"IN",
7877+
Ref("LocalVariableNameSegment"),
7878+
"DO",
7879+
Indent,
7880+
),
7881+
Delimited(
7882+
Ref("StatementSegment"),
7883+
delimiter=Ref("DelimiterGrammar"),
7884+
),
7885+
parse_mode=ParseMode.GREEDY_ONCE_STARTED,
7886+
reset_terminators=True,
7887+
terminators=[Sequence(Ref("DelimiterGrammar"), "END", "FOR")],
7888+
),
7889+
# There must be a trailing semicolon
7890+
Ref("DelimiterGrammar"),
7891+
Dedent,
7892+
"END",
7893+
"FOR",
7894+
)
7895+
7896+
7897+
class BindVariableSegment(BaseSegment):
7898+
"""A :VARIABLE_NAME expression."""
7899+
7900+
type = "bind_variable"
7901+
7902+
match_grammar = Sequence(
7903+
Ref("ColonSegment"),
7904+
Ref("LocalVariableNameSegment"),
7905+
)

‎test/fixtures/dialects/snowflake/begin_end.sql‎

Lines changed: 6 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,7 +1,11 @@
1-
begin;
1+
-- NOTE: This is a loop BEGIN, and not a transaction BEGIN,
2+
-- because BEGIN is NOT followed immediately by a ";"
3+
-- See: https://docs.snowflake.com/en/sql-reference/sql/begin
4+
-- See: https://docs.snowflake.com/en/sql-reference/snowflake-scripting/begin
5+
begin
26
select 1;
37
select 2;
4-
begin;
8+
begin
59
select 3;
610
select 4;
711
end;

‎test/fixtures/dialects/snowflake/begin_end.yml‎

Lines changed: 38 additions & 44 deletions
Original file line numberDiff line numberDiff line change
@@ -3,49 +3,43 @@
33
# computed by SQLFluff when running the tests. Please run
44
# `python test/generate_parse_fixture_yml.py` to generate them after adding or
55
# altering SQL files.
6-
_hash: f6916791b4ecddc4ace203e17b85a320cbabe527a66abe32ba71b53811c14cc5
6+
_hash: cdeba1691b01c319b7ecb004bfef43090c4eb5b684adccf1cff2663b2734b9fe
77
file:
8-
- statement:
9-
transaction_statement:
10-
keyword: begin
11-
- statement_terminator: ;
12-
- statement:
13-
select_statement:
14-
select_clause:
15-
keyword: select
16-
select_clause_element:
17-
numeric_literal: '1'
18-
- statement_terminator: ;
19-
- statement:
20-
select_statement:
21-
select_clause:
22-
keyword: select
23-
select_clause_element:
24-
numeric_literal: '2'
25-
- statement_terminator: ;
26-
- statement:
27-
transaction_statement:
28-
keyword: begin
29-
- statement_terminator: ;
30-
- statement:
31-
select_statement:
32-
select_clause:
33-
keyword: select
34-
select_clause_element:
35-
numeric_literal: '3'
36-
- statement_terminator: ;
37-
- statement:
38-
select_statement:
39-
select_clause:
40-
keyword: select
41-
select_clause_element:
42-
numeric_literal: '4'
43-
- statement_terminator: ;
44-
- statement:
8+
statement:
459
scripting_block_statement:
46-
keyword: end
47-
- statement_terminator: ;
48-
- statement:
49-
scripting_block_statement:
50-
keyword: end
51-
- statement_terminator: ;
10+
- keyword: begin
11+
- statement:
12+
select_statement:
13+
select_clause:
14+
keyword: select
15+
select_clause_element:
16+
numeric_literal: '1'
17+
- statement_terminator: ;
18+
- statement:
19+
select_statement:
20+
select_clause:
21+
keyword: select
22+
select_clause_element:
23+
numeric_literal: '2'
24+
- statement_terminator: ;
25+
- statement:
26+
scripting_block_statement:
27+
- keyword: begin
28+
- statement:
29+
select_statement:
30+
select_clause:
31+
keyword: select
32+
select_clause_element:
33+
numeric_literal: '3'
34+
- statement_terminator: ;
35+
- statement:
36+
select_statement:
37+
select_clause:
38+
keyword: select
39+
select_clause_element:
40+
numeric_literal: '4'
41+
- statement_terminator: ;
42+
- keyword: end
43+
- statement_terminator: ;
44+
- keyword: end
45+
statement_terminator: ;

‎test/fixtures/dialects/snowflake/create_procedure.yml‎

Lines changed: 32 additions & 34 deletions
Original file line numberDiff line numberDiff line change
@@ -3,7 +3,7 @@
33
# computed by SQLFluff when running the tests. Please run
44
# `python test/generate_parse_fixture_yml.py` to generate them after adding or
55
# altering SQL files.
6-
_hash: 8aaa4f8d66b88d7e19428bb0d8ce519633edb5d3a938e3fe4b0919bddff27708
6+
_hash: a1e78aed2e96871f7bea75234bcc4cf6d20cb79dacfcbc8df61eed5f658719a7
77
file:
88
- statement:
99
create_procedure_statement:
@@ -374,42 +374,40 @@ file:
374374
- keyword: SQL
375375
- keyword: AS
376376
- scripting_block_statement:
377-
keyword: BEGIN
378-
statement:
377+
- keyword: BEGIN
378+
- statement:
379379
select_statement:
380380
select_clause:
381381
keyword: select
382382
select_clause_element:
383383
numeric_literal: '1'
384-
- statement_terminator: ;
385-
- statement:
386-
select_statement:
387-
select_clause:
388-
keyword: select
389-
select_clause_element:
390-
numeric_literal: '2'
391-
- statement_terminator: ;
392-
- statement:
393-
select_statement:
394-
select_clause:
395-
keyword: select
396-
select_clause_element:
397-
numeric_literal: '3'
398-
- statement_terminator: ;
399-
- statement:
400-
select_statement:
401-
select_clause:
402-
keyword: select
403-
select_clause_element:
404-
numeric_literal: '4'
405-
- statement_terminator: ;
406-
- statement:
407-
return_statement:
408-
keyword: return
409-
expression:
410-
numeric_literal: '5'
411-
- statement_terminator: ;
412-
- statement:
413-
scripting_block_statement:
414-
keyword: END
384+
- statement_terminator: ;
385+
- statement:
386+
select_statement:
387+
select_clause:
388+
keyword: select
389+
select_clause_element:
390+
numeric_literal: '2'
391+
- statement_terminator: ;
392+
- statement:
393+
select_statement:
394+
select_clause:
395+
keyword: select
396+
select_clause_element:
397+
numeric_literal: '3'
398+
- statement_terminator: ;
399+
- statement:
400+
select_statement:
401+
select_clause:
402+
keyword: select
403+
select_clause_element:
404+
numeric_literal: '4'
405+
- statement_terminator: ;
406+
- statement:
407+
return_statement:
408+
keyword: return
409+
expression:
410+
numeric_literal: '5'
411+
- statement_terminator: ;
412+
- keyword: END
415413
- statement_terminator: ;

‎test/fixtures/dialects/snowflake/create_table.sql‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -133,3 +133,5 @@ CREATE OR REPLACE TABLE IF NOT EXISTS EXAMPLE_TABLE_WITH_RLS (
133133
CREATE OR REPLACE TABLE IF NOT EXISTS EXAMPLE_TABLE_WITH_RLS (
134134
EXAMPLE VARCHAR
135135
) WITH ROW ACCESS POLICY rls_policy ON (EXAMPLE);
136+
137+
CREATE TABLE IDENTIFIER(:SOME_TABLE) (AMOUNT NUMBER);

‎test/fixtures/dialects/snowflake/create_table.yml‎

Lines changed: 21 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -3,7 +3,7 @@
33
# computed by SQLFluff when running the tests. Please run
44
# `python test/generate_parse_fixture_yml.py` to generate them after adding or
55
# altering SQL files.
6-
_hash: 5713dc82fdf72799dbe6ac754f9882dbb3bd98243abbb0732eedc485ab74bed9
6+
_hash: 09ce4465cc81752ef2cc8eba0e1c3de1f2359fdf047a71d44494499f5fb5cc93
77
file:
88
- statement:
99
create_table_statement:
@@ -1202,3 +1202,23 @@ file:
12021202
naked_identifier: EXAMPLE
12031203
end_bracket: )
12041204
- statement_terminator: ;
1205+
- statement:
1206+
create_table_statement:
1207+
- keyword: CREATE
1208+
- keyword: TABLE
1209+
- table_reference:
1210+
keyword: IDENTIFIER
1211+
bracketed:
1212+
start_bracket: (
1213+
bind_variable:
1214+
colon: ':'
1215+
variable: SOME_TABLE
1216+
end_bracket: )
1217+
- bracketed:
1218+
start_bracket: (
1219+
column_definition:
1220+
naked_identifier: AMOUNT
1221+
data_type:
1222+
data_type_identifier: NUMBER
1223+
end_bracket: )
1224+
- statement_terminator: ;

‎test/fixtures/dialects/snowflake/for_in_loop.sql‎

Lines changed: 18 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,18 @@
1+
-- Simple LET
2+
LET CUR_TABLE CURSOR FOR SELECT * FROM THE_TABLE;
3+
-- Simple LOOP
4+
BEGIN
5+
SELECT 1;
6+
SELECT 2;
7+
END;
8+
-- Simple FOR
9+
FOR THE_ROW IN CUR_TABLE DO
10+
LET NEW_VAR := THE_ROW.COL1 || "_SUFFIX";
11+
END FOR;
12+
-- FOR and LET in LOOP
13+
BEGIN
14+
LET CUR_TABLE CURSOR FOR SELECT * FROM THE_TABLE;
15+
FOR THE_ROW IN CUR_TABLE DO
16+
LET NEW_VAR := THE_ROW.COL1 || "_SUFFIX";
17+
END FOR;
18+
END;

0 commit comments

Comments
 (0)