GoogleCloudPlatform / GoogleCloudPlatform/professional-services-data-validator
`generate-table-partitions` generates invalid SQL in filter clause when a timestamp field is part of the primary-key
- Dominant language
- Python
- Stars
- 524
- Forks
- 171
- Avg merge
- 5d 15h
- Merged PRs (30d)
- 4
Description
The following command:
```
data-validation generate-table-partitions -sc rs -tc bq -tbls source.table=target.table --primary-keys _hash_key,_hash_diff,_creation_timestamp,source_last_update_date,delivery_area_id --partition-num 164 --filter-status fail --bq-result-handler pso_data_validator.results -cdir ~/
```
generates 164 partitioned configs as expected but the none of those can be run using `data-validation configs run -c ~/config_X.yaml` throwing the follow:
```
07/31/2023 09:37:36 AM-ERROR: Error (psycopg2.errors.SyntaxError) syntax error at or near "10"
LINE 3: ...2f114e622b' AND (_creation_timestamp > 2023-05-16 10:48:06.8...
```
It seems the `filter: ` part of the YALM contains timestamp values without the required quotes (see `_creation_timestamp` below).
```
filters:
- source: (_hash_key > '04b0a057a5ceccd4fff06984b5ed8e0ba1e90591' OR _hash_key =
'04b0a057a5ceccd4fff06984b5ed8e0ba1e90591' AND (_hash_diff > 'cba27aebaf720f51987e0d3087213bd50fc3a70c'
OR _hash_diff = 'cba27aebaf720f51987e0d3087213bd50fc3a70c' AND (_creation_timestamp
> 2023-04-18 10:39:12.808967 OR _creation_timestamp = 2023-04-18 10:39:12.808967 ...
```
Contributor guide
Assessment
This issue has not been assessed yet.