以前の記事で、Dataiku DSSとpostgreSQLの接続初期設定の記事を書きましたが、今回はもう少し使いこなした系のチュートリアルをやってみました。
以前の記事で設定したconnectionはすでにあるものとして書きます。
今回実施したチュートリアルはこちらのDataiku DSS & SQLです。
Dataiku DSSはver 8.0.2を使いました。
プロジェクトの作成～データの確認
まずチュートリアル用のプロジェクトを作ります。
[+New Project] > [DSS Tutorials] > [Code] > [SQL in Dataiku DSS (Tutorial)]
プロジェクトができたら[GO TO FLOW]をクリック。
既に二つのデータセットがアップロードされています。
左側のordersデータセットは、t-shirtの注文ログデータです。
右側のcustomers_stackedはは、以下のようなデータで、顧客情報のデータのようです。
最右カラムのoriginal_datasetをAnalyzeで確認すると、labeledとunlabeledの2種類のカテゴリがあることがわかります。ラベルつきのデータとラベルなしのデータをスタックしたデータとなっているようです。
データベースへのデータ格納
Syncレシピを使ったデータベースへのデータ格納
ordersデータセットをpostgreSQLへインポートするためのSyncレシピを使ってみます。
Dataiku DSSはデフォルトでcsv読み込み時にすべてのカラムをstring型と認識します。
データベースに入れる段階で、適切な型を設定しておきます。Explore画面のグレーの型の文字クリックで変更可能です。pages_visitedとtshirt_qualityをintに、tshirt_priceをdoubleに設定します。
念のため、設定した型が適切かチェックします。
[Settings]>[Schema]>[CHECK NOW]をクリック。
以下のようにSchema and data are consisttent.と表示されればOKです。
[INFER TYPES FROM DATA]>[SAVE]をクリックして、変更を保存します。
次に、[ACTION]>[Sync]をクリックし、Syncレシピを起動します。
Store infoにPostgreSQL_tshirtを選択し、[CREATE_RECIPE]をクリック。
さらに[RUN]をクリック。Job succeededとでれば成功です。
フローに戻って確認してみると、ordersデータセットからSyncレシピを用いてpostgreSQL内のデータとしてorders_copyが作成されているのがわかります。ちなみに、SCHEMAは先ほど変更した通りになっています。
Prepare Recipe経由でのデータベースへの格納
データ加工を行いたい場合は、Syncレシピを使わずにPreapareレシピを用いることで、データ加工後のデータをデータベースに格納することも可能です。
customers_stackedデータセットにprepareレシピを適用して、customers_stacked_preparedを作成します。ここでも、Store intoにはPostgreSQL_tshirtを指定します。
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ができました。
SQL Queryレシピを使ったデータセットの作成
データベース内のテーブルに対応するデータセットができたので、in-databaseでの処理というやつをやってみます。つまり、データベース側の機能を使ってデータ加工をします。
orders_copyデータセットを選択した状態で、SQLレシピをクリックします。
SQL queryをクリックします。
[SET]をクリックし、出力データセット名としてorders_by_customerを入力します。
[CREATE DATASET]クリック後に、[CREATE RECIPE]をクリックします。
デフォルトではこのようにSQL文がセットされた状態になっています。
ここで、注文を顧客ごとにグルーピングし、過去の注文履歴を集計してみます。これは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]クリックで構文チェックしておきます。
問題なければ[RUN]をクリック。
フローで確認すると、orders_by_customerデータセットができています。
中を見てみると、ちゃんとcustomer_idごとにデータが集計されていることがわかります。
ビジュアルレシピを使用したデータベースの操作
ビジュアルレシピを使ったデータベースの操作を行ってみます。ここでは、テーブルに紐づけられたデータセットを結合して、また別のテーブルに紐づけたデータセットに格納するといった操作を行います。
customers_stacked_preparedを選択して、[Join with...]をクリック。
二番目のデータせセットにorders_by_cusomerを設定し、出力データセットにcustomers_enrichedを設定し、[CREATE RECIPE]をクリック。
過去に注文したことのある顧客だけに絞りたいので、JOIN TYPEをINNER JOINに変更します。ちなみにJOIN TYPEは[Join]で変更可能です。
[Selected columns]で、出力カラムを調整することができます。customer_IDとcutomer_idは重複するカラムのため、orders_by_customerデータセットのcustomer_idを削除します。
最後に[Output]>[VIEW QUERY]でSQL文を確認します。
特に問題ないのでこのまま[RUN]をクリックします。
フロー上から、二つのpostgreSQLデータセットがJOINされてひとつのデータセットが作成されていることがわかります。SQL文をうたなくても、テーブルの結合を行うことができました。
#データベースを利用した可視化
データベースを利用することでチャートの描画を速くすることができるようです。これは実データ扱うときには便利そう。
さきほど作ったcustomers_enrichedデータセットでチャートを描いてみます。
X軸にpages_visited_arg、Y軸にCount of recordsを設定します。Sampling&EngineのExecution engineをIn-databaseにして[SAVE]をクリック。
ヒストグラムの形状はあまり変わっていませんが、右側のrecords数が変化していますね。In-databaseの場合、全データで描画していることがわかります。今回はレコード数がすくないのであまりありがたみがないのですが、どのくらい速いのかについては、どこかで試してみたいところです。
SQL notebook
pythonやRのように、SQL notebookが用意されています。
[Lab]>[New Code Notebook]> [SQL]>[CREATE]でSQL notebookが作成できます。
左側の[ALL TABLES]クリックで、アクセス可能なテーブルのリストが表示されます。
[+query]でSQL文を入力できるようになります。左側のテーブル一覧あたりを押すことで自動的にSQL文が挿入されるのがちょっと面白いです。
左側の赤で囲ってあるところを押すと、右側にSQL文が自動で入力されています。
[RUN]ボタンをクリックすると、下側にSQL文の結果のサンプルが表示されます。
[+QUERY]ボタンでさらに構文を追加するといった使い方ができるようです。
SQL文をいろいろ書いてみて、結果を確認したいといった場合に使いやすそうです。
まとめ
今回は以下についてチュートリアルにのっとってやってみました。実務で使いどころ多そうな印象です。他にもいろいろなデータストレージに対応しているのでまた試してみたいです。
- Dataiku DSSからのpostgreSQLデータベースへのデータ格納
- Dataiku DSS上でのpostgreSQLデータベースの操作
- Dataiku DSS上でのデータベースを利用した可視化
- SQL notebook
以上