Flink-StarRocks详解:第五部分查询数据湖(第55天)_starrocks查询版本语法

系列文章目录

4.查询数据湖
4.1 Catalog
4.1.1 概述
4.1.1.1 基本概念
4.1.1.2 Catalog
4.1.1.3 访问Catalog
4.1.2 Default catalog
4.1.3 External Catalog
4.2 文件外部表
4.2.1 使用限制
4.2.2 开源版本语法
4.2.3 阿里云版本
5. 查询及优化

---

前言

本文为Flink-StarRocks详解后续章节：主要详解StarRocks查询数据湖
由于篇幅过长，后续接着下面进行详解：
数仓场景：即席查询大案例

4.查询数据湖

4.1 Catalog

4.1.1 概述

StarRocks 自 2.3 版本起支持 Catalog（数据目录）功能，实现在一套系统内同时维护内、外部数据，方便轻松访问并查询存储在各类外部源的数据。

4.1.1.1 基本概念

内部数据：指保存在 StarRocks 中的数据。
外部数据：指保存在外部数据源（如 Apache Hive™、Apache Iceberg、Apache Hudi、Delta Lake、JDBC）中的数据。

4.1.1.2 Catalog

当前 StarRocks 提供两种类型 Catalog：internal catalog 和 external catalog。

 Internal catalog: 内部数据目录，用于管理 StarRocks 所有内部数据。例如，执行 CREATE DATABASE 和 CREATE TABLE 语句创建的数据库和数据表都由 internal catalog 管理。 每个 StarRocks 集群都有且只有一个 internal catalog 名为 default_catalog。
 External catalog: 外部数据目录，用于连接外部 metastore。在 StarRocks 中，可以通过 external catalog 直接查询外部数据，无需进行数据导入或迁移。当前支持创建以下类型的 external catalog：
 Hive catalog：用于查询 Hive 数据。
 Iceberg catalog：用于查询 Iceberg 数据。
 Hudi catalog：用于查询 Hudi 数据。
 Delta Lake catalog：用于查询 Delta Lake 数据。
 JDBC catalog：用于查询 JDBC 数据源的数据。

4.1.1.3 访问Catalog

可以使用 SET CATALOG 切换当前会话里生效的 Catalog，然后通过该 Catalog 查询数据。
语法：

SET CATALOG <catalog_name>
1

参数：
catalog_name：当前会话里生效的 Catalog，支持 Internal Catalog 和 External Catalog。如果指定的 Catalog 不存在，则会引发异常。
示例：
通过如下命令，切换当前会话里生效的 Catalog 为 Hive Catalog hive_metastore：

SET CATALOG hive_metastore;
1

如想在一个 catalog 中查询其他 catalog 中数据，可通过 catalog_name.db_name.table_name 的格式来引用目标数据。
例如，在 default_catalog.olap_db 下查询 hive_catalog 中的 hive_table。

SELECT * FROM hive_catalog.hive_db.hive_table;
1

4.1.2 Default catalog

StarRocks 2.3 及以上版本提供了 Internal Catalog（内部数据目录），用于管理 StarRocks 的内部数据。每个 StarRocks 集群都有且只有一个 Internal Catalog，名为 default_catalog。StarRocks 暂不支持修改 Internal Catalog 的名称，也不支持创建新的 Internal Catalog。
使用：
默认是default_catalog，也可以显示切换。

SET CATALOG default_catalog;
1

然后通过 SELECT 查询内部数据

SELECT * FROM <table_name>;
1

如在以上步骤中未指定数据库，则可以在查询语句中直接指定。

SELECT * FROM <db_name>.<table_name>;
或
SELECT * FROM default_catalog.<db_name>.<table_name>;
1
2
3

4.1.3 External Catalog

External Catalog（外部数据目录），用于管理外部数据源的访问信息。
语法：

CREATE EXTERNAL CATALOG <catalog_name>
[COMMENT <comment>]
PROPERTIES
(
  "type" = "<type>",
  MetastoreParams
)
1
2
3
4
5
6
7

参数说明：
 catalog_name：External Catalog的名称，必选参数。
命名要求如下：
 总长度不能超过64个字符。
 必须由字母（a_z或AZ）、数字（0~9）或下划线（_）组成，且只能以字母开头。
comment：External Catalog的描述，可选参数。
type：数据源的类型，必选参数。
目前开源版本支持hive、hudi、iceberg、delta lake、jdbc、elasticsearch、paimon七种类型的数据源，阿里云EMR Serverless StarRocks目前只支持hive、hudi、iceberg三种。
MetastoreParams：StarRocks访问相应数据源的相关参数配置
示例：
开源版本：

CREATE EXTERNAL CATALOG hive_catalog_hms
PROPERTIES
(
    "type" = "hive",
    "hive.metastore.type" = "hive",
    "hive.metastore.uris" = "thrift://xx.xx.xx:9083"
);
1
2
3
4
5
6
7

阿里云版本：

