Write Apache Parquet files straight from a JDBC ResultSet — no Spark, no Avro, no intermediate CSV.

Website · Usage · Java API · Changelog

---

Why

Analytics engines like DuckDB and ClickHouse read Parquet natively and fast. Getting data out of an operational RDBMS and into Parquet normally means a Spark job, an Avro schema or a lossy CSV round trip.

JDBCParquetWriter does it in one call. It derives the Parquet schema from ResultSetMetaData, streams the rows through Parquet's ParquetWriter and writes a single local file — preserving nullability, DECIMAL precision and scale, dates and timestamps.

• Schema derived automatically from the JDBC metadata, nullability included
• Exact DECIMAL transport: INT64 up to 18 digits of precision, BINARY beyond it
• Handles Oracle's unspecified NUMBER scale (-127)
• Row-by-row streaming, so the source table never has to fit in memory
• SNAPPY by default, any CompressionCodecName on request
• Generates the matching INSERT … SELECT * FROM read_parquet(…) import statement

Install

<dependency>
    <groupId>com.manticore-projects.jdbc</groupId>
    <artifactId>jdbcparquetwriter</artifactId>
    <version>[1.3,)</version>
</dependency>

implementation 'com.manticore-projects.jdbc:jdbcparquetwriter:+'

Requires Java 17 or later. Snapshots are published to https://central.sonatype.com/repository/maven-snapshots/.

Usage

Given an ordinary table:

CREATE TABLE test.execution_ref (
    id_execution_ref         DECIMAL(9) NOT NULL
    , value_date             DATE       NOT NULL
    , posting_date           TIMESTAMP  NOT NULL
    , amount                 DECIMAL(23,5)  NULL
);

INSERT INTO test.execution_ref VALUES (1, {d '2021-01-06'}, {ts '2021-01-15 07:48:40.851'}, 100.22);
INSERT INTO test.execution_ref VALUES (2, {d '2021-01-12'}, {ts '2021-01-13 06:55:10.329'}, 75.30);
INSERT INTO test.execution_ref VALUES (3, {d '2021-01-13'}, {ts '2021-01-14 05:00:41.136'}, NULL);

Write a ResultSet

String tableName = "execution_ref";
File file = File.createTempFile(tableName, ".parquet");

String sqlStr = "SELECT * FROM test." + tableName;
try (Statement st = conn.createStatement(); ResultSet rs = st.executeQuery(sqlStr)) {
    long rows = JDBCParquetWriter.write(file, tableName, rs);
}

Write a whole table

long rows = JDBCParquetWriter.write(file, "test.execution_ref", conn);

Choose a codec

JDBCParquetWriter.write(file, tableName, rs, CompressionCodecName.ZSTD);

Write a query and get the import statement back

String importStr = JDBCParquetWriter.writeFileForQueryResult(
        folder, "SELECT * FROM test.execution_ref", "execution_ref",
        conn, JDBCParquetWriter.Dialect.DUCKDB, CompressionCodecName.SNAPPY);

// INSERT INTO execution_ref SELECT * FROM read_parquet('/tmp/execution_ref.parquet');

Export every table a query reads

writeFilesForQueryTables parses the statement with JSqlParser, finds the source tables and dumps each one:

String importStr = JDBCParquetWriter.writeFilesForQueryTables(
        folder, complexQuery, conn,
        JDBCParquetWriter.Dialect.DUCKDB, CompressionCodecName.SNAPPY);

Reading it back

$ duckdb
D SELECT * FROM read_parquet('/tmp/execution_ref.parquet');
┌──────────────────┬────────────┬────────────────────────────┬───────────────┐
│ ID_EXECUTION_REF │ VALUE_DATE │        POSTING_DATE        │    AMOUNT     │
│      int64       │    date    │  timestamp with time zone  │ decimal(23,5) │
├──────────────────┼────────────┼────────────────────────────┼───────────────┤
│                1 │ 2021-01-06 │ 2021-01-15 07:48:40.851+07 │     100.22000 │
│                2 │ 2021-01-12 │ 2021-01-13 06:55:10.329+07 │      75.30000 │
│                3 │ 2021-01-13 │ 2021-01-14 05:00:41.136+07 │          NULL │
└──────────────────┴────────────┴────────────────────────────┴───────────────┘

Note — the path is a string literal, so it belongs in single quotes. Double quotes make DuckDB read it as an identifier.

Type mapping

JDBC type	Parquet type	Logical annotation
BOOLEAN	BOOLEAN	—
TINYINT, SMALLINT, INTEGER	INT32	—
BIGINT	INT64	—
REAL	FLOAT	—
FLOAT, DOUBLE	DOUBLE	—
CHAR, VARCHAR, CLOB (and the N variants)	BINARY	String
BINARY, VARBINARY, BLOB	BINARY	—
DATE	INT32	Date
TIME	INT32	Time(MILLIS)
TIMESTAMP	INT64	Timestamp(MILLIS, UTC)
DECIMAL/NUMERIC, scale > 0, precision ≤ 18	INT64	Decimal(p,s)
DECIMAL/NUMERIC, scale > 0, precision > 18	BINARY	Decimal(p,s)
DECIMAL/NUMERIC, scale = 0, precision < 5	INT32	—
DECIMAL/NUMERIC, scale = 0, precision ≥ 5	INT64	—

A column of any other type raises IllegalArgumentException. Columns reported as nullable become optional, the rest required.

Building

gradle build

The build runs Checkstyle, PMD, SpotBugs, Spotless and JaCoCo. JMH benchmarks comparing H2 and DuckDB live in PerformanceTest; the SAMPLE_SIZE environment variable controls the generated portfolio size — be careful, 100 million rows produce a 20 GB H2 database.

gradle jmh

License

GPL-3.0. See LICENSE.

---

Part of the Manticore Projects toolchain, alongside JSqlParser and MJdbcUtils.