以外部表 (External Table) 的形式，接入其他数据源

外部表 | StarRocks https://docs.starrocks.io/zh/docs/data_source/External_table/

外部表

StarRocks 支持以外部表 (External Table) 的形式，接入其他数据源。外部表指的是保存在其他数据源中的数据表，而 StartRocks 只保存表对应的元数据，并直接向外部表所在数据源发起查询。目前 StarRocks 已支持的第三方数据源包括 MySQL、StarRocks、Elasticsearch、Apache Hive™、Apache Iceberg 和 Apache Hudi。对于 StarRocks 数据源，现阶段只支持 Insert 写入，不支持读取，对于其他数据源，现阶段只支持读取，还不支持写入。

NOTICE

从 3.0 版本起，对于查询 Hive、Iceberg、Hudi 数据源的场景，推荐使用 Catalog。参见 Hive catalog、Iceberg catalog、Hudi catalog。

从 3.1 版本起，对于查询 MySQL、PostgreSQL 的场景推荐使用 JDBC catalog，对于查询 Elasticsearch 的场景推荐使用 Elasticsearch catalog。

从 2.5 版本开始，查询外部数据源时支持 Data Cache，提升对热数据的查询性能。参见 Data Cache。

StarRocks 外部表

1.19 版本开始，StarRocks 支持将数据通过外表方式写入另一个 StarRocks 集群的表中。这可以解决用户的读写分离需求，提供更好的资源隔离。用户需要首先在目标集群上创建一张目标表，然后在源 StarRocks 集群上创建一个 Schema 信息一致的外表，并在属性中指定目标集群和表的信息。

通过 INSERT INTO 写入数据至 StarRocks 外表，可以将源集群的数据写入至目标集群。借助这一能力，可以实现如下目标：

集群间的数据同步。
读写分离。向源集群中写入数据，并且源集群的数据变更同步至目标集群，目标集群提供查询服务。

以下是创建目标表和外表的示例：

# 在目标集群上执行
CREATE TABLE t
(
    k1 DATE,
    k2 INT,
    k3 SMALLINT,
    k4 VARCHAR(2048),
    k5 DATETIME
)
ENGINE=olap
DISTRIBUTED BY HASH(k1);

# 在外表集群上执行
CREATE EXTERNAL TABLE external_t
(
    k1 DATE,
    k2 INT,
    k3 SMALLINT,
    k4 VARCHAR(2048),
    k5 DATETIME
)
ENGINE=olap
DISTRIBUTED BY HASH(k1)
PROPERTIES
(
    "host" = "127.0.0.1",
    "port" = "9020",
    "user" = "user",
    "password" = "passwd",
    "database" = "db_test",
    "table" = "t"
);

# 写入数据至 StarRocks 外表，实现源集群的数据写入至目标集群。推荐生产环境使用第二种方式。
insert into external_t values ('2020-10-11', 1, 1, 'hello', '2020-10-11 10:00:00');

insert into external_t select * from other_table;
 

其中：

EXTERNAL：该关键字指定创建的是 StarRocks 外表。
host：该属性描述目标表所属 StarRocks 集群 Leader FE 的 IP 地址。
port：该属性描述目标表所属 StarRocks 集群 FE 的 RPC 访问端口。
备注
为确保外表所属集群能够正常访问目标表所属 StarRocks 集群，您需要确保网络策略和防火墙设置允许以下端口的访问：
- FE 的 RPC 访问端口，可参考配置文件 fe/fe.conf 中的 rpc_port 配置取值，默认为 9020。
- BE 的 bRPC 访问端口，可参考配置文件 be/be.conf 中的 brpc_port 配置取值，默认为 8060。
user：该属性描述目标表所属 StarRocks 集群的访问用户名。
password：该属性描述目标表所属 StarRocks 集群的访问密码。
database：该属性描述目标表所属数据库名称。
table：该属性描述目标表名称。

目前 StarRocks 外表使用上有以下限制：

仅可以在外表上执行 insert into 和 show create table 操作，不支持其他数据写入方式，也不支持查询和 DDL。
创建外表语法和创建普通表一致，但其中的列名等信息请保持同其对应的目标表一致。
外表会周期性从目标表同步元信息（同步周期为 10 秒），在目标表执行的 DDL 操作可能会延迟一定时间反应在外表上。

更多数据库（JDBC）的外部表

自 2.3.0 版本起，StarRocks 支持通过外部表的方式查询支持 JDBC 的数据库，无需将数据导入至 StarRocks，即可实现对这类数据库的极速分析。本文介绍如何在 StarRocks 创建外部表，查询支持 JDBC 的数据库中的数据。

前提条件

在您使用 JDBC 外表时， FE、BE 节点会下载 JDBC 驱动程序，因此 FE、BE 节点所在机器必须能够访问用于下载 JDBC 驱动程序 JAR 包的 URL，该 URL 由创建 JDBC 资源中的配置项 driver_url 指定。

创建和管理 JDBC 资源

创建 JDBC 资源

