『Data Uploader』で実践!データアップロードジョブ(MySQLのデータを抽出→変換処理→Amazon S3にアップロード) #データ統合基盤 #CSアナリティクス

2020.12.13

当エントリは『クラスメソッド CSアナリティクス Advent Calendar 2020』13日目のエントリです。

当エントリでは、クラスメソッドが展開しているデータ統合基盤サービス『CSアナリティクス』(以降"CSA")のプロダクト群を用いて『任意のデータソースからデータを抽出し、変換処理を経た上で任意のクラウドストレージにデータをアップロードする』一連の流れを作り上げる流れについて説明します。

目次

 

「データアップロードジョブ」とは

CSA Data Uploaderでは、作成出来る「ジョブの定義」に幾つかパターンが存在します。当エントリで紹介するのはその中の「データアップロード」というジョブです。

このジョブは『任意の場所からデータを抽出して、簡易的な変換処理を踏まえた後でクラウドストレージにファイルをアップロード』するというケースを実現するためのジョブタイプです。ジョブ作成時に『入力』『変換』『出力』の処理パートが用意され、それぞれのパートで必要な処理を画面UI経由で設定します。

 

当エントリで実践する内容

当エントリでは、以下の処理を経てデータをアップロードする流れをCSA Data Uploaderで実践してみます。

  • 入力:オンプレ想定のMySQLからODBC接続を介してデータを抽出
  • 変換:抽出したデータに対し、「必要な項目の選択」と「項目順序の並び替え」を行う
  • 出力:抽出・変換したデータをAmazon S3に任意の形式でファイルとしてアップロード

 

CSA Data Uploader データアップロード実践

 

入力処理の作成:MySQLからデータを抽出

まずはファーストステップ、MySQLからデータを抽出するパートの実践です。検証用に、郵便番号情報を下記の形でMySQLデータベースに格納しておきました。

$ mysql -h xxxxxxxxxxxxx --local-infile=1 -u xxxxxxx -p 
mysql> use csadudb;
mysql> LOAD DATA LOCAL INFILE './ken_all-utf8.csv' \
    -> INTO TABLE csadudb.zipcode \
    -> FIELDS TERMINATED BY ',' \
    -> OPTIONALLY ENCLOSED BY '"' \
    -> LINES TERMINATED BY '\r\n';
Query OK, 124508 rows affected (3.90 sec)
Records: 124508  Deleted: 0  Skipped: 0  Warnings: 0

mysql> SELECT COUNT(*) FROM csadudb.zipcode;
+----------+
| COUNT(*) |
+----------+
|   124508 |
+----------+
1 row in set (0.18 sec)

