JDBC catalog

A JDBC catalog is a kind of external catalog that enables you to query data from data sources accessed through JDBC without ingestion.

Also, you can directly transform and load data from JDBC data sources by using INSERT INTO based on JDBC catalogs.

JDBC catalogs support MySQL and PostgreSQL.

PostgreSQL unconstrained numeric

PostgreSQL numeric and decimal columns declared without precision and scale are mapped to StarRocks DECIMAL(38,18) (Decimal128), instead of VARCHAR. This mapping supports up to 20 integer digits and 18 fractional digits. Values must be exactly representable: overflow, nonzero fractional digits beyond scale 18, NaN, and infinities cause a read error. Additional trailing fractional zeros are accepted. NULL remains NULL.

This changes these columns from string semantics to numeric semantics, including sorting, comparisons, grouping, and function resolution. Numeric columns with explicitly declared precision and scale retain their existing mapping. When a scan reads an unconstrained numeric column, predicate, expression, aggregation, and join pushdown are conservatively disabled for that scan so the numeric conversion occurs before evaluation in StarRocks. Columns returned by PostgreSQL views and native_query use the same mapping when their metadata reports unconstrained numeric.

If a value does not fit, expose an explicitly typed PostgreSQL view with a business-appropriate precision and scale, or expose the value as text when preserving its textual representation is required.

PostgreSQL uuid

From v4.2 onwards, PostgreSQL uuid columns are mapped to StarRocks VARCHAR(36), instead of VARBINARY. The value itself does not change: the JDBC driver hands StarRocks a java.util.UUID, and the JDBC bridge has always written it out as the 36 characters of its canonical lowercase text, so the same bytes are now labeled as text rather than as binary. NULL remains NULL, and uuid[] remains unsupported. Every other UUID path in StarRocks already produces text: the uuid() and uuid_v7() functions, Iceberg UUIDType, and Parquet UUID columns read through FILES().

This gives these columns string semantics, including comparisons, sorting, grouping, and function resolution. Comparisons on a uuid column are now pushed down to PostgreSQL, which previously failed outright: =, !=, <=>, range comparisons, BETWEEN, and ORDER BY ... LIMIT all reach the remote statement, and none of them carries COLLATE "C". The canonical text sorts in exactly the order PostgreSQL sorts the values, so no collation is needed, and PostgreSQL rejects one on the type in any case. A pushed comparison can therefore still use a PostgreSQL index on the column. MIN and MAX over a uuid column are the exception and stay in StarRocks: PostgreSQL defines no min or max aggregate for the type, however well its values order. Values previously rendered as hexadecimal because the column was binary -- with binary_encoding_level set to ALL, or when nested inside an ARRAY or STRUCT -- are now returned as the UUID text itself.

An asynchronous materialized view that projects a uuid column directly materialized it as VARBINARY, so the type change deactivates it on its next refresh; ALTER MATERIALIZED VIEW ... ACTIVE cannot revive it and it has to be dropped and recreated. A materialized view that casts the column, and an ordinary logical view, are unaffected. Set the catalog property postgresql.uuid.mapping to varbinary to keep the previous mapping on a catalog whose materialized views have not been rebuilt yet.

Prerequisites

• The FEs and BEs or CNs in your StarRocks cluster can download the JDBC driver from the download URL specified by the driver_url parameter.

Create a JDBC catalog

Syntax

CREATE EXTERNAL CATALOG <catalog_name>
[COMMENT <comment>]
PROPERTIES ("key"="value", ...)

Parameters

catalog_name

The name of the JDBC catalog. The naming conventions are as follows:

• The name can contain letters, digits (0-9), and underscores (_). It must start with a letter.
• The name is case-sensitive and cannot exceed 1023 characters in length.

comment

The description of the JDBC catalog. This parameter is optional.

PROPERTIES

The properties of the JDBC Catalog. PROPERTIES must include the following parameters:

