When MySQL, PostgreSQL, and Oracle Argue: Doris JDBC Catalog Acts as the Peacemaker

At noon, Xiao Wang was staring at his computer screen, looking worried.

He is in charge of the company's data platform and recently received a task: to perform real-time analysis on data from three different databases—MySQL, PostgreSQL, and Oracle.

"I have to write three sets of ETL programs to synchronize the data into Doris. What a workload...", Xiao Wang rubbed his sore eyes.

"Why make it so complicated? Use JDBC Catalog!" his colleague Xiao Li's voice came from behind.

Xiao Wang looked puzzled: "What is this magical JDBC Catalog?"

Xiao Li smiled: "It's like a universal key in the data world, opening the doors to multiple databases with one key."

The Magic Tool to Break Data Silos

The core charm of Doris JDBC Catalog lies in its ability to connect to a variety of databases through the standard JDBC interface, including MySQL, PostgreSQL, Oracle, SQL Server, IBM Db2, ClickHouse, SAP HANA, and OceanBase.

Let's look at a practical example:

CREATE CATALOG mysql_source PROPERTIES (

    "type"="jdbc",

    "user"="root",

    "password"="secret",

    "jdbc_url" = "jdbc:mysql://example.net:3306",

    "driver_url" = "mysql-connector-j-8.3.0.jar",

    "driver_class" = "com.mysql.cj.jdbc.Driver"

)

That's it!

Once created, you can query tables in MySQL just like querying local Doris tables:

-- Show all databases
SHOW DATABASES FROM mysql_source;

-- Show tables in the test database
SHOW TABLES FROM mysql_source.test;

-- Directly query a table in MySQL
SELECT * FROM mysql_source.test.users;

For data analysts, this means no more writing complex ETL processes, no more worrying about data consistency, and no additional storage costs.

The data remains stored in the original databases, but you can query them as if they were local tables.

The Magic Formula of JDBC Catalog

To unleash the full power of JDBC Catalog, you need to understand its key configuration options.

Driver Configuration

First, the driver package path can be specified in three ways:

1. Filename only, such as mysql-connector-j-8.3.0.jar. The system will look for it in the jdbc_drivers/ directory.
2. Local absolute path, such as file:///path/to/mysql-connector-j-8.3.0.jar.
3. HTTP address. The system will automatically download the driver file.

Tip:
To better manage driver packages, you can use the jdbc_driver_secure_path FE configuration item to allow driver package paths, enhancing security.

Connection Pool Tuning

The connection pool has a significant impact on performance. For example, establishing a new connection for each query is like having to apply for a new access card every time you enter the office—it's cumbersome!

Key parameters include:

• connection_pool_min_size: Minimum number of connections (default is 1).
• connection_pool_max_size: Maximum number of connections (default is 30).
• connection_pool_max_wait_time: Maximum milliseconds to wait for a connection (default is 5000).
• connection_pool_max_life_time: Maximum lifecycle of a connection (default is 1800000 milliseconds).

-- Adjust connection pool size based on load
ALTER CATALOG mysql_source SET PROPERTIES (
    'connection_pool_max_size' = '50',
    'connection_pool_max_wait_time' = '10000'
);

Advanced Usage: Statement Pass-Through

Xiao Wang curiously asked, "What if I want to perform some DDL or DML operations in MySQL?"

Xiao Li smiled mysteriously: "Doris provides a statement pass-through feature that allows you to execute native SQL statements directly in the data source."

DDL and DML Pass-Through

Currently, only DDL and DML statements are supported, and you must use the syntax corresponding to the data source.

-- Insert data
CALL EXECUTE_STMT("mysql_source", "INSERT INTO users VALUES(1, 'Zhang San'), (2, 'Li Si')");

-- Delete data
CALL EXECUTE_STMT("mysql_source", "DELETE FROM users WHERE id = 2");

-- Create a table
CALL EXECUTE_STMT("mysql_source", "CREATE TABLE new_users (id INT, name VARCHAR(50))");

-- Use the query table function to execute a native query
SELECT * FROM query(
  "catalog" = "mysql_source", 
  "query" = "SELECT id, name FROM users WHERE id > 10"
);

-- Increase the maximum number of connections
ALTER CATALOG mysql_source SET PROPERTIES ('connection_pool_max_size' = '100');

-- Increase the wait time
ALTER CATALOG mysql_source SET PROPERTIES ('connection_pool_max_wait_time' = '10000');