版本: 最新版本-3.5

外部表

注意

从 v3.0 版本开始，我们建议您使用 Catalog 查询 Hive、Iceberg 和 Hudi 中的数据。参见 Hive catalog、Iceberg catalog 和 Hudi catalog。
从 v3.1 版本开始，我们建议您使用 JDBC catalog 查询 MySQL 和 PostgreSQL 中的数据，使用 Elasticsearch catalog 查询 Elasticsearch 中的数据。
外部表功能旨在帮助将数据加载到 StarRocks 中，而不是作为正常操作对外部系统执行高效查询。更高效的解决方案是将数据加载到 StarRocks 中。

StarRocks 支持通过外部表访问其他数据源。外部表基于存储在其他数据源中的数据表创建。StarRocks 仅存储数据表的元数据。您可以使用外部表直接查询其他数据源中的数据。目前，除了 StarRocks 外部表，所有其他外部表都已弃用。您只能将来自另一个 StarRocks 集群的数据写入到当前的 StarRocks 集群。您无法从中读取数据。对于 StarRocks 以外的数据源，您只能从这些数据源读取数据。

从 2.5 版本开始，StarRocks 提供了数据缓存功能，可加速外部数据源上的热数据查询。更多信息，请参见数据缓存。

StarRocks 外部表

从 StarRocks 1.19 版本开始，StarRocks 允许您使用 StarRocks 外部表将数据从一个 StarRocks 集群写入到另一个集群。这实现了读写分离，并提供了更好的资源隔离。您可以首先在目标 StarRocks 集群中创建目标表。然后，在源 StarRocks 集群中，您可以创建一个具有与目标表相同 schema 的 StarRocks 外部表，并在 PROPERTIES 字段中指定目标集群和表的信息。

可以使用 INSERT INTO 语句将数据从源集群写入到目标集群，以写入到 StarRocks 外部表。它可以帮助实现以下目标

StarRocks 集群之间的数据同步。
读写分离。数据写入到源集群，源集群的数据更改同步到目标集群，目标集群提供查询服务。

以下代码显示了如何创建目标表和外部表。

# Create a destination table in the destination StarRocks cluster.
CREATE TABLE t
(
    k1 DATE,
    k2 INT,
    k3 SMALLINT,
    k4 VARCHAR(2048),
    k5 DATETIME
)
ENGINE=olap
DISTRIBUTED BY HASH(k1);

# Create an external table in the source StarRocks cluster.
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"
);

# Write data from a source cluster to a destination cluster by writing data into the StarRocks external table. The second statement is recommended for the production environment.
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: 此关键字指示要创建的表是外部表。
host: 此参数指定目标 StarRocks 集群的 Leader FE 节点的 IP 地址。
port: 此参数指定目标 StarRocks 集群的 FE 节点的 RPC 端口。
注意
为确保 StarRocks 外部表所属的源集群可以访问目标 StarRocks 集群，您必须配置您的网络和防火墙以允许访问以下端口
- FE 节点的 RPC 端口。参见 FE 配置文件 fe/fe.conf 中的 rpc_port。默认的 RPC 端口是 9020。
- BE 节点的 bRPC 端口。参见 BE 配置文件 be/be.conf 中的 brpc_port。默认的 bRPC 端口是 8060。
user: 此参数指定用于访问目标 StarRocks 集群的用户名。
password: 此参数指定用于访问目标 StarRocks 集群的密码。
database: 此参数指定目标表所属的数据库。
table: 此参数指定目标表的名称。

当您使用 StarRocks 外部表时，以下限制适用

您只能在 StarRocks 外部表上运行 INSERT INTO 和 SHOW CREATE TABLE 命令。不支持其他数据写入方法。此外，您无法从 StarRocks 外部表查询数据或对外部表执行 DDL 操作。
创建外部表的语法与创建普通表的语法相同，但外部表中的列名和其他信息必须与目标表相同。
外部表每 10 秒从目标表同步表元数据。如果在目标表上执行 DDL 操作，两个表之间的数据同步可能会有延迟。

(已弃用) 用于 JDBC 兼容数据库的外部表

从 v2.3.0 版本开始，StarRocks 提供了外部表来查询 JDBC 兼容数据库。这样，您无需将数据导入 StarRocks 即可快速分析此类数据库中的数据。本节介绍如何在 StarRocks 中创建外部表以及查询 JDBC 兼容数据库中的数据。

前提条件

在使用 JDBC 外部表查询数据之前，请确保 FE 和 BE 可以访问 JDBC 驱动程序的下载 URL。下载 URL 由用于创建 JDBC 资源的语句中的 driver_url 参数指定。

创建和管理 JDBC 资源

创建 JDBC 资源

在创建外部表以查询数据库中的数据之前，您需要在 StarRocks 中创建 JDBC 资源以管理数据库的连接信息。数据库必须支持 JDBC 驱动程序，并被称为“目标数据库”。创建资源后，您可以使用它来创建外部表。

执行以下语句以创建名为 jdbc0 的 JDBC 资源

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 语法。对于一些常见数据库的 URI 语法，请访问 Oracle、PostgreSQL、SQL Server 的官方网站。

注意：URI 必须包含目标数据库的名称。例如，在前面的代码示例中，jdbc_test 是您要连接的目标数据库的名称。

driver_url: JDBC 驱动程序 JAR 包的下载 URL。支持 HTTP URL 或文件 URL，例如，https://repo1.maven.org/maven2/org/postgresql/postgresql/42.3.3/postgresql-42.3.3.jar 或 file:///home/disk1/postgresql-42.3.3.jar。
driver_class: JDBC 驱动程序的类名。常见数据库的 JDBC 驱动程序类名如下
- MySQL: com.mysql.jdbc.Driver (MySQL 5.x 及更早版本), com.mysql.cj.jdbc.Driver (MySQL 6.x 及更高版本)
- SQL Server: com.microsoft.sqlserver.jdbc.SQLServerDriver
- Oracle: oracle.jdbc.driver.OracleDriver
- PostgreSQL: org.postgresql.Driver

