Metadata-Version: 2.4
Name: ping-dataexport
Version: 0.9.0
Summary: PING's Data Export Tool - To Parquet/CSV/TEXT as Accelerator for Data & AI Project
Author: Vorapol Ping (ping-godhand)
Maintainer: Godhand.DEV
License-Expression: Apache-2.0
Project-URL: Homepage, https://github.com/ping-godhand/python-ping-dataexport
Project-URL: Source, https://github.com/ping-godhand/python-ping-dataexport
Project-URL: Issues, https://github.com/ping-godhand/python-ping-dataexport/issues
Project-URL: Organization, https://godhand.dev
Keywords: export,database,csv,parquet,etl,data-platform,airflow,sql-server,oracle,postgresql,mysql,mariadb,sqlite
Classifier: Development Status :: 4 - Beta
Classifier: Environment :: Console
Classifier: Intended Audience :: Developers
Classifier: Operating System :: OS Independent
Classifier: Programming Language :: Python :: 3
Classifier: Programming Language :: Python :: 3.10
Classifier: Programming Language :: Python :: 3.11
Classifier: Programming Language :: Python :: 3.12
Classifier: Programming Language :: Python :: 3.13
Classifier: Topic :: Database
Classifier: Topic :: Software Development :: Libraries :: Python Modules
Requires-Python: >=3.10
Description-Content-Type: text/markdown
License-File: LICENSE
Requires-Dist: pyarrow>=18
Provides-Extra: odbc
Requires-Dist: pyodbc>=5; extra == "odbc"
Provides-Extra: oracle
Requires-Dist: oracledb>=2; extra == "oracle"
Provides-Extra: postgresql
Requires-Dist: psycopg[binary]>=3.1; extra == "postgresql"
Provides-Extra: mysql
Requires-Dist: pymysql>=1.1; extra == "mysql"
Provides-Extra: drivers
Requires-Dist: pyodbc>=5; extra == "drivers"
Requires-Dist: oracledb>=2; extra == "drivers"
Requires-Dist: psycopg[binary]>=3.1; extra == "drivers"
Requires-Dist: pymysql>=1.1; extra == "drivers"
Provides-Extra: dev
Requires-Dist: pytest>=8; extra == "dev"
Requires-Dist: ruff>=0.6; extra == "dev"
Dynamic: license-file

# ping-dataexport

