GoogleCloudPlatform / GoogleCloudPlatform/professional-services-data-validator

Snowflake: Support TIMESTAMP_TZ data type

Open
#916 2 comments 0 reactions 0 assignees View on GitHub
priority: p2
Dominant language
Python
Stars
524
Forks
171
Avg merge
5d 15h
Merged PRs (30d)
4

Description

Snowflake TIMESTAMP with time zone causes the following error when running column/row validations:

```
sqlalchemy.exc.ProgrammingError: (snowflake.connector.errors.ProgrammingError) 001003 (42000): 01ade410-0504-af59-0067-8487000b0aae: SQL compilation error:
syntax error line 2 at position 541 unexpected 'AT'.
syntax error line 2 at position 544 unexpected 'TIME'.
[SQL: WITH t0 AS
(SELECT t2."ID" AS "ID", t2."COL_INT8" AS "COL_INT8", t2."COL_INT16" AS "COL_INT16", t2."COL_INT32" AS "COL_INT32", t2."COL_INT64" AS "COL_INT64", t2."COL_DEC_20" AS "COL_DEC_20", t2."COL_DEC_38" AS "COL_DEC_38", t2."COL_DEC_10_2" AS "COL_DEC_10_2", t2."COL_FLOAT32" AS "COL_FLOAT32", t2."COL_FLOAT64" AS "COL_FLOAT64", t2."COL_VARCHAR_30" AS "COL_VARCHAR_30", t2."COL_CHAR_2" AS "COL_CHAR_2", t2."COL_STRING" AS "COL_STRING", t2."COL_DATE" AS "COL_DATE", t2."COL_DATETIME" AS "COL_DATETIME", t2."COL_TSTZ" AS "COL_TSTZ", CAST(t2."COL_TSTZ" AT TIME ZONE %(COL_TSTZ_1)s AS TIMESTAMP_NTZ) AS "cast_timestamp__COL_TSTZ"
FROM pso_data_validator.public."DVT_CORE_TYPES" AS t2)
SELECT count(*) AS "count", sum(t1."epoch_seconds__cast_timestamp__COL_TSTZ") AS "sum__epoch_seconds__cast_timestamp__COL_TSTZ"
FROM (SELECT t0."ID" AS "ID", t0."COL_INT8" AS "COL_INT8", t0."COL_INT16" AS "COL_INT16", t0."COL_INT32" AS "COL_INT32", t0."COL_INT64" AS "COL_INT64", t0."COL_DEC_20" AS "COL_DEC_20", t0."COL_DEC_38" AS "COL_DEC_38", t0."COL_DEC_10_2" AS "COL_DEC_10_2", t0."COL_FLOAT32" AS "COL_FLOAT32", t0."COL_FLOAT64" AS "COL_FLOAT64", t0."COL_VARCHAR_30" AS "COL_VARCHAR_30", t0."COL_CHAR_2" AS "COL_CHAR_2", t0."COL_STRING" AS "COL_STRING", t0."COL_DATE" AS "COL_DATE", t0."COL_DATETIME" AS "COL_DATETIME", t0."COL_TSTZ" AS "COL_TSTZ", t0."cast_timestamp__COL_TSTZ" AS "cast_timestamp__COL_TSTZ", date_part(epoch_seconds, t0."cast_timestamp__COL_TSTZ") AS "epoch_seconds__cast_timestamp__COL_TSTZ"
FROM t0) AS t1]
[parameters: {'COL_TSTZ_1': 'UTC'}]
(Background on this error at: https://sqlalche.me/e/14/f405)
```

The issue is this line `CAST(t2."COL_TSTZ" AT TIME ZONE %(COL_TSTZ_1)s AS TIMESTAMP_NTZ)` which preserves the "AT TIME ZONE" in the SQLAlchemy label and causes a compliation error.

When fixing this we also need to update the integration tests, search for "issue-916" in test_snowflake.py

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.