XplentyでAPIを叩いてデータを取得してみる（政府統計の総合窓口（e-Stat））

『カスタマーストーリー』を支えるツール特集

kobayashi.m

2020.07.14

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

はじめに

データアナリティクス事業本部のkobayashiです。

XplentyはETL、ELTツールとして様々なデータソースを扱え、また処理もXplentyのGUIで直感的に作成できます。今回もXplentyを使ってAPIを叩いてデータを取しそのデータをDBに保存してみます。

Xplenty | Simplified ETL & ELT to BigQuery, Snowflake, Redshift & Azure

前回まではレスポンスの取り扱いにそれほど困ることがないパターンを扱いました。今回は複雑なレスポンスを返すAPIのデータをXplentyで取り扱ってみたいと思い、e-StatのAPIをソースにし設定・ジョブ実行を行います。またトークンや統計表IDを変数に格納して汎用的に扱えるようにします。

前回の記事

APIで取得するデータ

今回は政府統計の総合窓口（e-Stat）のデータを取り扱います。 e-Statとは各省庁が実施している統計調査のデータを利用できるサイトです。

政府統計の総合窓口（ｅ－Ｓｔａｔ）は、各府省情報化統括責任者（CIO）連絡会議で決定された「統計調査等業務の業務・システム最適化計画」に基づき、日本の政府統計関係情報のワンストップサービスを実現するため2008年から本運用を開始した政府統計のポータルサイトです。

引用元このサイトについて | 政府統計の総合窓口

e-StatにはAPIがありユーザー登録をしてトークンを取得することでAPI経由でデータが取得できます。

政府統計の総合窓口(e-Stat)−API機能へようこそ | 政府統計の総合窓口(e-Stat)−API機能

今回はこの中の「平成２７年国勢調査小地域　年齢（５歳階級、４区分）別、男女別人口東京都（統計表ID：8003000051）」のデータをXplentyを使って取得し、最終的にRedshiftへ保存します。

e-stat APIのレスポンスの確認

今回扱うAPIは政府統計の総合窓口（e-Stat）のAPI 仕様 3.0版 | 政府統計の総合窓口(e-Stat)−API機能になります。Curl等でリクエストURLを叩いてレスポンスを確認します。

リクエストURL

http://api.e-stat.go.jp/rest/3.0/app/json/getStatsData?appId={トークン}d&lang=J&statsDataId=8003000051&metaGetFlg=Y&explanationGetFlg=N&annotationGetFlg-N&cntGetFlg=N

レスポンス

{
  "GET_STATS_DATA": {
    "RESULT": {
      "STATUS": 0,
      "ERROR_MSG": "正常に終了しました。",
      "DATE": "2020-06-24T10:34:26.720+09:00"
    },
    "PARAMETER": {
      "LANG": "J",
      "STATS_DATA_ID": 8003000051,
      "NARROWING_COND": {
        "LEVEL_AREA_COND": 3
      },
      "DATA_FORMAT": "J",
      "START_POSITION": 1,
      "LIMIT": 10,
      "METAGET_FLG": "Y",
      "EXPLANATION_GET_FLG": "N",
      "CNT_GET_FLG": "N"
    },
    "STATISTICAL_DATA": {
      ...
      },
      "CLASS_INF": {
        "CLASS_OBJ": [
          {
            "@id": "cat01",
            "@name": "年齢（５歳階級、４区分）別、男女別人口",
            "CLASS": [
              {
                "@code": "0010",
                "@name": "総数、年齢「不詳」含む",
                "@level": "1",
                "@unit": "人"
              },
              {
                "@code": "0020",
                "@name": "　総数０～４歳",
                "@level": "2",
                "@unit": "人",
                "@parentCode": "0010"
              },
              ...
            ]
          },
          {
            "@id": "cat02",
            "@name": "秘匿地域・合算地域有り",
            "CLASS": [
              {
                "@code": "1",
                "@name": "無し",
                "@level": "1"
              },
              {
                "@code": "2",
                "@name": "合算",
                "@level": "1"
              },
              {
                "@code": "3",
                "@name": "秘匿",
                "@level": "1"
              }
            ]
          },
          {
            "@id": "area",
            "@name": "東京都",
            "CLASS": [
              {
                "@code": "13101001001",
                "@name": "千代田区丸の内１丁目",
                "@level": "3",
                "@parentCode": "131010010"
              },
              {
                "@code": "13101001002",
                "@name": "千代田区丸の内２丁目",
                "@level": "3",
                "@parentCode": "131010010"
              },
              ...
            ]
          }
        ]
      },
      "DATA_INF": {
        "NOTE": {
          "@char": "-",
          "$": "当該数値がないもの"
        },
        "VALUE": [
          {
            "@cat01": "0010",
            "@cat02": "1",
            "@area": "13101005001",
            "@unit": "人",
            "$": "0"
          },
          {
            "@cat01": "0010",
            "@cat02": "1",
            "@area": "13101008001",
            "@unit": "人",
            "$": "832"
          },
          ...
        ]
      }
    }
  }
}

