Metadata-Version: 2.4
Name: ping-dataexport
Version: 0.3.5
Summary: PING's Data Export Tool - To Parquet/CSV/TEXT as Accelerator for Data & AI Project
Author: vorapol
License-Expression: Apache-2.0
Keywords: export,database,csv,parquet,etl,data-platform,airflow
Classifier: Development Status :: 4 - Beta
Classifier: Environment :: Console
Classifier: Intended Audience :: Developers
Classifier: Operating System :: OS Independent
Classifier: Programming Language :: Python :: 3
Classifier: Topic :: Database
Classifier: Topic :: Software Development :: Libraries :: Python Modules
Requires-Python: >=3.9
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: drivers
Requires-Dist: pyodbc>=5; extra == "drivers"
Requires-Dist: oracledb>=2; extra == "drivers"
Provides-Extra: dev
Requires-Dist: pytest>=8; extra == "dev"
Dynamic: license-file

# ping-dataexport

## PING (ping-godhand)'s Universal Data Exporter

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

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

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

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

## Description (English)

Universal database data export: connect to a database, run a SQL query, stream
the result to **CSV / TXT / Parquet**. Built for data-platform work — exporting
legacy databases into files a data lake or warehouse can ingest.

- **Type fidelity first** — rows stream cursor → writer with native driver
  types. `DECIMAL(p,s)` lands in Parquet as `decimal128(p,s)`, ints with NULLs
  stay ints, dates stay dates. No pandas, ever.
- **Databases** — SQLite (stdlib, zero setup), SQL Server (MS ODBC / FreeTDS),
  Oracle (native thin mode or ODBC), any ODBC source.
- **Two front doors** — a CLI and a library API (`ping_dataexport.api`) built
  for Airflow: exceptions instead of exit codes, results as objects.

## Install

```bash
pip install ping-dataexport             # base (pyarrow) — sqlite works out of the box
pip install ping-dataexport[odbc]       # + pyodbc     (SQL Server, FreeTDS, ODBC)
pip install ping-dataexport[oracle]     # + oracledb   (Oracle native)
pip install ping-dataexport[drivers]    # + both
```

## 1. Create a config.ini

One `[Main]` section for defaults, one section per database source:

```ini
[Main]
ExportFolder=./output        ; relative output/log paths resolve under this
FetchSize=10000
ForceMakeDir=Yes
RowIDColumn=row_id           ; optional audit column names
DataDateColumn=data_date     ; optional audit data extract date

[SQLITE01]
Type=sqlite
Database=./demo.sqlite

[DB01]
Type=mssql-odbc
Host=host
Port=1433
Database=DB
Username=user
Password=secret

[ORA01]
Type=oracle
Host=orahost
Port=1521
Service=ORCLPDB
Username=scott
Password=tiger
```

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

## 2. Config keys — type / possible values

`[Main]` (all optional; a missing `[Main]` yields the defaults):

| Key | Type | Possible values / default |
|-----|------|---------------------------|
| `ExportFolder` | path | base folder for relative output/log paths; default: current directory |
| `TempFolder` | path | reserved for a future release |
| `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 date); absent = no column |
| `LogFile` | path | default `{ExportFolder}/logs/log_{jobname}.txt` |

DB section (one per source; section name is what `-s` refers to):

| Key | Type | Possible values / notes |
|-----|------|--------------------------|
| `Type` | enum | `sqlite` \| `mssql-odbc` \| `freetds` \| `oracle` \| `oracle-odbc` \| `odbc`; omitted with `ConnectionString`/`DSN` present ⇒ `odbc` |
| `Name` | string | display name in logs; default: the section name |
| `ConnectionString` | string | raw ODBC connection string (alternative to Host/Port/Database) |
| `DSN` | string | ODBC data source name |
| `Host` / `Port` | string / int | server address |
| `Database` | string | database name; for `sqlite`: the file path |
| `Service` / `SID` | string | Oracle service name or SID |
| `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 | e.g. `utf-8`, `tis-620` |
| `Driver` | string | ODBC driver name override, e.g. `ODBC Driver 18 for SQL Server` |

```ini
PasswordBase64=c2VjcmV0    ; base64("secret")
```

## 3. 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:

| 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` \| `watermark` — see section 4 |
| `-col` | `--column` | column the mode partitions on |
| `-row` | `--row` | rows per file (`-m splitrow`) |
| `-df` | `--datefrom` | start / anchor date `yyyy-MM-dd`; omitted with `-dr`/`-mr` ⇒ today (system date) |
| `-dt` | `--dateto` | end date `yyyy-MM-dd` (`-m date`) |
| `-dr` | `--daysrelative` | days back from `-df` (`-m relativedate`) |
| `-mr` | `--monthsrelative` | months back from `-df` (`-m monthbydate`) |
| `-rm` | `--removeexisting` | `Yes`: clear the output directory before exporting |
| `-j` | `--job` | run one job.ini section — see section 5 |
| `-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 logs and the default log filename |
| `-cf` | `--configfile` | config file path (default `./config.ini`) |
| `-lf` | `--logfile` | log file path |
| `-t` | `--test` | test mode: validate + preview, no files |

### 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 |
| `-gz` | CSV/TXT output | rejected with parquet (it compresses itself) |
| no `-o` and no `-dir` | — | console preview only (first `PreviewRow` rows, nothing written) |

## 4. 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.

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

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

## 5. 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 |
| `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` / `watermark` |
| `Column` | `-col` / `--column` | the column the mode partitions on |
| `Row` | `-row` / `--row` | rows per file (`Mode=splitrow`) |
| `DateFrom` | `-df` / `--datefrom` | start / anchor date, `yyyy-MM-dd` |
| `DateTo` | `-dt` / `--dateto` | end date, `yyyy-MM-dd` (`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 |
| `Name` | `-n` / `--name` | job name shown in logs; default: the section name |

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

## 6. Use as a library (Python / Airflow)

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

# ad-hoc — any CLI option works as a keyword argument
result = run_export("DB01", "SELECT * FROM sales",
                    configfile="config.ini", output="sales.parquet")
print(result.status, result.rows, result.files)

# one job.ini section / every section in parallel
run_job("DailySales", jobfile="job.ini", configfile="config.ini")
results = run_jobfile(jobfile="job.ini", configfile="config.ini", workers=4)
```

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

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.

## Output and logs

- Relative output paths resolve under `ExportFolder`; `ForceMakeDir=Yes`
  creates missing directories.
- Every run logs structured lines to stdout and to a log file:
  `-lf path` > `LogFile=` in config.ini > default
  `{ExportFolder}/logs/log_{jobname}.txt`.
- CLI exit codes: `0` success, `1` failure (in `-p` parallel mode: `1` if any
  job failed).

## ODBC prerequisites (ODBC sources only)

The Python drivers install via pip, but ODBC drivers are OS-level installs:
Microsoft ODBC Driver 17/18 for SQL Server, FreeTDS (`apt install tdsodbc`), or
your Oracle ODBC driver. `Type=sqlite` and `Type=oracle` (thin mode) need none.

## License

Apache-2.0.