CREATE EXTERNAL CATALOG hive
PROPERTIES 
(
    "type" = "hive",
    "dlf.catalog.endpoint" = "dlf-vpc.cn-beijing.aliyuncs.com",
    "dlf.catalog.region" = "cn-beijing",
    "dlf.catalog.proxyMode" = "DLF_ONLY",
    "dlf.catalog.akMode" = "MANUAL",
    "hive.metastore.type" = "DLF",
    "dlf.catalog.id" = "dlf_test",
    "dlf.catalog.accessKeyId" = "*******",
    "dlf.catalog.accessKeySecret" = "*****"
);
1
2
3
4
5
6
7
8
9
10
11
12
13

4.2 文件外部表

文件外部表 (File External Table) 是一种特殊的外部表。可以通过文件外部表直接查询外部存储系统上的 Parquet 和 ORC 格式的数据文件，无需导入数据。同时，文件外部表也不依赖任何 Metastore。StarRocks 当前支持的外部存储系统包括 HDFS、Amazon S3 及其他兼容 S3 协议的对象存储、阿里云对象存储 OSS 和腾讯云对象存储 COS。
该特性从 StarRocks 2.5 版本开始支持。

4.2.1 使用限制

 当前仅支持在 default_catalog 下的数据库内创建文件外部表，不支持 external catalog。可以通过 SHOW CATALOGS 来查询集群下的 catalog。
 仅支持查询 Parquet 和 ORC 格式的数据文件。
 目前仅支持读取目标数据文件中的数据，不支持例如 INSERT，DELETE，DROP 等写入操作。

4.2.2 开源版本语法

切换到目前数据库后，可以使用如下语法创建一个文件外部表。

CREATE EXTERNAL TABLE <table_name> 
(
    <col_name> <col_type> [NULL | NOT NULL] [COMMENT "<comment>"]
) 
ENGINE=FILE
COMMENT ["comment"]
PROPERTIES
(
    FileLayoutParams,
    StorageCredentialParams
)
1
2
3
4
5
6
7
8
9
10
11

参数说明：
参数 必选 说明

具体参考：https://docs.starrocks.io/zh/docs/data_source/file_external_table/
1

示例：

USE db_example;
CREATE EXTERNAL TABLE table_1
(
    name string, 
    id int
) 
ENGINE=file
PROPERTIES 
(
    "path" = "s3://bucket-test/folder1/", 
    "format" = "orc",
    "aws.s3.use_instance_profile" = "false",
    "aws.s3.access_key" = "<iam_user_access_key>",
    "aws.s3.secret_key" = "<iam_user_access_key>",
    "aws.s3.region" = "us-west-2" 
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

4.2.3 阿里云版本

在阿里云EMR Serverless StarRocks中，通过配置正确的OSS AccessKey和OSS Endpiont信息，即可以轻松地连接到OSS Bucket，并在StarRocks中使用SQL语句对数据进行查询和分析等操作。

操作步骤：
（1）进入EMR Serverless StarRocks实例配置页面。
1）在EMR控制台，点击StarRocks，单击待查看的实例名称。

2）单击实例配置页签。

（2）修改core-site.xml配置。
1）单击core-site.xml页签。
2）搜索并修改以下配置项。

①配置accessKey步骤：
登录AccessKey管理页面，

https://ram.console.aliyun.com/manage/ak?spm=a2c4g.11186623.0.0.389247f8OlOaMw 
1

首次进入需勾选并点击确定

因为安全原因，这里使用子用户的AccessKey

点击创建用户

登录名为写自己得，显示名称为自己的名称，勾选两种访问方式
点击自定义密码，设置为88888666!
然后是不需要重置密码，不需要MFA多因素认证
点击确定
然后通过手机号或者扫脸进行验证。
验证成功后，复制对应的AccessKey ID 和 Secret

②通过查询访问域名和数据中心

 https://help.aliyun.com/zh/oss/user-guide/regions-and-endpoints 得到 北京的内网Endpoint为 oss-cn-beijing-internal.aliyuncs.com
1

（3）修改BE配置。
1）单击BE页签。
2）搜索并修改以下配置项。

（4）生效配置。
1）单击提交参数。
2）在弹出的对话框中，输入原因说明，单击确定。
（5）查询示例
接paimon章节在oss中创建的表

CREATE DATABASE test;

CREATE EXTERNAL TABLE test.UpdateTable
(
 a STRING,
 b INT,
 c INT
) 
ENGINE=file
PROPERTIES 
(
    "path" = "oss://*******-paimon/test.db/UpdateTable/bucket-0/", 
    "format" = "orc"
);

SELECT * FROM test.UpdateTable;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

查询结果如下：

注意：如果查询报错，原因可能是子账号没有OSS访问权限，所以需要给子账号开通权限

开通方式，就是在RAM访问控制中，点击用户，然后添加权限。

然后把OSS权限授权给子用户。

5. 查询及优化

查询SQL参考：

https://docs.starrocks.io/zh/docs/sql-reference/sql-statements/all-commands/
1

常用函数参考：

https://docs.starrocks.io/zh/docs/sql-reference/sql-functions/function-list/
1

另外，为了获取更快的查询速度，StarRocks也提供了一些查询加速的方法，主要的方式有使用CBO 优化器、物化视图、Colocata Join、索引、数据去重等。
具体详见：

https://docs.starrocks.io/zh/docs/using_starrocks/Cost_based_optimizer/
1