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

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

VARIANT 型列に JSON オブジェクトをネイティブで格納する（チュートリアル: COPY の使用によるローカルファイルシステムからの一括ロードに示すとおり）。
中間テーブルに JSON オブジェクトをネイティブで格納してから、 FLATTEN 関数を使用して JSON 要素をテーブルの別の列に抽出する（チュートリアル: 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 が格納されている単一列を参照します。クエリは、次の関数も使用します。

state_city JSON キーから都市と州の値を抽出する SUBSTR , SUBSTRING 関数。
sale_date JSON キー値をタイムスタンプにキャストする TO_TIMESTAMP / TO_TIMESTAMP_*。

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

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

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