Commented out code related with dropping work or error table.
This is a deprecated version of the SnowConvert documentation, please visit the official site HERE.
Severity
Low
Description
This warning is shown when a DROP statement from fastload and multiload files is removed. The statement is only removed if the tables that are being dropped are ERRORtables used in a BEGIN LOADING command.
Error tables are specified on fastload and multiload, but they are not needed in snowflake. Since they are created internally when used on these scripts, and not in Snowflake; the reference would not be resolved when dropping them.
Code Example
Input Code:
DROP TABLE FastTable;
DROP TABLE Error1;
DROP TABLE Error2;
CREATE TABLE FastTable, NO FALLBACK
( ID INTEGER, UFACTOR INTEGER, MISC CHAR(42))
PRIMARY INDEX(ID);
DEFINE ID (INTEGER), UFACTOR (INTEGER), MISC (CHAR(42))
FILE=FileName;
SHOW;
BEGIN LOADING FastTable ERRORFILES Error1,Error2
CHECKPOINT 10000;
INSERT INTO FastTable (ID, UFACTOR, MISC) VALUES
(:ID, :MISC);
END LOADING;
Output Code:
snowconvert_helpers.execute_sql_statement("""DROP TABLE PUBLIC.FastTable""", con)
#** MSC-WARNING - MSCEWI2022 - COMMENTED OUT CODE RELATED WITH DROPPING WORK OR ERROR TABLE.
#DROP TABLE Error1 **
#** MSC-WARNING - MSCEWI2022 - COMMENTED OUT CODE RELATED WITH DROPPING WORK OR ERROR TABLE.
#DROP TABLE Error2 **
snowconvert_helpers.execute_sql_statement("""CREATE TABLE PUBLIC.FastTable
(
ID INTEGER,
UFACTOR INTEGER,
MISC CHAR(42))""", con)
#** MSC-WARNING - MSCEWI2020 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE. DEFINE ID (INTEGER), UFACTOR (INTEGER), MISC (CHAR(42))
# FILE=FileName **
#** MSC-WARNING - MSCEWI2020 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE. SHOW **
snowconvert_helpers.execute_sql_statement("""COPY INTO FastTable FROM {} ON_ERROR = CONTINUE""".format(inputDataPlaceholder))
sql = """CREATE TABLE CTE_FastTable AS SELECT DISTINCT * FROM FastTable"""
snowconvert_helpers.execute_sql_statement(sql, con)
sql = """DROP TABLE FastTable"""
snowconvert_helpers.execute_sql_statement(sql, con)
sql = """ ALTER TABLE CTE_FastTable RENAME TO FastTable"""
snowconvert_helpers.execute_sql_statement(sql, con)
#** MSC-WARNING - MSCEWI2020 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE. END LOADING **
Recommendations
Functional equivalence is not affected by removing DROP statements applied to error tables, is just an informative warning.
