Variáveis de vinculação¶

Visão geral das variáveis de vinculação¶

Com variáveis de vinculação, você substitui literais em instruções SQL por espaços reservados. Por exemplo, a instrução SQL a seguir usa literais para os valores inseridos:

INSERT INTO t (c1, c2) VALUES (1, 'Test string');

A seguinte instrução SQL usa espaços reservados para os valores inseridos:

INSERT INTO t (c1, c2) VALUES (?, ?);

O código do seu aplicativo vincula dados a cada espaço reservado na instrução SQL. A técnica para vincular dados a um espaço reservado depende da linguagem de programação. A sintaxe do espaço reservado também varia de acordo com a linguagem de programação. É ?, :varname ou %varname.

Usar variáveis ​​de vinculação em procedimentos armazenados em Javascript¶

Você pode usar Javascript para criar procedimentos armazenados que executam SQL.

Para especificar variáveis de vinculação no código Javascript, use espaços reservados ?. Por exemplo, a seguinte instrução INSERT especifica variáveis de vinculação para os valores inseridos em uma linha de tabela:

INSERT INTO t (col1, col2) VALUES (?, ?)

No código Javascript, é possível usar variáveis de vinculação para os valores na maioria das instruções SQL. Para obter informações sobre limitações, consulte Limitações para variáveis de vinculação.

Para obter mais informações sobre o uso de variáveis de vinculação em Javascript, consulte Variáveis de vinculação.

Usar variáveis ​​de vinculação com o Snowflake Scripting¶

Você pode usar o Snowflake Scripting para criar código de procedimento que executa SQL, como blocos de código e procedimentos armazenados. Para especificar variáveis de vinculação no código do Snowflake Scripting, prefixe o nome da variável com dois pontos. Por exemplo, a seguinte instrução INSERT especifica uma variável de vinculação chamada variable1:

INSERT INTO t (c1) VALUES (:variable1)

Quando você executa SQL em um comando EXECUTE IMMEDIATE ou um OPEN para um cursor, pode vincular variáveis ​​com a cláusula USING.

Este exemplo vincula variáveis em um comando EXECUTE IMMEDIATE com uma cláusula USING:

EXECUTE IMMEDIATE :query USING (minimum_price, maximum_price);

Para ver o exemplo completo que inclui esse código, consulte Execução de uma instrução com variáveis de vinculação.

Ao declarar um cursor, você pode especificar os parâmetros de vinculação (caracteres ?) em uma instrução SELECT. Você pode então vincular esses parâmetros a variáveis na cláusulaUSING quando você abre o cursor.

O exemplo a seguir declara um cursor e especifica os parâmetros de vinculação, depois abre o cursor com a cláusula USING:

LET c1 CURSOR FOR SELECT id FROM invoices WHERE price > ? AND price < ?;
OPEN c1 USING (minimum_price, maximum_price);

O Snowflake Scripting também oferece suporte à numeração de variáveis de ligação por posição e à reutilização de uma variável de ligação em uma instrução SQL. Para variáveis de vinculação numeradas, cada declaração de variável recebe um índice, e você pode se referir à enésima variável declarada com :n. Por exemplo, o bloco do Snowflake Scripting a seguir especifica a variável de vinculação :1 para a variável i e :2 para a variável v, e reutiliza a variável de vinculação :1 em uma instrução SQL:

EXECUTE IMMEDIATE $$
DECLARE
  i INTEGER DEFAULT 1;
  v VARCHAR DEFAULT 'SnowFlake';
  r RESULTSET;
BEGIN
  CREATE OR REPLACE TABLE snowflake_scripting_bind_demo (id INTEGER, value VARCHAR);
  EXECUTE IMMEDIATE 'INSERT INTO snowflake_scripting_bind_demo (id, value)
    SELECT :1, (:2 || :1)' USING (i, v);
  r := (SELECT * FROM snowflake_scripting_bind_demo);
  RETURN TABLE(r);
END;
$$
;

+----+------------+
| ID | VALUE      |
|----+------------|
|  1 | SnowFlake1 |
+----+------------+

No código do Snowflake Scripting, é possível usar variáveis de vinculação para os valores na maioria das instruções SQL. Para obter informações sobre limitações, consulte Limitações para variáveis de vinculação.

Para obter mais informações sobre o uso de variáveis de vinculação no Snowflake Scripting, consulte Como usar uma variável em uma instrução SQL (vinculação) e Uso de um argumento em uma instrução SQL (vinculação).

Usar variáveis ​​de vinculação com a API SQL¶

Você pode usar o API de SQL do Snowflake para acessar e atualizar os dados em um banco de dados do Snowflake. Você pode criar aplicativos que usam SQL API para enviar instruções SQL e gerenciar implantações.

Ao enviar uma solicitação que executa uma instrução SQL, você pode usar variáveis ​​de vinculação para valores na instrução. Para obter mais informações, consulte Como usar variáveis de vinculação em uma instrução.

Usar variáveis ​​de vinculação com drivers¶

