JDBC Connectivity

Gnok speaks JDBC three ways:

Option	Driver / URL	Best for
Gnok JDBC driver (recommended)	io.gnok:gnok-jdbc — jdbc:gnok:postgres://… or jdbc:gnok:flightsql://…	One driver, both transports, plus gnok session settings (snapshot pinning, timezone, dialect flags). The path used by the bundled Tableau & Metabase connectors.
Standard PostgreSQL JDBC	org.postgresql — jdbc:postgresql://…	Tools that already ship the Postgres driver; no gnok-specific session params.
Arrow Flight SQL JDBC	org.apache.arrow:flight-sql-jdbc-driver — jdbc:arrow-flight-sql://…	High-throughput, zero-copy columnar transfer.

All paths support prepared statements, parameterized queries, metadata discovery, and authentication.

Client connectivity is set up by request

JDBC access isn't self-service for new organizations yet; use Gnok Studio in the meantime. Email support@gnok.io to request client connectivity. Gnok support provides the host and port for each transport, the Gnok JDBC driver, and credentials for your organization. Hosts and ports in the examples below (such as pg.example.com:5433) are placeholders.

The Gnok JDBC driver wraps the standard PostgreSQL and Arrow Flight SQL drivers, so the examples in the two sections below also apply to it — it simply adds the jdbc:gnok:… URL prefix and gnok session propagation. If you don't need session scoping, you can point any tool straight at the standard drivers.

---

Gnok JDBC Driver

The gnok-jdbc driver is a single jar that speaks both transports through one driver class. It delegates to org.postgresql (PostgreSQL transport) and the Arrow Flight SQL driver (Flight transport) under the hood, and adds gnok-specific session propagation — snapshot pinning, timezone, and SQL-dialect flags — that the raw drivers can't carry. Install one driver in your BI tool and switch transports with the URL prefix.

