GoogleCloudPlatform / GoogleCloudPlatform/professional-services-data-validator

Add support for Oracle/PostgreSQL JSON validations

Open
#1,338 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

**Test case**

Oracle:
```
CREATE TABLE dvt_test.tab_json
(
id VARCHAR2(10) NOT NULL PRIMARY KEY
, col_json1 CLOB
, col_json2 CLOB
);
ALTER TABLE dvt_test.tab_json ADD CONSTRAINT tab_json_chk1 CHECK (col_json1 IS JSON) ENABLE;
ALTER TABLE dvt_test.tab_json ADD CONSTRAINT tab_json_chk2 CHECK (col_json2 IS JSON) ENABLE;
INSERT INTO dvt_test.tab_json VALUES (1,'{"c": 1, "b": 2, "a": 3}','{"c": 1, "b": 2, "a": 3}');
COMMIT;
```

PostgreSQL:
```
CREATE TABLE dvt_test.tab_json
(
id varchar(10) NOT NULL PRIMARY KEY
, col_json1 json
, col_json2 jsonb
);
INSERT INTO dvt_test.tab_json VALUES (1,'{"c": 1, "b": 2, "a": 3}','{"c": 1, "b": 2, "a": 3}');
```

Example row commands:
```
data-validation validate row -sc ora -tc pg -tbls=dvt_test.tab_json --hash="*"
...
sqlalchemy.exc.DatabaseError: (cx_Oracle.DatabaseError) ORA-00932: inconsistent datatypes: expected CLOB got CHAR

data-validation validate row -sc ora -tc pg -tbls=dvt_test.tab_json -comp-fields="*"
...
TypeError: Arguments differences_target_value:map and differences_source_value:string are not comparable
```

Example column level command which only uses length, really min/max should be able to use the real value. But perhaps legnth is fine for this.
```
data-validation validate column -sc ora -tc pg -tbls=dvt_test.tab_json --count="*" --min="*" --format=csv
...
╒═══════════════════════════════════════╤═══════════════════╤═════════════════════╤════════════════════════════════╤════════════════════╤════════════════════╤══════════════════╤═════════════════════╤══════════════════════════════════════╕
│ validation_name │ validation_type │ source_table_name │ source_column_name │ source_agg_value │ target_agg_value │ pct_difference │ validation_status │ run_id │
╞═══════════════════════════════════════╪═══════════════════╪═════════════════════╪════════════════════════════════╪════════════════════╪════════════════════╪══════════════════╪═════════════════════╪══════════════════════════════════════╡
│ count__length__cast_string__col_json2 │ Column │ dvt_test.tab_json │ length__cast_string__col_json2 │ 1 │ 1 │ 0 │ success │ a5e0c088-d0ab-459c-940c-42d4239a31bc │
├───────────────────────────────────────┼───────────────────┼─────────────────────┼────────────────────────────────┼────────────────────┼────────────────────┼──────────────────┼─────────────────────┼──────────────────────────────────────┤
│ count__length__cast_string__col_json1 │ Column │ dvt_test.tab_json │ length__cast_string__col_json1 │ 1 │ 1 │ 0 │ success │ a5e0c088-d0ab-459c-940c-42d4239a31bc │
├───────────────────────────────────────┼───────────────────┼─────────────────────┼────────────────────────────────┼────────────────────┼────────────────────┼──────────────────┼─────────────────────┼──────────────────────────────────────┤
│ count │ Column │ dvt_test.tab_json │ │ 1 │ 1 │ 0 │ success │ a5e0c088-d0ab-459c-940c-42d4239a31bc │
├───────────────────────────────────────┼───────────────────┼─────────────────────┼────────────────────────────────┼────────────────────┼────────────────────┼──────────────────┼─────────────────────┼──────────────────────────────────────┤
│ count__length__id │ Column │ dvt_test.tab_json │ length__id │ 1 │ 1 │ 0 │ success │ a5e0c088-d0ab-459c-940c-42d4239a31bc │
╘═══════════════════════════════════════╧═══════════════════╧═════════════════════╧════════════════════════════════╧════════════════════╧════════════════════╧══════════════════╧═════════════════════╧══════════════════════════════════════╛
```

Oracle uses CLOB/NCLOB columns for JSON data, we should be able to compare these with PostgreSQL JSON columns. At a minimum, for column validation, we should be able to compare by string length like we do for some other string columns.

We need to check all validation types. Search for "issue-1338" in the repo.

We might need to revert some of the changes from https://github.com/GoogleCloudPlatform/professional-services-data-validator/issues/1335.

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.