StarRocks 外表最佳实践
原创StarRocks 外表最佳实践
原创
自 2.3.0 版本起，StarRocks 支持通过外部表的方式查询支持 JDBC 的数据库，无需将数据导入至 StarRocks，即可实现对这类数据库的极速分析。从 3.1 版本起，对于查询 MySQL、PostgreSQL 的场景推荐使用 JDBC catalog。
文档：https://docs.starrocks.io/zh/docs/data_source/External_table/
基于JDBC管理外表
用户需要显式地创建一个外部资源（RESOURCE），并在创建外表时引用这个资源。
适用场景：
- 多表连接同一数据库：当需要从StarRocks连接到多个外部表，且这些外部表都指向同一个外部数据库时，使用
RESOURCE可以提高效率和可维护性。 - 频繁变更连接信息：如果连接信息可能会频繁变更，使用
RESOURCE可以在一个地方更新，而不需要修改每一个外部表的定义。
使用限制
- 创建 JDBC 外部表时，不支持索引，也不支持通过 PARTITION BY、DISTRIBUTED BY 来指定数据分布规则。
- 查询 JDBC 外部表时，不支持下推函数。StarRocks支持对目标表进行谓词下推，把过滤条件推给目标表执行，但是不支持下推函数。这意味着对于JDBC外部表，StarRocks可以将一些基础的比较运算符（如
>、>=、=、<、<=）、IN、IS NULL和BETWEEN ... AND ...等下推到外部数据库执行，但是不支持将函数下推到外部数据库执行。不支持下推函数的原因可能是因为外部数据库可能不支持这些函数，或者StarRocks与外部数据库之间的数据类型映射和函数兼容性存在问题。
创建JDBC资源
注意：名字中不能带'-'。
创建MySQL资源
CREATE EXTERNAL RESOURCE jdbc_mysql
PROPERTIES (
"type" = "jdbc",
"user" = "root",
"password" = "changeme",
"jdbc_uri" = "jdbc:mysql://127.0.0.1:3306/jdbc_test",
"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"
);创建PostgreSQL资源
CREATE EXTERNAL RESOURCE jdbc_postgresql
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 TABLE `users` (
`id` int NOT NULL,
`name` varchar(45) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;注意：MySQL中的DDL语句如修改列，不会同步到StarRocks。
若JDBC资源中没有在jdbc_uri指定database，则需要在创建外表的PROPERTIES中指定database。
CREATE EXTERNAL TABLE mysql_external_table_users(
`id` bigint NOT NULL AUTO_INCREMENT ,
`name` varchar(45) NOT NULL
) ENGINE=jdbc
PROPERTIES (
"resource" = "jdbc_mysql",
"table" = "users"
);创建外表报错如下，这种方式创建的外表的所有字段必须可以为空。
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 NULL，才可以创建成功。
CREATE EXTERNAL TABLE mysql_external_table_users(
`id` bigint DEFAULT NULL,
`name` varchar(45) DEFAULT NULL
) ENGINE=jdbc
PROPERTIES (
"resource" = "jdbc_mysql",
"table" = "users"
);管理JDBC资源
查看JDBC资源。
SHOW RESOURCES;删除指定JDBC，如"jdbc0"，删除 JDBC 资源会导致使用该 JDBC 资源创建的 JDBC 外部表不可用，但目标数据库的数据并不会丢失。如果您仍需要通过 StarRocks 查询目标数据库的数据，可以重新创建 JDBC 资源和 JDBC 外部表。
DROP RESOURCE "jdbc0";基于连接信息管理外表（已弃用）
文档：https://docs.starrocks.io/zh/docs/data_source/External_table/#deprecated-mysql-%E5%A4%96%E9%83%A8%E8%A1%A8
创建MySQL外表
CREATE EXTERNAL TABLE `mysql_external_table_users` (
`id` bigint NOT NULL AUTO_INCREMENT ,
`name` varchar(45) NOT NULL
) ENGINE=MYSQL
PROPERTIES (
"host" = "9.x.x.x",
"port" = "3306",
"user" = "root",
"password" = "x",
"database" = "test_db",
"table" = "users"
);延伸
JDBC Catalog
StarRocks 从 3.0 版本开始支持 JDBC Catalog。
JDBC Catalog 是一种 External Catalog。通过 JDBC Catalog，您不需要执行数据导入就可以直接查询 JDBC 数据源里的数据。
文档：https://docs.starrocks.io/zh/docs/data_source/catalog/jdbc_catalog/
创建CATALOG 和创建JDBC Resource很类似，但是这种方式不需要创建外表，直接查询对应数据库。
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"
);查询当前所在 StarRocks 集群里所有 Catalog
SHOW CATALOGS;查询某个 External Catalog 的创建语句
SHOW CREATE CATALOG jdbc0;删除一个 JDBC Catalog
DROP Catalog jdbc0;查看指定 Catalog 所属的集群中的数据库
SHOW DATABASES FROM <catalog_name>;切换当前会话生效的 Catalog
SET CATALOG <catalog_name>;指定当前会话生效的数据库，也可以直接将会话切换到目标 Catalog 下的指定数据库
USE <db_name>;
USE <catalog_name>.<db_name>;查询目标数据库中的目标表
SELECT * FROM <table_name>;原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
腾讯云开发者
