Tipos de dados semiestruturados¶

Características de um VARIANT¶

Um VARIANT pode armazenar um valor de qualquer outro tipo, incluindo OBJECT e ARRAY.

Um VARIANT pode ter um tamanho máximo de até 16 MB. 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.

Uso de valores em um 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, os valores podem ser implicitamente convertidos em um VARIANT. Para obter mais detalhes, consulte Conversão do tipo de dados.

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

Criar uma tabela e inserir um valor:

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

Copy

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

UPDATE varia SET v = TO_VARIANT(float1);  -- converts FROM a float TO a variant.
UPDATE varia SET float2 = v::FLOAT;       -- converts FROM a variant TO a float.

Copy

SELECT todos os valores:

SELECT * FROM varia;
+--------+-----------------------+--------+
| FLOAT1 | V                     | FLOAT2 |
|--------+-----------------------+--------|
|   1.23 | 1.230000000000000e+00 |   1.23 |
+--------+-----------------------+--------+

Copy

Para converter um valor do tipo de dados VARIANT, especifique o tipo de dados que você deseja converter. Por exemplo, a seguinte instrução usa o operador :: para especificar que o VARIANT deve ser convertido em FLOAT:

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

Copy

VARIANT armazena tanto o valor quanto o tipo de dados do valor. Isso permite que você também utilize valores VARIANT em expressões em que 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 ...;

Copy

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

Por padrão, quando VARCHARs, DATEs, TIMEs e TIMESTAMPs são recuperados de uma coluna VARIANT, os valores ficam 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 no formato VARIANT, então as seguintes diretrizes se aplicam:

• Para dados que são em sua maioria regulares e usam apenas tipos JSON nativos (cadeias de caracteres e números, não carimbos de data/hora), o desempenho de armazenamento e de consulta para operações com dados relacionais e dados em uma coluna VARIANT é muito semelhante. Entretanto, valores não nativos, como datas e carimbos de data/hora, são armazenados como cadeias de caracteres quando carregados em uma coluna VARIANT, de modo que as operações com esses 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 de VARIANT, consulte: Considerações sobre dados semiestruturados armazenados em VARIANT.

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

Exemplo de inserção de um VARIANT¶

Para inserir dados VARIANT diretamente, use IIS (INSERT INTO ... SELECT). O exemplo abaixo mostra como inserir dados de formato JSON em um VARIANT:

INSERT INTO varia (v) 
    SELECT TO_VARIANT(PARSE_JSON('{"key3": "value3", "key4": "value4"}'));

Copy

Usos comuns para um VARIANT¶

Um VARIANT é normalmente usado 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 no 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 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 OBJECT¶

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

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

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

Copy

Atualmente, o Snowflake não oferece suporte a objetos de tipos explícitos.

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 OBJECT é 16 MB.

Um OBJECT pode conter dados semiestruturados.

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

Constantes de 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 }).

Por exemplo, o seguinte bloco de código mostra duas constantes de OBJECT, a primeira é um OBJECT vazio, e a segunda contém os nomes e as capitais de duas províncias canadenses:

{}

{ 'Alberta': 'Edmonton' , 'Manitoba': 'Winnipeg' }

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

UPDATE my_table SET my_object = { 'Alberta': 'Edmonton' , 'Manitoba': 'Winnipeg' };

UPDATE my_table SET my_object = OBJECT_CONSTRUCT('Alberta', 'Edmonton', 'Manitoba', 'Winnipeg');

Observe que as instruções SQL especificam literais de cadeia de caracteres dentro de um OBJECT com aspas simples (como em qualquer lugar no Snowflake SQL), mas as literais de cadeia de caracteres dentro de um OBJECT são exibidas com aspas duplas:

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

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

Elementos de acesso de um OBJECT por chave¶

Para recuperar o valor em um objeto, especifique a chave entre colchetes, como mostrado abaixo:

select my_variant_column['key1'] from my_table;

Copy

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

SELECT object_column['thirteen'],
       object_column:thirteen
    FROM object_example;
