SQL・テストデータ
CSVやJSONのサンプルからINSERT文を作りたい
使うツール
テスト用の数行を用意したいだけなのに、列名と値の順番をそろえ、文字列へ引用符を付ける作業で時間を使うことがあります。JSON/CSV → INSERTは、その下書きを作る道具です。ただし、表の型や制約を知らないまま『このまま実行できる』と判断はしません。生成されたSQLが元のデータをどう表したかを読むところまでを、一つの作業として進めましょう。
二行のJSONで、列と値の対応を読む
表名をpeople、方言をPostgreSQLにし、次のJSONを入力します。idは数値、codeとnameは文字列、noteは一行目がnull、二行目が空文字です。生成結果では列がid、code、name、noteの順に並び、それぞれの値がその順番へ対応します。二行目のJSONでキーの並びが違っていても、値は列名に合わせて配置されます。
[{"id":1,"code":"001","name":"O'Reilly","note":null},{"name":"Mina","id":2,"code":"010","note":""}](1, '001', 'O''Reilly', NULL),
(2, '010', 'Mina', '')文字列内の一重引用符が二つへ重ねられること、001の先頭ゼロが残ること、NULLと''が別の表記になることを確認します。元のJSONで001を数値として書くのはJSON構文としても不正です。コードとしてゼロを残したいなら、最初から文字列であることを明示します。変換後に見た目を直すより、元データの型を正しく表す方が確実です。
列がない行は、DEFAULTにはならない
このツールは全行に登場するキーから列一覧を作り、その行にない列をNULLとして出力します。二行目からnoteを削除すると、空文字ではなくNULLになります。DBの既定値を使うDEFAULTを生成するわけではありません。列を指定しないINSERTと、列を指定してNULLを渡すINSERTは、実行先の既定値や制約によって結果が異なる場合があります。
同じ理由で、未設定、空文字、不明、ゼロを同じ値へまとめないようにします。年齢0と年齢不明、メモなしと空のメモなどは、利用目的によって別の状態です。変換前に二、三行の代表例を作り、それぞれの状態がどう出力されるかを確認します。大量の行を一度に生成してから違いを探すより、意味の取り違えを見つけやすくなります。
CSVの数字は、ここでは文字列として入る
同じ内容をCSVへ変えると、idの1や2も文字列として読まれます。このツールはCSVの値を見て数値型へ自動変換しません。先頭ゼロを守れる一方、数値列へ適切に入るかはDB側の型変換にも関係します。CSVにNULLと書いただけなら、それも文字列'NULL'です。JSONのnullと同じ意味だと決め付けないでください。
id,code,note
1,001,NULL
2,010,""CSVの引用符は区切り文字や改行を含む値を表すためのもので、SQLの引用符とは別の層です。カンマを含む値を一列として読み取れたか、見出しの列名が正しいかを先にCSV確認・編集で見られます。文字コードの問題で既に文字が壊れていれば、INSERT化では元に戻せません。変換の入口で、正しい文字と列に分かれていることを確認します。
入れ子や大整数は、同じ方法で扱えない
JSONの数値は精度を保持して読み取るため、18446744073709551615のような値も桁を変えずSQLへ出します。ただし、その値が実DBの列型に収まるかは別問題です。一方、配列やオブジェクトを一つの値として入れる形式には対応しません。勝手にJSON文字列へ変換したり、関連テーブルへ分解したりせずエラーになります。DBのJSON型へ入れたい場合も、ここで自動的に正しい式が作られるとは考えません。
行数は1,000、列数は256、行と列の積は10,000セルまでという上限があります。上限を超えたら一部を黙って切り捨てて完成したSQLとして扱うのではなく、入力範囲を見直します。テーブル名や列名にも対応文字の制限があり、任意の既存スキーマを完全に再現する機能ではありません。生成前に、対象の列と型、既定値、必須条件を確認しておきます。
生成は下書き、実行は別の工程
出力には接続先情報がなく、ツール自身がINSERTを実行することもありません。実行するなら、対象DB、環境、既存データとの重複、権限、制約を別に確認します。自動採番列へ値を入れてよいか、同じ主キーが既にあるか、外部キー先が存在するかなどは、この画面の成功表示から分かりません。実行環境の手順に従ってレビューしてください。
小さな架空データなら、入力の各セルと出力の各値を目で対応させられます。まず二行でNULL、空文字、引用符、先頭ゼロを確認し、その後で必要な件数へ増やします。実在の個人情報をテストへコピーする代わりに、ダミーデータ生成を使う方法もあります。変換が速いほど、実行前に意味を確かめる時間を残すことが大切です。
生成した列と、実際のテーブル定義を照合する
JSONにid、name、noteがあるからといって、対象テーブルがその三列をそのまま受け付けるとは限りません。idが自動生成列なら明示入力を許さない場合があり、noteがNOT NULLなら生成したNULLが拒否される場合もあります。文字列の最大長、外部キー、一意制約もこの生成では確認していません。エラーを避けるために全部を文字列へ変えるのではなく、元の値の意味とテーブルの型を照合します。生成されたSQLが整って見えることと、DBへ受け入れられることは別です。
同じSQLを二回実行した場合の結果も、このツールは決めません。一意なキーがあれば二回目が失敗するかもしれませんし、なければ同じ行が増えるかもしれません。失敗した途中から再実行する手順まで考えず、生成した文章を繰り返し貼り付けないようにします。トランザクション、既存データの照合、重複時の扱いは実行先での別の設計です。このツールはINSERT文の下書きを作るだけで、冪等なインポート処理や移行手順を自動生成しているわけではありません。
確認用の小さなデータには、普通の文字列だけでなく引用符、空文字、null、先頭ゼロを含めます。生成前の値とSQL上のリテラルを見比べ、どこで文字列になり、どこでNULLになったかを説明できる状態にします。大量の行を先に作るより、二、三行で受け渡し規則を確かめる方が違いを見つけやすくなります。実行環境でのテストが必要なら、承認された隔離データへ限定し、実運用のテーブルへ生成結果を試し打ちしないことが前提です。