Tipos de dados semiestruturados¶

Características de um valor VARIANT¶

Um valor VARIANT pode ter um tamanho máximo de até 16 MB de dados descompactados. Entretanto, na prática, o tamanho máximo geralmente é menor devido à sobrecarga interna. O tamanho máximo também depende do objeto que está sendo armazenado.

Inserção de dados VARIANT¶

Para inserir dados VARIANT diretamente, use INSERT INTO ... SELECT. O exemplo a seguir mostra como inserir dados formatados em JSON em um valor VARIANT:

CREATE OR REPLACE TABLE variant_insert (v VARIANT);
INSERT INTO variant_insert (v)
  SELECT PARSE_JSON('{"key3": "value3", "key4": "value4"}');
SELECT * FROM variant_insert;

+---------------------+
| V                   |
|---------------------|
| {                   |
|   "key3": "value3", |
|   "key4": "value4"  |
| }                   |
+---------------------+

Como usar valores VARIANT¶

Para converter um valor de ou para o tipo de dados VARIANT, pode-se converter explicitamente usando a função CAST, a função TO_VARIANT ou o operador :: (por exemplo, expression::VARIANT).

Em algumas situações, um valor pode ser implicitamente convertido em um valor VARIANT. Para obter mais detalhes, consulte Conversão do tipo de dados.

O código de amostra abaixo mostra como usar um valor VARIANT, incluindo como converter de um valor VARIANT para um valor VARIANT.

Criar uma tabela e inserir um valor:

CREATE OR REPLACE TABLE varia (float1 FLOAT, v VARIANT, float2 FLOAT);
INSERT INTO varia (float1, v, float2) VALUES (1.23, NULL, NULL);

O primeiro UPDATE converte um valor de um valor FLOAT para um valor VARIANT. O segundo UPDATE converte um valor VARIANT para um valor FLOAT.

UPDATE varia SET v = TO_VARIANT(float1);  -- converts from a FLOAT value to a VARIANT value.
UPDATE varia SET float2 = v::FLOAT;       -- converts from a VARIANT value to a FLOAT value.

SELECT todos os valores:

SELECT * FROM varia;

+--------+-----------------------+--------+
| FLOAT1 | V                     | FLOAT2 |
|--------+-----------------------+--------|
|   1.23 | 1.230000000000000e+00 |   1.23 |
+--------+-----------------------+--------+

Conforme mostrado no exemplo anterior, para converter um valor do tipo de dados VARIANT, converta o valor VARIANT para o tipo de dados de destino. Por exemplo, a seguinte instrução usa o operador :: para converter VARIANT em FLOAT:

SELECT my_variant_column::FLOAT * 3.14 FROM ...;

Os dados VARIANT armazenam tanto o valor quanto o tipo de dados do valor. Portanto, é possível usar valores VARIANT em expressões onde o tipo de dados do valor é válido sem primeiro converter o VARIANT. Por exemplo, se a coluna VARIANT my_variant_column contém um valor numérico, então você pode multiplicar diretamente my_variant_column por outro valor numérico:

SELECT my_variant_column * 3.14 FROM ...;

Você pode recuperar o tipo de dados nativos do valor usando a função TYPEOF.

Por padrão, quando os valores VARCHAR, DATE, TIME e TIMESTAMP são recuperados de uma coluna VARIANT, os valores são colocados entre aspas duplas. Você pode eliminar as aspas duplas convertendo explicitamente os valores nos tipos de dados subjacentes (por exemplo, de VARIANT para VARCHAR). Por exemplo:

SELECT 'Sample', 'Sample'::VARIANT, 'Sample'::VARIANT::VARCHAR;

+----------+-------------------+----------------------------+
| 'SAMPLE' | 'SAMPLE'::VARIANT | 'SAMPLE'::VARIANT::VARCHAR |
|----------+-------------------+----------------------------|
| Sample   | "Sample"          | Sample                     |
+----------+-------------------+----------------------------+

Um valor VARIANT pode estar ausente (conter SQL NULL), que é diferente de um valor VARIANT null, que é um valor real usado para representar um valor nulo em dados semiestruturados. VARIANT null é um valor verdadeiro que se compara como igual a si mesmo. Para obter mais informações, consulte VARIANT null.

