Glue DataBrew で、一度出力した S3 に、再度データの追加をやってみる

# はじめに

Glue DataBrew をつかってデータ加工を行い、Athena でクエリー出来る環境を作成できます。

初期構築時に入れたデータのクエリーを行えますが、日々データは生まれていきます。どのように DataBrew を動かして Athena としてデータを追加できるか不明だったので、検証した手順を載せておきます。



# 用意するデータ

以下の 3 種類の csv データを準備します。



sales-01.csv

- 1月~3月のデータ

```
date,shiten_code,revenue
20100101,0001,100000
20100101,0002,200000
20100101,0003,300000
20100101,0004,400000
20100101,0005,500000
20100201,0001,100000
20100201,0002,200000
20100201,0003,300000
20100201,0004,400000
20100201,0005,500000
20100301,0001,100000
20100301,0002,200000
20100301,0003,300000
20100301,0004,400000
20100301,0005,500000
```



sales-02.csv

- 4月~5月のデータ

```
date,shiten_code,revenue
20100401,0001,1000
20100401,0002,2000
20100401,0003,3000
20100401,0004,4000
20100401,0005,5000
20100501,0001,1000
20100501,0002,2000
20100501,0003,3000
20100501,0004,4000
20100501,0005,5000
```



shiten-master.csv

```
shiten_code,name
0001,東京支店
0002,大阪支店
0003,名古屋支店
0004,福岡支店
0005,札幌支店
```



# S3 にデータ用意

S3 バケットに、2つのファイルを格納します。sales-02.csv は後で追加していきます。

![image-20220723134059100.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/1f7481dc-4b28-f14d-58c0-0e679dc41cfd.png)



# Glue DataBrew でデータセットの作成

Connect new dataset を押します

![image-20220723134617153.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/90ce6f31-a5a8-80ac-baf1-ea836dacbc2a.png)



`sales-01.csv` を選択します

![image-20220723134917756.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/eba0e61b-5fa8-cb51-389a-83ec4f0db598.png)



同様に shiten-master も追加しました

![image-20220723135049947.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/e6857b11-6723-bed7-4f64-391c9bce970a.png)



# Glue DataBrew Project 作成

Create project を押します

![image-20220723135128912.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/3a2d738f-21c9-bf97-e567-aa7a29499ae4.png)



`adddata-sales-01` のデータセットを選択します

![image-20220723135945107.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/581be918-743b-712e-7052-94149e5d2759.png)



Create Project

![image-20220723135312143.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/29bc9162-891e-9ade-5f9c-390b3088f168.png)



Project の初期設定で頑張っている様子が見えます

![image-20220723135507228.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/de13fde4-adcf-8e65-d4f4-126bde8d8c81.png)



表示されました。

![image-20220723140704795.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/4207f029-8eb5-59e9-c307-441c90d6b094.png)



# データ加工の Recipe を作成

## 支店コードで結合

JOIN を押します

![image-20220723141133642.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/2a18e850-e568-6272-2fe4-b1c83d20546a.png)



`adddata-shiten-master` を選択して Next を押します

![image-20220723141223125.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/2c70ea81-1286-9f2b-0d81-c6456619fd63.png)



2 つのデータセットを `shiten_code` で結合する設定を入れて、Table B 側の shiten_code は不要なので除外します。そして、Joined table prebiew を押します

![image-20220723141401295.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/adb48fe4-8eed-f9f9-4930-ed4c7114029a.png)



結合されました



![image-20220723141432579.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/01b1f9e5-d3f0-54f5-1434-e9c4e36d0493.png)





## 日付を Date 型に変更

date 列が int になっているので、アイコンをクリック

![image-20220723141739451.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/be8b9d6f-8404-9d8d-8b94-be274d641222.png)



date を選択

![image-20220723141802175.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/369127b4-0856-135a-8d42-0574b64b1bc8.png)



Apply を押す

![image-20220723141835008.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/2eb76d7d-0419-821d-81a5-bd178b089567.png)



# Recipe の Publish

Publish を押します

![image-20220723142248270.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/258725ed-bfb8-f6be-18a3-e6b87956dacd.png)



Publish

![image-20220723142322742.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/6bcc8ce3-5d88-3c7e-76bd-aaa9d38f12bb.png)



Recipe が作成されました

![image-20220723142358806.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/a8779411-d7ad-95a8-27ac-5a6171434ce5.png)



# Recipe から Job の作成

作成した Recipe を選択して、Create job with this recipe を押します

![image-20220723142612825.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/0f3908f2-047b-5972-40ce-3735948cb348.png)



ジョブの名前と、データセットを選択します

![image-20220723142908271.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/c95d7807-2242-42b4-27a6-c15d529be160.png)



ジョブ実行結果の出力先として、S3 を選択します。そして、出力に関する詳細を Settings で変更できます。

![image-20220723143214659.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/350d1ea9-806a-84fd-efb2-0b60f26c8054.png)



