SnowConvert: Transact DDLs¶
CREATE の単語が先行するすべての DDL ステートメントの翻訳リファレンス。
インデックス¶
Applies to
[x] SQL Server
[x] Azure Synapse Analytics
警告
現在、_ Create Index _ ステートメントは変換されていませんが、解析されています。また、ソースコードにCreate index ステートメントがある場合、_ Assessment Report _ ではこれらも考慮されます。
Create Indexの例
CREATE INDEX my_index_name ON my_table (column1, column2);
注釈
アーキテクチャ上の理由により、Snowflakeはインデックスをサポートしていないため、 SnowConvert はインデックスの作成に関連するすべてのコードを削除します。Snowflakeは自動的にすべてのテーブルにマイクロパーティションを作成し、 DML 操作のパフォーマンスを高速化します。ユーザーはこれらのマイクロパーティションの作成や管理を心配する必要はありません。
通常、これだけで非常に優れたクエリパフォーマンスが得られますが、データクラスタリングキーを作成することで改善する方法もあります。 Snowflakeの公式ページ には、マイクロパーティションとデータクラスタリングに関する詳細情報が掲載されています。
マテリアライズドビュー¶
Applies to
[x] Azure Synapse Analytics
説明¶
Snowconvertでは、マテリアライズドビューはSnowflake動的テーブルに変換されます。動的テーブルを適切に構成するには、2つの重要なパラメーターを定義する必要があります: TARGET_LAG と WAREHOUSE。これらのパラメーターが構成オプションで指定されないままになっている場合、以下の例に示すように、Snowconvertは変換中にデフォルト値として事前に割り当てられた値を使用します。
マテリアライズドビューの詳細情報については、 こちら をクリックしてください。
動的テーブルに必要なパラメーターについては、 こちら をご覧ください。
サンプルソースパターン¶
SqlServer
CREATE MATERIALIZED VIEW sales_total
AS
SELECT SUM(amount) AS total_sales
FROM sales;
Snowflake
CREATE OR REPLACE DYNAMIC TABLE sales_total
--** SSC-FDM-0031 - DYNAMIC TABLE REQUIRED PARAMETERS SET BY DEFAULT **
TARGET_LAG='1 day'
WAREHOUSE=UPDATE_DUMMY_WAREHOUSE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
AS
SELECT SUM(amount) AS total_sales
FROM
sales;
既知の問題¶
現在、既知のエラーは検出されていません。
プロシージャ¶
Applies to
[x] SQL Server
[x] Azure Synapse Analytics
注釈
わかりやすくするため、出力コードの一部を省略しています。
1.CREATE PROCEDURE 翻訳¶
Snowflake CREATE PROCEDURE は SQL 構文で定義されており、その内部ステートメントは JavaScript で定義されています。
ソースコード:
-- Additional Params: -t JavaScript
CREATE PROCEDURE HumanResources.uspGetAllEmployees
@FirstName NVARCHAR(50),
@Age INT
AS
-- TSQL Statements and queries...
GO
翻訳済みコード:
CREATE OR REPLACE PROCEDURE HumanResources.uspGetAllEmployees (FIRSTNAME STRING, AGE INT)
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
// SnowConvert Helpers Code section is omitted.
$$;
パラメーターの DATA TYPE¶
パラメーターのデータ型はSnowflakeの同等のものに翻訳されています。データ型も参照してください。
EXEC ヘルパー¶
SnowFlake 環境でプロシージャのステートメントを実行できるようにするには、これらのステートメントを前処理して、ソース言語に固有のいくつかの変数に実行を反映するように調整する必要があります。
SnowConvert はサポートされているステートメントを自動的に翻訳し、EXEC ヘルパーを使用します。このヘルパーは、ステートメントのネイティブ環境での実行をシミュレートするために、多くの変数へのアクセスと更新機能を提供します。
例えば、移行されたプロシージャの中に、常に追加されるコードブロックがあります。次のセクションで、このコードの基本構造を説明します。私たちは常に、変換と私たちが必要とするヘルパーを合理化するための新しい改善方法を評価し、模索していることを心に留めておいてください。
構造¶
EXEC ヘルパーの基本構造は以下の通りです。
変数宣言セクション: ここでは、プロシージャ内のステートメントの実行に関連する値を格納するさまざまな変数またはオブジェクトを宣言します。これには、ステートメントによって影響を受ける行の数や、結果セット自体などの値が含まれます。
fixBind関数宣言: これは、バインドが日付型である場合にバインドを修正するための補助関数です。
EXEC 関数宣言: これはメインの EXEC ヘルパー関数です。実行するステートメント、バインド配列（基本的に実行によって変更される可能性があり、プロシージャの実行中データの永続性を必要とする変数またはパラメーター）、ERROR_HANDLERS を使用しなければならないかどうかを決定するnoCatchフラグ、ステートメントの実行中に例外が発生したときにカスタムコードを実行するためのcatchFunction関数を受け取ります。EXEC 関数の本文は非常に単純です。ステートメントを実行し、その実行によって生成されたすべての貴重なデータをすべてエラー処理ブロック内に格納します。
ERROR VARS: EXEC catchブロックは、
MESSAGE_TEXT、SQLCODE、SQLSTATE、PROC_NAMEおよびERROR_LINEなどのエラー変数のリストを設定します。これらの変数は、ユーザー定義関数から値を取得するために使用でき、 SQL Server ERROR_LINE、 ERROR_MESSAGE、 ERROR_NUMBER、 ERROR_PROCEDURE および ERROR_STATE 組み込み関数の動作をエミュレートします。これらの変数がすべて1つの値でセットされた後、UPDATE_ERROR_VARSユーザー定義関数は、SQL スコープでアクセスできるように、いくつかの環境変数をエラー値で更新します。
コード¶
次のコードブロックは、プロシージャ内の EXEC ヘルパーを表しています。
var _RS, ROW_COUNT, _ROWS, MESSAGE_TEXT, SQLCODE = 0, SQLSTATE = '00000', ERROR_HANDLERS, NUM_ROWS_AFFECTED, INTO;
var fixBind = function (arg) {
arg = arg == undefined ? null : arg instanceof Date ? arg.toISOString() : arg;
return arg;
};
var fetch = (count,rows,stmt) => (count && rows.next() && Array.apply(null,Array(stmt.getColumnCount())).map((_,i) => rows.getColumnValue(i + 1))) || [];
var EXEC = (stmt,binds = [],noCatch = false) => {
binds = binds ? binds.map(fixBind) : binds;
for(var stmt of stmt.split(";").filter((_) => _)) {
try {
_RS = snowflake.createStatement({
sqlText : stmt,
binds : binds
});
_ROWS = _RS.execute();
ROW_COUNT = _RS.getRowCount();
NUM_ROWS_AFFECTED = _RS.getNumRowsAffected();
return {
THEN : (action) => !SQLCODE && action(fetch(_ROWS))
};
} catch(error) {
let rStack = new RegExp('At .*, line (\\d+) position (\\d+)');
let stackLine = error.stackTraceTxt.match(rStack) || [0,-1];
MESSAGE_TEXT = error.message.toString();
SQLCODE = error.code.toString();
SQLSTATE = error.state.toString();
snowflake.execute({sqlText: `SELECT UPDATE_ERROR_VARS_UDF(?,?,?,?,?)`,binds: [stackLine[1], SQLCODE, SQLSTATE, MESSAGE_TEXT, PROC_NAME]});
throw error;
}
}
};
単純な EXEC の例
これは、ストアドプロシージャ内で EXEC を呼び出す単純な例です
ソースコード
-- Additional Params: -t JavaScript
CREATE PROCEDURE dbo.EXEC_EXAMPLE_1
AS
EXECUTE('SELECT 1 AS Message');
GO
-- =============================================
-- Example to execute the stored procedure
-- =============================================
EXECUTE dbo.EXEC_EXAMPLE_1
GO
期待されるコード
CREATE OR REPLACE PROCEDURE dbo.EXEC_EXAMPLE_1 ()
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
// REGION SnowConvert Helpers Code
var _RS, ROW_COUNT, _ROWS, MESSAGE_TEXT, SQLCODE = 0, SQLSTATE = '00000', OBJECT_SCHEMA_NAME = 'dbo', ERROR_HANDLERS, NUM_ROWS_AFFECTED, PROC_NAME = arguments.callee.name, DOLLAR_DOLLAR = '$' + '$';
function* sqlsplit(sql) {
var part = '';
var ismark = () => sql[i] == '$' && sql[i + 1] == '$';
for(var i = 0;i < sql.length;i++) {
if (sql[i] == ';') {
yield part + sql[i];
part = '';
} else if (ismark()) {
part += sql[i++] + sql[i++];
while ( i < sql.length && !ismark() ) {
part += sql[i++];
}
part += sql[i] + sql[i++];
} else part += sql[i];
}
if (part.trim().length) yield part;
};
var formatDate = (arg) => (new Date(arg - (arg.getTimezoneOffset() * 60000))).toISOString().slice(0,-1);
var fixBind = function (arg) {
arg = arg == undefined ? null : arg instanceof Date ? formatDate(arg) : arg;
return arg;
};
var EXEC = (stmt,binds = [],severity = "16",noCatch = false) => {
binds = binds ? binds.map(fixBind) : binds;
for(var stmt of sqlsplit(stmt)) {
try {
_RS = snowflake.createStatement({
sqlText : stmt,
binds : binds
});
_ROWS = _RS.execute();
ROW_COUNT = _RS.getRowCount();
NUM_ROWS_AFFECTED = _RS.getNumRowsAffected();
return {
THEN : (action) => !SQLCODE && action(fetch(_ROWS))
};
} catch(error) {
let rStack = new RegExp('At .*, line (\\d+) position (\\d+)');
let stackLine = error.stackTraceTxt.match(rStack) || [0,-1];
MESSAGE_TEXT = error.message.toString();
SQLCODE = error.code.toString();
SQLSTATE = error.state.toString();
snowflake.execute({
sqlText : `SELECT UPDATE_ERROR_VARS_UDF(?,?,?,?,?,?)`,
binds : [stackLine[1],SQLCODE,SQLSTATE,MESSAGE_TEXT,PROC_NAME,severity]
});
throw error;
}
}
};
// END REGION
EXEC(`SELECT 1 AS Message;`);
$$;
パラメーターを持つストアドプロシージャ内の EXEC
この例では、EXEC コマンドがストアドプロシージャ内にあり、パラメーター値を受け取ります
ソースコード
-- Additional Params: -t JavaScript
CREATE PROCEDURE dbo.EXEC_EXAMPLE_2
@p1 varchar(50) = N''
AS
EXEC ('SELECT ' + @p1);
GO
-- =============================================
-- Example to execute the stored procedure
-- =============================================
EXECUTE dbo.EXEC_EXAMPLE_2 N'''Hello World!'''
GO
期待されるコード
CREATE OR REPLACE PROCEDURE dbo.EXEC_EXAMPLE_2 (P1 STRING DEFAULT '')
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
// SnowConvert Helpers Code section is omitted.
EXEC(`SELECT
${P1};`);
$$;
パラメーターを持つストアドプロシージャを呼び出す EXEC
この例では、EXEC は別のストアドプロシージャを呼び出し、パスはパラメーターを追加します
ソースコード
-- Additional Params: -t JavaScript
CREATE PROCEDURE dbo.EXEC_EXAMPLE_3
@p1 varchar(50) = N''
AS
EXEC EXEC_EXAMPLE_2 @p1
GO
-- =============================================
-- Example to execute the stored procedure
-- =============================================
EXECUTE dbo.EXEC_EXAMPLE_3 N'''Hello World!'''
GO
期待されるコード
CREATE OR REPLACE PROCEDURE dbo.EXEC_EXAMPLE_3 (P1 STRING DEFAULT '')
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
// SnowConvert Helpers Code section is omitted.
EXEC(`CALL EXEC_EXAMPLE_2(?)`,[P1]);
$$;
デフォルト値のパラメーター。¶
SqlServer では、プロシージャを呼び出すときにパラメーターが指定されない場合に備えて、デフォルト値を持つパラメーターが存在することがあります。
例えば
CREATE PROCEDURE PROC_WITH_DEFAULT_PARAMS1
@PARAM1 INT = 0, @PARAM2 INT = 0, @PARAM3 INT = 0, @PARAM4 INT = 0
AS
BEGIN
.
.
.
END
Snowflakeでは次のように翻訳されます
CREATE OR REPLACE PROCEDURE PROC_WITH_DEFAULT_PARAMS1(param1 int default 0, param2 int default 0, param3 int default 0, param4 int default 0)
RETURNS TABLE()
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
.
.
.
$$;
CALL PROC_WITH_DEFAULT_PARAMS1(param2 => 10, param4 => 15);
CURSOR ヘルパー¶
CREATE OR REPLACE PROCEDURE PROC1()
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
var CURSOR = function (stmt, binds) {
var statementObj, result_set, total_rows, isOpen = false, self = this, row_count;
this.CURRENT = new Object;
var fetch = (count,rows,stmt) => (count && rows.next() && Array.apply(null,Array(stmt.getColumnCount())).map((_,i) => rows.getColumnValue(i + 1))) || [];
var fixBind = function (arg) {
arg = arg == undefined ? null : arg instanceof Date ? formatDate(arg) : arg;
return arg;
};
this.OPEN = function(openParameters) {
if (result_set == undefined) {
try {
if (openParameters) binds = openParameters;
if (binds instanceof Function) binds = binds();
var finalBinds = binds && binds.map(fixBind);
var finalStmt = stmt instanceof Function ? stmt() : stmt;
statementObj = snowflake.createStatement({
sqlText : finalStmt,
binds : finalBinds
});
result_set = statementObj.execute();
total_rows = statementObj.getRowCount();
isOpen = true;
row_count = 0;
} catch(error) {
RAISE(error.code,"error",error.message);
}
else {
isOpen = true;
}
}
return this;
};
this.CURSOR_ROWS = function () {
return total_rows;
};
this.FETCH_STATUS = function() {
if(total_rows > row_count)
return 0;
else
return -1;
};
this.FETCH_NEXT = function() {
self.res = [];
if (isOpen) {
self.res = fetch(total_rows,result_set,statementObj);
if (self.res)
row_count++;
}
return self.res && self.res.length > 0;
};
this.INTO = function () {
return self.res;
};
this.CLOSE = function () {
isOpen = false;
};
this.DEALLOCATE = function() {
this.CURRENT = row_count = result_set_table = total_rows = result_set = statementObj = self = undefined;
};
};
var COL1, COL2;
var sql_stmt = ``;
let c = new CURSOR(`SELECT COL1, COL2 FROM TABLE1;`,() => []);
c.OPEN();
c.FETCH_NEXT();
[COL1, COL2] = c.INTO();
while ( c.FETCH_STATUS()) {
sql_stmt = `INSERT INTO TABLE2 (COL1, COL2) VALUES (` + COL1+ `, ` + COL2 + `)`;
snowflake.createStatement({
sqlText : sql_stmt
}).execute();
}
c.CLOSE();
c.DEALLOCATE();
return 'sucess';
$$;
Insert Into EXEC ヘルパー¶
Insert into Execヘルパーは、Insert insertIntoTemporaryTable(sql) という関数を生成します。この関数は、INSERT INTO TABLE_NAME EXEC(...) を TSQL からSnowflakeへ変換して、データを仮テーブルに挿入し、それを元のInsertに再追加することで、元のステートメントからの動作を模倣することを可能にします。
このステートメントのコードがどのように変更されるかについての情報は、Insert Into Execのセクションを参照してください
注釈
この INSERT INTO EXEC 用に生成されたコードは、内部に複数のクエリを含む EXECUTE ステートメントを処理する際にパフォーマンス上の問題が発生する可能性があります。
function insertIntoTemporaryTable(sql) {
var table = "SnowConvertPivotTemporaryTable";
return EXEC('CREATE OR REPLACE TEMPORARY TABLE ${table} AS ${sql}');
}
insertIntoTemporaryTable(`${DBTABLES}`)
EXEC(`INSERT INTO MYDB.PUBLIC.T_Table SELECT * FROM MYDB.PUBLIC.SnowConvertPivotTemporaryTable`);
EXEC(`DROP TABLE SnowConvertPivotTemporaryTable`)
LIKE ヘルパー¶
Like式がプロシージャ内に見つかった場合、例えば
CREATE PROCEDURE ProcedureLike @VariableValue VARCHAR(50) AS
BEGIN
IF @VariableValue like '%c%'
BEGIN
Select AValue from ATable;
END;
END;
プロシージャの内部はjavascriptに変換されるので、Like式はエラーを投げます。これを避け、機能を維持するために、Like式が見つかった場合、プロシージャの最初に関数が追加されます。
function LIKE(expr,pattern,esc,cs) {
function fixPattern(pattern,esc) {
const specials = '/.*+?|(){}[]\\'.split('');
var newPattern = "";
var fix = (c) => specials.includes(c) ? '\\' + c : c;
for(var i = 0;i < pattern.length;i++) {
var c = pattern[i];
if (c === esc) {
newPattern += pattern[i + 1]
i++
} else if (c === '%') {
newPattern += ".*?"
} else if (c === '_') {
newPattern += "."
} else if (c === '[' || ']') {
newPattern += c
} else newPattern += fix(c)
}
return newPattern;
}
return new RegExp(`^${fixPattern(pattern,esc)}$`,cs ? '' : 'i').exec(expr) != null;
}
この関数を使えば、SQLのLike式の機能を複製することができます。どのように使えるか、様々なケースを見てみましょう
-- Additional Params: -t JavaScript
CREATE PROCEDURE ProcedureLike @VariableValue VARCHAR(50) AS
BEGIN
IF @VariableValue like '%c%'
BEGIN
Select AValue from ATable;
END;
IF @VariableValue not like '%c%'
BEGIN
Select BValue from BTable;
END;
IF @VariableValue like '%c!%%' escape '!'
BEGIN
Select CValue from CTable;
END;
END;
最後のコードには、通常のlikeとnot like、およびエスケープ付きのlikeがあります。変換は以下のようになります
CREATE OR REPLACE PROCEDURE ProcedureLike (VARIABLEVALUE STRING)
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
// SnowConvert Helpers Code section is omitted.
if (LIKE(VARIABLEVALUE,`%c%`)) {
{
EXEC(` Select
AValue
from
ATable`);
}
}
if (!LIKE(VARIABLEVALUE,`%c%`)) {
{
EXEC(` Select
BValue
from
BTable`);
}
}
if (LIKE(VARIABLEVALUE,`%c!%%`,`!`)) {
{
EXEC(` Select
CValue
from
CTable`);
}
}
$$;
Likeは関数呼び出しに変換されることに注意してください
LIKE(VARIABLEVALUE,`%c%`)
!LIKE(VARIABLEVALUE,`%c%`)
LIKE(VARIABLEVALUE,`%c!%%`,`!`)
関数 LIKE が受け取るパラメーターは以下の通りです。
評価される式。
比較のパターン
エスケープ文字がある場合、これはオプションのパラメーターです。
Selectヘルパー¶
スカラー値を変数にセットする必要がある場合、SELECT という関数を生成します
-- Additional Params: -t JavaScript
CREATE PROCEDURE MAX_EMPLOYEE_ID
AS
BEGIN
DECLARE @VARIABLE INT
SET @VARIABLE = (SELECT MAX(EMPLOYEE_ID) FROM EMPLOYEES);
RETURN @VARIABLE
END;
この場合、SELECT ヘルパーを使って以下のコードを生成します
CREATE OR REPLACE PROCEDURE MAX_EMPLOYEE_ID ()
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
// SnowConvert Helpers Code section is omitted.
let VARIABLE;
VARIABLE = SELECT(` MAX(EMPLOYEE_ID) FROM
EMPLOYEES`);
return VARIABLE;
$$;
SELECT ヘルパーは、クエリから取得した値をローカル値に挿入するためにも使えます。このヘルパーは、SQL Server SELECT @local_variable と同じ動作をサポートするように特別に設計されました。args パラメーターは、select内のすべてのローカル変数に適用される各操作を表します。SELECT @Variableも参照してください。例:
-- Additional Params: -t JavaScript
CREATE PROCEDURE [PROCEDURE1] AS
DECLARE @VAR1 int;
DECLARE @VAR2 int;
select @VAR1 = col1 + col2, @VAR2 += col1 from table1;
GO
この場合、変数の割り当ては、SQL Serverの動作をエミュレートするために JavaScript ラムダに翻訳されます。
CREATE OR REPLACE PROCEDURE PROCEDURE1 ()
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
// SnowConvert Helpers Code section is omitted.
let VAR1;
let VAR2;
SELECT(` col1 + col2,
col1
from
table1`,[],(value) => VAR1 = value,(value) => VAR2 += value);
$$;
RAISERROR ヘルパー¶
このヘルパーは、ソースコード中に RAISERROR 呼び出しの使用がある場合に生成されます。例:
var RAISERROR = (message,severity,state) => {
snowflake.execute({
sqlText : `SELECT UPDATE_ERROR_VARS_UDF(?,?,?)`,
binds : [message,severity,state]
});
var msg = `Message: ${message}, Level: ${severity}, State: ${state}`;
throw msg;
};
RAISERROR は、エラーメッセージ、重大度、および状態の値を環境変数として保存するために、UPDATE_ERROR_VARS_UDF を実行します。これは、ERROR 組み込み関数のいずれかを呼び出して使用する必要がある場合に備えます。最後に、SQL Serverと同じ形式でエラーメッセージが投げられます。
アイデンティティ関数ヘルパー¶
このヘルパーは、アイデンティティ関数 がプロシージャ内のSelect Intoで使用されるたびに生成されます。
var IdentityHelper = (seed,increment) => {
var sequenceString = "`CREATE OR REPLACE SEQUENCE SnowConvert_Temp_Seq START = ${seed} INCREMENT = ${increment}`";
return EXEC(sequenceString);
このヘルパーのパラメーターは元の関数と同じで、TSQL のアイデンティティ関数の動作を模倣するシーケンスを生成するために作成されます。元のコードへの変更は次のとおりです。
ソースコードにあるのと同じパラメーターを使用した、IdentityHelper 関数への追加メソッド呼び出し。
そして、シーケンス内の次の値を取得するための関数設計を IDENTITY_UDF に呼び出します。
IdentityHelper(1,1)
EXEC(`CREATE TABLE PUBLIC.department_table3 AS SELECT IDENTITY_UDF() /*** MSC-WARNING - MSCEWI1046 - 'identity' FUNCTION MAPPED TO 'IDENTITY_UDF', FUNCTIONAL EQUIVALENCE VERIFICATION PENDING ***/ as Primary_Rank
from PUBLIC.department_table`);
TSQL のように、パラメーターが与えられていない場合は、(1,1)がデフォルト値になります。
CALL プロシージャヘルパー¶
このヘルパーは、以前はユーザー定義関数であったが、現在は翻訳プロセスの結果としてプロシージャになっているものへの呼び出しがあるたびに生成されます。
var CALL = (sql,binds = [],...args) => {
EXEC("CALL " + sql,binds);
_ROWS.next();
return (_ROWS.getColumnValue(1))[0];
};
このヘルパーの目的は、プロシージャをあたかも関数のように呼び出すために必要なロジックをカプセル化することです。
プロシージャは SELECT のようなクエリの中で呼び出すことができないため、この機能には制限があることに留意してください。
FooSelfAssign(@PAR INT) がプロシージャに翻訳されたと仮定した場合の使用例:
// Input code
DECLARE @VAR1 INT = FooSelfAssign(1);
DECLARE @VAR4 INT = FooSelfAssign(FooSelfAssign(FooSelfAssign(FooSelfAssign(4))));
// Output code
let VAR1 = CALL(`FooSelfAssign(1)`)
let VAR4 = CALL(`FooSelfAssign(?)`,[CALL(`FooSelfAssign(?)`,[CALL(`FooSelfAssign(?)`,[CALL(`FooSelfAssign(4)`)])])]);
VAR1 の翻訳は非常に簡単ですが、VAR4 の場合、CALL の一番外側には、CALLs の残りの部分がバインディングとしてリストに含まれていることに注意してください。
後続の各 CALL は、別の CALL 内に含まれている場合、バインディングに翻訳されます。
2.変数¶
DECLARE @Variable¶
ソースコード
DECLARE @product_list VARCHAR(MAX) = ' ';
DECLARE @Variable1 AS VARCHAR(100), @Variable2 AS VARCHAR(100);
翻訳済みコード
let PRODUCT_LIST = ` `;
let VARIABLE1;
let VARIABLE2;
DECLARE @Variable Table¶
この場合、DECLARE は変数テーブルを宣言するために使用されています。例を見てみましょう。
-- Additional Params: -t JavaScript
CREATE PROCEDURE PROC1
AS
BEGIN
DECLARE @VariableNameTable TABLE
(
[Col1] INT NOT NULL,
[Col2] INT NOT NULL
);
INSERT INTO @VariableNameTable Values(111,222);
Select * from @VariableNameTable;
END
Exec PROC1;
このコードをSQL Serverで実行すると、次のような結果が得られます
では、Snowflakeの変換を見てみましょう
CREATE OR REPLACE PROCEDURE PROC1 ()
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
// SnowConvert Helpers Code section is omitted.
{
EXEC(`CREATE OR REPLACE TEMPORARY TABLE T_VariableNameTable
(
Col1 INT NOT NULL,
Col2 INT NOT NULL
)`);
EXEC(`INSERT INTO T_VariableNameTable Values(111,222)`);
EXEC(`Select
*
from
T_VariableNameTable`);
}
EXEC(`CALL PROC1()`);
$$;
61 行から 67 行までがプロシージャ内のステートメントの結果であることに注意してください。
宣言変数テーブルが仮テーブルになります。名前内の文字@がT_に置き換えられていることに注意してください。
このコードをSnowflakeで実行しても、結果は得られず、nullが表示されるだけです。これは、最後のSelectが EXEC ヘルパー内にあるためです。では、テーブルがそこにあることをどうやって知るのでしょうか？
EXEC のプロシージャ内で一時テーブルとして作成されたため、プロシージャの外部でそのテーブルに対してSelectを実行できます。
Select * from PUBLIC.T_VariableNameTable;
このステートメントを実行すると、次のような結果が得られます
SET @Variable¶
今のところ、セット変数は右辺にある式によって変換されます。
式に変換がある場合は、JavaScript の相当するものに変換されます。
例
-- Additional Params: -t JavaScript
CREATE PROCEDURE PROC1
AS
BEGIN
SET @product_list2 = '';
SET @product_list = '';
SET @var1 += '';
SET @var2 &= '';
SET @var3 ^= '';
SET @var4 |= '';
SET @var5 /= '';
SET @var6 %= '';
SET @var7 *= '';
SET @var8 -= '';
SET @ProviderStatement = 'SELECT * FROM TABLE1
WHERE COL1 = '+@PARAM1+ ' AND COL2 = ' + @LOCALVAR1;
SET @NotSupported = functionValue(a,b,c);
END
翻訳済みコード
CREATE OR REPLACE PROCEDURE PROC1 ()
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
// SnowConvert Helpers Code section is omitted.
PRODUCT_LIST2 = ``;
PRODUCT_LIST = ``;
VAR1 += ``;
VAR2 &= ``;
VAR3 ^= ``;
VAR4 |= ``;
VAR5 /= ``;
VAR6 %= ``;
VAR7 *= ``;
VAR8 -= ``;
PROVIDERSTATEMENT = `SELECT
*
FROM
TABLE1
WHERE
COL1 = ${PARAM1}
AND COL2 = ${LOCALVAR1};`;
NOTSUPPORTED = SELECT(` !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'functionValue' NODE ***/!!!
functionValue(a, b, c)`);
$$;
例でわかるように、変数 NOTSUPPORTED の値は、当分の間変換されないので、コメントされています。これは、まだ変換が完了していないことを意味します。
他の種類のセットについてはコメントされます。例えば次の通りです
-- Additional Params: -t JavaScript
CREATE PROCEDURE PROC1
AS
BEGIN
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
;
SET NOCOUNT ON
;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
;
SET NOCOUNT OFF
;
END
翻訳済みコード
CREATE OR REPLACE PROCEDURE PROC1 ()
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
// SnowConvert Helpers Code section is omitted.
/*** SSC-EWI-0040 - THE STATEMENT IS NOT SUPPORTED IN SNOWFLAKE ***/
/*SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED*/
;
/*** SSC-EWI-0040 - THE STATEMENT IS NOT SUPPORTED IN SNOWFLAKE ***/
/*SET NOCOUNT ON*/
;
/*** SSC-EWI-0040 - THE STATEMENT IS NOT SUPPORTED IN SNOWFLAKE ***/
/*SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED*/
;
/*** SSC-EWI-0040 - THE STATEMENT IS NOT SUPPORTED IN SNOWFLAKE ***/
/*SET NOCOUNT OFF*/
;
$$;
SELECT @Variable¶
今のところ、SELECT @variable は単純なselectに変換され、変数の割り当ては削除され、演算子の右側に式が残ります。select内のローカル変数の割り当て操作は、arrow 関数に置き換えられます。この関数は、SQL Server でローカル変数の割り当て中に行われた操作と同じ動作を表します。
入力¶
-- Additional Params: -t JavaScript
CREATE PROCEDURE PROC1 AS
DECLARE @VAR1 int;
DECLARE @VAR2 int;
SELECT @VAR1 = COL1 + COL2, @VAR2 = COL3 FROM TABLE1;
GO
出力¶
CREATE OR REPLACE PROCEDURE PROC1 ()
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
// SnowConvert Helpers Code section is omitted.
let VAR1;
let VAR2;
SELECT(` COL1 + COL2,
COL3
FROM
TABLE1`,[],(value) => VAR1 = value,(value) => VAR2 = value);
$$;
3.ステートメント翻訳¶
SELECT¶
基本的な形式¶
基本的な SELECT 形式にはバインディングがないため、翻訳では1つのパラメーターを持つ EXEC ヘルパー関数の呼び出しを作成する必要があります。\ \ 例:
-- Source code:
SELECT * FROM DEMO_TABLE_1;
// Translated code:
EXEC(`SELECT * FROM DEMO_TABLE_1`);
IF¶
ソースコード
IF Conditional_Expression
-- SQL Statement
ELSE IF Conditiona_Expression2
-- SQL Statement
ELSE
-- SQL Statement
翻訳済みコード
if (Conditional_Expression) {
// SQL Statement
} else if (Conditional_Expression2) {
// SQL Statement
} else{
// SQL Statement
}
WHILE¶
ソースコード
WHILE ( Conditional_Expression )
BEGIN
-- SQL STATEMENTS
END;
翻訳済みコード
while ( Conditional_Expression )
{
// SQL STATEMENTS
}
EXEC / EXECUTE¶
ソースコード
-- Execute simple statement
Exec('Select 1');
-- Execute statement using Dynamic Sql
Exec('Select ' + @par1 + ' from [db].[t1]');
-- Execute Procedure with parameter
EXEC db.sp2 'Create proc [db].[p3] AS', @par1, 1
翻訳済みコード
-- Execute simple statement
EXEC(`Select 1`);
-- Execute statement using Dynamic Sql
EXEC(`Select ${PAR1} from MYDB.db.t1`);
-- Execute Procedure with parameter
EXEC(`CALL db.sp2(/*** SSC-EWI-0038 - THIS STATEMENT MAY BE A DYNAMIC SQL THAT COULD NOT BE RECOGNIZED AND CONVERTED ***/
'Select * from MYDB.db.t1', ?, 1, Default)`,[PAR1]);
THROW¶
THROW の変換は、エラーを受け取るcatchブロックが元のステートメントで指定された情報にアクセスできるようにします。
例:
-- Case 1
THROW
-- Case 2
THROW 123, 'The error message', 1
-- Case 3
THROW @var1, @var2, @var3
以下に変換されます。
// Case 1
throw {};
// Case 2
throw { code: 123, message: "The error message", status: 1 };
// Case 3
throw { code: VAR1, message: VAR2, status: VAR3 };
RAISERROR¶
SQL Server RAISERROR 関数はSnowflakeではサポートされていません。SnowConvert はすべての使用を識別し、元の動作をエミュレートする ヘルパー を生成します。例:
From¶
-- Additional Params: -t JavaScript
CREATE OR ALTER PROCEDURE RAISERRORTEST AS
BEGIN
DECLARE @MessageTXT VARCHAR = 'ERROR MESSAGE';
RAISERROR (N'E_INVALIDARG', 16, 1);
RAISERROR ('Diagram does not exist or you do not have permission.', 16, 1);
RAISERROR(@MessageTXT, 16, 1);
END
GO
終わり¶
CREATE OR REPLACE PROCEDURE RAISERRORTEST ()
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
// SnowConvert Helpers Code section is omitted.
let MESSAGETXT = `ERROR MESSAGE`;
RAISERROR("E_INVALIDARG","16","1");
RAISERROR("Diagram does not exist or you do not have permission.","16","1");
RAISERROR(MESSAGETXT,"16","1");
$$;
BREAK/CONTINUE¶
break/continue変換は、コードの流れを確実に止めたり、別のブロックに続けたりします。
例:
-- Additional Params: -t JavaScript
CREATE PROCEDURE ProcSample
AS
BEGIN
IF @@ROWCOUNT > 0
Continue;
ELSE
BREAK;
END
以下に変換されます。
CREATE OR REPLACE PROCEDURE ProcSample ()
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
// SnowConvert Helpers Code section is omitted.
if (ROW_COUNT > 0) {
continue;
} else {
break;
}
$$;
INSERT INTO EXEC¶
INSERT INTO [Table] EXEC(...) ステートメントはSnowflakeではサポートされていないため、コードが若干変更されています。これにより、数行のコードを追加することで動作を複製できます。
最初に追加された行は、
insertIntoTemporaryTableへの呼び出しで、EXEC内の引数から抽出されたコードが、結果セットを仮テーブルに挿入します。関数の詳細情報については、Insert Into EXEC ヘルパーセクションをご覧ください。InsertのExecはコードから削除され、EXEC の結果を仮テーブルから取り出すクエリが実行されます。
SELECT * FROM MYDB.PUBLIC.SnowConvertPivotTemporaryTable
最後に追加された行は、追加された仮テーブルの DROP TABLE ステートメントです。
DROP TABLE SnowConvertPivotTemporaryTable
ソースコード:
INSERT INTO #Table1
EXEC ('SELECT
Table1.ID
FROM Population');
INSERT INTO #Table1
EXEC (@DBTables);
翻訳済みコード:
insertIntoTemporaryTable(`SELECT Table1.ID FROM MYDB.PUBLIC.Population)
EXEC(`INSERT INTO MYDB.PUBLIC.T_Table1 SELECT * FROM MYDB.PUBLIC.SnowConvertPivotTemporaryTable`);
EXEC(`DROP TABLE SnowConvertPivotTemporaryTable`)
insertIntoTemporaryTable(`${DBTABLES}`)
EXEC(`INSERT INTO MYDB.PUBLIC.T_Table1 SELECT * FROM MYDB.PUBLIC.SnowConvertPivotTemporaryTable`);
EXEC(`DROP TABLE SnowConvertPivotTemporaryTable`)
BEGIN TRANSACTION¶
BEGIN TRANSACTION はSnowflakeの BEGIN コマンドに変換され、EXEC ヘルパー呼び出しに挿入されます。
ヘルパーは結果の BEGIN を実際に実行する役割を担います。
例:
-- Input code
BEGIN TRAN @transaction_name;
// Output code
EXEC(`BEGIN`, []);
COMMIT TRANSACTION¶
COMMIT TRANSACTION はSnowflakeの COMMIT コマンドに変換され、EXEC ヘルパー呼び出しに挿入されます。
ヘルパーは結果の COMMIT を実際に実行する役割を担います。
例:
-- Input code
COMMIT TRAN @transaction_name;
// Output code
EXEC(`COMMIT`, []);
ROLLBACK TRANSACTION¶
ROLLBACK TRANSACTION はSnowflakeの ROLLBACK コマンドに変換され、EXEC ヘルパー呼び出しに挿入されます。
ヘルパーは結果の ROLLBACK を実際に実行する役割を担います。
例:
-- Input code
ROLLBACK TRAN @transaction_name;
// Output code
EXEC(`ROLLBACK`, []);
WAITFOR DELAY¶
WAITFOR DELAY 句はSnowflakeの SYSTEM$WAIT 関数に変換されます。DELAY の time\to\pass パラメーターは、SYSTEM$WAIT 関数のパラメーターとして使用するために秒数に変換されます。
WAITFOR 句の他のバリアントはSnowflakeではサポートされていないため、対応するメッセージが表示されます。
例:
-- Input code
1) WAITFOR DELAY '02:00';
2) WAITFOR TIME '13:30';
3) WAITFOR (RECEIVE TOP (1)
@dh = conversation_handle,
@mt = message_type_name,
@body = message_body
FROM [eqe]), TIMEOUT 5000;
// Output code
1) EXEC(`SYSTEM$WAIT(120)`,[]);
2) /*** SSC-EWI-0040 - THE STATEMENT IS NOT SUPPORTED IN SNOWFLAKE ***/
/*WAITFOR TIME '13:30'*/
;
3) /*** SSC-EWI-0040 - THE STATEMENT IS NOT SUPPORTED IN SNOWFLAKE ***/
/*WAITFOR (RECEIVE TOP (1)
@dh = conversation_handle,
@mt = message_type_name,
@body = message_body
FROM [eqe]), TIMEOUT 5000*/
;
3.カーソル¶
CURSORS はSnowflakeではサポートされていないため、SnowConvert はその機能を JavaScript ヘルパーにマッピングし、ターゲットプラットフォームでの元の動作をエミュレートします。例:
入力:¶
-- Additional Params: -t JavaScript
CREATE PROCEDURE [procCursorHelper] AS
DECLARE vendor_cursor CURSOR FOR
SELECT VendorID, Name
FROM Purchasing.Vendor
WHERE PreferredVendorStatus = 1
ORDER BY VendorID;
GO
出力:¶
CREATE OR REPLACE PROCEDURE procCursorHelper ()
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
// SnowConvert Helpers Code section is omitted.
var VENDOR_CURSOR = new CURSOR(`SELECT
VendorID,
Name
FROM
Purchasing.Vendor
WHERE
PreferredVendorStatus = 1
ORDER BY VendorID`,[],false);
$$;
DECLARE CURSOR¶
今のところ、カーソル宣言はコメントされているだけです。
ソースコード
DECLARE myCursor1 CURSOR FOR SELECT COL1 FROM TABLE1
翻訳済みコード
let myCursor1 = new CURSOR(`SELECT COL1 FROM TABLE1`,() => []);
OPEN¶
ソースコード
OPEN myCursor1
OPEN GLOBAL myCursor2
翻訳済みコード
myCursor1.OPEN();
myCursor2.OPEN()
FETCH¶
ソースコード
DECLARE @VALUE1 INT
FETCH NEXT FROM myCursor1 into @VALUE1
翻訳済みコード
var VALUE1;
myCursor1.FETCH_NEXT();
VALUE1 = myCursor1.INTO();
CLOSE¶
ソースコード
CLOSE myCursor1
CLOSE GLOBAL myCursor2
翻訳済みコード
myCursor1.CLOSE()
myCursor2.CLOSE()
DEALLOCATE¶
ソースコード
DEALLOCATE myCursor1
DEALLOCATE GLOBAL myCursor2
翻訳済みコード
myCursor1.DEALLOCATE()
myCursor2.DEALLOCATE()
@@FETCH_STATUS¶
ソースコード
@@FETCH_STATUS
翻訳済みコード
myCursor1.FETCH_STATUS()
@@CURSOR_ROWS¶
ソースコード
@@CURSOR_ROWS
翻訳済みコード
myCursor1.FETCH_STATUS()
4.式¶
バイナリ操作¶
ソースコード
SET @var1 = 1 + 1;
SET @var1 = 1 - 1;
SET @var1 = 1 / 1;
SET @var1 = 1 * 1;
SET @var1 = 1 OR 1;
SET @var1 = 1 AND 1;
翻訳済みコード
VAR1 = 1 + 1;
VAR1 = 1 - 1;
VAR1 = 1 / 1;
VAR1 = 1 * 1;
VAR1 = 1 || 1;
VAR1 = 1 && 1;
条件¶
ソースコード
@var1 > 0
@var1 = 0
@var1 < 0
@var1 <> 0
翻訳済みコード
VAR1 > 0
VAR1 = 0
VAR1 < 0
VAR1 != 0
IN 述語¶
NULL 述語¶
ソースコード
@var1 is null
@var2 is not null
翻訳済みコード
VAR1 == null
VAR2 != null
5.ラベルとGoto¶
ラベル は JavaScript では SQL Serverと同じ動作はしません。動作をシミュレートするために、これらは 関数 に変換されています。その使用は、ラベルのすべてのロジックを含む生成関数の呼び出しに置き換えられています。例:
ソースコード¶
-- Additional Params: -t JavaScript
CREATE PROCEDURE [procWithLabels]
AS
SUCCESS_EXIT:
SET @ErrorStatus = 0
RETURN @ErrorStatus
ERROR_EXIT:
RETURN @ErrorStatus
翻訳済みコード¶
CREATE OR REPLACE PROCEDURE procWithLabels ()
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
// SnowConvert Helpers Code section is omitted.
SUCCESS_EXIT();
ERROR_EXIT();
function SUCCESS_EXIT() {
ERRORSTATUS = 0;
return ERRORSTATUS;
}
function ERROR_EXIT() {
return ERRORSTATUS;
}
$$;
上の例にあるように、ソースコードのラベルであった関数宣言は、コードをすっきりさせるためにコードの末尾に置かれます。
GOTO も JavaScript には存在しないコマンドです。その動作をシミュレートするために、それらの使用は、returnステートメントに先行して、参照されている関数（ラベル）への呼び出しに変換されています。例:
ソースコード¶
-- Additional Params: -t JavaScript
CREATE PROCEDURE [procWithLabels]
AS
DECLARE @ErrorStatus int = 0;
IF @ErrorStatus <> 0 GOTO ERROR_EXIT
SUCCESS_EXIT:
SET @ErrorStatus = 0
RETURN @ErrorStatus
ERROR_EXIT:
RETURN @ErrorStatus
翻訳済みコード¶
CREATE OR REPLACE PROCEDURE procWithLabels ()
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
// SnowConvert Helpers Code section is omitted.
let ERRORSTATUS = 0;
if (ERRORSTATUS != 0) {
return ERROR_EXIT();
}
SUCCESS_EXIT();
ERROR_EXIT();
function SUCCESS_EXIT() {
ERRORSTATUS = 0;
return ERRORSTATUS;
}
function ERROR_EXIT() {
return ERRORSTATUS;
}
$$;
上の例でわかるように、return が関数呼び出しに追加されています。これは、SQL Serverが GOTO で行っているように、コードの流れを止めるためです。
既知の問題¶
問題は見つかりませんでした。
関連 EWIs¶
SSC-EWI-0040: ステートメントがサポートされていません。
SSC-EWI-0073: 機能同等性レビュー保留中。
テーブル¶
Applies to
[x] SQL Server
[x] Azure Synapse Analytics
注釈
わかりやすくするため、出力コードの一部を省略しています。
基本的なテーブルの作成¶
ソース
CREATE TABLE [MYSCHEMA].[MYTABLE]
(
[COL1] INT IDENTITY (1,1) NOT NULL,
[COL2] INT,
[COL2 COL3 COL4] VARCHAR,
[COL VARCHAR_SPANISH] [VARCHAR](20) COLLATE Modern_Spanish_CI_AI DEFAULT 'HOLA',
[COL VARCHAR_LATIN] [VARCHAR](20) COLLATE Latin1_General_CI_AI DEFAULT 'HELLO'
);
期待される
CREATE OR REPLACE SEQUENCE MYSCHEMA.MYTABLE_COL1
START WITH 1
INCREMENT BY 1
COMMENT = 'FOR TABLE-COLUMN MYSCHEMA.MYTABLE.COL1';
CREATE OR REPLACE TABLE MYSCHEMA.MYTABLE (
COL1 INT DEFAULT MYSCHEMA.MYTABLE_COL1.NEXTVAL NOT NULL,
COL2 INT,
"COL2 COL3 COL4" VARCHAR,
"COL VARCHAR_SPANISH" VARCHAR(20) COLLATE 'ES-CI-AI' /*** SSC-PRF-0002 - CASE INSENSITIVE COLUMNS CAN DECREASE THE PERFORMANCE OF QUERIES ***/ DEFAULT 'HOLA',
"COL VARCHAR_LATIN" VARCHAR(20) COLLATE 'EN-CI-AI' /*** SSC-PRF-0002 - CASE INSENSITIVE COLUMNS CAN DECREASE THE PERFORMANCE OF QUERIES ***/ DEFAULT 'HELLO'
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
;
仮テーブル¶
ソースコードの中には、文字#で始まるテーブル名があります。
CREATE TABLE #MyLocalTempTable (
COL1 INT,
COL2 INT
);
その場合は、出力コードで仮テーブルに変換されます。
上のコードをどのように移行するか見てみましょう。
CREATE OR REPLACE TEMPORARY TABLE T_MyLocalTempTable (
COL1 INT,
COL2 INT
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
;
ご覧のように、テーブルの定義に TEMPORARY が追加され、文字 # が T_ に置き換えられました。
また、テーブルのすべての参照も、仮テーブルに与えられた新しい名前に合わせて変換されます。
NULL および NOT NULL 列オプション¶
NULL および NOT NULL 列オプションがSnowflakeでサポートされています。
ソース
CREATE TABLE [SCHEMA1].[TABLE1](
[COL1] [varchar](20) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [SCHEMA1].[TABLE2](
[COL1] [varchar](20) NULL
) ON [PRIMARY]
GO
期待される
CREATE OR REPLACE TABLE SCHEMA1.TABLE1 (
COL1 VARCHAR(20) NOT NULL
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
;
CREATE OR REPLACE TABLE SCHEMA1.TABLE2 (
COL1 VARCHAR(20) NULL
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
;
アイデンティティ列オプション¶
アイデンティティ列の場合、シーケンスが作成され、列に割り当てられます。
ソース
CREATE TABLE acct3.UnidentifiedCash3 (
UnidentifiedCash_ID3 INT IDENTITY (666, 313) NOT NULL
);
期待される
CREATE OR REPLACE SEQUENCE acct3.UnidentifiedCash3_UnidentifiedCash_ID3
START WITH 666
INCREMENT BY 313
COMMENT = 'FOR TABLE-COLUMN acct3.UnidentifiedCash3.UnidentifiedCash_ID3';
CREATE OR REPLACE TABLE acct3.UnidentifiedCash3 (
UnidentifiedCash_ID3 INT DEFAULT acct3.UnidentifiedCash3_UnidentifiedCash_ID3.NEXTVAL NOT NULL
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
;
デフォルト列オプション¶
デフォルトのExprはSnowflakeでサポートされていますが、SQL Serverでは制約名と一緒に使用することができます。この部分はSnowflakeではサポートされていないため、削除され、警告が追加されました。
ソース
CREATE TABLE [SCHEMA1].[TABLE1] (
[COL1] VARCHAR (10) CONSTRAINT [constraintName] DEFAULT ('0') NOT NULL
);
期待される
CREATE OR REPLACE TABLE SCHEMA1.TABLE1 (
COL1 VARCHAR(10)
--** SSC-FDM-0012 - CONSTRAINT IN DEFAULT EXPRESSION IS NOT SUPPORTED IN SNOWFLAKE **
DEFAULT ('0') NOT NULL
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
;
列の制約¶
ソース
CREATE TABLE [SalesLT].[Address](
[AddressID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[AddressLine1] [nvarchar](60) NOT NULL,
[AddressLine2] [nvarchar](60) NULL,
[City] [nvarchar](30) NOT NULL,
[StateProvince] [dbo].[Name] NOT NULL,
[CountryRegion] [dbo].[Name] NOT NULL,
[PostalCode] [nvarchar](15) NOT NULL,
[rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL,
[ModifiedDate] [datetime] NOT NULL,
CONSTRAINT [PK_Address_AddressID] PRIMARY KEY CLUSTERED
(
[AddressID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY],
CONSTRAINT [AK_Address_rowguid] UNIQUE NONCLUSTERED
(
[rowguid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
期待される
CREATE OR REPLACE SEQUENCE SalesLT.Address_AddressID
START WITH 1
INCREMENT BY 1
COMMENT = 'FOR TABLE-COLUMN SalesLT.Address.AddressID';
--** SSC-FDM-0007 - MISSING DEPENDENT OBJECT "[dbo].[Name]" **
CREATE OR REPLACE TABLE SalesLT.Address (
AddressID INT DEFAULT SalesLT.Address_AddressID.NEXTVAL NOT NULL,
AddressLine1 VARCHAR(60) NOT NULL,
AddressLine2 VARCHAR(60) NULL,
City VARCHAR(30) NOT NULL,
StateProvince VARIANT /*** SSC-FDM-TS0015 - DATA TYPE DBO.NAME IS NOT SUPPORTED IN SNOWFLAKE ***/ NOT NULL,
CountryRegion VARIANT /*** SSC-FDM-TS0015 - DATA TYPE DBO.NAME IS NOT SUPPORTED IN SNOWFLAKE ***/ NOT NULL,
PostalCode VARCHAR(15) NOT NULL,
rowguid VARCHAR
!!!RESOLVE EWI!!! /*** SSC-EWI-0040 - THE STATEMENT IS NOT SUPPORTED IN SNOWFLAKE ***/!!!
ROWGUIDCOL NOT NULL,
ModifiedDate TIMESTAMP_NTZ(3) NOT NULL,
CONSTRAINT PK_Address_AddressID PRIMARY KEY (AddressID),
CONSTRAINT AK_Address_rowguid UNIQUE (rowguid)
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "transact", "convertedOn": "09/23/2024" }}'
;
照合列オプション¶
照合変換については、以下のリンクをご覧ください。
ENCRYPTED WITH 列オプション¶
Encrypted WithはSnowflakeではサポートされていないため、削除され、警告が追加されました。
ソース
CREATE TABLE [SCHEMA1].[TABLE1] (
[COL1] NVARCHAR(60) ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = MyCEK, ENCRYPTION_TYPE = RANDOMIZED, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256')
);
期待される
CREATE OR REPLACE TABLE SCHEMA1.TABLE1 (
COL1 VARCHAR(60)
-- --** SSC-FDM-TS0009 - ENCRYPTED WITH NOT SUPPORTED IN SNOWFLAKE **
-- ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = MyCEK, ENCRYPTION_TYPE = RANDOMIZED, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256')
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
;
NOT FOR REPLICATION¶
Snowflakeでは、NOT FOR REPLICATION オプションはサポートされていません。SEQUENCE に移行されるアイデンティティに使用されます。
警告
NOT FOR REPLICATION は等価物に翻訳されるため、Snowflakeでは必要ないステートメントで、削除されていることに注意してください。
ソース
CREATE TABLE [TABLE1] (
[COL1] INT IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL
) ON [PRIMARY];
出力
CREATE OR REPLACE SEQUENCE TABLE1_COL1
START WITH 1
INCREMENT BY 1
COMMENT = 'FOR TABLE-COLUMN TABLE1.COL1';
CREATE OR REPLACE TABLE TABLE1 (
COL1 INT DEFAULT TABLE1_COL1.NEXTVAL NOT NULL
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
;
On Primary¶
ON PRIMARY オプションは、SQL Serverで、テーブルなどのオブジェクトをどのファイルに作成するかを定義するために使用するステートメントです。データベース内のプライマリまたはセカンダリファイルグループなど。Snowflakeは異なるロジックを提供し、明確な制約を示します。詳細については、以下の Snowflakeドキュメント を参照してください。
ソース¶
CREATE TABLE [TABLE1](
[COL1] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [pk_dimAddress_AddressId] PRIMARY KEY CLUSTERED ([COL1])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
出力¶
CREATE OR REPLACE TABLE TABLE1 (
COL1 VARCHAR(255) COLLATE 'EN-CI-AS' /*** SSC-PRF-0002 - CASE INSENSITIVE COLUMNS CAN DECREASE THE PERFORMANCE OF QUERIES ***/ /*** SSC-FDM-TS0002 - COLLATION FOR VALUE CP1 NOT SUPPORTED ***/ NOT NULL
CONSTRAINT pk_dimAddress_AddressId PRIMARY KEY (COL1)
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "transact", "convertedOn": "09/23/2024" }}'
;
ASC/DESC 列の並び替え¶
Snowflakeでは列の並び替えはサポートされていません。ASC または DESC キーワードは削除されました。
ソース¶
CREATE TABLE [TABLE1](
[COL1] [int] NOT NULL,
CONSTRAINT [constraint1] PRIMARY KEY CLUSTERED ([COL1] ASC)
) ON [PRIMARY]
出力¶
CREATE OR REPLACE TABLE TABLE1 (
COL1 INT NOT NULL,
CONSTRAINT constraint1 PRIMARY KEY (COL1)
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "transact", "convertedOn": "09/23/2024" }}'
;
計算列¶
計算列はSnowflakeでサポートされており、例えばテーブルをデプロイできるようにするために明示的なデータ型を追加する必要があるだけです。
ソース¶
CREATE TABLE [TABLE1](
[COL2] [int] NOT NULL,
[COL2] [int] NOT NULL,
[COL1] AS (COL3 * COL2),
)
出力¶
CREATE OR REPLACE TABLE TABLE1 (
COL2 INT NOT NULL,
COL2 INT NOT NULL,
COL1 VARIANT AS (COL3 * COL2) /*** SSC-FDM-TS0014 - COMPUTED COLUMN WAS TRANSFORMED TO ITS SNOWFLAKE EQUIVALENT, FUNCTIONAL EQUIVALENCE VERIFICATION PENDING. ***/
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
;
計算式が変換できない場合は警告が追加され、次の例のように式の戻り値の型を持つ単純な列定義が代わりに使用されます。
ソース¶
CREATE TABLE [TABLE1](
[Col1] AS (CONVERT ([XML], ExpressionValue))
)
CONVERT ([NUMERIC], ExpressionValue) という式は、SnowConvert ではまだサポートされていないため、検査後、SnowConvert はその型が XML であると判断するので、変換は次のようになります。
CREATE OR REPLACE TABLE TABLE1 (
Col1 TEXT AS (CAST(ExpressionValue AS VARIANT !!!RESOLVE EWI!!! /*** SSC-EWI-0036 - XML DATA TYPE CONVERTED TO VARIANT ***/!!!)) /*** SSC-FDM-TS0014 - COMPUTED COLUMN WAS TRANSFORMED TO ITS SNOWFLAKE EQUIVALENT, FUNCTIONAL EQUIVALENCE VERIFICATION PENDING. ***/
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
;
SnowConvert は、SQL Serverで元の式の型を決定する処理を実行します。しかし、列は同等のターゲット型を持ちます。前の例では、SQLServer の列型は XML でしたが、XML を格納するSnowflakeのターゲット型は TEXT です。データ型のマッピングに関する詳しい情報は、データ型のセクションを参照してください。
MASKED WITH 列オプション¶
SQL Serverでは、データマスキングを使用して、機密情報を権限のないユーザーから保護しています。詳しくは、SQL SERVER ドキュメント をご覧ください。Snowflakeには動的データマスキング機能がありますが、Enterprise Editionでのみ利用可能です。以下の Snowflakeドキュメント をご覧ください。
入力¶
CREATE TABLE TABLE1
(
[COL1] [nvarchar](50) MASKED WITH (FUNCTION = 'default()') NULL
);
出力¶
CREATE OR REPLACE TABLE TABLE1
(
COL1 VARCHAR(50)
!!!RESOLVE EWI!!! /*** SSC-EWI-TS0017 - COLUMN MASKING NOT SUPPORTED IN CREATE TABLE ***/!!!
MASKED WITH (FUNCTION = 'default()') NULL
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
;
ROWGUIDCOL 列オプション¶
ROWGUIDCOL はSnowflakeでは適用されなくなりました。これは、SQL Serverで、現在 VARCHAR に翻訳されている UNIQUEIDENTIFIER 型に使用されます。例:
入力¶
CREATE TABLE TABLEROWID (
[ROWGUID] UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL
) ON [PRIMARY];
出力¶
CREATE OR REPLACE TABLE TABLEROWID (
ROWGUID VARCHAR
!!!RESOLVE EWI!!! /*** SSC-EWI-0040 - THE STATEMENT IS NOT SUPPORTED IN SNOWFLAKE ***/!!!
ROWGUIDCOL NOT NULL
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
;
GENERATED ALWAYS AS ROW START/END 列オプション¶
ROW START/END はSnowflakeではサポートされていません。SnowConvert がこの種の列オプションを変換しようとするとエラーが追加されます。
入力¶
CREATE TABLE TABLEROWID (
[COL1] DATETIME GENERATED ALWAYS AS ROW START NOT NULL
) ON [PRIMARY];
出力¶
CREATE OR REPLACE TABLE TABLEROWID (
COL1 TIMESTAMP_NTZ(3) GENERATED ALWAYS AS ROW START !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'GeneratedClause' NODE ***/!!! NOT NULL
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
;
既知の問題¶
問題は見つかりませんでした。
関連 EWIs¶
SSC-EWI-0036: データ型が別のデータ型に変換されました。
SSC-EWI-0040: ステートメントがサポートされていません。
SSC-EWI-0073: 機能同等性レビュー保留中。
SSC-EWI-TS0017: マスキングはサポートされていません。
SSC-FDM-0012: デフォルト式の制約はサポートされていません。
SSC-FDM-TS0002: このメッセージは、SnowflakeでサポートされていないCollate句がある場合に表示されます。
SSC-FDM-TS0009: Encrypted withはSnowflakeではサポートされていません。
SSC-FDM-TS0014: 計算された列が変換されました。
SSC-FDM-TS0015: データ型はSnowflakeではサポートされていません。
SSC-PRF-0002: 大文字と小文字を区別しない列はクエリのパフォーマンスを低下させる可能性があります。
ビュー¶
Applies to
[x] SQL Server
[x] Azure Synapse Analytics
注釈
わかりやすくするため、出力コードの一部を省略しています。
このセクションでは、create viewの変換を確認します。
サンプルソースパターン¶
SIMPLE CREATE VIEW¶
次の例は、単純な CREATE VIEW ステートメントに対する変換を示しています。
CREATE VIEW VIEWNAME
AS
SELECT AValue from ATable;
期待される
CREATE OR REPLACE VIEW VIEWNAME
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
AS
SELECT
AValue
from
ATable;
CREATE OR ALTER VIEW¶
SqlServer で使用された CREATE OR ALTER 定義は、Snowflakeでは CREATE OR REPLACE に変換されます。
CREATE OR ALTER VIEW VIEWNAME
AS
SELECT AValue from ATable;
CREATE OR REPLACE VIEW VIEWNAME
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
AS
SELECT
AValue
from
ATable;
CREATE VIEW WITH¶
このタイプのビューでは、ビュー名の後に以下の句が来ることがあります
WITH ENCRYPTIONWITH SCHEMABINDINGWITH VIEW_METADATA
警告
上記の句は、Snowflake構文では関係ないため、翻訳から削除されていることに注意してください。
CREATE OR ALTER VIEW VIEWNAME
WITH ENCRYPTION
AS
SELECT AValue from ATable;
CREATE OR REPLACE VIEW VIEWNAME
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
AS
SELECT
AValue
from
ATable;
CREATE VIEW AS SELECT WITH CHECK OPTION¶
このタイプのビューでは、 WITH CHECK OPTION 句は、Create Viewで使用されるSelectステートメントの終了後に続きます。
警告
Snowflake構文では関係ないため、WITH CHECK OPTION が翻訳から削除されていることに注意してください。
CREATE OR ALTER VIEW VIEWNAME
AS
SELECT AValue from ATable
WITH CHECK OPTION;
期待される
CREATE OR REPLACE VIEW VIEWNAME
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
AS
SELECT
AValue
from
ATable;
CREATE VIEW AS COMMON TABLE EXPRESSION¶
データを取得するには、共通テーブル式を使用する必要があります。
CREATE VIEW EMPLOYEEIDVIEW
AS
WITH CTE AS ( SELECT NationalIDNumber from [HumanResources].[Employee]
UNION ALL
SELECT BusinessEntityID FROM [HumanResources].[EmployeeDepartmentHistory] )
SELECT * FROM MyCTE;
CREATE OR REPLACE VIEW EMPLOYEEIDVIEW
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
AS
--** SSC-PRF-TS0001 - PERFORMANCE WARNING - RECURSION FOR CTE NOT CHECKED. MIGHT REQUIRE RECURSIVE KEYWORD **
WITH CTE AS ( SELECT
NationalIDNumber
from
HumanResources.Employee
UNION ALL
SELECT
BusinessEntityID
FROM
HumanResources.EmployeeDepartmentHistory
)
SELECT
*
FROM
MyCTE;
UNSUPPORTED SCENARIOS¶
Update、Insert、またはDeleteステートメントを含む共通テーブル式は、Snowflakeおよび SQLServer ではサポートされていないため、コメントアウトされます。
無効な CTE がビューに追加された場合は、完全にコメントアウトされます。
--!!!RESOLVE EWI!!! /*** SSC-EWI-0021 - COMMON TABLE EXPRESSION IN VIEW NOT SUPPORTED ***/!!!
--CREATE OR REPLACE VIEW PUBLIC.EmployeeInsertVew
--AS
--WITH MyCTE AS ( SELECT
--NationalIDNumber
--from
--HumanResources.Employee
--UNION ALL
--SELECT
--BusinessEntityID
--FROM
--HumanResources.EmployeeDepartmentHistory
--)
--INSERT INTO PUBLIC.Dummy
FINAL SAMPLE¶
最後のサンプルとして、これまで見てきたケースをまとめて、どのように変換されるかを見てみましょう
CREATE OR ALTER VIEW VIEWNAME
WITH ENCRYPTION
AS
Select AValue from ATable
WITH CHECK OPTION;
期待される
CREATE OR REPLACE VIEW VIEWNAME
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
AS
Select
AValue
from
ATable;
ご覧のように、 OR ALTER を OR REPLACE に変更し、ビュー名の後にある句 WITH ENCRYPTION とSelectの後にある句 WITH CHECK OPTION を削除しました。
関連 EWIs¶
SSC-PRF-TS0001: パフォーマンスの警告 - CTE の再帰がチェックされていません。再帰キーワードが必要になるかもしれません。
Azure Synapse Analytics¶
Applies to
[x] Azure Synapse Analytics
説明¶
このセクションでは、Azure Synapse Analytics Tables 固有の構文の翻訳を示します。
注釈
ほとんどのパターンでは、ほぼ同じ構文と動作を共有しているため、一般的な テーブルの翻訳仕様を確認してください。
テーブルを作成¶
CREATE TABLE { database_name.schema_name.table_name | schema_name.table_name | table_name }
(
{ column_name <data_type> [ <column_options> ] } [ ,...n ]
)
[ WITH ( <table_option> [ ,...n ] ) ]
[;]
としてテーブルを作成¶
CREATE TABLE { database_name.schema_name.table_name | schema_name.table_name | table_name }
[ ( column_name [ ,...n ] ) ]
WITH (
<distribution_option> -- required
[ , <table_option> [ ,...n ] ]
)
AS <select_statement>
OPTION <query_hint>
[;]
サンプルソースパターン¶
WITH テーブルオプション¶
Azure Synapse Analyticsでは、テーブルオプションを定義するための構文が追加されています。
<table_option> ::=
{
CLUSTERED COLUMNSTORE INDEX -- default for Azure Synapse Analytics
| CLUSTERED COLUMNSTORE INDEX ORDER (column [,...n])
| HEAP --default for Parallel Data Warehouse
| CLUSTERED INDEX ( { index_column_name [ ASC | DESC ] } [ ,...n ] ) -- default is ASC
}
{
DISTRIBUTION = HASH ( distribution_column_name )
| DISTRIBUTION = HASH ( [distribution_column_name [, ...n]] )
| DISTRIBUTION = ROUND_ROBIN -- default for Azure Synapse Analytics
| DISTRIBUTION = REPLICATE -- default for Parallel Data Warehouse
}
| PARTITION ( partition_column_name RANGE [ LEFT | RIGHT ] -- default is LEFT
FOR VALUES ( [ boundary_value [,...n] ] ) )
Snowflakeは、マイクロパーティショニングなどのメカニズムを通じて、テーブルの最適化を自動的に処理します。このため、Snowflakeにはこれらのテーブルオプションに相当する構文が存在しません。したがって、Transactのテーブルオプションのいくつかを定義する必要はありません。
省略されるテーブルオプション:
CLUSTERED COLUMNSTORE INDEX （列なし）
HEAP
DISTRIBUTION
PARTITION
CLUSTERED [ COLUMNSTORE ] INDEX の列が、Snowflakeの CLUSTER BY に変換されます。CLUSTER KEY を定義する必要があるかどうかを確認することをお勧めしますので、パフォーマンスレビュー PRF を追加します。
Transact¶
CREATE TABLE my_table (
enterprise_cif INT,
name NVARCHAR(100),
address NVARCHAR(255),
created_at DATETIME
)
WITH (
DISTRIBUTION = HASH(enterprise_cif),
CLUSTERED INDEX (enterprise_cif)
);
Snowflake¶
CREATE OR REPLACE TABLE my_table (
enterprise_cif INT,
name VARCHAR(100),
address VARCHAR(255),
created_at TIMESTAMP_NTZ(3)
)
--** SSC-PRF-0007 - PERFORMANCE REVIEW - CLUSTER BY **
CLUSTER BY (enterprise_cif)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "transact", "convertedOn": "10/09/2024" }}'
;
既知の問題¶
問題は見つかりませんでした。
関連 EWIs¶
SSC-PRF-0007: PERFORMANCE REVIEW - CLUSTER BY.
TEXTIMAGE_ON¶
Applies to
[x] SQL Server
注釈
関連性のないステートメント。
警告
このステートメントは 関連性のない構文であるため、 移行から削除されていることに注意してください。 Snowflakeでは必要ないということです。
説明¶
TEXTIMAGE_ON [PRIMARY] は、テーブル内の大容量の情報グループを扱うTransactの方法です。Snowflakeでは、大容量のデータファイルや情報を別の配置で扱うため、このような特性を定義する必要はありません。
サンプルソースパターン¶
この例では、TEXTIMAGE_ON [PRIMARY] は不要な構文のため削除されていることに注意してください。
CREATE TABLE [dbo].[TEST_Person](
[date_updated] [datetime] NULL
) TEXTIMAGE_ON [PRIMARY]
CREATE OR REPLACE TABLE dbo.TEST_Person (
date_updated TIMESTAMP_NTZ(3) NULL
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
;
既知の問題¶
問題は見つかりませんでした。
関連 EWIs ¶
関連 EWIs はありません。
FUNCTION¶
Applies to
[x] SQL Server
[x] Azure Synapse Analytics
説明¶
SQL Serverは、2種類の ユーザー定義関数 のみをサポートしています。
これらの UDFs 型を使用すると、内部ロジックに従って 単純なものと複雑なもの にサブカテゴリー化することが可能です。
単純な UDFs、SQL Serverの構文とSnowflakeの構文が一致します。この型はロジックを追加せず、結果をストレートに求めます。これらは通常、Snowflakeの SQL UDFs と一致します。\ \ 複雑 UDFs は、特定のステートメント（INSERT、DELETE、UPDATE、SET、DECLARE など）またはフロー制御 ブロック（IF...ELSE、WHILE など）を多用しており、通常、Snowflakeの SQL UDFs 定義との不一致または違反を表します。
制限事項¶
Transact UDFs は、他のデータベースエンジン（OracleやTeradataなど）にはない制限があります。これらの制限は、失敗の範囲を狭めることによって翻訳に役立ちます。つまり、避けるべきシナリオがあるということです。
以下に、SQL Serverが UDFs に対して持つ制限の一部を示します
UDFs はデータベースの状態を変更するアクションの実行には使用できません
ユーザー定義関数に、テーブルをターゲットとする OUTPUT INTO 句を含めることはできません
ユーザー定義関数は複数の結果セットを返すことはできません。複数の結果セットを返す必要がある場合は、ストアドプロシージャを使用します。
全リストはこちらのリンク ユーザー定義関数の作成（データベースエンジン）をご覧ください
INLINE TABLE-VALUED¶
Applies to
[x] SQL Server
[x] Azure Synapse Analytics
説明¶
注釈
わかりやすくするため、出力コードの一部を省略しています。
Inline Table-Valued関数は、パラメーターを受け取り、SELECT ステートメントを実行し、TABLE (SQL Server言語リファレンスInline Table-Valued関数の作成) を返すことができるテーブル式です。
Transact構文¶
-- Transact-SQL Inline Table-Valued Function Syntax
CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type
[ = default ] [ READONLY ] }
[ ,...n ]
]
)
RETURNS TABLE
[ WITH <function_option> [ ,...n ] ]
[ AS ]
RETURN [ ( ] select_stmt [ ) ]
[ ; ]
Snowflake SQL 構文¶
CREATE OR REPLACE FUNCTION <name> ( [ <arguments> ] )
RETURNS TABLE ( <output_col_name> <output_col_type> [, <output_col_name> <output_col_type> ... ] )
AS '<sql_expression>'sql
サンプルソースパターン¶
次のセクションでは、この種の CREATE FUNCTION 構文で出現し得るすべてのソースコードパターンについて説明します。
Inline Table-Valued関数の場合、本文ごとに次のステートメントが1つだけ存在できます。
SELECTステートメントWITH共通テーブル式
1つのテーブルから直接値を選択して返す¶
これは最も単純なシナリオで、テーブルから単純なselectを実行し、その値を返します
Transact-SQL¶
CREATE FUNCTION GetDepartmentInfo()
RETURNS TABLE
AS
RETURN
(
SELECT DepartmentID, Name, GroupName
FROM HumanResources.Department
);
GO
SELECT * from GetDepartmentInfo()
DepartmentID|Name |GroupName |
------------+--------------------------+------------------------------------+
1|Engineering |Research and Development |
2|Tool Design |Research and Development |
3|Sales |Sales and Marketing |
4|Marketing |Sales and Marketing |
5|Purchasing |Inventory Management |
6|Research and Development |Research and Development |
7|Production |Manufacturing |
8|Production Control |Manufacturing |
9|Human Resources |Executive General and Administration|
10|Finance |Executive General and Administration|
11|Information Services |Executive General and Administration|
12|Document Control |Quality Assurance |
13|Quality Assurance |Quality Assurance |
14|Facilities and Maintenance|Executive General and Administration|
15|Shipping and Receiving |Inventory Management |
16|Executive |Executive General and Administration|
Snowflake SQL¶
CREATE OR REPLACE FUNCTION GetDepartmentInfo ()
RETURNS TABLE(
DepartmentID STRING /*** SSC-FDM-TS0012 - INFORMATION FOR THE COLUMN DepartmentID WAS NOT FOUND. STRING DATATYPE USED TO MATCH CAST AS STRING OPERATION ***/,
Name STRING /*** SSC-FDM-TS0012 - INFORMATION FOR THE COLUMN Name WAS NOT FOUND. STRING DATATYPE USED TO MATCH CAST AS STRING OPERATION ***/,
GroupName STRING /*** SSC-FDM-TS0012 - INFORMATION FOR THE COLUMN GroupName WAS NOT FOUND. STRING DATATYPE USED TO MATCH CAST AS STRING OPERATION ***/
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
AS
$$
SELECT
CAST(DepartmentID AS STRING),
CAST(Name AS STRING),
CAST(GroupName AS STRING)
FROM
HumanResources.Department
$$;
SELECT
*
from
TABLE(GetDepartmentInfo());
DepartmentID|Name |GroupName |
------------+--------------------------+------------------------------------+
1|Engineering |Research and Development |
2|Tool Design |Research and Development |
3|Sales |Sales and Marketing |
4|Marketing |Sales and Marketing |
5|Purchasing |Inventory Management |
6|Research and Development |Research and Development |
7|Production |Manufacturing |
8|Production Control |Manufacturing |
9|Human Resources |Executive General and Administration|
10|Finance |Executive General and Administration|
11|Information Services |Executive General and Administration|
12|Document Control |Quality Assurance |
13|Quality Assurance |Quality Assurance |
14|Facilities and Maintenance|Executive General and Administration|
15|Shipping and Receiving |Inventory Management |
16|Executive |Executive General and Administration|
列名を変更し、組み込み関数を使用して複数のテーブルから値を選択して返す¶
これは、selectステートメントで組み込み関数を使用して、異なるテーブルからデータを取得し、列の名前を変更してテーブルを返すクエリの例です。
Transact-SQL¶
CREATE FUNCTION GetPersonBasicInfo()
RETURNS TABLE
AS
RETURN
(
SELECT TOP (20)
P.PersonType,
P.FirstName,
E.JobTitle,
E.Gender,
YEAR(E.HireDate) as HIREYEAR
FROM
Person.Person P
INNER JOIN
HumanResources.Employee E
ON
P.BusinessEntityID = E.BusinessEntityID
);
GO
SELECT * FROM GetPersonBasicInfo();
PersonType|FirstName|JobTitle |Gender|HIREYEAR|
----------+---------+---------------------------------+------+--------+
EM |Ken |Chief Executive Officer |M | 2009|
EM |Terri |Vice President of Engineering |F | 2008|
EM |Roberto |Engineering Manager |M | 2007|
EM |Rob |Senior Tool Designer |M | 2007|
EM |Gail |Design Engineer |F | 2008|
EM |Jossef |Design Engineer |M | 2008|
EM |Dylan |Research and Development Manager |M | 2009|
EM |Diane |Research and Development Engineer|F | 2008|
EM |Gigi |Research and Development Engineer|F | 2009|
EM |Michael |Research and Development Manager |M | 2009|
EM |Ovidiu |Senior Tool Designer |M | 2010|
EM |Thierry |Tool Designer |M | 2007|
EM |Janice |Tool Designer |F | 2010|
EM |Michael |Senior Design Engineer |M | 2010|
EM |Sharon |Design Engineer |F | 2011|
EM |David |Marketing Manager |M | 2007|
EM |Kevin |Marketing Assistant |M | 2007|
EM |John |Marketing Specialist |M | 2011|
EM |Mary |Marketing Assistant |F | 2011|
EM |Wanida |Marketing Assistant |F | 2011|
Snowflake SQL¶
CREATE OR REPLACE FUNCTION GetPersonBasicInfo ()
RETURNS TABLE(
PersonType STRING /*** SSC-FDM-TS0012 - INFORMATION FOR THE COLUMN PersonType WAS NOT FOUND. STRING DATATYPE USED TO MATCH CAST AS STRING OPERATION ***/,
FirstName STRING /*** SSC-FDM-TS0012 - INFORMATION FOR THE COLUMN FirstName WAS NOT FOUND. STRING DATATYPE USED TO MATCH CAST AS STRING OPERATION ***/,
JobTitle STRING /*** SSC-FDM-TS0012 - INFORMATION FOR THE COLUMN JobTitle WAS NOT FOUND. STRING DATATYPE USED TO MATCH CAST AS STRING OPERATION ***/,
Gender STRING /*** SSC-FDM-TS0012 - INFORMATION FOR THE COLUMN Gender WAS NOT FOUND. STRING DATATYPE USED TO MATCH CAST AS STRING OPERATION ***/,
HIREYEAR INTEGER
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
AS
$$
SELECT
TOP 20
CAST(P.PersonType AS STRING),
CAST(P.FirstName AS STRING),
CAST(E.JobTitle AS STRING),
CAST(E.Gender AS STRING),
YEAR(E.HireDate :: TIMESTAMP) as HIREYEAR
FROM
Person.Person P
INNER JOIN
HumanResources.Employee E
ON P.BusinessEntityID = E.BusinessEntityID
$$;
SELECT
*
FROM
TABLE(GetPersonBasicInfo());
PersonType|FirstName|JobTitle |Gender|HIREYEAR|
----------+---------+---------------------------------+------+--------+
EM |Ken |Chief Executive Officer |M | 2009|
EM |Terri |Vice President of Engineering |F | 2008|
EM |Roberto |Engineering Manager |M | 2007|
EM |Rob |Senior Tool Designer |M | 2007|
EM |Gail |Design Engineer |F | 2008|
EM |Jossef |Design Engineer |M | 2008|
EM |Dylan |Research and Development Manager |M | 2009|
EM |Diane |Research and Development Engineer|F | 2008|
EM |Gigi |Research and Development Engineer|F | 2009|
EM |Michael |Research and Development Manager |M | 2009|
EM |Ovidiu |Senior Tool Designer |M | 2010|
EM |Thierry |Tool Designer |M | 2007|
EM |Janice |Tool Designer |F | 2010|
EM |Michael |Senior Design Engineer |M | 2010|
EM |Sharon |Design Engineer |F | 2011|
EM |David |Marketing Manager |M | 2007|
EM |Kevin |Marketing Assistant |M | 2007|
EM |John |Marketing Specialist |M | 2011|
EM |Mary |Marketing Assistant |F | 2011|
EM |Wanida |Marketing Assistant |F | 2011|
WITH ステートメントを使用して列を選択する¶
inline table-valued関数の本文は、以下のように WITH ステートメントを使用して指定することもできます。
Transact-SQL¶
CREATE FUNCTION GetMaritalStatusByGender
(
@P_Gender nchar(1)
)
RETURNS TABLE
AS
RETURN
(
WITH CTE AS
(
SELECT BusinessEntityID, MaritalStatus, Gender
FROM HumanResources.Employee
where Gender = @P_Gender
)
SELECT
MaritalStatus, Gender, CONCAT(P.FirstName,' ', P.LastName) as Name
FROM
CTE INNER JOIN Person.Person P
ON
CTE.BusinessEntityID = P.BusinessEntityID
);
GO
select * from GetMaritalStatusByGender('F');
MaritalStatus|Gender|Name |
-------------+------+-----------------------+
S |F |Terri Duffy |
M |F |Gail Erickson |
S |F |Diane Margheim |
M |F |Gigi Matthew |
M |F |Janice Galvin |
M |F |Sharon Salavaria |
S |F |Mary Dempsey |
M |F |Wanida Benshoof |
M |F |Mary Gibson |
M |F |Jill Williams |
S |F |Jo Brown |
M |F |Britta Simon |
M |F |Margie Shoop |
M |F |Rebecca Laszlo |
M |F |Suchitra Mohan |
M |F |Kim Abercrombie |
S |F |JoLynn Dobney |
M |F |Nancy Anderson |
M |F |Ruth Ellerbrock |
M |F |Doris Hartwig |
M |F |Diane Glimp |
M |F |Bonnie Kearney |
M |F |Denise Smith |
S |F |Diane Tibbott |
M |F |Carole Poland |
M |F |Carol Philips |
M |F |Merav Netz |
S |F |Betsy Stadick |
S |F |Danielle Tiedt |
S |F |Kimberly Zimmerman |
M |F |Elizabeth Keyser |
M |F |Mary Baker |
M |F |Alice Ciccu |
M |F |Linda Moschell |
S |F |Angela Barbariol |
S |F |Kitti Lertpiriyasuwat |
S |F |Susan Eaton |
S |F |Kim Ralls |
M |F |Nicole Holliday |
S |F |Anibal Sousa |
M |F |Samantha Smith |
S |F |Olinda Turner |
S |F |Cynthia Randall |
M |F |Sandra Reátegui Alayo |
S |F |Linda Randall |
S |F |Shelley Dyck |
S |F |Laura Steele |
S |F |Susan Metters |
S |F |Katie McAskill-White |
M |F |Barbara Decker |
M |F |Yvonne McKay |
S |F |Janeth Esteves |
M |F |Brenda Diaz |
M |F |Lorraine Nay |
M |F |Paula Nartker |
S |F |Lori Kane |
M |F |Kathie Flood |
S |F |Belinda Newman |
M |F |Karen Berge |
M |F |Lori Penor |
M |F |Jo Berry |
M |F |Laura Norman |
M |F |Paula Barreto de Mattos|
M |F |Mindy Martin |
M |F |Deborah Poe |
S |F |Candy Spoon |
M |F |Barbara Moreland |
M |F |Janet Sheperdigian |
S |F |Wendy Kahn |
S |F |Sheela Word |
M |F |Linda Meisner |
S |F |Erin Hagens |
M |F |Annette Hill |
S |F |Jean Trenary |
S |F |Stephanie Conroy |
S |F |Karen Berg |
M |F |Janaina Bueno |
M |F |Linda Mitchell |
S |F |Jillian Carson |
S |F |Pamela Ansman-Wolfe |
S |F |Lynn Tsoflias |
M |F |Amy Alberts |
S |F |Rachel Valdez |
M |F |Jae Pak |
Snowflake SQL¶
CREATE OR REPLACE FUNCTION GetMaritalStatusByGender
(P_GENDER STRING
)
RETURNS TABLE(
MaritalStatus STRING /*** SSC-FDM-TS0012 - INFORMATION FOR THE COLUMN MaritalStatus WAS NOT FOUND. STRING DATATYPE USED TO MATCH CAST AS STRING OPERATION ***/,
Gender STRING /*** SSC-FDM-TS0012 - INFORMATION FOR THE COLUMN Gender WAS NOT FOUND. STRING DATATYPE USED TO MATCH CAST AS STRING OPERATION ***/,
Name VARCHAR
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
AS
$$
--** SSC-PRF-TS0001 - PERFORMANCE WARNING - RECURSION FOR CTE NOT CHECKED. MIGHT REQUIRE RECURSIVE KEYWORD **
WITH CTE AS
(
SELECT
BusinessEntityID,
MaritalStatus,
Gender
FROM
HumanResources.Employee
where
Gender = :P_GENDER
)
SELECT
CAST(MaritalStatus AS STRING),
CAST(Gender AS STRING),
CONCAT(P.FirstName,' ', P.LastName) as Name
FROM
CTE
INNER JOIN
Person.Person P
ON CTE.BusinessEntityID = P.BusinessEntityID
$$;
select
*
from GetMaritalStatusByGender('F') !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'TableValuedFunctionCall' NODE ***/!!!;
MaritalStatus|Gender|Name |
-------------+------+-----------------------+
S |F |Terri Duffy |
M |F |Gail Erickson |
S |F |Diane Margheim |
M |F |Gigi Matthew |
M |F |Janice Galvin |
M |F |Sharon Salavaria |
S |F |Mary Dempsey |
M |F |Wanida Benshoof |
M |F |Mary Gibson |
M |F |Jill Williams |
S |F |Jo Brown |
M |F |Britta Simon |
M |F |Margie Shoop |
M |F |Rebecca Laszlo |
M |F |Suchitra Mohan |
M |F |Kim Abercrombie |
S |F |JoLynn Dobney |
M |F |Nancy Anderson |
M |F |Ruth Ellerbrock |
M |F |Doris Hartwig |
M |F |Diane Glimp |
M |F |Bonnie Kearney |
M |F |Denise Smith |
S |F |Diane Tibbott |
M |F |Carole Poland |
M |F |Carol Philips |
M |F |Merav Netz |
S |F |Betsy Stadick |
S |F |Danielle Tiedt |
S |F |Kimberly Zimmerman |
M |F |Elizabeth Keyser |
M |F |Mary Baker |
M |F |Alice Ciccu |
M |F |Linda Moschell |
S |F |Angela Barbariol |
S |F |Kitti Lertpiriyasuwat |
S |F |Susan Eaton |
S |F |Kim Ralls |
M |F |Nicole Holliday |
S |F |Anibal Sousa |
M |F |Samantha Smith |
S |F |Olinda Turner |
S |F |Cynthia Randall |
M |F |Sandra Reátegui Alayo |
S |F |Linda Randall |
S |F |Shelley Dyck |
S |F |Laura Steele |
S |F |Susan Metters |
S |F |Katie McAskill-White |
M |F |Barbara Decker |
M |F |Yvonne McKay |
S |F |Janeth Esteves |
M |F |Brenda Diaz |
M |F |Lorraine Nay |
M |F |Paula Nartker |
S |F |Lori Kane |
M |F |Kathie Flood |
S |F |Belinda Newman |
M |F |Karen Berge |
M |F |Lori Penor |
M |F |Jo Berry |
M |F |Laura Norman |
M |F |Paula Barreto de Mattos|
M |F |Mindy Martin |
M |F |Deborah Poe |
S |F |Candy Spoon |
M |F |Barbara Moreland |
M |F |Janet Sheperdigian |
S |F |Wendy Kahn |
S |F |Sheela Word |
M |F |Linda Meisner |
S |F |Erin Hagens |
M |F |Annette Hill |
S |F |Jean Trenary |
S |F |Stephanie Conroy |
S |F |Karen Berg |
M |F |Janaina Bueno |
M |F |Linda Mitchell |
S |F |Jillian Carson |
S |F |Pamela Ansman-Wolfe |
S |F |Lynn Tsoflias |
M |F |Amy Alberts |
S |F |Rachel Valdez |
M |F |Jae Pak |
既知の問題¶
問題は見つかりませんでした
関連 EWIs¶
SSC-FDM-TS0012: 式の情報が見つかりませんでした。使用される STRING への CAST
SSC-PRF-TS0001: パフォーマンスの警告 - CTE の再帰がチェックされていません。再帰キーワードが必要になるかもしれません。
SSC-EWI-0073: 機能同等性レビュー保留中
MULTI-STATEMENT TABLE-VALUED¶
Applies to
[x] SQL Server
[x] Azure Synapse Analytics
注釈
わかりやすくするため、出力コードの一部を省略しています。
注釈
このページのコードサンプルはすべて、まだ SnowConvert に実装されていません。各シナリオをSnowflakeにどのように翻訳すべきかのリファレンスとして解釈してください。これらの翻訳は将来変更される可能性があります。出力コードでは、わかりやすくするために省略されている部分があります。
説明¶
Multi-statement table-valuedはInline-statement table-valued (INLINE TABLE-VALUEDに似ています。ただし、Multi-statement table-valuedは、関数本文に複数のステートメントを持つことができ、テーブル列は戻り値のタイプで指定され、BEGIN/END ブロックを持ちます(SQL Server言語リファレンスmulti-statement table-valued関数の作成
Transact-SQL 構文¶
CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type
[ = default ] [READONLY] }
[ ,...n ]
]
)
RETURNS @return_variable TABLE <table_type_definition>
[ WITH <function_option> [ ,...n ] ]
[ AS ]
BEGIN
function_body
RETURN
END
[ ; ]
Snowflake SQL¶
CREATE OR REPLACE FUNCTION <name> ( [ <arguments> ] )
RETURNS TABLE ( <output_col_name> <output_col_type> [, <output_col_name> <output_col_type> ... ] )
AS '<sql_expression>'
サンプルソースパターン¶
次のセクションでは、この種の CREATE FUNCTION 構文で出現し得るすべてのソースコードパターンについて説明します。
Multi-Statement Table-Valued関数の関数本文は、SELECT ステートメントでなければなりません。このため、他のステートメントは個別に呼び出す必要があります。
テーブルへの値の挿入¶
テーブルに1つ以上の行を挿入し、新しい値を含むテーブルを返す
Transact-SQL¶
CREATE OR ALTER FUNCTION calc_behavioral_segment()
RETURNS @behavioral_segments TABLE (behavioral_segment VARCHAR(50))
AS
BEGIN
DECLARE @col varchar(15)
SET @col = 'Unknown'
INSERT INTO @behavioral_segments
SELECT @col
RETURN
END
SELECT * FROM calc_behavioral_segment();
BEHAVIORAL_SEGMENT|
------------------+
Unknown|
Snowflake SQL¶
!!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'TABLE VALUED FUNCTIONS' NODE ***/!!!
CREATE OR ALTER FUNCTION calc_behavioral_segment ()
RETURNS BEHAVIORAL_SEGMENTS TABLE (
behavioral_segment VARCHAR(50))
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
AS
BEGIN
DECLARE @col varchar(15)
SET @col = 'Unknown'
INSERT INTO @behavioral_segments
SELECT @col
RETURN
END
SELECT * FROM calc_behavioral_segment();;
BEHAVIORAL_SEGMENT|
------------------+
Unknown|
if/elseステートメントに従って値を挿入する¶
条件に従ってテーブルに行を挿入し、新しい値を含むテーブルを返す
Transact-SQL¶
CREATE OR ALTER FUNCTION odd_or_even_number(@number INT)
RETURNS @numbers TABLE (number_type VARCHAR(15))
AS
BEGIN
IF ((@number % 2) = 0)
BEGIN
INSERT @numbers SELECT 'Even'
END
ELSE
BEGIN
INSERT @numbers SELECT 'Odd'
END
RETURN
END
SELECT * FROM odd_or_even_number(9);
NUMBER_TYPE|
------------------+
Odd|
Snowflake SQL¶
!!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'TABLE VALUED FUNCTIONS' NODE ***/!!!
CREATE OR ALTER FUNCTION odd_or_even_number (NUMBER INT)
RETURNS NUMBERS TABLE (
number_type VARCHAR(15))
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
AS
BEGIN
IF ((@number % 2) = 0)
BEGIN
INSERT @numbers SELECT 'Even'
END
ELSE
BEGIN
INSERT @numbers SELECT 'Odd'
END
RETURN
END
SELECT * FROM odd_or_even_number(9);;
NUMBER_TYPE|
------------------+
Odd|
if/elseステートメントに従って複数挿入する¶
以下の例では、複数の値をテーブルに挿入し、条件に応じて複数の変数が変更されます。新しい値を含むテーブルを返す
Transact-SQL¶
CREATE OR ALTER FUNCTION new_employee_hired(@id VARCHAR (50), @position VARCHAR(50), @experience VARCHAR(15))
RETURNS @new_employee TABLE (id_employee VARCHAR (50), working_from_home BIT, team VARCHAR(15), computer VARCHAR(15))
AS
BEGIN
DECLARE @wfh BIT
DECLARE @team VARCHAR(15)
DECLARE @computer VARCHAR(15)
IF @position = 'DEVELOPER'
BEGIN
SET @team = 'TEAM_1'
SET @computer = 'LAPTOP'
END
IF @position = 'IT'
BEGIN
SET @team = 'TEAM_2'
SET @computer = 'DESKTOP'
END
IF @experience = 'JUNIOR'
BEGIN
SET @wfh = '0'
END
IF @experience = 'SENIOR'
BEGIN
SET @wfh = '1'
END
INSERT INTO @new_employee VALUES (@id, @wfh, @team, @computer)
RETURN
END
SELECT * FROM new_employee_hired('123456789', 'DEVELOPER', 'SENIOR');
ID_EMPLOYEE| WORKING_FROM_HOME| TEAM| COMPUTER|
--------------+-------------------|---------|-----------|
123456789| 1| TEAM_1| LAPTOP|
Snowflake¶
!!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'TABLE VALUED FUNCTIONS' NODE ***/!!!
CREATE OR ALTER FUNCTION new_employee_hired (ID STRING, POSITION STRING, EXPERIENCE STRING)
RETURNS NEW_EMPLOYEE TABLE (
id_employee VARCHAR(50),
working_from_home BOOLEAN,
team VARCHAR(15),
computer VARCHAR(15))
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
AS
BEGIN
DECLARE @wfh BIT
DECLARE @team VARCHAR(15)
DECLARE @computer VARCHAR(15)
IF @position = 'DEVELOPER'
BEGIN
SET @team = 'TEAM_1'
SET @computer = 'LAPTOP'
END
IF @position = 'IT'
BEGIN
SET @team = 'TEAM_2'
SET @computer = 'DESKTOP'
END
IF @experience = 'JUNIOR'
BEGIN
SET @wfh = '0'
END
IF @experience = 'SENIOR'
BEGIN
SET @wfh = '1'
END
INSERT INTO @new_employee VALUES (@id, @wfh, @team, @computer)
RETURN
END
SELECT * FROM new_employee_hired('123456789', 'DEVELOPER', 'SENIOR');;
ID_EMPLOYEE| WORKING_FROM_HOME| TEAM| COMPUTER|
--------------+-------------------|---------|-----------|
123456789| 1| TEAM_1| LAPTOP|
警告
ネストされたifステートメントがあり、ステートメント内で複数の変数が変更される場合は、ストアドプロシージャを使用する必要があります。
以前に挿入した値の更新¶
テーブルの列値を関数本文に更新し、新しい値を返します。
Transact-SQL¶
CREATE OR ALTER FUNCTION get_employees_history()
RETURNS @employee_history TABLE (
department_name NVARCHAR(50),
first_name NVARCHAR(50),
last_name NVARCHAR(50),
start_date DATE,
end_date DATE,
job_title NVARCHAR(50),
months_working INT
)
BEGIN
INSERT INTO @employee_history
SELECT D.name AS department_name, P.first_name, P.last_name, EH.start_date, EH.end_date, E.job_title, 0 FROM Department D
LEFT OUTER JOIN employee_department_history EH
ON D.department_ID = EH.department_ID
INNER JOIN Employee E
ON E.business_entity_ID = EH.business_entity_ID
INNER JOIN Person P
ON P.business_entity_ID = E.business_entity_ID
UPDATE @employee_history
SET
months_working =
CASE WHEN end_date IS NULL THEN DATEDIFF(MONTH, start_date, GETDATE())
ELSE DATEDIFF(MONTH, start_date, end_date)
END
RETURN;
END;
SELECT TOP(10) * FROM get_employees_history();
DEPARTMENT_NAME| FIRST_NAME| LAST_NAME| START_DATE| END_DATE| JOB_TITLE| MONTHS_WORKING|
-------------------------------------+ ------------------+-------------------+----------------+-----------------+------------------------------------+----------------------+
Sales| Syed| Abbas| 2013-03-14| NULL| Pacific Sales Manager| 106|
Production| Kim| Abercrombie| 2010-01-16| NULL| Production Technician - WC60| 144|
Quality Assurance| Hazem| Abolrous| 2009-02-28| NULL| Quality Assurance Manager| 155|
Shipping and Receiving| Pilar| Ackerman| 2009-01-02| NULL| Shipping and Receiving Supervisor| 156|
Production| Jay| Adams| 2009-03-05| NULL| Production Technician - WC60| 154|
Information Services| François| Ajenstat| 2009-01-17| NULL| Database Administrator| 156|
Sales| Amy| Alberts| 2012-04-16| NULL| European Sales Manager| 117|
Production| Greg| Alderson| 2008-12-02| NULL| Production Technician - WC45| 157|
Quality Assurance| Sean| Alexander| 2008-12-28| NULL| Quality Assurance Technician| 157|
Facilities and Maintenance| Gary| Altman| 2009-12-02| NULL| Facilities Manager| 145|
Snowflake SQL¶
!!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'TABLE VALUED FUNCTIONS' NODE ***/!!!
CREATE OR ALTER FUNCTION get_employees_history ()
RETURNS EMPLOYEE_HISTORY TABLE (
department_name VARCHAR(50),
first_name VARCHAR(50),
last_name VARCHAR(50),
start_date DATE,
end_date DATE,
job_title VARCHAR(50),
months_working INT
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
BEGIN
INSERT INTO @employee_history
SELECT D.name AS department_name, P.first_name, P.last_name, EH.start_date, EH.end_date, E.job_title, 0 FROM Department D
LEFT OUTER JOIN employee_department_history EH
ON D.department_ID = EH.department_ID
INNER JOIN Employee E
ON E.business_entity_ID = EH.business_entity_ID
INNER JOIN Person P
ON P.business_entity_ID = E.business_entity_ID
UPDATE @employee_history
SET
months_working =
CASE WHEN end_date IS NULL THEN DATEDIFF(MONTH, start_date, GETDATE())
ELSE DATEDIFF(MONTH, start_date, end_date)
END
RETURN;
END;
SELECT TOP(10) * FROM get_employees_history();;
DEPARTMENT_NAME| FIRST_NAME| LAST_NAME| START_DATE| END_DATE| JOB_TITLE| MONTHS_WORKING|
-------------------------------------+ ------------------+-------------------+----------------+-----------------+------------------------------------+----------------------+
Sales| Syed| Abbas| 2013-03-14| NULL| Pacific Sales Manager| 106|
Production| Kim| Abercrombie| 2010-01-16| NULL| Production Technician - WC60| 144|
Quality Assurance| Hazem| Abolrous| 2009-02-28| NULL| Quality Assurance Manager| 155|
Shipping and Receiving| Pilar| Ackerman| 2009-01-02| NULL| Shipping and Receiving Supervisor| 156|
Production| Jay| Adams| 2009-03-05| NULL| Production Technician - WC60| 154|
Information Services| François| Ajenstat| 2009-01-17| NULL| Database Administrator| 156|
Sales| Amy| Alberts| 2012-04-16| NULL| European Sales Manager| 117|
Production| Greg| Alderson| 2008-12-02| NULL| Production Technician - WC45| 157|
Quality Assurance| Sean| Alexander| 2008-12-28| NULL| Quality Assurance Technician| 157|
Facilities and Maintenance| Gary| Altman| 2009-12-02| NULL| Facilities Manager| 145|
複数の戻り句¶
以下のサンプルでは戻り句が複数ありますが、これは状況に応じて関数全体を実行し続ける必要がないためです。
Transact-SQL¶
CREATE OR ALTER FUNCTION create_new_team(@team_name VARCHAR(50))
RETURNS @new_team TABLE (type VARCHAR(50), name VARCHAR(50))
AS
BEGIN
DECLARE @employees INT
SET @employees = (SELECT count(*) FROM employee)
DECLARE @type VARCHAR(15)
SET @type = 'small_team'
IF (@employees < 8)
BEGIN
INSERT @new_team VALUES (@type, @team_name)
RETURN
END
SET @type = 'big_team'
INSERT @new_team VALUES (@type, @team_name)
RETURN
END
SELECT * FROM create_new_team('Team1');
TYPE| NAME|
--------------+ --------------+
SMALL_TEAM| TEAM1|
Snowflake SQL¶
!!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'TABLE VALUED FUNCTIONS' NODE ***/!!!
CREATE OR ALTER FUNCTION create_new_team (TEAM_NAME STRING)
RETURNS NEW_TEAM TABLE (
type VARCHAR(50),
name VARCHAR(50))
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
AS
BEGIN
DECLARE @employees INT
SET @employees = (SELECT count(*) FROM employee)
DECLARE @type VARCHAR(15)
SET @type = 'small_team'
IF (@employees < 8)
BEGIN
INSERT @new_team VALUES (@type, @team_name)
RETURN
END
SET @type = 'big_team'
INSERT @new_team VALUES (@type, @team_name)
RETURN
END
SELECT * FROM create_new_team('Team1');;
TYPE| NAME|
--------------+ --------------+
SMALL_TEAM| TEAM1|
警告
この変換は、挿入する値が1つだけの場合に適用されます。複数の値がある場合は、ストアドプロシージャを使用する必要があります。
複雑なケース¶
この例は、ネストされた if ステートメントを使用し、真の条件に応じて値を挿入する複雑なケースです。
Transact-SQL¶
CREATE OR ALTER FUNCTION vacation_status(@id VARCHAR (50))
RETURNS @status TABLE (vacation_status VARCHAR(30))
AS
BEGIN
DECLARE @hire_date DATETIME
SET @hire_date = (SELECT @hire_date FROM employee WHERE employeeId = @id)
DECLARE @vacation_hours INT
SET @vacation_hours = (SELECT count(vacation_hours) FROM employee WHERE employeeId = @id)
DECLARE @time_working INT
SET @time_working = (SELECT DATEDIFF(MONTH, @hire_date,GETDATE()))
IF (@vacation_hours > 0)
BEGIN
IF (@time_working > 3)
BEGIN
IF (@vacation_hours < 120)
BEGIN
INSERT INTO @status VALUES ('Ok')
END
IF (@vacation_hours = 120)
BEGIN
INSERT INTO @status values ('In the limit')
END
IF (@vacation_hours > 120)
BEGIN
INSERT INTO @status VALUES ('With excess')
END
END
ELSE
BEGIN
INSERT INTO @status values ('Hired recently')
END
END
ELSE
BEGIN
INSERT INTO @status values ('No hours')
END
RETURN
END
SELECT * FROM vacation_status('adventure-worksken0')
VACATION_STATUS|
-----------------+
OK|
Snowflake SQL¶
!!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'TABLE VALUED FUNCTIONS' NODE ***/!!!
CREATE OR ALTER FUNCTION vacation_status (ID STRING)
RETURNS STATUS TABLE (
vacation_status VARCHAR(30))
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
AS
BEGIN
DECLARE @hire_date DATETIME
SET @hire_date = (SELECT @hire_date FROM employee WHERE employeeId = @id)
DECLARE @vacation_hours INT
SET @vacation_hours = (SELECT count(vacation_hours) FROM employee WHERE employeeId = @id)
DECLARE @time_working INT
SET @time_working = (SELECT DATEDIFF(MONTH, @hire_date,GETDATE()))
IF (@vacation_hours > 0)
BEGIN
IF (@time_working > 3)
BEGIN
IF (@vacation_hours < 120)
BEGIN
INSERT INTO @status VALUES ('Ok')
END
IF (@vacation_hours = 120)
BEGIN
INSERT INTO @status values ('In the limit')
END
IF (@vacation_hours > 120)
BEGIN
INSERT INTO @status VALUES ('With excess')
END
END
ELSE
BEGIN
INSERT INTO @status values ('Hired recently')
END
END
ELSE
BEGIN
INSERT INTO @status values ('No hours')
END
RETURN
END
SELECT * FROM vacation_status('adventure-worksken0');
VACATION_STATUS|
-----------------+
OK|
既知の問題¶
クエリに沿ったWhileステートメント¶
この例の問題点は、メインセレクトの WITH 句の中でwhileステートメントを CTE に変換する方法がないことです。このため、同じロジックを維持するために、このステートメントをストアプロシージャに変換せざるを得ません。
Transact-SQL¶
--Additional Params: -t JavaScript
CREATE OR ALTER FUNCTION get_group_name
(@department_id INT)
RETURNS @group_names TABLE (group_name VARCHAR(15))
AS
BEGIN
DECLARE @name VARCHAR(30) = 'Another Department'
WHILE @name = 'Another Department'
BEGIN
IF (@department_id < 3)
BEGIN
SET @name = 'engineering'
END
IF @department_id = 3
BEGIN
SET @name = 'Tool Design'
END
SELECT @department_id = @department_id / 3
END
INSERT @group_names SELECT @name
RETURN
END
SELECT * FROM get_group_name(9);
GROUP_NAME|
------------------+
Tool Design|
Snowflake SQL¶
!!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'TABLE VALUED FUNCTIONS' NODE ***/!!!
CREATE OR ALTER FUNCTION get_group_name
(DEPARTMENT_ID INT)
RETURNS @group_names TABLE (
group_name VARCHAR(15))
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
AS
BEGIN
DECLARE @name VARCHAR(30) = 'Another Department'
WHILE @name = 'Another Department'
BEGIN
IF (@department_id < 3)
BEGIN
SET @name = 'engineering'
END
IF @department_id = 3
BEGIN
SET @name = 'Tool Design'
END
SELECT @department_id = @department_id / 3
END
INSERT @group_names SELECT @name
RETURN
END
SELECT * FROM get_group_name(9);;
GROUP_NAME|
------------------+
Tool Design|
カーソル宣言¶
ユーザー定義関数は、CURSOR を DECLARE、OPEN、FETCH、CLOSE、DEALLOCATE することはできません。ストアドプロシージャを使用してカーソルを操作します。
Transact-SQL¶
--Additional Params: -t JavaScript
CREATE OR ALTER FUNCTION amount_new_specimens(@id int)
RETURNS @new_specimens TABLE (amount int)
AS
BEGIN
DECLARE @first_specimen VARCHAR(30) ;
set @first_specimen = (select name_specimen from specimen where specimen_id = @id);
DECLARE @second_specimen VARCHAR(30);
DECLARE @specimens TABLE (name_specimen VARCHAR(30))
DECLARE Cursor1 CURSOR
FOR SELECT name_specimen
FROM specimen
OPEN cursor1
FETCH NEXT FROM cursor1
INTO @second_specimen;
WHILE @@FETCH_STATUS = 0
BEGIN
IF @first_specimen <> @second_specimen
BEGIN
INSERT INTO @specimens values (CONCAT_WS('-', @first_specimen, @second_specimen))
END
FETCH NEXT FROM cursor1
INTO @second_specimen;
END
CLOSE cursor1;
DEALLOCATE cursor1;
INSERT INTO @new_specimens SELECT COUNT(*) FROM @specimens
RETURN
END
SELECT * FROM amount_new_specimens(1);
AMOUNT|
------------------+
3|
Snowflake SQL¶
--Additional Params: -t JavaScript
!!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'TABLE VALUED FUNCTIONS' NODE ***/!!!
CREATE OR ALTER FUNCTION amount_new_specimens (ID INT)
RETURNS @new_specimens TABLE (
amount INT
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
AS
BEGIN
DECLARE @first_specimen VARCHAR(30) ;
set @first_specimen = (select name_specimen from specimen where specimen_id = @id);
DECLARE @second_specimen VARCHAR(30);
DECLARE @specimens TABLE (name_specimen VARCHAR(30))
DECLARE Cursor1 CURSOR
FOR SELECT name_specimen
FROM specimen
OPEN cursor1
FETCH NEXT FROM cursor1
INTO @second_specimen;
WHILE @@FETCH_STATUS = 0
BEGIN
IF @first_specimen <> @second_specimen
BEGIN
INSERT INTO @specimens values (CONCAT_WS('-', @first_specimen, @second_specimen))
END
FETCH NEXT FROM cursor1
INTO @second_specimen;
END
CLOSE cursor1;
DEALLOCATE cursor1;
INSERT INTO @new_specimens SELECT COUNT(*) FROM @specimens
RETURN
END
SELECT * FROM amount_new_specimens(1);;
AMOUNT|
------------------+
3|
共通テーブル式では、異なるステートメントはサポートされていません¶
UPDATE、INSERT、DELETE、ALTER、DROP の句は、区切り文字を使用して宣言した後でも、共通テーブル式の本文ではサポートされていません。このため、関数をストアドプロシージャとして動作するように変更することができます。
Transact-SQL¶
--Additional Params: -t JavaScript
CREATE OR ALTER PROCEDURE product_history
AS
BEGIN
DECLARE @product_history TABLE (
product_name NVARCHAR(50),
rating INT
)
INSERT INTO @product_history
SELECT P.Name AS product_name, AVG(ALL R.rating) FROM Production.product P
INNER JOIN Production.product_review R
ON R.product_ID = P.product_ID
GROUP BY P.Name;
DELETE FROM @product_history
WHERE rating < 2;
SELECT * FROM @product_history;
END
GO;
EXEC product_history
PRODUCT_NAME| Rating|
----------------------------------+------------------|
HL Mountain Pedal| 3|
Mountain Bike Socks, M| 5|
Road-550-W Yellow, 40| 5|
Snowflake SQL¶
CREATE OR REPLACE PROCEDURE product_history ()
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
// REGION SnowConvert Helpers Code
var _RS, ROW_COUNT, _ROWS, MESSAGE_TEXT, SQLCODE = 0, SQLSTATE = '00000', OBJECT_SCHEMA_NAME = 'UNKNOWN', ERROR_HANDLERS, NUM_ROWS_AFFECTED, PROC_NAME = arguments.callee.name, DOLLAR_DOLLAR = '$' + '$';
function* sqlsplit(sql) {
var part = '';
var ismark = () => sql[i] == '$' && sql[i + 1] == '$';
for(var i = 0;i < sql.length;i++) {
if (sql[i] == ';') {
yield part + sql[i];
part = '';
} else if (ismark()) {
part += sql[i++] + sql[i++];
while ( i < sql.length && !ismark() ) {
part += sql[i++];
}
part += sql[i] + sql[i++];
} else part += sql[i];
}
if (part.trim().length) yield part;
};
var formatDate = (arg) => (new Date(arg - (arg.getTimezoneOffset() * 60000))).toISOString().slice(0,-1);
var fixBind = function (arg) {
arg = arg == undefined ? null : arg instanceof Date ? formatDate(arg) : arg;
return arg;
};
var EXEC = (stmt,binds = [],severity = "16",noCatch = false) => {
binds = binds ? binds.map(fixBind) : binds;
for(var stmt of sqlsplit(stmt)) {
try {
_RS = snowflake.createStatement({
sqlText : stmt,
binds : binds
});
_ROWS = _RS.execute();
ROW_COUNT = _RS.getRowCount();
NUM_ROWS_AFFECTED = _RS.getNumRowsAffected();
return {
THEN : (action) => !SQLCODE && action(fetch(_ROWS))
};
} catch(error) {
let rStack = new RegExp('At .*, line (\\d+) position (\\d+)');
let stackLine = error.stackTraceTxt.match(rStack) || [0,-1];
MESSAGE_TEXT = error.message.toString();
SQLCODE = error.code.toString();
SQLSTATE = error.state.toString();
snowflake.execute({
sqlText : `SELECT UPDATE_ERROR_VARS_UDF(?,?,?,?,?,?)`,
binds : [stackLine[1],SQLCODE,SQLSTATE,MESSAGE_TEXT,PROC_NAME,severity]
});
throw error;
}
}
};
// END REGION
EXEC(`CREATE OR REPLACE TEMPORARY TABLE T_product_history (
product_name VARCHAR(50),
rating INT
)`);
EXEC(` INSERT INTO T_product_history
SELECT
P.Name AS product_name,
AVG(ALL R.rating) FROM
Production.product P
INNER JOIN
Production.product_review R
ON R.product_ID = P.product_ID
GROUP BY
P.Name`);
EXEC(`DELETE FROM
T_product_history
WHERE
rating < 2`);
EXEC(`
SELECT
*
FROM
T_product_history`);
$$;
!!!RESOLVE EWI!!! /*** SSC-EWI-0040 - THE STATEMENT IS NOT SUPPORTED IN SNOWFLAKE ***/!!!
;
CALL product_history();
PRODUCT_NAME| Rating|
----------------------------------+------------------|
HL Mountain Pedal| 3|
Mountain Bike Socks, M| 5|
Road-550-W Yellow, 40| 5|
関連 EWIs¶
SSC-EWI-0040: ステートメントがサポートされていません。
SSC-EWI-0073: 機能同等性レビュー保留中
SCALAR¶
Applies to
[x] SQL Server
[x] Azure Synapse Analytics
説明¶
注釈
わかりやすくするため、出力コードの一部を省略しています。
スカラーユーザー定義関数は、パラメーターを受け取り、複雑な計算などのアクションを実行し、その結果をスカラー値として返すTransact-SQL または共通言語ランタイム（CLR）ルーチンです。(SQL Server言語リファレンス CREATE FUNCTION サブセクション)。
注釈
これらの関数は通常、SELECT ステートメントの内部で使用されるか、単一変数のセットアップ（ストアドプロシージャの内部で使用されることがほとんどです）で使用されます。
Transact-SQL 構文¶
-- Transact-SQL Scalar Function Syntax
CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ][ type_schema_name. ] parameter_data_type
[ = default ] [ READONLY ] }
[ ,...n ]
]
)
RETURNS return_data_type
[ WITH <function_option> [ ,...n ] ]
[ AS ]
BEGIN
function_body
RETURN scalar_expression
END
[ ; ]
Snowflake構文¶
Snowflakeでは、ユーザー定義関数で3つの異なる言語を使用できます。
SQL
JavaScript
Java
今のところ、 SnowConvert は SQL と JavaScript のみをターゲット言語としてサポートしています。
注釈
SQL ユーザー定義関数は、本文として1つのクエリしかサポートしていません。データベースからの読み取りは可能ですが、書き込みや変更はできません。(スカラー SQL UDFs リファレンス)。
CREATE [ OR REPLACE ] [ SECURE ] FUNCTION <name> ( [ <arg_name> <arg_data_type> ] [ , ... ] )
RETURNS { <result_data_type> | TABLE ( <col_name> <col_data_type> [ , ... ] ) }
[ [ NOT ] NULL ]
[ { CALLED ON NULL INPUT | { RETURNS NULL ON NULL INPUT | STRICT } } ]
[ VOLATILE | IMMUTABLE ]
[ COMMENT = '<string_literal>' ]
AS '<function_definition>'
注釈
JavaScript ユーザー定義関数は、本文中に複数のステートメントを記述することができますが、データベースへのクエリを実行することはできません。（スカラー JavaScript UDFs リファレンス）
CREATE [ OR REPLACE ] [ SECURE ] FUNCTION <name> ( [ <arg_name> <arg_data_type> ] [ , ... ] )
RETURNS { <result_data_type> | TABLE ( <col_name> <col_data_type> [ , ... ] ) }
[ [ NOT ] NULL ]
LANGUAGE JAVASCRIPT
[ { CALLED ON NULL INPUT | { RETURNS NULL ON NULL INPUT | STRICT } } ]
[ VOLATILE | IMMUTABLE ]
[ COMMENT = '<string_literal>' ]
AS '<function_definition>'
サンプルソースパターン¶
SetおよびDeclareステートメント¶
関数本文で最もよく使われるステートメントは DECLARE と SET ステートメントです。デフォルト値のない DECLARE ステートメントでは、変換は無視されます。SET ステートメントおよびデフォルト値のある DECLARE ステートメントでは、COMMON TABLE EXPRESSION に変換されます。各共通テーブル式には、ローカル変数値を表す列が含まれます。
Transact-SQL¶
CREATE OR ALTER FUNCTION PURCHASING.GetVendorName()
RETURNS NVARCHAR(50) AS
BEGIN
DECLARE @result NVARCHAR(50)
DECLARE @BUSINESSENTITYID INT
SET @BUSINESSENTITYID = 1492
SELECT @result = Name FROM PURCHASING.VENDOR WHERE BUSINESSENTITYID = @BUSINESSENTITYID
RETURN @result
END
GO
SELECT PURCHASING.GetVendorName() as vendor_name;
vendor_name |
-----------------------+
Australia Bike Retailer|
Snowflake¶
CREATE OR REPLACE FUNCTION PURCHASING.GetVendorName ()
RETURNS VARCHAR(50)
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
AS
$$
WITH CTE1 AS
(
SELECT
1492 AS BUSINESSENTITYID
),
CTE2 AS
(
SELECT
Name AS RESULT
FROM
PURCHASING.VENDOR
WHERE
BUSINESSENTITYID = (
SELECT
BUSINESSENTITYID
FROM
CTE1
)
)
SELECT
RESULT
FROM
CTE2
$$;
SELECT
PURCHASING.GetVendorName() as vendor_name;
VENDOR_NAME |
-----------------------+
Australia Bike Retailer|
If/Elseステートメントの変換¶
If/Elseステートメントはさまざまな方法で処理できます。クエリ内で条件を許可するselect内で CASE EXPRESSION を使用して、JavaScriptまたは SQL に変換できます。JavaScript変換は非常に簡単ですが、Caseステートメントは一見するとそれほど明白ではないかもしれません。
Transact-SQL¶
CREATE OR ALTER FUNCTION PURCHASING.HasActiveFlag(@BusinessEntityID int)
RETURNS VARCHAR(10) AS
BEGIN
DECLARE @result VARCHAR(10)
DECLARE @ActiveFlag BIT
SELECT @ActiveFlag = ActiveFlag from PURCHASING.VENDOR v where v.BUSINESSENTITYID = @BusinessEntityID
IF @ActiveFlag = 1
SET @result = 'YES'
ELSE IF @ActiveFlag = 0
SET @result = 'NO'
RETURN @result
END
GO
SELECT PURCHASING.HasActiveFlag(1516) as has_active_flag;
has_active_flag|
---------------+
NO |
Snowflake¶
CREATE OR REPLACE FUNCTION PURCHASING.HasActiveFlag (P_BUSINESSENTITYID INT)
RETURNS VARCHAR(10)
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
AS
$$
WITH CTE1 AS
(
SELECT
ActiveFlag AS ACTIVEFLAG
from
PURCHASING.VENDOR v
where
v.BUSINESSENTITYID = P_BUSINESSENTITYID
),
CTE2 AS
(
SELECT
CASE
WHEN (
SELECT
ACTIVEFLAG
FROM
CTE1
) = 1
THEN 'YES'
WHEN (
SELECT
ACTIVEFLAG
FROM
CTE1
) = 0
THEN 'NO'
END AS RESULT
)
SELECT
RESULT
FROM
CTE2
$$;
SELECT
PURCHASING.HasActiveFlag(1516) as has_active_flag;
HAS_ACTIVE_FLAG|
---------------+
NO |
ネストされたステートメント¶
ネストされたステートメントの場合、構造化プログラミングは1つのクエリに変換されます。フロー制御内のステートメントは、実行順序を保持するためにテーブル構造の中にネストされます。
注釈
CASE EXPRESSIONS はステートメントごとに1つの値しか返せません
例¶
注釈
どちらのプログラミングパラダイムでも、次のコードは機能的に等価です。
DECLARE @VendorId AS int;
DECLARE @AccountNumber AS VARCHAR(50);
SELECT @VendorId = poh.VendorID
FROM Purchasing.PurchaseOrderHeader poh
WHERE PurchaseOrderID = 1
SELECT @AccountNumber = v.AccountNumber
FROM Purchasing.Vendor v
WHERE v.BusinessEntityID = @VendorId
SELECT V.AccountNumber AccountNumber
FROM (SELECT poh.VendorID VendorId
FROM Purchasing.PurchaseOrderHeader poh
WHERE PurchaseOrderID = 1
) T1, Purchasing.Vendor v
WHERE v.BusinessEntityID = T1.VendorId
AccountNumber|
-------------+
LITWARE0001 |
SELECTs による条件変数¶
条件ステートメント内での変数定義と割り当ては、やや問題になりがちです。というのも、コードのさらに下にある変数への参照は、その変数が最後に変更された場所を知っていなければならないからです。それだけでなく、参照が別の条件ステートメント内にある場合、その変数への以前の既知の割り当てを参照する何らかのリダイレクトが必要です。
入力コードに見られるようなネストや複雑なクエリによって、これはすべて悪化します。
次のシナリオでは、最初の IF ステートメントは、内容が十分に単純なので、問題なく変換できます。2番目と3番目の IF ステートメントは、SELECT を通して変数割り当て以外のステートメントがあるため、現時点ではサポートされていないためコメントアウトされています。
SQL Server¶
CREATE or ALTER FUNCTION PURCHASING.SELECTINUDF (
@param1 varchar(12)
)
RETURNS int
AS
BEGIN
declare @var1 int;
declare @var2 int;
declare @var3 int;
IF @param1 = 'first'
BEGIN
select @var1 = col1 + 10 from table1 WHERE id = 0;
select @var2 = col1 + 20 from table1 WHERE id = 0;
select @var3 = col1 + 30 from table1 WHERE id = 0;
END
IF @param1 = 'second'
BEGIN
declare @var4 int = 10;
select @var1 = col1 + 40 from table1 WHERE id = 0;
select @var2 = col1 + 40 from table1 WHERE id = 0;
END
IF @param1 = 'third'
BEGIN
select col1 from table1 where id = 0;
select @var1 = col1 + 50 from table1 WHERE id = 0;
select @var2 = col1 + 50 from table1 WHERE id = 0;
END
RETURN @var1
END
SELECT PURCHASING.SELECTINUDF('first') as result; -- Assuming table1.col1 is 0 when ID = 0
RESULT|
------+
10|
Snowflake¶
CREATE OR REPLACE FUNCTION PURCHASING.SELECTINUDF (PARAM1 STRING)
RETURNS INT
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
AS
$$
WITH CTE1 AS
(
SELECT
CASE
WHEN PARAM1 = 'first'
THEN (SELECT
col1 + 10 AS VAR1 from
table1
WHERE
id = 0)
END AS VAR1,
CASE
WHEN PARAM1 = 'first'
THEN (SELECT
col1 + 20 AS VAR2 from
table1
WHERE
id = 0)
END AS VAR2,
CASE
WHEN PARAM1 = 'first'
THEN (SELECT
col1 + 30 AS VAR3 from
table1
WHERE
id = 0)
END AS VAR3
),
!!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'IF STATEMENT' NODE ***/!!!
CTE2 AS
(
/* IF @param1 = 'second'
BEGIN
declare @var4 int = 10;
select @var1 = col1 + 40 from table1 WHERE id = 0;
select @var2 = col1 + 40 from table1 WHERE id = 0;
END*/
SELECT
null
),
!!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'IF STATEMENT' NODE ***/!!!
CTE3 AS
(
/* IF @param1 = 'third'
BEGIN
select col1 from table1 where id = 0;
select @var1 = col1 + 50 from table1 WHERE id = 0;
select @var2 = col1 + 50 from table1 WHERE id = 0;
END*/
SELECT
null
),
CTE4 AS
(
SELECT
PURCHASING.SELECTINUDF('first') as result
)
SELECT
VAR1
FROM
CTE4
$$ -- Assuming table1.col1 is 0 when ID = 0
;
RESULT|
------+
10|
変数を割り当てて返す¶
この単純なパターンでは、変数宣言があり、SELECT ステートメントを使用して変数がセットされ、最後に返されます。これは、元の動作を維持するために、共通テーブル式 に移行する予定です。
SQL Server¶
CREATE OR ALTER FUNCTION Purchasing.GetTotalFreight()
RETURNS MONEY AS
BEGIN
DECLARE @Result MONEY
SELECT @Result = ISNULL(SUM(t.Freight), 0) from Purchasing.PurchaseOrderHeader t
return @Result
END
GO
select Purchasing.GetTotalFreight() as Result;
Result |
------------+
1583978.2263|
Snowflake¶
CREATE OR REPLACE FUNCTION Purchasing.GetTotalFreight ()
RETURNS NUMBER(38, 4)
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
AS
$$
WITH CTE1 AS
(
SELECT
NVL(SUM(t.Freight), 0) AS RESULT from
Purchasing.PurchaseOrderHeader t
)
SELECT
RESULT
FROM
CTE1
$$;
select
Purchasing.GetTotalFreight() as Result;
RESULT |
------------+
1583978.2263|
複数の関数呼び出し¶
この特定のパターンでは、明らかなクエリはありませんが、同じ変数に対して複数の関数が呼び出され、最後にその変数が返されます。Snowflakeは関数内でのクエリしかサポートしていないため、このブロックの解決策は、Selectに追加して内部で呼び出しをネストし、戻り値がソース上の値と同じであることを確認することになります。
SQL Server¶
CREATE OR ALTER FUNCTION PURCHASING.Foo
(
@PARAM1 INT
)
RETURNS varchar(25)
AS
BEGIN
DECLARE @filter INT = @PARAM1
DECLARE @NAME VARCHAR(25) = (SELECT Name from Purchasing.Vendor v where BusinessEntityID = @filter)
SET @NAME = REPLACE(@NAME, 'Australia', 'USA')
SET @NAME = REPLACE(@NAME, 'Bike', 'Car')
RETURN @NAME
END
GO
SELECT PURCHASING.Foo(1492) AS Name;
Name |
----------------+
USA Car Retailer|
Snowflake¶
CREATE OR REPLACE FUNCTION PURCHASING.Foo (PARAM1 INT)
RETURNS VARCHAR(25)
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
AS
$$
WITH CTE1 AS
(
SELECT
PARAM1 AS FILTER
),
CTE2 AS
(
SELECT
(SELECT
Name
from
Purchasing.Vendor v
where
BusinessEntityID = (
SELECT
FILTER
FROM
CTE1
)
) AS NAME
),
CTE3 AS
(
SELECT
REPLACE((
SELECT
NAME
FROM
CTE3
), 'Australia', 'USA') AS NAME
),
CTE4 AS
(
SELECT
REPLACE((
SELECT
NAME
FROM
CTE4
), 'Bike', 'Car') AS NAME
)
SELECT
NAME
FROM
CTE4
$$;
SELECT
PURCHASING.Foo(1492) AS Name;
NAME |
----------------+
USA Car Retailer|
複数の IF 条件に基づいて変数を増やし、その値を返す¶
このパターンでは、複数の IF 条件を使って変数が変更（この場合は増加）されます。最初に変数のセットが初期化され、結果変数を増やすかどうかを決定するために使用されます。最後に、結果変数が返されます。
SQL Server¶
CREATE OR ALTER FUNCTION PURCHASING.FOO()
RETURNS MONEY
AS
BEGIN
declare @firstValue MONEY
declare @secondValue MONEY
declare @Result MONEY
select @Result = 0
select @firstValue = SubTotal from Purchasing.PurchaseOrderHeader where PurchaseOrderID = 1
select @secondValue = SubTotal from Purchasing.PurchaseOrderHeader where PurchaseOrderID = 2
if @firstValue is not null
select @Result = @Result + @firstValue
if @secondValue is not null
select @Result = @Result + @secondValue
return @Result
END
GO
SELECT PURCHASING.Foo() AS Result;
Result |
--------+
473.1415|
Snowflake¶
CREATE OR REPLACE FUNCTION PURCHASING.FOO ()
RETURNS NUMBER(38, 4)
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
AS
$$
WITH CTE1 AS
(
select
0 AS RESULT
),
CTE2 AS
(
select
SubTotal AS FIRSTVALUE
from
Purchasing.PurchaseOrderHeader
where
PurchaseOrderID = 1
),
CTE3 AS
(
select
SubTotal AS SECONDVALUE
from
Purchasing.PurchaseOrderHeader
where
PurchaseOrderID = 2
),
CTE4 AS
(
SELECT
CASE
WHEN (
SELECT
FIRSTVALUE
FROM
CTE2
) is not null
THEN (
select
(
SELECT
RESULT
FROM
CTE1
) + (
SELECT
FIRSTVALUE
FROM
CTE2
) AS RESULT)
END AS RESULT
),
CTE5 AS
(
SELECT
CASE
WHEN (
SELECT
SECONDVALUE
FROM
CTE3
) is not null
THEN (
select
(
SELECT
RESULT
FROM
CTE1
) + (
SELECT
SECONDVALUE
FROM
CTE3
) AS RESULT)
ELSE (SELECT
RESULT
FROM
CTE4)
END AS RESULT
)
SELECT
RESULT
FROM
CTE5
$$;
SELECT
PURCHASING.Foo() AS Result;
RESULT |
--------+
473.1415|
2つ以上の RETURN ステートメント¶
このパターンでは、CASE 式で実行される最後のステートメントのように、コードの流れを断ち切るreturn句を含む IF ブロックが本文の最後に追加されます。
基本ケース¶
この特別なシナリオでは、条件 RETURN ステートメントと最終 RETURN ステートメントの間にロジックがないため、すべての本文は単一の CASE EXPRESSION にマッピングされます。
SQL Server¶
CREATE OR ALTER FUNCTION [PURCHASING].[FOO] ()
RETURNS INT
AS
BEGIN
IF exists (SELECT PreferredVendorStatus FROM Purchasing.Vendor v )
RETURN 1
RETURN 0
END
GO
SELECT PURCHASING.FOO() as result;
result|
------+
1|
Snowflake¶
CREATE OR REPLACE FUNCTION PURCHASING.FOO ()
RETURNS INT
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
AS
$$
SELECT
CASE
WHEN exists (SELECT
PreferredVendorStatus
FROM
Purchasing.Vendor v
)
THEN 1
ELSE 0
END
$$;
SELECT
PURCHASING.FOO() as result;
RESULT|
------+
1|
共通テーブル式¶
共通テーブル式は元のコードと同じように保持され、生成された式と連結されます。SnowConvert は、重複した名前が生成されるのを避けるために、最初にすべてのオリジナルの COMMON TABLE EXPRESSION 名を識別することができます。
SQL Server¶
CREATE OR ALTER FUNCTION [PURCHASING].[FOO]
(
@status INT
)
Returns INT
As
Begin
Declare @result as int = 0
;WITH ctetable(RevisionNumber) as
(
SELECT RevisionNumber
FROM Purchasing.PurchaseOrderHeader poh
where poh.Status = @status
),
finalCte As
(
SELECT RevisionNumber FROM ctetable
)
Select @result = count(RevisionNumber) from finalCte
return @result;
End
GO
SELECT PURCHASING.FOO(4) as result;
result|
------+
3689|
Snowflake¶
CREATE OR REPLACE FUNCTION PURCHASING.FOO (STATUS INT)
Returns INT
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
AS
$$
WITH CTE1 AS
(
SELECT
0 AS RESULT
),
ctetable (
RevisionNumber
) as
(
SELECT
RevisionNumber
FROM
Purchasing.PurchaseOrderHeader poh
where
poh.Status = STATUS
),
finalCte As
(
SELECT
RevisionNumber
FROM
ctetable
),
CTE2 AS
(
Select
COUNT(RevisionNumber) AS RESULT from
finalCte
)
SELECT
RESULT
FROM
CTE2
$$;
SELECT
PURCHASING.FOO(4) as result;
RESULT|
------+
3689|
JavaScript UDFs に変換¶
複数のステートメントがあり、関数がデータベースにアクセスしない場合は、機能の等価性を維持しながら JavaScript 関数に変換できます
SQL Server¶
CREATE OR ALTER FUNCTION PURCHASING.GetFiscalYear
(
@DATE AS DATETIME
)
RETURNS INT
AS
BEGIN
DECLARE @FiscalYear AS INT
DECLARE @CurMonth AS INT
SET @CurMonth = DATEPART(M,@DATE)
SET @FiscalYear = DATEPART(YYYY, @DATE)
IF (@CurMonth >= 7)
BEGIN
SET @FiscalYear = @FiscalYear + 1
END
RETURN @FiscalYear
END
GO
SELECT PURCHASING.GetFiscalYear('2020-10-10') as DATE;
CREATE OR ALTER FUNCTION PURCHASING.[getCleanChargeCode]
(
@ChargeCode varchar(50)
)
returns varchar(50) as
begin
declare @CleanChargeCode varchar(50),@Len int,@Pos int=2
set @Pos=LEN(@ChargeCode)-1
while @Pos > 1
begin
set @CleanChargeCode=RIGHT(@ChargeCode,@Pos)
if TRY_CAST(@CleanChargeCode as bigint) is not null
return @CleanChargeCode
set @Pos=@Pos-1
end
set @Pos=LEN(@ChargeCode)-1
while @Pos > 1
begin
set @CleanChargeCode=LEFT(@ChargeCode,@Pos)
if TRY_CAST(@CleanChargeCode as bigint) is not null
return @CleanChargeCode
set @Pos=@Pos-1
end
return null
end
GO
SELECT PURCHASING.[getCleanChargeCode]('16test') AS CleanChargeCode;
DATE|
----+
2021|
CleanChargeCode|
---------------+
16 |
Snowflake¶
--** SSC-FDM-0029 - USER DEFINED FUNCTION WAS TRANSFORMED TO SNOWFLAKE PROCEDURE **
CREATE OR REPLACE PROCEDURE PURCHASING.GetFiscalYear (DATE TIMESTAMP_NTZ(3))
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
DECLARE
FISCALYEAR INT;
CURMONTH INT;
BEGIN
CURMONTH := DATE_PART(month, :DATE :: TIMESTAMP);
FISCALYEAR := DATE_PART(year, :DATE :: TIMESTAMP);
IF ((:CURMONTH >= 7)) THEN
BEGIN
FISCALYEAR := :FISCALYEAR + 1;
END;
END IF;
RETURN :FISCALYEAR;
END;
$$;
SELECT
!!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'GetFiscalYear' NODE ***/!!!
PURCHASING.GetFiscalYear('2020-10-10') as DATE;
--** SSC-FDM-0029 - USER DEFINED FUNCTION WAS TRANSFORMED TO SNOWFLAKE PROCEDURE **
CREATE OR REPLACE PROCEDURE PURCHASING.getCleanChargeCode (CHARGECODE STRING)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
DECLARE
CLEANCHARGECODE VARCHAR(50);
LEN INT;
POS INT := 2;
BEGIN
POS := LEN(:CHARGECODE)-1;
WHILE (:POS > 1) LOOP
CLEANCHARGECODE := RIGHT(:CHARGECODE, :POS);
IF (TRY_CAST(:CLEANCHARGECODE as BIGINT) is not null) THEN
RETURN :CLEANCHARGECODE;
END IF;
POS := :POS -1;
END LOOP;
POS := LEN(:CHARGECODE)-1;
WHILE (:POS > 1) LOOP
CLEANCHARGECODE := LEFT(:CHARGECODE, :POS);
IF (TRY_CAST(:CLEANCHARGECODE as BIGINT) is not null) THEN
RETURN :CLEANCHARGECODE;
END IF;
POS := :POS -1;
END LOOP;
RETURN null;
END;
$$;
SELECT
PURCHASING.getCleanChargeCode('16test') !!!RESOLVE EWI!!! /*** SSC-EWI-0067 - UDF WAS TRANSFORMED TO SNOWFLAKE PROCEDURE, CALLING PROCEDURES INSIDE QUERIES IS NOT SUPPORTED ***/!!! AS CleanChargeCode;
DATE |
------+
2021.0|
CLEANCHARGECODE|
---------------+
16 |
既知の問題¶
警告
ユーザー定義関数を使用して、データベースの状態を変更するアクションを実行することはできません
警告
ユーザー定義関数に、テーブルをターゲットとする OUTPUT INTO 句を含めることはできません
警告
ユーザー定義関数は、CURSOR を DECLARE、OPEN、FETCH、CLOSE、DEALLOCATE することはできません。カーソルを使用する必要がある場合は、ストアドプロシージャを使用してください。
警告
ユーザー定義関数は、データベースへの呼び出しが少なくとも1つある場合、WHILE のようなフロー制御ステートメントを実行できません
警告
ストアドプロシージャに変換された他のユーザー定義関数への参照を持つユーザー定義関数も、ストアドプロシージャに変換されます。
警告
SQL では、@@ROWCOUNT を使用するユーザー定義関数はサポートされていないため、機能的等価性を保つためにストアドプロシージャに変換する必要があります。
警告
変数を自分自身に割り当てる SELECT ステートメントを持つユーザー定義関数は、Snowflakeではサポートされていません。SELECT @local_variable もご参照ください
サポートされていないすべてのケースについては、関連する EWIs と以下のパターンを確認して、推奨事項と可能な回避策を入手してください。
クエリに付随するif/elseステートメント以外の条件¶
次のシナリオでは、他のクエリと一緒に「whileステートメント」を使用します。この例の問題点は、メインセレクトの WITH 句の中でwhileステートメントを CTE に変換する方法がないことです。このため、同じロジックを維持するために、このステートメントを JavaScript プロシージャに変換せざるを得ません。
SQL Server¶
CREATE OR ALTER FUNCTION PURCHASING.FOO()
RETURNS INT
AS
BEGIN
DECLARE @i int = 0, @p int;
Select @p = COUNT(*) FROM PURCHASING.VENDOR
WHILE (@p < 1000)
BEGIN
SET @i = @i + 1
SET @p = @p + @i
END
IF (@i = 6)
RETURN 1
RETURN @p
END
GO
SELECT PURCHASING.FOO() as result;
result|
------+
1007|
Snowflake
--** SSC-FDM-0029 - USER DEFINED FUNCTION WAS TRANSFORMED TO SNOWFLAKE PROCEDURE **
CREATE OR REPLACE PROCEDURE PURCHASING.FOO ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
DECLARE
I INT := 0;
P INT;
BEGIN
Select
COUNT(*)
INTO
:P
FROM
PURCHASING.VENDOR;
WHILE (:P < 1000) LOOP
I := :I + 1;
P := :P + :I;
END LOOP;
IF ((:I = 6)) THEN
RETURN 1;
END IF;
RETURN :P;
END;
$$;
SELECT
PURCHASING.FOO() !!!RESOLVE EWI!!! /*** SSC-EWI-0067 - UDF WAS TRANSFORMED TO SNOWFLAKE PROCEDURE, CALLING PROCEDURES INSIDE QUERIES IS NOT SUPPORTED ***/!!! as result;
FOO |
----+
1007|
行セットを反復処理して、独自の値を使用して変数を割り当てる¶
以下の例では、@names という変数を使って、列の複数の値を1つの文字列に連結しています。示されているように、変数は反復ごとに更新されますが、これは SnowFlake UDFs ではサポートされていません。このシナリオでは、関数を プロシージャ に変換する必要があります。
SQL Server
CREATE OR ALTER FUNCTION PURCHASING.FOO()
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @names varchar(8000)
SET @names = ''
SELECT @names = ISNULL(@names + ' ', '') + Name from Purchasing.Vendor v
return @names
END
GO
select PURCHASING.FOO() as names;
names |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Australia Bike Retailer Allenson Cycles Advanced Bicycles Trikes, Inc. Morgan Bike Accessories Cycling Master Chicago Rent-All Greenwood Athletic Company Compete Enterprises, Inc International Light Speed Training Systems Gardner Touring Cycles Internati|
Snowflakeクエリ
--** SSC-FDM-0029 - USER DEFINED FUNCTION WAS TRANSFORMED TO SNOWFLAKE PROCEDURE **
CREATE OR REPLACE PROCEDURE PURCHASING.FOO ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
DECLARE
NAMES VARCHAR(8000);
BEGIN
NAMES := '';
SELECT
NVL(:NAMES || ' ', 0) + Name
INTO
:NAMES
from
Purchasing.Vendor v;
RETURN :NAMES;
END;
$$;
select
PURCHASING.FOO() !!!RESOLVE EWI!!! /*** SSC-EWI-0067 - UDF WAS TRANSFORMED TO SNOWFLAKE PROCEDURE, CALLING PROCEDURES INSIDE QUERIES IS NOT SUPPORTED ***/!!! as names;
警告
上記のシナリオについて、以下の制限を考慮してください。
SELECT、INSERT、DELETE、UPDATE、MERGEなどの DML クエリ内でのユーザー定義関数の呼び出しはすべて失敗します。これらのクエリ内でのストアドプロシージャの呼び出しは許可されていないためです。プロシージャの内部でユーザー定義関数を呼び出す場合は、その前に
CALLキーワードを付ける必要があります。COMPUTED COLUMNS で使用されるユーザー定義関数は実行中に失敗します。
関連 EWIs¶
SSC-EWI-0067: UDF がSnowflakeプロシージャに変換され、クエリ内でのプロシージャの呼び出しがサポートされていません。
SSC-EWI-0073: 機能同等性レビュー保留中。
SSC-FDM-0029: ユーザー定義関数がSnowflakeプロシージャに変換されました。