Parameter	Description
type	The type of the resource. Set the value to jdbc.
user	The username that is used to connect to the target database.
password	The password that is used to connect to the target database.
jdbc_uri	The URI that the JDBC driver uses to connect to the target database. For MySQL, the URI is in the "jdbc:mysql://ip:port" format. For PostgreSQL, the URI is in the "jdbc:postgresql://ip:port/db_name" format. For more information: PostgreSQL.
driver_url	The download URL of the JDBC driver JAR package, for example, https://repo1.maven.org/maven2/org/postgresql/postgresql/42.3.3/postgresql-42.3.3.jar.
driver_class	The class name of the JDBC driver. The JDBC driver class names of common database engines are as follows: MySQL: com.mysql.jdbc.Driver (MySQL v5.x and earlier) and com.mysql.cj.jdbc.Driver (MySQL v6.x and later) PostgreSQL: org.postgresql.Driver

Optional PostgreSQL properties

When driver_class is set to PostgreSQL, you can configure the following optional property:

Parameter	Default	Description
postgresql.uuid.mapping	varchar	Controls PostgreSQL uuid mapping. varchar maps the column to VARCHAR(36). varbinary restores the mapping used before v4.2, as a transition for a catalog whose materialized views over uuid columns have not been rebuilt yet. An unrecognized value is ignored and varchar is used. Change it with ALTER CATALOG, which rebuilds the catalog's connector; REFRESH EXTERNAL TABLE does not re-derive a JDBC table's schema.

Optional statistics cache properties

StarRocks caches per-table statistics read from JDBC sources to avoid blocking query planning. One cache entry holds a table's row count together with the per-column statistics the source reports, and both are loaded and expired as a unit. These properties let you tune the cache behavior per catalog. If not set, the global FE configuration values are used. The parameter names predate column statistics and are unchanged.

Parameter	Default	Description
jdbc_row_count_cache_refresh_sec	600	Background refresh interval (seconds). After this interval, the cached entry is returned immediately while a reload runs asynchronously in the background.
jdbc_row_count_cache_expire_sec	1200	Hard eviction TTL (seconds). Cache entries not accessed within this window are evicted. Must be greater than jdbc_row_count_cache_refresh_sec.
jdbc_row_count_cache_max_size	10000	Maximum number of table entries in the statistics cache for this catalog.

NOTE

The FEs download the JDBC driver JAR package at the time of JDBC catalog creation, and the BEs or CNs download the JDBC driver JAR package at the time of the first query. The amount of time taken for the download varies depending on network conditions.

PostgreSQL arrays

Using org.postgresql.Driver, these PostgreSQL array columns are readable:

PostgreSQL column type	StarRocks type
boolean[]	ARRAY<BOOLEAN>
smallint[]	ARRAY<SMALLINT>
integer[]	ARRAY<INT>
bigint[]	ARRAY<BIGINT>
real[]	ARRAY<FLOAT>
double precision[]	ARRAY<DOUBLE>
date[]	ARRAY<DATE>
timestamp[] (without time zone)	ARRAY<DATETIME>
text[], varchar[], char(n)[]	ARRAY<VARCHAR>

One-dimensional values preserve element order, UTF-8 strings, NULL arrays, empty arrays, and NULL elements. char(n)[] maps to ARRAY<VARCHAR> rather than ARRAY<CHAR>, and its elements keep the blank padding PostgreSQL stores. date[] and timestamp[] elements are read as wall-clock values, unchanged by the session time zone, and an element outside 0001-01-01 through 9999-12-31 -- a BC date, or infinity -- fails the query rather than reading as some other value.

Every other array element type maps to an unsupported type, and a column of that type cannot be selected. That includes numeric[], uuid[], time[], bytea[], json[] and jsonb[], and also timestamptz[]: reading a timestamp with time zone into ARRAY<DATETIME> would collapse the two instants of a daylight-saving fall-back onto one wall clock, and nothing downstream could tell them apart again.

