oracle-pq-exporter 0.1.1

Parallel Oracle-to-Parquet exporter CLI using oracledb Thin mode (no Oracle Client required)

oracle-pq-exporter

A fast, parallel Oracle-to-Parquet exporter CLI — built to replace SQL*Plus spool for bulk data extraction.

한국어 문서는 소스 배포판의 README.ko.md를 참고하세요. (Korean documentation is available in README.ko.md, shipped in the source distribution.)

oracle-pq-exporter connects to Oracle using oracledb Thin mode (no Oracle Client installation required), splits a table across worker processes with ORA_HASH(ROWID, N), streams fetch batches through Polars/PyArrow into snappy-compressed Parquet files, and merges the parts back into a single file. It was battle-tested in a production Oracle → BigQuery migration.

Features

• No Oracle Client required — pure-Python oracledb Thin mode (UTF-8 by default, safe for CJK data)
• Parallel extraction — rows are split across processes via ORA_HASH(ROWID, N), then part files are merged with a streaming (memory-bounded) merge
• NUMBER precision preserved — columns that exceed Float64's ~15.95 significant digits are fetched as strings so values survive exactly (see Precision handling)
• Automatic retry on transient network errors — reconnect-level blips are retried (default: 3 attempts, 5s apart)
• Graceful shutdown — SIGINT/SIGTERM finishes the current batch, closes writers, and exits cleanly
• Two extraction modes — by partition (PARTITION (...) clause) or by date range (BETWEEN)
• Sensible output — schema-only empty Parquet for 0-row results, per-worker logs, fetch/convert/write timing breakdown

Installation

pip install oracle-pq-exporter

Requires Python 3.10+. No Oracle Client, no extra system packages.

Connection configuration

Credentials are resolved in the following order (no password on the command line, so nothing leaks into your shell history):

1. CLI arguments: --user, --dsn
2. Environment variables: OPQ_USER, OPQ_PASSWORD, OPQ_DSN
3. Config file: --config path/to/config.toml, or the default ~/.config/opq-export/config.toml
4. If no password is found anywhere, you are prompted interactively (getpass)

Example ~/.config/opq-export/config.toml:

[oracle]
user = "myuser"
dsn = "host:1521/SERVICE"
# Putting the password here is discouraged — prefer OPQ_PASSWORD or the interactive prompt.

[export]
batch_size = 50000
num_workers = 2
merge = "Y"
output_dir = "./output"
log_dir = "./logs"
max_retries = 3
retry_delay = 5

Usage

# Partition-based extraction
opq-export partition OWNER TABLE_NAME PARTITION_NAME -w 8 -m Y

# Whole-table extraction (no partition)
opq-export partition OWNER TABLE_NAME

# Date-range extraction
opq-export date OWNER TABLE_NAME TARGET_COL START_DATE END_DATE -w 4 -m N

# Example: credentials via environment variables
export OPQ_USER=myuser OPQ_PASSWORD=secret OPQ_DSN=host:1521/SERVICE
opq-export date SALES T_ORDER ORDER_DATE 20240101 20241231 -w 8

Main options:

Option	Description	Default
-w, --workers	Number of parallel workers (8–16 recommended)	2
-m, --merge	Merge part files after extraction (Y/N)	Y
--batch-size	Fetch batch size	50000
--output-dir	Parquet output directory	./output
--log-dir	Log directory	./logs
--max-retries / --retry-delay	Network retry count / delay (seconds)	3 / 5

Output file name: {OWNER}_{TABLE}[_{PARTITION}]_{yyyymmdd}.parquet (with -m N, per-worker ..._partNN.parquet files are kept instead).

NUMBER precision handling

Fetching Oracle NUMBER the default way materializes values as Float64, which silently corrupts anything beyond ~15.95 significant digits. For example, a NUMBER(19,4) value of 813224649805.2459 comes back as 813224649805.2458. The loss happens at fetch time, so no downstream layer (Polars, Arrow, Parquet) can recover it.

This tool fetches the following NUMBER columns as strings and stores them as strings in Parquet, preserving values exactly (logic validated against production data):

• scale > 9 (many decimal places)
• precision > 15 (beyond Float64 significant digits)
• scale < 0 (negative scale)

Other NUMBER columns map to Decimal, and DATE/TIMESTAMP columns map to Datetime(us, Asia/Seoul).

Python API

from oracle_parquet_exporter import DbConfig, ExportJob, ExportSettings, export_data

db = DbConfig(user="myuser", password="secret", dsn="host:1521/SERVICE")
job = ExportJob(mode="partition", owner="SALES", table_name="T_ORDER", partition_nm="P202401")
export_data(job, db, ExportSettings(num_workers=8, merge="Y"))

Notes

• Same-table workers share nothing and connect independently — size -w to what your DB host and network can absorb.

License

MIT