この記事では、CSVで公開されているオープンデータをSQLで取得・検索・分析できるかどうかを試してみた記録を紹介します。オープンデータを利用したアプリケーションの開発などに興味がある方は、ぜひ参考にしてください。また、TiDBアドベントカレンダーの25日目としても公開しました。

TiDBはCSVからテーブルが作れる

ダッシュボードを開いてみて気づいたのですが、TiDBでは[Import your own data]を利用してローカル環境にあるファイルからテーブルが作成できます。この場合、アップロードしたファイルの内容に基づいてテーブルのスキーマが用意される様子です。ちなみにAmazon S3に配置したファイルやCLIを利用したインポートも可能な様子です。

50MiBまでのCSVファイルをローカルからアップロードできます。逆にSQLのdumpファイルなどは使えない様子で、その辺りはS3やCLIなどで実行する形になるのかもしれません。

ダウンロードしたCSVファイルからテーブルを作る

今回は京都府のオープンデータサイトから、CSVファイルをダウンロードしてみました。ファイルの選定は好みで良いと思いますが、いざという時のため、Excel以外のエディタでCSVを開ける準備だけはしておきましょう。

ダウンロードしたCSVファイルを早速TiDBへアップロードします。インポート先のデータベースとテーブルを指定する必要がありますが、テーブルがない場合はその場で作成できるようになっています。

ファイルのアップロードが完了すると、CSVに基づいてスキーマが表示されます。ここでカラム名に利用できないものが混ざっていないかや、どのデータをプライマリー扱いするかなどを確認しておきましょう。もし警告メッセージが表示されているカラム名が見つかった場合は、その場で変更すればOKの様子です。ただし2回目以降のインポートにて同じ作業が必要となるため、見つかった場合は、カラム名の変更作業を行うバッチスクリプトなどを作っておく方がよいかもしれません。

オープンデータの場合、特にカラム名は日本語となりやすいです。そのため、カラム名のバリデーションには引っかかりやすいと思っておきましょう。安全策を取る場合は、英語のカラム名に置換しておく方が良さそうです。

調整が終わったらインポートを実行します。これでインポートが完了すれば、取り込み作業は完了です。

CSVデータにイレギュラーがあると、インポートに失敗する

ただしインポートに失敗することがあります。この辺りはCSVファイルがTiDBの想定していないデータを持っている可能性などが考えられますので、CSVファイル側を調べていくことになります。

ざっとみた感じですこし気になるデータがいくつかありましたので、今回は作業完了を優先して「明確に大丈夫そうな行を3−5行のみピックアップ」することでアップロードを成功させました。

インポートしたデータにはSQLでクエリできる

TiDBダッシュボードのSQL Editorに移動すると、取り込んだデータが確認できます。スキーマやサンプルのクエリもあるのが親切ですね。

試しにデフォルトのクエリを実行してみましょう。するとCSVから取り込んだ京都に関するデータが表示されます。

もちろんこのデータはSQLによる絞り込みなどが可能です。例えばWHERE句を利用して郵便番号が一致する施設だけを検索してみましょう。横着してカラム名を日本語にしたため、エディタ上でwarningがでていますが、今回は気にしないこととします。

USE test;
SELECT * from test.test_kyoto_kids_opendata where 郵便番号 = "618-0071";

実行すると、無事該当する郵便番号の施設が表示されました。

このようにCSVで公開されているデータをSQLで扱うための仕組みとして、TiDBを使うことができそうです。

結論

CSVデータで公開されているオープンデータの分析にTiDBは使えそうです。CSVファイルをダウンロードし、TiDBへインポートすることでSQLを使った分析が可能でした。これを活用して、行政データの分析や、オープンデータを利用したアプリケーションの開発などにTiDBを利用する事例などが今後でてくるかもしれません。

ただし注意が必要だなと思ったことは、CSVデータが必ず完全な形でTiDBへインポートできるとは限らない点です。これはオープンデータに関する議論でも定期的に出てくるデータの質に関する問題で、遅くともインポートに失敗した場合にはCSVファイルの中身を確認しておく必要がありそうです。もちろんExcel以外で。この点については、例えば生成AIを利用してCSVデータを作り直させたり、ある程度自治体の癖などが分かればバッチ処理化するなどしてのりこえらえるかも・・・しれません。