ERROR-2:sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedObject) unrecognized configuration parameter "standard_conforming_strings"

[arpitgup]

@nj1973
@helensilva14

Describe the bug

• data-validation CLI tool fails with FileNotFoundError for connection configuration files when executed via Terraform's local-exec provisioner. The data-validation validate command cannot find the connection files (REDSHIFT_CONN_CTD.connection.json ) that were supposedly created by the data-validation connections add commands earlier in the same script. The error path suggests the files are expected in a user home directory (/builder/home/.config/google-pso-data-validator/), but they were likely created in a different temporary or working directory.

version of DVT

• 8.0.0

Type of connections are you using for source and target

• Source: Redshift
• Target: BigQuery

Steps to reproduce the behavior

1. Set up a Python virtual environment.
2. Install google-pso-data-validator.
3. Add Redshift and BigQuery connections using data-validation connections add.
4. Immediately attempt to run data-validation validate commands.
5. The validate command fails with a FileNotFoundError.

Error Logs:

│   File "/workspace/data-cloud/Data Warehouse Migration-591/demo/terraform/DWMH_Validation_Automation/.venv/lib/python3.11/site-packages/data_validation/gcs_helper.py", line 82, in read_file
│     with open(file_path, "r") as f:
│          ^^^^^^^^^^^^^^^^^^^^
│ FileNotFoundError: [Errno 2] No such file or directory:
│ '/builder/home/.config/google-pso-data-validator/REDSHIFT_CONN_CTD.connection.json'
│ ERROR:root:Exception No module named 'ibm_db_dbi' while importing
│ db2_connect

│   File "/workspace/data-cloud/Data Warehouse Migration-591/demo/terraform/DWMH_Validation_Automation/.venv/lib/python3.11/site-packages/data_validation/gcs_helper.py", line 82, in read_file
│     with open(file_path, "r") as f:
│          ^^^^^^^^^^^^^^^^^^^^
│ FileNotFoundError: [Errno 2] No such file or directory:
│ '/builder/home/.config/google-pso-data-validator/REDSHIFT_CONN_CTD.connection.json'
│ ERROR:root:Exception No module named 'ibm_db_dbi' while importing
│ db2_connect

│   File "/workspace/data-cloud/Data Warehouse Migration-591/demo/terraform/DWMH_Validation_Automation/.venv/lib/python3.11/site-packages/data_validation/gcs_helper.py", line 82, in read_file
│     with open(file_path, "r") as f:
│          ^^^^^^^^^^^^^^^^^^^^
│ FileNotFoundError: [Errno 2] No such file or directory:
│ '/builder/home/.config/google-pso-data-validator/REDSHIFT_CONN_CTD.connection.json'

[arpitgup]

Hi @nj1973

Terraform Code for execution:

