sqlparser/sqlflow_public

Valid MERGE statement in Redshift fails to parse or display lineage

Closed this issue · 3 comments

johncamburn commented

The following statement is valid in both Redshift and Snowflake. It parses and displays properly in SQLFlow using the Snowflake database option, but it fails using Redshift.

MERGE INTO v2_int_customer.hcp_identifier USING int_temp.hcp_identifier_temp AS _t1 ON v2_int_customer.hcp_identifier.data_source_key = _t1.data_source_key
AND v2_int_customer.hcp_identifier.source_key = _t1.source_key WHEN MATCHED THEN
UPDATE
SET master_id = _t1.master_id WHEN NOT MATCHED THEN
INSERT (data_source_key,
source_key,
master_id)
VALUES (_t1.data_source_key, _t1.source_key, _t1.master_id);

image
The above image shows the MERGE command being properly parsed and visualized using the Snowflake library.

image
This image shows the error that occurs when parsing and visualizing the exact same query in Redshift. NOTE: this syntax definitely works in Redshift, as we are running this code successfully today on version Redshift 1.0.69065.

sqlparser commented

Thanks for the feedback. Will fix this bug and get back to you once the new version is released.

James

sqlparser commented

Redshift is based on PostgreSQL, but it doesn't support all PostgreSQL features, and MERGE is one of those unsupported features.

sqlparser commented

@johncamburn fixed and you may give a try here: https://sqlflow.gudusoft.com/