Metadata-Version: 2.4
Name: oracle-pq-exporter
Version: 0.1.1
Summary: Parallel Oracle-to-Parquet exporter CLI using oracledb Thin mode (no Oracle Client required)
Author-email: sangho <bboradoli@gmail.com>
License: MIT
License-File: LICENSE
Keywords: etl,export,oracle,oracledb,parallel,parquet,polars
Classifier: Development Status :: 4 - Beta
Classifier: Environment :: Console
Classifier: Intended Audience :: Developers
Classifier: License :: OSI Approved :: MIT License
Classifier: Programming Language :: Python :: 3
Classifier: Programming Language :: Python :: 3.10
Classifier: Programming Language :: Python :: 3.11
Classifier: Programming Language :: Python :: 3.12
Classifier: Topic :: Database
Classifier: Topic :: Software Development :: Libraries :: Python Modules
Requires-Python: >=3.10
Requires-Dist: oracledb>=2.0
Requires-Dist: polars>=1.0
Requires-Dist: pyarrow>=15.0
Requires-Dist: tomli>=2.0; python_version < '3.11'
Description-Content-Type: text/markdown

# 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](#number-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

```bash
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`:

```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

```bash
# 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

```python
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
