SnowConvert: Teradata SQL から JavaScript (プロシージャ)¶
GET DIAGNOSTICS EXCEPTION¶
説明¶
GET DIAGNOSTICS は診断エリアから成功、例外、完了条件に関する情報を取得します。
Teradata GET DIAGNOSTICS に関する詳細情報は、 こちら をご覧ください。
GET DIAGNOSTICS
{
[ EXCEPTION < condition_number >
[ < parameter_name | variable_name > = < information_item > ]...
]
|
[ < parameter_name | variable_name > = < information_item > ]...
}
注釈
わかりやすくするため、出力コードの一部を省略しています。
サンプルソースパターン¶
Teradata ¶
-- Additional Params: -t JavaScript
CREATE PROCEDURE getDiagnosticsSample ()
BEGIN
DECLARE V_MESSAGE, V_CODE VARCHAR(200);
DECLARE V_Result INTEGER;
SELECT c1 INTO V_Result FROM tab1;
GET DIAGNOSTICS EXCEPTION 1 V_MESSAGE = MESSAGE_TEXT;
END;
Snowflake ¶
CREATE OR REPLACE PROCEDURE getDiagnosticsSample ()
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
EXECUTE AS CALLER
AS
$$
// SnowConvert Helpers Code section is omitted.
var V_MESSAGE;
var V_CODE;
var V_RESULT;
EXEC(`SELECT c1 FROM tab1`,[]);
[V_RESULT] = INTO();
V_MESSAGE = MESSAGE_TEXT;
$$;
既知の問題¶
サポートされていない条件属性ステートメント
CLASS_ORIGIN
CONDITION_IDENTIFIER
CONDITION_NUMBER
MESSAGE_LENGTH
RETURNED_SQLSTATE
SUBCLASS_ORIGIN
マクロ¶
説明¶
Teradataの MACRO ステートメントはSnowflake MACRO 構文に翻訳されます。
Teradata MACRO に関する詳細情報については、 こちら をご覧ください。
注釈
わかりやすくするため、出力コードの一部を省略しています。
サンプルソースパターン¶
マクロ変換の作成¶
Teradata
-- Additional Params: -t JavaScript
CREATE MACRO new_table (col1 INTEGER, col2 VARCHAR(12))
AS
(
insert into table1 (col1, col2) values (:col1, :col2);
select * from table1 where col1 = :col1;
);
Snowflake
CREATE OR REPLACE PROCEDURE new_table (COL1 FLOAT, COL2 VARCHAR(12))
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
EXECUTE AS CALLER
AS
$$
// SnowConvert Helpers Code section is omitted.
// ** SSC-EWI-0022 - ONE OR MORE IDENTIFIERS IN THIS STATEMENT WERE CONSIDERED PARAMETERS BY DEFAULT. REFERENCED TABLE NOT FOUND. **
EXEC(`INSERT INTO table1 (col1, col2)
VALUES (:1, :2)`,[COL1,COL2]);
INSERT_TEMP(`SELECT * from table1 where col1 = :1`,[COL1]);
return tablelist;
$$;
既知の問題¶
1.マクロからストアプロシージャへの変換¶
Snowflakeはマクロをサポートしていないため、Teradataマクロはストアドプロシージャに変換されます。
関連 EWIs¶
SSC-EWI-0022: このステートメントに含まれる1つ以上の識別子がデフォルトでパラメーターとみなされていました。
プロシージャ¶
説明¶
Teradataの PROCEDURE ステートメントはSnowflake PROCEDURE 構文に翻訳されます。
Teradata PROCEDURE の詳細情報については、 こちらをクリック してください。
サンプルソースパターン¶
プロシージャ変換の作成¶
SnowConvert プロシージャヘルパーリージョン¶
すべてのプロシージャは、 SnowConvert ヘルパーコードと呼ばれるリージョンを含んでいます。これらは、 JavaScript のネイティブではないTeradataの機能をエミュレートするのに役立つ変数と関数のセットです。プロシージャのサンプルコードでは、わかりやすくするため、このリージョンは示しません。EXEC のように常に追加されるヘルパーもあれば、 FETCH、 INTO などのようにオンデマンドで追加されるヘルパーもあります。
リージョンヘルパーのコードは以下の通りです。
// REGION SnowConvert Helpers Code
var HANDLE_NOTFOUND;
var fetch = (count,rows,stmt) => (count && rows.next() && Array.apply(null,Array(stmt.getColumnCount())).map((_,i) => rows.getColumnValue(i + 1))) || [];
var _RS, ROW_COUNT, _ROWS, MESSAGE_TEXT, SQLCODE = 0, SQLSTATE = '00000', ERROR_HANDLERS, ACTIVITY_COUNT = 0, INTO, _OUTQUERIES = [], DYNAMIC_RESULTS = 9;
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 = function (stmt,binds,noCatch,catchFunction,opts) {
try {
binds = binds ? binds.map(fixBind) : binds;
_RS = snowflake.createStatement({
sqlText : stmt,
binds : binds
});
_ROWS = _RS.execute();
ROW_COUNT = _RS.getRowCount();
ACTIVITY_COUNT = _RS.getNumRowsAffected();
HANDLE_NOTFOUND && HANDLE_NOTFOUND(_RS);
if (INTO) return {
INTO : function () {
return INTO();
}
};
if (_OUTQUERIES.length < DYNAMIC_RESULTS) _OUTQUERIES.push(_ROWS.getQueryId());
if (opts && opts.temp) return _ROWS.getQueryId();
} catch(error) {
MESSAGE_TEXT = error.message;
SQLCODE = error.code;
SQLSTATE = error.state;
var msg = `ERROR CODE: ${SQLCODE} SQLSTATE: ${SQLSTATE} MESSAGE: ${MESSAGE_TEXT}`;
if (catchFunction) catchFunction(error);
if (!noCatch && ERROR_HANDLERS) ERROR_HANDLERS(error); else throw new Error(msg);
}
};
var CURSOR = function (stmt,binds,withReturn) {
var rs, rows, row_count, opened = false, resultsetTable = '', self = this;
this.CURRENT = new Object;
this.INTO = function () {
return self.res;
};
this.OPEN = function (usingParams) {
try {
if (usingParams) binds = usingParams;
if (binds instanceof Function) binds = binds();
var finalBinds = binds && binds.map(fixBind);
var finalStmt = stmt instanceof Function ? stmt() : stmt;
if (withReturn) {
resultsetTable = EXEC(finalStmt,finalBinds,true,null,{
temp : true
});
finalStmt = `SELECT * FROM TABLE(RESULT_SCAN('${resultsetTable}'))`;
finalBinds = [];
}
rs = snowflake.createStatement({
sqlText : finalStmt,
binds : finalBinds
});
rows = rs.execute();
row_count = rs.getRowCount();
ACTIVITY_COUNT = rs.getRowCount();
opened = true;
return this;
} catch(error) {
ERROR_HANDLERS && ERROR_HANDLERS(error);
}
};
this.NEXT = function () {
if (row_count && rows.next()) {
this.CURRENT = new Object;
for(let i = 1;i <= rs.getColumnCount();i++) {
(this.CURRENT)[rs.getColumnName(i)] = rows.getColumnValue(i);
}
return true;
} else return false;
};
this.FETCH = function () {
self.res = [];
self.res = fetch(row_count,rows,rs);
if (opened) if (self.res.length > 0) {
SQLCODE = 0;
SQLSTATE = '00000';
} else {
SQLCODE = 7362;
SQLSTATE = '02000';
var fetchError = new Error('There are not rows in the response');
fetchError.code = SQLCODE;
fetchError.state = SQLSTATE;
if (ERROR_HANDLERS) ERROR_HANDLERS(fetchError);
} else {
SQLCODE = 7631;
SQLSTATE = '24501';
}
return self.res && self.res.length > 0;
};
this.CLOSE = function () {
if (withReturn && _OUTQUERIES.includes(resultsetTable)) {
_OUTQUERIES.splice(_OUTQUERIES.indexOf(resultsetTable),1);
}
rs = rows = row_count = undefined;
opened = false;
resultsetTable = '';
};
};
let PROCRESULTS = (...OUTPARAMS) => JSON.stringify([...OUTPARAMS,[..._OUTQUERIES]]);
// END REGION
Teradata
-- Additional Params: -t JavaScript
REPLACE PROCEDURE my_procedure (in param1 VARCHAR(10), OUT param2 BLOB)
dynamic result sets 9
SELECT * FROM table1;
Snowflake
CREATE OR REPLACE PROCEDURE my_procedure (PARAM1 STRING, PARAM2 BINARY /*** SSC-FDM-TD0001 - COLUMN CONVERTED FROM BLOB DATA TYPE ***/)
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
EXECUTE AS CALLER
AS
$$
// SnowConvert Helpers Code section is omitted.
EXEC(`SELECT * FROM table1`,[]);
return PROCRESULTS(PARAM2);
$$;
注意: Snowflakeのストアドプロシージャの本文は、javascript関数として実行されます。
If¶
IF ステートメント の変換は以下の通りです。
Teradata
IF value = 2 THEN
Snowflake
if(value == 2){
}
ケース¶
Caseステートメント の変換は以下の通りです。
Teradata
case value
when 0 then
select * from table1
else
update table1 set name = "SpecificValue" where id = value;
end case
Snowflake
switch(value) {
case 0:EXEC(`SELECT * FROM PUBLIC.table1`,[]);
break;
default:EXEC(`UPDATE PUBLIC.table1 set name = "SpecificValue" where id = value`,[]);
break;
}
カーソル宣言、 OPEN、 FETCH および CLOSE¶
cursorステートメント の変換は以下の通りです。
Teradata
-- Additional Params: -t JavaScript
CREATE PROCEDURE procedure1()
DYNAMIC RESULT SETS 2
BEGIN
-------- Local variables --------
DECLARE sql_cmd VARCHAR(20000) DEFAULT ' ';
DECLARE num_cols INTEGER;
------- Declare cursor with return only-------
DECLARE resultset CURSOR WITH RETURN ONLY FOR firststatement;
------- Declare cursor -------
DECLARE cur2 CURSOR FOR SELECT COUNT(columnname) FROM table1;
-------- Set --------
SET sql_cmd='sel * from table1';
-------- Prepare cursor --------
PREPARE firststatement FROM sql_cmd;
-------- Open cursors --------
OPEN resultset;
OPEN cur1;
-------- Fetch -------------
FETCH cur1 INTO val1, val2;
-------- Close cursor --------
CLOSE cur1;
END;
Snowflake
CREATE OR REPLACE PROCEDURE procedure1 ()
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
EXECUTE AS CALLER
AS
$$
// SnowConvert Helpers Code section is omitted.
//------ Local variables --------
var SQL_CMD = ` `;
var NUM_COLS;
var RESULTSET = new CURSOR(() => FIRSTSTATEMENT,[],true);
//----- Declare cursor -------
var CUR2 = new CURSOR(`SELECT COUNT(columnname) FROM table1`,[],false);
//------ Set --------
SQL_CMD = `SELECT * from table1`;
//------ Prepare cursor --------
var FIRSTSTATEMENT = SQL_CMD;
//------ Open cursors --------
RESULTSET.OPEN();
CUR1.OPEN();
//------ Fetch -------------
CUR1.FETCH() && ([val1,val2] = CUR1.INTO());
//------ Close cursor --------
CUR1.CLOSE();
return PROCRESULTS();
$$;
While¶
whileステートメント の変換は以下の通りです。
Teradata
while (counter < 10) do
set counter = counter + 1;
Snowflake¶
while ( counter < 10) {
counter = counter + 1;
}
セキュリティ¶
securityステートメント の変換は以下の通りです。
Teradata |
Snowflake |
---|---|
SQL SECURITY CREATOR |
EXECUTE AS OWNER |
SQL SECURITY INVOKER |
EXECUTE AS CALLER |
SQL SECURITY DEFINER |
EXECUTE AS OWNER |
FOR-CURSOR-FOR ループ¶
FOR-CURSOR-FOR ループ の変換は以下の通りです。
Teradata
-- Additional Params: -t JavaScript
REPLACE PROCEDURE Database1.Proc1()
BEGIN
DECLARE lNumber INTEGER DEFAULT 1;
FOR class1 AS class2 CURSOR FOR
SELECT COL0,
TRIM(COL1) AS COL1ALIAS,
TRIM(COL2),
COL3
FROM someDb.prefixCol
DO
INSERT INTO TempDB.Table1 (:lgNumber, :lNumber, (',' || :class1.ClassCD || '_Ind CHAR(1) NOT NULL'));
SET lNumber = lNumber + 1;
END FOR;
END;
Snowflake
CREATE OR REPLACE PROCEDURE Database1.Proc1 ()
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
EXECUTE AS CALLER
AS
$$
// SnowConvert Helpers Code section is omitted.
var LNUMBER = 1;
/*** SSC-EWI-0023 - PERFORMANCE REVIEW - THIS LOOP CONTAINS AN INSERT, DELETE OR UPDATE STATEMENT ***/
for(var CLASS2 = new CURSOR(`SELECT
COL0,
TRIM(COL1) AS COL1ALIAS,
TRIM(COL2),
COL3
FROM
someDb.prefixCol`,[],false).OPEN();CLASS2.NEXT();) {
let CLASS1 = CLASS2.CURRENT;
EXEC(`INSERT INTO TempDB.Table1
VALUES (:lgNumber, :1, (',' || :
!!!RESOLVE EWI!!! /*** SSC-EWI-0026 - THE VARIABLE class1.ClassCD MAY REQUIRE A CAST TO DATE, TIME OR TIMESTAMP ***/!!!
:2 || '_Ind CHAR(1) NOT NULL'))`,[LNUMBER,CLASS1.CLASSCD]);
LNUMBER = LNUMBER + 1;
}
CLASS2.CLOSE();
$$;
注意: Teradataのプロシージャに存在する FOR ループは、その機能をエミュレートするjavascript内の FOR ブロックに変換されます。
ステートメント内部で参照されるプロシージャパラメーターと変数¶
プロシージャのステートメント内で参照されるプロシージャパラメーターと変数の変換は次のようになります。
Teradata
-- Additional Params: -t JavaScript
REPLACE PROCEDURE PROC1 (param1 INTEGER, param2 VARCHAR(30))
BEGIN
DECLARE var1 VARCHAR(1024);
DECLARE var2 SMALLINT;
DECLARE weekstart date;
set weekstart= '2019-03-03';
set var1 = 'something';
set var2 = 123;
SELECT * FROM TABLE1 WHERE SOMETHING = :param1;
SELECT * FROM TABLE1 WHERE var1 = var1 AND date1 = weekstart AND param2 = :param2;
INSERT INTO TABLE2 (col1, col2, col3, col4, col5) VALUES (:param1, :param2, var1, var2, weekstart);
END;
Snowflake
CREATE OR REPLACE PROCEDURE PROC1 (PARAM1 FLOAT, PARAM2 STRING)
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
EXECUTE AS CALLER
AS
$$
// SnowConvert Helpers Code section is omitted.
var VAR1;
var VAR2;
var WEEKSTART;
WEEKSTART = `2019-03-03`;
VAR1 = `something`;
VAR2 = 123;
// ** SSC-EWI-0022 - ONE OR MORE IDENTIFIERS IN THIS STATEMENT WERE CONSIDERED PARAMETERS BY DEFAULT. REFERENCED TABLE NOT FOUND. **
EXEC(`SELECT * FROM TABLE1 WHERE SOMETHING = :1`,[PARAM1]);
// ** SSC-EWI-0022 - ONE OR MORE IDENTIFIERS IN THIS STATEMENT WERE CONSIDERED PARAMETERS BY DEFAULT. REFERENCED TABLE NOT FOUND. **
EXEC(`SELECT * FROM TABLE1 WHERE :1 = :1 AND date1 = :2 AND RTRIM(param2) = :3`,[VAR1,WEEKSTART,PARAM2]);
// ** SSC-EWI-0022 - ONE OR MORE IDENTIFIERS IN THIS STATEMENT WERE CONSIDERED PARAMETERS BY DEFAULT. REFERENCED TABLE NOT FOUND. **
EXEC(`INSERT INTO TABLE2 (col1, col2, col3, col4, col5) VALUES (:1, :2, :3, :4, :5)`,[PARAM1,PARAM2,VAR1,VAR2,WEEKSTART]);
$$;
注意: プロシージャのパラメーターまたはプロシージャの内部で宣言された変数が、変換が必要なTeradataステートメントの内部で参照されるときはいつでも、 この参照は、元の参照の機能を保持するために、結果のテキストからエスケープされます。
終了¶
Javascriptでは、 break
にパラメーターを追加することで、Teradata LEAVE
ジャンプの動作をエミュレートすることができます。
ラベルは、Javascriptのラベル付きステートメントを使用してエミュレートすることもできます。
LEAVE ステートメント の変換は以下の通りです。
Teradata
-- Additional Params: -t JavaScript
REPLACE PROCEDURE PROC1 ()
BEGIN
DECLARE v_propval VARCHAR(1024);
DECLARE Cur1 cursor for
Select
propID
from viewName.viewCol
where propval is not null;
LABEL_WHILE:
WHILE (SQLCODE = 0)
DO
IF (SQLSTATE = '02000' )
THEN LEAVE LABEL_WHILE;
END IF;
LABEL_INNER_WHILE:
WHILE (SQLCODE = 0)
DO
IF (SQLSTATE = '02000' )
THEN LEAVE LABEL_INNER_WHILE;
END IF;
END WHILE LABEL_INNER_WHILE;
SELECT * FROM TABLE1;
END WHILE L1;
END;
Snowflake
CREATE OR REPLACE PROCEDURE PROC1 ()
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
EXECUTE AS CALLER
AS
$$
// SnowConvert Helpers Code section is omitted.
var V_PROPVAL;
var CUR1 = new CURSOR(`SELECT propID from viewName.viewCol
where
propval is not null`,[],false);
LABEL_WHILE: {
while ( SQLCODE == 0 ) {
if (SQLSTATE == `02000`) {
break LABEL_WHILE;
}
LABEL_INNER_WHILE: {
while ( SQLCODE == 0 ) {
if (SQLSTATE == `02000`) {
break LABEL_INNER_WHILE;
}
}
}
EXEC(`SELECT * FROM TABLE1`,[]);
}
}
$$;
プロシージャから結果を取得する¶
翻訳の説明¶
Teradataでは、プロシージャからデータを返す方法は2つあります。1つ目は出力パラメーターを使用する方法で、2つ目は 動的結果セット と カーソル を使用する方法です。次の例に両方を示します。それぞれの重要なポイントについて、以下に説明します。
ストアドプロシージャからデータを返す例¶
Teradata
-- Additional Params: -t JavaScript
REPLACE PROCEDURE Procedure1(OUT P1 INTEGER)
DYNAMIC RESULT SETS 2
BEGIN
DECLARE SQL_CMD,SQL_CMD_1 VARCHAR(20000) DEFAULT ' ';
DECLARE RESULTSET CURSOR WITH RETURN ONLY FOR FIRSTSTATEMENT;
SET SQL_CMD = 'SEL * FROM EMPLOYEE';
PREPARE FIRSTSTATEMENT FROM SQL_CMD;
OPEN RESULTSET;
SET P1 = (SEL CAST(AVG(AGE) AS INTEGER) FROM EMPLOYEE);
END;
Snowflake
CREATE OR REPLACE PROCEDURE Procedure1 (P1 FLOAT)
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
EXECUTE AS CALLER
AS
$$
// SnowConvert Helpers Code section is omitted.
var SQL_CMD = ` `;
var SQL_CMD_1 = ` `;
var RESULTSET = new CURSOR(() => FIRSTSTATEMENT,[],true);
SQL_CMD = `SELECT * FROM EMPLOYEE`;
var FIRSTSTATEMENT = SQL_CMD;
RESULTSET.OPEN();
EXEC(`(SELECT CAST(TRUNC(AVG(AGE)) AS INTEGER) FROM EMPLOYEE)`,[]);
var subQueryVariable0;
[subQueryVariable0] = INTO();
P1 = subQueryVariable0;
return PROCRESULTS(P1);
$$;
この変換された SQL では、いくつかの変換が行われます。
DYNAMIC RESULT SETS 2
定義はDYNAMIC_RESULTS
変数に変換されます。
var DYNAMIC_RESULTS = 2;
WITH RETURN
属性を持つカーソルがオープンされると(つまりクエリが実行されると)、そのクエリ ID は後で返されるために_ OUTQUERIES
コレクションに格納されます。クエリIDは、 Snowflakeストアドプロシージャ用 JavaScript API で提供されているgetQueryId()
関数によって取得されます。最初のk-クエリ-IDs のみがコレクションに格納されます。kは
DYNAMIC_RESULTS
変数の値です。これはTeradataの動作をエミュレートするためで、ストアドプロシージャ内でさらに多くのカーソルがオープンされたとしても、最初のkのオープンされたカーソルのみを返します。DECLARE CURSOR WITH RETURN
とPREPARE
の組み合わせは、以下のように翻訳されます。
var RESULTSET = new CURSOR(() => FIRSTSTATEMENT,[],true);
出力パラメーターは、プロシージャのreturnステートメントを介してサポートされます。各出力パラメーターの値と
_OUTQUERIES
コレクションを含む配列が作成されます。PROCRESULTS
関数は、この配列の作成と入力を処理します。詳細は PROCRESULTS()ヘルパーを参照してください。
return PROCRESULTS(P1);
ストアドプロシージャからデータを取得する例¶
出力パラメーターとクエリ IDs がプロシージャから返される場合、以下のように、2番目のプロシージャが1番目のプロシージャを呼び出してこれらの値を取得することができます。
Teradata
-- Additional Params: -t JavaScript
CREATE PROCEDURE Procedure2()
BEGIN
DECLARE x INTEGER;
CALL Procedure1(x);
END;
Snowflake
CREATE OR REPLACE PROCEDURE Procedure2 ()
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
EXECUTE AS CALLER
AS
$$
// SnowConvert Helpers Code section is omitted.
var X;
EXEC(`CALL Procedure1(:1)`,[X]);
$$;
Procedure1
からの引数P1
の値が返され、X
変数に格納されます。Procedure1
から返された_ OUTQUERIES
は、resultset
変数に格納されます。
注釈
この動作は、 INOUT パラメーターにも適用されます。
既知の問題¶
問題は見つかりませんでした。
関連 EWIs¶
SSC-EWI-0022: このステートメントに含まれる1つ以上の識別子がデフォルトでパラメーターとみなされていました。
SSC-EWI-0023: パフォーマンスレビュー - ループには挿入、削除、更新ステートメントが含まれます。
SSC-EWI-0026: 変数は日付、時刻、タイムスタンプにキャストする必要があります。
SSC-FDM-TD0001: このメッセージは SnowConvert がデータ型 BLOB を見つけたときに表示されます。