SnowConvert : DDLs Transact¶
Référence de traduction pour toutes les instructions DDL qui sont précédées du mot CREATE
.
Index¶
Applies to
[x] SQL Server
[x] Azure Synapse Analytics
Avertissement
Actuellement, l’instruction _ Create Index _ n’est pas convertie mais elle est analysée. De même, si votre code source comporte des instructions create index
, celles-ci seront prises en compte dans le _ rapport d’évaluation. _
Exemple de Create Index
CREATE INDEX my_index_name ON my_table (column1, column2);
Note
Pour des raisons architecturales, Snowflake ne prend pas en charge les index. Par conséquent, SnowConvert supprimera tout le code lié à la création d’index. Snowflake crée automatiquement des micro-partitions pour chaque table afin d’accélérer les performances des opérations DML. L’utilisateur n’a pas à se préoccuper de la création ou de la gestion de ces micro-partitions.
En général, cela suffit pour obtenir de très bonnes performances de requête, mais il est possible de les améliorer en créant des clés de clustering de données. La page officielle de Snowflake fournit plus d’informations sur les micro-partitions et le clustering de données.
Vue matérialisée¶
Applies to
[x] Azure Synapse Analytics
Description ¶
Dans Snowconvert, les vues matérialisées sont transformées en tables dynamiques Snowflake. Pour configurer correctement les tables dynamiques, deux paramètres essentiels doivent être définis : TARGET\LAG et WAREHOUSE. Si ces paramètres ne sont pas spécifiés dans les options de configuration, Snowconvert utilisera par défaut des valeurs prédéfinies lors de la conversion, comme le montre l’exemple ci-dessous.
Pour plus d’informations sur les vues matérialisées, cliquez ici.
Pour plus de détails sur les paramètres nécessaires pour les tables dynamiques, cliquez ici.
Modèles d’échantillons de sources¶
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;
Problèmes connus¶
Aucune erreur connue n’a été détectée pour le moment.
Procédures¶
Applies to
[x] SQL Server
[x] Azure Synapse Analytics
Note
Certaines parties du code de sortie sont omises pour des raisons de clarté.
1. CREATE PROCEDURE Translation¶
Snowflake CREATE PROCEDURE
est défini en syntaxe SQL tandis que ses instructions internes sont définies en JavaScript.
Code source :
-- Additional Params: -t JavaScript
CREATE PROCEDURE HumanResources.uspGetAllEmployees
@FirstName NVARCHAR(50),
@Age INT
AS
-- TSQL Statements and queries...
GO
Code traduit :
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 du paramètre¶
Les types de données des paramètres sont traduits en équivalents Snowflake. Voir aussi Types de données.
Assistant EXEC¶
Pour pouvoir exécuter les instructions d’une procédure dans l’environnement SnowFlake, ces instructions doivent être prétraitées et adaptées pour refléter leur exécution dans plusieurs variables spécifiques à la langue source.
SnowConvert traduit automatiquement les instructions prises en charge et utilise un assistant EXEC. Ce fournisseur offre des possibilités d’accès et de mise à jour à de nombreuses variables qui simulent l’exécution de ces instructions dans leur environnement natif.
Par exemple, vous pouvez constater que, dans les procédures migrées, un bloc de code est toujours ajouté. Nous allons expliquer la structure de base de ce code dans la section suivante. Gardez à l’esprit que nous évaluons en permanence de nouvelles façons d’optimiser les transformations et les outils d’assistance nécessaires.
Structure¶
La structure de base de l’aide EXEC est la suivante :
Section de déclaration des variables : On déclare ici les différentes variables ou objets qui contiendront les valeurs associées à l’exécution des instructions à l’intérieur de la procédure. Il s’agit de valeurs telles que le nombre de lignes affectées par une instruction, voire le jeu de résultats lui-même.
Déclaration de la fonction fixBind : Il s’agit d’une fonction auxiliaire utilisée pour corriger les liaisons lorsqu’elles sont de type Date.
Déclaration de la fonction EXEC : Il s’agit de la fonction d’assistance principale de EXEC. Elle reçoit l’instruction à exécuter, le tableau des liaisons (essentiellement les variables ou paramètres qui peuvent être modifiés par l’exécution et qui nécessitent la permanence des données tout au long de l’exécution de la procédure), le drapeau noCatch qui détermine si ERROR_HANDLERS doivent être utilisés, et la fonction catchFunction pour l’exécution du code personnalisé en cas d’exception lors de l’exécution de l’instruction. Le corps de la fonction EXEC est très simple : il s’agit d’exécuter l’instruction et de stocker toutes les données utiles produites par son exécution, le tout à l’intérieur d’un bloc de gestion des erreurs.
ERROR VARS : Le bloc de capture EXEC établit une liste de variables d’erreur telles que
MESSAGE_TEXT
,SQLCODE
,SQLSTATE
,PROC_NAME
etERROR_LINE
qui pourraient être utilisées pour récupérer les valeurs des fonctions définies par l’utilisateur, afin d’émuler le comportement des fonctions intégrées SQL Server ERROR_LINE, ERROR_MESSAGE, ERROR_NUMBER, ERROR_PROCEDURE et ERROR_STATE . Une fois que l’ensemble de ces variables a été définie sur une valeur, la fonction définie par l’utilisateurUPDATE_ERROR_VARS
sera chargée de mettre à jour certaines variables d’environnement avec les valeurs d’erreur, afin d’y avoir accès dans le champ d’application SQL.
Code¶
Le bloc de code suivant représente l’assitant EXEC à l’intérieur d’une procédure :
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;
}
}
};
Exemple EXEC simple
Voici un exemple simple d’un appel à EXEC à l’intérieur d’une procédure stockée
Code source
-- 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
Code attendu
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 dans une procédure stockée avec un paramètre
Dans cet exemple, la commande EXEC se trouve à l’intérieur d’une procédure stockée et reçoit une valeur de paramètre
Code source
-- 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
Code attendu
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 invoquant une procédure stockée avec un paramètre
Dans cet exemple, EXEC invoque une autre procédure stockée et ajoute un paramètre
Code source
-- 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
Code attendu
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]);
$$;
Paramètres avec valeur par défaut.¶
Dans SqlServer, certains paramètres peuvent avoir une valeur par défaut au cas où ils ne seraient pas spécifiés lors de l’appel d’une procédure.
Par exemple
CREATE PROCEDURE PROC_WITH_DEFAULT_PARAMS1
@PARAM1 INT = 0, @PARAM2 INT = 0, @PARAM3 INT = 0, @PARAM4 INT = 0
AS
BEGIN
.
.
.
END
Traduit dans Snowflake par
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);
Assistant 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';
$$;
Assistant EXEC Insert Into¶
L’assistant Exec Insert into génère une fonction appelée Insert insertIntoTemporaryTable(sql).
Cette fonction permettra à la transformation pour INSERT INTO TABLE_NAME EXEC(...)
de TSQL à Snowflake d’imiter le comportement de l’instruction originale en insérant les données dans une table temporaire, puis en les réinsérant dans l’instruction originale.
Pour plus d’informations sur la manière dont le code de cette instruction est modifié, consultez la section relative à l’insertion dans l’exécution
Note
Ce code généré pour INSERT INTO EXEC, peut présenter des problèmes de performance lors du traitement des instructions EXECUTE contenant plusieurs requêtes.
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`)
Assistant LIKE¶
Dans le cas où une expression similaire est trouvée dans une procédure, par exemple
CREATE PROCEDURE ProcedureLike @VariableValue VARCHAR(50) AS
BEGIN
IF @VariableValue like '%c%'
BEGIN
Select AValue from ATable;
END;
END;
Comme l’intérieur de la procédure est transformé en javascript, l’expression like provoquera une erreur. Afin d’éviter et de conserver la fonctionnalité, une fonction est ajoutée au début de la procédure si une expression similaire est trouvée.
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;
}
Cette fonction permet de répliquer la fonctionnalité de l’expression like de sql. Voyons les différents cas où elle peut être utilisée
-- 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;
Dans le dernier code, il y a un normal like, un not like et un like avec échappement. La transformation sera
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`);
}
}
$$;
Notez que les fonctionnalités sont transformées en appels de fonctions
LIKE(VARIABLEVALUE,`%c%`)
!LIKE(VARIABLEVALUE,`%c%`)
LIKE(VARIABLEVALUE,`%c!%%`,`!`)
Les paramètres que la fonction LIKE reçoit sont les suivants :
L’expression en cours d’évaluation.
Le modèle de comparaison
S’il est présent, le caractère d’échappement, ce paramètre est facultatif.
Assistant Select¶
Génère une fonction appelée SELECT lorsqu’une valeur scalaire doit être définie dans une variable
-- 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;
Dans ce cas, il générera le code suivant avec l’assistant 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;
$$;
L’assistant SELECT pourrait également être utilisé pour insérer dans une valeur locale une valeur récupérée à partir d’une requête. L’assistant a été conçu spécifiquement pour prendre en charge le même comportement que SQL Server SELECT @local_variable. Le paramètre args
représente chaque opération appliquée à toutes les variables locales à l’intérieur de select. Voir aussi SELECT @Variable. Par exemple :
-- Additional Params: -t JavaScript
CREATE PROCEDURE [PROCEDURE1] AS
DECLARE @VAR1 int;
DECLARE @VAR2 int;
select @VAR1 = col1 + col2, @VAR2 += col1 from table1;
GO
Dans ce cas, les assignations de variables seront traduites en JavaScript
lambdas afin d’émuler le comportement de SQL Server.
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);
$$;
Assistant RAISERROR¶
Cette aide est générée lorsqu’il existe des utilisations d’un appel à RAISERROR dans le code source. Exemple :
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 exécute la commande UPDATE_ERROR_VARS_UDF afin de stocker la valeur du message d’erreur, la gravité et l’état en tant que variables d’environnement, au cas où elles devraient être utilisées en appelant l’une des fonctions intégrées ERROR. Enfin, le message d’erreur est envoyé dans le même format que celui de SQL Server.
Assistant de la fonction Identify¶
Cette aide est générée chaque fois que la fonction Identify est utilisée sur un Select Into à l’intérieur d’une procédure.
var IdentityHelper = (seed,increment) => {
var sequenceString = "`CREATE OR REPLACE SEQUENCE SnowConvert_Temp_Seq START = ${seed} INCREMENT = ${increment}`";
return EXEC(sequenceString);
Les paramètres de cet assistant sont les mêmes que ceux de la fonction originale, elle est créée afin de générer une séquence imitant le comportement de la fonction Identify dans TSQL, les changements apportés au code original sont les suivants :
Un appel de méthode supplémentaire à la fonction IdentityHelper en utilisant les mêmes paramètres que ceux trouvés dans le code source.
Et appel à la conception d’une fonction IDENTITY_UDF pour obtenir la valeur suivante dans la séquence.
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`);
Comme dans TSQL, si aucun paramètre n’est donné, les valeurs par défaut seront (1,1).
Aide à la procédure CALL¶
Cet assistant est généré chaque fois qu’il y a un appel à ce qui était auparavant une fonction définie par l’utilisateur, mais qui est maintenant une procédure à la suite du processus de traduction.
var CALL = (sql,binds = [],...args) => {
EXEC("CALL " + sql,binds);
_ROWS.next();
return (_ROWS.getColumnValue(1))[0];
};
L’objectif de cet assistant est d’encapsuler la logique requise pour appeler des procédures comme s’il s’agissait de fonctions.
N’oubliez pas que cette fonctionnalité est limitée, car les procédures ne peuvent pas être invoquées dans le cadre de requêtes telles que SELECT.
Exemple d’utilisation, en supposant que FooSelfAssign(@PAR INT)
ait été traduit en procédure :
// 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)`)])])]);
Notez que la traduction de VAR1 est très simple, mais que pour VAR4, le CALL le plus éloigné contient une liste avec le reste des CALLs, en tant qu’annonces.
Chaque CALL successif est traduit en une liaison, s’il est contenu dans un autre CALL.
2. Variables¶
DECLARE @Variable¶
Code source
DECLARE @product_list VARCHAR(MAX) = ' ';
DECLARE @Variable1 AS VARCHAR(100), @Variable2 AS VARCHAR(100);
Code traduit
let PRODUCT_LIST = ` `;
let VARIABLE1;
let VARIABLE2;
DECLARE @Variable Table¶
Dans ce cas, DECLARE est utilisé pour déclarer une table de variables, voyons un exemple.
-- 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;
Si nous exécutons ce code dans le serveur Sql, nous obtiendrons le résultat suivant
Voyons maintenant la transformation dans 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()`);
$$;
Notez que les lignes 61 à 67 sont les résultats de ces instructions à l’intérieur de la procédure.
La table de variables déclarées est transformée en table temporaire. Notez que le nom, qui dans le nom le caractère @ a été remplacé pour T_.
Si nous exécutons ce code dans Snowflake, nous n’obtiendrons aucun résultat. Il affichera simplement null. C’est parce que le dernier Select se trouve maintenant dans l’assistant EXEC. Alors, comment savoir si la table est là ?
Puisqu’elle a été créée en tant que table temporaire dans la procédure dans EXEC, nous pouvons effectuer une sélection dans cette table en dehors de la procédure.
Select * from PUBLIC.T_VariableNameTable;
Si nous exécutons cette instruction, nous obtiendrons le résultat suivant
SET @Variable¶
Pour l’instant, la variable Set est transformée en fonction de l’expression qu’elle contient dans sa partie droite.
Si l’expression a une transformation, elle sera transformée en son équivalent JavaScript.
Exemple
-- 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
Code traduit
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)`);
$$;
Comme vous pouvez le voir dans l’exemple, la valeur de la variable NOTSUPPORTED est commentée puisqu’elle n’est pas transformée pour le moment. Cela signifie que la transformation n’est pas encore terminée.
D’autres types d’ensembles sont commentés, par exemple les suivants
-- 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
Code traduit
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¶
Pour l’instant, SELECT @variable
est transformé en un simple select, en supprimant les assignations de variables et en conservant les expressions à droite de l’opérateur. Les opérations d’affectation des variables locales dans select seront remplacées par des fonctions arrow
qui représentent le même comportement que l’opération effectuée lors de l’affectation de la variable locale dans SQL Server
.
Entrée¶
-- Additional Params: -t JavaScript
CREATE PROCEDURE PROC1 AS
DECLARE @VAR1 int;
DECLARE @VAR2 int;
SELECT @VAR1 = COL1 + COL2, @VAR2 = COL3 FROM TABLE1;
GO
Sortie¶
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. Statements translation¶
SELECT¶
Forme de base¶
La forme de base SELECT n’a pas de liaisons, la traduction implique donc la création d’un appel à la fonction d’assistance EXEC, avec un paramètre.\ \ Par exemple :
-- Source code:
SELECT * FROM DEMO_TABLE_1;
// Translated code:
EXEC(`SELECT * FROM DEMO_TABLE_1`);
IF¶
Code source
IF Conditional_Expression
-- SQL Statement
ELSE IF Conditiona_Expression2
-- SQL Statement
ELSE
-- SQL Statement
Code traduit
if (Conditional_Expression) {
// SQL Statement
} else if (Conditional_Expression2) {
// SQL Statement
} else{
// SQL Statement
}
WHILE¶
Code source
WHILE ( Conditional_Expression )
BEGIN
-- SQL STATEMENTS
END;
Code traduit
while ( Conditional_Expression )
{
// SQL STATEMENTS
}
EXEC / EXECUTE¶
Code source
-- 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
Code traduit
-- 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¶
La transformation pour THROW garantit que le bloc catch qui reçoit l’erreur a accès aux informations spécifiées dans l’instruction d’origine.
Par exemple :
-- Case 1
THROW
-- Case 2
THROW 123, 'The error message', 1
-- Case 3
THROW @var1, @var2, @var3
Sera transformé en :
// Case 1
throw {};
// Case 2
throw { code: 123, message: "The error message", status: 1 };
// Case 3
throw { code: VAR1, message: VAR2, status: VAR3 };
RAISERROR¶
La fonction RAISERROR SQL Server n’est pas prise en charge par Snowflake. SnowConvert identifie toutes les utilisations afin de générer un assistant qui émule le comportement original. Exemple :
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¶
La transformation break/continue permet d’arrêter le flux du code ou de le poursuivre avec un autre bloc.
Par exemple :
-- Additional Params: -t JavaScript
CREATE PROCEDURE ProcSample
AS
BEGIN
IF @@ROWCOUNT > 0
Continue;
ELSE
BREAK;
END
Sera transformé en :
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¶
Le code est légèrement modifié car l’instruction INSERT INTO [Table] EXEC(...)
n’est pas prise en charge par Snowflake. Ceci nous permet de répliquer le comportement en ajoutant quelques lignes de code :
La première ligne ajoutée est un appel à
insertIntoTemporaryTable
où le code extrait de l’argument se trouve à l’intérieur deEXEC
, ce qui permet d’insérer le jeu de résultats dans une table temporaire. Pour plus d’informations sur cette fonction, consultez la section Assistant Into EXEC.L’Insert de Exec est supprimé du code et une requête récupère les résultats de EXEC dans la table temporaire.
SELECT * FROM MYDB.PUBLIC.SnowConvertPivotTemporaryTable
La dernière ligne ajoutée est une instruction DROP TABLE pour la table temporaire ajoutée.
DROP TABLE SnowConvertPivotTemporaryTable
Code source :
INSERT INTO #Table1
EXEC ('SELECT
Table1.ID
FROM Population');
INSERT INTO #Table1
EXEC (@DBTables);
Code traduit :
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 est transformée en commande BEGIN de Snowflake et insérée dans un appel à l’assistant EXEC.
L’assistant est chargé de l’exécution effective de l’application BEGIN.
Exemple :
-- Input code
BEGIN TRAN @transaction_name;
// Output code
EXEC(`BEGIN`, []);
COMMIT TRANSACTION¶
COMMIT TRANSACTION est transformée en commande COMMIT de Snowflake et insérée dans un appel à l’assistant EXEC.
L’assistant est chargé de l’exécution effective de l’application COMMIT.
Exemple :
-- Input code
COMMIT TRAN @transaction_name;
// Output code
EXEC(`COMMIT`, []);
ROLLBACK TRANSACTION¶
ROLLBACK TRANSACTION est transformée en commande ROLLBACK de Snowflake et insérée dans un appel à l’assistant EXEC.
L’assistant est chargé de l’exécution effective de l’application ROLLBACK.
Exemple :
-- Input code
ROLLBACK TRAN @transaction_name;
// Output code
EXEC(`ROLLBACK`, []);
WAITFOR DELAY¶
La clause WAITFOR DELAY est transformée en fonction SYSTEM$WAIT
de Snowflake. Le paramètre time_to_pass de DELAY est transformé en secondes, pour utilisation comme paramètre dans la fonction SYSTEM$WAIT
.
Les autres variantes de la clause WAITFOR ne sont pas prises en charge par Snowflake et sont donc signalées par le message correspondant.
Exemple :
-- 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¶
Comme CURSORS
n’est pas pris en charge par Snowflake, SnowConvert mappe leur fonctionnalité sur une aide JavaScript
qui émule le comportement original dans la plateforme cible. Exemple :
Entrée :¶
-- 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
Sortie :¶
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¶
Pour l’instant, le curseur Declare est simplement commenté.
Code source
DECLARE myCursor1 CURSOR FOR SELECT COL1 FROM TABLE1
Code traduit
let myCursor1 = new CURSOR(`SELECT COL1 FROM TABLE1`,() => []);
OPEN¶
Code source
OPEN myCursor1
OPEN GLOBAL myCursor2
Code traduit
myCursor1.OPEN();
myCursor2.OPEN()
FETCH¶
Code source
DECLARE @VALUE1 INT
FETCH NEXT FROM myCursor1 into @VALUE1
Code traduit
var VALUE1;
myCursor1.FETCH_NEXT();
VALUE1 = myCursor1.INTO();
CLOSE¶
Code source
CLOSE myCursor1
CLOSE GLOBAL myCursor2
Code traduit
myCursor1.CLOSE()
myCursor2.CLOSE()
DEALLOCATE¶
Code source
DEALLOCATE myCursor1
DEALLOCATE GLOBAL myCursor2
Code traduit
myCursor1.DEALLOCATE()
myCursor2.DEALLOCATE()
@@FETCH_STATUS¶
Code source
@@FETCH_STATUS
Code traduit
myCursor1.FETCH_STATUS()
@@CURSOR_ROWS¶
Code source
@@CURSOR_ROWS
Code traduit
myCursor1.FETCH_STATUS()
4. Expressions¶
Opérations binaires¶
Code source
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;
Code traduit
VAR1 = 1 + 1;
VAR1 = 1 - 1;
VAR1 = 1 / 1;
VAR1 = 1 * 1;
VAR1 = 1 || 1;
VAR1 = 1 && 1;
Conditionnels¶
Code source
@var1 > 0
@var1 = 0
@var1 < 0
@var1 <> 0
Code traduit
VAR1 > 0
VAR1 = 0
VAR1 < 0
VAR1 != 0
Prédicat IN¶
Prédicat NULL¶
Code source
@var1 is null
@var2 is not null
Code traduit
VAR1 == null
VAR2 != null
5. Labels and Goto¶
Les étiquettes
n’ont pas le même comportement dans JavaScript que dans SQL Server. Pour simuler le comportement, elles sont transformées en fonctions
. Son utilisation est remplacée par un appel à la fonction générée qui contient toute la logique de l’étiquette. Exemple :
Code source¶
-- Additional Params: -t JavaScript
CREATE PROCEDURE [procWithLabels]
AS
SUCCESS_EXIT:
SET @ErrorStatus = 0
RETURN @ErrorStatus
ERROR_EXIT:
RETURN @ErrorStatus
Code traduit¶
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;
}
$$;
Comme vous le voyez dans l’exemple ci-dessus, les déclarations de fonctions qui étaient les étiquettes dans le code source, seront placées à la fin du code afin de le rendre plus propre.
GOTO
est une autre commande qui n’existe pas sur JavaScript. Pour simuler son comportement, leurs utilisations sont transformées en appels à la fonction (label) qui est référencée, précédés d’une instruction de retour. Exemple :
Code source¶
-- 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
Code traduit¶
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;
}
$$;
Comme vous le voyez dans l’exemple ci-dessus, le retour
est ajouté à l’appel de la fonction, afin d’arrêter le flux de code comme le fait SQL Server avec GOTO
.
Problèmes connus ¶
Aucun problème n’a été constaté.
EWIs connexes¶
SSC-EWI-0040 : Instruction non prise en charge.
SSC-EWI-0073 : En attente de l’examen de l’équivalence fonctionnelle.
Tables¶
Applies to
[x] SQL Server
[x] Azure Synapse Analytics
Note
Certaines parties du code de sortie sont omises pour des raisons de clarté.
Create table de base¶
Source
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'
);
Valeur attendue
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"}}'
;
Tables temporaires¶
Dans le code source, certains noms de tables peuvent commencer par le caractère #.
CREATE TABLE #MyLocalTempTable (
COL1 INT,
COL2 INT
);
Si c’est le cas, ils sont transformés en tables temporaires dans le code de sortie.
Voyons comment le code ci-dessus serait migré.
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"}}'
;
Comme vous pouvez le voir, TEMPORARY a été ajouté à la définition de la table, et le caractère # a été remplacé par T_.
De même, toutes les références de la table seront également transformées pour correspondre au nouveau nom donné à la table temporaire.
Option de colonne NULL et NOT NULL¶
Les options de colonne NULL
et NOT NULL
sont prises en charge dans Snowflake.
Source
CREATE TABLE [SCHEMA1].[TABLE1](
[COL1] [varchar](20) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [SCHEMA1].[TABLE2](
[COL1] [varchar](20) NULL
) ON [PRIMARY]
GO
Valeur attendue
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"}}'
;
Option de colonne d’identité¶
Pour les colonnes d’identité, une séquence est créée et attribuée à la colonne.
Source
CREATE TABLE acct3.UnidentifiedCash3 (
UnidentifiedCash_ID3 INT IDENTITY (666, 313) NOT NULL
);
Valeur attendue
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"}}'
;
Option de colonne par défaut¶
L’expression par défaut est prise en charge dans Snowflake, cependant, dans Sql Server, elle peut être associée à une contrainte Nom. Cette partie n’étant pas prise en charge par Snowflake, elle a été supprimée et un avertissement a été ajouté.
Source
CREATE TABLE [SCHEMA1].[TABLE1] (
[COL1] VARCHAR (10) CONSTRAINT [constraintName] DEFAULT ('0') NOT NULL
);
Valeur attendue
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"}}'
;
Contrainte de colonne¶
Source
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]
Valeur attendue
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" }}'
;
Option de colonne Collate¶
Pour la transformation Collate, veuillez consulter le lien suivant.
Option de colonne ENCRYPTED WITH¶
L’option Encrypted With n’est pas prise en charge dans Snowflake, elle est donc supprimée et un avertissement est ajouté.
Source
CREATE TABLE [SCHEMA1].[TABLE1] (
[COL1] NVARCHAR(60) ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = MyCEK, ENCRYPTION_TYPE = RANDOMIZED, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256')
);
Valeur attendue
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¶
L’option NOT FOR REPLICATION n’est pas prise en charge dans Snowflake. Elle est utilisé pour l’identité qui est migrée vers SEQUENCE
.
Avertissement
Notez que NOT FOR REPLICATION
est une instruction qui n’est pas requise dans Snowflake car elle est traduite par un équivalent, elle est donc supprimée.
Source
CREATE TABLE [TABLE1] (
[COL1] INT IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL
) ON [PRIMARY];
Sortie
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¶
L’option ON PRIMARY
est une instruction utilisée dans SQL Server pour définir sur quel fichier un objet, par exemple une table, va être créé. Par exemple, sur un groupe de fichiers primaire ou secondaire à l’intérieur de la base de données. Snowflake fournit une logique différente et indique des contraintes distinctes. Pour plus d’informations, veuillez consulter la documentation Snowflake suivante.
Source¶
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]
Sortie¶
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" }}'
;
Tri des colonnes ASC/DESC¶
Le tri des colonnes n’est pas pris en charge dans Snowflake, les mots-clés ASC
ou DESC
sont supprimés.
Source¶
CREATE TABLE [TABLE1](
[COL1] [int] NOT NULL,
CONSTRAINT [constraint1] PRIMARY KEY CLUSTERED ([COL1] ASC)
) ON [PRIMARY]
Sortie¶
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" }}'
;
Colonnes calculées¶
Les colonnes calculées sont prises en charge dans Snowflake, il suffit d’ajouter le type de données explicite pour pouvoir déployer la table, par exemple.
Source¶
CREATE TABLE [TABLE1](
[COL2] [int] NOT NULL,
[COL2] [int] NOT NULL,
[COL1] AS (COL3 * COL2),
)
Sortie¶
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"}}'
;
Si l’expression calculée ne peut pas être transformée, un avertissement est ajouté et une simple définition de colonne avec le type de retour de l’expression sera utilisée à la place, comme dans l’exemple suivant :
Source¶
CREATE TABLE [TABLE1](
[Col1] AS (CONVERT ([XML], ExpressionValue))
)
L’expression CONVERT ([NUMERIC], ExpressionValue)
n’est pas encore prise en charge par SnowConvert, donc, après son inspection, SnowConvert déterminera que son type est XML, et la transformation sera donc la suivante
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 lancera un processus pour déterminer le type d’expression d’origine dans SQL Server. Toutefois, la colonne aura le type de cible équivalent. Dans l’exemple précédent, le type de colonne dans SQLServer était XML, mais le type de cible dans Snowflake pour stocker un XML est TEXT. Pour plus d’informations sur le mappage des types de données, consultez les sections sur les types de données.
Option de colonne MASKED WITH¶
Dans SQL Server, le masquage des données est utilisé pour empêcher les utilisateurs non privilégiés d’accéder à des informations sensibles. Consultez la documentation SQL SERVER pour plus d’informations. Dans Snowflake, il existe une fonctionnalité de masquage dynamique des données, mais elle n’est disponible que pour l’Enterprise Edition. Veuillez consulter la documentation Snowflake suivante.
Entrée¶
CREATE TABLE TABLE1
(
[COL1] [nvarchar](50) MASKED WITH (FUNCTION = 'default()') NULL
);
Sortie¶
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"}}'
;
Option de colonne ROWGUIDCOL¶
ROWGUIDCOL
n’est plus applicable dans Snowflake. Il est utilisé dans SQL Server pour les types UNIQUEIDENTIFIER qui sont actuellement traduits en VARCHAR
. Par exemple :
Entrée¶
CREATE TABLE TABLEROWID (
[ROWGUID] UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL
) ON [PRIMARY];
Sortie¶
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"}}'
;
Option de colonne GENERATED ALWAYS AS ROW START/END¶
ROW START/END
n’est pas pris en charge par Snowflake. Une erreur est ajoutée lorsque SnowConvert tente de transformer ce type d’option de colonne.
Entrée¶
CREATE TABLE TABLEROWID (
[COL1] DATETIME GENERATED ALWAYS AS ROW START NOT NULL
) ON [PRIMARY];
Sortie¶
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"}}'
;
Problèmes connus¶
Aucun problème n’a été constaté.
EWIs connexes¶
SSC-EWI-0036 : Type de données converti en un autre type de données.
SSC-EWI-0040 : Instruction non prise en charge.
SSC-EWI-0073 : En attente de l’examen de l’équivalence fonctionnelle.
SSC-EWI-TS0017 : Le masquage n’est pas pris en charge.
SSC-FDM-0012 : La contrainte dans l’expression par défaut n’est pas prise en charge.
SSC-FDM-TS0002 : Ce message s’affiche lorsqu’une clause de classement n’est pas prise en charge par Snowflake.
SSC-FDM-TS0009 : Encrypted with n’est pas pris en charge dans Snowflake.
SSC-FDM-TS0014 : Colonne calculée transformée.
SSC-FDM-TS0015 : Le type de données n’est pas pris en charge par Snowflake.
SSC-PRF-0002 : Les colonnes insensibles à la casse peuvent diminuer les performances des requêtes.
Vues¶
Applies to
[x] SQL Server
[x] Azure Synapse Analytics
Note
Certaines parties du code de sortie sont omises pour des raisons de clarté.
Dans cette section, nous allons vérifier la transformation pour Create View.
Modèles d’échantillons de sources¶
SIMPLE CREATE VIEW¶
L’exemple suivant montre une transformation pour une instruction CREATE VIEW
simple.
CREATE VIEW VIEWNAME
AS
SELECT AValue from ATable;
Valeur attendue
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¶
La définition de CREATE OR ALTER utilisée dans SqlServer est transformée en CREATE OR REPLACE dans Snowflake.
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¶
Dans ce type de vue, après le nom de la vue, on peut trouver les clauses suivantes
WITH ENCRYPTION
WITH SCHEMABINDING
WITH VIEW_METADATA
Avertissement
Notez que les clauses ci-dessus sont supprimées de la traduction, parce qu’elles ne sont pas pertinentes dans la syntaxe 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¶
Dans ce type de vue, la clause WITH CHECK OPTION
vient après la fin de l’instruction Select utilisée dans Create View.
Avertissement
Notez que WITH CHECK OPTION
a été supprimé de la traduction, car il n’est pas pertinent dans la syntaxe Snowflake.
CREATE OR ALTER VIEW VIEWNAME
AS
SELECT AValue from ATable
WITH CHECK OPTION;
Valeur attendue
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¶
Des expressions de table communes doivent être utilisées pour récupérer les données :
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¶
Les expressions de table courantes avec des instructions de mise à jour, d’insertion ou de suppression seront commentées car elles ne sont pas prises en charge par Snowflake et SQLServer.
Dans le cas où une CTE invalide est ajoutée à la vue, celle-ci sera complètement commentée.
--!!!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¶
Voyons un dernier échantillon, rassemblons tous les cas que nous avons vus jusqu’à présent et voyons comment se ferait la transformation
CREATE OR ALTER VIEW VIEWNAME
WITH ENCRYPTION
AS
Select AValue from ATable
WITH CHECK OPTION;
Valeur attendue
CREATE OR REPLACE VIEW VIEWNAME
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
AS
Select
AValue
from
ATable;
Comme vous pouvez le voir, nous avons remplacé OR ALTER par OR REPLACE et nous avons supprimé la clause WITH ENCRYPTION qui vient après le nom de la vue et la clause WITH CHECK OPTION qui vient après le Select.
EWIs connexes¶
SSC-PRF-TS0001 : Avertissement de performance - la récursivité pour CTE n’a pas été vérifiée. Peut nécessiter un mot-clé récursif.
Azure Synapse Analytics¶
Applies to
[x] Azure Synapse Analytics
Description¶
Cette section présente la traduction de la syntaxe spécifique aux tables Azure Synapse Analytics.
Note
Pour la plupart des modèles, consultez la spécification de traduction pour les tables générales, car elles partagent pratiquement la même syntaxe et le même comportement.
Create Table¶
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 As¶
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>
[;]
Modèles d’échantillons de sources¶
WITH options de table¶
Azure Synapse Analytics présente une syntaxe supplémentaire pour définir les options de table.
<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 gère automatiquement l’optimisation des tables grâce à des mécanismes tels que la micropartition. C’est pourquoi il n’existe pas de syntaxe équivalente pour certaines de ces options de table dans Snowflake. Il n’est donc pas nécessaire de définir certaines options de table de Transact.
Options de table qui seront omises :
CLUSTERED COLUMNSTORE INDEX (sans colonne)
HEAP
DISTRIBUTION
PARTITION
CLUSTERED [ COLUMNSTORE ] INDEX
avec des colonnes, sera transformé en CLUSTER BY
dans Snowflake . Un examen des performances PRF sera ajouté car il est conseillé de vérifier si la définition d’un CLUSTER KEY est nécessaire.
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" }}'
;
Problèmes connus¶
Aucun problème n’a été constaté.
EWIs connexes¶
SSC-PRF-0007 : PERFORMANCE REVIEW - CLUSTER BY.
TEXTIMAGE_ON¶
Applies to
[x] SQL Server
Note
Instruction non pertinente.
Avertissement
Remarquez que cette instruction est retirée de la migration parce qu’il s’agit d’une syntaxe non pertinente. Cela signifie qu’elle n’est pas exigée dans Snowflake.
Description¶
TEXTIMAGE_ON [PRIMARY]
est un moyen, dans Transact, de traiter les grands groupes d’informations à l’intérieur d’une table. Dans Snowflake, il n’est pas nécessaire de définir ce type de caractéristiques, car Snowflake traite les fichiers de données ou les informations de grande taille d’une manière différente.
Modèles d’échantillons de sources¶
Notez que dans cet exemple, TEXTIMAGE_ON [PRIMARY]
a été supprimé en raison de sa syntaxe inutile.
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"}}'
;
Problèmes connus ¶
Aucun problème n’a été constaté.
EWIs connexes ¶
Pas d’EWIs connexes.
FUNCTION¶
Applies to
[x] SQL Server
[x] Azure Synapse Analytics
Description¶
SQL Server ne prend en charge que deux types de fonctions définies par l’utilisateur :
À partir de ces types d’UDFs, il est possible de les sous-catégoriser en simples et complexes, selon la logique interne.
Les UDFs simples ; ce type fait correspondre la syntaxe SQL Server à la syntaxe de Snowflake. Ce type n’ajoute aucune logique et va directement au résultat. Ils correspondent généralement aux UDFs SQL de Snowflake.\ \ Les UDFs complexes ; ce type fait un usage intensif d’une instruction particulière (INSERT, DELETE, UPDATE, SET, DECLARE, etc) ou de blocs de contrôle du flux blocs(IF…ELSE, WHILE, etc) et représente généralement une inadéquation ou une violation de la définition des UDFs SQL de Snowflake.
Limitations¶
Les UDFs Transact présentent certaines limites qui ne sont pas présentes dans d’autres moteurs de base de données (comme Oracle et Teradata). Ces limites facilitent les traductions en réduisant le champ d’application de l’échec. Cela signifie qu’il existe des scénarios spécifiques que nous pouvons espérer éviter.
Voici quelques-unes des limites de SQL Server applicables aux UDFs
Les UDFs ne peuvent pas être utilisées pour effectuer des actions qui modifient l’état de la base de données
Les fonctions définies par l’utilisateur ne peuvent pas contenir de clause OUTPUT INTO ayant une table pour cible
Les fonctions définies par l’utilisateur ne peuvent pas renvoyer plusieurs jeux de résultats. Utilisez une procédure stockée si vous devez renvoyer plusieurs jeux de résultats.
Pour obtenir la liste complète, consultez le lien Créer des fonctions définies par l’utilisateur (moteur de base de données)
INLINE TABLE-VALUED¶
Applies to
[x] SQL Server
[x] Azure Synapse Analytics
Description¶
Note
Certaines parties du code de sortie sont omises pour des raisons de clarté.
Les fonctions en ligne à valeur de table sont des expressions de table qui peuvent accepter des paramètres, exécuter une instruction SELECT et renvoyer une TABLE (Référence linguistique SQL Server Création d’une fonction à valeur de table intégrée.)
Syntaxe 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 [ ) ]
[ ; ]
Syntaxe 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
Modèles d’échantillons de sources¶
La section suivante décrit tous les modèles de code source pouvant apparaître dans ce type de syntaxe CREATE FUNCTION
.
Pour les fonctions en ligne à valeur de table, il ne peut y avoir qu’une seule instruction par corps qui puisse être :
Instruction
SELECT
Expression de table commune
WITH
Sélectionner et retourner des valeurs directement à partir d’une table¶
Il s’agit du scénario le plus simple, qui consiste à effectuer une simple sélection dans une table et à renvoyer les valeurs correspondantes
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|
Sélectionner et retourner des valeurs à partir de plusieurs tables en renommant les colonnes et en utilisant des fonctions intégrées¶
Voici un exemple de requête utilisant des fonctions intégrées dans une instruction Select pour obtenir des données de différentes tables, renommer les colonnes et renvoyer une table.
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|
Sélectionnez des colonnes à l’aide de l’instruction WITH¶
Le corps d’une fonction à valeur de table intégrée peut également être spécifié à l’aide d’une instruction WITH, comme indiqué ci-dessous.
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 |
Problèmes connus¶
Aucun problème n’a été trouvé
EWIs connexes¶
SSC-FDM-TS0012 : L’information pour l’expression n’a pas été trouvée. CAST vers STRING utilisé
SSC-PRF-TS0001 : Avertissement de performance - la récursivité pour CTE n’a pas été vérifiée. Peut nécessiter un mot-clé récursif.
SSC-EWI-0073 : En attente de l’examen de l’équivalence fonctionnelle
MULTI-STATEMENT TABLE-VALUED¶
Applies to
[x] SQL Server
[x] Azure Synapse Analytics
Note
Certaines parties du code de sortie sont omises pour des raisons de clarté.
Note
Tous les échantillons de code figurant sur cette page n’ont pas encore été mis en œuvre dans SnowConvert. Ils doivent être interprétés comme une référence pour la manière dont chaque scénario doit être traduit dans Snowflake. Certaines parties du code de sortie sont omises pour des raisons de clarté.
Description¶
Une fonction à valeur de table multi-instructions est similaire à une fonction à valeur de table d’instruction intégrée. (INLINE TABLE-VALUED. Cependant, la fonction à valeur de table multi-instructions peut avoir plus d’une instruction dans son corps, les colonnes de la table sont spécifiées dans le type de retour et elle a un bloc BEGIN/END (Référence linguistique SQL Server Créer une fonction à valeur de table multi-instructions
Syntaxe 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>'
Modèles d’échantillons de sources¶
La section suivante décrit tous les modèles de code source pouvant apparaître dans ce type de syntaxe CREATE FUNCTION.
Le corps de la fonction à valeur de table multi-instructions doit être une instruction SELECT. C’est pourquoi les autres instructions doivent être appelées séparément.
Insérer des valeurs dans une table¶
Insère une ou plusieurs lignes dans la table et renvoie la table avec les nouvelles valeurs
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|
Insérer une valeur en fonction de l’instruction if/else¶
Insère une ligne dans la table conformément à la condition et renvoie la table avec la nouvelle valeur
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|
Insère plusieurs éléments en fonction de l’instruction if/else¶
L’exemple ci-dessous insère plusieurs valeurs dans la table et plusieurs variables sont modifiées en fonction de la condition. Renvoie la table contenant les nouvelles valeurs
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|
Avertissement
Dans le cas d’instructions if imbriquées et si plusieurs variables sont modifiées dans les instructions, il est nécessaire d’utiliser une procédure stockée.
Mise à jour des valeurs précédemment insérées¶
Met à jour les valeurs des colonnes de la table dans le corps de la fonction et la renvoie avec les nouvelles valeurs.
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|
Clauses de retour multiples¶
Dans l’échantillon suivant, il y a plus d’une clause de retour, car selon la situation, il n’est pas nécessaire de continuer à exécuter toute la fonction.
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|
Avertissement
Cette transformation est appliquée lorsqu’il n’y a qu’une seule valeur à insérer. S’il y a plus d’une valeur, il est nécessaire d’utiliser une procédure stockée.
Cas complexes¶
L’exemple est un casse complexe qui utilise des instructions imbriquées if
et insère une valeur en fonction de la condition true.
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|
Problèmes connus¶
Instructions While avec requêtes en parallèle¶
Le problème de cet exemple est qu’il n’y a aucun moyen de transformer l’instruction while en CTE à l’intérieur de la clause WITH
du select principal, ce qui nous oblige à transformer cette instruction en procédure stockée pour conserver la même logique.
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|
Déclarer le curseur¶
Les fonctions définies par l’utilisateur ne peuvent pas DECLARE, OPEN, FETCH, CLOSE ou DEALLOCATE a CURSOR
. Utilisez une procédure stockée pour travailler avec des curseurs.
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|
Les instructions différentes ne sont pas prises en charge dans les expressions de tables communes¶
Les clauses UPDATE
, INSERT
, DELETE
, ALTER
ou DROP
ne sont pas prises en charge dans le corps des expressions de tables communes, même après leur déclaration à l’aide d’un délimiteur. C’est pourquoi la fonction peut être modifiée pour fonctionner comme une procédure stockée.
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 connexes¶
SSC-EWI-0040 : Instruction non prise en charge.
SSC-EWI-0073 : En attente de l’examen de l’équivalence fonctionnelle
SCALAR¶
Applies to
[x] SQL Server
[x] Azure Synapse Analytics
Description¶
Note
Certaines parties du code de sortie sont omises pour des raisons de clarté.
Une fonction définie par l’utilisateur est une routine Transact-SQL ou Common Language Runtime (CLR) qui accepte des paramètres, effectue une action, telle qu’un calcul complexe, et renvoie le résultat de cette action sous la forme d’une valeur scalaire. (Référence linguistique SQL Server Sous section CREATE FUNCTION).
Note
Ces fonctions sont généralement utilisées dans l’instruction SELECT
ou dans la configuration d’une seule variable (très probablement dans une procédure stockée).
Syntaxe 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
[ ; ]
Syntaxe Snowflake¶
Snowflake permet l’utilisation de 3 langues différentes dans les fonctions définies par l’utilisateur :
SQL
JavaScript
Java
Pour l’instant, SnowConvert ne prend en charge que les langues cibles SQL
et JavaScript
.
Note
Les fonctions définies par l’utilisateur SQL n’acceptent qu’une seule requête comme corps. Elles peuvent lire la base de données, mais ne sont pas autorisées à l’écrire ou à la modifier. (Référence des UDFs SQL scalaires).
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>'
Note
Les fonctions définies par l’utilisateur JavaScript autorisent plusieurs instructions dans leur corps, mais ne peuvent pas effectuer de requêtes dans la base de données. (Référence des UDFs JavaScript scalaires)
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>'
Modèles d’échantillons de sources¶
Instructions Set et Declare¶
Les instructions les plus courantes dans les corps de fonctions sont les instructions DECLARE
et SET
. Pour les instructions DECLARE
sans valeur par défaut, la transformation sera ignorée. Les instructions SET
et DECLARE
avec une valeur par défaut seront transformées en COMMON TABLE EXPRESSION.
Chaque expression de table commune contiendra une colonne qui représente la valeur de la variable locale.
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|
Transformation des instructions If/Else¶
Les instructions If/Else peuvent être traitées de différentes manières, elles peuvent être transformées en Javascript ou en SQL à l’aide de CASE EXPRESSION dans Select qui permet des conditionnels à l’intérieur des requêtes, alors que la transformation en Javascript est assez simple, l’instruction Case peut ne pas être si évidente à première vue.
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 |
Instructions imbriquées¶
Pour les instructions imbriquées, la programmation structurée est transformée en une requête unique. Les instructions du contrôle du flux seront imbriquées dans des structures de table afin de préserver l’ordre d’exécution.
Note
CASE EXPRESSIONS
ne peut renvoyer qu’une seule valeur par instruction
Exemple¶
Note
Le code suivant est fonctionnellement équivalent dans les deux paradigmes de programmation.
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 |
Variables conditionnelles via SELECTs¶
La définition et l’affectation de variables dans les instructions conditionnelles posent généralement des problèmes, car les références à la variable plus loin dans le code doivent savoir où la variable a été modifiée pour la dernière fois. De plus, si la référence se trouve à l’intérieur d’une autre instruction conditionnelle, il faudrait une sorte de redirection qui fasse référence à l’affectation précédente connue de la variable.
Tout cela est aggravé par l’imbrication et la complexité des requêtes que l’on peut trouver sur le code d’entrée.
Dans le scénario suivant, la première instruction IF
peut être transformée sans problème, car son contenu est assez simple. Les deuxième et troisième instructions IF
sont commentées parce qu’elles ne sont pas prises en charge pour le moment, étant donné qu’il existe des instructions autres que les affectations de variables par le biais de 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|
Assigner et retourner une variable¶
Dans ce modèle simple, il y a une déclaration de variable, puis cette variable est définie à l’aide d’une instruction SELECT
et enfin renvoyée. Celle-ci va être migrée vers une expression de table commune afin de conserver le comportement d’origine.
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|
Appels de fonctions multiples¶
Pour ce modèle spécifique, il n’y a pas de requêtes évidentes, mais il y a plusieurs appels à plusieurs fonctions travaillant sur la même variable et la renvoyant à la fin. Comme Snowflake ne prend en charge que les requêtes à l’intérieur de ses fonctions, la solution pour ce bloc va être de l’ajouter à un Select et d’imbriquer les appels à l’intérieur, en s’assurant que la valeur de retour est la même que celle de la source.
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|
Augmenter une variable en fonction de plusieurs conditions IF et renvoyer sa valeur¶
Pour ce modèle, une variable est modifiée (augmentée dans le cas présent) à l’aide de plusieurs conditions IF. Au début, un jeu de résultats est initialisé et utilisé pour déterminer si la variable de résultat doit être augmentée ou non. Enfin, la variable de résultat est renvoyée.
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|
Deux ou plusieurs instructions RETURN¶
Pour ce modèle, le bloc IF
contenant la clause de retour qui interrompt le flux de code est ajouté à la fin du corps, comme la dernière instruction à exécuter dans une expression CASE
.
Cas de base¶
Pour ce scénario particulier, il n’y a pas de logique entre l’instruction conditionnelle RETURN
et l’instruction finale RETURN
, de sorte que tous les corps seront mappés sur une seule 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|
Expressions de table communes¶
Les expressions de table communes seront conservées comme dans le code original, et elles seront concaténées avec les expressions générées. SnowConvert est capable d’identifier en premier lieu tous les noms originaux de COMMON TABLE EXPRESSION
afin d’éviter de générer des noms dupliqués.
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|
Transformer en UDFs JavaScript¶
S’il y a plusieurs instructions et que la fonction n’accède en aucune façon à la base de données, elle peut être transformée en une fonction JavaScript en conservant l’équivalence fonctionnelle
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 |
Problèmes connus¶
Avertissement
Les fonctions définies par l’utilisateur ne peuvent pas être utilisées pour effectuer des actions qui modifient l’état de la base de données
Avertissement
Les fonctions définies par l’utilisateur ne peuvent pas contenir de clause OUTPUT INTO
ayant une table pour cible
Avertissement
Les fonctions définies par l’utilisateur ne peuvent pas DECLARE, OPEN, FETCH, CLOSE ou DEALLOCATE a CURSOR
. Utilisez une procédure stockée si vous devez utiliser des curseurs.
Avertissement
Les fonctions définies par l’utilisateur ne peuvent pas exécuter d’instructions de contrôle du flux, telles que WHILE, s’il y a au moins un appel à la base de données
Avertissement
Les fonctions définies par l’utilisateur qui contiennent des références à d’autres fonctions définies par l’utilisateur et qui ont été transformées en procédures stockées seront également transformées en procédures stockées.
Avertissement
Les fonctions définies par l’utilisateur qui utilisent @@ROWCOUNT ne sont pas prises en charge dans SQL et doivent être transformées en procédures stockées afin de conserver l’équivalence fonctionnelle.
Avertissement
Les fonctions définies par l’utilisateur qui ont des instructions SELECT
assignant une variable à elle-même ne sont pas prises en charge dans Snowflake. Voir aussi SELECT @local_variable
Pour tous les cas non pris en charge, veuillez consulter les EWIs connexes et les modèles ci-dessous pour obtenir des recommandations et des solutions de contournement.
Conditionnelles autres que les instructions if/else en parallèle des requêtes¶
Le scénario suivant implique l’utilisation de l’instruction « while » en parallèle d’autres requêtes. Le problème de cet exemple est qu’il n’y a aucun moyen de transformer l’instruction while en CTE à l’intérieur de la clause WITH
du select principal, ce qui nous oblige à transformer cette instruction en procédure JavaScript pour conserver la même logique.
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|
Affectation d’une variable à l’aide de sa propre valeur en parcourant un jeu de lignes¶
Dans l’exemple suivant, la variable @names
est utilisée pour concaténer plusieurs valeurs d’une colonne en une seule chaîne. La variable est mise à jour à chaque itération comme indiqué, ce qui n’est pas pris en charge par les UDFs SnowFlake. Pour ce scénario, la fonction doit être transformée en procédure.
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|
Requête 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;
Avertissement
Pour les scénarios décrits ci-dessus, tenez compte des limites suivantes :
Tous les appels aux fonctions définies par l’utilisateur dans les requêtes DML telles que
SELECT
,INSERT
,DELETE
,UPDATE
ouMERGE
échoueront parce que les appels aux procédures stockées dans ces requêtes ne sont pas autorisés.Les appels à des fonctions définies par l’utilisateur à l’intérieur de procédures doivent être précédés du mot-clé
CALL
.Les fonctions définies par l’usage utilisées dans les COMPUTED COLUMNS échoueront lors de l’exécution.
EWIs connexes¶
SSC-EWI-0067 : UDF a été transformée en procédure Snowflake, l’appel de procédures à l’intérieur d’une requête n’est pas pris en charge.
SSC-EWI-0073 : En attente de l’examen de l’équivalence fonctionnelle.
SSC-FDM-0029 : Une fonction définie par l’utilisateur a été transformée en procédure Snowflake.