Se os dados foram carregados do formato JSON e armazenados em uma coluna VARIANT, as seguintes considerações se aplicam:

• Para dados que são principalmente regulares e usam apenas tipos JSON nativos (como cadeias de caracteres e números), o desempenho é muito semelhante para operações de armazenamento e consulta em dados relacionais e dados em uma coluna VARIANT.

• Para dados não nativos (como datas e carimbos de data/hora), os valores são armazenados como cadeias de caracteres quando carregados em uma coluna VARIANT. Portanto, as operações nesses valores podem ser mais lentas e também consumir mais espaço do que quando armazenadas em uma coluna relacional com o tipo de dados correspondente.

Para obter mais informações sobre o uso do tipo de dados VARIANT, consulte Considerações sobre dados semiestruturados armazenados em VARIANT.

Para obter mais informações sobre como consultar dados semiestruturados armazenados em uma coluna VARIANT, consulte Consulta de dados semiestruturados.

Usos comuns para dados VARIANT¶

Os dados VARIANT são normalmente usados quando:

• Você deseja criar dados hierárquicos definindo explicitamente uma hierarquia que contenha dois ou mais ARRAYs ou OBJECTs.

• Você deseja carregar dados JSON, Avro, ORC ou Parquet diretamente, sem descrever explicitamente a estrutura hierárquica dos dados.

  O Snowflake pode converter dados dos formato JSON, Avro, ORC ou Parquet em uma hierarquia interna de dados ARRAY, OBJECT e VARIANT e armazenar esses dados hierárquicos diretamente em um valor VARIANT. Embora você mesmo possa construir manualmente a hierarquia de dados, geralmente é mais fácil deixar o Snowflake fazer isso por você.

  Para obter mais informações sobre carregamento e conversão de dados semiestruturados, consulte Carregamento de dados semiestruturados.

Características de um valor OBJECT¶

Em dados OBJECT semiestruturados Snowflake, cada chave é um VARCHAR e cada valor é um valor VARIANT..

Como um valor VARIANT pode armazenar um valor de qualquer outro tipo de dados, valores VARIANT diferentes (em diferentes pares chave-valor) podem ter diferentes tipos de dados subjacentes. Por exemplo, um valor OBJECT pode conter o nome de uma pessoa como um valor VARCHAR e a idade de uma pessoa como um valor INTEGER. No exemplo a seguir, tanto o nome quanto a idade são convertidos em valores VARIANT.

SELECT OBJECT_CONSTRUCT(
  'name', 'Jones'::VARIANT,
  'age',  42::VARIANT);

As seguintes considerações se aplicam aos dados OBJECT.

• Atualmente, Snowflake não oferece suporte a objetos com tipos explicitamente definidos.

• Em um par chave-valor, a chave não deve ser uma cadeia de caracteres vazia, e nem a chave nem o valor devem ser NULL.

• O comprimento máximo de um valor OBJECT é 16 MB.

• Um valor OBJECT pode conter dados semiestruturados.

• Um valor OBJECT pode ser usado para criar estruturas de dados hierárquicas.

Inserção de dados OBJECT¶

Para inserir dados OBJECT diretamente, use INSERT INTO ... SELECT.

O exemplo a seguir usa a função OBJECT_CONSTRUCT para construir o valor OBJECT que ela insere.

CREATE OR REPLACE TABLE object_example (object_column OBJECT);
INSERT INTO object_example (object_column)
  SELECT OBJECT_CONSTRUCT('thirteen', 13::VARIANT, 'zero', 0::VARIANT);
SELECT * FROM object_example;

+-------------------+
| OBJECT_COLUMN     |
|-------------------|
| {                 |
|   "thirteen": 13, |
|   "zero": 0       |
| }                 |
+-------------------+

Em cada par chave-valor, o valor foi explicitamente convertido para VARIANT. A conversão explícita não era obrigatória nesses casos. O Snowflake pode converter implicitamente em VARIANT. (Para obter mais informações sobre conversão implícita, consulte Conversão do tipo de dados).

Você também pode usar uma constante OBJECT para especificar o valor OBJECT a ser inserido. Para obter mais informações, consulte Constantes OBJECT.

Constantes OBJECT¶