StarRocks arrays use positions starting at 1. PostgreSQL lower bounds are not retained: for example, PostgreSQL [0:1]={a,b} becomes ["a","b"], so items[1] returns a in StarRocks. A constant subscript over such a column -- items[1], or the equivalent element_at(items, 1) -- is evaluated by PostgreSQL and only the element is returned, instead of the whole array being read back and indexed locally. The subscript is pushed down as written, assuming a lower bound of 1, which is the lower bound of every array PostgreSQL builds for itself. On a value stored with another lower bound, the pushed-down subscript therefore returns a different element than the same subscript evaluated in StarRocks, silently: on [0:2]={zero,one,two}, items[1] is one remotely and zero locally. Set enable_jdbc_array_lower_bound_correction to true to have the pushed-down subscript corrected for each value's own lower bound, so that it answers exactly what StarRocks answers. Set enable_jdbc_array_subscript_push_down to false to turn the push-down off altogether, on the filter and the projection path alike, and go back to reading the whole array column and taking the subscript in StarRocks. The two variables compose in one direction only: with the push-down off, enable_jdbc_array_lower_bound_correction has nothing left to govern, because no subscript reaches PostgreSQL. A variable subscript such as items[id], comparisons, joins, ordering, and grouping or distinct aggregation on whole array values execute in StarRocks. Reading a multidimensional value fails with an explicit unsupported-array error; such values are not flattened. A constant subscript over a column that holds one is the exception: it is pushed down like any other, and PostgreSQL answers NULL for a subscript that does not address every dimension, so the query returns NULL where reading the column would have failed. Neither setting of enable_jdbc_array_lower_bound_correction changes that -- PostgreSQL records no dimension on the column (attndims is not enforced and a column may hold values of different dimensions row by row), so there is nothing to gate on. Turning enable_jdbc_array_subscript_push_down off does change it: the column is read directly again, and the query fails with the unsupported-array error instead of returning NULL.

Examples

The following example creates two different JDBC catalogs.

-- PostgresSQL
CREATE EXTERNAL CATALOG jdbc0
PROPERTIES
(
    "type"="jdbc", 
    "user"="postgres",
    "password"="changeme",
    "jdbc_uri"="jdbc:postgresql://127.0.0.1:5432/jdbc_test",
    "driver_url"="https://repo1.maven.org/maven2/org/postgresql/postgresql/42.3.3/postgresql-42.3.3.jar",
    "driver_class"="org.postgresql.Driver"
);
-- MySQL
CREATE EXTERNAL CATALOG jdbc1
PROPERTIES
(
    "type"="jdbc",
    "user"="root",
    "password"="changeme",
    "jdbc_uri"="jdbc:mysql://127.0.0.1:3306",
    "driver_url"="https://repo1.maven.org/maven2/mysql/mysql-connector-java/8.0.28/mysql-connector-java-8.0.28.jar",
    "driver_class"="com.mysql.cj.jdbc.Driver"
);

View JDBC catalogs

You can use SHOW CATALOGS to query all catalogs in the current StarRocks cluster:

SHOW CATALOGS;

You can also use SHOW CREATE CATALOG to query the creation statement of an external catalog. The following example queries the creation statement of a JDBC catalog named jdbc0:

SHOW CREATE CATALOG jdbc0;

Drop a JDBC catalog

You can use DROP CATALOG to drop a JDBC catalog.

The following example drops a JDBC catalog named jdbc0:

DROP Catalog jdbc0;

Query a table in a JDBC catalog

1. Use SHOW DATABASES to view the databases in your JDBC-compatible cluster:

   SHOW DATABASES FROM <catalog_name>;

2. Use SET CATALOG to switch to the destination catalog in the current session:

   SET CATALOG <catalog_name>;

   Then, use USE to specify the active database in the current session:

   USE <db_name>;

   Or, you can use USE to directly specify the active database in the destination catalog:

   USE <catalog_name>.<db_name>;

