Facing a syntax error with psycopg2 while trying to read data using a query involving a WITH statement

GX Core Support
1

this is the runtimebatchrequest
RuntimeBatchRequest(
datasource_name=“my_postgres_datasource”,
data_connector_name=“default_runtime_data_connector_name”,
data_asset_name=“default_name”, # this can be anything that identifies this data
runtime_parameters={“query”: combined_query},
batch_identifiers={“default_identifier_name”: “default_identifier”},
)
this is the combined_query
combined_query = WITH data_table_1 AS (SELECT ‘category_1’ AS type, COUNT() AS cnt FROM table_1_union UNION SELECT ‘category_2’ AS type, COUNT() AS cnt FROM table_2_union),
data_table_2 AS (SELECT ‘category_1’ AS type, COUNT() AS cnt FROM table_1_values UNION SELECT ‘category_2’ AS type, COUNT() AS cnt FROM table_2_values)
SELECT COUNT(d.diff) FROM (SELECT dt1.type, ABS(dt1.cnt - dt2.cnt) AS diff
FROM data_table_1 dt1 JOIN data_table_2 dt2 ON dt1.type = dt2.type) d
WHERE d.diff > 0

this is the validator
validator_query = self.gx_context.get_validator(batch_request = batch)

the error that i am getting
(psycopg2.errors.SyntaxError) syntax error at or near “(”\nLINE 2: FROM (SELECT b1 as( select 'pt_lat_value' as type, count(*) …\n ^\n\n[SQL: CREATE TEMPORARY TABLE “gx_temp_d875805f” AS SELECT * \nFROM (SELECT data_table_1 AS (SELECT ‘category_1’ AS type, COUNT() AS cnt FROM table_1_union UNION SELECT ‘category_2’ AS type, COUNT() AS cnt FROM table_2_union),
data_table_2 AS (SELECT ‘category_1’ AS type, COUNT() AS cnt FROM table_1_values UNION SELECT ‘category_2’ AS type, COUNT() AS cnt FROM table_2_values)
SELECT COUNT(d.diff) FROM (SELECT dt1.type, ABS(dt1.cnt - dt2.cnt) AS diff
FROM data_table_1 dt1 JOIN data_table_2 dt2 ON dt1.type = dt2.type) d
WHERE d.diff > 0) AS anon_1 \nWHERE true]\n(Background on this error at: Error Messages — SQLAlchemy 2.0 Documentation)

“the query that gx uses to build the temporary table excluded the “with” part and this is giving syntax error.”
I am new to GX so it will be great help for me.

2

Hi @rishab12023, thanks for reaching out, and welcome to the community!

  • The use of RuntimeBatchRequest is deprecated, I’d first recommend updating to the latest version of GX (0.18.13 as of this post) if you are not already running it.

  • Here’s a direct link to our docs on connecting to a Postgres data source: Connect to SQL database Data Assets | Great Expectations. These docs will guide you through connecting to your Postgres data source using the current Fluent Data Source (FDS) approach instead of RuntimeBatchRequest.

  • After creating your Postgres Data Source, you can either create a Table Asset or Query Asset. If you use a Query Asset, you’ll need to provide a SELECT statement for the query - queries starting with other keywords, such as WITH for common table expressions (CTEs), are not supported.

    • Our recommended best practice if you want to use a CTE to create a GX Data Asset is to create a SQL view using that CTE outside of your GX workflow and then connect to the view with a GX Table Asset.