レスポンスを見ていくと $.GET_STATS_DATA.STATISTICAL_DATAの中に欲しいデータが入っており、さらにその中のCLASS_INF.CLASS_OBJにcat01,cat02,areaのマスタ類がリストで入っています。更にその中のCLASSにリストでマスタデータが入っています。

同じく$.GET_STATS_DATA.STATISTICAL_DATAの中にDATA_INFがありこの中のVALUEにリスト形式で統計データが入っています。

Xplentyのパッケージの作成

Xplentyのパッケージの中身は大まかに以下になります。

e-stat APIにリクエストを送りレスポンスを取得する
受け取ったデータをマスタ類と統計データに分ける
マスタデータが入ったJsonデータを加工してRedshiftへ保存できる形に加工する
加工したマスタデータをマスタテーブル(estat_mst)に保存できるようにまとめる
統計データが入ったJsonデータを加工してRedshiftへ保存できる形に加工する
マスタテーブル(estat_mst)と統計データテーブル(estat_data)用のRedshiftコンポーネントを作成する

これらのコンポーネントを組み合わせてパッケージを作成していきます。

はじめにサイドバーからPackagesを選択してNew Packageを押下し新しいパッケージを作成します。

1.e-stat API用にREST APIコンポーネントを作成

まずはe-statのAPIを使ってデータを取得し、加工・保存を行います。またトークンが変わった際の利便性や統計表IDを簡単に切り替えて他のデータを取得しやすくするため、Xplentyのユーザー変数機能を使います。

手順1-1）.画面上部の...を押下し編集設定画面へ進む。

手順1-2）.Add anotherを押下してトークン用、統計表ID用の変数を設定しSaveを押下して保存する。

key	value
appId	'{トークン}'
statsDataId	'8003000051'

文字列として扱うので値は必ず'クォートで囲います。ここで変数を指定することで以降の手順中にて$appId,$statsDataIdの形で設定した値を取り出せます。

手順1-3）.Add componetを押下し、SourcesでREST APIを選択する

手順1-4）.作成されたアイコンをダブルクリックするとREST APIコンポーネント設定用のモーダルが表示されるので、AuthenticationでNoneを選択する。

手順1-5）.Request and responseの設定を行いNextを押下する。

Method : GETを選択
URL : http://api.e-stat.go.jp/rest/3.0/app/json/getStatsData?appId=$appId&lang=J&statsDataId=$statsDataId&metaGetFlg=Y&explanationGetFlg=N&annotationGetFlg-N&cntGetFlg=Nを入力
Headers
- Key : Content-Typeを入力
- VALUE : application/jsonを入力
Body : (空)
Use Pagination : チェックしない
Base record JSONPath Expression : $.GET_STATS_DATA.STATISTICAL_DATAを入力

他の設定値はデフォルトのままにします。

手順1-6）.Response Schemaへ進み、Selected Fieldsで右下のAddを押下し下記の設定値でSelected Fieldsを設定する。その後Saveを押下し、設定を保存する。

Key	Alias	Type
CLASS_INF	CLASS_INF	MAP
DATA_INF	DATA_INF	MAP

この画面ではレスポンスの内容を確認できるので必要なデータを確認しながら設定を行えます。

これでREST APIコンポーネントの設定は終わりますので、次にデータ加工用のコンポーネントをを設定します。

2. 受け取ったデータをマスタ類と統計データに分ける

