SnowConvert AI - Vertica - VerticaとSnowflakeの識別子の違い¶

引用符で囲まれている識別子¶

In Vertica, quoted identifiers stick to the case sensitivity rules, which means that, for example, column names are still case insensitive even when quoted. Thus, identifiers "ABC", "ABc", and "aBc" are synonymous, as are ABC, ABc, and aBc :

Vertica¶

CREATE TABLE test.quotedIdentTable
(
  "col#1" INTEGER
);

SELECT "col#1" FROM test.quotedIdentTable;

SELECT "COL#1" FROM test.quotedIdentTable;

Copy

Snowflakeでは、引用符で囲まれた識別子の大文字と小文字の区別はセッションパラメーター QUOTED_IDENTIFIERS_IGNORE_CASE に依存します。デフォルトでは、引用符で囲まれた識別子の比較で大文字と小文字が区別されます。つまり、上記の例を移行した場合の結果コードは次のようになります。

Snowflake¶

CREATE TABLE test.quotedIdentTable
(
  "col#1" INTEGER
);

SELECT
  "col#1"
FROM
  test.quotedIdentTable;

SELECT
  "COL#1"
FROM
  test.quotedIdentTable;

Copy

セッションパラメーターが TRUE に設定されていない限り、2番目の選択を実行すると失敗します。

SnowConvert AI が引用符付き識別子を移行する方法¶

SnowConvert AI will analyze quoted identifiers to determine if they contain non-alphanumeric characters or are reserved words in Snowflake, if they do SnowConvert AI will leave them as they are, alphanumeric identifiers will be left unquoted:

Vertica¶

CREATE TABLE test.identsTable1
(
  "col#1" INTEGER,
  "col2" INTEGER
);

-- Group is a reserved word
SELECT 
"col#1" AS "group",
"col2" AS "hello"
FROM
test.identsTable1;

Copy

Snowflake¶

CREATE TABLE test.identsTable1
(
  "col#1" INTEGER,
  col2 INTEGER
);

-- Group is a reserved word
SELECT
  "col#1" AS "group",
  col2 AS hello
FROM
  test.identsTable1;

Copy