JSONLのデータベースへのインポートとエクスポート:PostgreSQL、MongoDBほか
JSONLファイルをデータベースにインポートする方法と、データベースのレコードをJSONL形式でエクスポートする方法を詳しく解説します。PostgreSQLのCOPYコマンド、MongoDBのCLIツール、MySQLのJSON_TABLE、SQLite用のスクリプトから、本番環境で使える一括インポートの実装パターンまで取り上げます。
データベースのインポートとエクスポートにJSONLを使う理由
JSONL(JSON Lines)は、データベースと外部システムの間でデータをやり取りする形式として広く使われています。各行がそれだけで完結した独立したJSONオブジェクトなので、データセット全体をメモリに読み込まずに、1レコードずつストリーム処理できます。PostgreSQLのインスタンス間で数百万行を移行する場合や、MongoDBのコレクションをデータウェアハウスに同期する場合、データベースからエクスポートしたデータを機械学習パイプラインに渡す場合には、この特性が欠かせません。
JSONLはCSVと異なり、データ型を保持でき、ネストしたオブジェクトや配列にも対応しています。区切り文字のエスケープに伴う曖昧さもありません。PostgreSQLのjsonbカラムの内容をそのままJSONLファイルに格納できますが、CSVではネストした構造をフラット化するか、エスケープする必要があります。また、JSON配列全体を扱う場合と異なり、JSONLはストリーム処理が可能です。ファイル全体のパースが終わるのを待たずに、レコードを読み込んだ時点で処理、検証、変換できます。
現在では、主要なデータベースはいずれもJSONLを扱うツールを備えています。PostgreSQLではjsonbへのキャストを介してCOPYで行を取り込めます。MongoDBにはJSONLをデフォルト形式とするmongoimportとmongoexportが付属しています。MySQL 8.0にはJSONから構造化データを抽出するJSON_TABLEが追加され、SQLiteにはJSON1拡張があります。以降のセクションでは、各データベースで使う具体的なコマンドやスクリプト、ベストプラクティスを紹介します。
PostgreSQL:JSONLのインポートとエクスポート
PostgreSQLでJSONLを扱う方法はいくつかあります。一括インポートにはCOPYコマンドが最も高速です。また、jsonb型を使えば、半構造化データをそのまま保存し、クエリを実行できます。型が定義されたカラムを持つテーブルには、インポート時にJSONLのフィールドをパースして格納できます。
JSONLをPostgreSQLに最も高速にインポートするには、COPYコマンドで各行をjsonb値として読み込みます。単一のjsonbカラムを持つテーブルを作成し、COPY FROMでファイルをデータベースに直接ストリームとして取り込みます。
-- Create a staging table with a single jsonb column
CREATE TABLE staging_import (data jsonb);
-- Import the JSONL file (each line becomes one row)
\COPY staging_import (data) FROM 'users.jsonl';
-- Verify the import
SELECT count(*) FROM staging_import;
SELECT data->>'name' AS name, data->>'email' AS email
FROM staging_import
LIMIT 5;
-- Move data into a structured table
INSERT INTO users (name, email, age)
SELECT
data->>'name',
data->>'email',
(data->>'age')::int
FROM staging_import;データをJSONL形式でエクスポートするには、row_to_json()またはto_jsonb()で各行をJSONオブジェクトに変換し、COPYで結果をファイルに書き出します。テーブルの各行が、出力ファイルの1行に対応します。
-- Export entire table as JSONL
\COPY (
SELECT row_to_json(t)
FROM (SELECT id, name, email, created_at FROM users) t
) TO 'users_export.jsonl';
-- Export with filtering and transformation
\COPY (
SELECT row_to_json(t)
FROM (
SELECT id, name, email,
created_at::text AS created_at
FROM users
WHERE active = true
ORDER BY id
) t
) TO 'active_users.jsonl';
-- Verify: each line is valid JSON
-- {"id":1,"name":"Alice","email":"[email protected]",...}本番環境では、psycopg2を使ったPythonスクリプトでJSONLファイルを読み込み、型が定義されたカラムにレコードを挿入します。この方法なら、検証、エラー処理、バッチサイズを細かく制御できます。
import json
import psycopg2
from psycopg2.extras import execute_values
def import_jsonl_to_postgres(file_path, conn_string):
conn = psycopg2.connect(conn_string)
cur = conn.cursor()
batch = []
batch_size = 1000
total = 0
with open(file_path, 'r') as f:
for line_num, line in enumerate(f, 1):
line = line.strip()
if not line:
continue
try:
record = json.loads(line)
batch.append((
record['name'],
record['email'],
record.get('age'),
))
except (json.JSONDecodeError, KeyError) as e:
print(f"Skipping line {line_num}: {e}")
continue
if len(batch) >= batch_size:
execute_values(
cur,
"INSERT INTO users (name, email, age) "
"VALUES %s ON CONFLICT (email) DO NOTHING",
batch
)
total += len(batch)
batch = []
print(f"Imported {total} records...")
if batch:
execute_values(
cur,
"INSERT INTO users (name, email, age) "
"VALUES %s ON CONFLICT (email) DO NOTHING",
batch
)
total += len(batch)
conn.commit()
cur.close()
conn.close()
print(f"Done. Imported {total} records.")
# Usage
import_jsonl_to_postgres('users.jsonl', 'postgresql://localhost/mydb')MongoDB:JSONLを標準でサポート
MongoDBは、コマンドラインのDatabase ToolsでJSONLを手厚くサポートしています。mongoimportとmongoexportはデフォルトでJSONL形式を使うため、MongoDBはJSONLでのデータ交換を特に手軽に行えるデータベースの1つです。JSONLファイルの各行が、そのままMongoDBのドキュメントに対応します。
mongoimportはJSONLファイルを読み込み、各行をドキュメントとして指定のコレクションに挿入します。upsertモード、フィールドの型変換、スループットを高めるための並列挿入に対応しています。
# Basic import: each line becomes a document
mongoimport \
--uri="mongodb://localhost:27017/mydb" \
--collection=users \
--file=users.jsonl
# Upsert mode: update existing documents by _id
mongoimport \
--uri="mongodb://localhost:27017/mydb" \
--collection=users \
--file=users.jsonl \
--mode=upsert \
--upsertFields=email
# Drop collection before import (clean slate)
mongoimport \
--uri="mongodb://localhost:27017/mydb" \
--collection=users \
--file=users.jsonl \
--drop
# Import with parallel workers for large files
mongoimport \
--uri="mongodb://localhost:27017/mydb" \
--collection=users \
--file=large_users.jsonl \
--numInsertionWorkers=4mongoexportは、コレクション内の各ドキュメントを一行のJSONとして書き出します。出力するドキュメントの絞り込み、フィールドの選択、並べ替えも可能です。出力結果は正しい形式のJSONLファイルなので、後続のどのシステムでもそのまま処理できます。
# Export entire collection as JSONL (default format)
mongoexport \
--uri="mongodb://localhost:27017/mydb" \
--collection=users \
--out=users_export.jsonl
# Export with query filter
mongoexport \
--uri="mongodb://localhost:27017/mydb" \
--collection=users \
--query='{"active": true}' \
--out=active_users.jsonl
# Export specific fields only
mongoexport \
--uri="mongodb://localhost:27017/mydb" \
--collection=users \
--fields=name,email,created_at \
--out=users_partial.jsonl
# Export with sorting
mongoexport \
--uri="mongodb://localhost:27017/mydb" \
--collection=users \
--sort='{"created_at": -1}' \
--out=users_sorted.jsonlMySQL:JSON_TABLEとスクリプトでJSONLを扱う
MySQLにはMongoDBのようなJSONL専用のインポートコマンドは組み込まれていませんが、MySQL 8.0で導入されたJSON_TABLEを使えば、JSON文字列から構造化データを抽出できます。JSONLを一括インポートするには、LOAD DATA INFILEでデータをそのまま読み込み、JSON_TABLEでフィールドを抽出する方法か、Pythonなどのスクリプト言語を使う方法が最も確実です。
JSONLファイルをそのままのテキストとしてステージングテーブルに読み込んでから、JSON_TABLEで各フィールドを抽出し、カラムが定義されたテーブルに格納します。この2段階の処理は、MySQL内だけで完結します。
-- Step 1: Create a staging table for raw lines
CREATE TABLE jsonl_staging (
id INT AUTO_INCREMENT PRIMARY KEY,
raw_line TEXT NOT NULL
);
-- Step 2: Load the JSONL file as raw text
LOAD DATA INFILE '/var/lib/mysql-files/users.jsonl'
INTO TABLE jsonl_staging
LINES TERMINATED BY '\n'
(raw_line);
-- Step 3: Extract structured data with JSON_TABLE
INSERT INTO users (name, email, age)
SELECT jt.name, jt.email, jt.age
FROM jsonl_staging,
JSON_TABLE(
raw_line, '$'
COLUMNS (
name VARCHAR(255) PATH '$.name',
email VARCHAR(255) PATH '$.email',
age INT PATH '$.age'
)
) AS jt;
-- Step 4: Clean up staging table
DROP TABLE jsonl_staging;
-- Verify the import
SELECT * FROM users LIMIT 5;より柔軟に処理したい場合は、mysql-connector-pythonを使ったPythonスクリプトでJSONLファイルを読み込み、レコードをバッチで挿入します。大きなファイルを効率よく処理でき、エラーの詳細も出力できます。
import json
import mysql.connector
def import_jsonl_to_mysql(file_path, config):
conn = mysql.connector.connect(**config)
cursor = conn.cursor()
batch = []
batch_size = 1000
total = 0
insert_sql = (
"INSERT INTO users (name, email, age) "
"VALUES (%s, %s, %s) "
"ON DUPLICATE KEY UPDATE name=VALUES(name)"
)
with open(file_path, 'r') as f:
for line_num, line in enumerate(f, 1):
line = line.strip()
if not line:
continue
try:
record = json.loads(line)
batch.append((
record['name'],
record['email'],
record.get('age'),
))
except (json.JSONDecodeError, KeyError) as e:
print(f"Skipping line {line_num}: {e}")
continue
if len(batch) >= batch_size:
cursor.executemany(insert_sql, batch)
conn.commit()
total += len(batch)
batch = []
print(f"Imported {total} records...")
if batch:
cursor.executemany(insert_sql, batch)
conn.commit()
total += len(batch)
cursor.close()
conn.close()
print(f"Done. Imported {total} records.")
# Usage
import_jsonl_to_mysql('users.jsonl', {
'host': 'localhost',
'user': 'root',
'password': 'secret',
'database': 'mydb'
})SQLite:軽量なJSONLインポート
SQLiteは、ローカルでの開発や小規模なデータセットの処理に適しています。JSONL専用のインポートコマンドはありませんが、JSON1拡張のjson_extract()でJSONデータを扱えます。また、SQLiteに組み込まれたトランザクション機能を使えば、シンプルなPythonスクリプトでJSONLファイルを効率よくインポートできます。
Python標準のsqlite3モジュールでJSONLファイルを読み込み、SQLiteデータベースにレコードを挿入します。すべての挿入を単一のトランザクションにまとめると、大きなファイルを処理する際のパフォーマンスが大幅に向上します。
import json
import sqlite3
def import_jsonl_to_sqlite(jsonl_path, db_path):
conn = sqlite3.connect(db_path)
cursor = conn.cursor()
# Create table if it doesn't exist
cursor.execute("""
CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
email TEXT UNIQUE,
age INTEGER,
raw_json TEXT
)
""")
total = 0
errors = 0
with open(jsonl_path, 'r') as f:
# Use a transaction for bulk insert performance
conn.execute('BEGIN TRANSACTION')
for line_num, line in enumerate(f, 1):
line = line.strip()
if not line:
continue
try:
record = json.loads(line)
cursor.execute(
"INSERT OR IGNORE INTO users "
"(name, email, age, raw_json) "
"VALUES (?, ?, ?, ?)",
(
record['name'],
record.get('email'),
record.get('age'),
line, # Store original JSON
)
)
total += 1
except (json.JSONDecodeError, KeyError) as e:
errors += 1
print(f"Line {line_num}: {e}")
conn.commit()
conn.close()
print(f"Imported {total} records, {errors} errors")
# Usage
import_jsonl_to_sqlite('users.jsonl', 'app.db')
# Query with json_extract (SQLite JSON1 extension)
# SELECT json_extract(raw_json, '$.name') FROM users;一括インポートのベストプラクティス
大きなJSONLファイルをデータベースにインポートする際は、トランザクション、エラー処理、リソース管理に十分な配慮が必要です。ここで紹介するベストプラクティスは、どのデータベースにも共通して適用でき、本番環境で安定して動作するインポートパイプラインの構築に役立ちます。
トランザクションをバッチ単位にまとめる
パフォーマンス1行ごとに1トランザクションを実行しないでください。1トランザクションあたり500〜5000行のバッチにまとめて挿入します。これにより、ディスクI/Oとロックのオーバーヘッドを10〜100分の一に抑えられます。多くのデータベースでは、バッチサイズを1000にしたときに最も大きな効果が得られます。それ以上大きくしても追加の効果は小さくなり、失敗時のロールバックの負担が増えます。
行単位でエラーを処理する
信頼性JSONLの各行を個別にパースし、検証します。検証や挿入に失敗した場合は、その行番号とエラーメッセージをログに記録します。ユニーク制約違反でインポート全体が中断しないよう、ON CONFLICT / ON DUPLICATE KEYで適切に処理してください。失敗した行は、後で確認できるように別のエラーファイルに保存します。
進捗を記録する
可観測性数百万行あるファイルでは、N件ごと(例:10,000件ごと)に進捗をログに記録します。読み込んだ総行数、挿入に成功した件数、重複のためスキップした件数、エラー件数を記録してください。インポート速度(レコード/秒)を計算して表示すると、パフォーマンスのボトルネックを特定できます。こうした情報は、長時間かかるインポートの監視に欠かせません。
この汎用パターンは、バッチ処理、エラー処理、進捗追跡を組み合わせています。insert関数を入れ替えることで、任意のデータベースに適応できます。
import json
import time
def batch_import_jsonl(file_path, insert_fn, batch_size=1000):
"""Generic JSONL batch importer with error handling."""
batch = []
stats = {
'total_lines': 0,
'imported': 0,
'skipped': 0,
'errors': 0
}
start_time = time.time()
error_file = open(file_path + '.errors', 'w')
with open(file_path, 'r') as f:
for line_num, line in enumerate(f, 1):
stats['total_lines'] = line_num
line = line.strip()
if not line:
stats['skipped'] += 1
continue
try:
record = json.loads(line)
batch.append(record)
except json.JSONDecodeError as e:
stats['errors'] += 1
error_file.write(f"{line_num}\t{e}\t{line}\n")
continue
if len(batch) >= batch_size:
inserted = insert_fn(batch)
stats['imported'] += inserted
batch = []
if line_num % 10000 == 0:
elapsed = time.time() - start_time
rate = stats['imported'] / elapsed
print(
f"Progress: {line_num:,} lines | "
f"{stats['imported']:,} imported | "
f"{rate:,.0f} records/sec"
)
# Flush remaining batch
if batch:
inserted = insert_fn(batch)
stats['imported'] += inserted
error_file.close()
elapsed = time.time() - start_time
print(f"\nComplete in {elapsed:.1f}s")
print(f" Lines: {stats['total_lines']:,}")
print(f" Imported: {stats['imported']:,}")
print(f" Errors: {stats['errors']:,}")
return stats無料のJSONLツールでデータを準備する
JSONLをデータベースにインポートする前に、ファイル形式を検証し、必要に応じて形式を変換しましょう。当サイトの無料オンラインツールはすべての処理をブラウザ内で行うため、データが端末の外に送信されることはありません。
JSONL データを表計算用の CSV ファイルにエクスポートできます。Excel や Google Sheets での分析に便利です。
JSONL ファイルを 1 行ずつ検証します。構文エラーや文字エンコーディング、形式の問題をすぐに検出できます。
JSON Schemaを使用してJSONLファイルを検証する方法を学びます。Python、Node.js、CLIツール、CI/CD自動化をカバー。
ギガバイト規模の JSONL データを処理する方法を紹介します。ストリーミングでの読み込み、メモリ管理、ファイル分割、圧縮、オンラインツールを取り上げます。
ETL パイプラインで JSONL をデータ交換形式として使う方法を解説します。Apache Kafka によるストリーミング、Airflow の DAG、Snowflake と BigQuery への読み込み、耐障害性のあるパイプラインの設計を取り上げます。