Considerações sobre dados semiestruturados armazenados em VARIANT¶

Limitações de tamanho dos dados¶

O tipo de dados VARIANT impõe um limite de tamanho de 16 MB para linhas individuais.

Para alguns formatos de dados semiestruturados (por exemplo, JSON), os conjuntos de dados são freqüentemente uma simples concatenação de múltiplos documentos. A saída JSON de um software é composta de uma única grande matriz contendo vários registros. Não há necessidade de separar os documentos com quebras de linha ou vírgulas, embora ambas as formas sejam suportadas.

Se os dados excederem 16 MB, ative a opção de formato de arquivo STRIP_OUTER_ARRAY do comando COPY INTO <tabela> para remover a estrutura da matriz externa e carregar os registros em linhas de tabela separadas:

COPY INTO <table>
  FROM @~/<file>.json
  FILE_FORMAT = (TYPE = 'JSON' STRIP_OUTER_ARRAY = true);

Armazenamento de dados semiestruturados em uma coluna VARIANT vs. nivelamento da estrutura aninhada¶

Se você ainda não tem certeza dos tipos de operações que deseja realizar em seus dados semiestruturados, o Snowflake recomenda armazenar os dados em uma coluna VARIANT, por enquanto.

Para dados que são em sua maioria regulares e usam apenas tipos de dados nativos do formato semiestruturado que você está usando (por exemplo, cadeias de caracteres e inteiros no formato JSON), os requisitos de armazenamento e o desempenho de consulta para operações em dados relacionais e dados em uma coluna VARIANT são muito semelhantes.

Para melhor remoção e menor consumo de armazenamento, o Snowflake recomenda nivelar seus dados de OBJECT e chaves em colunas relacionais separadas se seus dados semiestruturados incluírem:

• Datas e carimbos de data/hora, especialmente que não sejam ISO 8601, como valores de cadeias de caracteres

• Números dentro de cadeias de caracteres

• Matrizes

Valores não nativos (como datas e carimbos de data/hora em JSON) 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.

Se você conhece seus casos de uso para os dados, realize testes em um conjunto de dados típico. Carregue o conjunto de dados em uma coluna VARIANT de uma tabela. Use a função FLATTEN para extrair os OBJECTs e as chaves que você planeja consultar em uma tabela separada. Execute um conjunto típico de consultas em ambas as tabelas para ver qual estrutura oferece o melhor desempenho.

Conversão de pares chave-valor¶

Ao extrair pares chave-valor de uma coluna VARIANT, converta os valores para o tipo de dados desejado (usando a notação ::) para evitar resultados inesperados. Por exemplo, quando você extrai um par chave-valor da cadeia de caracteres sem converter, os resultados ficam entre aspas duplas (para mostrar que o valor VARIANT contém uma cadeia de caracteres e não outro tipo; isto é, "1" é uma cadeia de caracteres, enquanto 1 é um número):

SELECT col1:city;

+----------------------+
| CITY                 |
|----------------------|
| "Los Angeles"        |
+----------------------+

SELECT col1:city::string;

+----------------------+
| CITY                 |
|----------------------|
| Los Angeles          |
+----------------------+

Valores NULL¶

O Snowflake oferece suporte a dois tipos de valores NULL em dados semiestruturados:

• SQL NULL: SQL NULL significa a mesma coisa para tipos de dados semiestruturados e para tipos de dados estruturados: o valor está faltando ou é desconhecido.

• JSON nulo (às vezes chamado de “VARIANT NULL”): em uma coluna VARIANT, os valores JSON nulos são armazenados como uma cadeia de cadeia de caracteres contendo a palavra “null” para distingui-los dos valores SQL NULL.

O exemplo a seguir mostra a diferençã entre o SQL NULL e o JSON nulo:

select 
    parse_json(NULL) AS "SQL NULL", 
    parse_json('null') AS "JSON NULL", 
    parse_json('[ null ]') AS "JSON NULL",
    parse_json('{ "a": null }'):a AS "JSON NULL",
    parse_json('{ "a": null }'):b AS "ABSENT VALUE";