Uma constante (também conhecida como literal) refere-se a um valor de dados fixo. O Snowflake oferece suporte ao uso de constantes para especificar valores de OBJECT. Constantes de OBJECT são delimitadas com chaves ({ e }).

Constantes OBJECT têm a seguinte sintaxe:

{ [<key>: <value> [, <key>: <value> , ...]] }

Onde:

key

A chave em um par chave-valor. key deve ser uma cadeia de caracteres literal.

value

O valor que está associado à chave. value pode ser um literal ou uma expressão. value pode ser qualquer tipo de dados.

A seguir estão alguns exemplos que especificam constantes OBJECT:

• {} é um valor OBJECT vazio.

• { 'key1': 'value1' , 'key2': 'value2' } contém os pares chave-valor especificados para o valor OBJECT usando literais para os valores.

• { 'key1': c1+1 , 'key2': c1+2 } contém os pares chave-valor especificados para o valor OBJECT usando expressões para os valores.

• {*} é um curinga que constrói o valor OBJECT a partir dos dados especificados usando os nomes de atributo como chaves e os valores associados como valores.

  Quando especificado em uma constante de objeto, o curinga pode ser não qualificado ou qualificado com um nome de tabela ou alias. Por exemplo, ambas as especificações curinga são válidas:

  SELECT {*} FROM my_table;
  
  SELECT {my_table1.*}
    FROM my_table1 INNER JOIN my_table2
        ON my_table2.col1 = my_table1.col1;
  

  Copy

  É possível usar as palavras-chave ILIKE e EXCLUDE em uma constante de objeto. Para selecionar colunas específicas, use a palavra-chave ILIKE. Por exemplo, a consulta a seguir seleciona colunas que correspondem ao padrão col1% na tabela my_table:

  SELECT {* ILIKE 'col1%'} FROM my_table;
  

  Copy

  Para excluir colunas específicas, use a palavra-chave EXCLUDE. Por exemplo, a consulta a seguir exclui col1 na tabela my_table:

  SELECT {* EXCLUDE col1} FROM my_table;
  

  Copy

  A consulta a seguir exclui col1 e col2 na tabela my_table:

  SELECT {* EXCLUDE (col1, col2)} FROM my_table;
  

  Copy

  Curingas não podem ser misturados com pares chave-valor. Por exemplo, a seguinte especificação de curinga não é permitida:

  SELECT {*, 'k': 'v'} FROM my_table;
  

  Copy

  Mais de um curinga não pode ser usado em uma constante de objeto. Por exemplo, a seguinte especificação de curinga não é permitida:

  SELECT {t1.*, t2.*} FROM t1, t2;
  

  Copy

As instruções a seguir usam uma constante OBJECT e a função OBJECT_CONSTRUCT para realizar uma inserção de dados OBJECT em uma tabela. Os valores OBJECT contêm os nomes e capitais de duas províncias canadenses.

CREATE OR REPLACE TABLE my_object_table (my_object OBJECT);

INSERT INTO my_object_table (my_object)
  SELECT { 'PROVINCE': 'Alberta'::VARIANT , 'CAPITAL': 'Edmonton'::VARIANT };

INSERT INTO my_object_table (my_object)
  SELECT OBJECT_CONSTRUCT('PROVINCE', 'Manitoba'::VARIANT , 'CAPITAL', 'Winnipeg'::VARIANT );

SELECT * FROM my_object_table;

+--------------------------+
| MY_OBJECT                |
|--------------------------|
| {                        |
|   "CAPITAL": "Edmonton", |
|   "PROVINCE": "Alberta"  |
| }                        |
| {                        |
|   "CAPITAL": "Winnipeg", |
|   "PROVINCE": "Manitoba" |
| }                        |
+--------------------------+

O exemplo a seguir usa um curinga ({*}) para inserir dados OBJECT obtendo os nomes e valores de atributos a partir da cláusula FROM. Primeiro, crie uma tabela nomeada demo_ca_provinces com valores VARCHAR que contenham os nomes das províncias e capitais:

CREATE OR REPLACE TABLE demo_ca_provinces (province VARCHAR, capital VARCHAR);
INSERT INTO demo_ca_provinces (province, capital) VALUES
  ('Ontario', 'Toronto'),
  ('British Columbia', 'Victoria');

SELECT province, capital
  FROM demo_ca_provinces
  ORDER BY province;

