データ準備機能詳解(SQLクエリの利用) #quicksight #16 | Amazon QuickSight Advent Calendar 2016
当エントリは『Amazon QuickSight Advent Calendar 2016』の16本目のエントリです。
昨日の15本目のエントリは『テーブル結合』でした。
『AWS re:Invent 2016』の直前に一般利用可能となったAWSによるBIサービス、Amazon QuickSight。発表されたばかりですが、早速Amazon QuickSightを使い倒すべく色々な切り口でその内容について見て行きたいと思います。
15本目となる当エントリでは、Amazon QuickSightのデータ準備機能の1つ、『SQLクエリ』の内容について見て行きたいと思います。
新しいデータベースデータセットを作成する際、既存のSQLクエリを選択するか、新しいSQLクエリを作成してデータベースから取得したデータを絞り込むか、また複数のテーブルデータを結合する事が出来ます。SQLクエリを使用する事で、データセットを絞り込むために任意の(結合)条件をSQL文に含める事が出来ます。
結合の種類と結合に使用する項目を指定して複数テーブルを結合する『だけ』で良いという場合は"テーブル結合"の機能を使う事が出来ます。詳細については以下をご参照ください。
なお、当該機能では対象となるSQLデータベースのデータソースに基づいてデータセットのSQLクエリを指定する事だけが可能となっています。
テーブルを選択して項目に変更を加えていた場合(例：フィールド名の変更や計算フィールドの追加)、これらの変更は『テーブル指定』から『カスタムSQLの使用』に切り替えたタイミングで破棄されます。
カスタムSQLクエリの作成
当エントリで紹介する機能を確認するために、下記エントリ執筆時に用意したAmazon RedshiftのMovielensデータを使いたいと思います。
今回用に使用するデータセットの枠を作成。
スキーマ及びテーブルの指定を行い、
データセット作成一歩手前までの内容は上記紹介エントリと同様です。[Edit/Preview data]を選択。
[Tables]ペイン内に『Switch to Custom SQL tool』というリンクがありますのでこれをクリック。
すると、カスタムSQLを設定出来る画面に遷移します。任意のSQLタイトルと、実行させたいSQL文を設定。(※現行、この画面にはSQL編集機能は存在しておらず単なるテキストエリアでしかないので、お好みのSQLエディタでSQL文を生成した後、その内容をコピペで貼り付ける方が楽です)
ここでは、以下のSQLクエリを実行させてみる事にしました。ratingsに対して、そのレーティング対象となっている作品タイトルをmoviesテーブルから持って来る、という内容です。そして作品(シリーズ)を、今月公開を控えている大作スピンオフと、個人的な『心の映画2015年第1位』及び『心の映画2016年(暫定)第1位』のタイトルで絞り込んでいます。この条件の場合、合計1233件が結果として表示される事を確認しています。
SELECT
movielens.ratings.user_id,
movielens.ratings.movie_id,
movielens.ratings.rating,
movielens.ratings.timestamp,
movielens.movies.title
FROM
movielens.ratings
INNER JOIN movielens.movies ON (movielens.ratings.movie_id = movielens.movies.movie_id)
WHERE
(movielens.movies.title like'%Star Wars%')
OR (movielens.movies.title like'%Mad Max%')
OR (movielens.movies.title like'%Godzilla%')
ORDER BY
movielens.ratings.timestamp,
movielens.ratings.user_id,
movielens.ratings.movie_id,
movielens.ratings.rating;
user_id | movie_id | rating | timestamp | title
---------+----------+--------+---------------------+------------------------------------------------------------------------------------------
224 | 260 | 4 | 1996-03-30 19:26:52 | Star Wars: Episode IV - A New Hope (1977)
536 | 260 | 5 | 1996-04-14 08:48:40 | Star Wars: Episode IV - A New Hope (1977)
464 | 260 | 5 | 1996-04-19 11:15:58 | Star Wars: Episode IV - A New Hope (1977)
110 | 260 | 4 | 1996-08-15 09:22:56 | Star Wars: Episode IV - A New Hope (1977)
602 | 260 | 4 | 1996-09-10 12:41:38 | Star Wars: Episode IV - A New Hope (1977)
254 | 260 | 4 | 1996-10-12 22:03:48 | Star Wars: Episode IV - A New Hope (1977)
36 | 260 | 5 | 1996-11-03 21:46:04 | Star Wars: Episode IV - A New Hope (1977)
86 | 260 | 5 | 1996-11-16 16:33:02 | Star Wars: Episode IV - A New Hope (1977)
168 | 260 | 5 | 1996-11-24 23:48:31 | Star Wars: Episode IV - A New Hope (1977)
168 | 1196 | 4 | 1996-11-24 23:57:26 | Star Wars: Episode V - The Empire Strikes Back (1980)
168 | 1210 | 4 | 1996-11-24 23:58:20 | Star Wars: Episode VI - Return of the Jedi (1983)
344 | 260 | 5 | 1996-12-16 08:31:18 | Star Wars: Episode IV - A New Hope (1977)
344 | 1196 | 5 | 1996-12-16 08:31:45 | Star Wars: Episode V - The Empire Strikes Back (1980)
344 | 1210 | 5 | 1996-12-16 08:34:06 | Star Wars: Episode VI - Return of the Jedi (1983)
7 | 1196 | 5 | 1996-12-29 14:17:14 | Star Wars: Episode V - The Empire Strikes Back (1980)
:
570 | 122886 | 2 | 2016-10-06 19:48:11 | Star Wars: Episode VII - The Force Awakens (2015)
570 | 1882 | 1.5 | 2016-10-06 19:55:41 | Godzilla (1998)
570 | 5378 | 2 | 2016-10-07 02:12:58 | Star Wars: Episode II - Attack of the Clones (2002)
251 | 1210 | 4 | 2016-10-14 20:46:29 | Star Wars: Episode VI - Return of the Jedi (1983)
251 | 260 | 4.5 | 2016-10-14 20:46:45 | Star Wars: Episode IV - A New Hope (1977)
251 | 1196 | 4.5 | 2016-10-14 20:46:47 | Star Wars: Episode V - The Empire Strikes Back (1980)
251 | 122886 | 3 | 2016-10-14 20:47:45 | Star Wars: Episode VII - The Force Awakens (2015)
251 | 122882 | 3.5 | 2016-10-14 20:51:34 | Mad Max: Fury Road (2015)
(1233 rows)
カスタムSQL編集画面で[Fisish]を押下するとクエリが実行され、データプレビュー画面に戻ってきます。内容を確認し、[Save & Visualize]を押下。
データのSPICEへのインポートが終了。ちゃんと件数も合致してます。
絞り込んだデータを使い、以下の様な形でVisualを作成出来ました。
既存クエリの変更
作成したカスタムSQLを変更する場合は、データ準備の画面、[Fields]ペインにある[Edit SQL]を選択すると、
以下の様にカスタムSQL編集画面に遷移し、SQLの変更を行う事が出来ます。
テーブル利用に戻す
ドキュメントには記載があったのですが、画面UIから辿る術が見つかりませんでした。作る順序とかの問題だったのかな？
まとめ
という訳で『Amazon QuickSight Advent Calendar 2016』16本目、データ準備機能『SQLクエリ』に関するご紹介でした。明日もお楽しみに！
![[レポート]AWSでデータメッシュアーキテクチャを構築する#ANT336 #reinvent](https://devio2023-media.developers.io/wp-content/uploads/2022/11/eyecatch_reinvent2022_session-report.png)