mysql> SELECT * FROM csadudb.zipcode LIMIT 20;
+---------+-------------+---------+--------------------+-----------------------------------+--------------------------------------------+-----------+--------------------+-----------------------------------------+-------+-------+-------+-------+-------+-------+
| jiscode | zipcode_old | zipcode | pref_kana          | city_kana                         | street_kana                                | pref      | city               | street                                  | flag1 | flag2 | flag3 | flag4 | flag5 | flag6 |
+---------+-------------+---------+--------------------+-----------------------------------+--------------------------------------------+-----------+--------------------+-----------------------------------------+-------+-------+-------+-------+-------+-------+
| 01101   | 060         | 0600000 | ホッカイドウ       | サッポロシチュウオウク            | イカニケイサイガナイバアイ                 | 北海道    | 札幌市中央区       | 以下に掲載がない場合                    |     0 |     0 |     0 |     0 |     0 |     0 |
| 01101   | 064         | 0640941 | ホッカイドウ       | サッポロシチュウオウク            | アサヒガオカ                               | 北海道    | 札幌市中央区       | 旭ケ丘                                  |     0 |     0 |     1 |     0 |     0 |     0 |
| 01101   | 060         | 0600041 | ホッカイドウ       | サッポロシチュウオウク            | オオドオリヒガシ                           | 北海道    | 札幌市中央区       | 大通東                                  |     0 |     0 |     1 |     0 |     0 |     0 |
| 01101   | 060         | 0600042 | ホッカイドウ       | サッポロシチュウオウク            | オオドオリニシ(1-19チョウメ)               | 北海道    | 札幌市中央区       | 大通西(1〜19丁目)                  |     1 |     0 |     1 |     0 |     0 |     0 |
| 01101   | 064         | 0640820 | ホッカイドウ       | サッポロシチュウオウク            | オオドオリニシ(20-28チョウメ)              | 北海道    | 札幌市中央区       | 大通西(20〜28丁目)                |     1 |     0 |     1 |     0 |     0 |     0 |
| 01101   | 060         | 0600031 | ホッカイドウ       | サッポロシチュウオウク            | キタ1ジョウヒガシ                          | 北海道    | 札幌市中央区       | 北一条東                                |     0 |     0 |     1 |     0 |     0 |     0 |
| 01101   | 060         | 0600001 | ホッカイドウ       | サッポロシチュウオウク            | キタ1ジョウニシ(1-19チョウメ)              | 北海道    | 札幌市中央区       | 北一条西(1〜19丁目)                |     1 |     0 |     1 |     0 |     0 |     0 |
| 01101   | 064         | 0640821 | ホッカイドウ       | サッポロシチュウオウク            | キタ1ジョウニシ(20-28チョウメ)             | 北海道    | 札幌市中央区       | 北一条西(20〜28丁目)              |     1 |     0 |     1 |     0 |     0 |     0 |
| 01101   | 060         | 0600032 | ホッカイドウ       | サッポロシチュウオウク            | キタ2ジョウヒガシ                          | 北海道    | 札幌市中央区       | 北二条東                                |     0 |     0 |     1 |     0 |     0 |     0 |
| 01101   | 060         | 0600002 | ホッカイドウ       | サッポロシチュウオウク            | キタ2ジョウニシ(1-19チョウメ)              | 北海道    | 札幌市中央区       | 北二条西(1〜19丁目)                |     1 |     0 |     1 |     0 |     0 |     0 |
| 01101   | 064         | 0640822 | ホッカイドウ       | サッポロシチュウオウク            | キタ2ジョウニシ(20-28チョウメ)             | 北海道    | 札幌市中央区       | 北二条西(20〜28丁目)              |     1 |     0 |     1 |     0 |     0 |     0 |
| 01101   | 060         | 0600033 | ホッカイドウ       | サッポロシチュウオウク            | キタ3ジョウヒガシ                          | 北海道    | 札幌市中央区       | 北三条東                                |     0 |     0 |     1 |     0 |     0 |     0 |
| 01101   | 060         | 0600003 | ホッカイドウ       | サッポロシチュウオウク            | キタ3ジョウニシ(1-19チョウメ)              | 北海道    | 札幌市中央区       | 北三条西(1〜19丁目)                |     1 |     0 |     1 |     0 |     0 |     0 |
| 01101   | 064         | 0640823 | ホッカイドウ       | サッポロシチュウオウク            | キタ3ジョウニシ(20-30チョウメ)             | 北海道    | 札幌市中央区       | 北三条西(20〜30丁目)              |     1 |     0 |     1 |     0 |     0 |     0 |
| 01101   | 060         | 0600034 | ホッカイドウ       | サッポロシチュウオウク            | キタ4ジョウヒガシ(1-8チョウメ)             | 北海道    | 札幌市中央区       | 北四条東(1〜8丁目)                  |     1 |     0 |     1 |     0 |     0 |     0 |
| 01101   | 060         | 0600004 | ホッカイドウ       | サッポロシチュウオウク            | キタ4ジョウニシ(1-19チョウメ)              | 北海道    | 札幌市中央区       | 北四条西(1〜19丁目)                |     1 |     0 |     1 |     0 |     0 |     0 |
| 01101   | 064         | 0640824 | ホッカイドウ       | サッポロシチュウオウク            | キタ4ジョウニシ(20-30チョウメ)             | 北海道    | 札幌市中央区       | 北四条西(20〜30丁目)              |     1 |     0 |     1 |     0 |     0 |     0 |
| 01101   | 060         | 0600035 | ホッカイドウ       | サッポロシチュウオウク            | キタ5ジョウヒガシ                          | 北海道    | 札幌市中央区       | 北五条東                                |     0 |     0 |     1 |     0 |     0 |     0 |
| 01101   | 060         | 0600005 | ホッカイドウ       | サッポロシチュウオウク            | キタ5ジョウニシ(1-24チョウメ)              | 北海道    | 札幌市中央区       | 北五条西(1〜24丁目)                |     1 |     0 |     1 |     0 |     0 |     0 |
| 01101   | 064         | 0640825 | ホッカイドウ       | サッポロシチュウオウク            | キタ5ジョウニシ(25-29チョウメ)             | 北海道    | 札幌市中央区       | 北五条西(25〜29丁目)              |     1 |     0 |     1 |     0 |     0 |     0 |
+---------+-------------+---------+--------------------+-----------------------------------+--------------------------------------------+-----------+--------------------+-----------------------------------------+-------+-------+-------+-------+-------+-------+
20 rows in set (0.17 sec)

下記情報を参考に、「MySQLのODBCドライバインストール&設定」「CSA Data UploaderのODBC接続の作成」を行い、

