リレーショナルテーブルへの JSON データのロード

テーブルに JSON データをアップロードする際には、次のオプションがあります。

このチュートリアルの COPY コマンドは、ステージングされた JSON ファイルの個別要素に対するクエリに SELECT ステートメントを使用します。

このチュートリアルで示すコマンド例には、 PUT ステートメントが含まれます。これらコマンドは、PUT コマンドをサポートする SnowSQL で実行することをお勧めします。 Snowsight や Classic Console などのクライアントは、 PUT コマンドをサポートしていません。

このトピックの内容:

前提条件

このチュートリアルのために、次を実行する必要があります。

  • Snowflake が提供する JSON データファイルをダウンロードする。

  • このチュートリアル用のデータベース、テーブル、および仮想ウェアハウスを作成する。

データベース、テーブル、および仮想ウェアハウスは、Snowflakeのほとんどのアクティビティに必要となる基本的なSnowflakeオブジェクトです。

ロード用のデータファイル

サンプル JSON データファイルをダウンロードするには、 sales.json をクリックします。リンクをクリックしてもファイルがダウンロードされない場合は、リンクを右クリックして、リンク/ファイルをローカルファイルシステムに保存します。

このチュートリアルでは、次のディレクトリに JSON データファイルを解凍したことを前提としています。

  • Linux/macOS: /tmp/load

  • Windows: C:\tempload

サンプル住宅売上 JSON データを含むデータファイル。JSON オブジェクト例を次に示します。

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

データベース、テーブル、および仮想ウェアハウスの作成

次のコマンドにより、このチュートリアルで使用するためのオブジェクトを作成します。チュートリアルを完了したら、オブジェクトをドロップできます。

 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

これらのコマンドは、仮テーブルを作成することに注意してください。仮テーブルは、ユーザーセッションの間のみ保持され、他のユーザーには表示されません。

ステップ1: ファイル形式オブジェクトを作成する

CREATE FILE FORMAT コマンドを実行して、 sf_tut_json_format ファイル形式を作成します。

CREATE OR REPLACE FILE FORMAT sf_tut_json_format
  TYPE = JSON;
Copy

TYPE = 'JSON' は、ソースファイル形式の種類を示します。CSV は、デフォルトのファイル形式の種類です。

ステップ2: ステージオブジェクトを作成する

CREATE STAGE を実行して内部 sf_tut_stage ステージを作成します。

CREATE OR REPLACE TEMPORARY STAGE sf_tut_stage
 FILE_FORMAT = sf_tut_json_format;
Copy

仮ステージは、仮テーブルのように、セッションの終了時に自動的にドロップされます。

ステップ3: データファイルをステージングする

PUT コマンドを実行して、 JSON ファイルをローカルファイルシステムから名前付きステージにアップロードします。

  • Linuxまたは 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

ステップ4: データをターゲットテーブルにコピーする

sales.json.gz ステージングデータファイルを 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

SELECT クエリの$1は、 JSON が格納されている単一列を参照します。クエリは、次の関数も使用します。

次のクエリを実行して、データがコピーされたことを検証します。

SELECT * from home_sales;
Copy

ステップ5: 正常にコピーされたデータファイルを削除する

ステージからテーブルにデータが正常にコピーされたことを確認した後に、 REMOVE コマンドを使用して内部ステージからデータファイルを削除し、 データストレージ を節約することができます。

REMOVE @sf_tut_stage/sales.json.gz;
Copy

ステップ6: クリーンアップする

次の DROP <オブジェクト> コマンドを実行して、システムをチュートリアルを開始する前の状態に戻します。

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

データベースをドロップすると、テーブルなどのすべての子データベースオブジェクトが自動的に削除されます。