[![PyPI](https://img.shields.io/pypi/v/ping-dataexport.svg)](https://pypi.org/project/ping-dataexport/)
[![Python](https://img.shields.io/pypi/pyversions/ping-dataexport.svg)](https://pypi.org/project/ping-dataexport/)
[![License: Apache-2.0](https://img.shields.io/badge/license-Apache--2.0-blue.svg)](https://github.com/ping-godhand/python-ping-dataexport/blob/main/LICENSE)

**Fast, simple export from a database to cloud storage or local storage — very
fast — an accelerator for data analytics projects.**

Connect to a database, run a SQL query, stream the result to **Parquet** (or
CSV / CSV.gz / TXT) on local disk or **directly into `s3://` `gs://` `abfs://`**.
Correct data types in Parquet, `row_id` / `data_date` audit columns, newline and
meta-character stripping, and one pipeline behind three front doors: **CLI**,
**Airflow** (`PingDataExportOperator`), **Python import**. Batch, watermark
(incremental), split by column, split by date, split by row count or **file size**,
backdate and backfill patterns are built in. Secrets stay out of the config file
(`Password=${PG_PASSWORD}`); every run can leave a JSON summary for the scheduler.

> Provided as-is. No support, no warranty, no roadmap commitments, use at your own risk.

## คำอธิบาย (ภาษาไทย)

โปรแกรมช่วย Export ข้อมูลอย่างง่ายและเร็ว: เชื่อมต่อฐานข้อมูล รันคำสั่ง SQL
แล้ว Export ผลลัพธ์ออกเป็นไฟล์ **Parquet / CSV / TXT** ลงดิสก์หรือตรงเข้า
**S3 / Google Cloud Storage / Azure Data Lake** สร้างมาเพื่องาน Data Platform
โดยเฉพาะ — ส่งออกข้อมูลจากฐานข้อมูลเดิม ให้เป็นไฟล์ที่ Data Lake หรือ
Data Warehouse นำไปใช้ต่อได้ทันที

- **ความถูกต้องของชนิดข้อมูลมาก่อนเสมอ** — ข้อมูลไหลจาก cursor → writer
  ทีละ Row ด้วย Data Type ดั้งเดิม: `DECIMAL(p,s)` ลงใน Parquet เป็น
  `decimal128(p,s)` ไม่ผ่าน float, คอลัมน์ int ที่มี NULL ยังคงเป็น int,
  วันที่ยังคงเป็นวันที่ และไม่ใช้ pandas เด็ดขาด
- **ฐานข้อมูลที่รองรับ** — SQLite (Library มาตรฐาน ไม่ต้องติดตั้งอะไรเพิ่ม),
  SQL Server (MS ODBC / FreeTDS), Oracle (โหมด thin แบบ Native หรือ ODBC),
  PostgreSQL, MySQL / MariaDB และแหล่งข้อมูล ODBC ใดก็ได้
- **ใช้งานได้สามทาง** — CLI `ping-dataexport`, Library API (`ping_dataexport.api`)
  สำหรับ Python script และ Airflow task — pipeline เดียวกันทั้งหมด

## Why this tool

| | |
|---|---|
| **Correct types, first of all** | Rows stream cursor → writer with the driver's native Python types. The Parquet schema comes from `cursor.description`, never from guessing at the data. No pandas, no numpy, ever — they silently turn `int`-with-NULL into `float` and `Decimal` into binary floats. |
| **Fast** | Streaming `fetchmany` batches into typed Arrow `RecordBatch`es; server-side cursors for PostgreSQL and MySQL; `arraysize` tuned for Oracle and ODBC. Memory stays flat however large the table. |
| **Simple** | A data engineer operates it from `config.ini` + `job.ini`. One line (`ExportFolder=s3://lake/exports`) moves every job from disk to the lake. |
| **Three front doors, one pipeline** | The CLI, the Python API and the Airflow operator run the identical build → validate → resolve → execute path. Exceptions, not exit codes, in library mode; the result (rows, files, schema) lands in XCom or in a `--summary` JSON. |
| **Ingestion patterns** | Full dump, split by row count or by **file size** (`Mode=splitsize`, 128 MB Parquet parts), one file per distinct value, per day, per month, relative windows (`@@CURRENT_DATE@@-7@@`), and **watermark** incremental runs with state kept beside the output — in the bucket if that is where the output goes. |
| **Secrets from the environment** | `${VAR}` in any config value — `Password=${PG_PASSWORD}`, `AWSSecretKey=${AWS_SECRET}` — expanded when read; unset fails loud. Lake credentials are only read when a cloud destination is used. |
| **Zero extra cloud dependencies** | `s3://`, `gs://`, `abfs://` / `abfss://` are served by `pyarrow.fs` — the same PyArrow that writes the Parquet. No boto3, no SDKs, no fsspec. |

### What the lake sees

| Database column | Parquet type | Notes |
|-----------------|--------------|-------|
| `DECIMAL(18,4)`, Oracle `NUMBER(18,4)`, PG `numeric(18,4)` | `decimal128(18,4)` | exact — never through `float` |
| Oracle bare `NUMBER`, PG bare `numeric` / `SUM(...)` (no declared p, s) | `decimal128(38,10)` | exact or the job fails naming the column; `[Main] UnconstrainedNumber=string` keeps every digit as text |
| `INT` / `SMALLINT` with NULLs | `int32` / `int64`, nullable | stays an integer |
| `BIGINT`, Oracle `NUMBER(p,0)` | `int64` | |
| `DATE` | `date32` | |
| `DATETIME` / `TIMESTAMP` | `timestamp[us]` | |
| PG `timestamptz` | `timestamp[us, tz=UTC]` | the instant, converted to UTC — the file says so |
| Oracle `TIMESTAMP WITH TIME ZONE` | `timestamp[us]` | the stored wall-clock as oracledb returns it, by design |
| MySQL / MariaDB `FLOAT` | `double` | re-selected as `CAST(.. AS DOUBLE)` — the text protocol would print only 6 digits |
| `TIME` | `time64[us]` | |
| `BIT` / `BOOLEAN` | `bool` | |
| `VARBINARY` / `BLOB` / `bytea` | `binary` | |
| text of any kind (Thai, CJK, emoji included) | `string` (UTF-8) | strip rules applied when requested |

CSV / TXT render the same values losslessly (`Decimal` via `str()`, ISO dates,
hex for binary). **Every output is UTF-8** — Parquet, CSV, TXT and their gzip forms —
with no BOM and no code page: `สมชาย` and `กรุงเทพมหานคร` come back byte-for-byte from
every engine (the integration suite and the DB zoo assert it on Thai / English names). Every run logs the resolved schema — `EXPORT | SCHEMA |
id:BIGINT->int64, amount:DECIMAL(12,2)->decimal128(12, 2), …` — before the first
file exists, also under `--test`. The complete per-driver mapping — and the short,
public list of known precision edge cases — is in
[docs/DATA_TYPES.md](https://github.com/ping-godhand/python-ping-dataexport/blob/main/docs/DATA_TYPES.md).

### What it is not

No change-data-capture, no loading into the warehouse (it stops at the file or
the bucket), no scheduler (Airflow does that), no pandas.

## Install

Requires Python 3.10 or newer.

```bash
pip install ping-dataexport               # base (pyarrow) — sqlite works out of the box
pip install ping-dataexport[odbc]         # + pyodbc      (SQL Server, FreeTDS, any ODBC)
pip install ping-dataexport[oracle]       # + oracledb    (Oracle native, thin mode)
pip install ping-dataexport[postgresql]   # + psycopg     (PostgreSQL)
pip install ping-dataexport[mysql]        # + pymysql     (MySQL and MariaDB)
pip install ping-dataexport[drivers]      # + all of the above
```

## Quick start

```ini
; config.ini
[Main]
ExportFolder=./output
RowIDColumn=row_id
DataDateColumn=data_date

[PG01]
Type=postgresql
Host=pghost
Database=analytics
Username=app
Password=secret
```

```bash
ping-dataexport -s PG01 -q "SELECT * FROM sales" -o sales.parquet                       # one Parquet file
ping-dataexport -s PG01 -q "SELECT * FROM sales" -m groupby -col region -dir by_region -pf sales
ping-dataexport -s PG01 -q "SELECT * FROM sales" -o s3://lake/raw/sales.parquet         # straight to S3

pde -s PG01 -q "SELECT * FROM sales" -o sales.parquet     # pde = the short alias, same command
```

`pde` is installed beside `ping-dataexport` and is the identical entry point — every
example on this page works with either name (`python -m ping_dataexport` is the third).

```python
from ping_dataexport.api import run_export
result = run_export("PG01", "SELECT * FROM sales", output="sales.parquet")
print(result.rows, result.files)
```

The rest of this page is the reference: config keys (§1), CLI options (§2) and the
`--sidecar` schema file (§2.1), export modes (§3), job files (§4), the Python / Airflow
API (§5), and checking a finished Parquet file (§6).

## 1. Create a config.ini

One `[Main]` section for defaults, then one section per database source. The
section name is what `-s` / `Source=` refers to:

```ini
[Main]
; base for relative output paths (default: current directory)
ExportFolder=./output
; default: {ExportFolder}/logs — the log file is dataexport-YYYYMMDD.log
LogFolder=./logs
FetchSize=10000
ForceMakeDir=Yes
; optional audit columns
RowIDColumn=row_id
DataDateColumn=data_date

[SQLITE01]
Name=DEMO-SQLITE
Type=sqlite
Database=./demo.sqlite

[DB01]
Name=SQLSERVER-PRD
Type=mssql-odbc
Host=host
Port=1433
Database=DB
Username=user
Password=${DB01_PASSWORD}

[PG01]
Name=POSTGRES-PRD
Type=postgresql
ConnectionString=host=pghost port=5432 dbname=analytics sslmode=require
Username=app
Password=${PG01_PASSWORD}
```

A full sample with every `Type` in both connection forms ships as
`config.sample.ini` in the source distribution. Comments must be on their own
line: a `;` after a value is part of the value (connection strings contain `;`).

**Secrets stay out of the file: `${VAR}` reads an environment variable.**
`Password=${PG_PASSWORD}`, `Database=${DEMO_DB}`, `AWSSecretKey=${AWS_SECRET}` — any
value, in `config.ini` and `job.ini`, is expanded when it is read. An unset variable
fails the run naming the section and key (never a silent empty string); `$${VAR}`
writes a literal `${VAR}`; a bare `$` or `$NAME` is left alone. A `[Cloud]` reference
is only resolved when a cloud destination is actually used, so a local job never
trips over a lake credential that is not on this machine.

**Where the file is looked up.** `-cf` / `configfile=` point anywhere. When
you do not specify one, `config.ini` is searched in this order and the run
fails with a message naming both places if neither has it:

1. the current working directory (where you run the command from)
2. the directory of the running `.py` script (e.g. next to your `main.py`)

`job.ini` follows the same rule.

### 1.1 Main config keys

All optional; a missing `[Main]` yields the defaults. **Layering, for every key that
also exists as a job key / CLI option: CLI or job section > `[Main]` > the code-level
default in `__config__.py`.**

| Key | Type | Possible values / default |
|-----|------|---------------------------|
| `ExportFolder` | path or URI | base folder for relative output paths; default: current directory. Local (`./out`, `D:\export`, `/Volumes/data`, `file:///data/out`) **or a cloud URI** (`s3://bucket/prefix`, `gs://…`, `abfs://…`) — see [1.3](#13-cloud-destinations--cloud) |
| `TempFolder` | local path | reserved for a future release; **always local** |
| `LogFolder` | local path | folder for log files; default `{ExportFolder}/logs`. The log file inside it is **`dataexport-YYYYMMDD.log`** — one per day, every job appends. Relative = under `ExportFolder`. No `ExportFolder` and no `LogFolder` ⇒ console only. **Always local** — with a cloud `ExportFolder` set it explicitly or get console only |
| `LogFile` | local path | explicit log **file** (rarely needed) — wins over `LogFolder`; `-lf` on the CLI wins over both; **always local** |
| `FetchSize` | int > 0 | rows per batch; default `50000` |
| `PreviewRow` | int > 0 | rows shown in console/test preview; default `10` |
| `ForceMakeDir` | bool | `Yes`/`No`, `True`/`False`, `1`/`0`, `on`/`off`; default `Yes` |
| `RowIDColumn` | string | audit column name (running row number); absent = no column |
| `DataDateColumn` | string | audit column name (data extract timestamp); absent = no column |
| `UnconstrainedNumber` | `decimal` \| `string` \| `double` | (default from `__config__.DEFAULT_UNCONSTRAINED_NUMBER`) how a `DECIMAL` column **without a declared (p, s)** — Oracle bare `NUMBER`, PostgreSQL bare `numeric` and aggregates — lands in Parquet. Default **`decimal`** = `decimal128(38, 10)`, exact: a value with more than 10 fractional digits or more than 28 integer digits fails the job naming the column (never rounds). `string` keeps every digit as text; `double` is a binary float (lossy, the pre-0.7 behaviour) |
| `PartitionWorkers` | int > 0 | (default from `__config__.DEFAULT_PARTITION_WORKERS`) partitions of one job exported concurrently for `groupby` / `date` / `relativedate` / `monthbydate`, one DB connection per worker; default `4`. `-pw` / `PartitionWorkers=` in a job override it |
| `HiveLayout` | bool | default for `-hlo` / `HiveLayout=`: Hive-style partition directories (see section 3); default `No` |
| `Retry` | int ≥ 0 | (default from `__config__.DEFAULT_RETRY`) retries per connect and per partition on a **transient** error (dropped connection, timeout, busy/locked source, object-store 5xx — never on a bad password, a missing table or a syntax error); default `3` |
| `RetryBackoffSeconds` | number ≥ 0 | (default from `__config__.DEFAULT_RETRY_BACKOFF_SECONDS`) wait before the first retry, doubling each time (`60`, `120`, `240`); default `60` |
| `ParquetCompression` | `snappy` \| `zstd` \| `gzip` \| `brotli` \| `lz4` \| `none` | (default from `__config__.PARQUET_COMPRESSION`) the Parquet codec; default `snappy`. `zstd` is ~30 % smaller at similar speed. Never changes a column's type. `-pc` / `ParquetCompression=` in a job override it (`-gz` stays a CSV/TXT flag) |
| `ParquetRowGroupRows` | int > 0 | rows per Parquet row group; default: one row group per `FetchSize` rows. 64–128 MB row groups scan faster on object stores. `-prg` / `ParquetRowGroupRows=` in a job override it |

### 1.2 Database config keys — type / possible values

Every database section has `Name` + `Type`, then **one** way to say where the
database is:

- **(A) `ConnectionString`** — the driver's own string, passed through as-is.
  **`ConnectionString` overrides all**: when it is set, `Host`, `Port`,
  `Database`, `Service`, `SID`, `Driver` and `DSN` are ignored. Only
  `Username` / `Password` are merged in, and only when the string does not
  already carry them (`UID=`/`PWD=`, `user=`/`password=`).
- **(B) discrete keys** — `Host` / `Port` / `Database` (+ `Service` or `SID` for
  Oracle, `Driver` for ODBC); the connector builds the string for you.

| Key | Type | Possible values / notes |
|-----|------|--------------------------|
| `Type` | enum | `sqlite` \| `mssql-odbc` \| `freetds` \| `oracle-odbc` \| `odbc` \| `oracle` \| `postgresql` \| `mysql` \| `mariadb` — case-insensitive, aliases below. Omitted with `ConnectionString`/`DSN` present ⇒ `odbc` |
| `Name` | string | display name in logs; default: the section name |
| `ConnectionString` | string | **(A) overrides all** — form depends on `Type`, see *Per database* below |
| `DSN` | string | ODBC data source name (pyodbc Types only); like `ConnectionString`, wins over discrete keys |
| `Host` / `Port` | string / int | (B) server address; default port per Type: 1433 / 1521 / 5432 / 3306 |
| `Database` | string | (B) database name; for `sqlite`: the **file path** (opened read-only) |
| `Service` / `SID` | string | (B) Oracle service name or SID |
| `Driver` | string | (B) ODBC driver name override, e.g. `ODBC Driver 17 for SQL Server` |
| `Username` | string | login user |
| `Password` | string | plain-text password |
| `PasswordBase64` | string | base64-encoded password (obfuscation, not encryption); when both are set, `Password` wins |
| `Encoding` | string | text encoding: pyodbc `setdecoding` (e.g. `tis-620`), PostgreSQL `client_encoding`, MySQL `charset` (default `utf8mb4`) |

`Type` values and the aliases accepted for each (one canonical name per line):

| `Type` | Aliases | Database | Python package (pip) | OS-level |
|--------|---------|----------|----------------------|----------|
| `sqlite` | — | SQLite file | nothing (stdlib) | nothing |
| `mssql-odbc` | `mssql`, `sqlserver` | SQL Server | `pyodbc` | Microsoft ODBC Driver 17/18 |
| `freetds` | — | SQL Server / Sybase (legacy) | `pyodbc` | FreeTDS |
| `oracle-odbc` | — | Oracle via ODBC | `pyodbc` | Oracle ODBC driver |
| `odbc` | — | anything with an ODBC driver | `pyodbc` | that driver |
| `oracle` | — | Oracle native (thin mode) | `oracledb` | nothing |
| `postgresql` | `postgres`, `pgsql`, `pg` | PostgreSQL | `psycopg[binary]` | nothing |
| `mysql` | — | MySQL | `pymysql` | nothing |
| `mariadb` | `maria` | MariaDB | `pymysql` | nothing (Connector/C **not** needed) |

#### Per database — both forms

Each block shows **(A)** `ConnectionString` (overrides all) and **(B)** discrete
keys. `Username`/`Password` (or `PasswordBase64`) go in either form.

**SQLite** — `Type=sqlite`. `Database=` is the file path, opened read-only.

```ini
[SQLITE01]
Type=sqlite
; (B)
Database=./demo.sqlite
; (A) sqlite URI
;ConnectionString=file:/data/demo.sqlite?mode=ro
```

**SQL Server** — `Type=mssql-odbc` (`mssql`, `sqlserver`). `Driver=` optional,
default `ODBC Driver 18 for SQL Server`; default port 1433.

```ini
[MSSQL01]
Type=mssql-odbc
; (B)
Host=sqlhost
Port=1433
Database=DB
;Driver=ODBC Driver 17 for SQL Server
; (A)
;ConnectionString=Driver={ODBC Driver 18 for SQL Server};Server=sqlhost,1433;Database=DB;Encrypt=no
Username=sa
Password=secret
```

**SQL Server / Sybase via FreeTDS** — `Type=freetds`. For legacy servers
(SQL 2000/2005). `Driver=` default `FreeTDS`; `Encoding=` for old codepages.

```ini
[TDS01]
Type=freetds
; (B)
Host=legacyhost
Port=1433
Database=DB
; (A) DSN from odbc.ini
;ConnectionString=DSN=legacy2000;TDS_Version=7.2
Username=sa
Password=secret
;Encoding=tis-620
```

**Oracle via ODBC** — `Type=oracle-odbc`. `Driver=` is **required** in (B).

```ini
[ORAODBC01]
Type=oracle-odbc
; (B)
Driver=Oracle in OraClient19Home1
Host=orahost
Port=1521
Service=ORCLPDB
; (A)
;ConnectionString=Driver={Oracle in OraClient19Home1};DBQ=orahost:1521/ORCLPDB
Username=scott
Password=tiger
```

**Any ODBC source** — `Type=odbc` (also the default when `Type` is omitted but
`ConnectionString`/`DSN` is present). No (B) form: give `DSN=` or (A).

```ini
[ODBC01]
Type=odbc
; a DSN from odbc.ini ...
DSN=my_dsn
; ... or (A)
;ConnectionString=Driver={PostgreSQL Unicode};Server=host;Port=5432;Database=DB
Username=user
Password=secret
```

**Oracle native** — `Type=oracle`. Thin mode, no Oracle client install.
(B) needs `Service` or `SID`; default port 1521. (A) is an Easy Connect string
or a full TNS descriptor. `NUMBER` columns arrive as `int` / `Decimal`, never
`float`.

```ini
[ORA01]
Type=oracle
; (B)
Host=orahost
Port=1521
Service=ORCLPDB
;SID=ORCL
; (A) Easy Connect
;ConnectionString=orahost:1521/ORCLPDB
;ConnectionString=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=orahost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCLPDB)))
Username=scott
Password=tiger
```

**PostgreSQL** — `Type=postgresql` (`postgres`, `pgsql`, `pg`). Default port
5432; `Encoding=` → `client_encoding`. (A) is a libpq conninfo or URI. Rows
stream through a server-side cursor; `json`/`jsonb` export as text.

```ini
[PG01]
Type=postgresql
; (B)
Host=pghost
Port=5432
Database=analytics
; (A) conninfo
;ConnectionString=host=pghost port=5432 dbname=analytics sslmode=require
; (A) URI
;ConnectionString=postgresql://app:secret@pghost:5432/analytics
Username=app
Password=secret
```

**MySQL** — `Type=mysql`. Default port 3306; `Encoding=` → `charset` (default
`utf8mb4`). (A) is `key=value;key=value` handed to `pymysql.connect()`
(`host, port, user, password, database, charset, unix_socket, connect_timeout, …`).
Rows stream through an unbuffered cursor.

```ini
[MY01]
Type=mysql
; (B)
Host=myhost
Port=3306
Database=shop
; (A)
;ConnectionString=host=myhost;port=3306;database=shop;charset=utf8mb4
Username=app
Password=secret
```

**MariaDB** — `Type=mariadb` (`maria`). Same driver and keys as MySQL
(MariaDB speaks the MySQL protocol; the MariaDB Connector/C is not needed).
The separate `Type` keeps configs readable.

```ini
[MARIA01]
Type=mariadb
; (B)
Host=mariahost
Port=3306
Database=shop
; (A)
;ConnectionString=host=mariahost;port=3306;database=shop
Username=app
Password=secret
```

```ini
PasswordBase64=c2VjcmV0
; = base64("secret"); Password= wins when both are present
```

### 1.3 Cloud destinations — `[Cloud]`

Any output — `-o` / `-dir`, `Output=` / `OutputDir=`, or `ExportFolder`
itself — may point at an object store instead of a disk. The files are written
**directly** through `pyarrow.fs` (no staging copy, no extra package):

```
s3://bucket/prefix                                 Amazon S3 (MinIO etc. via AWSEndpoint)
gs://bucket/prefix                                 Google Cloud Storage
abfs://container@account.dfs.core.windows.net/p    Azure Data Lake Gen2 / Blob (abfss:// same)
abfs://container/prefix                            Azure, account from AzureAccountName
file:///data/export                                local disk spelled as a URI
```

```bash
ping-dataexport -s ORA01 -q "SELECT * FROM sales" -o s3://lake/raw/sales.parquet
ping-dataexport -s ORA01 -q "SELECT * FROM sales" -m groupby -col region -dir gs://lake/by_region -pf sales
```

Set `ExportFolder=s3://lake/exports` and every relative `Output=`/`OutputDir=`
in your job files lands in the bucket — the same job.ini works on disk and in
the cloud. Watermark state is kept beside the output, so an incremental job
into a cloud folder resumes from the bucket. `LogFolder`, `LogFile` and
`TempFolder` stay on the machine that runs the export.

Credentials — every key optional; the first level with a value wins (explicit
beats ambient). `PreferredAuthenticationType = Config | ENV | OS` pins one level
instead. Azure: `AzureAccountKey` wins over the service principal when both are set.

| Level | AWS | Azure | Google |
|-------|-----|-------|--------|
| `[Cloud]` | `AWSAccessKey` + `AWSSecretKey` (+ `AWSSessionToken`, `AWSRegion`, `AWSEndpoint`) | `AzureAccountKey`, or `AzureTenantID` + `AzureClientID` + `AzureClientSecret`; `AzureAccountName` | `GoogleCredentialJSONPath` |
| environment | `AWS_ACCESS_KEY_ID` / `AWS_ACCESS_KEY`, `AWS_SECRET_ACCESS_KEY` / `AWS_SECRET_KEY`, `AWS_REGION` | `AZURE_STORAGE_ACCOUNT_KEY`, or `AZURE_TENANT_ID`, `AZURE_CLIENT_ID`, `AZURE_CLIENT_SECRET`; `AZURE_STORAGE_ACCOUNT_NAME` | `GOOGLE_APPLICATION_CREDENTIALS` |
| the provider's own chain | `~/.aws`, EC2/ECS instance role | managed identity, `az login` | `gcloud auth application-default login`, GCE/GKE metadata |

```ini
[Cloud]
PreferredAuthenticationType=
GoogleCredentialJSONPath=
AzureAccountName=
AzureTenantID=
AzureClientID=
AzureClientSecret=
AWSAccessKey=
AWSSecretKey=
```

Needs a pyarrow build that includes the provider's filesystem: the PyPI wheel
has S3, GCS and Azure; conda's pyarrow lacks GCS and Azure. Full rules, log
lines and the library `cloud=` argument:
[docs/FILESYSTEM.md](https://github.com/ping-godhand/python-ping-dataexport/blob/main/docs/FILESYSTEM.md).

## 2. Run an export (CLI)

`ping-dataexport` and `python -m ping_dataexport` are equivalent. The config
file defaults to `./config.ini` (`-cf` to point elsewhere).

```bash
# preview to the console (no -o/-dir): prints the first rows
ping-dataexport -s SQLITE01 -q "SELECT * FROM sales"

# single file — format inferred from the extension (.csv/.txt/.parquet)
ping-dataexport -s SQLITE01 -q "SELECT * FROM sales" -o sales.parquet
ping-dataexport -s SQLITE01 -q "SELECT * FROM sales" -o sales.csv -gz   # gzip

# validate + preview only, write nothing
ping-dataexport -s SQLITE01 -q "SELECT * FROM sales" -o sales.csv -t
```

All options. The short form is the job.ini key's initials (`-df` = `DateFrom`),
the long form the key in lowercase; both are stable:

| Option | Full name | Meaning |
|--------|-----------|---------|
| `-s` | `--source` | DB config section name (e.g. `DB01`) |
| `-q` | `--query` | SQL query |
| `-o` | `--output` | single output file (format from extension, or `-f`) |
| `-dir` | `--outputdir` | output directory (required for `-m` modes); `-od` still works as a legacy alias |
| `-pf` | `--prefix` | output filename prefix for mode exports; `-bn`/`--basename` still work as legacy aliases |
| `-f` | `--format` | `csv`, `txt`, `parquet` |
| `-gz` | `--gzip` | gzip the output (CSV/TXT only) |
| `-sep` | `--sep` | field separator (default `,`; `\t` maps to tab) |
| `-quo` | `--quote` | quote character (default `"`) |
| `-stnl` | `--stripnewline` | newline handling in text columns: `space` \| `blank` \| `escape` \| `doubleescape` |
| `-stmc` | `--stripmetachar` | strip invisible/control characters from text columns |
| `-fs` | `--fetchsize` | fetch size (rows per batch) |
| `-m` | `--mode` | export mode: `groupby` \| `date` \| `relativedate` \| `monthbydate` \| `splitrow` \| `splitsize` \| `watermark` — see section 3 |
| `-col` | `--column` | column the mode partitions on |
| `-row` | `--row` | rows per file (`-m splitrow`) |
| `-smb` | `--splitsizemb` | MB per file (`-m splitsize`); default `128`; a fraction (`0.01` = 10 KB) is accepted, which is how you test it on a small table |
| `-df` | `--datefrom` | start / anchor date `yyyy-MM-dd` or `@@CURRENT_DATE@@[±N@@]` (see *Date tokens*); omitted with `-dr`/`-mr` ⇒ today (system date) |
| `-dt` | `--dateto` | end date `yyyy-MM-dd` or `@@CURRENT_DATE@@[±N@@]` (`-m date`) |
| `-dr` | `--daysrelative` | days back from `-df` (`-m relativedate`) |
| `-mr` | `--monthsrelative` | months back from `-df` (`-m monthbydate`) |
| `-rm` | `--removeexisting` | clear the output directory before exporting; `-rm` alone means Yes (`-rm No` is accepted) |
| `-pw` | `--partitionworkers` | partitions of this job exported concurrently, one DB connection each (`groupby` / `date` / `relativedate` / `monthbydate`); default `[Main] PartitionWorkers` = 4 |
| `-hlo` | `--hivelayout` | Hive-style partition directories under the output directory: `year=YYYY/month=MM/day=DD/` (`date` / `relativedate`), `year=YYYY/month=MM/` (`monthbydate`), `column=value/` (`groupby`) — see section 3 |
| `-rs` | `--resume` | rerun after a failure: skip every partition whose output file already exists (`groupby` / `date` / `relativedate` / `monthbydate`; not with `-rm`) |
| `-rt` | `--retry` | retries per connect / partition on a transient error (default `[Main] Retry` = 3) |
| `-rbs` | `--retrybackoffseconds` | wait before the first retry, doubling each time (default `[Main] RetryBackoffSeconds` = 60) |
| `-un` | `--unconstrainednumber` | `decimal` \| `string` \| `double` — Parquet type of a `DECIMAL` with no declared (p, s) (default `[Main] UnconstrainedNumber` = `decimal`) |
| `-pc` | `--parquetcompression` | `snappy` \| `zstd` \| `gzip` \| `brotli` \| `lz4` \| `none` — Parquet codec (default `[Main] ParquetCompression` = `snappy`) |
| `-prg` | `--parquetrowgrouprows` | rows per Parquet row group (default `[Main] ParquetRowGroupRows`, else `FetchSize`) |
| `-sc` | `--sidecar` | write `<file>.schema.json` beside every csv/txt file: each column's type for BigQuery, Snowflake, Spark (Databricks / Fabric) and T-SQL, plus the file's delimiter, quoting and NULL marker — see section 2.1 |
| `-j` | `--job` | run one job.ini section — see section 4 |
| `-jf` | `--jobfile` | job file path (default `./job.ini`) |
| `-p` | `--parallel` | run ALL job sections in parallel with N workers (default: CPU count); `-pl` still works as a legacy alias |
| `-n` | `--name` | job name shown in the log's jobname column |
| `-cf` | `--configfile` | config file path (default `config.ini`: current dir, then the script's dir) |
| `-lf` | `--logfile` | log file path (overrides `LogFolder`/`LogFile`) |
| `-sm` | `--summary` | write a JSON run summary to this local path after the run — status, rows, partitions, files, schema, seconds, error — also when the run fails; a list of them for `-p`. Relative to the current directory |
| `-t` | `--test` | test mode (dry run): validate + preview, no files; `--dry-run` is the same flag |
| `-V` | `--version` | print the version and exit |

### Which options must go together

| If you use… | You must also have… | Notes |
|-------------|--------------------|-------|
| (anything) | `-s` + `-q` | the two basics — always required, unless `-j` supplies them from a job file |
| `-o` | nothing else | single file; **cannot** be combined with `-dir`/`-pf` |
| `-m` (any mode) | `-dir` + `-pf` | every mode writes multiple files, so it needs a directory + filename prefix |
| `-dir` or `-pf` | `-m` | they only make sense for mode exports |
| `-m groupby` / `watermark` | `-col` | the column to group / track |
| `-m date` | `-col` + `-df` + `-dt` | the date column and an explicit range |
| `-m relativedate` | `-col` + `-dr` | `-df` optional (defaults to today) |
| `-m monthbydate` | `-col` + `-mr` | `-df` optional (defaults to today) |
| `-m splitrow` | `-row` | no `-col` needed |
| `-m splitsize` | `-smb` (optional, default 128 MB) | no `-col` needed |
| `-rm` | `-m` (not `watermark`) | clearing the directory would destroy the watermark state |
| `-gz` | CSV/TXT output | rejected with parquet (it compresses itself) |
| no `-o` and no `-dir` | — | console preview only (first `PreviewRow` rows, nothing written) |

### 2.1 `-sc` / `--sidecar` — the types CSV cannot carry

CSV and TXT have no types: a loader re-infers them and gets them wrong in exactly the
ways this tool exists to prevent — an int column with a NULL becomes a float, a
`DECIMAL(18,4)` becomes a double, a date becomes a string. `-sc` writes
`<file>.schema.json` beside every delimited file (one per split part, in the same
bucket or folder as the data) with what the **database** declared:

```powershell
python -m ping_dataexport -s DB01 -q "SELECT * FROM sales" -o sales.csv -sc
#  -> sales.csv  +  sales.csv.schema.json
```

```json
{
  "format": "ping-dataexport/schema@1",
  "file": "sales.csv", "job": "DailySales", "source": "DB01", "rows": 10000,
  "text": {
    "delimiter": ",", "quote": "\"", "escape": "\"", "quoting": "minimal",
    "header": true, "null_value": "", "encoding": "utf-8", "compression": "none",
    "line_terminator": "\n", "date_format": "yyyy-MM-dd",
    "timestamp_format": "yyyy-MM-dd HH:mm:ss[.SSSSSS]", "timestamp_timezone": "UTC",
    "boolean_values": ["True", "False"], "binary_encoding": "hex"
  },
  "columns": [
    { "name": "amount", "position": 3, "kind": "DECIMAL", "precision": 18, "scale": 4,
      "nullable": true,
      "types": { "arrow": "decimal128(18, 4)",
                 "parquet": "FIXED_LEN_BYTE_ARRAY (DECIMAL(18,4))",
                 "bigquery": "NUMERIC(18,4)", "snowflake": "NUMBER(18,4)",
                 "spark": "DECIMAL(18,4)", "redshift": "DECIMAL(18,4)",
                 "tsql": "DECIMAL(18,4)", "fabric": "DECIMAL(18,4)" } }
  ]
}
```

- **`columns[].types`** names each column in the vocabulary of the destination:
  `bigquery`, `snowflake`, `spark` (**Databricks** and the **Fabric Lakehouse** — both
  Spark SQL), `redshift`, `tsql` (**Synapse** dedicated SQL pool, SQL Server) and
  `fabric` (the **Fabric Warehouse**'s narrower T-SQL: `VARCHAR` UTF-8 instead of
  `NVARCHAR`, `DATETIME2(6)` as the only timestamp), plus `arrow` / `parquet` — identical
  to what this tool's Parquet writer would have produced, so a CSV load and a Parquet
  load land on the same table.
- **`text`** is the exact dialect of the file: the arguments a `COPY INTO`, `bq load`,
  `CREATE EXTERNAL TABLE` or `spark.read.csv` needs.
- **`rows`** is that file's own row count — a load can be reconciled without reading the
  data back.
- Every column says `"nullable": true`: DB-API metadata does not report nullability
  reliably across drivers, and a wrong `NOT NULL` breaks a load. Widening, never
  narrowing.
- Parquet already carries its schema, so `-sc` with `-f parquet` is reported as ignored
  (`VALIDATION | IGNORED`) rather than failing a job file shared by both formats.

## 3. Export modes (partitioned outputs)

Modes split one query into multiple files. All need `-dir` (output directory)
+ `-pf` (filename prefix); partition values are always bound SQL parameters.

### Date tokens for `-df` / `-dt` (`DateFrom` / `DateTo`)

Instead of a fixed `yyyy-MM-dd`, both accept the current date of the machine
running the export, optionally shifted by whole days:

| Token | Meaning |
|-------|---------|
| `@@CURRENT_DATE@@` | today |
| `@@CURRENT_DATE@@-N@@` | today minus N days, e.g. `@@CURRENT_DATE@@-7@@` |
| `@@CURRENT_DATE@@+N@@` | today plus N days, e.g. `@@CURRENT_DATE@@+1@@` |

`N` is a plain integer (`-1`, `-30`, `-100`, `+10` …). The token is
case-insensitive. Anything else — a missing closing `@@`, a non-integer, a
space, a double sign — is rejected with a validation error before anything runs.
Handy in job files that run daily:

```ini
[Last7Days]
Source=DB01
Query=SELECT * FROM sales
Mode=date
Column=txn_date
DateFrom=@@CURRENT_DATE@@-7@@
DateTo=@@CURRENT_DATE@@-1@@
OutputDir=out/last7
BaseName=sales
```

```bash
ping-dataexport -s DB01 -q "SELECT * FROM sales" -m date -col txn_date \
    -df "@@CURRENT_DATE@@-7@@" -dt "@@CURRENT_DATE@@" -dir last7 -pf sales
```

**`groupby` — one file per distinct value of a column:**

```bash
ping-dataexport -s DB01 -q "SELECT * FROM sales" -m groupby -col region -dir by_region -pf sales
```
```
by_region/
├── sales_@@NULL@@.csv  <- rows where region IS NULL
├── sales_MY.csv
├── sales_SG.csv
├── sales_TH.csv
└── sales_VN.csv
```

**`date` — one file per day in a date range (inclusive):**

```bash
ping-dataexport -s DB01 -q "SELECT * FROM sales" -m date -col txn_date \
    -df 2026-01-01 -dt 2026-01-05 -dir daily -pf sales
```
```
daily/
├── sales_2026-01-01.csv
├── sales_2026-01-02.csv
├── sales_2026-01-03.csv
├── sales_2026-01-04.csv
└── sales_2026-01-05.csv
```

**`relativedate` — N days back from an anchor date, anchor included
(omit `-df` to anchor on today):**

```bash
ping-dataexport -s DB01 -q "SELECT * FROM sales" -m relativedate -col txn_date \
    -df 2026-01-10 -dr 3 -dir rel -pf sales
```
```
rel/
├── sales_2026-01-07.csv
├── sales_2026-01-08.csv
├── sales_2026-01-09.csv
└── sales_2026-01-10.csv
```

**`monthbydate` — N months back plus the anchor month (the anchor month stops
at the anchor date; omit `-df` to anchor on today):**

```bash
ping-dataexport -s DB01 -q "SELECT * FROM sales" -m monthbydate -col txn_date \
    -df 2026-03-15 -mr 3 -dir monthly -pf sales
```
```
monthly/
├── sales_202512.csv
├── sales_202601.csv
├── sales_202602.csv
└── sales_202603.csv    <- 2026-03-01 .. 2026-03-15 only
```

**`splitrow` — rotate to a new file every N rows:**

```bash
ping-dataexport -s DB01 -q "SELECT * FROM sales ORDER BY id" -m splitrow -row 100000 -dir split -pf part
```
```
split/
├── part_1.csv          <- 100,000 rows each
├── part_2.csv
└── part_3.csv          <- remainder
```

**`splitsize` — rotate to a new file every N MB (what object stores and query engines
want: files of a known size, not a known row count):**

```bash
ping-dataexport -s DB01 -q "SELECT * FROM sales ORDER BY id" -m splitsize -smb 128 -dir split -pf part -f parquet
```
```
split/
├── part_1.parquet      <- ~128 MB each
├── part_2.parquet
└── part_3.parquet      <- remainder
```

A file is closed as soon as it passes the limit, so each part is the limit plus at
most one row (CSV/TXT: exact; gzip: the compressed bytes, observed after every
1 MB of text; Parquet: the bytes on the stream plus the buffered rows priced at the
file's bytes-per-row so far, so the first file may run over by up to one row group —
set `-prg` / `-fs` so a row group is well under the limit — plus the footer).
`-smb 0.01` (10 KB) is the way to see the rotation on a small table. `row_id` keeps
counting across parts, as in `splitrow`.

**`watermark` — incremental: only rows newer than the last run:**

```bash
ping-dataexport -s DB01 -q "SELECT * FROM sales" -m watermark -col txn_date -dir incr -pf sales
```
```
incr/
├── sales_20260831_120000.csv    <- filename = run timestamp
└── __watermark__.ini            <- state file: highest txn_date exported so far
```

Run the watermark job again: no new rows → no new file; new rows → one more
timestamped file. The state advances only after a successful export. Delete
`__watermark__.ini` to start over from a full export.

Every run also prints structured log lines to the console (and to the log
file — see *Output and logs* below):

```
2026-08-31 11:24:41 | JOB_20260831_112441 | INFO | CONFIG | JOB | source=DB01 mode=groupby
2026-08-31 11:24:41 | JOB_20260831_112441 | INFO | EXPORT | PARTITION | region = 'TH'
2026-08-31 11:24:41 | JOB_20260831_112441 | INFO | EXPORT | FILE | ./output/by_region/sales_TH.csv
2026-08-31 11:24:41 | JOB_20260831_112441 | INFO | EXPORT | PROGRESS | 200 rows (finished)
2026-08-31 11:24:41 | JOB_20260831_112441 | INFO | RESULT | COMPLETED | 0.2 seconds
```

### Lake layout, parallel partitions, rerun after a failure

Three options apply to the partitioned modes (`groupby`, `date`, `relativedate`,
`monthbydate`); each is one flag on the CLI or one key in a job / in `[Main]`:

| Option | What it does |
|--------|--------------|
| `-hlo` / `HiveLayout=Yes` | **Hive-style directories** instead of suffixed filenames: `year=YYYY/month=MM/day=DD/sales.parquet` (`date`, `relativedate`), `year=YYYY/month=MM/sales.parquet` (`monthbydate`), `region=TH/sales.parquet` (`groupby`; NULL → `region=__HIVE_DEFAULT_PARTITION__/`). BigQuery external tables, Athena, Synapse serverless, Spark and Databricks discover the partitions from the path — nothing to rename |
| `-pw N` / `PartitionWorkers=N` | **N partitions at a time**, one DB connection each (default `[Main] PartitionWorkers` = 4). `row_id` counts per partition. `splitrow`, `watermark` and single-file exports are one stream and stay sequential |
| `-rs` / `Resume=Yes` | **rerun after a failure**: a partition whose output file already exists is skipped (`EXPORT | SKIP`), the rest are exported. A partial file never survives a failure (the writer deletes it), so an existing file is a complete one. Not with `-rm` |

```bash
ping-dataexport -s DB01 -q "SELECT * FROM sales" -m date -col txn_date \
    -df 2026-01-01 -dt 2026-12-31 -dir gs://lake/sales -pf sales -f parquet -hlo -pw 8
```
```
gs://lake/sales/year=2026/month=01/day=01/sales.parquet
gs://lake/sales/year=2026/month=01/day=02/sales.parquet
…
```

**Transient failures retry by themselves.** A dropped connection, a timeout, a
busy or locked source, an object-store 5xx — the connect and the failing partition
are retried on a fresh connection, `[Main] Retry` times (default 3), waiting
`RetryBackoffSeconds` (default 60, doubling: 60, 120, 240). A bad password, a
missing table or a syntax error fails immediately. Combine with `-rs` for the
rerun of a job that still failed.

## 4. Job files (job.ini)

Put recurring exports in a job file so you never retype long commands. Each
`[Section]` is **one job**: run one with `-j SectionName`, or all of them at
once with `-p`. Every key is simply a CLI option written as a config key:

```ini
[DailySales]
Source=DB01
Query=SELECT id, txn_date, amount FROM sales
    WHERE region = 'TH'
Mode=date
Column=txn_date
DateFrom=2026-01-01
DateTo=2026-01-31
OutputDir=out/daily_sales
BaseName=sales
Format=csv

[FullDump]
Source=DB01
Query=SELECT * FROM sales
Output=out/full_dump.parquet
```

```bash
ping-dataexport -j DailySales                 # one section (job.ini by default, -jf elsewhere)
ping-dataexport -j DailySales -dir other_dir   # CLI options override the section
ping-dataexport -p 4                          # ALL sections in parallel, 4 workers
```

Every job.ini key and the CLI option it mirrors (keys are case-insensitive):

| Job key | CLI option | Meaning |
|---------|------------|---------|
| `Source` | `-s` / `--source` | which config.ini DB section to export from |
| `Query` | `-q` / `--query` | the SQL to run; indent continuation lines for multi-line queries |
| `Output` | `-o` / `--output` | single output file path |
| `OutputDir` | `-dir` / `--outputdir` | output directory (needed when `Mode=` is set) |
| `BaseName` | `-pf` / `--prefix` | output filename prefix for mode exports; `Prefix=` is accepted as the same key |
| `Format` | `-f` / `--format` | `csv` / `txt` / `parquet` (else inferred from `Output` extension) |
| `Gzip` | `-gz` / `--gzip` | `Yes`/`No` — gzip the output (CSV/TXT only) |
| `Sep` | `-sep` / `--sep` | field separator (default `,`; `\t` = tab) |
| `Quote` | `-quo` / `--quote` | quote character (default `"`) |
| `StripNewLine` | `-stnl` / `--stripnewline` | `space` \| `blank` \| `escape` \| `doubleescape` |
| `StripMetaChar` | `-stmc` / `--stripmetachar` | `Yes`/`No` — strip invisible/control characters |
| `FetchSize` | `-fs` / `--fetchsize` | rows per batch |
| `Mode` | `-m` / `--mode` | `groupby` / `date` / `relativedate` / `monthbydate` / `splitrow` / `splitsize` / `watermark` |
| `Column` | `-col` / `--column` | the column the mode partitions on |
| `Row` | `-row` / `--row` | rows per file (`Mode=splitrow`) |
| `SplitSizeMb` | `-smb` / `--splitsizemb` | MB per file (`Mode=splitsize`; default 128) |
| `DateFrom` | `-df` / `--datefrom` | start / anchor date, `yyyy-MM-dd` or `@@CURRENT_DATE@@[±N@@]` |
| `DateTo` | `-dt` / `--dateto` | end date, `yyyy-MM-dd` or `@@CURRENT_DATE@@[±N@@]` (`Mode=date`) |
| `DaysRelative` | `-dr` / `--daysrelative` | days back from `DateFrom` (`Mode=relativedate`) |
| `MonthsRelative` | `-mr` / `--monthsrelative` | months back from `DateFrom` (`Mode=monthbydate`) |
| `RemoveExisting` | `-rm` / `--removeexisting` | `Yes`: clear `OutputDir` before exporting |
| `PartitionWorkers` | `-pw` / `--partitionworkers` | partitions exported concurrently (default `[Main] PartitionWorkers` = 4) |
| `HiveLayout` | `-hlo` / `--hivelayout` | `Yes`: Hive-style partition directories (`year=YYYY/month=MM/day=DD/`, `column=value/`) |
| `Resume` | `-rs` / `--resume` | `Yes`: skip partitions whose output file already exists (not with `RemoveExisting`) |
| `Retry` | `-rt` / `--retry` | transient-error retries for this job (default `[Main] Retry`) |
| `RetryBackoffSeconds` | `-rbs` / `--retrybackoffseconds` | first wait before a retry, doubling (default `[Main] RetryBackoffSeconds`) |
| `UnconstrainedNumber` | `-un` / `--unconstrainednumber` | `decimal` / `string` / `double` for this job (default `[Main] UnconstrainedNumber`) |
| `ParquetCompression` | `-pc` / `--parquetcompression` | `snappy` / `zstd` / `gzip` / `brotli` / `lz4` / `none` for this job (default `[Main] ParquetCompression`) |
| `ParquetRowGroupRows` | `-prg` / `--parquetrowgrouprows` | rows per Parquet row group for this job (default `[Main] ParquetRowGroupRows`, else `FetchSize`) |
| `Sidecar` | `-sc` / `--sidecar` | `Yes`: write `<file>.schema.json` beside every csv/txt file of this job (section 2.1) |
| `Name` | `-n` / `--name` | job name shown in logs; default: the section name |

A full sample ships as `job.sample.ini` in the source distribution.

## 5. Run as Python import (library / Airflow)

`from ping_dataexport.api import run_export, run_job, run_jobfile`. Every CLI
option is a keyword argument; every call returns an `ExportResult`
(`ok`, `rows`, `partitions`, `files`, `seconds`, `schema`; `.to_dict()` / `.to_json()`
for XCom, a reconciliation job or a file — the same JSON the CLI writes with
`--summary path.json`) or raises. Failures raise
exceptions (`ConfigError`, `JobValidationError`, `ExportError`) — never
`sys.exit` — so an Airflow task fails cleanly. `run_jobfile` is the exception:
one job's failure never stops the others; check each `ExportResult.status`.

### 5.1 With a config file

`configfile=` points at your `config.ini` (default `config.ini`, looked up in
the current directory, then next to the running script — see section 1).

```python
from ping_dataexport.api import run_export, run_job, run_jobfile

# ad-hoc — source = a config.ini section name
result = run_export("DB01", "SELECT * FROM sales",
                    configfile="config.ini", output="sales.parquet")
print(result.rows, result.files)

# groupby mode: outputdir + basename, like -dir/-pf on the CLI
run_export("DB01", "SELECT * FROM sales", configfile="config.ini",
           mode="groupby", column="region", outputdir="by_region", basename="sales")

# one job.ini section (overrides win over the section, like CLI options)
result = run_job("DailySales", jobfile="job.ini", configfile="config.ini",
                 overrides={"outputdir": "other_dir"})

# every job.ini section in parallel
results = run_jobfile(jobfile="job.ini", configfile="config.ini", workers=4)
failed = [r for r in results if not r.ok]
```

### 5.2 Without a config file

Define the database in code — as a dict of the same keys as a config.ini
section (`Type`, `Host`, `ConnectionString`, …; same rules, `ConnectionString`
overrides all) or as a `DBConfig` — and pass `[Main]` settings as `main=`.
No `config.ini` is read or required.

```python
from ping_dataexport.api import run_export, run_job, run_jobfile

PG = {"Type": "postgresql", "Host": "pghost", "Port": 5432,
      "Database": "analytics", "Username": "app", "Password": "secret"}
MAIN = {"ExportFolder": "./output", "FetchSize": 10000, "RowIDColumn": "row_id"}

# ad-hoc — source = the dict itself
result = run_export(PG, "SELECT * FROM sales", main=MAIN, output="sales.parquet")

# a typed object works the same
from ping_dataexport.config import DBConfig
sqlite = DBConfig(section="LOCAL", name="LOCAL", type="sqlite", database="demo.sqlite")
run_export(sqlite, "SELECT * FROM t", output="t.csv")

# job files without config.ini: sources= maps every Source= name used in job.ini
SOURCES = {"DB01": PG, "MY01": {"Type": "mysql", "ConnectionString": "host=myhost;database=shop",
                                "Username": "app", "PasswordBase64": "c2VjcmV0"}}
run_job("DailySales", jobfile="job.ini", sources=SOURCES, main=MAIN)
run_jobfile(jobfile="job.ini", sources=SOURCES, main=MAIN, workers=4)
```

`sources=` and `main=` also work **alongside** a config file: an in-code
source with the same name as a config.ini section wins. `cloud=` passes the
`[Cloud]` credentials the same way — e.g. from an Airflow connection:

```python
run_export(PG, "SELECT * FROM sales", output="s3://lake/raw/sales.parquet",
           cloud={"AWSAccessKey": conn.login, "AWSSecretKey": conn.password})
```

### 5.3 Airflow

`PingDataExportOperator` wraps the api — one task per export, the result in XCom,
credentials from an Airflow connection or `${VAR}` in config.ini. Airflow itself is
optional: the package adds no dependency and the module imports without it.

```python
from ping_dataexport.airflow import PingDataExportOperator

daily = PingDataExportOperator(
    task_id="daily_sales",
    job="DailySales",                                   # a job.ini section …
    jobfile="/opt/airflow/dags/ping/job.ini",
    configfile="/opt/airflow/dags/ping/config.ini",
    overrides={"datefrom": "{{ ds }}", "dateto": "{{ ds }}"},   # … CLI options win over it
    cloud_conn_id="google_cloud_default",               # or cloud={...}, or ${VAR} in config.ini
)
# XCom: {"status": "COMPLETED", "rows": 1048576, "files": [...], "schema": [...], ...}
```

`source=` + `query=` runs an ad-hoc export (every CLI option in `options={...}`); neither
runs the whole job file in parallel and raises if any section failed. The api works
directly too — `run_job(...)` in a `@task`; an exception fails the task. Full reference:
[docs/AIRFLOW_OPERATOR.md](https://github.com/ping-godhand/python-ping-dataexport/blob/main/docs/AIRFLOW_OPERATOR.md).

The package also works with **no pip install at all**: copy the
`ping_dataexport/` folder next to your code and import it — relative imports
only, no metadata lookups.

## 6. Check a Parquet file — types, `CREATE TABLE`, first rows

The export ends with a file; the next question is always *what do I write in the
warehouse to load it?* `checker` answers it from the file itself — no database, no
writing, footer and one batch only:

```powershell
python -m ping_dataexport.checker out/sales.parquet
python -m ping_dataexport.checker s3://bucket/sales.parquet -n 20 -d snowflake -t DW.SALES
python -m ping_dataexport.checker out/sales.parquet --json     # the whole check as JSON
```

```text
FILE      | D:\export\sales.parquet
ROWS      | 10,000 in 10 row group(s), 332,660 bytes
CODEC     | SNAPPY

COLUMNS
  #  | name       | arrow             | parquet                                  | kind          | null
  1  | id         | int64             | INT64                                    | BIGINT        | NO
  3  | amount     | decimal128(18, 4) | FIXED_LEN_BYTE_ARRAY (DECIMAL(18,4))     | DECIMAL(18,4) | YES
  8  | created_at | timestamp[us]     | INT64 (TIMESTAMP(MICROS))                | TIMESTAMP     | YES

-- Snowflake
CREATE OR REPLACE TABLE "DW"."SALES" (
  "id"         NUMBER(19,0) NOT NULL,
  "amount"     NUMBER(18,4),
  "created_at" TIMESTAMP_NTZ
);

FIRST 10 ROW(S)
  id | amount  | created_at
  1  | 2.5000  | 2026-01-01 09:30:00
```

| Option | Meaning |
|--------|---------|
| `-n` / `--rows` | rows to print (default `__config__.CHECK_PREVIEW_ROWS` = 10) |
| `-d` / `--dialect` | one platform's DDL only; repeatable. `databricks` (`dbx`, `spark`, `delta`, also the Fabric Lakehouse), `snowflake` (`snf`), `bigquery` (`bq`, `gbq`), `redshift` (`rs`), `fabric` (`warehouse`), `synapse` (`tsql`, `sqlserver`, `mssql`) |
| `-t` / `--table` | table name for the DDL, qualified if you like (`analytics.sales`, `project.dataset.sales`); default: the file's name |
| `--json` | the whole check — columns, every `CREATE TABLE`, the preview — as one JSON object |

From Python:

```python
from ping_dataexport.api import check_parquet

check = check_parquet("out/sales.parquet")          # or s3:// gs:// abfs(s)://
print(check.report())
print(check.create_table("databricks", table="lake.sales"))
types = {c.name: c.types["bigquery"] for c in check.columns}
```

Every platform gets its own vocabulary, including the places they genuinely differ:
Redshift `VARCHAR(65535)` / `DOUBLE PRECISION` / `SUPER`, the Fabric Warehouse's
`VARCHAR` (UTF-8, no `NVARCHAR`) and `DATETIME2(6)` for a tz-aware timestamp (the value
is already the UTC instant), Synapse's `NVARCHAR(MAX)` / `DATETIMEOFFSET`, Snowflake's
`TIMESTAMP_TZ`, BigQuery's `NUMERIC` vs `BIGNUMERIC`.

Types are read from the Parquet schema, never inferred from values, and a `NOT NULL` is
emitted only for a column the file itself marks required.

## Output and logs

- Relative output paths resolve under `ExportFolder`; `ForceMakeDir=Yes`
  creates missing directories. Outputs and `ExportFolder` may be cloud URIs
  (`s3://`, `gs://`, `abfs://`, `abfss://`, [1.3](#13-cloud-destinations--cloud));
  logs never are.
- Every run prints structured lines to the console and appends them to **one
  log file per day**: `{LogFolder}/dataexport-YYYYMMDD.log`, where
  `LogFolder` defaults to `{ExportFolder}/logs`. Sequential and parallel runs,
  and every job, share that file — the second column of each line is the job
  name. Priority: `-lf path` > `LogFile=` > `LogFolder=` > default. With no
  `ExportFolder` and no `LogFolder` the log goes to the console only.
- CLI exit codes: `0` success, `1` failure (in `-p` parallel mode: `1` if any
  job failed), `130` when interrupted.

## Driver prerequisites

| `Type` | pip | OS-level |
|--------|-----|----------|
| `sqlite` | nothing (stdlib) | nothing |
| `oracle` | `oracledb` | nothing (thin mode) |
| `postgresql` | `psycopg[binary]` | nothing |
| `mysql` / `mariadb` | `pymysql` | nothing (pure Python; the MariaDB Connector/C is **not** needed) |
| `mssql-odbc` | `pyodbc` | Microsoft ODBC Driver 17/18 for SQL Server |
| `freetds` | `pyodbc` | FreeTDS (`apt install tdsodbc`) |
| `oracle-odbc` / `odbc` | `pyodbc` | your ODBC driver |

## More

- Source, issues and the developer docs (data types, connectors, filesystem,
  job handling, logging, testing):
  <https://github.com/ping-godhand/python-ping-dataexport>
- Runnable examples to copy (`main.py`, `simple_parquet.py`, an Airflow DAG):
  [main/](https://github.com/ping-godhand/python-ping-dataexport/tree/main/main)
- **Type fidelity is proven, not assumed:** the DB zoo
  ([docs/TESTING_DB_ZOO.md](https://github.com/ping-godhand/python-ping-dataexport/blob/main/docs/TESTING_DB_ZOO.md))
  seeds every declared type × ten edge rows into sqlite plus SQL Server 2022, Oracle 23ai,
  PostgreSQL 16, MySQL 8.4 and MariaDB 11 (`docker compose -f docker/container-db/docker-compose.yml up -d`),
  exports them through the real CLI and compares every cell and every Parquet column type
  with a hand-written truth. Zero undocumented differences; the known ones are listed in
  [docs/DATA_TYPES.md](https://github.com/ping-godhand/python-ping-dataexport/blob/main/docs/DATA_TYPES.md) → *Known fidelity losses*.

## Author & License

Built by **Vorapol Ping (ping-godhand)** — https://github.com/ping-godhand
for **Godhand.DEV** — https://godhand.dev

Copyright (c) 2026 Godhand.DEV.
Licensed under the [Apache License 2.0](https://github.com/ping-godhand/python-ping-dataexport/blob/main/LICENSE).
