RedshiftにJSONを格納してTableauで可視化してみた

nayu.t.s

2021.06.08

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

データアナリティクス事業本部の鈴木です。

先日、Amazon Redshiftで半構造化データをテーブルに直接取り込み、分析を可能にするSUPER型がGA（Generally Available）になりました。今回は、SUPER型で取り込んだJSONをTableauで可視化してみたので、ご紹介します。

RedshiftのSUPER型とは？

SUPER型はRedshiftのデータ型です。SUPER型を使用することで、JSONを始めとしたさまざまな形式の半構造化データをRedshiftに格納して、アクセスできます。

Amazon Redshift での半構造化データの取り込みとクエリ - Amazon Redshift

Tableauで可視化してみる

フィッシャーのアイリスデータセットをJSON形式に変換し、Redshiftに格納して、Tableauで可視化します。アイリスデータセットは言わずと知れた有名なデータセットで、3種のアヤメの情報が合計150サンプル分入っています。

テーブルを作成する

まず、データを格納するテーブルを作成します。今回は検証のため、アヤメのデータをJSONに変換してから格納したいので、iris_jsonという名前の列をSUPER型で定義しておきます。

CREATE TABLE iris(
      sampleid  INT   NOT NULL,
      iris_json SUPER
);

サンプルデータをRedshiftに格納する

アイリスデータセットは具体的には、scikit-learnのIris Datasetを使いました。半構造化データのRedshiftへのロードには「INSERTまたはUPDATEを使用する方法」と「COPYを使用する方法」がありますが、今回は簡単のため、前者で行います。データをロードし、挿入用のSQLを手作りしました。

import pandas as pd
from sklearn import datasets

# アヤメのデータをsklearnから読み出す。
iris = datasets.load_iris()

# pandasデータフレームに変換する。データをJSONに変換しやすいため。
# カラム名にスペースが入っていたので、扱いやすい名前に変えた。
feature_names = ["sepal_length", "sepal_width", "petal_length", "petal_width"]
df_iris = pd.DataFrame(iris["data"], columns=feature_names)

# 各行が何の種類なのか取り出す。
df_iris['species'] = iris.target_names[iris["target"]]

# 行ごとにJSON文字列に変換し、新しい列に格納する。
df_iris["iris_json"] = df_iris.apply(lambda row: row.to_json(), axis=1)

# 挿入用のSQL文を手作りする。
with open("./insert_iris.sql", "w") as f:
    f.write("insert into iris values\n")
    for i in range(0, len(df_iris[["iris_json"]])):
        row = f"({i+1}, JSON_PARSE(\'{df_iris.iloc[i]['iris_json']}\'))"
        if i != len(df_iris[["iris_json"]])-1:
            row += ",\n"
        else:
            row += ";\n"
        f.write(row)

以下のようなSQL文が出力されます。

insert into iris values
(1, JSON_PARSE('{"sepal_length":5.1,"sepal_width":3.5,"petal_length":1.4,"petal_width":0.2,"species":"setosa"}')),
・・・
（省略）
・・・
(150, JSON_PARSE('{"sepal_length":5.9,"sepal_width":3.0,"petal_length":5.1,"petal_width":1.8,"species":"virginica"}'));

JSON形式の文字列をJSON_PARSE関数に渡している点がポイントです。これによりRedshiftは、この文字列を解析対象だと認識してくれます。

続いて、Redshiftにデータを挿入します。手作りしたSQL文をDatabase Managerなどで実行します。私はDBeaverから操作を行いました。 JSON格納結果

このように、irisテーブルにデータを挿入できました。JSONがテーブル定義で決めたiris_jsonに挿入されているのが分かります。

マテリアライズドビューを作成する

Tableauからデータを参照するため、マテリアライズドビューを作成します。マテリアライズドビューの定義内でドット記法を使ってJSONの属性を展開することで、Tableauから値を読み取れるようにしました。

また、Redshiftのマテリアライズドビューは、新規作成した時に実行結果が保持されるため、Tableauからの読み取りの高速化が期待できます。代わりに、ソーステーブルが更新された場合にはリフレッシュが必要になります。

CREATE MATERIALIZED VIEW iris_materialized_view AS
    SELECT 
      sampleid,
      iris_json.sepal_length,
      iris_json.sepal_width,
      iris_json.petal_length,
      iris_json.petal_width,
      iris_json.species
      FROM iris;

マテリアライズドビューの中身はこのようになりました。構造化された形式になっています。

作成したマテリアライズドビュー