GoogleCloudPlatform / GoogleCloudPlatform/professional-services-data-validator
Data Validator Redshift Adapter - Invalid SQL in Schema Inference
- Dominant language
- Python
- Stars
- 524
- Forks
- 171
- Avg merge
- 5d 15h
- Merged PRs (30d)
- 4
Description
**Issue:** When validating Redshift tables using data-validation validate row, the tool fails with a SQL syntax error during schema inference.
**Error Message:**
psycopg2.errors.SyntaxError: syntax error at or near "stg_shifts__contexts"
in context "CREATE VIEW b6bb0676bf04468e858e97bb4a3a5859 AS stg_shifts__contexts"
LINE 1: CREATE VIEW b6bb0676bf04468e858e97bb4a3a5859 AS stg_shift...
**Root Cause:**
In file third_party/ibis/ibis_redshift/__init__.py line 96, the _metadata method generates invalid SQL:
con.exec_driver_sql(f"CREATE VIEW {name} AS {query}")
The query variable contains just the table name (e.g., stg_shifts__contexts) instead of a valid SELECT statement.
**Expected SQL:**
CREATE VIEW b6bb0676bf04468e858e97bb4a3a5859 AS SELECT * FROM dbt_staging.stg_shifts__contexts
**Actual SQL Generated:**
CREATE VIEW b6bb0676bf04468e858e97bb4a3a5859 AS stg_shifts__contexts
Steps to Reproduce:
data-validation validate row \
-sc redshift_connection \
-tc bigquery_connection \
-tbls schema_name.table_name \
--hash '*' \
--primary-keys id_column
Environment:
- google-pso-data-validator: 8.2.0
- SQLAlchemy: 1.4.49 (downgraded from 2.0.41 for Redshift compatibility) - https://github.com/GoogleCloudPlatform/professional-services-data-validator/issues/1563
- ibis-framework: 5.1.0
- Python: 3.11
Contributor guide
Assessment
This issue has not been assessed yet.