+------------------+----------+
| PROVINCE         | CAPITAL  |
|------------------+----------|
| British Columbia | Victoria |
| Ontario          | Toronto  |
+------------------+----------+

Insira os dados de objeto em my_object_table usando os dados da tabela demo_ca_provinces:

INSERT INTO my_object_table (my_object)
  SELECT {*} FROM demo_ca_provinces;

SELECT * FROM my_object_table;

+----------------------------------+
| MY_OBJECT                        |
|----------------------------------|
| {                                |
|   "CAPITAL": "Edmonton",         |
|   "PROVINCE": "Alberta"          |
| }                                |
| {                                |
|   "CAPITAL": "Winnipeg",         |
|   "PROVINCE": "Manitoba"         |
| }                                |
| {                                |
|   "CAPITAL": "Toronto",          |
|   "PROVINCE": "Ontario"          |
| }                                |
| {                                |
|   "CAPITAL": "Victoria",         |
|   "PROVINCE": "British Columbia" |
| }                                |
+----------------------------------+

O exemplo a seguir usa expressões para os valores em uma constante OBJECT:

SET my_variable = 10;
SELECT {'key1': $my_variable+1, 'key2': $my_variable+2};

+--------------------------------------------------+
| {'KEY1': $MY_VARIABLE+1, 'KEY2': $MY_VARIABLE+2} |
|--------------------------------------------------|
| {                                                |
|   "key1": 11,                                    |
|   "key2": 12                                     |
| }                                                |
+--------------------------------------------------+

As instruções SQL especificam literais de cadeia de caracteres dentro de um valor OBJECT com aspas simples (como em outros lugares no Snowflake SQL), mas literais de cadeia de caracteres dentro de um valor OBJECT são exibidos com aspas duplas:

SELECT { 'Manitoba': 'Winnipeg' } AS province_capital;

+--------------------------+
| PROVINCE_CAPITAL         |
|--------------------------|
| {                        |
|   "Manitoba": "Winnipeg" |
| }                        |
+--------------------------+

Acesso a elementos de um valor OBJECT por chave¶

Para recuperar o valor em um valor OBJECT, especifique a chave entre colchetes, conforme mostrado abaixo:

SELECT my_variant_column['key1'] FROM my_table;

Você também pode usar o operador dois-pontos. O comando a seguir mostra que os resultados são os mesmos, independentemente de você usar colchetes ou dois pontos:

SELECT object_column['thirteen'],
       object_column:thirteen
  FROM object_example;

+---------------------------+------------------------+
| OBJECT_COLUMN['THIRTEEN'] | OBJECT_COLUMN:THIRTEEN |
|---------------------------+------------------------|
| 13                        | 13                     |
+---------------------------+------------------------+

Para obter mais informações sobre o operador dois-pontos, consulte Notação de pontos, que descreve o uso dos operadores : e . para acessar os dados aninhados.

Usos comuns para dados OBJECT¶

Os dados OBJECT são normalmente usados quando uma ou mais das seguintes condições são verdadeiras:

• Você tem várias partes de dados que são identificadas por cadeias de caracteres. Por exemplo, se você quiser procurar informações pelo nome da província, talvez queira usar um valor OBJECT.

• Você deseja armazenar informações sobre os dados com os dados; os nomes (chaves) não são apenas identificadores distintos, mas são significativos.

• As informações não têm ordem natural, ou a ordem pode ser inferida somente a partir das chaves.

• A estrutura dos dados varia, ou os dados podem estar incompletos. Por exemplo, se você quiser criar um catálogo de livros que geralmente contém o título, o nome do autor e a data de publicação, mas em alguns casos a data de publicação é desconhecida, talvez você queira usar um valor OBJECT.

Características de uma matriz¶

Cada valor em uma matriz semiestruturada é do tipo VARIANT. Um valor VARIANT pode conter um valor de qualquer outro tipo de dados.

Valores de outros tipos de dados podem ser convertidos em valores VARIANT e então armazenados em uma matriz. Algumas funções para matrizes, incluindo ARRAY_CONSTRUCT, podem converter implicitamente valores em valores VARIANT para você.

