主夫ときどきプログラマ

データベース、Webエンジニアリング、コミュニティ、etc

APIから取得したJSONをとりあえずMySQL8.0に入れてJSON_TABLE()でどうにかする

f:id:masayuki14:20181017152938p:plain

MySQL8.0から追加された機能の一つに JSON_TABLE() 関数がある。これを使うとJSON型のデータを表形式に出力することができる。 MySQLをドキュメントストアとして使う場合、この関数を使用することで別のテーブルとJOINできるようになるので重要な関数だ。 今回はこれを使ってJSONデータをCSVに変換する簡単な方法を紹介する。

どうしてこうなった

内閣府から提供されているRESAS-APIから市町村データを取得してCSVにしたい、というタスクがあった。 以下の2つのAPI都道府県と市町村の一覧が取得できるので、都道府県ごとに結合して一つのCSVファイルを作ることを目指した。

このAPIは簡単に利用登録ができ、発行されるAPI_KEYを使って以下のようにAPIをコールできる。

$ export API_KEI=<your_api_key>
$ curl -H "X-API-KEY: $API_KEY" -X GET 'https://opendata.resas-portal.go.jp/api/v1/prefecture'

MySQL8.0を利用する

MySQLは5.7からJSON型を利用できるので、とりあえずAPIから取得したJSONをそのままテーブルに入れてみよう、ということで以下のようにテーブルを作成した。

CREATE TABLE `j_prefectures` (
  `doc` json DEFAULT NULL
) ENGINE=InnoDB;

CREATE TABLE `j_cities` (
  `doc` json DEFAULT NULL
) ENGINE=InnoDB;

とてもシンプル。

ワンライナーMySQLJSONを入れる

都道府県のAPIは1回コールすればJSONが取得できるので、それをワンライナーでそのままテーブルにINSERTする。 テーブルもシンプルなのでSQLもシンプルにできるため、プログラミング言語を使う必要はない。

$ mysql -u root -p[password] resas < <(echo "insert into j_prefectures (\`doc\`) values ('" $(curl -H 'X-API-KEY: '$API_KEY -X GET 'https://opendata.resas-portal.go.jp/api/v1/prefectures')  "')" )

簡単に解説しておく。 mysql -u root -p < file.sqlという構文でfile.sqlに書かれたSQL文を実行できる。 bashの機能で<( command )とするとcommandの出力をファイルとして扱ってくれる。 なのでechocurl でINSERT文を作ってmysqlに渡している。 また -p[password]コマンドラインからパスワードを渡すことができるが、コマンド履歴に残るので注意が必要だ。

同様に市町村一覧もAPIから取得してINSERTする。市町村のAPIprefCodeを指定しないといけないので1から47まで値をかえてコールする。 これもワンライナーで実行できる。

$ while read code; do mysql -u root -p[password] resas < <(echo "insert into j_cities(\`doc\`) values ('" $(curl -H 'X-API-KEY: '$API_KEY -X GET 'https://opendata.resas-portal.go.jp/api/v1/cities?prefCode='$code)  "')"); done < <(seq 1 47)

JSON_TABLE() を使って表にする

JSON_TABLE()を使うと、都道府県と市町村をそれぞれ表で出力することができる。

select prefecture.*
from j_prefectures, JSON_TABLE(
  `doc`,
  "$.result[*]"
  columns (
    prefCode int path '$.prefCode',
    prefName varchar(64) path '$.prefName'
  )
) prefecture;

+----------+-----------+
| prefCode | prefName  |
+----------+-----------+
|        1 | 北海道    |
|        2 | 青森県    |
|        3 | 岩手県    |
|        4 | 宮城県    |
|        5 | 秋田県    |
select cities.*
from j_cities, JSON_TABLE(
  `doc`,
  "$.result[*]"
  columns (
    prefCode int path '$.prefCode',
    cityCode varchar(64) path '$.cityCode',
    cityName varchar(64) path '$.cityName',
    bigCityFlag varchar(4) path '$.bigCityFlag'
  )
) cities;

+----------+----------+--------------------+-------------+
| prefCode | cityCode | cityName           | bigCityFlag |
+----------+----------+--------------------+-------------+
|        1 | 01100    | 札幌市             | 2           |
|        1 | 01101    | 札幌市中央区       | 1           |
|        1 | 01102    | 札幌市北区         | 1           |
|        1 | 01103    | 札幌市東区         | 1           |
|        1 | 01104    | 札幌市白石区       | 1           |

JSON_TABLE()のバグ

じつは JSON_TABLE() で作成された表へのアクセスはroot権限のユーザーしかできない。 このバグはMySQL Bugs: #90610: ERROR 1142 (42000) when using JSON_TABLEで報告されていて 8.0.13 で修正されるようだ。

CTEでラップしてJOINする

MySQL8.0からCTEが利用できるので、JSON_TABLE()で作成した表どうしをJOINすることができる。

with
prefectures as (
  select pref_tbl.*
  from j_prefectures, JSON_TABLE(
    `doc`,
    "$.result[*]"
    columns (
      prefCode int path '$.prefCode',
      prefName varchar(64) path '$.prefName'
    )
  ) pref_tbl
),
cities as (
  select city_tbl.*
  from j_cities, JSON_TABLE(
    `doc`,
    "$.result[*]"
    columns (
      prefCode int path '$.prefCode',
      cityCode varchar(64) path '$.cityCode',
      cityName varchar(64) path '$.cityName',
      bigCityFlag varchar(4) path '$.bigCityFlag'
    )
  ) city_tbl
)
select * from prefectures join cities using (prefCode);

+----------+-----------+----------+--------------------+-------------+
| prefCode | prefName  | cityCode | cityName           | bigCityFlag |
+----------+-----------+----------+--------------------+-------------+
|        1 | 北海道    | 01100    | 札幌市             | 2           |
|        1 | 北海道    | 01101    | 札幌市中央区       | 1           |
|        1 | 北海道    | 01102    | 札幌市北区         | 1           |
|        1 | 北海道    | 01103    | 札幌市東区         | 1           |
|        1 | 北海道    | 01104    | 札幌市白石区       | 1           |

CSVで出力する

これでAPIから取得したJSONを結合して一つの表にできたのでCSVファイルに出力する。

select * from prefectures join cities using (prefCode)
into outfile '/var/lib/mysql-files/prefecture.csv'
fields terminated by ',' ;

secure-file-prev による制限

secure-file-prev システム変数によって SELECT ... INTO OUTFILE などのデータの入出力が制限されているため、出力先には気をつける必要がある。

MySQL :: MySQL 8.0 Reference Manual :: 5.1.6 Server Command Options

SHOW VARIABLES で確認できるので、出力先はこれに指定されているディレクトリにしよう。

mysql> show variables like 'secure%';
+------------------+-----------------------+
| Variable_name    | Value                 |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+
1 row in set (0.00 sec)

まとめ

MySQL8.0で強化された機能を使うことで、JSONを使うタスクを簡単に処理できるので、この記事を読んだ人は是非試してみよう。