创建资源时，FE 使用 driver_url 参数中指定的 URL 下载 JDBC 驱动程序 JAR 包，生成校验和，并使用校验和验证 BE 下载的 JDBC 驱动程序。

注意：如果 JDBC 驱动程序 JAR 包的下载失败，则资源的创建也会失败。

当 BE 第一次查询 JDBC 外部表并发现其机器上不存在相应的 JDBC 驱动程序 JAR 包时，BE 使用 driver_url 参数中指定的 URL 下载 JDBC 驱动程序 JAR 包，所有 JDBC 驱动程序 JAR 包都保存在 ${STARROCKS_HOME}/lib/jdbc_drivers 目录中。

查看 JDBC 资源

执行以下语句以查看 StarRocks 中的所有 JDBC 资源

SHOW RESOURCES;

注意：ResourceType 列是 jdbc。

删除 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 和目标数据库之间的数据类型映射，请参见 [数据类型映射](External_table.md#Data type mapping)。

注意

不支持索引。

您不能使用 PARTITION BY 或 DISTRIBUTED BY 来指定数据分布规则。

查询 JDBC 外部表

在查询 JDBC 外部表之前，您必须执行以下语句以启用 Pipeline 引擎

set enable_pipeline_engine=true;

注意：如果 Pipeline 引擎已启用，您可以跳过此步骤。

执行以下语句以使用 JDBC 外部表查询目标数据库中的数据。

select * from JDBC_tbl;

StarRocks 通过将过滤条件推送到目标表来支持谓词下推。尽可能接近数据源执行过滤条件可以提高查询性能。目前，StarRocks 可以下推运算符，包括二元比较运算符 (>, >=, =, <, 和 <=)，IN, IS NULL, 和 BETWEEN ... AND ...。但是，StarRocks 不能下推函数。

数据类型映射

目前，StarRocks 只能查询目标数据库中的基本类型的数据，例如 NUMBER, STRING, TIME, 和 DATE。如果目标数据库中的数据值的范围不受 StarRocks 支持，则查询会报告错误。

目标数据库和 StarRocks 之间的映射因目标数据库的类型而异。

MySQL 和 StarRocks

MySQL	StarRocks
BOOLEAN	BOOLEAN
TINYINT	TINYINT
SMALLINT	SMALLINT
MEDIUMINTINT	INT
BIGINT	BIGINT
FLOAT	FLOAT
DOUBLE	DOUBLE
DECIMAL	DECIMAL
CHAR	CHAR
VARCHAR	VARCHAR
DATE	DATE
DATETIME	DATETIME

Oracle 和 StarRocks

Oracle	StarRocks
CHAR	CHAR
VARCHARVARCHAR2	VARCHAR
DATE	DATE
SMALLINT	SMALLINT
INT	INT
BINARY_FLOAT	FLOAT
BINARY_DOUBLE	DOUBLE
DATE	DATE
DATETIME	DATETIME
NUMBER	DECIMAL

PostgreSQL 和 StarRocks

PostgreSQL	StarRocks
SMALLINTSMALLSERIAL	SMALLINT
INTEGERSERIAL	INT
BIGINTBIGSERIAL	BIGINT
BOOLEAN	BOOLEAN
REAL	FLOAT
DOUBLE PRECISION	DOUBLE
DECIMAL	DECIMAL
TIMESTAMP	DATETIME
DATE	DATE
CHAR	CHAR
VARCHAR	VARCHAR
TEXT	VARCHAR

SQL Server 和 StarRocks

SQL Server	StarRocks
BOOLEAN	BOOLEAN
TINYINT	TINYINT
SMALLINT	SMALLINT
INT	INT
BIGINT	BIGINT
FLOAT	FLOAT
REAL	DOUBLE
DECIMALNUMERIC	DECIMAL
CHAR	CHAR
VARCHAR	VARCHAR
DATE	DATE
DATETIMEDATETIME2	DATETIME

限制

创建 JDBC 外部表时，您不能在表上创建索引，也不能使用 PARTITION BY 和 DISTRIBUTED BY 来指定表的数据分布规则。
查询 JDBC 外部表时，StarRocks 不能将函数下推到表中。

(已弃用) Elasticsearch 外部表

StarRocks 和 Elasticsearch 是两个流行的分析系统。StarRocks 在大规模分布式计算方面表现出色。Elasticsearch 非常适合全文搜索。StarRocks 与 Elasticsearch 结合使用可以提供更完整的 OLAP 解决方案。

创建 Elasticsearch 外部表的示例

语法

CREATE EXTERNAL TABLE elastic_search_external_table
(
    k1 DATE,
    k2 INT,
    k3 SMALLINT,
    k4 VARCHAR(2048),
    k5 DATETIME
)
ENGINE=ELASTICSEARCH
PROPERTIES (
    "hosts" = "http://192.168.0.1:9200,http://192.168.0.2:9200",
    "user" = "root",
    "password" = "root",
    "index" = "tindex",
    "type" = "_doc",
    "es.net.ssl" = "true"
);

下表描述了参数。

参数	必需	默认值	描述
hosts	是	None (无)	Elasticsearch 集群的连接地址。您可以指定一个或多个地址。StarRocks 可以从此地址解析 Elasticsearch 版本和索引分片分配。StarRocks 基于 `GET /_nodes/http` API 操作返回的地址与您的 Elasticsearch 集群通信。因此，`host` 参数的值必须与 `GET /_nodes/http` API 操作返回的地址相同。否则，BE 可能无法与您的 Elasticsearch 集群通信。
index	是	None (无)	在 StarRocks 的表上创建的 Elasticsearch 索引的名称。该名称可以是别名。此参数支持通配符 ()。例如，如果您将 `index` 设置为 `hello`，StarRocks 会检索所有名称以 `hello` 开头的索引。
user	否	空	用于登录启用基本身份验证的 Elasticsearch 集群的用户名。确保您有权访问 `/cluster/state/nodes/http` 和索引。
password	否	空	用于登录 Elasticsearch 集群的密码。
type	否	`_doc`	索引的类型。默认值：`_doc`。如果要查询 Elasticsearch 8 及更高版本中的数据，则不需要配置此参数，因为 Elasticsearch 8 及更高版本中已删除映射类型。
es.nodes.wan.only	否	`false`	指定 StarRocks 是否仅使用 `hosts` 指定的地址访问 Elasticsearch 集群并获取数据。 `true`：StarRocks 仅使用 `hosts` 指定的地址访问 Elasticsearch 集群并获取数据，并且不会嗅探 Elasticsearch 索引的分片所在的 DataNode。如果 StarRocks 无法访问 Elasticsearch 集群内部的 DataNode 的地址，则需要将此参数设置为 `true`。 `false`：StarRocks 使用 `host` 指定的地址嗅探 Elasticsearch 集群索引的分片所在的 DataNode。在 StarRocks 生成查询执行计划后，相关的 BE 直接访问 Elasticsearch 集群内部的 DataNode，以从索引的分片中获取数据。如果 StarRocks 可以访问 Elasticsearch 集群内部的 DataNode 的地址，我们建议您保留默认值 `false`。
es.net.ssl	否	`false`	指定是否可以使用 HTTPS 协议访问您的 Elasticsearch 集群。只有 StarRocks 2.4 及更高版本支持配置此参数。 `true`：HTTPS 和 HTTP 协议都可以用于访问您的 Elasticsearch 集群。 `false`：只能使用 HTTP 协议访问您的 Elasticsearch 集群。
enable_docvalue_scan	否	`true`	指定是否从 Elasticsearch 列式存储中获取目标字段的值。在大多数情况下，从列式存储读取数据比从行式存储读取数据性能更好。
enable_keyword_sniff	否	`true`	指定是否基于 KEYWORD 类型字段嗅探 Elasticsearch 中的 TEXT 类型字段。如果此参数设置为 `false`，StarRocks 将在分词后执行匹配。

列式扫描以实现更快的查询

如果您将 enable_docvalue_scan 设置为 true，StarRocks 在从 Elasticsearch 获取数据时遵循以下规则

尝试并查看：StarRocks 会自动检查是否为目标字段启用了列式存储。如果是，StarRocks 会从列式存储中获取目标字段中的所有值。
自动降级：如果在列式存储中任何一个目标字段不可用，StarRocks 会解析并从行式存储 (_source) 中获取目标字段中的所有值。

注意

列式存储对于 Elasticsearch 中的 TEXT 类型字段不可用。因此，如果您查询包含 TEXT 类型值的字段，StarRocks 会从 _source 获取字段的值。

如果您查询大量的字段（大于或等于 25 个），与从 _source 读取字段值相比，从 docvalue 读取字段值不会显示出明显的优势。

嗅探 KEYWORD 类型字段

如果您将 enable_keyword_sniff 设置为 true，Elasticsearch 允许直接数据摄取而无需索引，因为它会在摄取后自动创建索引。对于 STRING 类型字段，Elasticsearch 将创建一个同时具有 TEXT 和 KEYWORD 类型的字段。这就是 Elasticsearch 的 Multi-Field 功能的工作方式。映射如下

"k4": {
   "type": "text",
   "fields": {
      "keyword": {   
         "type": "keyword",
         "ignore_above": 256
      }
   }
}

例如，要在 k4 上执行 "=" 过滤，StarRocks on Elasticsearch 会将过滤操作转换为 Elasticsearch TermQuery。

原始 SQL 过滤器如下

k4 = "StarRocks On Elasticsearch"

转换后的 Elasticsearch 查询 DSL 如下

"term" : {
    "k4": "StarRocks On Elasticsearch"

}

k4 的第一个字段是 TEXT，它将在数据摄取后由为 k4 配置的分析器（如果未为 k4 配置分析器，则由标准分析器）进行分词。因此，第一个字段将被分词为三个词：StarRocks、On 和 Elasticsearch。详细信息如下

POST /_analyze
{
  "analyzer": "standard",
  "text": "StarRocks On Elasticsearch"
}

分词结果如下

{
   "tokens": [
      {
         "token": "starrocks",
         "start_offset": 0,
         "end_offset": 5,
         "type": "<ALPHANUM>",
         "position": 0
      },
      {
         "token": "on",
         "start_offset": 6,
         "end_offset": 8,
         "type": "<ALPHANUM>",
         "position": 1
      },
      {
         "token": "elasticsearch",
         "start_offset": 9,
         "end_offset": 11,
         "type": "<ALPHANUM>",
         "position": 2
      }
   ]
}

假设您执行以下查询

"term" : {
    "k4": "StarRocks On Elasticsearch"
}

字典中没有与词 StarRocks On Elasticsearch 匹配的词，因此不会返回任何结果。

但是，如果您已将 enable_keyword_sniff 设置为 true，StarRocks 会将 k4 = "StarRocks On Elasticsearch" 转换为 k4.keyword = "StarRocks On Elasticsearch" 以匹配 SQL 语义。转换后的 StarRocks On Elasticsearch 查询 DSL 如下

"term" : {
    "k4.keyword": "StarRocks On Elasticsearch"
}

k4.keyword 是 KEYWORD 类型。因此，数据作为完整词写入 Elasticsearch，允许成功匹配。

列数据类型映射

创建外部表时，您需要根据 Elasticsearch 表中列的数据类型指定外部表中列的数据类型。下表显示了列数据类型的映射。

Elasticsearch	StarRocks
BOOLEAN	BOOLEAN
BYTE	TINYINT/SMALLINT/INT/BIGINT
SHORT	SMALLINT/INT/BIGINT
INTEGER	INT/BIGINT
LONG	BIGINT
FLOAT	FLOAT
DOUBLE	DOUBLE
KEYWORD	CHAR/VARCHAR
TEXT	CHAR/VARCHAR
DATE	DATE/DATETIME
NESTED	CHAR/VARCHAR
OBJECT	CHAR/VARCHAR
ARRAY	ARRAY

注意

StarRocks 使用 JSON 相关函数读取 NESTED 类型的数据。

Elasticsearch 会自动将多维数组展平为一维数组。StarRocks 也会这样做。从 v2.5 开始添加了对从 Elasticsearch 查询 ARRAY 数据的支持。

谓词下推

StarRocks 支持谓词下推。可以将过滤器下推到 Elasticsearch 执行，从而提高查询性能。下表列出了支持谓词下推的运算符。

SQL 语法	ES 语法
`=`	term query
`in`	terms query
`>=, <=, >, <`	range
`and`	bool.filter
`或`	bool.should
`not`	bool.must_not
`not in`	bool.must_not + terms
`esquery`	ES Query DSL

示例

esquery 函数用于将无法用 SQL 表示的查询（例如 match 和 geoshape）下推到 Elasticsearch 进行过滤。esquery 函数中的第一个参数用于关联索引。第二个参数是基本 Query DSL 的 JSON 表达式，用括号括起来。JSON 表达式必须只有且只有一个根键，例如 match、geo_shape 或 bool。

match query

select * from es_table where esquery(k4, '{
    "match": {
       "k4": "StarRocks on elasticsearch"
    }
}');

geo-related query

select * from es_table where esquery(k4, '{
  "geo_shape": {
     "location": {
        "shape": {
           "type": "envelope",
           "coordinates": [
              [
                 13,
                 53
              ],
              [
                 14,
                 52
              ]
           ]
        },
        "relation": "within"
     }
  }
}');

bool query

select * from es_table where esquery(k4, ' {
     "bool": {
        "must": [
           {
              "terms": {
                 "k1": [
                    11,
                    12
                 ]
              }
           },
           {
              "terms": {
                 "k2": [
                    100
                 ]
              }
           }
        ]
     }
  }');

使用说明

早于 5.x 的 Elasticsearch 与晚于 5.x 的 Elasticsearch 扫描数据的方式不同。目前，仅支持晚于 5.x 的版本。
支持启用了 HTTP 基本身份验证的 Elasticsearch 集群。
从 StarRocks 查询数据可能不如直接从 Elasticsearch 查询数据快，例如与计数相关的查询。原因是 Elasticsearch 直接读取目标文档的元数据，而无需过滤真实数据，这加速了计数查询。

(已弃用) Hive 外部表

在使用 Hive 外部表之前，请确保您的服务器上已安装 JDK 1.8。

创建 Hive 资源

一个 Hive 资源对应一个 Hive 集群。您必须配置 StarRocks 使用的 Hive 集群，例如 Hive Metastore 地址。您必须指定 Hive 外部表使用的 Hive 资源。

创建一个名为 hive0 的 Hive 资源。

CREATE EXTERNAL RESOURCE "hive0"
PROPERTIES (
  "type" = "hive",
  "hive.metastore.uris" = "thrift://xx.xx.xx.xx:9083"
);

查看 StarRocks 中创建的资源。

SHOW RESOURCES;

删除名为 hive0 的资源。

DROP RESOURCE "hive0";

您可以在 StarRocks 2.3 及更高版本中修改 Hive 资源的 hive.metastore.uris。有关更多信息，请参见 ALTER RESOURCE。

创建一个数据库

CREATE DATABASE hive_test;
USE hive_test;

创建 Hive 外部表

语法

CREATE EXTERNAL TABLE table_name (
  col_name col_type [NULL | NOT NULL] [COMMENT "comment"]
) ENGINE=HIVE
PROPERTIES (
  "key" = "value"
);

示例：在 hive0 资源对应的 Hive 集群中的 rawdata 数据库下创建外部表 profile_parquet_p7。

CREATE EXTERNAL TABLE `profile_wos_p7` (
  `id` bigint NULL,
  `first_id` varchar(200) NULL,
  `second_id` varchar(200) NULL,
  `p__device_id_list` varchar(200) NULL,
  `p__is_deleted` bigint NULL,
  `p_channel` varchar(200) NULL,
  `p_platform` varchar(200) NULL,
  `p_source` varchar(200) NULL,
  `p__city` varchar(200) NULL,
  `p__province` varchar(200) NULL,
  `p__update_time` bigint NULL,
  `p__first_visit_time` bigint NULL,
  `p__last_seen_time` bigint NULL
) ENGINE=HIVE
PROPERTIES (
  "resource" = "hive0",
  "database" = "rawdata",
  "table" = "profile_parquet_p7"
);

描述

外部表中的列
- 列名必须与 Hive 表中的列名相同。
- 列的顺序不需要与 Hive 表中的列顺序相同。
- 您可以仅选择 Hive 表中的某些列，但您必须选择所有分区键列。
- 外部表的分区键列不需要使用 partition by 指定。它们必须与其他列在同一描述列表中定义。您不需要指定分区信息。StarRocks 将自动从 Hive 表中同步此信息。
- 将 ENGINE 设置为 HIVE。
PROPERTIES
- hive.resource：使用的 Hive 资源。
- database：Hive 数据库。
- table：Hive 中的表。不支持 view。

下表描述了 Hive 和 StarRocks 之间的列数据类型映射。

Hive 的列类型	StarRocks 的列类型	描述
INT/INTEGER	INT
BIGINT	BIGINT
TIMESTAMP	DATETIME	将 TIMESTAMP 数据转换为 DATETIME 数据时，精度和时区信息将丢失。您需要根据 sessionVariable 中的时区将 TIMESTAMP 数据转换为没有时区偏移量的 DATETIME 数据。
STRING	VARCHAR
VARCHAR	VARCHAR
CHAR	CHAR
DOUBLE	DOUBLE
FLOAT	FLOAT
DECIMAL	DECIMAL
ARRAY	ARRAY

注意

目前，支持的 Hive 存储格式是 Parquet、ORC 和 CSV。如果存储格式是 CSV，则引号不能用作转义字符。

支持 SNAPPY 和 LZ4 压缩格式。

可以查询的 Hive 字符串列的最大长度为 1 MB。如果字符串列超过 1 MB，它将被处理为空列。

使用 Hive 外部表

查询 profile_wos_p7 的总行数。

select count(*) from profile_wos_p7;

更新缓存的 Hive 表元数据

Hive 分区信息和相关文件信息缓存在 StarRocks 中。缓存刷新间隔由 hive_meta_cache_refresh_interval_s 指定，默认值为 7200 秒。hive_meta_cache_ttl_s 指定缓存的超时时间，默认值为 86400 秒。
- 缓存的数据也可以手动刷新。
  1. 如果 Hive 表中添加或删除了分区，您必须运行 REFRESH EXTERNAL TABLE hive_t 命令来刷新 StarRocks 中缓存的表元数据。hive_t 是 StarRocks 中 Hive 外部表的名称。
  2. 如果某些 Hive 分区中的数据被更新，您必须运行 REFRESH EXTERNAL TABLE hive_t PARTITION ('k1=01/k2=02', 'k1=03/k2=04') 命令来刷新 StarRocks 中的缓存数据。hive_t 是 StarRocks 中 Hive 外部表的名称。'k1=01/k2=02' 和 'k1=03/k2=04' 是数据已更新的 Hive 分区的名称。
  3. 当您运行 REFRESH EXTERNAL TABLE hive_t 时，StarRocks 首先检查 Hive 外部表的列信息是否与 Hive Metastore 返回的 Hive 表的列信息相同。如果 Hive 表的 Schema 发生更改，例如添加列或删除列，StarRocks 会将更改同步到 Hive 外部表。同步后，Hive 外部表的列顺序与 Hive 表的列顺序保持一致，分区列是最后一列。
当 Hive 数据以 Parquet、ORC 和 CSV 格式存储时，您可以将 Hive 表的 Schema 更改（例如 ADD COLUMN 和 REPLACE COLUMN）同步到 StarRocks 2.3 及更高版本中的 Hive 外部表。

访问对象存储

FE 配置文件的路径是 fe/conf，如果需要自定义 Hadoop 集群，可以在其中添加配置文件。例如：如果 HDFS 集群使用高可用 nameservice，您需要将 hdfs-site.xml 放在 fe/conf 下。如果 HDFS 配置了 ViewFs，您需要将 core-site.xml 放在 fe/conf 下。
BE 配置文件的路径是 be/conf，如果需要自定义 Hadoop 集群，可以在其中添加配置文件。例如，如果 HDFS 集群使用高可用 nameservice，您需要将 hdfs-site.xml 放在 be/conf 下。如果 HDFS 配置了 ViewFs，您需要将 core-site.xml 放在 be/conf 下。
在 BE 所在的机器上，在 BE 的**启动脚本** bin/start_be.sh 中将 JAVA_HOME 配置为 JDK 环境，而不是 JRE 环境，例如，export JAVA_HOME = <JDK path>。您必须将此配置添加到脚本的开头并重新启动 BE 才能使配置生效。
配置 Kerberos 支持
1. 要使用 kinit -kt keytab_path principal 登录到所有 FE/BE 机器，您需要有访问 Hive 和 HDFS 的权限。kinit 命令登录只有一段时间有效期，需要放入 crontab 定期执行。
2. 将 hive-site.xml/core-site.xml/hdfs-site.xml 放在 fe/conf 下，将 core-site.xml/hdfs-site.xml 放在 be/conf 下。
3. 将 -Djava.security.krb5.conf=/etc/krb5.conf 添加到 $FE_HOME/conf/fe.conf 文件中 JAVA_OPTS 选项的值中。/etc/krb5.conf 是 krb5.conf 文件的保存路径。您可以根据您的操作系统更改路径。
4. 直接将 JAVA_OPTS="-Djava.security.krb5.conf=/etc/krb5.conf" 添加到 $BE_HOME/conf/be.conf 文件中。/etc/krb5.conf 是 krb5.conf 文件的保存路径。您可以根据您的操作系统更改路径。
5. 添加 Hive 资源时，您必须将域名传递给 hive.metastore.uris。此外，您必须在 /etc/hosts 文件中添加 Hive/HDFS 域名和 IP 地址之间的映射。

配置对 AWS S3 的支持：将以下配置添加到 fe/conf/core-site.xml 和 be/conf/core-site.xml。

<configuration>
   <property>
      <name>fs.s3a.access.key</name>
      <value>******</value>
   </property>
   <property>
      <name>fs.s3a.secret.key</name>
      <value>******</value>
   </property>
   <property>
      <name>fs.s3a.endpoint</name>
      <value>s3.us-west-2.amazonaws.com</value>
   </property>
   <property>
   <name>fs.s3a.connection.maximum</name>
   <value>500</value>
   </property>
</configuration>

fs.s3a.access.key: AWS 访问密钥 ID。
fs.s3a.secret.key: AWS 密钥。
fs.s3a.endpoint: 要连接的 AWS S3 Endpoint。
fs.s3a.connection.maximum: StarRocks 到 S3 的最大并发连接数。如果在查询期间发生错误 Timeout waiting for connection from poll，您可以将此参数设置为更大的值。

（已弃用）Iceberg 外部表

从 v2.1.0 开始，StarRocks 允许您通过使用外部表查询 Apache Iceberg 中的数据。要查询 Iceberg 中的数据，您需要在 StarRocks 中创建一个 Iceberg 外部表。创建表时，您需要在外部表和要查询的 Iceberg 表之间建立映射。

准备工作

确保 StarRocks 具有访问 Apache Iceberg 使用的元数据服务（例如 Hive metastore）、文件系统（例如 HDFS）和对象存储系统（例如 Amazon S3 和阿里云对象存储服务）的权限。

注意事项

Iceberg 外部表只能用于查询以下类型的数据
- Iceberg v1 表（分析数据表）。从 v3.0 开始支持 ORC 格式的 Iceberg v2 表（行级删除），从 v3.1 开始支持 Parquet 格式的 Iceberg v2 表。有关 Iceberg v1 表和 Iceberg v2 表之间的差异，请参见 Iceberg Table Spec。
- 以 gzip（默认格式）、Zstd、LZ4 或 Snappy 格式压缩的表。
- 存储在 Parquet 或 ORC 格式的文件。
StarRocks 2.3 及更高版本中的 Iceberg 外部表支持同步 Iceberg 表的 Schema 更改，而 StarRocks 2.3 之前版本中的 Iceberg 外部表不支持。如果 Iceberg 表的 Schema 发生更改，您必须删除相应的外部表并创建一个新的外部表。

步骤

步骤 1：创建 Iceberg 资源

在创建 Iceberg 外部表之前，您必须在 StarRocks 中创建一个 Iceberg 资源。该资源用于管理 Iceberg 访问信息。此外，您还需要在用于创建外部表的语句中指定此资源。您可以根据您的业务需求创建一个资源

如果从 Hive metastore 获取 Iceberg 表的元数据，您可以创建一个资源并将 Catalog 类型设置为 HIVE。
如果从其他服务获取 Iceberg 表的元数据，您需要创建一个自定义 Catalog。然后创建一个资源并将 Catalog 类型设置为 CUSTOM。

创建一个 Catalog 类型为 `HIVE` 的资源

例如，创建一个名为 iceberg0 的资源并将 Catalog 类型设置为 HIVE。

CREATE EXTERNAL RESOURCE "iceberg0" 
PROPERTIES (
   "type" = "iceberg",
   "iceberg.catalog.type" = "HIVE",
   "iceberg.catalog.hive.metastore.uris" = "thrift://xx.xx.xx.xx:9083" 
);

下表描述了相关参数。

参数	描述
type	资源类型。将值设置为 `iceberg`。
iceberg.catalog.type	资源的 Catalog 类型。支持 Hive Catalog 和自定义 Catalog。如果您指定 Hive Catalog，请将值设置为 `HIVE`。如果您指定自定义 Catalog，请将值设置为 `CUSTOM`。
iceberg.catalog.hive.metastore.uris	Hive metastore 的 URI。参数值的格式如下：`thrift://<Iceberg 元数据的 IP 地址>:<端口号>`。端口号默认为 9083。Apache Iceberg 使用 Hive Catalog 访问 Hive metastore，然后查询 Iceberg 表的元数据。

创建一个 Catalog 类型为 `CUSTOM` 的资源

自定义 Catalog 需要继承抽象类 BaseMetastoreCatalog，并且您需要实现 IcebergCatalog 接口。此外，自定义 Catalog 的类名不能与 StarRock 中已存在的类的名称重复。创建 Catalog 后，打包 Catalog 及其相关文件，并将它们放在每个前端 (FE) 的 fe/lib 路径下。然后重新启动每个 FE。完成上述操作后，您可以创建一个 Catalog 为自定义 Catalog 的资源。

例如，创建一个名为 iceberg1 的资源并将 Catalog 类型设置为 CUSTOM。

CREATE EXTERNAL RESOURCE "iceberg1" 
PROPERTIES (
   "type" = "iceberg",
   "iceberg.catalog.type" = "CUSTOM",
   "iceberg.catalog-impl" = "com.starrocks.IcebergCustomCatalog" 
);

下表描述了相关参数。

参数	描述
type	资源类型。将值设置为 `iceberg`。
iceberg.catalog.type	资源的 Catalog 类型。支持 Hive Catalog 和自定义 Catalog。如果您指定 Hive Catalog，请将值设置为 `HIVE`。如果您指定自定义 Catalog，请将值设置为 `CUSTOM`。
iceberg.catalog-impl	自定义 Catalog 的完全限定类名。FE 根据此名称搜索 Catalog。如果 Catalog 包含自定义配置项，则在创建 Iceberg 外部表时，必须将它们作为键值对添加到 `PROPERTIES` 参数中。

您可以在 StarRocks 2.3 及更高版本中修改 Iceberg 资源的 hive.metastore.uris 和 iceberg.catalog-impl。有关更多信息，请参见 ALTER RESOURCE。

查看 Iceberg 资源

SHOW RESOURCES;

删除 Iceberg 资源

例如，删除一个名为 iceberg0 的资源。

DROP RESOURCE "iceberg0";

删除 Iceberg 资源会使引用此资源的所有外部表不可用。但是，不会删除 Apache Iceberg 中的相应数据。如果您仍然需要查询 Apache Iceberg 中的数据，请创建一个新资源和一个新外部表。

步骤 2：（可选）创建数据库

例如，在 StarRocks 中创建一个名为 iceberg_test 的数据库。

CREATE DATABASE iceberg_test; 
USE iceberg_test; 

注意：StarRocks 中数据库的名称可以与 Apache Iceberg 中数据库的名称不同。

步骤 3：创建 Iceberg 外部表

例如，在数据库 iceberg_test 中创建一个名为 iceberg_tbl 的 Iceberg 外部表。

CREATE EXTERNAL TABLE `iceberg_tbl` ( 
    `id` bigint NULL, 
    `data` varchar(200) NULL 
) ENGINE=ICEBERG 
PROPERTIES ( 
    "resource" = "iceberg0", 
    "database" = "iceberg", 
    "table" = "iceberg_table" 
); 

下表描述了相关参数。

参数	描述
ENGINE	引擎名称。将值设置为 `ICEBERG`。
resource	外部表引用的 Iceberg 资源的名称。
database	Iceberg 表所属的数据库的名称。
table	Iceberg 表的名称。

注意

外部表的名称可以与 Iceberg 表的名称不同。

外部表的列名必须与 Iceberg 表中的列名相同。两个表的列顺序可以不同。

如果您在自定义 Catalog 中定义了配置项，并且希望在查询数据时配置项生效，则可以在创建外部表时将配置项作为键值对添加到 PROPERTIES 参数中。例如，如果您在自定义 Catalog 中定义了配置项 custom-catalog.properties，则可以运行以下命令来创建外部表。

CREATE EXTERNAL TABLE `iceberg_tbl` ( 
    `id` bigint NULL, 
    `data` varchar(200) NULL 
) ENGINE=ICEBERG 
PROPERTIES ( 
    "resource" = "iceberg0", 
    "database" = "iceberg", 
    "table" = "iceberg_table",
    "custom-catalog.properties" = "my_property"
); 

创建外部表时，您需要根据 Iceberg 表中列的数据类型指定外部表中列的数据类型。下表显示了列数据类型的映射。

Iceberg 表	Iceberg 外部表
BOOLEAN	BOOLEAN
INT	TINYINT / SMALLINT / INT
LONG	BIGINT
FLOAT	FLOAT
DOUBLE	DOUBLE
DECIMAL(P, S)	DECIMAL
DATE	DATE / DATETIME
TIME	BIGINT
TIMESTAMP	DATETIME
STRING	STRING / VARCHAR
UUID	STRING / VARCHAR
FIXED(L)	CHAR
BINARY	VARCHAR
LIST	ARRAY

StarRocks 不支持查询数据类型为 TIMESTAMPTZ、STRUCT 和 MAP 的 Iceberg 数据。

步骤 4：查询 Apache Iceberg 中的数据

创建外部表后，您可以使用该外部表查询 Apache Iceberg 中的数据。

select count(*) from iceberg_tbl;

（已弃用）Hudi 外部表

从 v2.2.0 开始，StarRocks 允许您通过使用 Hudi 外部表查询 Hudi 数据湖中的数据，从而实现闪电般快速的数据湖分析。本主题介绍了如何在您的 StarRocks 集群中创建 Hudi 外部表，并使用 Hudi 外部表查询 Hudi 数据湖中的数据。

准备工作

确保您的 StarRocks 集群被授予访问 Hive metastore、HDFS 集群或您可以在其中注册 Hudi 表的 Bucket 的权限。

注意事项

Hudi 外部表对于 Hudi 是只读的，只能用于查询。
StarRocks 支持查询 Copy on Write 和 Merge On Read 表（从 v2.5 开始支持 MOR 表）。有关这两种表类型之间的差异，请参见 Table & Query Types。
StarRocks 支持 Hudi 的以下两种查询类型：Snapshot Queries 和 Read Optimized Queries（Hudi 仅支持对 Merge On Read 表执行 Read Optimized Queries）。不支持 Incremental Queries。有关 Hudi 查询类型的更多信息，请参见 Table & Query Types。
StarRocks 支持 Hudi 文件的以下压缩格式：gzip、zstd、LZ4 和 Snappy。Hudi 文件的默认压缩格式是 gzip。
StarRocks 无法同步来自 Hudi 管理表的 Schema 更改。有关更多信息，请参见 Schema Evolution。如果更改了 Hudi 管理表的 Schema，您必须从 StarRocks 集群中删除关联的 Hudi 外部表，然后重新创建该外部表。

步骤

步骤 1：创建和管理 Hudi 资源

您必须在 StarRocks 集群中创建 Hudi 资源。Hudi 资源用于管理您在 StarRocks 集群中创建的 Hudi 数据库和外部表。

创建 Hudi 资源

执行以下语句创建一个名为 hudi0 的 Hudi 资源

CREATE EXTERNAL RESOURCE "hudi0" 
PROPERTIES ( 
    "type" = "hudi", 
    "hive.metastore.uris" = "thrift://xx.xx.xx.xx:9083"
);

下表描述了参数。

参数	描述
type	Hudi 资源的类型。将值设置为 hudi。
hive.metastore.uris	Hudi 资源连接到的 Hive metastore 的 Thrift URI。将 Hudi 资源连接到 Hive metastore 后，您可以使用 Hive 创建和管理 Hudi 表。Thrift URI 的格式为 `<Hive metastore 的 IP 地址>:<Hive metastore 的端口号>`。默认端口号为 9083。

从 v2.3 开始，StarRocks 允许更改 Hudi 资源的 hive.metastore.uris 值。有关更多信息，请参见 ALTER RESOURCE。

查看 Hudi 资源

执行以下语句以查看在您的 StarRocks 集群中创建的所有 Hudi 资源

SHOW RESOURCES;

删除 Hudi 资源

执行以下语句以删除名为 hudi0 的 Hudi 资源

DROP RESOURCE "hudi0";

注意

删除 Hudi 资源会导致使用该 Hudi 资源创建的所有 Hudi 外部表不可用。但是，删除不会影响您存储在 Hudi 中的数据。如果您仍然想使用 StarRocks 查询 Hudi 中的数据，您必须在 StarRocks 集群中重新创建 Hudi 资源、Hudi 数据库和 Hudi 外部表。

步骤 2：创建 Hudi 数据库

执行以下语句以在您的 StarRocks 集群中创建并打开一个名为 hudi_test 的 Hudi 数据库

CREATE DATABASE hudi_test; 
USE hudi_test; 

注意

您为 StarRocks 集群中的 Hudi 数据库指定的名称不必与 Hudi 中关联的数据库的名称相同。

步骤 3：创建 Hudi 外部表

执行以下语句以在 hudi_test Hudi 数据库中创建一个名为 hudi_tbl 的 Hudi 外部表

CREATE EXTERNAL TABLE `hudi_tbl` ( 
    `id` bigint NULL, 
    `data` varchar(200) NULL 
) ENGINE=HUDI 
PROPERTIES ( 
    "resource" = "hudi0", 
    "database" = "hudi", 
    "table" = "hudi_table" 
); 

下表描述了参数。

参数	描述
ENGINE	Hudi 外部表的查询引擎。将值设置为 `HUDI`。
resource	您 StarRocks 集群中 Hudi 资源的名称。
database	Hudi 外部表在您的 StarRocks 集群中所属的 Hudi 数据库的名称。
table	Hudi 外部表与之关联的 Hudi 管理表。

注意

您为 Hudi 外部表指定的名称不必与关联的 Hudi 管理表的名称相同。

Hudi 外部表中的列必须具有相同的名称，但与关联的 Hudi 管理表中的对应列相比，顺序可以不同。

您可以从关联的 Hudi 管理表中选择一些或所有列，并在 Hudi 外部表中仅创建选定的列。下表列出了 Hudi 支持的数据类型与 StarRocks 支持的数据类型之间的映射。

Hudi 支持的数据类型	StarRocks 支持的数据类型
BOOLEAN	BOOLEAN
INT	TINYINT/SMALLINT/INT
DATE	DATE
TimeMillis/TimeMicros	TIME
TimestampMillis/TimestampMicros	DATETIME
LONG	BIGINT
FLOAT	FLOAT
DOUBLE	DOUBLE
STRING	CHAR/VARCHAR
ARRAY	ARRAY
DECIMAL	DECIMAL

注意

StarRocks 不支持查询 STRUCT 或 MAP 类型的数据，也不支持查询 Merge On Read 表中的 ARRAY 类型的数据。

步骤 4：查询 Hudi 外部表中的数据

在创建与特定 Hudi 管理表关联的 Hudi 外部表后，您无需将数据加载到 Hudi 外部表中。要查询 Hudi 中的数据，请执行以下语句

SELECT COUNT(*) FROM hudi_tbl;

（已弃用）MySQL 外部表

在星型 Schema 中，数据通常分为维度表和事实表。维度表的数据较少，但涉及 UPDATE 操作。目前，StarRocks 不支持直接 UPDATE 操作（可以通过使用 Unique Key 表来实现更新）。在某些情况下，您可以将维度表存储在 MySQL 中以进行直接数据读取。

要查询 MySQL 数据，您必须在 StarRocks 中创建一个外部表并将其映射到 MySQL 数据库中的表。创建表时，您需要指定 MySQL 连接信息。

CREATE EXTERNAL TABLE mysql_external_table
(
    k1 DATE,
    k2 INT,
    k3 SMALLINT,
    k4 VARCHAR(2048),
    k5 DATETIME
)
ENGINE=mysql
PROPERTIES
(
    "host" = "127.0.0.1",
    "port" = "3306",
    "user" = "mysql_user",
    "password" = "mysql_passwd",
    "database" = "mysql_db_test",
    "table" = "mysql_table_test"
);

参数

host：MySQL 数据库的连接地址
port：MySQL 数据库的端口号
user：登录 MySQL 的用户名
password：登录 MySQL 的密码
database：MySQL 数据库的名称
table：MySQL 数据库中表的名称

StarRocks 外部表​

(已弃用) 用于 JDBC 兼容数据库的外部表​

前提条件​

创建和管理 JDBC 资源​

创建 JDBC 资源​

查看 JDBC 资源​

删除 JDBC 资源​

创建数据库​

创建 JDBC 外部表​

查询 JDBC 外部表​

数据类型映射​

MySQL 和 StarRocks​

Oracle 和 StarRocks​

PostgreSQL 和 StarRocks​

SQL Server 和 StarRocks​

限制​

(已弃用) Elasticsearch 外部表​

创建 Elasticsearch 外部表的示例​

语法​

列式扫描以实现更快的查询​

嗅探 KEYWORD 类型字段​

列数据类型映射​

谓词下推​

示例​

使用说明​

(已弃用) Hive 外部表​

创建 Hive 资源​

创建一个数据库​

创建 Hive 外部表​

使用 Hive 外部表​

更新缓存的 Hive 表元数据​

访问对象存储​

（已弃用）Iceberg 外部表​

准备工作​

注意事项​

步骤​

步骤 1：创建 Iceberg 资源​

创建一个 Catalog 类型为 HIVE 的资源​

创建一个 Catalog 类型为 CUSTOM 的资源​

查看 Iceberg 资源​

删除 Iceberg 资源​

步骤 2：（可选）创建数据库​

步骤 3：创建 Iceberg 外部表​

步骤 4：查询 Apache Iceberg 中的数据​

（已弃用）Hudi 外部表​

准备工作​

注意事项​

步骤​

步骤 1：创建和管理 Hudi 资源​

创建 Hudi 资源​

查看 Hudi 资源​

删除 Hudi 资源​

步骤 2：创建 Hudi 数据库​

步骤 3：创建 Hudi 外部表​

步骤 4：查询 Hudi 外部表中的数据​

（已弃用）MySQL 外部表​

您觉得这篇文档怎么样？

StarRocks 外部表

(已弃用) 用于 JDBC 兼容数据库的外部表

前提条件

创建和管理 JDBC 资源

创建 JDBC 资源

查看 JDBC 资源

删除 JDBC 资源

创建数据库

创建 JDBC 外部表

查询 JDBC 外部表

数据类型映射

MySQL 和 StarRocks

Oracle 和 StarRocks

PostgreSQL 和 StarRocks

SQL Server 和 StarRocks

限制

(已弃用) Elasticsearch 外部表

创建 Elasticsearch 外部表的示例

语法

列式扫描以实现更快的查询

嗅探 KEYWORD 类型字段

列数据类型映射

谓词下推

示例

使用说明

(已弃用) Hive 外部表

创建 Hive 资源

创建一个数据库

创建 Hive 外部表

使用 Hive 外部表

更新缓存的 Hive 表元数据

访问对象存储

（已弃用）Iceberg 外部表

准备工作

注意事项

步骤

步骤 1：创建 Iceberg 资源

创建一个 Catalog 类型为 `HIVE` 的资源

创建一个 Catalog 类型为 `CUSTOM` 的资源

查看 Iceberg 资源

删除 Iceberg 资源

步骤 2：（可选）创建数据库

步骤 3：创建 Iceberg 外部表

步骤 4：查询 Apache Iceberg 中的数据

（已弃用）Hudi 外部表

准备工作

注意事项

步骤

步骤 1：创建和管理 Hudi 资源

创建 Hudi 资源

查看 Hudi 资源

删除 Hudi 资源

步骤 2：创建 Hudi 数据库

步骤 3：创建 Hudi 外部表

步骤 4：查询 Hudi 外部表中的数据

（已弃用）MySQL 外部表