Re:dash のPostgreSQL へ CSVファイルを Digdag * Embulk 経由で登録する

# Re:dash のPostgreSQL へ CSVファイルを Digdag * Embulk 経由で登録する
## 0.はじめに
こちらは[re:dash Advent Calendar 2016](http://qiita.com/advent-calendar/2016/redash)の22日目の記事です。

Advent Calendarについて出遅れたと思っていたところ、、、
こちらはまだ空き枠がありましたので参加しました。

Mac上で以下のような感じで
CSVファイルを digdag -> embulk ->　(Re:dashの)PostgreSQLへ格納してRe：dashから見るところまでを記事にします。
デモや素振り用などの参考にして頂ければと思います。

<img width="548" alt="image_01.png" src="https://qiita-image-store.s3.amazonaws.com/0/117986/42393438-277c-6f08-a49b-a857d6866dcb.png">

以下は今回本記事では取り扱いませんが以前書きました[こちらの記事](http://qiita.com/tbuchi888/items/fa02a10728999520e1a0)や、[こちらの記事](http://qiita.com/tbuchi888/items/66763a9af2e15fa74a3c)を参考に応用するとChatOps的な感じでデータ登録が可能です。

<img width="727" alt="image_02.png" src="https://qiita-image-store.s3.amazonaws.com/0/117986/7b8843e0-dbed-42b8-825c-c147566800eb.png">

## 1.環境
- OSX Yosemite
- Docker version 1.13.0-rc3, build 4d92237
- docker-compose version 1.9.0, build 2585387

以降は今回インストールするもの（2016/12/21時点）

- java version "1.8.0_111"
- Digdag 0.9.1
- embulk 0.8.15
  - embulk-output-postgresql (0.7.2, 0.6.3)
- Re:dash 0.11.1+b2095
  - nginx/1.9.10
  - Redis server v=2.8.23
  - postgres (PostgreSQL) 9.3.14
                   
## 2.各種インストール
### 2.1.Java8のインストール
DigdagとEmbulkはJava のインストールが必要です。
macの場合は[こちら](https://www.java.com/en/download/mac_download.jsp)からダウンロードしてインストールをしてください。

### 2.2.Didagのインストール
http://docs.digdag.io/getting_started.html#downloading-the-latest-version
より引用

> # 1. Downloading the latest version
> Digdag is a single executable file. You can install the file to ~/bin using curl command as following:
> $ curl -o ~/bin/digdag --create-dirs -L "https://dl.digdag.io/digdag-latest"
> $ chmod +x ~/bin/digdag
> $ echo 'export PATH="$HOME/bin:$PATH"' >> ~/.bashrc
> Please reopen your terminal window or type following command so that the change of PATH takes effect.
> $ source ~/.bashrc

なお、macなので`.bashrc`から`.bash_profile`へ変更

``` digdag
curl -o ~/bin/digdag --create-dirs -L "https://dl.digdag.io/digdag-latest"
chmod +x ~/bin/digdag
echo 'export PATH="$HOME/bin:$PATH"' >> ~/.bash_profile
source ~/.bash_profile
```

### 2.3.Embulkのインストール
https://github.com/embulk/embulk#linux--mac--bsd
より引用

> curl --create-dirs -o ~/.embulk/bin/embulk -L "http://dl.embulk.org/embulk-latest.jar"
> chmod +x ~/.embulk/bin/embulk
> echo 'export PATH="$HOME/.embulk/bin:$PATH"' >> ~/.bashrc
> source ~/.bashrc

Digdagで既に`~/bin/`へパスを通しているので
Digdagに合わせて格納先を~/bin/配下にコピーするように変更

```　embulk
curl --create-dirs -o ~/bin/embulk -L "http://dl.embulk.org/embulk-latest.jar"
chmod +x ~/bin/embulk
# digdag でパスを通しているので以下２行は不要
#echo 'export PATH="$HOME/bin:$PATH"' >> ~/.bash_profile
#source ~/.bash_profile

# 今回利用するPluginをインストール
embulk gem install embulk-output-postgresql
```

### 2.4.Re:dashのインストール
http://docs.redash.io/en/latest/setup.html#docker-compose
より引用

> **Docker Compose**
> 1.Make sure you have a Docker machine up and running.
> 2.Make sure your current working directory is the root of this GitHub repository.
> 3.Run docker-compose up postgres.
> 4.Run ./setup/docker/create_database.sh. This will access the postgres container and set up the database.
> 5.Run docker-compose up
> 6.Run docker-machine ls, take note of the ip for the Docker machine you are using, and open the web browser.
> 7.Visit that Docker machine IP at port 80, and you should see a Re:dash login screen.
>
> Now proceed to “Setup”.

Docker for mac を利用してインストールしますが、公式のままだとmac特有問題にあたりますので以下手順で行います。
詳細は以前書いた[「Re:dash」を「Docker for Mac」上で動かしてみた](http://qiita.com/tbuchi888/items/275bb2ed341c2936c9c3)を参照してください。

``` redash
# 1.git clone と　ディレクトリ移動
git clone https://github.com/getredash/redash.git
cd redash/

# 2.docker-compose-example.ymlより、docker-compose.ymlを作成
#   Dockerホスト上のPostgreSQLのデータ置き場を/opt/postgres-dataからカレントディレクトリ配下./postgres-dataへ変更（環境により適宜変更してください）
#   sedを使わずに `mv docker-compose-example.yml docker-compose.yml`して `vi docker-compose.yml`でもOK
# 　なお、macではなく、Linuxであれば`mv docker-compose-example.yml docker-compose.yml`だけで問題と思います。
cat docker-compose-example.yml | sed -e "s/\/opt\/postgres-data/\.\/postgres-data/" > docker-compose.yml
```

また今回は
**embulkから`PostgreSQL`コンテナの5432portへアクセス可能なように
`docker-compose.yml`のpostgres部分へ`ports:　　　- "5432:5432"`の設定を追記します。**
これをやっておかないと後半のdigdag/embulk部分でエラーとなります。

```　docker-compose.yml 
redash:
  image: redash/redash:latest
  ports:
    - "5000:5000"
  links:
    - redis
    - postgres
  environment:
    REDASH_LOG_LEVEL: "INFO"
    REDASH_REDIS_URL: "redis://redis:6379/0"
    REDASH_DATABASE_URL: "postgresql://postgres@postgres/postgres"
    REDASH_COOKIE_SECRET: veryverysecret
redis:
  image: redis:2.8
postgres:
  image: postgres:9.3
  ports:
    - "5432:5432"
  volumes:
    - ./postgres-data:/var/lib/postgresql/data
redash-nginx:
  image: redash/nginx:latest
  ports:
    - "80:80"
  links:
    - redash
```

```
# 3.`-d` オプション(バックグラウンド起動)をつけてPostgreSQLだけ起動
docker-compose up -d postgres

# 4.create_database.shの実行
./setup/docker/create_database.sh

# 5. 残りも`-d` オプション(バックグラウンド起動)をつけて起動
docker-compose up -d
```

## 3.各種設定
### 3.1.Digdag

以下のファイルを準備します。

``` csv2postgres.dig 
timezone: Asia/Tokyo

_export:
  workflow_name: "csv2postgres"
  start_msg:     "digdag ${workflow_name} start"
  end_msg:       "digdag ${workflow_name} finish"
  error_msg:     "digdag ${workflow_name} error"

+strat:
  echo>: ${start_msg}

# make config file for embulk
+guess_embulk:
  sh>: embulk guess ./guess.yml -o ./config.yml

+csv_to_db:
  embulk>: ./config.yml

## This is for _error confirmation. 
##+fail:
##  fail>: "fail!"

+end:
  echo>: ${end_msg}

_error:
  echo>: ${error_msg}
```

### 3.2.Embulk
元データのCSVファイル（今回は./data/org_sample.csv）とguess.ymlを準備します。
EmbulkのGuessを使って、csvファイルからPostgresqlへ入れる際のConfigファイルを自動生成します。

```　./data/org_sample.csv
yyyy/mm/dd,user,item,cost
2016/12/21,user01,AAA,2000
2016/12/22,user02,BBB,100
2016/12/23,user03,AAA,2000
2016/12/24,user01,BBB,100
```

Guess機能で自動生成させるために以下のようにInのparserの`columns：`配下を空行にします。

inのcsv-parser-plugin部分の詳細設定についてはこちらを
http://www.embulk.org/docs/built-in.html#csv-parser-plugin

なお、`path_prefix:`については、対象ファイル群の先頭の一部文字列の指定が必要です。

outのembulk-output-postgresql部分の詳細設定についてはこちらをご確認ください。
http://www.rubydoc.info/gems/embulk-output-postgresql/0.7.2

`table:` が今回作成されるテーブル名となります。
PostgreSQLのDB名等はdocker-composeでインストールした際のデフォルト値を利用しています。デフォルトから変更している場合は自環境に合わせた設定をお願いします。

``` guess.yml
in:
  type: file
  path_prefix: ./data/org_
  parser:
    charset: UTF-8
    newline: CRLF
    type: csv
    delimiter: ','
    quote: '"'
    escape: '"'
    trim_if_not_quoted: false
    skip_header_lines: 1
    allow_extra_columns: false
    allow_optional_columns: false
    columns:
out:
  type: postgresql
  default_timezone: Japan
  host: localhost
  user: postgres
  password: postgres
  database: postgres
  table: sampledata
  options: {loglevel: 2}
  mode: insert_direct
```

### 3.3.Re:dashのDataSource設定
Re:dashと一緒にインストールしたPostgreSQLをそのまま利用するためにDATASOURCESを以下にように設定します。
なお、PostgreSQLのDB名等はdocker-composeでインストールした際のデフォルト値を利用しています。

ブラウザで http://127.0.0.1/ ログイン画面へアクセスし
admin/admin（初期値）でログインします。
<img width="853" alt="redash01.png" src="https://qiita-image-store.s3.amazonaws.com/0/117986/2245fbd2-2fb1-bb83-60a2-0ecbd5b19878.png">

ログイン後に右上のDBアイコンをクリックして
<img width="888" alt="redash02.png" src="https://qiita-image-store.s3.amazonaws.com/0/117986/83b6e6e9-c416-caeb-6392-3b203225887f.png">

`＋New Data Source`を選択します。
<img width="888" alt="redash03.png" src="https://qiita-image-store.s3.amazonaws.com/0/117986/a6ed712f-45ce-139d-999f-68434bb8d15e.png">

Nameに適当な名前（今回はtest）を入れて、`Type`として`PostgreSQL`を選択します。
docker-composeでredashをインストールしている前提で
`Database Name`,`host`,`password`,`user`に全て`postgres`を設定して`Save`します。
デフォルトから変更している場合は自環境に合わせた設定をお願いします。

<img width="888" alt="redash04.png" src="https://qiita-image-store.s3.amazonaws.com/0/117986/fd83fff3-abe1-ee99-1088-30153598c03c.png">

上段の`Queries`から`New　Query`を選択
<img width="888" alt="redash05.png" src="https://qiita-image-store.s3.amazonaws.com/0/117986/abbe8d5a-0068-eaad-2591-3412e4d502f9.png">

`Data Source`のプルダウンから先ほど登録した`test`が選択できればOKです。
<img width="888" alt="redash06.png" src="https://qiita-image-store.s3.amazonaws.com/0/117986/daab8279-d9d1-7cf1-1da0-43681158b2a1.png">
<img width="888" alt="redash07.png" src="https://qiita-image-store.s3.amazonaws.com/0/117986/aae75076-e669-a16a-592a-73f98784a283.png">

## 4.実行

以下のようにファイルを配置し

```
├── csv2postgres.dig
├── data
│   └── org_sample.csv
└── guess.yml
```

terminalからDigdagを実行してCSVデータをPostgreSQL上テーブルへ格納します。
なお、下の例では、繰り返し確認するために`-a`オプションをつけています。

```
digdag run csv2postgres.dig -a
```

実行結果として最後に
`Success. Task state is saved at /Users/*********`
と出れば成功です。

なお、PostgreSQLへのアクセスでエラーとなる場合以下をご確認ください。
-　postgresqlのid,passwaordなどをデフォルトから変更していないか？
- 上述の`2.4.Re:dashのインストール`で`docker−compose.yml`についてpostgresqlコンテナへの`５４３２port`のバインドを忘れていないか？

参考：以下はembulkのGuess機能で自動生成したEmbulkの`config.yml`
以下のように`columns:`配下についてイイ感じに自動性生成してくれます。

``` config.yml
in:
  type: file
  path_prefix: ./data/org_
  parser:
    charset: UTF-8
    newline: CRLF
    type: csv
    delimiter: ','
    quote: '"'
    escape: '"'
    trim_if_not_quoted: false
    skip_header_lines: 1
    allow_extra_columns: false
    allow_optional_columns: false
    columns:
    - {name: yyyy/mm/dd, type: timestamp, format: '%Y/%m/%d'}
    - {name: user, type: string}
    - {name: item, type: string}
    - {name: cost, type: long}
out:
  type: postgresql
  default_timezone: Japan
  host: localhost
  user: postgres
  password: postgres
  database: postgres
  table: sampledata
  options: {loglevel: 2}
  mode: insert_direct
```

## 5.確認
ブラウザよりRe：dashの　New　Querryに戻り、追加したテーブルが利用できることを確認します。

Datastore`test`を選択して
例えば`select * from sampledata;`のようなSQLを入れて
`Excute`ボタンを押下します。

<img width="1161" alt="redash_query01.png" src="https://qiita-image-store.s3.amazonaws.com/0/117986/e5d35e02-0dba-ac01-f230-4ce601740da5.png">

以上です。