2019年10月2日水曜日

SQLite3 TSVデータを取り込む、TSVデータを出力する

概要

テキストデータであるTSVファイルをSQLite3に入力/出力させる方法。
コマンドラインツールから利用する。
自作のスクリプトでテキストを読込/出力、DBに接続してinsert/selectをやるよりはるかに簡単。
取り込む処理はインポート・バルクロード、出力する処理をエクスポートと言ったりする。

TSVのフォーマットについて

IANAによってMIMEタイプ tsv/tab-separated-values として定義されている。
http://www.iana.org/assignments/media-types/text/tab-separated-values

  • フィールド(列)はTABで区切る
  • レコード(行)は改行で区切る
  • 各行のTABの数は同じでなければならない
といったところ。

構文

TSVファイルのインポート

.mode tab
.import file_path table_name

もしくは


.mode tab
.import file_path
TSVファイルのエクスポート

.mode tab
.once file_path
select...

TSVファイルを読み込む

インポート先として指定した名前のテーブルが存在しているかどうかで挙動が異なる。

既にテーブルがある
  • TSVの一行目もデータとして登録される。
    一行目が不要なら、あらかじめ削除しておく必要がある。
  • テーブルのカラム数・データ型にTSVも合わせる必要がある。
    合っていない場合、エラーになる。
テーブルがない
  • 指定したテーブル名で新しくテーブルが作られる。
  • TSVの一行目がカラム名になり、二行目以降がデータとして挿入される。
  • カラムのデータ型は全てTEXT型になる。
環境
  • Windows 10 64bit
  • SQLite3 ver.3.29.0
実行例

sqlite> -- # 1.
sqlite> .mode tabs
sqlite> -- # 2.
sqlite> .import sample.tsv sample_table
  1. tabs(タブ区切り)モードに切り替え
  2. sample.tsv ファイルを sample_tableに挿入

TSVファイルを書き出す

  • エスケープ処理等はないので、データにTABが含まれていないかあらかじめ確認しておく。
  • 出力先に同名のファイルが存在していても、上書き確認はしてくれない。
実行例

sqlite> -- # 1.
sqlite> .mode tabs
sqlite> -- # 2.
sqlite> .once result.tsv
sqlite> -- # 3.
sqlite> select * from sample_table;
  1. tabs(タブ区切り)モードに切り替え
  2. 「.once」 コマンドで次のコマンドの結果をresult.tsvに出力
  3. 出力したい内容のコマンドを実行

参考URL

Posted by at
Labels: ,