[Preview] 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 by using INSERT INTO based on JDBC catalogs.

JDBC catalogs currently support MySQL and PostgreSQL.

Prerequisites

  • The FEs and BEs in your CelerData cluster can download the JDBC driver from the download URL specified by the driver_url parameter.
  • JAVA_HOME in the $BE_HOME/bin/start_be.sh file on each BE node is properly configured as a path in the JDK environment instead of a path in the JRE environment. For example, you can configure export JAVA_HOME = <JDK_absolute_path>.

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:

ParameterDescription
typethe type of the resource. Set the value to jdbc.
userthe username that is used to connect to the target database.
passwordthe password that is used to connect to the target database.
jdbc_urithe 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, visit the official websites of MySQL and PostgreSQL.
driver_urlthe download URL of the JDBC driver JAR package. An HTTP URL or file URL is supported, for example, https://repo1.maven.org/maven2/org/postgresql/postgresql/42.3.3/postgresql-42.3.3.jar and file:///home/disk1/postgresql-42.3.3.jar.
NOTE
You can also put the JDBC driver to any same path on the FE and BE nodes and set driver_url to that path, which must be in the file://<path>/to/the/driver format.
driver_classthe 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

NOTE

The FEs download the JDBC driver JAR package at the time of JDBC catalog creation, and the BEs 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.

Examples

The following example creates two JDBC catalogs: one named jdbc0, and the other named jdbc1.

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"
);

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 CelerData 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 <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>;

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.

In this article
Copyright © 2024 CelerData