半構造化データの取り扱い

Snowflake SQL 入門

George Boorman

Senior Curriculum Manager, DataCamp

構造化データと半構造化データ

構造化データの例

| cust_id | cust_name | cust_age | cust_email            |
|---------|-----------|----------|-----------------------|
| 1       | cust1     | 40       | cust1***@gmail.com    |
| 2       | cust2     | 35       | cust2***@gmail.com    |
| 3       | cust3     | 42       | cust3***@gmail.com    |

半構造化データの例

JSON形式の顧客の半構造化データ。顧客2には2つのメールアドレスがあります

Gitについて

JavaScript Object Notation
一般的な使用例: Web API と設定ファイル
JSONデータ構造:
- キーと値のペア、例: cust_id: 1

顧客ID、名前、年齢、メールを含む顧客JSONレコードの例

Snowflake の JSON

ネイティブJSONサポート
進化するスキーマに柔軟に対応

比較：

Postgres: JSONB {{3}}を使用します
Snowflake: VARIANT {{3}}を使用します

SnowflakeがJSONデータを保存する方法

VARIANTはOBJECTおよびARRAYデータ型{{1}}をサポートします
- オブジェクト: { "key": "value"}
- ARRAY: ["list", "of", "values"]

JSONデータを処理するためのSnowflakeテーブルの作成

CREATE TABLE cust_info_json_data (
  customer_id INT, 
  customer_info VARIANT -- VARIANT data type
);

半構造化データ関数

PARSE_JSON
- 構文： PARSE_JSON( <expr> )
- expr: 文字列形式のJSONデータ
- 戻り値: VARIANT 型、正しい JSON オブジェクト

PARSE_JSON

例：

SELECT PARSE_JSON(
-- Enclosed in strings      
'{
"cust_id": 1,
"cust_name": "cust1",
"cust_age": 40,
"cust_email":"cust1***@gmail.com"
}
'-- Enclosed in strings
) AS customer_info_json

PARSE_JSONの結果

OBJECT_CONSTRUCT

OBJECT_CONSTRUCT
- 構文： OBJECT_CONSTRUCT( [<key1>, <value1> [, <keyN>, <valueN> ...]] )
- 戻り値: JSONオブジェクト

    SELECT OBJECT_CONSTRUCT(
      -- Comma separated values rather than : notation
          'cust_id', 1,
          'cust_name', 'cust1',
          'cust_age', 40,
          'cust_email', 'cust1***@gmail.com'
    )

JSON形式でデータが保存された`customer_info`列の表

SnowflakeでJSONデータをクエリする

シンプルなJSON

:

SELECT
  customer_info:cust_age, -- Use colon to access cust_age from column
  customer_info:cust_name,
  customer_info:cust_email,
FROM 
  cust_info_json_data;

顧客の年齢、顧客名、メールアドレスを別々の列として表示するクエリ結果

SnowflakeでネストされたJSONデータをクエリする

ネストされたJSONの例

住所がオブジェクトで、street、city、state のキーと値のペアを持つ入れ子の JSON データ

値段は:
値段は.

コロン/ドット表記を使用したネストされたJSONのクエリ

コロン表記を使用して値にアクセスする

<column>:<level1_element>:<level2_element>:<level3_element>

SELECT 
    customer_info:address:street AS street_name 
FROM 
    cust_info_json_data

street_name のクエリ結果を表示

ドット記法を使用して値にアクセスする

<column>:<level1_element>.<level2_element>.<level3_element>

SELECT
    customer_info:address.street AS street_name
FROM
    cust_info_json_data

street_name のクエリ結果を表示

練習しましょう！

Snowflake SQL 入門