手順2-1）.Add componetを押下し、TransformationsでCloneを選択してCloneコンポーネントを作成しREST APIコンポーネントと接続する

3. マスタデータが入ったJsonデータを加工してRedshiftへ保存できる形に加工する

次にマスタデータを作成します。Selectコンポーネントを2つ繋げて、1つ目のコンポーネントでレスポンスをフラット化し、2つ目のコンポーネントでJson文字列を扱う関数を駆使して欲しい形に加工します。これをcat01,cat02,areaの3種類分設定します。

手順3-1）.Add componetを押下し、TransformationsでSelectを選択する。この操作を2回行い2つSelectコンポーネントを作成する。

手順3-2）.作成したSelectコンポーネントを前段のREST APIコンポーネントと接続する。（接続していないとカラムのマッピングができないため）

手順3-3）.作成したSelectコンポーネントの1つ目を選択して設定を行いSaveを押下し保存する。

EXPRESSION、ALIASに以下のように入力

EXPRESSION	ALIAS
JsonStringToMap(JsonExtract (CLASS_INF#'CLASS_OBJ','$.[0]'))#'@id'	class_id
JsonStringToMap(JsonExtract (CLASS_INF#'CLASS_OBJ','$.[0]'))#'@name'	class_name
Flatten(JsonStringToBag (JsonExtract (CLASS_INF#'CLASS_OBJ','$.[0].CLASS')))	values
ToString(SwitchTimeZone(CurrentTime(),'Asia/Tokyo'), 'yyyy-MM-dd\'T\'HH:mm:ss')	created_datetime

手順3-4）.作成したSelectコンポーネントの2つ目を選択して設定を行いSaveを押下し保存する。

EXPRESSION、ALIASに以下のように入力

EXPRESSION	ALIAS
class_id	class_id
class_name	class_name
JsonStringToMap(values)#'@code'	code
JsonStringToMap(values)#'@parentCode'	parentCode
JsonStringToMap(values)#'@unit'	unit
JsonStringToMap(values)#'@level'	level
JsonStringToMap(values)#'@name'	name
created_datetime	created_datetime

手順3-1〜3-4を繰り返して3種類分作成します。

作成する際の手順時の変更点は1箇所です。

cat02の作成をする際には手順3-3）でのEXPRESSIONの設定で'$.[0]'の部分を'$.[1]'に変更
areaの作成をする際には手順3-3）でのEXPRESSIONの設定で'$.[0]'の部分を'$.[2]'に変更

設定が終わると下記の状態になります。

4. 加工したマスタデータをマスタテーブルに保存できるようにまとめる

cat01,cat02,areaで別テーブル分けるよりも1テーブルでまとめたほうが後々の扱いが楽なのでマスタテーブルとして1テーブルに保存します。また他の統計表IDのデータを入れることも考えstatsDataIdカラムも作成します。

手順4-1）.Add componetを押下し、TransformationsでUnionを選択し前段のコンポーネント2つ(cat01,cat02用)を接続する。

手順4-2）.Unionコンポーネントをダブルクリックし設定画面に進み、Auto fillを押下して自動設定でカラムマッピングを行う。

手順4-1、4-2を繰り返し、手順4-2）のUnionコンポーネントとareaのSelectコンポーネントを結合します。

手順4-3）.cat01,cat02,areaを結合したUnionコンポーネントに新規に作成したSelectコンポーネントを接続する。

手順4-4）.Selectコンポーネントをダブルクリックし、カラムマッピングを行う。統計表IDを追加したいのでAuto fillを押下して自動設定を行った後に以下を追加する。

EXPRESSION : '$statsDataId'を入力(文字列として扱うので値は必ず'クォートで囲む)
ALIAS : statsDataIdを入力

ここまでの手順で下図の形になります。

5. 統計データが入ったJsonデータを加工してRedshiftへ保存できる形に加工する

マスタデータの作成は終わったので次に統計データを作成します。ここは再度Json文字列からデータベース用のデータを作成するので、Selectコンポーネントを2つ繋げて、1つ目のコンポーネントでレスポンスをフラット化し、2つ目のコンポーネントでJson文字列を扱う関数を駆使して欲しい形に加工します。

手順5-1）.Add componetを押下し、TransformationsでSelectを選択する。この操作を2回行い2つSelectコンポーネントを作成する。

手順5-2）.作成したSelectコンポーネントを前段のCloneコンポーネントと接続する。（接続していないとカラムのマッピングができないため）

手順3-3）.作成したSelectコンポーネントの1つ目を選択して設定を行いSaveを押下し保存する。

EXPRESSION、ALIASに以下のように入力

EXPRESSION	ALIAS
Flatten(JsonStringToBag (DATA_INF#'VALUE'))	values
ToString(SwitchTimeZone(CurrentTime(),'Asia/Tokyo'), 'yyyy-MM-dd\'T\'HH:mm:ss')	created_datetime

手順3-4）.作成したSelectコンポーネントの2つ目を選択して設定を行いSaveを押下し保存する。

EXPRESSION、ALIASに以下のように入力

EXPRESSION	ALIAS
'$statsDataId'	statsDataId
JsonStringToMap(values)#'@cat01'	cat01
JsonStringToMap(values)#'@cat02'	cat02
JsonStringToMap(values)#'@area'	area
JsonStringToMap(values)#'@unit'	unit
JsonStringToMap(values)#'$'	cnt
created_datetime	created_datetime

6. 保存先としてRedshiftコンポーネント作成

前回までと同様に簡単に行うためテーブルは予め作成せずXplentyのジョブで自動作成し、データは毎回洗い替えを行う設定にします。

手順4-1）.Add componetを押下し、DestinationsでAmazon Redshiftを選択する

手順4-2）.画面上にRedshiftのアイコンが作成されるので先に作成したSelectコンポーネントのアイコンとDrag&Dropで紐付ける。

手順4-3）.RedshiftのアイコンをダブルクリックするとRedshiftコンポーネント設定用のモーダルが表示されるので、設定済みのRedshiftのconnectionを選択する。

手順4-4）.移行先のRedshiftのスキーマとテーブルを入力し各種設定を行い、Nextを押下する。

Automatically create table if it doesn't exist : チェックする
Automatically add missing columns : チェックする
Operation type : Overwrite (Truncate and insert)を選択する

他の設定はデフォルトで問題ありません。

これでパッケージの作成は完了です。最終的なパッケージの形は下図になります。

Xplentyのパッケージの実行と確認

実際にジョブを実行して結果を確認します。

Redshitでデータを確認

マスタテーブル

select * from cm_kobayashi_test.estat_mst;

statsdataid	class_id	class_name	code	parentcode	unit	level	name	created_datetime
8003000051	cat01	年齢（５歳階級、４区分）別、男女別人口	0010	NULL	人	1	総数、年齢「不詳」含む	2020-06-29T09:55:27
8003000051	cat02	秘匿地域・合算地域有り	2	NULL	NULL	1	合算	2020-06-29T09:55:27
8003000051	area	東京都	13101002001	131010020	NULL	3	千代田区大手町１丁目	2020-06-29T09:55:27
8003000051	area	東京都	13101004001	131010040	NULL	3	千代田区有楽町１丁目	2020-06-29T09:55:27

Redshitでデータを確認

統計データテーブル

select * from cm_kobayashi_test.estat_data;

statsdataid	cat01	cat02	area	unit	cnt	created_datetime
8003000051	0010	1	13101008002	人	933	2020-06-29T09:55:27
8003000051	0010	1	13101009004	人	605	2020-06-29T09:55:27
8003000051	0010	1	13101016002	人	820	2020-06-29T09:55:27
8003000051	0010	1	13101017002	人	266	2020-06-29T09:55:27
8003000051	0010	1	13101018002	人	3199	2020-06-29T09:55:27

この様な形でマスタテーブルと統計データテーブルにデータを分離して登録できました。

まとめ

XplentyのREST APIコンポーネントを使ってe-stat APIからデータを取得してみました。「データがJsonの深い階層に存在する」、「データをコピーして使い回す」、「データを結合する」といった複雑な処理もレスポンスの形をしっかり把握して手順を踏めばXplentyで処理できます。

最後まで読んで頂いてありがとうございました。

XplentyでAPIを叩いてデータを取得してみる（政府統計の総合窓口（e-Stat））

はじめに

APIで取得するデータ

e-stat APIのレスポンスの確認

Xplentyのパッケージの作成

1.e-stat API用にREST APIコンポーネントを作成

2. 受け取ったデータをマスタ類と統計データに分ける

3. マスタデータが入ったJsonデータを加工してRedshiftへ保存できる形に加工する

4. 加工したマスタデータをマスタテーブルに保存できるようにまとめる

5. 統計データが入ったJsonデータを加工してRedshiftへ保存できる形に加工する

6. 保存先としてRedshiftコンポーネント作成

Xplentyのパッケージの実行と確認

Redshitでデータを確認

マスタテーブル

Redshitでデータを確認

統計データテーブル

まとめ

イベント

EVENT【5/29（水）大阪】Alteryxの使い方から導入メリットまですべてがわかるセミナー＆ワークショップ

EVENT【5/15（水）リモート】クラスメソッドの会社説明会を開催します

EVENT【5/8リモート】クラスメソッドのフリーランスエンジニア会社説明会〜フィンテック / リテール業界案件特集〜を開催します

EVENT【5/28（火）】AWSを最大活用するための1dayカンファレンス

EVENT【5/17（金）】認証機能の開発工数削減をデモで体験！次世代認証基盤サービス『Auth0 by Okta』導入実践ウェビナー

EVENT【5/14（火）】アノテーションの中途向けオンライン会社説明会を開催します

EVENT【5/23（木）】ユースケースに学ぶAWS運用のノウハウ～可視化から統制まで～

EVENT【5/16（木）】Snowflakeを触ってみよう！初めての方向けハンズオンセミナー

EVENT【5/10（金）名古屋】クラスメソッドグループの会社説明会を開催します！

EVENT【4/25（木）リモート】Google Cloudに携わるエンジニアのキャリア～クラスメソッド会社説明会～

XplentyでAPIを叩いてデータを取得してみる（政府統計の総合窓口（e-Stat））

はじめに

APIで取得するデータ

e-stat APIのレスポンスの確認

Xplentyのパッケージの作成

1.e-stat API用にREST APIコンポーネントを作成

2. 受け取ったデータをマスタ類と統計データに分ける

3. マスタデータが入ったJsonデータを加工してRedshiftへ保存できる形に加工する

4. 加工したマスタデータをマスタテーブルに保存できるようにまとめる

5. 統計データが入ったJsonデータを加工してRedshiftへ保存できる形に加工する

6. 保存先としてRedshiftコンポーネント作成

Xplentyのパッケージの実行と確認

Redshitでデータを確認

マスタテーブル

Redshitでデータを確認

統計データテーブル

まとめ

イベント

EVENT【5/29（水）大阪】Alteryxの使い方から導入メリットまですべてがわかるセミナー＆ワークショップ

EVENT【5/15（水）リモート】クラスメソッドの会社説明会を開催します

EVENT【5/8リモート】クラスメソッドのフリーランスエンジニア会社説明会 〜フィンテック / リテール 業界案件特集〜 を開催します

EVENT【5/28（火）】AWSを最大活用するための1dayカンファレンス

EVENT【5/17（金）】認証機能の開発工数削減をデモで体験！次世代認証基盤サービス『Auth0 by Okta』導入実践ウェビナー

EVENT【5/14（火）】アノテーションの中途向けオンライン会社説明会を開催します

EVENT【5/23（木）】ユースケースに学ぶAWS運用のノウハウ～可視化から統制まで～

EVENT【5/16（木）】Snowflakeを触ってみよう！初めての方向けハンズオンセミナー

EVENT【5/10（金） 名古屋】クラスメソッドグループの会社説明会を開催します！

EVENT【4/25（木）リモート】Google Cloudに携わるエンジニアのキャリア～クラスメソッド会社説明会～

関連記事

Auto Insights を使ってデータのインサイトを得る方法 #alteryx

データポリシーによる行レベルのセキュリティを使用する #tableau

Alteryx Analytics Cloud 2024年4月新機能：Tableau Connectionsの追加 #Alteryx

Alteryx Analytics Cloud の App Builderを使って分析Webアプリを作ってみた！

EVENT【5/8リモート】クラスメソッドのフリーランスエンジニア会社説明会〜フィンテック / リテール業界案件特集〜を開催します

EVENT【5/10（金）名古屋】クラスメソッドグループの会社説明会を開催します！