Usando drivers do Snowflake, você pode escrever aplicativos que executam operações no Snowflake. Os drivers suportam linguagens de programação como Go, Java e Python. Para obter informações sobre como usar variáveis de vinculação em um aplicativo para um driver específico, siga o link do driver:

• Go

• JDBC

• .NET

• Node.js

• ODBC

• Python

Usar variáveis ​​de vinculação com matrizes de valores¶

Você pode vincular uma matriz de valores a variáveis em instruções SQL. Usando essa técnica, você pode melhorar o desempenho inserindo diversas linhas em um único lote, o que evita compilações e viagens de ida e volta da rede. O uso de uma vinculação de matriz também é chamado de «inserção em massa» ou «inserção em lote».

A seguir está um exemplo de vinculação de matriz no código Python:

conn = snowflake.connector.connect( ... )
rows_to_insert = [('milk', 2), ('apple', 3), ('egg', 2)]
conn.cursor().executemany(
            "insert into grocery (item, quantity) values (?, ?)",
            rows_to_insert)

Este exemplo especifica a seguinte lista de vínculos: [('milk', 2), ('apple', 3), ('egg', 2)]. A maneira como um aplicativo especifica uma lista de vínculos depende da linguagem de programação.

Este código insere três linhas na tabela:

+-------+----+
| C1    | C2 |
|-------+----|
| milk  |  2 |
| apple |  3 |
| egg   |  2 |
+-------+----+

Para obter informações sobre como usar vinculações de matriz em um aplicativo para um driver específico, siga o link do driver:

• Go

• JDBC

• .NET

• Node.js

• ODBC

• Python

INSERT INTO t VALUES (?,?), (?,?);

Usar variáveis ​​de vinculação com dados semiestruturados¶

Para vincular variáveis a dados semiestruturados, vincule a variável como um tipo de cadeia de caracteres e use funções como PARSE_JSON ou ARRAY_CONSTRUCT.

O exemplo a seguir cria uma tabela com uma coluna VARIANT e depois chama a função PARSE_JSON para inserir dados semiestruturados na tabela com uma variável de vinculação:

CREATE TABLE t (a VARIANT);
-- Code that supplies a bind value for ? of '{'a': 'abc', 'x': 'xyz'}'
INSERT INTO t SELECT PARSE_JSON(a) FROM VALUES (?);

O exemplo a seguir consulta a tabela:

SELECT * FROM t;

A consulta retorna a seguinte saída:

+---------------+
| A             |
|---------------|
| {             |
|   "a": "abc", |
|   "x": "xyz"  |
| }             |
+---------------+

A instrução a seguir chama a função ARRAY_CONSTRUCT para inserir uma matriz de dados semiestruturados em uma coluna VARIANT com uma variável de vinculação:

INSERT INTO t SELECT ARRAY_CONSTRUCT(column1) FROM VALUES (?);

Ambos os exemplos podem inserir uma única linha ou podem usar uma vinculação de matriz para inserir várias linhas em um lote. Você pode usar esta técnica para inserir qualquer tipo de dado semiestruturado válido em uma coluna VARIANT.

Recuperar valores de variáveis ​​de vinculação¶

Para recuperar os valores das variáveis ​​de vinculação em uma consulta executada, use a função de tabela BIND_VALUES no esquema INFORMATION_SCHEMA. Com essa função, você pode recuperar valores de variáveis ​​de vinculação de qualquer código que aceite essas variáveis, incluindo código Javascript e Snowflake Scripting.

Você também pode acessar esses valores de variáveis ​​de vinculação na coluna bind_values na saída para a visualização do uso da conta QUERY_HISTORY, a visualização do uso da organização QUERY_HISTORY ou a função QUERY_HISTORY.

Para recuperar valores de variáveis ​​de vinculação usando qualquer um desses métodos, defina o parâmetro de nível de conta ALLOW_BIND_VALUES_ACCESS como TRUE.

Você pode querer recuperar valores de variáveis ​​de vinculação nos seguintes casos:

• Solução de problemas de consultas – quando você sabe os valores exatos de vinculação utilizados ​​nas consultas, fica mais fácil otimizá-las e depurar os seguintes tipos de problemas:

  • Uma consulta não é executada.

  • O desempenho de uma consulta é ruim.

  • Uma consulta não está usando caches ou planos de execução esperados.

• Recriação de consultas para testes – desenvolvedores e DBAs podem recriar consultas geradas pelo usuário com valores de variáveis ​​de vinculação para replicar problemas e para testes de estresse.

• Auditoria e conformidade – para fins de segurança e conformidade, as organizações devem auditar os dados que os usuários estão acessando. Elas podem usar valores de variáveis ​​de vinculação para determinar os dados exatos recuperados pelos usuários.

SELECT * FROM TABLE(
  INFORMATION_SCHEMA.BIND_VALUES('<query_id_value>'));

SELECT bind_values
  FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
  WHERE query_id = '<query_id_value>';

DECLARE
  name STRING;
  temperature FLOAT;
  res RESULTSET;
BEGIN
  name := 'Snowman';
  temperature := -20.14;
  res := (
    SELECT
      CONCAT('Hello ', :NAME, '!') as greeting,
      CONCAT('It is ', :TEMPERATURE, 'deg C today.') as weather
  );
  RETURN LAST_QUERY_ID();