• Driver class: io.gnok.jdbc.GnokDriver (auto-registers via META-INF/services/java.sql.Driver)
• Recommended transport for BI tools: PostgreSQL (jdbc:gnok:postgres://)

URL formats

jdbc:gnok:postgres://<host>:<port>/<catalog>?<params>     # PostgreSQL transport
jdbc:gnok:flightsql://<host>:<port>/?<params>             # Arrow Flight SQL transport

The transport is explicit — a bare jdbc:gnok:// is intentionally rejected.

Getting the driver

Gnok support provides the Gnok JDBC JAR and installation instructions when your client connectivity is set up. It bundles the transport drivers. See client packages.

Connect — PostgreSQL transport

String url = "jdbc:gnok:postgres://pg.example.com:5433/my_catalog?sslmode=verify-full";
Properties props = new Properties();
props.setProperty("user", "alice");
props.setProperty("password", "password123");

try (Connection conn = DriverManager.getConnection(url, props);
     Statement stmt = conn.createStatement();
     ResultSet rs = stmt.executeQuery(
         "SELECT region, SUM(amount) AS total FROM orders GROUP BY region")) {
    while (rs.next()) {
        System.out.printf("%s: %.2f%n", rs.getString("region"), rs.getDouble("total"));
    }
}

All standard PostgreSQL connection properties (ssl, sslmode, ApplicationName, currentSchema, …) pass straight through to the backend, and the metadata / prepared-statement / cursor / DML examples in the PostgreSQL JDBC Driver section below apply unchanged.

tip

Use sslmode=verify-full and the CA configuration supplied for your endpoint.

Connect — Flight SQL transport

String url = "jdbc:gnok:flightsql://flight.example.com:443/?tls=true";
Properties props = new Properties();
props.setProperty("auth", "bearer");
props.setProperty("token", jwtToken);   // or auth=basic + user/password

Connection conn = DriverManager.getConnection(url, props);

Session scoping

gnok-specific parameters are passed in the URL query string (or Properties). On the PostgreSQL transport they ride in the Postgres startup-message options parameter as -c gnok.<key>=<value> GUCs; on the Flight transport they ride as x-gnok-* headers. Either way the engine applies them to the connection's session context.

String url = "jdbc:gnok:postgres://pg.example.com:5433/my_catalog?sslmode=verify-full"
    + "&currentSchema=analytics"
    + "&snapshotMode=pinned&snapshotTtl=30s"
    + "&timezone=America/New_York";
Connection conn = DriverManager.getConnection(url, new Properties());

Parameter	Default	Description
sessionId	auto (UUID)	Stable session identity for the connection
snapshotMode	pinned	pinned, latest, or explicit
snapshotTtl	15s	Snapshot-pin TTL (15s, 500ms, 5m, …)
explicitSnapshotId	—	Snapshot id for snapshotMode=explicit
role	—	Role for access control; it must already be granted to you
timezone	—	Session timezone (e.g. America/New_York)
caseSensitiveIdentifiers	false	Identifier case sensitivity
ansiMode	false	ANSI SQL dialect mode
nullsFirst	false	NULLs sort first in ORDER BY
divByZeroReturnsNull	false	Division by zero returns NULL instead of erroring

Flight-transport-only auth parameters: tls (true/false), auth (none/basic/bearer), user, password, token.

Your organization and user identity come from the credentials you connect with; grants and row-level security policies apply to that identity.

note

Catalog/schema context and organization isolation (derived from your credentials) apply on both transports. Snapshot pinning is fully wired on the Flight transport (the two-phase ticket protocol); over the PostgreSQL transport these settings are propagated and recorded on the session, and the engine evaluates timestamps in UTC. Pick the transport that matches your consistency needs.

DatabaseMetaData

getMetaData() returns the upstream driver's metadata (which queries pg_catalog / information_schema server-side — the same catalog gnok serves over Flight), wrapped so the identity accessors report gnok: getConnection() returns the gnok connection, getURL() returns the jdbc:gnok:… URL, and getDriverName() / getDriverVersion() report the Gnok driver. getDatabaseProductName() stays PostgreSQL for BI-tool compatibility. All the metadata methods documented under PostgreSQL JDBC Driver below work unchanged.

---

PostgreSQL JDBC Driver

Driver Download

Use the standard PostgreSQL JDBC driver (postgresql-42.7.x):

<!-- Maven -->
<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.7.5</version>
</dependency>

// Gradle
implementation 'org.postgresql:postgresql:42.7.5'

Connection URL

jdbc:postgresql://pg.example.com:5433/my_catalog?sslmode=verify-full

Parameter	Default	Description
Host	—	PostgreSQL wire host provided for your organization (example: pg.example.com)
Port	—	Port provided for your organization
Database	—	Iceberg catalog name

Authentication

Use the authentication method Gnok support set up for your organization:

Password Authentication (default)

String url = "jdbc:postgresql://pg.example.com:5433/my_catalog?sslmode=verify-full";
Properties props = new Properties();
props.setProperty("user", "alice");
props.setProperty("password", "password123");

Connection conn = DriverManager.getConnection(url, props);

The server verifies the password and applies your roles and grants to the session.

JWT Authentication

Pass a pre-obtained JWT token in the password field:

String url = "jdbc:postgresql://pg.example.com:5433/my_catalog?sslmode=verify-full";
Properties props = new Properties();
props.setProperty("user", "alice");
props.setProperty("password", jwtToken);  // JWT bearer token

Connection conn = DriverManager.getConnection(url, props);

Use this mode only when Gnok support has told you that your endpoint accepts a token as the password.

SSL/TLS

Configure the JDBC driver to use TLS with certificate verification:

Properties props = new Properties();
props.setProperty("user", "alice");
props.setProperty("password", "password123");
props.setProperty("sslmode", "verify-full");

Connection conn = DriverManager.getConnection(
    "jdbc:postgresql://pg.example.com:5433/my_catalog?sslmode=verify-full", props
);

Basic Operations

Execute Queries

try (Statement stmt = conn.createStatement()) {
    ResultSet rs = stmt.executeQuery(
        "SELECT region, SUM(amount) AS total " +
        "FROM orders GROUP BY region ORDER BY total DESC"
    );
    while (rs.next()) {
        System.out.printf("%s: %.2f%n",
            rs.getString("region"),
            rs.getDouble("total")
        );
    }
}

Prepared Statements

String sql = "SELECT * FROM orders WHERE region = ? AND amount > ?";
try (PreparedStatement ps = conn.prepareStatement(sql)) {
    ps.setString(1, "US");
    ps.setDouble(2, 100.0);

    try (ResultSet rs = ps.executeQuery()) {
        while (rs.next()) {
            System.out.println(rs.getLong("order_id"));
        }
    }

    // Re-execute with different parameters
    ps.setString(1, "EU");
    ps.setDouble(2, 500.0);
    try (ResultSet rs = ps.executeQuery()) {
        // ...
    }
}

DML (INSERT, UPDATE, DELETE)

// INSERT
try (PreparedStatement ps = conn.prepareStatement(
    "INSERT INTO orders (order_id, customer_id, amount, region) VALUES (?, ?, ?, ?)"
)) {
    ps.setLong(1, 1001);
    ps.setLong(2, 42);
    ps.setBigDecimal(3, new BigDecimal("199.99"));
    ps.setString(4, "US");
    int rows = ps.executeUpdate();
    System.out.println("Inserted: " + rows);
}

// UPDATE
try (Statement stmt = conn.createStatement()) {
    int rows = stmt.executeUpdate(
        "UPDATE orders SET status = 'shipped' WHERE order_id = 1001"
    );
}

// DELETE
try (Statement stmt = conn.createStatement()) {
    int rows = stmt.executeUpdate(
        "DELETE FROM orders WHERE status = 'cancelled'"
    );
}

DDL

try (Statement stmt = conn.createStatement()) {
    // Create schema
    stmt.execute("CREATE SCHEMA IF NOT EXISTS analytics");

    // Create table
    stmt.execute(
        "CREATE TABLE analytics.events (" +
        "  event_id BIGINT NOT NULL," +
        "  event_type VARCHAR," +
        "  payload VARCHAR," +
        "  event_date DATE" +
        ") PARTITIONED BY (event_date)"
    );

    // Create table with Iceberg v3 features
    stmt.execute(
        "CREATE TABLE analytics.logs (" +
        "  id BIGINT," +
        "  data VARIANT" +
        ") WITH ('format-version' = '3')"
    );
}

Server-Side Cursors

For large result sets, use server-side cursors to control memory usage:

// Disable auto-commit to enable cursor-based fetching
conn.setAutoCommit(false);

try (Statement stmt = conn.createStatement()) {
    // Declare a cursor
    stmt.execute("DECLARE my_cursor CURSOR FOR SELECT * FROM large_table");

    // Fetch in batches
    ResultSet rs = stmt.executeQuery("FETCH 1000 FROM my_cursor");
    while (rs.next()) {
        // Process batch
    }

    // Fetch next batch
    rs = stmt.executeQuery("FETCH 1000 FROM my_cursor");

    // Close cursor
    stmt.execute("CLOSE my_cursor");
}

conn.setAutoCommit(true);

Cursor options:

Option	Description
SCROLL / NO SCROLL	Allow backward fetching (default: NO SCROLL)
WITH HOLD	Keep cursor open after commit
FETCH FORWARD N	Fetch next N rows
FETCH BACKWARD N	Fetch previous N rows (requires SCROLL)
FETCH ALL	Fetch all remaining rows
FETCH NEXT	Fetch one row

COPY TO STDOUT

Export query results in tab-delimited format:

try (Statement stmt = conn.createStatement()) {
    // Export a table
    ResultSet rs = stmt.executeQuery("COPY orders TO STDOUT");

    // Export a query result
    ResultSet rs = stmt.executeQuery("COPY (SELECT * FROM orders WHERE region = 'US') TO STDOUT");
}

Schema and Search Path

try (Statement stmt = conn.createStatement()) {
    // Set schema search path
    stmt.execute("SET search_path TO my_schema, public");

    // Query current path
    ResultSet rs = stmt.executeQuery("SHOW search_path");
}

DatabaseMetaData

The PostgreSQL wire protocol implements comprehensive DatabaseMetaData for tool compatibility:

DatabaseMetaData meta = conn.getMetaData();

// List catalogs
ResultSet catalogs = meta.getCatalogs();

// List schemas
ResultSet schemas = meta.getSchemas();

// List tables (supports pattern matching)
ResultSet tables = meta.getTables(
    "my_catalog",     // catalog
    "my_schema",      // schema pattern
    "%",              // table name pattern
    new String[]{"TABLE", "VIEW"}  // types
);

// List columns
ResultSet columns = meta.getColumns(
    "my_catalog",     // catalog
    "my_schema",      // schema
    "orders",         // table
    "%"               // column pattern
);

// Primary keys
ResultSet pks = meta.getPrimaryKeys("my_catalog", "my_schema", "orders");

Supported Metadata Methods

Method	Status	Notes
getCatalogs()	Supported	Returns Iceberg catalogs
getSchemas()	Supported	Returns schemas in the connected catalog
getTables()	Supported	Supports catalog, schema, and name pattern filters
getColumns()	Supported	Returns column name, type, nullability, ordinal position
getPrimaryKeys()	Supported	Non-nullable columns exposed as primary key candidates
getTableTypes()	Supported	Returns TABLE and VIEW
getImportedKeys()	Placeholder	Returns empty result set
getExportedKeys()	Placeholder	Returns empty result set
getTypeInfo()	Supported	Returns PostgreSQL type OIDs mapped from Arrow types

System Catalog Queries

Gnok implements pg_catalog and information_schema views for compatibility with BI tools and JDBC drivers:

pg_catalog

Table	Description
pg_database	Iceberg catalogs
pg_namespace	Schemas
pg_class	Tables and relations
pg_attribute	Table columns
pg_type	Data type definitions with OIDs
pg_index	Partition/sort columns as pseudo-indexes
pg_constraint	Non-nullable columns as primary key constraints
pg_roles	User role information
pg_proc	Function metadata
pg_settings	Server configuration
pg_am	Access methods

information_schema

View	Description
schemata	Catalog schemas
tables	All tables and views
columns	Table column definitions

note

System catalog tables return metadata derived from Iceberg catalogs, not from a real PostgreSQL instance. Results are sufficient for JDBC DatabaseMetaData, BI tool auto-discovery, and schema browsing, but do not include PostgreSQL-specific internals.

Data Type Mapping

Gnok / Iceberg Type	PostgreSQL OID	JDBC Type
BOOLEAN	16 (bool)	Types.BOOLEAN
TINYINT	21 (int2)	Types.SMALLINT
SMALLINT	21 (int2)	Types.SMALLINT
INT	23 (int4)	Types.INTEGER
BIGINT	20 (int8)	Types.BIGINT
FLOAT	700 (float4)	Types.REAL
DOUBLE	701 (float8)	Types.DOUBLE
DECIMAL(p,s)	1700 (numeric)	Types.NUMERIC
VARCHAR	1043 (varchar)	Types.VARCHAR
BINARY	17 (bytea)	Types.BINARY
DATE	1082 (date)	Types.DATE
TIME	1083 (time)	Types.TIME
TIMESTAMP	1114 (timestamp)	Types.TIMESTAMP
TIMESTAMPTZ	1184 (timestamptz)	Types.TIMESTAMP_WITH_TIMEZONE
JSON	114 (json)	Types.VARCHAR
JSONB	3802 (jsonb)	Types.VARCHAR
ARRAY<T>	—	Types.ARRAY

Connection Properties

Property	Default	Description
user	—	Username for authentication
password	—	Password or JWT token
ssl	false	Enable SSL/TLS
sslmode	—	require, verify-ca, verify-full
ApplicationName	—	Application identifier (visible in server logs)
currentSchema	—	Default schema

---

Arrow Flight SQL JDBC Driver

Driver Download

Use the Arrow Flight SQL JDBC driver:

<!-- Maven -->
<dependency>
    <groupId>org.apache.arrow</groupId>
    <artifactId>flight-sql-jdbc-driver</artifactId>
    <version>18.3.0</version>
</dependency>

Connection URL

jdbc:arrow-flight-sql://flight.example.com:443?useEncryption=true

Parameter	Default	Description
Host	—	Flight SQL host provided for your organization (example: flight.example.com)
Port	—	Port provided for your organization (example: 443)
TLS	Required for the hosted endpoint	Keep encryption and certificate verification enabled

Authentication

String url = "jdbc:arrow-flight-sql://flight.example.com:443?useEncryption=true";
Properties props = new Properties();
props.setProperty("user", "alice");
props.setProperty("password", jwtToken);  // JWT bearer token

Connection conn = DriverManager.getConnection(url, props);

Example

String url = "jdbc:arrow-flight-sql://flight.example.com:443?useEncryption=true";
Properties props = new Properties();
props.setProperty("user", "alice");
props.setProperty("password", jwtToken);

try (Connection conn = DriverManager.getConnection(url, props);
     Statement stmt = conn.createStatement()) {

    // Query
    ResultSet rs = stmt.executeQuery(
        "SELECT region, COUNT(*) AS cnt FROM orders GROUP BY region"
    );
    while (rs.next()) {
        System.out.printf("%s: %d%n", rs.getString(1), rs.getLong(2));
    }

    // Prepared statement
    PreparedStatement ps = conn.prepareStatement(
        "SELECT * FROM orders WHERE amount > ? AND region = ?"
    );
    ps.setDouble(1, 100.0);
    ps.setString(2, "US");
    rs = ps.executeQuery();
}

Metadata Discovery

The Flight SQL driver supports the same DatabaseMetaData methods:

DatabaseMetaData meta = conn.getMetaData();
ResultSet tables = meta.getTables(null, null, "%", null);
ResultSet columns = meta.getColumns(null, null, "orders", "%");

When to Use Flight SQL JDBC

Use the Arrow Flight SQL driver when:

• You need maximum throughput for large result sets (zero-copy Arrow columnar transfer)
• Your application can consume Arrow RecordBatch data natively
• You are building data pipelines or ETL jobs

Use the PostgreSQL JDBC driver when:

• You need BI tool compatibility (Tableau, DBeaver, pgAdmin, Metabase, etc.)
• You need server-side cursors for paginated result sets
• Your application uses standard JDBC patterns without Arrow-specific optimizations
• You need pg_catalog / information_schema metadata queries

---

BI Tool Configuration

There are two ways to wire a BI tool to Gnok over the PostgreSQL wire protocol:

• Standard PostgreSQL connector (shown per-tool below) — simplest; uses the tool's built-in Postgres driver. No gnok session parameters.
• Gnok JDBC driver — install gnok-jdbc-<ver>-all.jar and connect with a jdbc:gnok:postgres:// URL. Adds session settings (snapshot/timezone) and, where packaged, a branded connector entry.

Gnok JDBC driver in BI tools

Generic JDBC (Tableau "Other Databases (JDBC)", DBeaver, DataGrip):

1. Copy gnok-jdbc-<ver>-all.jar into the tool's drivers folder — Tableau: ~/Library/Tableau/Drivers (macOS) or C:\Program Files\Tableau\Drivers (Windows); DBeaver/DataGrip: register a custom driver with class io.gnok.jdbc.GnokDriver.
2. URL: jdbc:gnok:postgres://<host>:5433/<catalog>?sslmode=verify-full
3. Dialect: PostgreSQL
4. Username / Password: your gnok credentials

Tableau — named "Gnok" connector (.taco): a packaged connector built on gnok-jdbc gives Gnok its own entry with a clean dialog (host / port / catalog / credentials, Require SSL on by default). Place the jar in ~/Library/Tableau/Drivers, drop gnok_jdbc.taco into ~/Documents/My Tableau Repository/Connectors/, using the connector package and installation instructions Gnok support provides. Retain connector signature verification.

Then Connect → To a Server → More… → Gnok, fill in Server / Port / Catalog / Username / Password, and pick a schema.

Metabase: a Gnok driver plugin built on gnok-jdbc registers a Gnok database type. Drop the plugin jar into Metabase's plugins/ directory, restart, then Add database → Gnok with the host and port provided for your organization, database = your catalog, and your credentials.

All BI-tool examples below need the host, port, and TLS settings Gnok support provides, with certificate verification on. Example hosts and ports are placeholders.

DBeaver

1. Create a new PostgreSQL connection
2. Host: pg.example.com, Port: 5433, Database: your catalog name
3. Authentication: Username + password
4. DBeaver auto-discovers schemas, tables, and columns via pg_catalog

DBeaver-specific compatibility: DEALLOCATE ALL cleanup, SET application_name, SHOW search_path, and version() queries are all handled.

Tableau

1. Use the PostgreSQL connector
2. Server: pg.example.com, Port: 5433
3. Database: your catalog name
4. Authentication: Username + password

Tableau-specific: SET extra_float_digits, SET application_name = 'Tableau', and TRY_CAST are supported.

pgAdmin

1. Add a new server: Host: pg.example.com, Port: 5433
2. Maintenance database: your catalog name
3. pgAdmin schema browser works via pg_namespace, pg_class, pg_attribute queries

Superset / Metabase / Looker

Use the PostgreSQL connection type with host pg.example.com, port 5433, and your catalog name as the database.

Power BI

Use the PostgreSQL ODBC driver or the PostgreSQL connector with the same connection parameters.

---

Connection Pooling

Gnok supports standard JDBC connection pooling via HikariCP, c3p0, or similar libraries:

HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:postgresql://pg.example.com:5433/my_catalog?sslmode=verify-full");
config.setUsername("alice");
config.setPassword("password123");
config.setMaximumPoolSize(20);
config.setMinimumIdle(5);

HikariDataSource ds = new HikariDataSource(config);
try (Connection conn = ds.getConnection()) {
    // Use connection
}

To pool through the Gnok JDBC driver instead, set the jdbc:gnok:postgres://… URL and the driver class:

config.setJdbcUrl("jdbc:gnok:postgres://pg.example.com:5433/my_catalog?sslmode=verify-full");
config.setDriverClassName("io.gnok.jdbc.GnokDriver");

Pooled connections authenticate when they open; size the pool for your workload rather than opening a connection per query.

---

Troubleshooting

Issue	Solution
Connection refused	Check the host, port, and your network access; report persistent failures to support@gnok.io
FATAL: password authentication failed	Confirm the authentication method and credentials Gnok support set up for you
relation "pg_catalog.pg_xxx" does not exist	The queried system table may not be implemented; check supported tables above
SSL connection required	Use the TLS endpoint provided for your organization and enable certificate verification in your client
type "xxx" not supported	Some PostgreSQL-specific types have no Gnok equivalent; use CAST to a supported type
Slow metadata queries	First metadata query warms the catalog cache; subsequent queries are faster
DEALLOCATE errors	Upgrade to PostgreSQL JDBC 42.7+ which handles DEALLOCATE ALL correctly

Getting help

Gnok manages endpoint authentication and transport configuration. For connection details or changes, email support@gnok.io. See connection setup.