Settings の画面は、このまま変更せずに閉じます。一番上のものは、追記型の出力です。

![image-20220723145028287.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/a66f5dcc-12e3-437f-238d-67480898af54.png)



それ以外は Default のまま Create and run job を押します

![image-20220723145304127.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/f3647122-def7-5754-03be-137f1a2d0e41.png)



Job が Running になりました。この記事の環境では、約 4 分ほどで Job の実行が完了しました。

![image-20220723145406165.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/be784b2c-8b85-d0e4-0e6a-1cc1bbaf844c.png)



S３ バケットに、あらたに `outputs` のディレクトリが作成されています。`outputs` の中には、Job ごとのディレクトリが新たに作成され、その下に実行結果の csv が出力されています。

```
glue-databrew-adddata-test01
├── outputs
│   └── adddata-sales01-job_23Jul2022_1658555829911
│       └── adddata-sales01-job_23Jul2022_1658555829911_part00000.csv
```



# Glue の Crawler でテーブル定義を自動生成

Add crawler

![image-20220723155054454.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/ab93f7bc-f813-c526-ef6b-3e06bdfdf766.png)



名前を指定

![image-20220723155148791.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/04bf4449-9f5b-e4ac-ac76-da9cbf6b0364.png)



このまま Next を押します

![image-20220723155247653.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/3d829b18-cd3a-71a2-de14-e70cf57efcc5.png)



対象の S3 バケットとディレクトリを指定します

![image-20220723155421787.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/b464e552-0b4f-9842-9397-0c5862244a6d.png)



Next を押します

![image-20220723155512850.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/df041361-3061-6865-7b12-569c260b3084.png)



IAM Role を選んで Next

![image-20220723155530083.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/aa89ceb1-62fc-c40f-77b9-856672ba72f8.png)



オンデマンド実行

![image-20220723155557693.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/7a172e7d-e58a-8dc9-bf21-ff15f37b530e.png)



Database を選択して Next を押します

![image-20220723155802015.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/57a95221-29f5-5d40-c9e8-3d75489c86c4.png)



Finish を押します

![image-20220723155832198.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/f35473c5-d97b-d0f5-4d22-77f6f72a4a1e.png)



作成した Crawler を Run します

![image-20220723155912773.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/f499f356-f24e-8e2c-d4b6-bf81c4e115e3.png)



テーブル定義が作成されました

![image-20220723160620193.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/b23fc443-9ef7-f232-c44f-1222b42ecf4a.png)



テーブル定義を以下のクエリーで確認可能です

```
SHOW CREATE TABLE `outputs`;
```



表示結果

- パーティションが有効になっれえいることがわかります。

```
CREATE EXTERNAL TABLE `outputs`(
  `date` string, 
  `shiten_code` bigint, 
  `revenue` bigint, 
  `name` string)
PARTITIONED BY ( 
  `partition_0` string)
ROW FORMAT DELIMITED 
  FIELDS TERMINATED BY ',' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  's3://glue-databrew-adddata-test01/outputs/'
TBLPROPERTIES (
  'CrawlerSchemaDeserializerVersion'='1.0', 
  'CrawlerSchemaSerializerVersion'='1.0', 
  'UPDATED_BY_CRAWLER'='glue-databrew-adddata-test', 
  'areColumnsQuoted'='false', 
  'averageRecordSize'='25', 
  'classification'='csv', 
  'columnsOrdered'='true', 
  'compressionType'='none', 
  'delimiter'=',', 
  'objectCount'='1', 
  'recordCount'='21', 
  'sizeKey'='534', 
  'skip.header.line.count'='1', 
  'typeOfData'='file')
```





# Athena の動作確認

SELECT 文を投げてみます。

```
SELECT * FROM "glue-databrew-adddata"."outputs";
```

![image-20220723160846930.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/b3182342-d3f8-e09c-0f04-829ce4e2d070.png)



ちゃんと結合された状態でデータが見えています。

![image-20220723160906116.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/1b14026a-7297-7df4-d65b-5a86de099c18.png)



# Data Brew で新たなデータを追加

ここからが本番です。新たなデータセットを追加していきます。4~5 月のデータを S3 にアップロードしました。

```
date,shiten_code,revenue
20100401,0001,1000
20100401,0002,2000
20100401,0003,3000
20100401,0004,4000
20100401,0005,5000
20100501,0001,1000
20100501,0002,2000
20100501,0003,3000
20100501,0004,4000
20100501,0005,5000
```



DataBrew 上で、データセットとして作成します

![image-20220723161354546.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/37c2cc50-0610-bb39-ec49-f160e3fae133.png)



既存のレシピから新たなジョブを実行します。Create job with this recipe を押します。

![image-20220723161045972.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/a5e0458b-1c4b-1fd0-bc78-85cdb056c77e.png)



新たに登録したデータセットを選択します。

![image-20220723161507396.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/7f9b3b2b-eaf4-4019-06c7-c988cec83522.png)