Como as matrizes armazenam valores VARIANT e como os valores VARIANT podem armazenar outros tipos de dados dentro delas, os tipos de dados subjacentes dos valores em uma matriz não precisam ser idênticos. Entretanto, na maioria dos casos, os elementos de dados são do mesmo tipo ou de tipos compatíveis, de modo que todos podem ser processados da mesma maneira.

As seguintes considerações se aplicam a matrizes:

• O Snowflake não oferece suporte a matrizes de elementos de um tipo não VARIANT específico.

• Um matriz Snowflake é declarada sem especificar o número de elementos. Uma matriz pode crescer dinamicamente com base em operações como ARRAY_APPEND. Atualmente, o Snowflake não oferece suporte a matrizes de tamanhos fixos.

• Uma matriz pode conter valores NULL.

• O tamanho máximo teórico combinado de todos os valores em uma matriz é 16 MB. No entanto, as matrizes têm sobrecarga interna. O tamanho máximo prático dos dados é geralmente menor, dependendo do número e dos valores dos elementos.

Inserção de dados ARRAY¶

Para inserir dados ARRAY diretamente, use INSERT INTO ... SELECT.

O código a seguir usa a função ARRAY_CONSTRUCT para construir a matriz que ela insere.

CREATE OR REPLACE TABLE array_example (array_column ARRAY);
INSERT INTO array_example (array_column)
  SELECT ARRAY_CONSTRUCT(12, 'twelve', NULL);

Você também pode usar uma constante ARRAY para especificar a matriz a ser inserida. Para obter mais informações, consulte Constantes ARRAY.

Constantes ARRAY¶

Uma constante (também conhecida como literal) refere-se a um valor de dados fixo. O Snowflake oferece suporte ao uso de constantes para especificar valores de ARRAY. Constantes de ARRAY são delimitadas com colchetes ([ e ]).

Constantes ARRAY têm a seguinte sintaxe:

[<value> [, <value> , ...]]

Onde:

value

O valor que está associado a um elemento da matriz. value pode ser um literal ou uma expressão. value pode ser qualquer tipo de dados.

A seguir estão alguns exemplos que especificam constantes ARRAY:

• [] é um valor ARRAY vazio.

• { 'value1' , 'value2' } contém os valores especificados para a constante ARRAY usando literais para os valores.

• { c1+1 , c1+2 } contém os valores especificados para a constante ARRAY usando expressões para os valores.

O exemplo a seguir usa uma constante ARRAY para especificar a matriz a ser inserida.

INSERT INTO array_example (array_column)
  SELECT [ 12, 'twelve', NULL ];

As seguintes instruções utilizam uma constante de ARRAY e a função ARRAY_CONSTRUCT para realizar a mesma tarefa:

UPDATE my_table SET my_array = [ 1, 2 ];

UPDATE my_table SET my_array = ARRAY_CONSTRUCT(1, 2);

O exemplo a seguir usa expressões para os valores em uma constante ARRAY:

SET my_variable = 10;
SELECT [$my_variable+1, $my_variable+2];

+----------------------------------+
| [$MY_VARIABLE+1, $MY_VARIABLE+2] |
|----------------------------------|
| [                                |
|   11,                            |
|   12                             |
| ]                                |
+----------------------------------+

As instruções SQL especificam literais de cadeia de caracteres dentro de uma matriz com aspas simples (como em outros lugares no Snowflake SQL), mas literais de cadeia de caracteres dentro de uma matriz são exibidos com aspas duplas:

SELECT [ 'Alberta', 'Manitoba' ] AS province;

+--------------+
| PROVINCE     |
|--------------|
| [            |
|   "Alberta", |
|   "Manitoba" |
| ]            |
+--------------+

Acesso a elementos de uma matriz por índice ou por fatia¶

Os índices de matriz são baseados em 0, então o primeiro elemento em uma matriz é o elemento 0.

Os valores em uma matriz são acessados especificando-se o número de índice de um elemento da matriz entre colchetes. Por exemplo, a consulta a seguir lê o valor na posição de índice 2 na matriz armazenada em my_array_column.

SELECT my_array_column[2] FROM my_table;

Matrizes podem ser aninhadas. A consulta a seguir lê o zerésimo elemento do zerésimo elemento de uma matriz aninhada:

SELECT my_array_column[0][0] FROM my_table;

A tentativa de acessar um elemento além do final de uma matriz retorna NULL.

