Formatos compatíveis para dados semiestruturados

Este tópico descreve os formatos compatíveis de dados semiestruturados.

O Snowflake oferece suporte nativo aos formatos de dados semiestruturados abaixo. Especificamente, o Snowflake fornece opções nos comandos COPY para carregar e descarregar arquivos de dados nesses formatos.

JSON

O que é JSON?

O JSON (JavaScript Object Notation) é um formato leve, de texto sem formatação e troca de dados baseado em um subconjunto da linguagem de programação JavaScript.

Os dados JSON podem ser produzidos por qualquer aplicativo. Alguns exemplos comuns incluem:

  • Aplicativos em JavaScript que usam métodos nativos para gerar JSON.

  • Aplicativos que não são em JavaScript e usam bibliotecas (geralmente com extensões) para gerar dados JSON.

  • Geradores de JavaScript ad hoc.

  • Concatenação de documentos JSON (que podem ou não ser separados por linha).

Como não há uma especificação formal, existem diferenças significativas entre as várias implementações. Essas diferenças tornam impossível a importação de conjuntos de dados do tipo JSON se o analisador JSON for rigoroso na definição de linguagem. Para reduzir ao máximo os problemas com a importação de conjuntos de dados JSON, o Snowflake segue a regra «seja liberal no que você aceita». A intenção é aceitar a maior variedade possível de entradas JSON e semelhantes ao JSON que permitam uma interpretação inequívoca.

Este tópico descreve a sintaxe de documentos JSON aceitos pelo Snowflake.

Para obter mais informações sobre o JSON, consulte json.org.

Sintaxe básica do JSON

Os dados JSON são uma coleção hierárquica de pares nome/valor agrupados em objetos e matrizes:

  • Dois pontos : separam os nomes e valores nos pares nome/valor.

  • Chaves {} denotam os objetos.

  • Colchetes [] denotam as matrizes.

  • Vírgulas , separam as entidades em objetos e matrizes.

Pares nome-valor

Os pares nome/valor JSON consistem de um nome de campo (entre aspas duplas), seguido por dois pontos e depois um valor.

Por exemplo:

{"firstName":"John", "empid":45611}
Copy

Tipos de dados suportados

Um valor em um par nome/valor pode ser:

  • Um número (inteiro ou ponto flutuante)

  • Uma cadeia de caracteres (entre aspas duplas)

  • Um booliano (verdadeiro ou falso)

  • Uma matriz (entre colchetes)

  • Um objeto (entre chaves)

  • Nulo

Objetos

Os objetos JSON são escritos entre chaves. Um objeto pode conter vários pares de nomes/valores, separados por vírgulas. Por exemplo:

{"firstName":"John", "lastName":"Doe"}
Copy

Matrizes

As matrizes JSON são escritas entre colchetes. Uma matriz pode conter vários objetos, separados por vírgulas. Por exemplo:

{"employees":[
    {"firstName":"John", "lastName":"Doe"},
    {"firstName":"Anna", "lastName":"Smith"},
    {"firstName":"Peter", "lastName":"Jones"}
  ]
}
Copy

Exemplos de documentos JSON

FILE NAME: json_sample_data1

Contém uma matriz com 3 registros simples de funcionários (objetos):

{"root":[{"employees":[
    {"firstName":"John", "lastName":"Doe"},
    {"firstName":"Anna", "lastName":"Smith"},
    {"firstName":"Peter", "lastName":"Jones"}
]}]}
Copy

FILE NAME: json_sample_data2

Contém uma matriz com 3 registros de funcionários (objetos) e seus dados de dependentes associados (filhos, nomes e idades dos filhos, cidades onde o funcionário morou e os anos em que o funcionário morou nessas cidades):

