Create, manage, and use a JDBC Catalog - E-MapReduce

A JDBC Catalog is an External Catalog that lets you query MySQL and PostgreSQL databases directly from EMR Serverless StarRocks—no data movement required. JDBC Catalog is supported from StarRocks v3.0.

With a JDBC Catalog, you can:

Query data directly in MySQL, PostgreSQL, and data sources compatible with their protocols.
Load data from JDBC sources into StarRocks tables using INSERT INTO.

Create a JDBC catalog

Syntax

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

Parameters

catalog_name: Required. The name of the JDBC catalog.
- Can contain letters, digits, and underscores (_). Must start with a letter.
- Length: 1–64 characters.
comment: Optional. A description of the JDBC catalog.
PROPERTIES: Connection properties for the JDBC catalog. All properties in the following table are required.

Property	Description
`type`	The data source type. Set to `jdbc`.
`user`	The username used to connect to the database.
`password`	The password used to connect to the database.
`jdbc_uri`	The connection URI for the JDBC driver. Use `"jdbc:mysql://ip:port"` for MySQL or `"jdbc:postgresql://ip:port/db_name"` for PostgreSQL.
`driver_url`	The URL for downloading the JDBC driver JAR package. For a Serverless StarRocks instance, upload the JAR package to Object Storage Service (OSS) with public-read access, then set this to an HTTP URL using the internal endpoint of the OSS bucket. Example: `http://<YourBucketName>.oss-cn-xxxx-internal.aliyuncs.com/mysql-connector-java-*.jar`. For instructions on uploading the JAR package, see Simple upload. To configure the required access permissions, see Set the ACL for a bucket.
`driver_class`	The class name of the JDBC driver. For MySQL 5.x and earlier, use `com.mysql.jdbc.Driver`. For MySQL 6.x and later, use `com.mysql.cj.jdbc.Driver`. For PostgreSQL, use `org.postgresql.Driver`.

Example

-- MySQL
CREATE EXTERNAL CATALOG mysql_catalog
PROPERTIES
(
    "type"="jdbc",
    "user"="root",
    "password"="xxxxx",
    "jdbc_uri"="jdbc:mysql://127.0.0.1:3306",
    "driver_url"="http://<YourBucketName>.oss-cn-xxxx-internal.aliyuncs.com/mysql-connector-java-*.jar",
    "driver_class"="com.mysql.cj.jdbc.Driver"
);

Note

Replace mysql-connector-java-*.jar with the actual versioned filename of your MySQL JDBC driver JAR package.

View JDBC catalogs

To list all catalogs in the current StarRocks cluster:

SHOW CATALOGS;

To view the creation statement of a specific catalog—for example, jdbc0:

SHOW CREATE CATALOG jdbc0;

Delete a JDBC catalog

To delete a JDBC catalog—for example, jdbc0:

DROP CATALOG jdbc0;

Query data through a JDBC catalog

Option 1: Switch context, then query

List the databases in the catalog:
```
SHOW DATABASES FROM <catalog_name>;
```
Switch to the catalog and set the active database:
```
SET CATALOG <catalog_name>;
USE <db_name>;
```
Alternatively, switch to the database directly:
```
USE <catalog_name>.<db_name>;
```
Run a query against the target table:
```
SELECT * FROM <table_name>;
```

Option 2: Fully qualified table name

Query without switching context by using a three-part name:

SELECT * FROM <catalog_name>.<database_name>.<table_name>;

FAQ

Q: How do I resolve "Malformed database URL, failed to parse the main URL sections"?

The jdbc_uri value is malformed. Check the format:

MySQL: jdbc:mysql://ip:port
PostgreSQL: jdbc:postgresql://ip:port/db_name

Make sure there are no extra spaces, missing colons, or incorrect slashes. See the jdbc_uri parameter description above for details.