Skip to content

Commit 1f651ef

Browse files
kzosabekzosabe
authored
BigQuery: Support CREATE SNAPSHOT TABLE statement (#5779)
1 parent bd07b60 commit 1f651ef

5 files changed

Lines changed: 196 additions & 62 deletions

File tree

‎src/sqlfluff/dialects/dialect_bigquery.py‎

Lines changed: 39 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -387,6 +387,22 @@ class ArrayTypeSegment(ansi.ArrayTypeSegment):
387387
)
388388

389389

390+
class ForSystemTimeAsOfSegment(BaseSegment):
391+
"""A `FOR SYSTEM_TIME AS OF` syntax.
392+
393+
https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#for_system_time_as_of
394+
"""
395+
396+
type = "for_system_time_as_of_segment"
397+
match_grammar = Sequence(
398+
"FOR",
399+
OneOf("SYSTEM_TIME", Sequence("SYSTEM", "TIME")),
400+
"AS",
401+
"OF",
402+
Ref("ExpressionSegment"),
403+
)
404+
405+
390406
class QualifyClauseSegment(BaseSegment):
391407
"""A `QUALIFY` clause like in `SELECT`."""
392408

@@ -483,6 +499,7 @@ class StatementSegment(ansi.StatementSegment):
483499
Ref("SetStatementSegment"),
484500
Ref("ExportStatementSegment"),
485501
Ref("CreateExternalTableStatementSegment"),
502+
Ref("CreateSnapshotTableStatementSegment"),
486503
Ref("AssertStatementSegment"),
487504
Ref("CallStatementSegment"),
488505
Ref("ReturnStatementSegment"),
@@ -785,14 +802,7 @@ class IntervalExpressionSegment(ansi.IntervalExpressionSegment):
785802
]
786803
),
787804
PostTableExpressionGrammar=Sequence(
788-
Sequence(
789-
"FOR",
790-
OneOf("SYSTEM_TIME", Sequence("SYSTEM", "TIME")),
791-
"AS",
792-
"OF",
793-
Ref("ExpressionSegment"),
794-
optional=True,
795-
),
805+
Ref("ForSystemTimeAsOfSegment", optional=True),
796806
Sequence(
797807
"WITH",
798808
"OFFSET",
@@ -1756,6 +1766,27 @@ class CreateExternalTableStatementSegment(BaseSegment):
17561766
)
17571767

17581768

1769+
class CreateSnapshotTableStatementSegment(BaseSegment):
1770+
"""A `CREATE SNAPSHOT TABLE` statement.
1771+
1772+
https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language#create_snapshot_table_statement
1773+
"""
1774+
1775+
type = "create_snapshot_table_statement"
1776+
1777+
match_grammar = Sequence(
1778+
"CREATE",
1779+
"SNAPSHOT",
1780+
"TABLE",
1781+
Ref("IfNotExistsGrammar", optional=True),
1782+
Ref("TableReferenceSegment"),
1783+
"CLONE",
1784+
Ref("TableReferenceSegment"),
1785+
Ref("ForSystemTimeAsOfSegment", optional=True),
1786+
Ref("OptionsSegment", optional=True),
1787+
)
1788+
1789+
17591790
class CreateViewStatementSegment(ansi.CreateViewStatementSegment):
17601791
"""A `CREATE VIEW` statement.
17611792

‎src/sqlfluff/dialects/dialect_bigquery_keywords.py‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -13,6 +13,7 @@
1313
BY
1414
CASE
1515
CAST
16+
CLONE
1617
COLLATE
1718
CONTAINS
1819
CREATE
@@ -256,6 +257,7 @@
256257
SEQUENCE
257258
SESSION_USER
258259
SHARE
260+
SNAPSHOT
259261
SOURCE
260262
STAGE
261263
START

‎test/fixtures/dialects/bigquery/create_snapshot_table.sql‎

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,12 @@
1+
CREATE SNAPSHOT TABLE `example-project.example_dataset.example_table_snapshot_20240101`
2+
CLONE `example-project.example_dataset.example_table`;
3+
4+
CREATE SNAPSHOT TABLE IF NOT EXISTS `example-project.example_dataset.example_table_snapshot_20240101`
5+
CLONE `example-project.example_dataset.example_table`
6+
FOR SYSTEM_TIME AS OF TIMESTAMP("2024-01-01 12:00:00")
7+
OPTIONS (
8+
expiration_timestamp=TIMESTAMP("2024-02-01 12:00:00"),
9+
friendly_name="my_table_snapshot",
10+
description="example description",
11+
labels=[("example_key", "example_value")]
12+
);

‎test/fixtures/dialects/bigquery/create_snapshot_table.yml‎

Lines changed: 86 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,86 @@
1+
# YML test files are auto-generated from SQL files and should not be edited by
2+
# hand. To help enforce this, the "hash" field in the file must match a hash
3+
# computed by SQLFluff when running the tests. Please run
4+
# `python test/generate_parse_fixture_yml.py` to generate them after adding or
5+
# altering SQL files.
6+
_hash: be520ab6d2ddd160941f3e0d32350789d379962421dc595d087c5044efd59f2e
7+
file:
8+
- statement:
9+
create_snapshot_table_statement:
10+
- keyword: CREATE
11+
- keyword: SNAPSHOT
12+
- keyword: TABLE
13+
- table_reference:
14+
quoted_identifier: '`example-project.example_dataset.example_table_snapshot_20240101`'
15+
- keyword: CLONE
16+
- table_reference:
17+
quoted_identifier: '`example-project.example_dataset.example_table`'
18+
- statement_terminator: ;
19+
- statement:
20+
create_snapshot_table_statement:
21+
- keyword: CREATE
22+
- keyword: SNAPSHOT
23+
- keyword: TABLE
24+
- keyword: IF
25+
- keyword: NOT
26+
- keyword: EXISTS
27+
- table_reference:
28+
quoted_identifier: '`example-project.example_dataset.example_table_snapshot_20240101`'
29+
- keyword: CLONE
30+
- table_reference:
31+
quoted_identifier: '`example-project.example_dataset.example_table`'
32+
- for_system_time_as_of_segment:
33+
- keyword: FOR
34+
- keyword: SYSTEM_TIME
35+
- keyword: AS
36+
- keyword: OF
37+
- expression:
38+
function:
39+
function_name:
40+
function_name_identifier: TIMESTAMP
41+
bracketed:
42+
start_bracket: (
43+
expression:
44+
quoted_literal: '"2024-01-01 12:00:00"'
45+
end_bracket: )
46+
- options_segment:
47+
keyword: OPTIONS
48+
bracketed:
49+
- start_bracket: (
50+
- parameter: expiration_timestamp
51+
- comparison_operator:
52+
raw_comparison_operator: '='
53+
- function:
54+
function_name:
55+
function_name_identifier: TIMESTAMP
56+
bracketed:
57+
start_bracket: (
58+
expression:
59+
quoted_literal: '"2024-02-01 12:00:00"'
60+
end_bracket: )
61+
- comma: ','
62+
- parameter: friendly_name
63+
- comparison_operator:
64+
raw_comparison_operator: '='
65+
- quoted_literal: '"my_table_snapshot"'
66+
- comma: ','
67+
- parameter: description
68+
- comparison_operator:
69+
raw_comparison_operator: '='
70+
- quoted_literal: '"example description"'
71+
- comma: ','
72+
- parameter: labels
73+
- comparison_operator:
74+
raw_comparison_operator: '='
75+
- array_literal:
76+
start_square_bracket: '['
77+
expression:
78+
bracketed:
79+
- start_bracket: (
80+
- quoted_literal: '"example_key"'
81+
- comma: ','
82+
- quoted_literal: '"example_value"'
83+
- end_bracket: )
84+
end_square_bracket: ']'
85+
- end_bracket: )
86+
- statement_terminator: ;

‎test/fixtures/dialects/bigquery/select_for_system_time.yml‎

Lines changed: 57 additions & 54 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: a0bc988c91bbf2eec01f4c1ca18bfae91db7fdadf49b9613c98c8eb2ac7177f8
6+
_hash: 0ad09aa1c91c7187352441e855ffa3193c54cb231f4ef24d7f41b767fce821ea
77
file:
88
- statement:
99
select_statement:
@@ -16,28 +16,29 @@ file:
1616
keyword: FROM
1717
from_expression:
1818
from_expression_element:
19-
- table_expression:
19+
table_expression:
2020
table_reference:
2121
naked_identifier: lists_emails
22-
- alias_expression:
22+
alias_expression:
2323
keyword: AS
2424
naked_identifier: list_emails
25-
- keyword: FOR
26-
- keyword: SYSTEM_TIME
27-
- keyword: AS
28-
- keyword: OF
29-
- expression:
30-
function:
31-
function_name:
32-
function_name_identifier: CAST
33-
bracketed:
34-
start_bracket: (
35-
expression:
36-
quoted_literal: "'2019-12-02T20:52:34+00:00'"
37-
keyword: AS
38-
data_type:
39-
data_type_identifier: TIMESTAMP
40-
end_bracket: )
25+
for_system_time_as_of_segment:
26+
- keyword: FOR
27+
- keyword: SYSTEM_TIME
28+
- keyword: AS
29+
- keyword: OF
30+
- expression:
31+
function:
32+
function_name:
33+
function_name_identifier: CAST
34+
bracketed:
35+
start_bracket: (
36+
expression:
37+
quoted_literal: "'2019-12-02T20:52:34+00:00'"
38+
keyword: AS
39+
data_type:
40+
data_type_identifier: TIMESTAMP
41+
end_bracket: )
4142
- statement_terminator: ;
4243
- statement:
4344
select_statement:
@@ -50,25 +51,26 @@ file:
5051
keyword: FROM
5152
from_expression:
5253
from_expression_element:
53-
- table_expression:
54+
table_expression:
5455
table_reference:
5556
quoted_identifier: '`project.dataset.table1`'
56-
- keyword: FOR
57-
- keyword: SYSTEM_TIME
58-
- keyword: AS
59-
- keyword: OF
60-
- expression:
61-
function:
62-
function_name:
63-
function_name_identifier: CAST
64-
bracketed:
65-
start_bracket: (
66-
expression:
67-
quoted_literal: "'2020-05-11T14:02:52+00:00'"
68-
keyword: AS
69-
data_type:
70-
data_type_identifier: TIMESTAMP
71-
end_bracket: )
57+
for_system_time_as_of_segment:
58+
- keyword: FOR
59+
- keyword: SYSTEM_TIME
60+
- keyword: AS
61+
- keyword: OF
62+
- expression:
63+
function:
64+
function_name:
65+
function_name_identifier: CAST
66+
bracketed:
67+
start_bracket: (
68+
expression:
69+
quoted_literal: "'2020-05-11T14:02:52+00:00'"
70+
keyword: AS
71+
data_type:
72+
data_type_identifier: TIMESTAMP
73+
end_bracket: )
7274
- statement_terminator: ;
7375
- statement:
7476
select_statement:
@@ -81,23 +83,24 @@ file:
8183
keyword: FROM
8284
from_expression:
8385
from_expression_element:
84-
- table_expression:
86+
table_expression:
8587
table_reference:
8688
quoted_identifier: '`project.dataset.table1`'
87-
- keyword: FOR
88-
- keyword: SYSTEM
89-
- keyword: TIME
90-
- keyword: AS
91-
- keyword: OF
92-
- expression:
93-
function:
94-
function_name:
95-
function_name_identifier: CAST
96-
bracketed:
97-
start_bracket: (
98-
expression:
99-
quoted_literal: "'2020-05-11T14:02:52+00:00'"
100-
keyword: AS
101-
data_type:
102-
data_type_identifier: TIMESTAMP
103-
end_bracket: )
89+
for_system_time_as_of_segment:
90+
- keyword: FOR
91+
- keyword: SYSTEM
92+
- keyword: TIME
93+
- keyword: AS
94+
- keyword: OF
95+
- expression:
96+
function:
97+
function_name:
98+
function_name_identifier: CAST
99+
bracketed:
100+
start_bracket: (
101+
expression:
102+
quoted_literal: "'2020-05-11T14:02:52+00:00'"
103+
keyword: AS
104+
data_type:
105+
data_type_identifier: TIMESTAMP
106+
end_bracket: )

0 commit comments

Comments
 (0)