{"root":
   [
    { "kind": "person",
      "fullName": "John Doe",
      "age": 22,
      "gender": "Male",
      "phoneNumber":
        {"areaCode": "206",
         "number": "1234567"},
      "children":
         [
           {
             "name": "Jane",
             "gender": "Female",
             "age": "6"
           },
           {
              "name": "John",
              "gender": "Male",
              "age": "15"
           }
         ],
      "citiesLived":
         [
            {
               "place": "Seattle",
               "yearsLived": ["1995"]
            },
            {
               "place": "Stockholm",
               "yearsLived": ["2005"]
            }
         ]
      },
      {"kind": "person", "fullName": "Mike Jones", "age": 35, "gender": "Male", "phoneNumber": { "areaCode": "622", "number": "1567845"}, "children": [{ "name": "Earl", "gender": "Male", "age": "10"}, {"name": "Sam", "gender": "Male", "age": "6"}, { "name": "Kit", "gender": "Male", "age": "8"}], "citiesLived": [{"place": "Los Angeles", "yearsLived": ["1989", "1993", "1998", "2002"]}, {"place": "Washington DC", "yearsLived": ["1990", "1993", "1998", "2008"]}, {"place": "Portland", "yearsLived": ["1993", "1998", "2003", "2005"]}, {"place": "Austin", "yearsLived": ["1973", "1998", "2001", "2005"]}]},
      {"kind": "person", "fullName": "Anna Karenina", "age": 45, "gender": "Female", "phoneNumber": { "areaCode": "425", "number": "1984783"}, "citiesLived": [{"place": "Stockholm", "yearsLived": ["1992", "1998", "2000", "2010"]}, {"place": "Russia", "yearsLived": ["1998", "2001", "2005"]}, {"place": "Austin", "yearsLived": ["1995", "1999"]}]}
    ]
}
Copy

Avro

O que é Avro?

O Avro é uma estrutura de código aberto para serialização de dados e RPC, desenvolvida originalmente para uso com o Apache Hadoop. Ele utiliza esquemas definidos em JSON para produzir dados serializados em um formato binário compacto. Os dados serializados podem ser enviados para qualquer destino (ou seja, aplicativo ou programa) e podem ser facilmente desserializados no destino porque o esquema está incluído nos dados.

Um esquema Avro consiste em uma cadeia de caracteres, objeto ou matriz JSON que define o tipo de esquema e os atributos de dados (nomes de campo, tipos de dados, etc.) do tipo de esquema. Os atributos variam de acordo com o tipo de esquema. Há suporte para tipos de dados complexos, como matrizes e mapas.

O Snowflake lê os dados Avro em uma única coluna VARIANT. Você pode consultar os dados em uma coluna VARIANT assim como faria com dados JSON, usando comandos e funções similares.

Para obter mais informações, consulte avro.apache.org.

Exemplo de um esquema Avro

{
 "type": "record",
 "name": "person",
 "namespace": "example.avro",
 "fields": [
     {"name": "fullName", "type": "string"},
     {"name": "age",  "type": ["int", "null"]},
     {"name": "gender", "type": ["string", "null"]}
     ]
}
Copy

ORC

O que é ORC?

ORC (Optimized Row Columnar) é um formato binário usado para armazenar dados do Hive. O ORC foi projetado para realizar uma compactação eficiente e melhorar o desempenho de leitura, gravação e processamento de dados em relação aos formatos de arquivos anteriores da Hive. Para obter mais informações sobre o ORC, consulte https://orc.apache.org/.

Snowflake lê os dados ORC em apenas uma coluna VARIANT. Você pode consultar os dados em uma coluna VARIANT assim como faria com dados JSON, usando comandos e funções similares.

Como alternativa, você pode extrair colunas de um arquivo ORC preparado em colunas de tabela separadas usando uma instrução CREATE TABLE AS SELECT.

Nota

  • Os dados do mapa são desserializados em uma matriz de objetos, por exemplo:

    "map": [{"key": "chani", "value": {"int1": 5, "string1": "chani"}}, {"key": "mauddib", "value": {"int1": 1, "string1": "mauddib"}}]
    Copy

  • Os dados de união são desserializados em um único objeto, por exemplo:

    {"time": "1970-05-05 12:34:56.197", "union": {"tag": 0, "value": 3880900}, "decimal": 3863316326626557453.000000000000000000}
    Copy

Exemplo de dados ORC carregados em uma coluna VARIANT

+--------------------------------------+
| SRC                                  |
|--------------------------------------|
| {                                    |
|   "boolean1": false,                 |
|   "byte1": 1,                        |
|   "bytes1": "0001020304",            |
|   "decimal1": 12345678.654745,       |
|   "double1": -1.500000000000000e+01, |
|   "float1": 1.000000000000000e+00,   |
|   "int1": 65536,                     |
|   "list": [                          |
|     {                                |
|       "int1": 3,                     |
|       "string1": "good"              |
|     },                               |
|     {                                |
|       "int1": 4,                     |
|       "string1": "bad"               |
|     }                                |
|   ]                                  |
| }                                    |
+--------------------------------------+

Parquet

O que é Parquet?

O Parquet é uma representação compacta e eficiente de dados colunares, elaborada para projetos no ecossistema do Hadoop. Esse formato de arquivo oferece suporte a estruturas complexas de dados aninhados e utiliza os algoritmos de fragmentação e montagem de registros de Dremel. Os arquivos Parquet não podem ser abertos em um editor de texto. Para obter mais informações, consulte parquet.apache.org/docs/.

