主夫ときどきプログラマ

プログラミング、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を使うタスクを簡単に処理できるので、この記事を読んだ人は是非試してみよう。

第9回 関西DB勉強会でMySQL8.0の発表してきた

f:id:masayuki14:20181013174502j:plain

kansaidbstudy.connpass.com

第9回関西DB勉強会で発表してきた。良い勉強になった。

きっかけ

きっかけはツイッターでの突然のお誘い。

「す」さんはOSS Gateというコミュニティで一緒に活動をしているけど東京の人なので会うことは殆どなかったりする。RubyコミッターでRabbitの作者なのでRuby界隈のすごい人だと思ってたら、実はDB界隈にも顔の広い人で、なんかもうすごいすごいすごい人だというのが今回わかった。だからって何も怖くないやさしい人。

他のお2人もMySQLオフィシャルの人というこで、私でいいのか状態だったけど、ものは試しということで引き受けることにした。 うまくお膳立てもしてもらったので、Blockchain勉強会 in Kyoto #05で発表したものをブラッシュアップして発表することになった。

その時の記事がこちら。 MySQL8.0のJSON型における正規化について - 主夫ときどきプログラマ

人の縁とか過去の活動とか、人生何が起こるかわからないし、何が役に立つかわからない。 でも振り返るとそれらがつながってるのがわかる。

当日の様子

関西DB勉強会への参加自体もはじめてだった。 発表中にも質問がでたりつっこみが入ったりするようで、けっこうフランクな雰囲気だった。 人数も多いのにいい雰囲気が作れているのは運営の人たちの手腕なんだろうなあと思う。

kintoneFileMakerのソリューションのセッションや(どちらも今回始めて知った)、Insight Qubeというハードウェアのセッションもあり、扱う内容の幅の広さに驚いた。

もちろんSQLServerPostgreSQLMySQL、AmazonAuroraといったエンジニア寄りの話も充実していた。 長時間の勉強会が久しぶりだったので正直疲れた。でも懇親会でいろいろとお話できたのでとても楽しい勉強会だった。次回も参加しよう。

自分の発表は

さて、自分の発表はというと、最後ということもあり時間も押していたので、予定の持ち時間よりも短い発表になった。 これは事前に予想していたので、序盤の解説をわりと飛ばしてデモを中心に行った。 先に話をした山﨑さんがうまく流れを作ってくれたので、MySQL8.0の新機能の使い方や動きなどはうまく伝わったんじゃないかと思う。

そしてちゃんと緊張した。こちらも予想通りだったので、デモでのタイピングは準備しておいたテキストのコピペを貼り付けるというテクニックで回避した。さすがだ。

でもディスプレイをミラーリングしてなかったから、スクリーンの様子が見づらいなかでのデモだったのでこれはなにか対策を考えないといけない。 ミラーリングしたらスピーカーノート見れないしなぁ。

後日Twitterのタイムライン #dbkanを見てみると、わかりやすかったなどのポジティブな感想があったのでそれだけで発表してよかったなーと思う。 また発表できるように今後もMySQL8.0をおもちゃにして遊んでみよう。

発表のスライドはこちら。前半のMySQLの新機能解説も合わせてどうぞ。

イベントの告知

発表の中でも告知させてもらったけど、OSS Gateワークショップが大阪であるので、OSS開発に興味があるけど、どうしていいかわからない、という人がいれば参加してくれるとうれしい。サポーターが参加者に付いてサポートしてくれるので、ほとんどの人がはじめの一歩をうまく踏み出せている。

oss-gate.doorkeeper.jp

テクテクテックもテーマがマニアックですが、いかがでしょう。

spookies.connpass.com

spookies.connpass.com

11月21/22日に図ったかのようにMySQLPostgreSQLの大きいイベントが東京である。東京はこういうのがあるからやっぱりうらやましいよね。どっちも行きたいけど。。。

