summaryrefslogtreecommitdiffstats
path: root/tests/dialects/test_snowflake.py
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2023-10-04 12:14:40 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2023-10-04 12:14:40 +0000
commitd7f0758e21b5111b5327f3839c5c9f49a04d272b (patch)
treea425f4ebcc159d6bd9443fe4e0e2f9eb20151027 /tests/dialects/test_snowflake.py
parentAdding upstream version 18.7.0. (diff)
downloadsqlglot-d7f0758e21b5111b5327f3839c5c9f49a04d272b.tar.xz
sqlglot-d7f0758e21b5111b5327f3839c5c9f49a04d272b.zip
Adding upstream version 18.11.2.upstream/18.11.2
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'tests/dialects/test_snowflake.py')
-rw-r--r--tests/dialects/test_snowflake.py111
1 files changed, 80 insertions, 31 deletions
diff --git a/tests/dialects/test_snowflake.py b/tests/dialects/test_snowflake.py
index a217394..7c36bea 100644
--- a/tests/dialects/test_snowflake.py
+++ b/tests/dialects/test_snowflake.py
@@ -1,6 +1,7 @@
from unittest import mock
from sqlglot import UnsupportedError, exp, parse_one
+from sqlglot.optimizer.normalize_identifiers import normalize_identifiers
from tests.dialects.test_dialect import Validator
@@ -8,34 +9,6 @@ class TestSnowflake(Validator):
dialect = "snowflake"
def test_snowflake(self):
- self.validate_identity(
- 'DESCRIBE TABLE "SNOWFLAKE_SAMPLE_DATA"."TPCDS_SF100TCL"."WEB_SITE" type=stage'
- )
-
- self.validate_all(
- "SELECT * FROM x START WITH a = b CONNECT BY c = PRIOR d",
- read={
- "oracle": "SELECT * FROM x START WITH a = b CONNECT BY c = PRIOR d",
- },
- write={
- "oracle": "SELECT * FROM x START WITH a = b CONNECT BY c = PRIOR d",
- "snowflake": "SELECT * FROM x START WITH a = b CONNECT BY c = PRIOR d",
- },
- )
- self.validate_all(
- "SELECT INSERT(a, 0, 0, 'b')",
- read={
- "mysql": "SELECT INSERT(a, 0, 0, 'b')",
- "snowflake": "SELECT INSERT(a, 0, 0, 'b')",
- "tsql": "SELECT STUFF(a, 0, 0, 'b')",
- },
- write={
- "mysql": "SELECT INSERT(a, 0, 0, 'b')",
- "snowflake": "SELECT INSERT(a, 0, 0, 'b')",
- "tsql": "SELECT STUFF(a, 0, 0, 'b')",
- },
- )
-
self.validate_identity("LISTAGG(data['some_field'], ',')")
self.validate_identity("WEEKOFYEAR(tstamp)")
self.validate_identity("SELECT SUM(amount) FROM mytable GROUP BY ALL")
@@ -54,7 +27,6 @@ class TestSnowflake(Validator):
self.validate_identity("$x") # parameter
self.validate_identity("a$b") # valid snowflake identifier
self.validate_identity("SELECT REGEXP_LIKE(a, b, c)")
- self.validate_identity("PUT file:///dir/tmp.csv @%table")
self.validate_identity("CREATE TABLE foo (bar FLOAT AUTOINCREMENT START 0 INCREMENT 1)")
self.validate_identity("ALTER TABLE IF EXISTS foo SET TAG a = 'a', b = 'b', c = 'c'")
self.validate_identity("ALTER TABLE foo UNSET TAG a, b, c")
@@ -65,12 +37,16 @@ class TestSnowflake(Validator):
self.validate_identity("SELECT CONVERT_TIMEZONE('UTC', 'America/Los_Angeles', col)")
self.validate_identity("REGEXP_REPLACE('target', 'pattern', '\n')")
self.validate_identity(
- 'COPY INTO NEW_TABLE ("foo", "bar") FROM (SELECT $1, $2, $3, $4 FROM @%old_table)'
+ 'DESCRIBE TABLE "SNOWFLAKE_SAMPLE_DATA"."TPCDS_SF100TCL"."WEB_SITE" type=stage'
)
self.validate_identity(
"SELECT state, city, SUM(retail_price * quantity) AS gross_revenue FROM sales GROUP BY ALL"
)
self.validate_identity(
+ "SELECT * FROM foo window",
+ "SELECT * FROM foo AS window",
+ )
+ self.validate_identity(
r"SELECT RLIKE(a, $$regular expression with \ characters: \d{2}-\d{3}-\d{4}$$, 'i') FROM log_source",
r"SELECT REGEXP_LIKE(a, 'regular expression with \\ characters: \\d{2}-\\d{3}-\\d{4}', 'i') FROM log_source",
)
@@ -88,6 +64,36 @@ class TestSnowflake(Validator):
self.validate_all("CAST(x AS CHARACTER VARYING)", write={"snowflake": "CAST(x AS VARCHAR)"})
self.validate_all("CAST(x AS NCHAR VARYING)", write={"snowflake": "CAST(x AS VARCHAR)"})
self.validate_all(
+ "SELECT COLLATE('B', 'und:ci')",
+ write={
+ "bigquery": "SELECT COLLATE('B', 'und:ci')",
+ "snowflake": "SELECT COLLATE('B', 'und:ci')",
+ },
+ )
+ self.validate_all(
+ "SELECT * FROM x START WITH a = b CONNECT BY c = PRIOR d",
+ read={
+ "oracle": "SELECT * FROM x START WITH a = b CONNECT BY c = PRIOR d",
+ },
+ write={
+ "oracle": "SELECT * FROM x START WITH a = b CONNECT BY c = PRIOR d",
+ "snowflake": "SELECT * FROM x START WITH a = b CONNECT BY c = PRIOR d",
+ },
+ )
+ self.validate_all(
+ "SELECT INSERT(a, 0, 0, 'b')",
+ read={
+ "mysql": "SELECT INSERT(a, 0, 0, 'b')",
+ "snowflake": "SELECT INSERT(a, 0, 0, 'b')",
+ "tsql": "SELECT STUFF(a, 0, 0, 'b')",
+ },
+ write={
+ "mysql": "SELECT INSERT(a, 0, 0, 'b')",
+ "snowflake": "SELECT INSERT(a, 0, 0, 'b')",
+ "tsql": "SELECT STUFF(a, 0, 0, 'b')",
+ },
+ )
+ self.validate_all(
"ARRAY_GENERATE_RANGE(0, 3)",
write={
"bigquery": "GENERATE_ARRAY(0, 3 - 1)",
@@ -513,6 +519,40 @@ class TestSnowflake(Validator):
},
)
+ def test_staged_files(self):
+ # Ensure we don't treat staged file paths as identifiers (i.e. they're not normalized)
+ staged_file = parse_one("SELECT * FROM @foo", read="snowflake")
+ self.assertEqual(
+ normalize_identifiers(staged_file, dialect="snowflake").sql(dialect="snowflake"),
+ staged_file.sql(dialect="snowflake"),
+ )
+
+ self.validate_identity("SELECT * FROM @~")
+ self.validate_identity("SELECT * FROM @~/some/path/to/file.csv")
+ self.validate_identity("SELECT * FROM @mystage")
+ self.validate_identity("SELECT * FROM '@mystage'")
+ self.validate_identity("SELECT * FROM @namespace.mystage/path/to/file.json.gz")
+ self.validate_identity("SELECT * FROM @namespace.%table_name/path/to/file.json.gz")
+ self.validate_identity("SELECT * FROM '@external/location' (FILE_FORMAT => 'path.to.csv')")
+ self.validate_identity("PUT file:///dir/tmp.csv @%table")
+ self.validate_identity(
+ 'COPY INTO NEW_TABLE ("foo", "bar") FROM (SELECT $1, $2, $3, $4 FROM @%old_table)'
+ )
+ self.validate_identity(
+ "SELECT * FROM @foo/bar (FILE_FORMAT => ds_sandbox.test.my_csv_format, PATTERN => 'test') AS bla"
+ )
+ self.validate_identity(
+ "SELECT t.$1, t.$2 FROM @mystage1 (FILE_FORMAT => 'myformat', PATTERN => '.*data.*[.]csv.gz') AS t"
+ )
+ self.validate_identity(
+ "SELECT parse_json($1):a.b FROM @mystage2/data1.json.gz",
+ "SELECT PARSE_JSON($1)['a'].b FROM @mystage2/data1.json.gz",
+ )
+ self.validate_identity(
+ "SELECT * FROM @mystage t (c1)",
+ "SELECT * FROM @mystage AS t(c1)",
+ )
+
def test_sample(self):
self.validate_identity("SELECT * FROM testtable TABLESAMPLE BERNOULLI (20.3)")
self.validate_identity("SELECT * FROM testtable TABLESAMPLE (100)")
@@ -660,7 +700,6 @@ class TestSnowflake(Validator):
self.validate_identity("CREATE MATERIALIZED VIEW a COMMENT='...' AS SELECT 1 FROM x")
self.validate_identity("CREATE DATABASE mytestdb_clone CLONE mytestdb")
self.validate_identity("CREATE SCHEMA mytestschema_clone CLONE testschema")
- self.validate_identity("CREATE TABLE orders_clone CLONE orders")
self.validate_identity("CREATE TABLE IDENTIFIER('foo') (COLUMN1 VARCHAR, COLUMN2 VARCHAR)")
self.validate_identity("CREATE TABLE IDENTIFIER($foo) (col1 VARCHAR, col2 VARCHAR)")
self.validate_identity(
@@ -680,6 +719,16 @@ class TestSnowflake(Validator):
)
self.validate_all(
+ "CREATE TABLE orders_clone CLONE orders",
+ read={
+ "bigquery": "CREATE TABLE orders_clone CLONE orders",
+ },
+ write={
+ "bigquery": "CREATE TABLE orders_clone CLONE orders",
+ "snowflake": "CREATE TABLE orders_clone CLONE orders",
+ },
+ )
+ self.validate_all(
"CREATE OR REPLACE TRANSIENT TABLE a (id INT)",
read={
"postgres": "CREATE OR REPLACE TRANSIENT TABLE a (id INT)",