Nota

O Snowflake oferece suporte a arquivos Parquet produzidos com o gravador Parquet V2 para tabelas Apache Iceberg™ ou ao usar um scanner vetorizado.

Dependendo do caso de uso do carregamento, o Snowflake lê os dados do Parquet em uma única coluna VARIANT ou diretamente nas colunas da tabela (como quando você carrega dados de arquivos Parquet compatíveis com o Iceberg).

Você pode consultar os dados em uma coluna VARIANT assim como faria com dados JSON, usando comandos e funções similares. Como alternativa, você pode extrair colunas selecionadas de um arquivo preparado Parquet para inserir em diferentes colunas de tabela usando uma instrução CREATE TABLE AS SELECT.

Exemplo de dados Parquet carregados em uma coluna VARIANT

+------------------------------------------+
| SRC                                      |
|------------------------------------------|
| {                                        |
|   "continent": "Europe",                 |
|   "country": {                           |
|     "city": {                            |
|       "bag": [                           |
|         {                                |
|           "array_element": "Paris"       |
|         },                               |
|         {                                |
|           "array_element": "Nice"        |
|         },                               |
|         {                                |
|           "array_element": "Marseilles"  |
|         },                               |
|         {                                |
|           "array_element": "Cannes"      |
|         }                                |
|       ]                                  |
|     },                                   |
|     "name": "France"                     |
|   }                                      |
| }                                        |
+------------------------------------------+

XML

O que é XML?

XML (eXtensible Markup Language) é uma linguagem de marcação que define um conjunto de regras para a codificação de documentos. Originalmente, ele foi baseado no SGML, outra linguagem de marcação desenvolvida para padronizar a estrutura e os elementos que compõem um documento.

Desde seu lançamento, o XML cresceu para além do seu foco inicial em documentos e hoje abrange uma ampla gama de usos, entre eles a representação de estruturas de dados arbitrárias e servindo como linguagem base para protocolos de comunicação. Devido a sua extensibilidade, versatilidade e usabilidade, tornou-se um dos padrões mais utilizados para a troca de dados na Web.

Um documento XML consiste principalmente das seguintes construções:

  • Tags (identificadas por colchetes angulares, < e >)

  • Elementos

Os elementos geralmente consistem em uma tag de “início” e uma tag de “fim” correspondente, com o texto entre as tags constituindo o conteúdo do elemento. Um elemento também pode consistir em uma tag “empty-element” sem uma tag “end”. As tags “start” e “empty-element” podem conter atributos, que ajudam a definir as características ou os metadados do elemento.

Ao consultar os dados do XML, o operador de cifrão ($) retorna o conteúdo, como um valor VARIANT, do valor em que opera. Para um elemento, o conteúdo desse elemento é retornado:

  • Se o elemento contiver texto, o texto será retornado como um valor VARIANT.

  • Se o elemento contiver outro elemento, ele será retornado como um valor VARIANT no formato XML.

  • Se o elemento contiver uma série de elementos, uma matriz dos elementos será retornada como um valor VARIANT no formato JSON.

Use os seguintes operadores para acessar o valor VARIANT em uma consulta:

  • $ para o conteúdo do valor.

  • @ para o nome do valor. Esse operador é útil quando você está iterando por elementos com nomes diferentes.

    Use @attribute_name para obter o conteúdo de um atributo nomeado. Por exemplo, para @attr, o nome do atributo é attr. A consulta retorna o conteúdo do atributo com o nome que vem logo após o “E comercial” (&). Se nenhum atributo for encontrado, NULL é retornado.

Para exemplos que consultam os dados XML, consulte Exemplos de consultas aos dados XML.

Você pode usar as seguintes funções para trabalhar com os dados XML:

Exemplos de trabalho com XML

Os exemplos a seguir mostram como carregar e consultar os dados XML.

Exemplo de carregamento de um documento XML

Este exemplo mostra como você pode carregar o seguinte documento XML:

<?xml version="1.0"?>
<!DOCTYPE parts system "parts.dtd">
<?xml-stylesheet type="text/css" href="xmlpartsstyle.css"?>
<parts>
   <part count="4">
      <item>Spark Plugs</item>
      <partnum>A3-400</partnum>
      <manufacturer>ABC company</manufacturer>
      <price units="dollar"> 27.00</price>
   </part>
   <part count="1">
      <item>Motor Oil</item>
      <partnum>B5-200</partnum>
      <source>XYZ company</source>
      <price units="dollar"> 14.00</price>
   </part>
   <part count="1">
      <item>Motor Oil</item>
      <partnum>B5-300</partnum>
      <source>XYZ company</source>
      <price units="dollar"> 16.75</price>
   </part>
   <part count="1">
      <item>Engine Coolant</item>
      <partnum>B6-120</partnum>
       <source>XYZ company</source>
      <price units="dollar"> 19.00</price>
   </part>
   <part count="1">
      <item>Engine Coolant</item>
      <partnum>B6-220</partnum>
      <source>XYZ company</source>
      <price units="dollar"> 18.25</price>
   </part>
</parts>
Copy

Conclua as etapas a seguir para carregar o documento XML:

  1. Copie o conteúdo do documento XML em um arquivo em seu sistema de arquivos.

    Este exemplo pressupõe que o arquivo tenha o nome auto-parts.xml no diretório /examples/xml/.

  2. Prepare o arquivo no local de estágio interno:

    PUT FILE:///examples/xml/auto-parts.xml @~/xml_stage;
    Copy

  3. Crie uma tabela para o documento XML:

    CREATE OR REPLACE TABLE sample_xml_parts(src VARIANT);
    Copy

  4. Carregue o arquivo preparado XML na tabela:

    COPY INTO sample_xml_parts
  FROM @~/xml_stage
  FILE_FORMAT=(TYPE=XML) ON_ERROR='CONTINUE';
    Copy

Exemplos de consultas aos dados XML

Esses exemplos consultam os dados XML.

Consultando diretamente os dados XML

Consulte a coluna que contém os dados XML para retornar o documento XML.

Este exemplo consulta diretamente os dados XML carregados em Exemplo de carregamento de um documento XML:

SELECT src FROM sample_xml_parts;
Copy
+----------------------------------------------+
| SRC                                          |
|----------------------------------------------|
| <parts>                                      |
|   <part count="4">                           |
|     <item>Spark Plugs</item>                 |
|     <partnum>A3-400</partnum>                |
|     <manufacturer>ABC company</manufacturer> |
|     <price units="dollar">27.00</price>      |
|   </part>                                    |
|   <part count="1">                           |
|     <item>Motor Oil</item>                   |
|     <partnum>B5-200</partnum>                |
|     <source>XYZ company</source>             |
|     <price units="dollar">14.00</price>      |
|   </part>                                    |
|   <part count="1">                           |
|     <item>Motor Oil</item>                   |
|     <partnum>B5-300</partnum>                |
|     <source>XYZ company</source>             |
|     <price units="dollar">16.75</price>      |
|   </part>                                    |
|   <part count="1">                           |
|     <item>Engine Coolant</item>              |
|     <partnum>B6-120</partnum>                |
|     <source>XYZ company</source>             |
|     <price units="dollar">19.00</price>      |
|   </part>                                    |
|   <part count="1">                           |
|     <item>Engine Coolant</item>              |
|     <partnum>B6-220</partnum>                |
|     <source>XYZ company</source>             |
|     <price units="dollar">18.25</price>      |
|   </part>                                    |
| </parts>                                     |
+----------------------------------------------+
Consulte os dados XML usando operadores

Consulte a coluna que contém os dados XML usando os operadores $ e @.

Este exemplo consulta os dados XML carregados em Exemplo de carregamento de um documento XML usando o operador $. A consulta mostra metadados sobre os valores ($) e nomes (@) dos elementos.

