MSCEWI2006

Column name not supported.

This is a deprecated version of the SnowConvert documentation, please visit the official site HERE.

Severity

Medium

Description

Some column names that are supported for Teradata are not supported for Snowflake, such as reserved names. You can review the details about reserved word in the snowflake documentation: https://docs.snowflake.com/en/sql-reference/reserved-keywords.html

In general the following identifiers:

• ALL

• ALTER

• AND

• ANY

• AS

• BETWEEN

• BY

• CHECK

• COLUMN

• CONNECT

• CONSTRAINT

• CREATE

• CURRENT

• CURRENT_DATE

• CURRENT_TIME

• CURRENT_TIMESTAMP

• CURRENT_USER

• DELETE

• DISTINCT

• DROP

• ELSE

• EXISTS

• FOLLOWING

• FOR

• FROM

• GRANT

• GROUP

• HAVING

• ILIKE

• IN

• INCREMENT

• INSERT

• INTERSECT

• INTO

• IS

• LIKE

• LOCALTIME

• LOCALTIMESTAMP

• MINUS

• NOT

• NULL

• OF

• ON

• OR

• ORDER

• QUALIFY

• REGEXP

• REVOKE

• RLIKE

• ROW

• ROWS

• SAMPLE

• SELECT

• SET

• SOME

• START

• TABLE

• TABLESAMPLE

• THEN

• TO

• TRIGGER

• UNION

• UNIQUE

• UPDATE

• VALUES

• WHENEVER

• WHERE

• WITH

When found as column in a table definition will be renamed to XXX_columname_XXX. This will avoid issues when running the code in snowflake. These renaming is used to allow easy identification of these columns.

Code Example

Input Code:

CREATE MULTISET TABLE T_2006 ,
NO FALLBACK 
(
    LOCALTIME INTEGER,
    LOCALTIMESTAMP INTEGER,
    CURRENT_TIME INTEGER
);

Output Code:

CREATE TABLE PUBLIC.T_2006
(
XXX_LOCALTIME_XXX INTEGER /*** MSC-WARNING - MSCEWI2006 - LOCALTIME COLUMN NAME NOT SUPPORTED ***/,
XXX_LOCALTIMESTAMP_XXX INTEGER /*** MSC-WARNING - MSCEWI2006 - LOCALTIMESTAMP COLUMN NAME NOT SUPPORTED ***/,
XXX_CURRENT_TIME_XXX INTEGER /*** MSC-WARNING - MSCEWI2006 - CURRENT_TIME COLUMN NAME NOT SUPPORTED ***/
);

Recommendations

• Consider renaming the columns that use names that are not supported in Snowflake.

• You can use double quotes with these identifiers but be aware that unless you use the QUOTED_IDENTIFIERS_IGNORE_CASE the identifiers will be case-sensitive.

• If you are using MacOS a simple one-liner like this: find . -name '*.sql' -print0 | xargs -0 sed -i "" "s/XXX_GROUP_XXX/\"GROUP\"/g" can be used for example to change all the XXX_GROUP_XXX identifiers.

• In Windows you can use a Powershell snippet like this: Get-ChildItem -Filter . -Recurse | where {-not $.psiscontainer} | Foreach-Object { $c = ($ | Get-Content) $pattern = 'XXX_GROUP_XXX' $replacement = '"GROUP"' If ($c | Select-String -Pattern $pattern) { $c = $c -creplace $pattern, $replacement [IO.File]::WriteAllText($_.FullName, ($c -join “rn”)) } }

• If you need more support, you can email us at [e-mail]