勉強会の会場を提供しているInsight Technologyさん主催の三木会も面白そうなので参加してみたい。

[2018/10/18(木): 三木会@大阪] 『Pythonから使える列指向ファイルフォーマット・Parquetを使おう』『2018年データベースの選択は? SSDは、Oracle / SQLServer / MySQL / PostgreSQLのパフォーマンスを救えるのか??』 | db tech showcase

他の発表のスライド

他の発表のスライドも公開されているので一緒に載せておく。

他のブログ

タイムラインでいくつか見つけたのでそれも一緒に載せておく。

run-around.hatenablog.com

radiocat.hatenablog.com

odashinsuke.hatenablog.com

カジュアル面談の失敗談

Wantedlyのプロフィールを記入しておくと、スカウトメッセージが届くことがある。 会社によっては「転職予定はありません」という旨の返信をする場合もあるし、返信に困ってそのまま放置してしまうことも。 この場合はホント申し訳ない。ごめんなさい。

メッセージによっては不信感を抱くものもある。 あまりプロフィールが充実してないのに「弊社にぴったりだと思い」みたいなことが書いてあるメッセージは謎だ。

そんな中でカジュアル面談しましょう、となることがある。 基本的にオンラインでの面談をお願いしているが、いくつかの面談を通して感じたことをまとめた。

注)ソフトウェアエンジニア界隈のはなしです。

こっちから話したいことはあんまりないですよ

先方は基本的に会社の紹介や事業の紹介をしてくれるが、 こっちは「スカウト」という名目でメッセージをもらって面談に対応しているので、 「何か聞きたいことあるのかな」というスタンスなのであんまり話すことがない。

なので「なにか質問ありますか?」みたいに言われても「別に・・・」となってしまう。 事前にどういう話をしたいか、みたいなことを教えて欲しい。 アジェンダとかアンケートがあればなお良い。

プロフィールほんとに見ているか疑問

プロフィールには「紹介文」や「このさきやってみたいこと」を書いているし、Tiwtte/Blog/GitHubのリンクもある。スカウトしてくるくらいだからその辺りもチェックされているのかな、と思いきやそうでもない。 せっかく時間を作っているのだから有意義な時間にしたいとは思うものの、はじめに不信感や疑念を持ってしまうとその後を良い時間にするのは困難だ。

京都に住んでいるのに、東京の企業が「コーヒー飲みに来ませんか?」ってどういうつもりなんだろう。

遊びに来ませんか、ってなに?

遊びに来ませんか?って言われても困る。 分かってるけど、遊びたいわけじゃないでしょ、っていう。

Hello hey | hey こんな感じにイベント化していると行きやすい。

もし、なんらかのスカウトでカジュアル面談をオファーするとしたら。

文句ばっかり言っててもしょうがないので、もし自分がエンジニアを採用したい企業側だったらこうしたほうがいいかな、というものも書いておく。

事前に目的を明確にする

カジュアル面談で何を話したいかを事前に明確にする。 会社を知ってもらうためなのか、プロフィールについて詳しく知りたいことがあるのかなど。 目的がはっきりしていないカジュアル面談はお互いにいいものにはならない。

事前にアジェンダのようなものを示しておく

事前に何を話すのかをお互いに共有すれば、それぞれ準備をすることができる。 スカウトされた側も話すことを事前に考えることができるし、準備ができていない場合は延期することも可能になる。

市場調査のひとつにする

これからの時代は、企業と従業員がより対等な関係になっていくだろう。 企業は必要な人材を明確にしつつ、その人材が企業に何を求めているかを知る必要がある。

  • エンジニアはどういう働き方をしたいか
  • 会社でどういう体験をしたいか
  • どういう人と働きたいか

などを直接ピックアップする。

相手がフリーランスだったら

社員として迎えたいのか、フリーランスのままでも仕事をしたいのか明確にする。 あと単価を聞きましょう。