resource "null_resource" "Installing_Validation_Tool_Executing_Validation_Commands" {
  provisioner "local-exec" {
    interpreter = ["/bin/bash", "-c"]
    command = <<EOF
cd DWMH_Validation_Automation
apt-get -y install python3-venv
python3 -m venv .venv
source .venv/bin/activate
apt-get update  && apt-get install gcc -y && apt-get clean
python3 -m pip install google-pso-data-validator==7.7.0
data-validation connections add --connection-name ${var.BQ_Connection_Name} BigQuery --project-id ${var.project_id}
data-validation connections add --connection-name ${var.REDSHIFT_Connection_Name} Redshift --port 5439 --user ${var.database_user} --password '${var.database_password}' --database ${var.database_name} --host ${var.endpoint}
data-validation validate column --source-conn ${var.REDSHIFT_Connection_Name} --target-conn ${var.BQ_Connection_Name} --tables-list ${var.Prod_Schema}.customer=${var.dataset_id_prod}.customer,${var.dataset_id_prod}.part=${var.dataset_id_prod}.part,${var.dataset_id_prod}.nation=${var.dataset_id_prod}.nation,${var.dataset_id_prod}.lineitem=${var.dataset_id_prod}.lineitem,${var.dataset_id_prod}.supplier=${var.dataset_id_prod}.supplier,${var.dataset_id_prod}.partsupp=${var.dataset_id_prod}.partsupp,${var.dataset_id_prod}.region=${var.dataset_id_prod}.region,${var.dataset_id_prod}.orders=${var.dataset_id_prod}.orders --bq-result-handler ${var.project_id}.${google_bigquery_dataset.default.dataset_id}.results
data-validation -v validate schema --source-conn ${var.REDSHIFT_Connection_Name} --target-conn ${var.BQ_Connection_Name} --tables-list ${var.Prod_Schema}.customer=${var.dataset_id_prod}.customer,${var.dataset_id_prod}.part=${var.dataset_id_prod}.part,${var.dataset_id_prod}.nation=${var.dataset_id_prod}.nation,${var.dataset_id_prod}.lineitem=${var.dataset_id_prod}.lineitem,${var.dataset_id_prod}.supplier=${var.dataset_id_prod}.supplier,${var.dataset_id_prod}.partsupp=${var.dataset_id_prod}.partsupp,${var.dataset_id_prod}.region=${var.dataset_id_prod}.region,${var.dataset_id_prod}.orders=${var.dataset_id_prod}.orders --bq-result-handler ${var.project_id}.${google_bigquery_dataset.default.dataset_id}.results
data-validation validate row --source-conn ${var.REDSHIFT_Connection_Name} --target-conn ${var.BQ_Connection_Name} --tables-list ${var.Prod_Schema}.customer=${var.dataset_id_prod}.customer --primary-keys c_custkey --comparison-fields c_name,c_address,c_nationkey,c_phone --bq-result-handler ${var.project_id}.${google_bigquery_dataset.default.dataset_id}.results --use-random-row --random-row-batch-size 10
data-validation validate row --source-conn ${var.REDSHIFT_Connection_Name} --target-conn ${var.BQ_Connection_Name} --tables-list ${var.Prod_Schema}.lineitem=${var.dataset_id_prod}.lineitem --primary-keys l_orderkey,l_linenumber --comparison-fields l_quantity,l_tax,l_discount --bq-result-handler ${var.project_id}.${google_bigquery_dataset.default.dataset_id}.results --use-random-row --random-row-batch-size 10
data-validation validate row --source-conn ${var.REDSHIFT_Connection_Name} --target-conn ${var.BQ_Connection_Name} --tables-list ${var.Prod_Schema}.nation=${var.dataset_id_prod}.nation --primary-keys n_nationkey --comparison-fields n_name,n_regionkey,n_comment --bq-result-handler ${var.project_id}.${google_bigquery_dataset.default.dataset_id}.results --use-random-row --random-row-batch-size 10
data-validation validate row --source-conn ${var.REDSHIFT_Connection_Name} --target-conn ${var.BQ_Connection_Name} --tables-list ${var.Prod_Schema}.orders=${var.dataset_id_prod}.orders --primary-keys o_orderkey --comparison-fields o_orderstatus,o_priority,o_clerk --bq-result-handler ${var.project_id}.${google_bigquery_dataset.default.dataset_id}.results --use-random-row --random-row-batch-size 10
data-validation validate row --source-conn ${var.REDSHIFT_Connection_Name} --target-conn ${var.BQ_Connection_Name} --tables-list ${var.Prod_Schema}.part=${var.dataset_id_prod}.part --primary-keys p_partkey --comparison-fields p_name,p_brand,p_type,p_size --bq-result-handler ${var.project_id}.${google_bigquery_dataset.default.dataset_id}.results --use-random-row --random-row-batch-size 10
data-validation validate row --source-conn ${var.REDSHIFT_Connection_Name} --target-conn ${var.BQ_Connection_Name} --tables-list ${var.Prod_Schema}.partsupp=${var.dataset_id_prod}.partsupp --primary-keys ps_partkey,ps_suppkey --comparison-fields ps_availqty,ps_supplycost --bq-result-handler ${var.project_id}.${google_bigquery_dataset.default.dataset_id}.results --use-random-row --random-row-batch-size 10
data-validation validate row --source-conn ${var.REDSHIFT_Connection_Name} --target-conn ${var.BQ_Connection_Name} --tables-list ${var.Prod_Schema}.region=${var.dataset_id_prod}.region --primary-keys r_regionkey --comparison-fields r_name --bq-result-handler ${var.project_id}.${google_bigquery_dataset.default.dataset_id}.results --use-random-row --random-row-batch-size 10
data-validation validate row --source-conn ${var.REDSHIFT_Connection_Name} --target-conn ${var.BQ_Connection_Name} --tables-list ${var.Prod_Schema}.supplier=${var.dataset_id_prod}.supplier --primary-keys s_suppkey --comparison-fields s_name --bq-result-handler ${var.project_id}.${google_bigquery_dataset.default.dataset_id}.results --use-random-row --random-row-batch-size 10
EOF
  }
}

Complete Error Logs:

Error: local-exec provisioner error
│ 
│   with module.dwmh_Validation_Automated.null_resource.Installing_Validation_Tool_Executing_Validation_Commands,
│   on DWMH_Validation_Automation/main.tf line 48, in resource "null_resource" "Installing_Validation_Tool_Executing_Validation_Commands":
│   48:   provisioner "local-exec" {
│ 
│ Error running command 'cd DWMH_Validation_Automation
│ apt-get -y install python3-venv
│ python3 -m venv .venv
│ source .venv/bin/activate
│ apt-get update  && apt-get install gcc -y && apt-get clean
│ python3 -m pip install google-pso-data-validator
│ data-validation connections add --connection-name BQ_CONN_CTD BigQuery
│ --project-id plichtaw-591-20250804013802
│ data-validation connections add --connection-name REDSHIFT_CONN_CTD
│ Redshift --port 5439 --user awsuser --password 'MhiKk^&8nL)87ojlUPo#327'
│ --database prod --host
│ dwmr-demo-cluster.cs4ewok6dvip.us-east-1.redshift.amazonaws.com
│ data-validation validate column --source-conn REDSHIFT_CONN_CTD
│ --target-conn BQ_CONN_CTD --tables-list
│ dwmr_prod.customer=dwmr_prod.customer,dwmr_prod.part=dwmr_prod.part,dwmr_prod.nation=dwmr_prod.nation,dwmr_prod.lineitem=dwmr_prod.lineitem,dwmr_prod.supplier=dwmr_prod.supplier,dwmr_prod.partsupp=dwmr_prod.partsupp,dwmr_prod.region=dwmr_prod.region,dwmr_prod.orders=dwmr_prod.orders
│ --bq-result-handler plichtaw-591-20250804013802.pso_data_validator.results
│ data-validation -v validate schema --source-conn REDSHIFT_CONN_CTD
│ --target-conn BQ_CONN_CTD --tables-list
│ dwmr_prod.customer=dwmr_prod.customer,dwmr_prod.part=dwmr_prod.part,dwmr_prod.nation=dwmr_prod.nation,dwmr_prod.lineitem=dwmr_prod.lineitem,dwmr_prod.supplier=dwmr_prod.supplier,dwmr_prod.partsupp=dwmr_prod.partsupp,dwmr_prod.region=dwmr_prod.region,dwmr_prod.orders=dwmr_prod.orders
│ --bq-result-handler plichtaw-591-20250804013802.pso_data_validator.results
│ data-validation validate row --source-conn REDSHIFT_CONN_CTD --target-conn
│ BQ_CONN_CTD --tables-list dwmr_prod.customer=dwmr_prod.customer
│ --primary-keys c_custkey --comparison-fields
│ c_name,c_address,c_nationkey,c_phone --bq-result-handler
│ plichtaw-591-20250804013802.pso_data_validator.results --use-random-row
│ --random-row-batch-size 10
│ data-validation validate row --source-conn REDSHIFT_CONN_CTD --target-conn
│ BQ_CONN_CTD --tables-list dwmr_prod.lineitem=dwmr_prod.lineitem
│ --primary-keys l_orderkey,l_linenumber --comparison-fields
│ l_quantity,l_tax,l_discount --bq-result-handler
│ plichtaw-591-20250804013802.pso_data_validator.results --use-random-row
│ --random-row-batch-size 10
│ data-validation validate row --source-conn REDSHIFT_CONN_CTD --target-conn
│ BQ_CONN_CTD --tables-list dwmr_prod.nation=dwmr_prod.nation --primary-keys
│ n_nationkey --comparison-fields n_name,n_regionkey,n_comment
│ --bq-result-handler plichtaw-591-20250804013802.pso_data_validator.results
│ --use-random-row --random-row-batch-size 10
│ data-validation validate row --source-conn REDSHIFT_CONN_CTD --target-conn
│ BQ_CONN_CTD --tables-list dwmr_prod.orders=dwmr_prod.orders --primary-keys
│ o_orderkey --comparison-fields o_orderstatus,o_priority,o_clerk
│ --bq-result-handler plichtaw-591-20250804013802.pso_data_validator.results
│ --use-random-row --random-row-batch-size 10
│ data-validation validate row --source-conn REDSHIFT_CONN_CTD --target-conn
│ BQ_CONN_CTD --tables-list dwmr_prod.part=dwmr_prod.part --primary-keys
│ p_partkey --comparison-fields p_name,p_brand,p_type,p_size
│ --bq-result-handler plichtaw-591-20250804013802.pso_data_validator.results
│ --use-random-row --random-row-batch-size 10
│ data-validation validate row --source-conn REDSHIFT_CONN_CTD --target-conn
│ BQ_CONN_CTD --tables-list dwmr_prod.partsupp=dwmr_prod.partsupp
│ --primary-keys ps_partkey,ps_suppkey --comparison-fields
│ ps_availqty,ps_supplycost --bq-result-handler
│ plichtaw-591-20250804013802.pso_data_validator.results --use-random-row
│ --random-row-batch-size 10
│ data-validation validate row --source-conn REDSHIFT_CONN_CTD --target-conn
│ BQ_CONN_CTD --tables-list dwmr_prod.region=dwmr_prod.region --primary-keys
│ r_regionkey --comparison-fields r_name --bq-result-handler
│ plichtaw-591-20250804013802.pso_data_validator.results --use-random-row
│ --random-row-batch-size 10
│ data-validation validate row --source-conn REDSHIFT_CONN_CTD --target-conn
│ BQ_CONN_CTD --tables-list dwmr_prod.supplier=dwmr_prod.supplier
│ --primary-keys s_suppkey --comparison-fields s_name --bq-result-handler
│ plichtaw-591-20250804013802.pso_data_validator.results --use-random-row
│ --random-row-batch-size 10
│ ': exit status 1. Output:  build_config_managers_from_args(args)
│                       ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
│   File "/workspace/data-cloud/Data Warehouse Migration-591/demo/terraform/DWMH_Validation_Automation/.venv/lib/python3.11/site-packages/data_validation/__main__.py", line 361, in build_config_managers_from_args
│     return util.timed_call("Build config", _build_configs)
│            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
│   File "/workspace/data-cloud/Data Warehouse Migration-591/demo/terraform/DWMH_Validation_Automation/.venv/lib/python3.11/site-packages/data_validation/util.py", line 30, in timed_call
│     result = fn(*args, **kwargs)
│              ^^^^^^^^^^^^^^^^^^^
│   File "/workspace/data-cloud/Data Warehouse Migration-591/demo/terraform/DWMH_Validation_Automation/.venv/lib/python3.11/site-packages/data_validation/__main__.py", line 348, in _build_configs
│     pre_build_configs_list = cli_tools.get_pre_build_configs(args, validate_cmd)
│                              ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
│   File "/workspace/data-cloud/Data Warehouse Migration-591/demo/terraform/DWMH_Validation_Automation/.venv/lib/python3.11/site-packages/data_validation/cli_tools.py", line 1595, in get_pre_build_configs
│     source_client = clients.get_data_client(mgr.get_connection_config(args.source_conn))
│                                             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
│   File "/workspace/data-cloud/Data Warehouse Migration-591/demo/terraform/DWMH_Validation_Automation/.venv/lib/python3.11/site-packages/data_validation/state_manager.py", line 81, in get_connection_config
│     conn_str = gcs_helper.read_file(connection_path)
│                ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
│   File "/workspace/data-cloud/Data Warehouse Migration-591/demo/terraform/DWMH_Validation_Automation/.venv/lib/python3.11/site-packages/data_validation/gcs_helper.py", line 82, in read_file
│     with open(file_path, "r") as f:
│          ^^^^^^^^^^^^^^^^^^^^
│ FileNotFoundError: [Errno 2] No such file or directory:
│ '/builder/home/.config/google-pso-data-validator/REDSHIFT_CONN_CTD.connection.json'
│ ERROR:root:Exception No module named 'ibm_db_dbi' while importing
│ db2_connect
│ Traceback (most recent call last):
│   File "/workspace/data-cloud/Data Warehouse Migration-591/demo/terraform/DWMH_Validation_Automation/.venv/bin/data-validation", line 10, in <module>
│     sys.exit(main())
│              ^^^^^^
│   File "/workspace/data-cloud/Data Warehouse Migration-591/demo/terraform/DWMH_Validation_Automation/.venv/lib/python3.11/site-packages/data_validation/__main__.py", line 682, in main
│     validate(args)
│   File "/workspace/data-cloud/Data Warehouse Migration-591/demo/terraform/DWMH_Validation_Automation/.venv/lib/python3.11/site-packages/data_validation/__main__.py", line 658, in validate
│     run(args)
│   File "/workspace/data-cloud/Data Warehouse Migration-591/demo/terraform/DWMH_Validation_Automation/.venv/lib/python3.11/site-packages/data_validation/__main__.py", line 614, in run
│     config_managers = build_config_managers_from_args(args)
│                       ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
│   File "/workspace/data-cloud/Data Warehouse Migration-591/demo/terraform/DWMH_Validation_Automation/.venv/lib/python3.11/site-packages/data_validation/__main__.py", line 361, in build_config_managers_from_args
│     return util.timed_call("Build config", _build_configs)
│            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
│   File "/workspace/data-cloud/Data Warehouse Migration-591/demo/terraform/DWMH_Validation_Automation/.venv/lib/python3.11/site-packages/data_validation/util.py", line 30, in timed_call
│     result = fn(*args, **kwargs)
│              ^^^^^^^^^^^^^^^^^^^
│   File "/workspace/data-cloud/Data Warehouse Migration-591/demo/terraform/DWMH_Validation_Automation/.venv/lib/python3.11/site-packages/data_validation/__main__.py", line 348, in _build_configs
│     pre_build_configs_list = cli_tools.get_pre_build_configs(args, validate_cmd)
│                              ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
│   File "/workspace/data-cloud/Data Warehouse Migration-591/demo/terraform/DWMH_Validation_Automation/.venv/lib/python3.11/site-packages/data_validation/cli_tools.py", line 1595, in get_pre_build_configs
│     source_client = clients.get_data_client(mgr.get_connection_config(args.source_conn))
│                                             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
│   File "/workspace/data-cloud/Data Warehouse Migration-591/demo/terraform/DWMH_Validation_Automation/.venv/lib/python3.11/site-packages/data_validation/state_manager.py", line 81, in get_connection_config
│     conn_str = gcs_helper.read_file(connection_path)
│                ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
│   File "/workspace/data-cloud/Data Warehouse Migration-591/demo/terraform/DWMH_Validation_Automation/.venv/lib/python3.11/site-packages/data_validation/gcs_helper.py", line 82, in read_file
│     with open(file_path, "r") as f:
│          ^^^^^^^^^^^^^^^^^^^^
│ FileNotFoundError: [Errno 2] No such file or directory:
│ '/builder/home/.config/google-pso-data-validator/REDSHIFT_CONN_CTD.connection.json'
│ ERROR:root:Exception No module named 'ibm_db_dbi' while importing
│ db2_connect
│ Traceback (most recent call last):
│   File "/workspace/data-cloud/Data Warehouse Migration-591/demo/terraform/DWMH_Validation_Automation/.venv/bin/data-validation", line 10, in <module>
│     sys.exit(main())
│              ^^^^^^
│   File "/workspace/data-cloud/Data Warehouse Migration-591/demo/terraform/DWMH_Validation_Automation/.venv/lib/python3.11/site-packages/data_validation/__main__.py", line 682, in main
│     validate(args)
│   File "/workspace/data-cloud/Data Warehouse Migration-591/demo/terraform/DWMH_Validation_Automation/.venv/lib/python3.11/site-packages/data_validation/__main__.py", line 658, in validate
│     run(args)
│   File "/workspace/data-cloud/Data Warehouse Migration-591/demo/terraform/DWMH_Validation_Automation/.venv/lib/python3.11/site-packages/data_validation/__main__.py", line 614, in run
│     config_managers = build_config_managers_from_args(args)
│                       ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
│   File "/workspace/data-cloud/Data Warehouse Migration-591/demo/terraform/DWMH_Validation_Automation/.venv/lib/python3.11/site-packages/data_validation/__main__.py", line 361, in build_config_managers_from_args
│     return util.timed_call("Build config", _build_configs)
│            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
│   File "/workspace/data-cloud/Data Warehouse Migration-591/demo/terraform/DWMH_Validation_Automation/.venv/lib/python3.11/site-packages/data_validation/util.py", line 30, in timed_call
│     result = fn(*args, **kwargs)
│              ^^^^^^^^^^^^^^^^^^^
│   File "/workspace/data-cloud/Data Warehouse Migration-591/demo/terraform/DWMH_Validation_Automation/.venv/lib/python3.11/site-packages/data_validation/__main__.py", line 348, in _build_configs
│     pre_build_configs_list = cli_tools.get_pre_build_configs(args, validate_cmd)
│                              ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
│   File "/workspace/data-cloud/Data Warehouse Migration-591/demo/terraform/DWMH_Validation_Automation/.venv/lib/python3.11/site-packages/data_validation/cli_tools.py", line 1595, in get_pre_build_configs
│     source_client = clients.get_data_client(mgr.get_connection_config(args.source_conn))
│                                             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
│   File "/workspace/data-cloud/Data Warehouse Migration-591/demo/terraform/DWMH_Validation_Automation/.venv/lib/python3.11/site-packages/data_validation/state_manager.py", line 81, in get_connection_config
│     conn_str = gcs_helper.read_file(connection_path)
│                ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
│   File "/workspace/data-cloud/Data Warehouse Migration-591/demo/terraform/DWMH_Validation_Automation/.venv/lib/python3.11/site-packages/data_validation/gcs_helper.py", line 82, in read_file
│     with open(file_path, "r") as f:
│          ^^^^^^^^^^^^^^^^^^^^
│ FileNotFoundError: [Errno 2] No such file or directory:
│ '/builder/home/.config/google-pso-data-validator/REDSHIFT_CONN_CTD.connection.json'

[nj1973]

Thanks @arpitgup , the message below should just be a warning and not the root cause of your problem:

Exception No module named 'ibm_db_dbi' while importing db2_connect

I suspect that the FileNotFound exception is a knock on error from

sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedObject) unrecognized configuration parameter "standard_conforming_strings"