+---------------------------+------------------------+
| OBJECT_COLUMN['THIRTEEN'] | OBJECT_COLUMN:THIRTEEN |
|---------------------------+------------------------|
| 13                        | 13                     |
+---------------------------+------------------------+

Copy

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.

Exemplo de inserção de um OBJECT¶

Para inserir dados OBJECT diretamente, use IIS (INSERT INTO ... SELECT).

O seguinte código usa a função OBJECT_CONSTRUCT para construir o OBJECT que ele insere.

INSERT INTO object_example (object_column)
    SELECT OBJECT_CONSTRUCT('thirteen', 13::VARIANT, 'zero', 0::VARIANT);

Copy

O seguinte código usa uma constante de OBJECT para especificar o OBJECT a ser inserido.

INSERT INTO object_example (object_column)
    SELECT { 'thirteen': 13::VARIANT, 'zero': 0::VARIANT };

Copy

Observe que, em cada par chave-valor, o valor foi explicitamente convertido em 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).

Usos comuns para um OBJECT¶

Um OBJECT é normalmente usado quando um ou mais dos seguintes itens são verdadeiros:

• Você tem várias partes de dados que são identificadas por cadeias de caracteres. Por exemplo, se você quiser pesquisar informações por nome de província, você pode usar um 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ê deseja criar um catálogo de livros que normalmente contém o título, nome do autor e data de publicação, mas em alguns casos a data de publicação é desconhecida, então você pode usar um OBJECT.

Características de um ARRAY¶

Cada valor em um ARRAY semiestruturado é do tipo VARIANT. (Um VARIANT pode conter um valor de qualquer outro tipo de dados).

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

Como ARRAYs armazenam valores de VARIANT, e como valores de VARIANT podem armazenar outros tipos de dados dentro deles, os tipos de dados subjacentes dos valores em um ARRAY não precisam ser idênticos. Entretanto, na maioria dos casos, os elementos de dados são dos mesmos tipos ou compatíveis para que todos possam ser processados da mesma maneira.

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

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

Um ARRAY pode conter valores NULL.

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

Constantes de 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 ]).

Por exemplo, o seguinte bloco de código mostra duas constantes de ARRAY, a primeira é um ARRAY vazio, e a segunda contém os nomes de duas províncias canadenses:

[]

[ 'Alberta', 'Manitoba' ]

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);

Observe que as instruções SQL especificam literais de cadeia de caracteres dentro de um ARRAY com aspas simples (como em qualquer lugar no Snowflake SQL), mas as literais de cadeia de caracteres dentro de um ARRAY são exibidas com aspas duplas:

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

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

Acesso aos elementos de um ARRAY por índice ou por fatia¶

Os índices de matriz são baseados em 0; o primeiro elemento de 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 seguinte consulta lê o valor na posição de índice 2 na matriz armazenada em my_array_column.

select my_array_column[2] from my_table;

Copy

Matrizes podem ser aninhadas. A consulta seguinte lê o elemento zero do elemento zero de uma matriz aninhada:

select my_array_column[0][0] from my_table;

Copy

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;

Copy

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 ([]).

ARRAYs esparsas vs. densas¶

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--------------

Copy

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 ]

Copy

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;

Copy

A saída é uma matriz vazia:

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

Copy

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 os elementos para a direita, o que muda os valores de índice necessários para acessá-los, você quase sempre precisa preencher uma matriz esparsa da esquerda para a direita (ou seja, de 0 para cima, aumentando o valor de índice para cada novo valor inserido).

Exemplo de inserção de um ARRAY¶

Para inserir dados ARRAY diretamente, use IIS (INSERT INTO ... SELECT).

O seguinte código usa a função ARRAY_CONSTRUCT para construir o ARRAY que ele insere.

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

Copy

O seguinte código usa uma constante de ARRAY para especificar o ARRAY a ser inserido.

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

Copy

Usos comuns para um ARRAY¶

Um ARRAY é normalmente usado quando um ou mais dos seguintes itens são verdadeiros:

• Você tem muitos dados, cada um deles estruturado 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.