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

2020.07.14

はじめに

データアナリティクス事業本部の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とは各省庁が実施している統計調査のデータを利用できるサイトです。

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

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

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

今回はこの中の「平成27年国勢調査 小地域 年齢(5歳階級、4区分)別、男女別人口 東京都(統計表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": "年齢(5歳階級、4区分)別、男女別人口",
            "CLASS": [
              {
                "@code": "0010",
                "@name": "総数、年齢「不詳」含む",
                "@level": "1",
                "@unit": "人"
              },
              {
                "@code": "0020",
                "@name": " 総数0~4歳",
                "@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": "千代田区丸の内1丁目",
                "@level": "3",
                "@parentCode": "131010010"
              },
              {
                "@code": "13101001002",
                "@name": "千代田区丸の内2丁目",
                "@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_OBJcat01,cat02,areaのマスタ類がリストで入っています。更にその中のCLASSにリストでマスタデータが入っています。

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

Xplentyのパッケージの作成

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

  1. e-stat APIにリクエストを送りレスポンスを取得する
  2. 受け取ったデータをマスタ類と統計データに分ける
  3. マスタデータが入ったJsonデータを加工してRedshiftへ保存できる形に加工する
  4. 加工したマスタデータをマスタテーブル(estat_mst)に保存できるようにまとめる
  5. 統計データが入ったJsonデータを加工してRedshiftへ保存できる形に加工する
  6. マスタテーブル(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コンポーネント設定用のモーダルが表示されるので、AuthenticationNoneを選択する。

手順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 年齢(5歳階級、4区分)別、男女別人口 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 千代田区大手町1丁目 2020-06-29T09:55:27
8003000051 area 東京都 13101004001 131010040 NULL 3 千代田区有楽町1丁目 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で処理できます。

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