END;

EXECUTE IMMEDIATE
$$
DECLARE
  name STRING;
  temperature FLOAT;
  res RESULTSET;
BEGIN
  name := 'Snowman';
  temperature := -20.14;
  res := (
    SELECT
      CONCAT('Hello ', :NAME, '!') as greeting,
      CONCAT('It is ', :TEMPERATURE, 'deg C today.') as weather
  );
  RETURN LAST_QUERY_ID();
END;
$$
;

+--------------------------------------+
| anonymous block                      |
|--------------------------------------|
| 01bbe3d6-0109-0863-0000-a99502ffa062 |
+--------------------------------------+

SELECT * FROM TABLE(
  INFORMATION_SCHEMA.BIND_VALUES('01bbe3d6-0109-0863-0000-a99502ffa062'));

+--------------------------------------+----------+-------------+------+---------+
| QUERY_ID                             | POSITION | NAME        | TYPE | VALUE   |
|--------------------------------------+----------+-------------+------+---------|
| 01bbe3d6-0109-0863-0000-a99502ffa062 |     NULL | TEMPERATURE | REAL | -20.14  |
| 01bbe3d6-0109-0863-0000-a99502ffa062 |     NULL | NAME        | TEXT | Snowman |
+--------------------------------------+----------+-------------+------+---------+

cursor = conn.cursor()
print(cursor.execute(
          """
          SELECT
              CONCAT('Hello ', ?, '!') as greeting,
              CONCAT('It is ', ?, 'deg C today.') as weather
          """,
          params=["Snowman", -20.14],
      ).fetch_pandas_all())

query_id = cursor.sfqid
print(f"Bind values for query {query_id} are:")
print(cursor.execute("SELECT * FROM TABLE(INFORMATION_SCHEMA.BIND_VALUES(?))", params=[query_id]).fetch_pandas_all())

        GREETING                   WEATHER
0  Hello Snowman!  It is -20.14deg C today.

Bind values for query 01bbe918-0200-0001-0000-000000101145 are:

                               QUERY_ID POSITION  NAME  TYPE    VALUE
0  01bbe918-0200-0001-0000-000000101145        1  None  TEXT  Snowman
1  01bbe918-0200-0001-0000-000000101145        2  None  REAL   -20.14

Limitações para variáveis de vinculação¶

As seguintes limitações se aplicam às variáveis de vinculação:

• Limitações para as instruções SELECT:

  • As variáveis de vinculação não podem substituir números que fazem parte de uma definição de tipo de dados (por exemplo, NUMBER(?)) ou da especificação de agrupamento (por exemplo, COLLATE ?).

  • As variáveis de vinculação não podem ser usadas para a fonte em uma instrução SELECT que consulta arquivos em um estágio.

• Limitações dos comandos da DDL:

  • As variáveis de vinculação não podem ser usadas nos seguintes comandos da DDL:

    • CREATE/ALTER INTEGRATION

    • CREATE/ALTER REPLICATION GROUP

    • CREATE/ALTER PIPE

    • CREATE TABLE … USING TEMPLATE

  • As variáveis de vinculação não podem ser usadas nas cláusulas a seguir:

    • ALTER COLUMN

    • COMMENT ON CONSTRAINT

  • Nos comandos CREATE/ALTER, as variáveis de vinculação não podem ser usadas para os valores dos seguintes parâmetros:

    • CREDENTIALS

    • DIRECTORY

    • ENCRYPTION

    • IMPORTS

    • PACKAGES

    • REFRESH

    • TAG

    • Parâmetros específicos de tabelas externas

  • As variáveis de vinculação não podem ser usadas para propriedades que fazem parte de um valor FILE FORMAT.

• Nos comandos COPY INTO, as variáveis de vinculação não podem ser usadas para os valores dos seguintes parâmetros:

  • CREDENTIALS

  • ENCRYPTION

  • FILE_FORMAT

• Nos comandos SHOW, as variáveis de vinculação não podem ser usadas no parâmetro STARTS WITH.

• As variáveis de vinculação não podem ser usadas em um comando EXECUTE IMMEDIATE FROM.

• Os valores das variáveis de vinculação não podem ser convertidos automaticamente de um tipo de dados para outro quando as variáveis de vinculação são usadas:

  • Código do Snowflake Scripting que especifica explicitamente o tipo de dados

  • Instruções DDL

  • Nomes de estágio

Considerações de segurança para variáveis de vinculação¶

As variáveis de vinculação não mascaram dados confidenciais em todos os casos. Por exemplo, os valores das variáveis de vinculação podem aparecer em mensagens de erro e outros artefatos.

As variáveis de vinculação podem ajudar a evitar ataques de injeção de SQL quando você constrói instruções SQL com a entrada do usuário. No entanto, as variáveis de vinculação podem apresentar riscos potenciais à segurança. Se as entradas nas instruções SQL vierem de fontes externas, certifique-se de que elas sejam validadas. Para obter mais informações, consulte Injeção de SQL.