diff options
Diffstat (limited to 'tests/dialects/test_spark.py')
| -rw-r--r-- | tests/dialects/test_spark.py | 76 |
1 files changed, 76 insertions, 0 deletions
diff --git a/tests/dialects/test_spark.py b/tests/dialects/test_spark.py index 32be23e..2afa868 100644 --- a/tests/dialects/test_spark.py +++ b/tests/dialects/test_spark.py @@ -224,6 +224,9 @@ TBLPROPERTIES ( ) def test_spark(self): + self.validate_identity("SELECT TRANSFORM(ARRAY(1, 2, 3), x -> x + 1)") + self.validate_identity("SELECT TRANSFORM(ARRAY(1, 2, 3), (x, i) -> x + i)") + self.validate_identity("REFRESH table a.b.c") self.validate_identity("INTERVAL -86 days") self.validate_identity("SELECT UNIX_TIMESTAMP()") self.validate_identity("TRIM(' SparkSQL ')") @@ -233,6 +236,61 @@ TBLPROPERTIES ( self.validate_identity("SPLIT(str, pattern, lim)") self.validate_all( + "SELECT DATEDIFF(month, CAST('1996-10-30' AS TIMESTAMP), CAST('1997-02-28 10:30:00' AS TIMESTAMP))", + read={ + "duckdb": "SELECT DATEDIFF('month', CAST('1996-10-30' AS TIMESTAMP), CAST('1997-02-28 10:30:00' AS TIMESTAMP))", + }, + write={ + "spark": "SELECT DATEDIFF(month, TO_DATE(CAST('1996-10-30' AS TIMESTAMP)), TO_DATE(CAST('1997-02-28 10:30:00' AS TIMESTAMP)))", + "spark2": "SELECT MONTHS_BETWEEN(TO_DATE(CAST('1997-02-28 10:30:00' AS TIMESTAMP)), TO_DATE(CAST('1996-10-30' AS TIMESTAMP)))", + }, + ) + self.validate_all( + "SELECT MONTHS_BETWEEN('1997-02-28 10:30:00', '1996-10-30')", + write={ + "duckdb": "SELECT DATEDIFF('month', CAST('1996-10-30' AS TIMESTAMP), CAST('1997-02-28 10:30:00' AS TIMESTAMP))", + "hive": "SELECT MONTHS_BETWEEN('1997-02-28 10:30:00', '1996-10-30')", + "spark": "SELECT MONTHS_BETWEEN('1997-02-28 10:30:00', '1996-10-30')", + }, + ) + self.validate_all( + "SELECT MONTHS_BETWEEN('1997-02-28 10:30:00', '1996-10-30', FALSE)", + write={ + "duckdb": "SELECT DATEDIFF('month', CAST('1996-10-30' AS TIMESTAMP), CAST('1997-02-28 10:30:00' AS TIMESTAMP))", + "hive": "SELECT MONTHS_BETWEEN('1997-02-28 10:30:00', '1996-10-30')", + "spark": "SELECT MONTHS_BETWEEN('1997-02-28 10:30:00', '1996-10-30', FALSE)", + }, + ) + self.validate_all( + "SELECT TO_TIMESTAMP('2016-12-31 00:12:00')", + write={ + "": "SELECT CAST('2016-12-31 00:12:00' AS TIMESTAMP)", + "duckdb": "SELECT CAST('2016-12-31 00:12:00' AS TIMESTAMP)", + "spark": "SELECT CAST('2016-12-31 00:12:00' AS TIMESTAMP)", + }, + ) + self.validate_all( + "SELECT TO_TIMESTAMP('2016-12-31', 'yyyy-MM-dd')", + read={ + "duckdb": "SELECT STRPTIME('2016-12-31', '%Y-%m-%d')", + }, + write={ + "": "SELECT STR_TO_TIME('2016-12-31', '%Y-%m-%d')", + "duckdb": "SELECT STRPTIME('2016-12-31', '%Y-%m-%d')", + "spark": "SELECT TO_TIMESTAMP('2016-12-31', 'yyyy-MM-dd')", + }, + ) + self.validate_all( + "SELECT RLIKE('John Doe', 'John.*')", + write={ + "bigquery": "SELECT REGEXP_CONTAINS('John Doe', 'John.*')", + "hive": "SELECT 'John Doe' RLIKE 'John.*'", + "postgres": "SELECT 'John Doe' ~ 'John.*'", + "snowflake": "SELECT REGEXP_LIKE('John Doe', 'John.*')", + "spark": "SELECT 'John Doe' RLIKE 'John.*'", + }, + ) + self.validate_all( "UNHEX(MD5(x))", write={ "bigquery": "FROM_HEX(TO_HEX(MD5(x)))", @@ -446,3 +504,21 @@ TBLPROPERTIES ( "CURRENT_USER()", write={"spark": "CURRENT_USER()"}, ) + + def test_transform_query(self): + self.validate_identity("SELECT TRANSFORM(x) USING 'x' AS (x INT) FROM t") + self.validate_identity( + "SELECT TRANSFORM(zip_code, name, age) USING 'cat' AS (a, b, c) FROM person WHERE zip_code > 94511" + ) + self.validate_identity( + "SELECT TRANSFORM(zip_code, name, age) USING 'cat' AS (a STRING, b STRING, c STRING) FROM person WHERE zip_code > 94511" + ) + self.validate_identity( + "SELECT TRANSFORM(name, age) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' NULL DEFINED AS 'NULL' USING 'cat' AS (name_age STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '@' LINES TERMINATED BY '\n' NULL DEFINED AS 'NULL' FROM person" + ) + self.validate_identity( + "SELECT TRANSFORM(zip_code, name, age) ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' WITH SERDEPROPERTIES ('field.delim'='\t') USING 'cat' AS (a STRING, b STRING, c STRING) ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' WITH SERDEPROPERTIES ('field.delim'='\t') FROM person WHERE zip_code > 94511" + ) + self.validate_identity( + "SELECT TRANSFORM(zip_code, name, age) USING 'cat' FROM person WHERE zip_code > 94500" + ) |