StarRocks 外表最佳实践

StarRocks 外表最佳实践

自 2..0 版本起，StarRocks 支持通过外部表的方式查询支持 JDBC 的数据库，无需将数据导入至 StarRocks，即可实现对这类数据库的极速分析。从 .1 版本起，对于查询 MySQL、PostgreSQL 的场景推荐使用 JDBC catalog。

文档：/

基于JDBC管理外表

用户需要显式地创建一个外部资源（RESOURCE），并在创建外表时引用这个资源。

适用场景：

• 多表连接同一数据库：当需要从StarRocks连接到多个外部表，且这些外部表都指向同一个外部数据库时，使用RESOURCE可以提高效率和可维护性。
• 频繁变更连接信息：如果连接信息可能会频繁变更，使用RESOURCE可以在一个地方更新，而不需要修改每一个外部表的定义。

使用限制

• 创建 JDBC 外部表时，不支持索引，也不支持通过 PARTITIO BY、DISTRIBUTED BY 来指定数据分布规则。
• 查询 JDBC 外部表时，不支持下推函数。StarRocks支持对目标表进行谓词下推，把过滤条件推给目标表执行，但是不支持下推函数。这意味着对于JDBC外部表，StarRocks可以将一些基础的比较运算符（如>、>=、=、<、<=）、I、IS ULL和BETWEE ... AD ...等下推到外部数据库执行，但是不支持将函数下推到外部数据库执行。不支持下推函数的原因可能是因为外部数据库可能不支持这些函数，或者StarRocks与外部数据库之间的数据类型映射和函数兼容性存在问题。

创建JDBC资源

注意：名字中不能带'-'。

创建MySQL资源

代码语言：javascript代码运行次数：0运行复制

CREATE EXTERAL RESOURCE jdbc_mysql
PROPERTIES (
    "type" = "jdbc",
    "user" = "root",
    "password" = "changeme",
    "jdbc_uri" = "jdbc:mysql://127.0.0.1:06/jdbc_test",
    "driver_url"=".0.28/mysql-connector-java-8.0.28.jar",
    "driver_class"="jdbc.Driver"
);

创建PostgreSQL资源

代码语言：javascript代码运行次数：0运行复制

CREATE EXTERAL RESOURCE jdbc_postgresql
PROPERTIES (
    "type" = "jdbc",
    "user" = "postgres",
    "password" = "changeme",
    "jdbc_uri" = "jdbc:postgresql://127.0.0.1:542/jdbc_test",
    "driver_url" = "../postgresql-42...jar",
    "driver_class" = "org.postgresql.Driver"
);

创建外表

MySQL创建表

代码语言：javascript代码运行次数：0运行复制

CREATE TABLE `users` (
  `id` int OT ULL,
  `name` varchar(45) OT ULL,
  PRIMARY KEY (`id`)
) EGIE=InnoDB DEFAULT CHARSET=latin1;

注意：MySQL中的DDL语句如修改列，不会同步到StarRocks。

若JDBC资源中没有在jdbc_uri指定database，则需要在创建外表的PROPERTIES中指定database。

代码语言：javascript代码运行次数：0运行复制

CREATE EXTERAL TABLE mysql_external_table_users(
  `id` bigint  OT ULL AUTO_ICREMET ,
  `name` varchar(45) OT ULL
) EGIE=jdbc 
PROPERTIES (
    "resource" = "jdbc_mysql",
    "table" = "users"
);

创建外表报错如下，这种方式创建的外表的所有字段必须可以为空。

代码语言：javascript代码运行次数：0运行复制

Error Code: 1064. Getting analyzing error. Detail message: All columns must be nullable for external table. Column id is not nullable, You can rebuild the external table and We strongly recommend that you use catalog to access external data.

需要把StarRocks中的外表字段设置为DEFAULT ULL，才可以创建成功。

代码语言：javascript代码运行次数：0运行复制

CREATE EXTERAL TABLE mysql_external_table_users(
  `id` bigint  DEFAULT ULL,
  `name` varchar(45) DEFAULT ULL
) EGIE=jdbc 
PROPERTIES (
    "resource" = "jdbc_mysql",
    "table" = "users"
);

管理JDBC资源

查看JDBC资源。

代码语言：javascript代码运行次数：0运行复制

SHOW RESOURCES;

删除指定JDBC，如"jdbc0"，删除 JDBC 资源会导致使用该 JDBC 资源创建的 JDBC 外部表不可用，但目标数据库的数据并不会丢失。如果您仍需要通过 StarRocks 查询目标数据库的数据，可以重新创建 JDBC 资源和 JDBC 外部表。

代码语言：javascript代码运行次数：0运行复制

DROP RESOURCE "jdbc0";

基于连接信息管理外表（已弃用）

文档：

创建MySQL外表

代码语言：javascript代码运行次数：0运行复制

CREATE EXTERAL TABLE `mysql_external_table_users` (
  `id` bigint  OT ULL AUTO_ICREMET ,
  `name` varchar(45) OT ULL
) EGIE=MYSQL 
PROPERTIES (
"host" = "9.x.x.x",
"port" = "06",
"user" = "root",
"password" = "x",
"database" = "test_db",
"table" = "users"
);

延伸

JDBC Catalog

StarRocks 从 .0 版本开始支持 JDBC Catalog。

JDBC Catalog 是一种 External Catalog。通过 JDBC Catalog，您不需要执行数据导入就可以直接查询 JDBC 数据源里的数据。

文档：/

创建CATALOG 和创建JDBC Resource很类似，但是这种方式不需要创建外表，直接查询对应数据库。

代码语言：javascript代码运行次数：0运行复制

CREATE EXTERAL CATALOG jdbc0
PROPERTIES
(
    "type"="jdbc",
    "user"="postgres",
    "password"="changeme",
    "jdbc_uri"="jdbc:postgresql://127.0.0.1:542/jdbc_test",
    "driver_url"="../postgresql-42...jar",
    "driver_class"="org.postgresql.Driver"
);

查询当前所在 StarRocks 集里所有 Catalog

代码语言：javascript代码运行次数：0运行复制

SHOW CATALOGS;

查询某个 External Catalog 的创建语句

代码语言：javascript代码运行次数：0运行复制

SHOW CREATE CATALOG jdbc0;

删除一个 JDBC Catalog

代码语言：javascript代码运行次数：0运行复制

DROP Catalog jdbc0;

查看指定 Catalog 所属的集中的数据库

代码语言：javascript代码运行次数：0运行复制

SHOW DATABASES FROM <catalog_name>;

切换当前会话生效的 Catalog

代码语言：javascript代码运行次数：0运行复制

SET CATALOG <catalog_name>;

指定当前会话生效的数据库，也可以直接将会话切换到目标 Catalog 下的指定数据库

代码语言：javascript代码运行次数：0运行复制

USE <db_name>;
USE <catalog_name>.<db_name>;

查询目标数据库中的目标表

代码语言：javascript代码运行次数：0运行复制

SELECT * FROM <table_name>;