Uma fatia de uma matriz é uma sequência de elementos adjacentes (ou seja, um subconjunto contíguo da matriz).

Você pode acessar uma fatia de uma matriz chamando a função ARRAY_SLICE. Por exemplo:

SELECT ARRAY_SLICE(my_array_column, 5, 10) FROM my_table;

A função ARRAY_SLICE retorna elementos a partir do elemento inicial especificado (5 no exemplo acima) até, mas sem incluir, o elemento final especificado (10 no exemplo acima).

Uma matriz vazia ou uma fatia vazia é muitas vezes denotada por um par de colchetes sem nada entre eles ([]).

Matrizes densas e esparsas¶

Uma matriz pode ser densa ou esparsa.

Em uma matriz densa, os valores de índice dos elementos começam em zero e são sequenciais (0, 1, 2 etc.). Entretanto, em uma matriz esparsa, os valores de índice podem ser não sequenciais (por exemplo, 0, 2, 5). Os valores não precisam começar em 0.

Se um índice não tem um elemento correspondente, o valor correspondente a esse índice é dito ser indefinido. Por exemplo, se uma matriz esparsa tem três elementos, e esses elementos estão nos índices 0, 2 e 5, então os elementos nos índices 1, 3 e 4 são undefined.

   0            2                  5
+-----+.....+-------+.....+.....+------+
| Ann |     | Carol |     |     | Fred |
+-----+.....+-------+.....+.....+------+

        ^             ^     ^
        |             |     |
        undefined--------------

Um elemento indefinido é tratado como um elemento. Por exemplo, considere o exemplo anterior de uma matriz esparsa que contém elementos nos índices 0, 2 e 5 (e não tem elementos depois do índice 5). Se você ler a fatia contendo elementos nos índices 3 e 4, a saída é semelhante ao seguinte:

[ undefined, undefined ]

A tentativa de acessar uma fatia além do final de uma matriz resulta em uma matriz vazia, não em uma matriz de valores undefined. A seguinte instrução SELECT tenta ler além do último elemento da matriz esparsa da amostra:

SELECT ARRAY_SLICE(array_column, 6, 8) FROM table_1;

A saída é uma matriz vazia:

+---------------------------------+
| array_slice(array_column, 6, 8) |
+---------------------------------+
| [ ]                             |
+---------------------------------+

Observe que undefined é diferente de NULL. Um valor NULL em uma matriz é um elemento definido.

Em uma matriz densa, cada elemento consome espaço de armazenamento, mesmo que o valor do elemento seja NULL.

Em uma matriz esparsa, elementos undefined não consomem diretamente espaço de armazenamento.

Em uma matriz densa, a faixa teórica dos valores de índice é de 0 a 16777215. (O número máximo teórico de elementos é 16777216 porque o limite superior de tamanho é 16 MB (16777216 bytes) e o menor valor possível é um byte).

Em uma matriz esparsa, a faixa teórica dos valores de índice é de 0 a 2³¹ - 1. Entretanto, devido à limitação de 16 MB, uma matriz esparsa não pode conter valores 2³¹. O número máximo teórico de valores ainda está limitado a 16777216.

(Lembre-se de que, devido à sobrecarga interna, o limite de tamanho prático tanto em matrizes densas quanto em matrizes esparsas é pelo menos ligeiramente inferior ao máximo teórico de 16 MB).

Você pode criar uma matriz esparsa usando a função ARRAY_INSERT para inserir valores em pontos de índice específicos em uma matriz (deixando outros elementos da matriz undefined). Observe que, como ARRAY_INSERT empurra elementos para a direita, o que altera os valores de índice necessários para acessá-los, você quase sempre deseja preencher uma matriz esparsa da esquerda para a direita (ou seja, de 0 para cima, aumentando o valor do índice para cada novo valor inserido).

Usos comuns para dados ARRAY¶

Os dados ARRAY são normalmente usados quando uma ou mais das seguintes condições são verdadeiras:

• Há uma coleção de dados, e cada parte da coleção é estruturada da mesma forma ou de forma semelhante.

• Cada parte dos dados deve ser processada de forma semelhante. Por exemplo, você pode fazer um loop pelos dados, processando cada parte da mesma maneira.

• Os dados têm uma ordem natural, por exemplo, cronológica.