SELECT src:"$" FROM sample_xml_parts;
Copy
+--------------------------------+
| SRC:"$"                        |
|--------------------------------|
| [                              |
|   {                            |
|     "$": [                     |
|       {                        |
|         "$": "Spark Plugs",    |
|         "@": "item"            |
|       },                       |
|       {                        |
|         "$": "A3-400",         |
|         "@": "partnum"         |
|       },                       |
|       {                        |
|         "$": "ABC company",    |
|         "@": "manufacturer"    |
|       },                       |
|       {                        |
|         "$": 27,               |
|         "@": "price",          |
|         "@units": "dollar"     |
|       }                        |
|     ],                         |
|     "@": "part",               |
|     "@count": 4,               |
|     "item": 0,                 |
|     "manufacturer": 2,         |
|     "partnum": 1,              |
|     "price": 3                 |
|   },                           |
|   {                            |
|     "$": [                     |
|       {                        |
|         "$": "Motor Oil",      |
|         "@": "item"            |
|       },                       |
|       {                        |
|         "$": "B5-200",         |
|         "@": "partnum"         |
|       },                       |
|       {                        |
|         "$": "XYZ company",    |
|         "@": "source"          |
|       },                       |
|       {                        |
|         "$": 14,               |
|         "@": "price",          |
|         "@units": "dollar"     |
|       }                        |
|     ],                         |
|     "@": "part",               |
|     "@count": 1,               |
|     "item": 0,                 |
|     "partnum": 1,              |
|     "price": 3,                |
|     "source": 2                |
|   },                           |
|                                |
|              ...               |
|                                |
+--------------------------------+

Este exemplo consulta os mesmos dados XML usando o operador @. A consulta mostra o nome do elemento raiz.

SELECT src:"@" FROM sample_xml_parts;
Copy
+---------+
| SRC:"@" |
|---------|
| "parts" |
+---------+

Este exemplo consulta os mesmos dados XML usando o operador $ e o operador @. Na matriz de elementos filho no elemento raiz, a consulta mostra o valor do atributo count para o elemento no primeiro (0) e no segundo (1) índice.

SELECT src:"$"[0]."@count", src:"$"[1]."@count" FROM sample_xml_parts;
Copy
+---------------------+---------------------+
| SRC:"$"[0]."@COUNT" | SRC:"$"[1]."@COUNT" |
|---------------------+---------------------|
| 4                   | 1                   |
+---------------------+---------------------+
Consulta dos dados XML usando a função XMLGET

Consulte a coluna que contém os dados XML usando a função XMLGET.

Este exemplo consulta os dados XML carregados em Exemplo de carregamento de um documento XML e retorna a primeira instância de um elemento no elemento raiz dos dados XML. O número da instância é baseado em 0, não em 1. Portanto, as consultas a seguir são equivalentes:

SELECT XMLGET(src, 'part') FROM sample_xml_parts;

SELECT XMLGET(src, 'part', 0) FROM sample_xml_parts;
Copy
+--------------------------------------------+
| XMLGET(SRC, 'PART')                        |
|--------------------------------------------|
| <part count="4">                           |
|   <item>Spark Plugs</item>                 |
|   <partnum>A3-400</partnum>                |
|   <manufacturer>ABC company</manufacturer> |
|   <price units="dollar">27.00</price>      |
| </part>                                    |
+--------------------------------------------+

Essa consulta retorna o terceiro elemento (baseado em 0) no elemento raiz dos dados XML.

SELECT XMLGET(src, 'part', 3) FROM sample_xml_parts;
Copy
+---------------------------------------+
| XMLGET(SRC, 'PART', 3)                |
|---------------------------------------|
| <part count="1">                      |
|   <item>Engine Coolant</item>         |
|   <partnum>B6-120</partnum>           |
|   <source>XYZ company</source>        |
|   <price units="dollar">19.00</price> |
| </part>                               |
+---------------------------------------+
Consulta de dados XML para extrair o conteúdo do elemento usando várias funções

Este exemplo usa a função FLATTEN com a função XMLGET para extrair o conteúdo dos elementos nos dados XML carregados em Exemplo de carregamento de um documento XML.

O exemplo usa a função COALESCE para retornar o elemento filho manufacturer ou source, se existir, convertido em um valor VARCHAR. O SRC:"$" passado para FLATTEN especifica o valor no elemento raiz parts. O LATERAL FLATTEN itera por todos os elementos de repetição que são passados.

SELECT XMLGET(VALUE, 'item'):"$"::VARCHAR AS item,
       XMLGET(VALUE, 'partnum'):"$"::VARCHAR AS partnum,
       COALESCE(XMLGET(VALUE, 'manufacturer'):"$"::VARCHAR,
                XMLGET(VALUE, 'source'):"$"::VARCHAR) AS manufacturer_or_source,
       XMLGET(VALUE, 'price'):"$"::VARCHAR AS price,
  FROM sample_xml_parts,
    LATERAL FLATTEN(INPUT => SRC:"$");
Copy
+----------------+---------+------------------------+-------+
| ITEM           | PARTNUM | MANUFACTURER_OR_SOURCE | PRICE |
|----------------+---------+------------------------+-------|
| Spark Plugs    | A3-400  | ABC company            | 27    |
| Motor Oil      | B5-200  | XYZ company            | 14    |
| Motor Oil      | B5-300  | XYZ company            | 16.75 |
| Engine Coolant | B6-120  | XYZ company            | 19    |
| Engine Coolant | B6-220  | XYZ company            | 18.25 |
+----------------+---------+------------------------+-------+
Linguagem: Português