Considerações sobre dados semiestruturados armazenados em VARIANT

Este tópico apresenta práticas recomendadas, diretrizes gerais e considerações importantes para carregar e trabalhar com valores VARIANT que contenham dados semiestruturados. Esses dados podem ser dados hierárquicos construídos explicitamente ou dados carregados de formatos de dados semiestruturados como JSON, Avro, ORC e Parquet. As informações neste tópico não se aplicam necessariamente aos dados XML.

Neste tópico:

Limitações de tamanho dos dados

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

Para obter mais informações, consulte VARIANT.

Em geral, conjuntos de dados JSON são uma simples concatenação de vários 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);
Copy

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.

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 usa determinadas regras para extrair o máximo possível dos dados para uma forma de coluna. O restante dos dados é armazenado como uma única coluna em uma estrutura semiestruturada analisada.

Por padrão, o Snowflake extrai no máximo 200 elementos por partição, por tabela. Para aumentar este limite, entre em contato com o suporte Snowflake.

Elementos que não são extraídos

Elementos com 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. Isso se aplica a elementos com valores “nulos” e não a elementos com valores ausentes, que são representados em forma de coluna.

    Esta regra garante que nenhuma informação seja perdida (ou seja, que a diferença entre valores de VARIANT “nulos” e valores SQL NULL não seja perdida).

  • 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

Como a extração afeta as consultas

Quando você consulta um elemento semiestruturado, o mecanismo de execução do Snowflake se comporta de maneira diferente se um elemento tiver sido extraído.

  • Se o elemento tiver sido extraído em uma coluna, o mecanismo verificará apenas a coluna extraída.

  • Se o elemento não tiver sido extraído em uma coluna, o mecanismo deve examinar toda a estrutura JSON e então, para cada linha, percorrer a estrutura para emitir valores. Isso afeta o desempenho.

Para evitar o impacto no desempenho de elementos que não foram extraídos, faça o seguinte:

  • 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”.

  • Certifique-se de 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).

Análise de valores NULL

Para gerar um valor SQL NULL a partir de um par chave-valor VARIANT "null", use a função TO_CHAR , TO_VARCHAR para converter o valor como uma cadeia de caracteres, por exemplo:

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                             |
+-----------------+-----------------------------------+
Copy
Linguagem: Português