#やりたいこと
Redash上でpostgreSQLを利用しているのですが、手元にあるCSVファイルをどうしてもDB上に結合させたい。
イメージとしては既に使っているDB上に新たなテーブルデータを追加したいという訳です。
結果的にはCSVファイルをDB上に直接的に格納することができずに間接的な方法でしかできなかったです。
#この記事に書いてあること
- CSVファイルをRedash上に読み込ませる方法
- そのデータを既存のDBと結合させる方法
2に関してはスマートなやり方がわからず無理矢理結合させているのであまり参考にしない方がいいと思います。
(もし他にいいやり方がわかる方がいればご教授頂きたいです..)
#CSVファイルをRedash上に読み込ませる方法
基本的には公式ドキュメントに書いてありますが、色々とつまづいた部分があったので改めて書きます。
https://redash.io/help/data-sources/querying/csv-files
まずRedashではCSVファイルをアップロードすることはできません。
代わりにクラウドなどからCSVファイルを読み込むことによってRedash上に格納することができます。
###Step1 データソースを追加する
RedashでCSVとQuery Resultsというデータソースを用意する必要があります。
データソースCSVでは1. CSVファイルをRedash上に読み込ませる方法
データソースQuery Resultsでは2. そのデータを既存のDBと結合させる方法
に使用します。
追加方法はSetting -> Data Sources -> New Data Sourcesから追加できます。
NewDataSourcesを押すとプルダウン形式で様々な項目が出ると思うので、CSVとQueryResultsそれぞれ二つ作ってください。
名前はなんでもいいはずですが、公式では一般的にCSVはCSV, QueryResultsはQR とするのが良いとされています。
※データソースは管理権限アカウントでしか設定できません。管理権限以外のアカウントだとSettingが表示されません。
###Step2 CSVファイルを読み込ませるためのURLを発行する
先述したとおりRedashでは直接的にCSVファイルをアップロードすることができず、共有URLを介してCSVファイルを読み込む形になります。
使えるプラットフォームはDropbox, GoogleDrive, OneDrive, Github となっています。
ここでは代表的にDropboxによる読み込ませ方を紹介します。他のものに関しては公式ドキュメントを参考にしてください。
(GoogleDriveだと何故か綺麗なテーブルデータとして読み込んでくれなかった..)
※1行目をヘッダーとして認識するため、CSVファイルはヘッダー込みのファイルを用意した方がいいです
###Dropbox
まずCSVファイルをDropboxにアップロードします。
次にそのファイルを共有できる設定にしてリンクをコピーします。
DropBoxのリンクの構造は以下になっていると思います。
https://www.dropbox.com/s/識別子/ファイル名.csv?dl=0
このURLを読み込ませる用のURLに変換しなくては行けないので
www.dropbox.com -> dl.dropboxusercontent.com
に変換させます。
なので最終的なURLは
https://dl.dropboxusercontent.com/s/識別子/ファイル名.csv?dl=0
になると思います。
###Step3 発行したURLをRedash上で読み込ませる
Top -> Create -> NewQuery にしてプルダウンリストからCSVを選択
そしてクエリを書く部分に先ほど作成したhttps://www.dropbox.com/s/識別子/ファイル名.csv?dl=0を貼り付けてExecute
するとテーブルデータが下に表示されると思います。
うまく表示されたらSaveを押してクエリを保存。
###Step4 QueryResultsでクエリをキャッシュする
Top -> Create -> NewQuery にしてプルダウンリストからQueryResults(QR)を選択
クエリを書く部分に
SELECT * FROM query_クエリID
と入力して実行します。
クエリIDの部分には、Step3で保存したクエリにURLの部分の数字列がクエリIDに当たります。
ex) https://app.redash.io/~~~~~/queries/791429←ここ
実行した段階でクエリがキャッシュされるはずなので、次に
SELECT * FROM cached_query_クエリID
として実行します。先ほどと同様の結果が得られれば成功です。
これでCSVファイルがRedash上に保存された状態になると思います。
#2. そのデータを既存のDBと結合させる方法
こちらに関して全くスマートではないですが一応書いておきます。
###Step1 既存のDBの結合させたいデータをCSVファイルにする
プルダウンリストを既存のDBに戻して、結合させたいテーブルデータを
SELECT * FROM 結合させたいデータ
として出力して右下ら辺のボタンからCSVファイルとして書き出せるのでそこでエクスポートします。
###Step2 エクスポートしたCSVファイルを再度1の手順を行う
CSVファイルを1の手順を再度行なってキャッシュさせます。
既存DBデータをキャッシュさせたら先ほどの結合させたかったCSVファイルとJOINさせます
SELECT *
FROM cached_query_222 (←既存DBのデータ)
JOIN cached_query_111 (←元々結合させたかったCSVファイル)
ON ~
これで既存DBとの結合ができると思います。
かなり強引な方法だと思うのでもう少し便利なやり方があると嬉しい..