Researching the above message lead me to this SO post: https://stackoverflow.com/a/76629781

It "might" be that our Redshift code is not compatible with the latest SQLAlchemy releases.

[helensilva14]

@nj1973 your guess is probably correct about Redshift not being compatible with SQLAlchemy 2.0, we identified this problem almost 2 years ago, take a look here: #923 (comment).

And right now we are using the following versions:

    "psycopg2-binary>=2.9.9",
    "SQLAlchemy==2.0.41",  # Need 2.0 or higher for oracledb

[arpitgup]

@nj1973 @helensilva14
FYI - Latest version of google-pso-data-validator is working after explicitly installing SQLAlchemy==1.4.49 & (May be ibm_db_sa).

[helensilva14]

@arpitgup can you please confirm: were you able to run your validation without errors? If so we will open a PR to add this warning in our documentation for the people who would want to use DVT for Redshift.

[sundar-mudupalli-work]

Hi all,

We have two bad choices to make

• Stay with SQLAlchemy 1.4.49 - continue to support Redshift, SQLAlchemy is no longer supported and we cannot use oracle connector (cx_Oracle is no longer supported).
• Migrate to SQLAlchemy 2.0 - use SQLAlchemy supported version and connect to Oracle, but cannot use Redshift connections.

Even though SQLAlchemy 2.0 was released in Feb 2023 - the sqlalchemy-redshift library has not been updated to support it. You can see the issue here

