Redshift catalog extract

[dhuettenmoser]

dbcat/dbcat/catalog/db.py

Line 175 in 938c5af

WHERE TABLE_SCHEMA NOT IN ('information_schema', 'pg_catalog')

in dbcat.catalog.db line 175 should be: WHERE SCHEMA NOT IN ('information_schema', 'pg_catalog')
this supplemental where clause is added after the extractor renames the table_schema columns to schema

[siva-mudiyanur]

I guess 'table_schema' in where clause seems to be fine as its going to be used to query against catalog tables, column 'schema' is being returned by RedshiftMetadataExtractor

[vrajat]

Good catch. Thanks for pointing this out. I am not sure how I missed this when I tested with Redshift. This is also true for postgres and mysql. However postgres and mysql do not have unions or joins in their SQL queries w.r.t to schema and the extractors worked silently.

@dhuettenmoser or @siva-mudiyanur , do you have access to a redshift database that you can test fix with?

[vrajat]

I just tested using "schema" for postgres and mysql. The change does not work because postgres does not support alias in where clause.

[dhuettenmoser]

I've only tested against redshift. I modified the local library file in order to get the redshift extraction to work, and changed that line to WHERE SCHEMA NOT IN ('information_schema', 'pg_catalog')
Prior to this I was getting "column "table_schema" does not exist" errors, after making the adjustment the extractor worked as expected. Thanks for the quick response!

[vrajat]

Got it. Will you be able to test a patch version? I can release 0.5.4 with this fix. I will not be able to test redshift support for a few more days.

[dhuettenmoser]

yes, I can test that out.

[vrajat]

I've released v0.5.3. Can you check if that fixes this bug?

[dhuettenmoser]

Thanks for making that adjustment! I'll test it out in the morning to verify.

[siva-mudiyanur]

I may not get a chance to test soon but I would definitely make sure to test by end of this week

[dhuettenmoser]

This fix works in my case. Thanks!

[vrajat]

Thanks. I'll close this ticket. @siva-mudiyanur if you can confirm, I'll be grateful.

[siva-mudiyanur]

Hi @vrajat ..I've been getting errors when I try to update the package since the issue is fixed, am I missing something or package versions needs to be loosened up in the code?

INFO: pip is looking at multiple versions of dbcat to determine which version is compatible with other requirements. This could take a while.
ERROR: Cannot install amundsen-databuilder[athena,bigquery,glue,rds,snowflake]==5.2.0 and amundsen-databuilder[athena,bigquery,glue,rds,snowflake]==5.2.2 because these package versions have conflicting dependencies.

The conflict is caused by:
    amundsen-databuilder[athena,bigquery,glue,rds,snowflake] 5.2.2 depends on mysqlclient<3 and >=1.3.6; extra == "rds"
    amundsen-databuilder[athena,bigquery,glue,rds,snowflake] 5.2.0 depends on mysqlclient<3 and >=1.3.6; extra == "rds"

To fix this you could try to:
1. loosen the range of package versions you've specified
2. remove package versions to allow pip attempt to solve the dependency conflict