+----------+-----------+-----------+-----------+--------------+
| SQL NULL | JSON NULL | JSON NULL | JSON NULL | ABSENT VALUE |
|----------+-----------+-----------+-----------+--------------|
| NULL     | null      | [         | null      | NULL         |
|          |           |   null    |           |              |
|          |           | ]         |           |              |
+----------+-----------+-----------+-----------+--------------+

Copy

Para converter um valor VARIANT "null" para SQL NULL, faça a conversão dele para uma cadeia de caracteres. Por exemplo:

select 
    parse_json('{ "a": null }'):a,
    to_char(parse_json('{ "a": null }'):a);
+-------------------------------+----------------------------------------+
| PARSE_JSON('{ "A": NULL }'):A | TO_CHAR(PARSE_JSON('{ "A": NULL }'):A) |
|-------------------------------+----------------------------------------|
| null                          | NULL                                   |
+-------------------------------+----------------------------------------+

Copy

Arquivos de dados semiestruturados e colunarização¶

Quando dados semiestruturados são inseridos em uma coluna VARIANT, o Snowflake extrai o máximo possível dos dados para uma forma colunar, com base em certas regras. O restante é armazenado como uma única coluna em uma estrutura semiestruturada analisada. Atualmente, os elementos que possuem as seguintes características não são extraídos em uma coluna:

• Os elementos que contêm até mesmo um único valor “nulo” não são extraídos em uma coluna. Note que isso se aplica a elementos com valores “nulos” e não a elementos com valores ausentes, que são representados em forma de coluna.

  Essa regra garante que as informações não se percam, ou seja, a diferença entre valores “nulos” VARIANT e valores SQL NULL não é ofuscada.

• Elementos que contêm múltiplos tipos de dados. Por exemplo:

  O elemento foo em uma linha contém um número:

  {"foo":1}
  

  Copy

  O mesmo elemento em outra linha contém uma cadeia de caracteres:

  {"foo":"1"}
  

  Copy

Quando um elemento semiestruturado é consultado:

• Se o elemento tiver sido extraído em uma coluna, o mecanismo de execução do Snowflake (que é colunar) examina apenas a coluna extraída.

• Se o elemento não foi extraído em uma coluna, o mecanismo de execução deve examinar toda a estrutura JSON e então, para cada linha, percorrer a estrutura para emitir valores, impactando o desempenho.

Para evitar esse impacto no desempenho:

• Extraia elementos de dados semiestruturados contendo valores “nulos” em colunas relacionais antes de carregá-los.

  Como alternativa, se os valores “nulos” em seus arquivos indicarem valores ausentes e não tiverem nenhum outro significado especial, recomendamos definir a opção de formato do arquivo STRIP_NULL_VALUES como TRUE ao carregar os arquivos de dados semiestruturados. Essa opção remove elementos OBJECT ou ARRAY contendo valores “nulos”.

• Garanta que cada elemento único armazene valores de um único tipo de dados nativo no formato em questão (por exemplo, cadeia de caracteres ou número em JSON).

SELECT column1
  , TO_VARCHAR(PARSE_JSON(column1):a)
FROM
  VALUES('{"a" : null}')
, ('{"b" : "hello"}')
, ('{"a" : "world"}');

+-----------------+-----------------------------------+
| COLUMN1         | TO_VARCHAR(PARSE_JSON(COLUMN1):A) |
|-----------------+-----------------------------------|
| {"a" : null}    | NULL                              |
| {"b" : "hello"} | NULL                              |
| {"a" : "world"} | world                             |
+-----------------+-----------------------------------+

Como usar FLATTEN para filtrar os resultados em uma cláusula WHERE¶

A função FLATTEN detalha os valores aninhados em colunas separadas. Você pode usar a função para filtrar os resultados da consulta em uma cláusula WHERE.

O exemplo a seguir retorna pares chave-valor que correspondem a uma cláusula WHERE e os exibe em colunas separadas:

CREATE TABLE pets (v variant);

INSERT INTO pets SELECT PARSE_JSON ('{"species":"dog", "name":"Fido", "is_dog":"true"} ');
INSERT INTO pets SELECT PARSE_JSON ('{"species":"cat", "name":"Bubby", "is_dog":"false"}');
INSERT INTO pets SELECT PARSE_JSON ('{"species":"cat", "name":"dog terror", "is_dog":"false"}');

