MSCEWI2022

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 ERROR tables 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.

• If you need more support, you can email us at [email protected]