データ準備機能詳解(SQLクエリの利用) #quicksight #16 | Amazon QuickSight Advent Calendar 2016

Amazon QuickSight Advent Calendar 2016

#ビッグデータ

#Amazon QuickSight

#AWS

しんや

2016.12.16

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

当エントリは『Amazon QuickSight Advent Calendar 2016』の16本目のエントリです。

Amazon QuickSight (全部俺) Advent Calendar 2016 - Qiita

昨日の15本目のエントリは『テーブル結合』でした。

データ準備機能詳解(テーブル結合) #quicksight #15 | Amazon QuickSight Advent Calendar 2016 ｜ Developers.IO

『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データを使いたいと思います。

データ準備機能詳解(テーブル結合) #quicksight #15 | Amazon QuickSight Advent Calendar 2016 ｜ Developers.IO

今回用に使用するデータセットの枠を作成。

quicksight-data-preparation-sqlquery_01

スキーマ及びテーブルの指定を行い、

quicksight-data-preparation-sqlquery_02

データセット作成一歩手前までの内容は上記紹介エントリと同様です。[Edit/Preview data]を選択。

quicksight-data-preparation-sqlquery_03

[Tables]ペイン内に『Switch to Custom SQL tool』というリンクがありますのでこれをクリック。

quicksight-data-preparation-sqlquery_04

すると、カスタムSQLを設定出来る画面に遷移します。任意のSQLタイトルと、実行させたいSQL文を設定。(※現行、この画面にはSQL編集機能は存在しておらず単なるテキストエリアでしかないので、お好みのSQLエディタでSQL文を生成した後、その内容をコピペで貼り付ける方が楽です)

quicksight-data-preparation-sqlquery_05

ここでは、以下の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]を押下。

quicksight-data-preparation-sqlquery_06