Sundar Mudupalli

[helensilva14]

Hi @sundar-mudupalli-work!

I don't think we need to make version changes necessarily, specially given the fact we don't have a huge Redshift audience and Oracle is definitely more used with DVT. My thought was to just add instructions in docs/connections.md in the Redshift section stating to use SQLAlchemy==1.4.49.

https://github.com/GoogleCloudPlatform/professional-services-data-validator/blob/develop/docs/connections.md#redshift

[arpitgup]

@arpitgup can you please confirm: were you able to run your validation without errors? If so we will open a PR to add this warning in our documentation for the people who would want to use DVT for Redshift.

@helensilva14 after explicitly installing SQLAlchemy==1.4.49, I am able to run the validations.

[nj1973]

I don't think we need to make version changes necessarily, specially given the fact we don't have a huge Redshift audience and Oracle is definitely more used with DVT. My thought was to just add instructions in docs/connections.md in the Redshift section stating to use SQLAlchemy==1.4.49.

I was thinking the same. We have a default installation works with SQLAlchemy 2.0+ and Redshift specific instructions as a workaround. Although that could be time limited as other code become less and less compatible with older versions of SQLAlchemy.

Another option is we create test infrastructure for Redshift and see if we can patch the root cause of:

(psycopg2.errors.UndefinedObject) unrecognized configuration parameter "standard_conforming_strings"

[sundar-mudupalli-work]

Hi,

I am OK with Redshift specific instructions to use SQLAlchemy==1.4.49 in setup.py and rebuild data-validation using pip install .. I would say this option a) will not allow oracle connections and b) has been reported to work by customers, and is not part of the DVT test suite. We should also refer to the sqlalchemy-redshift issue.

Reviewing the above link regarding the root cause of the issue, it does not appear to be straightforward. There is another non AWS library that is SQLAlchemy 2.0 compliant and it seems to require some other stuff. Not very clear.

Hope that helps.

Sundar Mudupalli