Dataiku DSSでpostgreSQLを使う

[以前の記事](https://qiita.com/ayoyo/items/e304ea8d7c3454001e23)で、Dataiku DSSとpostgreSQLの接続初期設定の記事を書きましたが、今回はもう少し使いこなした系のチュートリアルをやってみました。
以前の記事で設定したconnectionはすでにあるものとして書きます。

今回実施したチュートリアルは[こちらのDataiku DSS & SQL](https://academy.dataiku.com/dss-sql)です。

Dataiku DSSはver 8.0.2を使いました。



# プロジェクトの作成～データの確認
まずチュートリアル用のプロジェクトを作ります。
[+New Project] > [DSS Tutorials] > [Code] > [SQL in Dataiku DSS (Tutorial)]

プロジェクトができたら[GO TO FLOW]をクリック。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/106215/19f64a38-4bf9-aafa-5300-21ac5f3fb196.png)



既に二つのデータセットがアップロードされています。
左側のordersデータセットは、t-shirtの注文ログデータです。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/106215/285bd3e0-ae65-eea4-d7ee-944f89f83a78.png)

右側のcustomers_stackedはは、以下のようなデータで、顧客情報のデータのようです。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/106215/7f269907-a8ab-59dc-7cd1-5a9fe989a41d.png)

最右カラムのoriginal_datasetをAnalyzeで確認すると、labeledとunlabeledの2種類のカテゴリがあることがわかります。ラベルつきのデータとラベルなしのデータをスタックしたデータとなっているようです。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/106215/11bf319b-fbd2-eedb-fc82-d47020948f91.png)

# データベースへのデータ格納
## Syncレシピを使ったデータベースへのデータ格納
ordersデータセットをpostgreSQLへインポートするためのSyncレシピを使ってみます。
Dataiku DSSはデフォルトでcsv読み込み時にすべてのカラムをstring型と認識します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/106215/1cc54eea-f171-1362-012d-14aea251b58a.png)

データベースに入れる段階で、適切な型を設定しておきます。Explore画面のグレーの型の文字クリックで変更可能です。pages_visitedとtshirt_qualityをintに、tshirt_priceをdoubleに設定します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/106215/bf208d10-60e1-d749-0b55-fde31b996b84.png)

念のため、設定した型が適切かチェックします。
[Settings]>[Schema]>[CHECK NOW]をクリック。

以下のようにSchema and data are consisttent.と表示されればOKです。
[INFER TYPES FROM DATA]>[SAVE]をクリックして、変更を保存します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/106215/f20bd8f2-6f76-ba26-c3d5-d82360fbc973.png)

次に、[ACTION]>[Sync]をクリックし、Syncレシピを起動します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/106215/df0fd772-0a37-2264-e4b6-9f5d1274ad39.png)


Store infoにPostgreSQL_tshirtを選択し、[CREATE_RECIPE]をクリック。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/106215/d6735900-11ce-bcc6-4e0d-427e907ae375.png)

さらに[RUN]をクリック。Job succeededとでれば成功です。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/106215/f0e09254-c886-3e21-f93d-534f170b4b0c.png)

フローに戻って確認してみると、ordersデータセットからSyncレシピを用いてpostgreSQL内のデータとしてorders_copyが作成されているのがわかります。ちなみに、SCHEMAは先ほど変更した通りになっています。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/106215/4180bcff-385a-db7b-447c-d70b4c592525.png)

## Prepare Recipe経由でのデータベースへの格納
データ加工を行いたい場合は、Syncレシピを使わずにPreapareレシピを用いることで、データ加工後のデータをデータベースに格納することも可能です。

customers_stackedデータセットにprepareレシピを適用して、customers_stacked_preparedを作成します。ここでも、Store intoにはPostgreSQL_tshirtを指定します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/106215/95dfb276-9012-2df1-e204-be60ed77e7aa.png)

prepareレシピで以下の3処理を行い、[RUN]をクリック。

 - birthdateカラムをdate parseします。Date formatはyyyy/MM/ddを選択します。また、birthdate_parsedカラムのstorage型がdateになっていますがstringにしておきます。dateのままだと、この後エラーが出ました。
 - user_agentカラムに対してClassifyを実行し、user_agent_brandとuser_agent_os_columnsのみ残します。
 - ip_addressカラムに対してResolve GeoIPを実施し、ip_address_countryとip_address_geopointのみ残します。
 
以上の操作で、データ加工済みのデータをpostgreSQLへ格納したcustomers_stacked_preparedができました。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/106215/874b4efb-6f58-928e-bfbd-401ae57fcfdf.png)

## SQL Queryレシピを使ったデータセットの作成
データベース内のテーブルに対応するデータセットができたので、in-databaseでの処理というやつをやってみます。つまり、データベース側の機能を使ってデータ加工をします。

orders_copyデータセットを選択した状態で、SQLレシピをクリックします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/106215/683bab14-d445-7678-ba24-48499a097795.png)

SQL queryをクリックします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/106215/20147f18-659b-0be9-da9f-0a4b5f7c43d1.png)

