Carregamento de dados JSON em uma tabela relacional¶

Ao carregar os dados JSON em uma tabela, você tem estas opções:

Armazenar objetos JSON nativamente em uma coluna do tipo VARIANT (como mostrado em Tutorial: carregamento em massa a partir de um sistema de arquivo local usando COPY).
Armazenar o objeto JSON nativamente em uma tabela intermediária e depois usar a função FLATTEN para extrair elementos JSON em colunas separadas em uma tabela (como mostrado em Tutorial: noções básicas de JSON)
Transformar elementos JSON diretamente em colunas de tabela, como mostrado neste tutorial.

O comando COPY neste tutorial usa uma instrução SELECT para consultar elementos individuais em um arquivo JSON preparado.

Os comandos de exemplo fornecidos neste tutorial incluem uma instrução PUT. Recomendamos executar estes comandos em SnowSQL que oferece suporte ao comando PUT. Clientes como Snowsight e Classic Console não oferecem suporte ao comando PUT.

Neste tópico:

Pré-requisitos¶

Para este tutorial você vai precisar:

Baixar um arquivo de dados JSON fornecido do Snowflake.
Criar um banco de dados, uma tabela e um warehouse virtual para este tutorial.

O banco de dados, tabela e warehouse virtual são objetos básicos do Snowflake necessários para a maioria das atividades do Snowflake.

Arquivo de dados para carregamento¶

Para baixar uma amostra do arquivo de dados JSON, clique em sales.json. Se clicar no link não baixar o arquivo, clique com o botão direito do mouse no link e salve o link/arquivo em seu sistema de arquivos local.

O tutorial considera que você tenha descompactado o arquivo de dados JSON nos seguintes diretórios:

Linux/macOS: /tmp/load

Windows: C:\tempload

O arquivo de dados inclui amostra de dados JSON de vendas de casas. Um objeto JSON de exemplo é mostrado:

{
   "location": {
      "state_city": "MA-Lexington",
      "zip": "40503"
   },
   "sale_date": "2017-3-5",
   "price": "275836"
}

Copy

Criação do banco de dados, tabela e warehouse virtual¶

Os seguintes comandos criam objetos especificamente para uso com este tutorial. Quando tiver concluído o tutorial, você poderá descartar os objetos.

 create or replace database mydatabase;

 use schema mydatabase.public;

CREATE OR REPLACE TEMPORARY TABLE home_sales (
  city STRING,
  zip STRING,
  state STRING,
  type STRING DEFAULT 'Residential',
  sale_date timestamp_ntz,
  price STRING
  );

create or replace warehouse mywarehouse with
  warehouse_size='X-SMALL'
  auto_suspend = 120
  auto_resume = true
  initially_suspended=true;

use warehouse mywarehouse;

Copy

Observe que estes comandos criam uma tabela temporária. Uma tabela temporária existe apenas pela duração da sessão do usuário e não fica visível para os outros usuários.

Etapa 1: criar objetos de formato de arquivo¶

Execute o comando CREATE FILE FORMAT para criar o formato de arquivo sf_tut_json_format.

CREATE OR REPLACE FILE FORMAT sf_tut_json_format
  TYPE = JSON;

Copy

TYPE = 'JSON' indica o tipo de formato do arquivo de origem. CSV é o tipo de formato de arquivo padrão.

Etapa 2: criar objeto de preparação¶

Execute CREATE STAGE para criar o estágio interno sf_tut_stage.

CREATE OR REPLACE TEMPORARY STAGE sf_tut_stage
 FILE_FORMAT = sf_tut_json_format;

Copy

Assim como as tabelas temporárias, os estágios temporários são automaticamente descartados ao final da sessão.

Etapa 3: preparar arquivo de dados¶

Execute o comando PUT para carregar o arquivo JSON de seu sistema de arquivos local no estágio nomeado.

Linux ou macOS

PUT file:///tmp/load/sales.json @sf_tut_stage AUTO_COMPRESS=TRUE;

Copy

Windows

PUT file://C:\temp\load\sales.json @sf_tut_stage AUTO_COMPRESS=TRUE;

Copy

Etapa 4: copiar dados na tabela de destino¶

Carregue o arquivo de dados preparado sales.json.gz na tabela home_sales.

COPY INTO home_sales(city, state, zip, sale_date, price)
   FROM (SELECT SUBSTR($1:location.state_city,4),
                SUBSTR($1:location.state_city,1,2),
                $1:location.zip,
                to_timestamp_ntz($1:sale_date),
                $1:price
         FROM @sf_tut_stage/sales.json.gz t)
   ON_ERROR = 'continue';

Copy

Observe que o $1 na consulta SELECT refere-se à coluna única na qual o JSON é armazenado. A consulta também usa as seguintes funções:

A função SUBSTR , SUBSTRING para extrair valores de cidade e estado da chave JSON estado_cidade.
O TO_TIMESTAMP / TO_TIMESTAMP_* para converter a chave JSON vendas_data em um carimbo de data/hora.

Execute a seguinte consulta para verificar se os dados foram copiados.

SELECT * from home_sales;

Copy

Etapa 5: remover arquivos de dados copiados com sucesso¶

Após verificar que copiou com sucesso os dados de seu estágio nas tabelas, você pode remover os arquivos de dados do estágio interno usando o comando REMOVE para salvar no armazenamento de dados.

REMOVE @sf_tut_stage/sales.json.gz;

Copy

Etapa 6: limpeza¶

Execute os seguintes comandos DROP <objeto> para retornar o sistema ao seu estado antes de iniciar o tutorial:

DROP DATABASE IF EXISTS mydatabase;
DROP WAREHOUSE IF EXISTS mywarehouse;

Copy

Descartar o banco de dados remove automaticamente todos os objetos do banco de dados filho, tais como tabelas.