SNOW-723663: MergeInto and CopyInto do not respect schema_translate_map

[sjhewitt]

This is related to #322 - some sql clauses in MergeInto and CopyInto aren't passed to the compiler and just rendered directly as strings.

1. What version of Python are you using?

3.9.7

2. What operating system and processor architecture are you using?

Linux-6.1.3-arch1-1-x86_64-with-glibc2.36

3. What are the component versions in the environment (pip freeze)?

snowflake-connector-python==2.7.12
snowflake-sqlalchemy==1.4.4
SQLAlchemy==1.4.41

4. What did you do?

from sqlalchemy import MetaData, Table, String, Column, select, create_engine, literal_column
from snowflake.sqlalchemy.custom_commands import MergeInto, CopyInto

metadata = MetaData()
t = Table("my_table", metadata, Column("col", String()), schema="PUBLIC")

engine = create_engine(
    snowflake_url,
    execution_options={"schema_translate_map": {"PUBLIC": "CUSTOM_SCHEMA"}},
)

source = select(literal_column("1").label("col")).alias("source")

try:
    stmt = MergeInto(target=t, source=source, on=source.c.col == t.c.col)
    stmt.when_matched_then_update().values(col=source.c.col)
    stmt.when_not_matched_then_insert().values(col=source.c.col)
    engine.execute(stmt)
except Exception as e:
    print(e)


try:
    stmt = CopyInto(into=t, from_=source)
    engine.execute(stmt)
except Exception as e:
    print(e)

This executes the following SQL:

MERGE INTO PUBLIC.my_table USING SELECT 1 AS col ON "PUBLIC".my_table.col = source.col WHEN MATCHED THEN UPDATE SET col = source.col WHEN NOT MATCHED THEN INSERT (col) VALUES (source.col)

and

COPY INTO PUBLIC.my_table FROM (SELECT 1 AS col)

5. What did you expect to see?

Expected that the schema PUBLIC is replaced with CUSTOM_SCHEMA in the generated sql:

MERGE INTO "CUSTOM_SCHEMA".my_table USING (SELECT 1 AS col) AS source ON "CUSTOM_SCHEMA".my_table.col = source.col WHEN MATCHED THEN UPDATE SET col = source.col WHEN NOT MATCHED THEN INSERT (col) VALUES (source.col)

and

COPY INTO "CUSTOM_SCHEMA".my_table FROM (SELECT 1 AS col)

6. Can you set logging to DEBUG and collect the logs?

2023-01-10 13:44:05,283 h[simar] u[~] o[~] l[snowflake.connector.connection] INFO Snowflake Connector for Python Version: 2.7.12, Python Version: 3.9.7, Platform: Linux-6.1.3-arch1-1-x86_64-with-glibc2.36
2023-01-10 13:44:05,283 h[simar] u[~] o[~] l[snowflake.connector.connection] INFO This connection is in OCSP Fail Open Mode. TLS Certificates would be checked for validity and revocation status. Any other Certificate Revocation related exceptions or OCSP Responder failures would be disregarded in favor of connectivity.
2023-01-10 13:44:05,283 h[simar] u[~] o[~] l[snowflake.connector.connection] INFO Setting use_openssl_only mode to False
2023-01-10 13:44:06,204 h[simar] u[~] o[~] l[snowflake.connector.cursor] INFO query: [select current_database(), current_schema();]
2023-01-10 13:44:06,364 h[simar] u[~] o[~] l[snowflake.connector.cursor] INFO query execution done
2023-01-10 13:44:06,365 h[simar] u[~] o[~] l[snowflake.connector.cursor] INFO query: [ROLLBACK]
2023-01-10 13:44:06,512 h[simar] u[~] o[~] l[snowflake.connector.cursor] INFO query execution done
2023-01-10 13:44:06,514 h[simar] u[~] o[~] l[snowflake.connector.cursor] INFO query: [MERGE INTO PUBLIC.my_table USING SELECT 1 AS col ON "PUBLIC".my_table.col = sour...]
2023-01-10 13:44:06,683 h[simar] u[~] o[~] l[snowflake.connector.cursor] INFO query execution done
2023-01-10 13:44:06,685 h[simar] u[~] o[~] l[snowflake.connector.cursor] INFO query: [ROLLBACK]
2023-01-10 13:44:06,822 h[simar] u[~] o[~] l[snowflake.connector.cursor] INFO query execution done
2023-01-10 13:44:06,823 h[simar] u[~] o[~] l[snowflake.connector.cursor] INFO query: [ROLLBACK]
2023-01-10 13:44:06,973 h[simar] u[~] o[~] l[snowflake.connector.cursor] INFO query execution done
(snowflake.connector.errors.ProgrammingError) 001003 (42000): 01a99078-0604-37f4-0024-8487005bbc42: SQL compilation error:
syntax error line 1 at position 33 unexpected 'SELECT'.
[SQL: MERGE INTO PUBLIC.my_table USING SELECT 1 AS col ON "PUBLIC".my_table.col = source.col WHEN MATCHED THEN UPDATE SET col = source.col WHEN NOT MATCHED THEN INSERT (col) VALUES (source.col)]
(Background on this error at: https://sqlalche.me/e/14/f405)
2023-01-10 13:44:06,973 h[simar] u[~] o[~] l[snowflake.connector.cursor] INFO query: [COPY INTO PUBLIC.my_table FROM (SELECT 1 AS col)]
2023-01-10 13:44:07,142 h[simar] u[~] o[~] l[snowflake.connector.cursor] INFO query execution done
2023-01-10 13:44:07,143 h[simar] u[~] o[~] l[snowflake.connector.cursor] INFO query: [ROLLBACK]
2023-01-10 13:44:07,283 h[simar] u[~] o[~] l[snowflake.connector.cursor] INFO query execution done
2023-01-10 13:44:07,283 h[simar] u[~] o[~] l[snowflake.connector.cursor] INFO query: [ROLLBACK]
2023-01-10 13:44:07,423 h[simar] u[~] o[~] l[snowflake.connector.cursor] INFO query execution done
(snowflake.connector.errors.ProgrammingError) 002146 (0A000): 01a99078-0604-38eb-0024-8487005b9c3e: SQL compilation error:
Invalid from object type used in Copy transformation
[SQL: COPY INTO PUBLIC.my_table FROM (SELECT 1 AS col) ]
(Background on this error at: https://sqlalche.me/e/14/f405)

[sfc-gh-dszmolka]

thank you for raising this with us and also for your contribution ! hopefully the connector team can review the PR soon