Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

SNOW-723663: MergeInto and CopyInto do not respect schema_translate_map #371

Open
sjhewitt opened this issue Jan 10, 2023 · 1 comment · May be fixed by #372
Open

SNOW-723663: MergeInto and CopyInto do not respect schema_translate_map #371

sjhewitt opened this issue Jan 10, 2023 · 1 comment · May be fixed by #372
Assignees
@sfc-gh-dszmolka
Labels
bug Something isn't working status-pr_pending_merge A PR is made and is under review status-triage_done Initial triage done, will be further handled by the driver team

Comments

@sjhewitt
Copy link
Contributor

sjhewitt commented Jan 10, 2023 •
edited

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

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

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

  1. 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
  1. 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)
  1. 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)
  1. 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)
@sjhewitt sjhewitt added bug Something isn't working needs triage labels Jan 10, 2023
@github-actions github-actions bot changed the title MergeInto and CopyInto do not respect schema_translate_map SNOW-723663: MergeInto and CopyInto do not respect schema_translate_map Jan 10, 2023
@sjhewitt sjhewitt linked a pull request Jan 10, 2023 that will close this issue
Open
4 tasks
@sfc-gh-dszmolka
Copy link

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

@sfc-gh-dszmolka sfc-gh-dszmolka added status-pr_pending_merge A PR is made and is under review status-triage_done Initial triage done, will be further handled by the driver team and removed needs triage labels Mar 15, 2024
@sfc-gh-dszmolka sfc-gh-dszmolka self-assigned this Mar 15, 2024
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@sfc-gh-dszmolka sfc-gh-dszmolka

Labels
bug Something isn't working status-pr_pending_merge A PR is made and is under review status-triage_done Initial triage done, will be further handled by the driver team
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

call _compiler_dispatch for merge_into and copy_into clauses sjhewitt/snowflake-sqlalchemy
2 participants
@sjhewitt @sfc-gh-dszmolka