出力先の S3 バケットを選択します。ジョブ実行時に新たなディレクトリを生成する Setting を入れている状態です。

![image-20220723161820579.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/6c51eed8-eada-bcc3-fb8c-a12150f46fe4.png)



Create

![image-20220723161733523.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/e092f301-2f60-963e-bea1-d3614e87e0ec.png)



Job が Running となりました

![image-20220723161905507.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/d4d5b4de-772b-5c70-1a16-a70c44863b76.png)



Job が完了すると、S3 の `outputs` ディレクトリの下に、あらたなディレクトリが作成されて、ジョブの実行結果が格納されています。

```
> s3-tree glue-databrew-adddata-test01
glue-databrew-adddata-test01
├── outputs
│   ├── adddata-sales01-job_23Jul2022_1658555829911
│   │   └── adddata-sales01-job_23Jul2022_1658555829911_part00000.csv
│   └── adddata-sales02-job_23Jul2022_1658560796182
│       └── adddata-sales02-job_23Jul2022_1658560796182_part00000.csv
```



# Athena で動作確認

この段階では、クロウラーで作成したテーブルではデータが更新されていません。なぜなら、パーティションが有効になっているテーブルなので、パーティションの反映が必要です。`MSCK REPAIR TABLE` コマンドで追加してもいいのですが、今回は再度クロウラーを実行してみましょう。

![image-20220723171403563.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/4fd5819e-0061-a6fe-1d1f-6954e2d46385.png)



これによって、新たにパーティションが認識されます。

![image-20220723171947490.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/49a383ab-0b32-4312-bf4b-584281dcb4e2.png)



これで Athena 上のクエリーにも反映されるようになりました。

![image-20220723172029559.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/d4282068-00b8-16e4-9325-7ed5de4f9d8d.png)



# 検証を通じてわかったこと

- 時系列データのように、日付ごとにデータが増えていくユースケースの場合は、比較的シンプル。例えば、1日ごとにデータの加工を行い、既存のテーブルに紐づく S3 に追加でデータをアップロードすればよい
  - パーティションが有効の場合は、再度クロウラーの実施や、`MSCK REPAIR TABLE` コマンドなどで新しいパーティションを認識させてあげる必要があり
- 過去のデータも含めてまるまる変更となるデータの場合は、すべてのデータを再加工する方法がまずは考えられる。(DROP TABLE → CREATE TABLE)
  - データ量が少ない場合は、全データの再加工がシンプルで良さそう
  - データ量が多い場合。Athena が Iceberg に対応したので、UPDATE クエリーで過去のデータを更新できようになった。この辺りも検討に加えると良いかもしれない 
- Glue DataBrew の Recipe を ジョブとして実行するときに、指定できるデータセットは1個のみ。
  - Join や Union するときに結合する 2個目以降のテーブルは、Recipe 内で指定したデータセットで固定となる
- データセットにアスタリスクを指定して、複数の csv を対象にすることができる。
  - Join や Union するときに、外部のデータセットを指定している場合、うまくアスタリスクが利用できそうかも
  - もしくは、普通に csv を上書きしてもいいかもしれない
- Glue DataBrew の Job の Output にある Settings を選ぶことで、追記型にするのか、上書き型にするのか選択可能。
![image-20220723145144935.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1002774/9fcbfb95-747f-4cec-86d9-2ed2ad150df4.png)

- Athena では、各種名前にハイフンが利用されていると一部の機能が利用できない。そのため、名前にハイフンではなくアンダーバーを利用するのが良い
- Glue DataBrew でデータ加工した csv を対象に、Glue Crawler を実行したとき、基本的にはパーティションが有効になる
  - DataBrew のジョブ実行時に、新たなディレクトリを生成する Output Setting を選択しているとき、パーティションとして認識される
  - デフォルトの動作が新たなディレクトリを生成するので、基本的にはパーティションが有効になりそう
- Glue Grawler によって作成されたテーブルから CREATE TABLE を生成して、手動でパーティションが無いテーブルを作成することも可能



# Appendix : パーティションない CREATE TABLE

```
CREATE EXTERNAL TABLE `outputs_no_partition`(
  `date` string, 
  `shiten_code` bigint, 
  `revenue` bigint, 
  `name` string)
ROW FORMAT DELIMITED 
  FIELDS TERMINATED BY ',' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  's3://glue-databrew-adddata-test01/outputs/'
TBLPROPERTIES (
  'CrawlerSchemaDeserializerVersion'='1.0', 
  'CrawlerSchemaSerializerVersion'='1.0', 
  'UPDATED_BY_CRAWLER'='glue-databrew-adddata-test', 
  'areColumnsQuoted'='false', 
  'averageRecordSize'='25', 
  'classification'='csv', 
  'columnsOrdered'='true', 
  'compressionType'='none', 
  'delimiter'=',', 
  'objectCount'='1', 
  'recordCount'='21', 
  'sizeKey'='534', 
  'skip.header.line.count'='1', 
  'typeOfData'='file')
```