3. Use SELECT to query the destination table in the specified database:

   SELECT * FROM <table_name>;

Join pushdown

From v4.2 onwards, the system can push multi-table joins (both INNER JOIN and CROSS JOIN) down to the external database when all joined tables come from the same JDBC catalog. This lets the source database execute the join using its own indexes and optimizer, and returns only the joined result back to the system.

Configure join pushdown

Join pushdown is enabled by default. Set the session variable enable_jdbc_join_push_down to false to disable it:

SET enable_jdbc_join_push_down = false;

How it works

When the rule fires, the optimizer rewrites the local join over multiple JDBC scans into a single JDBC scan that issues one SQL query containing a JOIN against the remote database.

For example, the following query against two MySQL tables in the same JDBC catalog:

SELECT o.order_id, c.region, o.amount
FROM jdbc0.db.orders o
JOIN jdbc0.db.customers c ON o.customer_id = c.customer_id
WHERE o.amount >= 20 AND c.region IN ('east', 'west');

When enable_jdbc_join_push_down is false, the system issues two separate scans and joins the result locally. When the variable is true, the whole join subtree is pushed down to the remote database. The local plan contains only one JDBC scan node, and the pushed-down SQL looks similar to:

SELECT `c1`, `c2`, `c3` FROM (
    SELECT t0.`order_id` AS c1, t1.`region` AS c2, t0.`amount` AS c3
    FROM (SELECT `order_id`, `customer_id`, `amount` FROM `db`.`orders` WHERE `amount` >= 20) t0
    INNER JOIN (SELECT `region`, `customer_id` FROM `db`.`customers` WHERE `region` IN ('east', 'west')) t1
      ON t0.`customer_id` = t1.`customer_id`
) sr_merged;

Use EXPLAIN on a query to verify whether the join was pushed down.

Scope and limitations

• Join type: INNER JOIN and CROSS JOIN are eligible for pushdown. Outer, semi, and anti joins fall back to local execution.
• Same catalog: all tables in a merged group must belong to the same JDBC catalog (same JDBC URI, credentials, and driver). Joins that mix two JDBC catalogs, or that mix JDBC tables with native tables, run locally.
• Cross-table predicate required: a group is pushed down only if it has at least one predicate that joins two tables in the group. Otherwise the rule skips it to avoid sending a pure cartesian product to the remote database.
• All-or-nothing predicate pushdown: every predicate owned by a group must be fully translatable to the remote dialect. If any predicate cannot be pushed (for example, a StarRocks-only function), the whole group falls back to a local join.
• Statistics: the merged JDBC scan reuses the same default statistics as a single-table JDBC scan. Downstream operators see the joined result as if it were one table of default size, and cardinality is not estimated from the underlying tables.

PostgreSQL date and timestamp values

PostgreSQL date and timestamp without time zone values retain their calendar fields when read through a JDBC catalog. The JVM's default time zone does not shift these values, and timestamps retain microsecond precision. The supported year range is 0001 through 9999 AD. Reading a BC date, infinity, -infinity, or a value outside this range fails with an error instead of changing its era or value.

Query JDBC data with native SQL

From v4.1 onwards, StarRocks supports querying JDBC data with database-native SELECT statements by using the native_query table function.

native_query is useful when the source database must run SQL that cannot be expressed as a single external table query, such as source-side joins, pre-filtered subqueries, or vendor-specific SQL syntax. StarRocks exposes the pass-through query result as a normal relation, so you can continue to apply StarRocks-side filters, joins, aggregations, and projections.

For syntax, limitations, and examples, see native_query.

FAQ

What do I do if an error suggesting "Malformed database URL, failed to parse the main URL sections" is thrown?

If you encounter such an error, the URI that you passed in jdbc_uri is invalid. Check the URI that you pass and make sure it is valid. For more information, see the parameter descriptions in the "PROPERTIES" section of this topic.