この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。
データアナリティクス事業本部の鈴木です。
先日、Amazon Redshiftで半構造化データをテーブルに直接取り込み、分析を可能にするSUPER型がGA（Generally Available）になりました。 今回は、SUPER型で取り込んだJSONをTableauで可視化してみたので、ご紹介します。
RedshiftのSUPER型とは？
SUPER型はRedshiftのデータ型です。SUPER型を使用することで、JSONを始めとしたさまざまな形式の半構造化データを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_iris.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から操作を行いました。
このように、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;マテリアライズドビューの中身はこのようになりました。構造化された形式になっています。
Tableauからマテリアライズドビューに接続してデータを可視化する
Tableau Desktopを起動し、Redshiftのiris_materialized_viewマテリアライズドビューに接続します。
iris_materialized_viewが読み取れていることが分かります。
ちなみに、SUPER型は直接は読み取れませんでした。試しにirisテーブルに接続してみると、「受け取った型には対応していない」旨のエラーが表示されます。
iris_materialized_viewからデータを読み取って、散布図を書いてみました。
最後に
SUPER型のカラムにJSONを格納し、マテリアライズドビューを通してTableauから参照する例を紹介しました。今回は有名なデータを題材にしましたが、初めて見るデータでも探索的にクエリを実行して肌感を掴み、さらにTableauで可視化できるので、とても強力です。
3
