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

Open
#923 16 comments 0 reactions 2 assignees Assigned to @piyushsarraf View on GitHub
priority: p3
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

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.