[SET]をクリックし、出力データセット名としてorders_by_customerを入力します。
[CREATE DATASET]クリック後に、[CREATE RECIPE]をクリックします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/106215/ba3ef6e4-4b0d-8858-f0d8-4d2b9e9215ef.png)


デフォルトではこのようにSQL文がセットされた状態になっています。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/106215/b82f5cff-b48f-babe-7f6e-f89c6a1a7134.png)

ここで、注文を顧客ごとにグルーピングし、過去の注文履歴を集計してみます。これはGroupレシピでも実行可能ですが、SQL文でも簡単に実装することができます。

入力されているSQL文を以下のように書き換えます。

```
SELECT customer_id,
       AVG(pages_visited) AS pages_visited_avg,
       SUM(tshirt_price*tshirt_quantity) AS total
    FROM "dku_tshirt"."DKU_TUTORIAL_SQL_orders_copy"
    GROUP BY customer_id;
```

[VALIDATE]クリックで構文チェックしておきます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/106215/41d76126-ba91-cba2-15e3-305f2b3647b6.png)

問題なければ[RUN]をクリック。
フローで確認すると、orders_by_customerデータセットができています。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/106215/2df4c3b9-4ee2-8300-7005-c9ad5c6288e6.png)

中を見てみると、ちゃんとcustomer_idごとにデータが集計されていることがわかります。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/106215/b7a759c4-2d39-26bf-c1cc-58f6577ae355.png)

# ビジュアルレシピを使用したデータベースの操作
ビジュアルレシピを使ったデータベースの操作を行ってみます。ここでは、テーブルに紐づけられたデータセットを結合して、また別のテーブルに紐づけたデータセットに格納するといった操作を行います。

customers_stacked_preparedを選択して、[Join with...]をクリック。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/106215/1a9cdfa8-b8a4-270a-9831-7d98883b3e46.png)


二番目のデータせセットにorders_by_cusomerを設定し、出力データセットにcustomers_enrichedを設定し、[CREATE RECIPE]をクリック。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/106215/b0fa5a1f-a1a4-d6f7-6915-455e41098800.png)

過去に注文したことのある顧客だけに絞りたいので、JOIN TYPEをINNER JOINに変更します。ちなみにJOIN TYPEは[Join]で変更可能です。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/106215/d834b9cf-b9fe-c2bb-6433-4a62006c09f0.png)

[Selected columns]で、出力カラムを調整することができます。customer_IDとcutomer_idは重複するカラムのため、orders_by_customerデータセットのcustomer_idを削除します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/106215/d16eae83-c0c4-d1a3-e725-8562d6c74ce0.png)

最後に[Output]>[VIEW QUERY]でSQL文を確認します。
特に問題ないのでこのまま[RUN]をクリックします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/106215/186da7d3-6c44-9529-a5d1-2404ac27c15f.png)

フロー上から、二つのpostgreSQLデータセットがJOINされてひとつのデータセットが作成されていることがわかります。SQL文をうたなくても、テーブルの結合を行うことができました。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/106215/c15ccaa7-f13e-bf5c-76dc-f9c1c78ba4cb.png)


#データベースを利用した可視化
データベースを利用することでチャートの描画を速くすることができるようです。これは実データ扱うときには便利そう。

さきほど作ったcustomers_enrichedデータセットでチャートを描いてみます。
X軸にpages_visited_arg、Y軸にCount of recordsを設定します。Sampling&EngineのExecution engineをIn-databaseにして[SAVE]をクリック。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/106215/87e3efb7-6158-4e8d-2e35-fe4e4664cdc0.png)

ヒストグラムの形状はあまり変わっていませんが、右側のrecords数が変化していますね。In-databaseの場合、全データで描画していることがわかります。今回はレコード数がすくないのであまりありがたみがないのですが、どのくらい速いのかについては、どこかで試してみたいところです。

# SQL notebook
pythonやRのように、SQL notebookが用意されています。
[Lab]>[New Code Notebook]> [SQL]>[CREATE]でSQL notebookが作成できます。

左側の[ALL TABLES]クリックで、アクセス可能なテーブルのリストが表示されます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/106215/1acbe66e-8ea4-5994-67bc-76a05052285a.png)

[+query]でSQL文を入力できるようになります。左側のテーブル一覧あたりを押すことで自動的にSQL文が挿入されるのがちょっと面白いです。
左側の赤で囲ってあるところを押すと、右側にSQL文が自動で入力されています。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/106215/ad1d77c1-fd69-7cef-e627-c4bbdffccd98.png)

[RUN]ボタンをクリックすると、下側にSQL文の結果のサンプルが表示されます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/106215/3e003776-67ef-1652-c9de-2137b4969ea3.png)

[+QUERY]ボタンでさらに構文を追加するといった使い方ができるようです。
SQL文をいろいろ書いてみて、結果を確認したいといった場合に使いやすそうです。

# まとめ

今回は以下についてチュートリアルにのっとってやってみました。実務で使いどころ多そうな印象です。他にもいろいろなデータストレージに対応しているのでまた試してみたいです。

 - Dataiku DSSからのpostgreSQLデータベースへのデータ格納
 - Dataiku DSS上でのpostgreSQLデータベースの操作
 - Dataiku DSS上でのデータベースを利用した可視化
 - SQL notebook

以上