Great_expectations: `expect_column_quantile_values_to_be_between` throws error for redshift datasource

[colmsnowplow]

psycopg2.errors.SyntaxError: Aggregate function "percentile_disc" is not supported; use approximate percentile_disc or percentile_cont instead.

Not sure yet if this is Redshift-specific or not but will be testing Snowflake and BQ at some point too.

[Aylr]

Ah! Yes, this is a little tricky. It looks like you are using the psycopg driver, which works well with redshift, but... sqlalchemy doesn't know this is redshift (which has a subtle SQL difference here).

So, as a workaround to use this expectation on redshift, you'll need to use a different redshift driver, and set a specific flag allow_relative_error=True in that expectation.

The downside to this is that there's a known issue with the redshift driver that makes db introspection super slow, so you'll likely notice some long lonely minutes while GE waits for the driver to sort out datatypes.

We've talked internally in the past about our options here and probably need to re-raise this issue.

[colmsnowplow]

No worries, I don't actually need this test in my suite at the moment, it's just one of the ones that was part of the default set generated on suite new - I raised the issue in case it wasn't known, since I didn't find any related issues.

Incidentally, as an FYI - on BigQuery, I've run into a similar error with the same expectation, in case it's not also on the radar:

DatabaseError: (google.cloud.bigquery.dbapi.exceptions.DatabaseError) 400 Syntax error: Unexpected keyword WITHIN at [1:46] (job ID: 36dc52b0-9af7-43cc-b841-9b007882272b)
...
[SQL: SELECT percentile_disc(%(percentile_disc_1)s) WITHIN GROUP (ORDER BY `doc_width` ASC) AS `anon_1`, percentile_disc(%(percentile_disc_2)s) WITHIN GROUP (ORDER BY `doc_width` ASC) AS `anon_2`, percentile_disc(%(percentile_disc_3)s) WITHIN GROUP (ORDER BY `doc_width` ASC) AS `anon_3`, percentile_disc(%(percentile_disc_4)s) WITHIN GROUP (ORDER BY `doc_width` ASC) AS `anon_4`, percentile_disc(%(percentile_disc_5)s) WITHIN GROUP (ORDER BY `doc_width` ASC) AS `anon_5` FROM `ge_temp`] [parameters: {'percentile_disc_1': 0.05, 'percentile_disc_2': 0.25, 'percentile_disc_3': 0.5, 'percentile_disc_4': 0.75, 'percentile_disc_5': 0.95}]

[github-actions[bot]]

Is this still relevant? If so, what is blocking it? Is there anything you can do to help move it forward?nnThis issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.