CSA Data Uploaderのジョブとして「データアップロードジョブ」を作成、入力種別に「ODBC接続」を指定した上で対象データベースにクエリを実行する設定を行います。[読み込みテスト]押下でOKが出ればアクセス出来ています。[保存]を押下。

入力要素右の[データ表示]押下でデータが表示出来ていれば入力設定は上手く行っています。

 

変換処理の作成:項目の抽出と並び替え

ここでは、入力処理で取得出来ていた情報から「必要な項目のみ選択」し「選択した項目を並び替え」る処理を変換処理として定義します。

まずは「カラム選択」から。ジョブの変換要素作成ボタンを押下し、下記のような形で任意の項目の選択チェックを外します。選択した内容のみが次の処理対象の列となる流れです。

続いて「カラムの並び替え」。ジョブの変換要素作成ボタンを押下して処理を追加した上で、「変更後の並び順」で表示された並び順で後続処理へとデータを連携します。ここでは「zipcode_old」を一番後ろに配置換えする処理を定義しました。[保存]を押下。

これで2つの変換処理が入力データに対して定義されました。変換処理右の[データ表示]押下で、変換処理を経た項目が指定した並び順で絞り込み&配置されている事が確認出来ます。

 

出力処理の作成:Amazon S3へアップロード

最後にアップロード処理の定義。ここでは予め任意のAWSアカウント配下のAmazon S3にアップロード可能なAWS CLIの設定を行っておき、

対象ジョブの出力要素作成で下記のような形で設定を行います。ここでは敢えて任意の固定ファイル名でアップロードする形を指定してみました。なお、CSA Data Upploaderのアップロード処理に於いては現状下記仕様は共通/固定のものとなっています。

  • ファイルの文字コード:UTF-8固定
  • ファイルの圧縮有無及び圧縮形式:圧縮する&gzip圧縮固定

 

データアップロードジョブの実行&内容確認

これで、今回実現したい処理の定義がすべて整いました。[保存して実行]で早速挙動を確認してみます。

実行タイプは「今すぐ実行」で試してみます。

実行がなされると画面上部には実行を開始した旨のメッセージが表示されます。

そして処理が完了すると画面右上に完了通知メッセージが表示されます。

実行結果は履歴として確認可能です。メニューの[参照]→[ジョブ実行履歴]に遷移し、対象ジョブの履歴表示アイコンを選択。

以下のように、実行結果をログとして確認出来ました。

また、合わせて対象Amazon S3のパスも確認してみます。指定した形式やファイル名でアップロードされている事が確認出来ました。

ファイルを一旦ダウンロードして中身も確認してみます。tsv形式で生成されている事が確認出来ました。

% gunzip ken-all-utf8.tsv.gz 
% wc ken-all-utf8.tsv 
  124509  795197 6990006 ken-all-utf8.tsv
% vi ken-all-utf8.tsv
jiscode zipcode pref    city    street  zipcode_old
01102   0010000 北海道  札幌市北区      以下に掲載がない場合    001
01102   0010010 北海道  札幌市北区      北十条西(1〜4丁目)  001
01102   0010011 北海道  札幌市北区      北十一条西(1〜4丁目)        001
01102   0010012 北海道  札幌市北区      北十二条西(1〜4丁目)        001
01102   0010013 北海道  札幌市北区      北十三条西(1〜4丁目)        001
01102   0010014 北海道  札幌市北区      北十四条西(1〜4丁目)        001
01102   0010015 北海道  札幌市北区      北十五条西(1〜5丁目)        001
01102   0010016 北海道  札幌市北区      北十六条西(1〜6丁目)        001
01102   0010017 北海道  札幌市北区      北十七条西(1〜6丁目)        001
01102   0010018 北海道  札幌市北区      北十八条西(1〜7丁目)        001
01102   0010019 北海道  札幌市北区      北十九条西(2〜7丁目)        001
01102   0010020 北海道  札幌市北区      北二十条西(2〜10丁目)      001
01102   0010021 北海道  札幌市北区      北二十一条西    001
01102   0010022 北海道  札幌市北区      北二十二条西    001
:
:

 

まとめ

という訳で、『クラスメソッド CSアナリティクス Advent Calendar 2020』13本目のエントリ、CSA Data Uploaderで「データアップロードジョブ」(任意の入力/変換/出力を一連の処理として定義したもの)を作成・実行する手順の紹介でした。CSA Data Uploaderは1ヶ月間のトライアル利用が可能となっています。興味をお持ち頂いた方は是非無料版ダウンロードページからインストーラを入手頂き、お試し頂けますと幸いです。

では、明日(14日目)のエントリもお楽しみに!