Carregamento de dados JSON em uma tabela relacional

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

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:

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.