SELECT a.v, b.key, b.value FROM pets a,LATERAL FLATTEN(input => a.v) b
WHERE b.value LIKE '%dog%';

+-------------------------+---------+--------------+
| V                       | KEY     | VALUE        |
|-------------------------+---------+--------------|
| {                       | species | "dog"        |
|   "is_dog": "true",     |         |              |
|   "name": "Fido",       |         |              |
|   "species": "dog"      |         |              |
| }                       |         |              |
| {                       | name    | "dog terror" |
|   "is_dog": "false",    |         |              |
|   "name": "dog terror", |         |              |
|   "species": "cat"      |         |              |
| }                       |         |              |
+-------------------------+---------+--------------+

Como usar FLATTEN para listar nomes de chave distintos¶

Ao trabalhar com dados semiestruturados desconhecidos, você pode não saber os nomes das chaves em um OBJECT. Você pode usar a função FLATTEN com o argumento RECURSIVE para retornar a lista de nomes de chave distintos em todos os elementos aninhados em um OBJECT:

SELECT REGEXP_REPLACE(f.path, '\\[[0-9]+\\]', '[]') AS "Path",
  TYPEOF(f.value) AS "Type",
  COUNT(*) AS "Count"
FROM <table>,
LATERAL FLATTEN(<variant_column>, RECURSIVE=>true) f
GROUP BY 1, 2 ORDER BY 1, 2;

A função REGEXP_REPLACE remove os valores do índice da matriz (por exemplo, [0]) e os substitui por colchetes ([]) para agrupar os elementos da matriz.

Por exemplo:

{"a": 1, "b": 2, "special" : "data"}   <--- row 1 of VARIANT column
{"c": 3, "d": 4, "normal" : "data"}    <----row 2 of VARIANT column

Output from query:

+---------+---------+-------+
| Path    | Type    | Count |
|---------+---------+-------|
| a       | INTEGER |     1 |
| b       | INTEGER |     1 |
| c       | INTEGER |     1 |
| d       | INTEGER |     1 |
| normal  | VARCHAR |     1 |
| special | VARCHAR |     1 |
+---------+---------+-------+

Como usar FLATTEN para listar caminhos em um OBJECT¶

Em relação a Como usar FLATTEN para listar nomes de chaves distintos, você pode usar a função FLATTEN com o argumento RECURSIVE para recuperar todas as chaves e caminhos em um OBJECT..

A seguinte consulta retorna chaves, caminhos e valores (incluindo valores VARIANT “nulos”) para todos os tipos de dados armazenados em uma coluna VARIANT. O código assume que a coluna VARIANT contém um OBJECT em cada linha.

SELECT
  t.<variant_column>,
  f.seq,
  f.key,
  f.path,
  REGEXP_COUNT(f.path,'\\.|\\[') +1 AS Level,
  TYPEOF(f.value) AS "Type",
  f.index,
  f.value AS "Current Level Value",
  f.this AS "Above Level Value"
FROM <table> t,
LATERAL FLATTEN(t.<variant_column>, recursive=>true) f;

A consulta a seguir é semelhante à primeira consulta, mas exclui OBJECTs e ARRAYs aninhados:

SELECT
  t.<variant_column>,
  f.seq,
  f.key,
  f.path,
  REGEXP_COUNT(f.path,'\\.|\\[') +1 AS Level,
  TYPEOF(f.value) AS "Type",
  f.value AS "Current Level Value",
  f.this AS "Above Level Value"
FROM <table> t,
LATERAL FLATTEN(t.<variant_column>, recursive=>true) f
WHERE "Type" NOT IN ('OBJECT','ARRAY');

As consultas retornam os seguintes valores:

<coluna_variante>

OBJECT armazenado como uma linha na coluna VARIANT.

Seq

Número sequencial único associado aos dados da linha.

Chave

Cadeia de caracteres associada a um valor na estrutura de dados.

Caminho

Caminho do elemento dentro da estrutura de dados.

Nível

Nível do par chave-valor dentro da estrutura de dados.

Tipo

Tipo de dados do valor.

Índice

Índice do elemento na estrutura de dados. Aplica-se somente a valores ARRAY; caso contrário NULL.

Valor no nível atual

Valor no nível atual na estrutura de dados.

Valor acima do nível

Valor um nível mais alto na estrutura de dados.