您需要提前在 StarRocks 中创建 JDBC 资源，用于管理数据库的相关连接信息。这里的数据库是指支持 JDBC 的数据库，以下简称为“目标数据库”。创建资源后，即可使用该资源创建外部表。

例如目标数据库为 PostgreSQL，则可以执行如下语句，创建一个名为 jdbc0 的 JDBC 资源，用于访问 PostgreSQL：

CREATE EXTERNAL RESOURCE 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"
);
 

PROPERTIES 的必填配置项：

type：资源类型，固定取值为 jdbc。
user：目标数据库用户名。
password：目标数据库用户登录密码。
jdbc_uri：JDBC 驱动程序连接目标数据库的 URI，需要满足目标数据库 URI 的语法。常见的目标数据库 URI，请参见 MySQL、Oracle、PostgreSQL、SQL Server 官网文档。

说明

目标数据库 URI 中必须指定具体数据库的名称，如上示例中的 jdbc_test。
driver_url：用于下载 JDBC 驱动程序 JAR 包的 URL，支持使用 HTTP 协议或者 file 协议。例如https://repo1.maven.org/maven2/org/postgresql/postgresql/42.3.3/postgresql-42.3.3.jar，file:///home/disk1/postgresql-42.3.3.jar。

说明

不同目标数据库使用的 JDBC 驱动程序不同，使用其他数据库的 JDBC 驱动程序会有不兼容的问题，建议访问目标数据库官网，查询并使用其支持的 JDBC 驱动程序。常见的目标数据库的 JDBC 驱动程序下载地址，请参见 MySQL、Oracle、PostgreSQL、SQL Server 。
driver_class：JDBC 驱动程序的类名称。

以下列举常见 JDBC 驱动程序的类名称：
- MySQL: com.mysql.jdbc.Driver（MySQL 5.x 及以下版本）、com.mysql.cj.jdbc.Driver（MySQL 8.x 及以上版本）
- SQL Server：com.microsoft.sqlserver.jdbc.SQLServerDriver
- Oracle: oracle.jdbc.driver.OracleDriver
- PostgreSQL：org.postgresql.Driver

创建资源时，FE 通过 driver_url 下载 JDBC 驱动程序 JAR 包，生成 checksum 并保存起来，用于校验 BE 下载的 JDBC 驱动程序 JAR 包的正确性。

说明

如果下载 JDBC 驱动程序失败，则创建资源也会失败。

BE 节点首次查询 JDBC 外部表时，如果发现所在机器上不存在相应的 JDBC 驱动程序 JAR 包，则会通过 driver_url 进行下载，所有的 JDBC 驱动程序 JAR 包都会保存在 ${STARROCKS_HOME}/lib/jdbc_drivers 目录下。

查看 JDBC 资源

执行如下语句，查看 StarRocks 中的所有 JDBC 资源：

说明

ResourceType 列为 jdbc。

SHOW RESOURCES;
 

删除 JDBC 资源

执行如下语句，删除名为 jdbc0 的 JDBC 资源：

DROP RESOURCE "jdbc0";
 

说明

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

创建数据库

执行如下语句，在 StarRocks 中创建并进入名为 jdbc_test 的数据库：

CREATE DATABASE jdbc_test; 
USE jdbc_test; 
 

说明

库名无需与目标数据库的名称保持一致。

创建 JDBC 外部表

执行如下语句，在数据库 jdbc_test 中，创建一张名为 jdbc_tbl 的 JDBC 外部表：

CREATE EXTERNAL TABLE jdbc_tbl (
    `id` bigint NULL, 
    `data` varchar(200) NULL 
) ENGINE=jdbc 
PROPERTIES (
    "resource" = "jdbc0",
    "table" = "dest_tbl"
);
 

PROPERTIES 配置项：

resource：所使用 JDBC 资源的名称，必填项。
table：目标数据库表名，必填项。

支持的数据类型以及与 StarRocks 的数据类型映射关系，请参见数据类型映射。

说明

不支持索引。

不支持通过 PARTITION BY、DISTRIBUTED BY 来指定数据分布规则。

查询 JDBC 外部表

查询 JDBC 外部表前，必须启用 Pipeline 引擎。

说明

如果已经启用 Pipeline 引擎，则可跳过本步骤。

set enable_pipeline_engine=true;
 

执行如下语句，通过 JDBC 外部表查询目标数据库的数据：

select * from jdbc_tbl;
 

StarRocks 支持对目标表进行谓词下推，把过滤条件推给目标表执行，让执行尽量靠近数据源，进而提高查询性能。目前支持下推运算符，包括二元比较运算符（>、>=、=、<、<=）、IN、IS NULL 和 BETWEEN ... AND ...，但是不支持下推函数。

posted @ 2024-10-31 19:06 papering 阅读(60) 评论(0) 编辑收藏举报

刷新页面返回顶部

papering

以外部表 (External Table) 的形式，接入其他数据源

外部表

StarRocks 外部表​

更多数据库（JDBC）的外部表​

前提条件​

创建和管理 JDBC 资源​

创建 JDBC 资源​

查看 JDBC 资源​

删除 JDBC 资源​

创建数据库​

创建 JDBC 外部表​

查询 JDBC 外部表​