第2.11章：StarRocks表设计--外部表

除了创建表、视图或者物化视图，StarRocks还支持创建外部表来访问外部数据源。所谓外部表，可以简单的理解为是在StarRocks中建立的与外部数据的映射，所有的数据还在各自的数据源中，但我们可以通过外部表向所在的数据源发起查询。目前StarRocks已支持的第三方数据源包括MySQL、ElasticSearch、Hive以及StarRocks。

外部表的创建和使用都比较简单，下面我们逐个介绍。

1 MySQL外部表

在星型模型中，我们一般将表类型划分为维度表和指标表。维度表数据量通常较小，但一般都会涉及UPDATE操作。由于当前StarRocks还未直接支持UPDATE语法，所以在某些情况下我们可以将维度表放在MySQL中，在StarRocks中为其创建外部表，然后进行关联查询（若希望最佳的查询效果，也可以将MySQL中的数据通过flink-cdc或者cannel同步至StarRocks中）。

例如，我们需要在StarRocks中为MySQL内test库的表mysql_table创建外部表，MySQL所在服务器IP为192.168.110.98，数据库版本为5.7.35，用户名和密码均为root，mysql_table的建表语句为：

CREATE TABLE test.`mysql_table` (

  `id` INT(11) NOT NULL,

  `name` VARCHAR(3) DEFAULT NULL,

  `height` INT(8) DEFAULT NULL,

  PRIMARY KEY (`id`)

);

插入数据：

insert into mysql_table values(10001,'诺亚',55),(10002,'雷杰多',50),(10003,'赛迦',58);

在StarRocks中创建外部表：

CREATE EXTERNAL TABLE mysql_external_table

(

    id INT,

    name VARCHAR(3),

    height INT

)

ENGINE=mysql

PROPERTIES

(

    "host" = "192.168.110.98",

    "port" = "3306",

    "user" = "root",

    "password" = "root",

    "database" = "test",

    "table" = "mysql_table"

);

查询：

mysql> select * from mysql_external_table;

+----------+-------------------+----------+

| id          | name              | height  |

+----------+-------------------+----------+

| 10001   | 诺亚                |    55     |

| 10002   | 雷杰多            |    50     |

| 10003   | 赛迦                |    58     |

+----------+-------------------+----------+

这里注意，在所有外部表建表语句中，varchar的长度没有意义，只需要保证与源表中的类型对的上即可。

2 ElasticSearch外部表

ElasticSearch全文检索能力非常强大，通过ES外表，我们可以方便的在StarRocks中进行联邦查询，提供更完备的OLAP解决方案。

以单节点的ES环境为例，其主要信息为：

版本：7.8.0

IP：192.168.110.98

http_port：9200

用户名：elastic

密码：elastic

（查看版本：curl -XGET 192.168.110.98:9200 -u elastic:elastic）

为方便演示，咱们不使用Postman或者ElasticSearch Head这类的工具或者插件，直接在192.168.110.98节点上使用curl命令来进行后续的操作。

首先，我们在ElasticSearch创建索引es_star，创建索引：

curl -XPUT http://192.168.110.98:9200/es_star?pretty -H 'Content-Type: application/json' -u elastic:elastic -d '{

   "settings": {

      "index": {

         "number_of_shards": "1",

         "number_of_replicas": "0"

      }

   },

   "mappings": {

      "properties": {

         "d_id": {

            "type": "long"

         },

         "d_date": {

            "type": "date"

         },

         "d_state": {

            "type": "keyword"

         },

         "d_info": {

            "type": "text",

            "analyzer": "standard"

         },

         "d_time": {

            "type": "float"

         }

      }

   }

}'

在索引中插入文档：

curl -u elastic:elastic -XPOST http://192.168.110.98:9200/es_star/_bulk?pretty -H 'Content-Type: application/json' -d '{"index":{"_index":"es_star","_type":"_doc"}}

{"d_id" : 100, "d_date": "2021-11-08", "d_state": "Elasticsearch", "d_info": "Elasticsearch", "d_time": 20.0}

{"index":{"_index":"es_star","_type":"_doc"}}

{"d_id" : 100, "d_date": "2021-11-11", "d_state": "StarRocks", "d_info": "StarRocks", "d_time": 30.0}

{"index":{"_index":"es_star","_type":"_doc"}}

{"d_id" : 100, "d_date": "2021-12-12", "d_state": "StarRocks On ES", "d_info": "StarRocks On ES", "d_time": 30.0}

{"index":{"_index":"es_star","_type":"_doc"}}

{"d_id" : 100, "d_date": "2021-12-01", "d_state": "StarRocks", "d_info": "StarRocks", "d_time": 50.0}

{"index":{"_index":"es_star","_type":"_doc"}}

{"d_id" : 100, "d_date": "2021-11-01", "d_state": "ES", "d_info": "ES", "d_time": 100.0}

'

查看索引中所有内容：

curl -u elastic:elastic -X GET http://192.168.110.98:9200/es_star/_search?pretty -H 'Content-Type: application/json' -d'

{

         "query" : {

                   "match_all": {}

         }

}'

在StarRocks中创建外表：

use starrocks;

CREATE EXTERNAL TABLE elasticsearch_external_table

(

  `d_id` bigint(20) COMMENT "",

  `d_date` datetime COMMENT "",

  `d_state` varchar(20) COMMENT "",

  `d_info` varchar(100) COMMENT "",

  `d_time` float COMMENT ""

)ENGINE=ELASTICSEARCH

PROPERTIES (

    "hosts" = "http://192.168.110.98:9200",

    "user" = "elastic",

    "password" = "elastic",

    "index" = "es_star",

    "type" = "_doc"

);

查询外表：

mysql> SELECT * FROM elasticsearch_external_table;

+----------+-----------------------------+------------------------+-------------------------+------------+

|  d_id     | d_date                        | d_state                 | d_info                    | d_time    |

+----------+-----------------------------+------------------------+-------------------------+------------+

|  100      | 2021-11-08 00:00:00 | Elasticsearch        | Elasticsearch        |     20        |

|  100      | 2021-11-11 00:00:00 | StarRocks             | StarRocks             |     30        |

|  100      | 2021-12-12 00:00:00 | StarRocks On ES | StarRocks On ES |     30        |

|  100      | 2021-12-01 00:00:00 | StarRocks             | StarRocks            |     50        |

|  100      | 2021-11-01 00:00:00 | ES                         | ES                        |    100       |

+----------+-----------------------------+------------------------+-------------------------+------------+

测试完成后，也可以在ES中删除不需要的索引：

curl -u elastic:elastic -XDELETE http://192.168.110.98:9200/es_star

StarRocks支持对ElasticSearch表进行谓词下推，把过滤条件推给ElasticSearch进行执行，让执行尽量靠近存储，提高查询性能。

通过esquery函数我们还可以将一些无法用sql表述的ES query（如match、geoshape等）下推给ES进行过滤处理。esquery的第一个列名参数用于关联index，第二个参数是ES的基本Query DSL的json表述，使用花括号{}包含，json的root key有且只能有一个，如match、geo_shape、bool等。写法例如：

select * from es_table where esquery(k4, '{

    "match": {

       "k4": "StarRocks on elasticsearch"

    }

}');

3 Hive外部表

Hive作为经典的传统数仓，目前应用仍旧非常广泛。通过Hive外部表，StarRocks可以直接对Hive中的数据进行查询，且查询性能较Presto等有数倍的优势。

当前有基于Hadoop的Hive实例，其主要信息为：

节点ip：192.168.110.201

hive.metastore.uris：thrift:// 192.168.110.201:9083（在配置文件hive-site.xml中查看）

Hadoop版本：3.1.3

Hive版本：3.1.2

当前Hive外表的存储格式仅支持Parquet和ORC类型，压缩格式支持snappy和lz4。为了方便演示，我们使用beeline客户端在default库中创建一个orc类型无压缩的Hive表orcfile_table：

create table if not exists default.orcfile_table(

siteid int,

sitename string,

pv bigint

)

row format delimited

fields terminated by '\t'

stored as orc;

导入三条数据：

insert into orcfile_table values(1,'Star',10000), (2,'Rocks',20000), (3,'StarRocks',30000);

StarRocks为Hive创建外部表分为2步。首先，我们需要在StarRocks中创建Hive资源，一个Hive资源对应一个Hive集群，包含集群的相关配置，如Hive meta store地址等。例如我们为服务器192.168.110.201上的Hive实例创建资源hive01，在StarRocks中执行：

CREATE EXTERNAL RESOURCE "hive01"

PROPERTIES (

  "type" = "hive",

  "hive.metastore.uris" = "thrift://192.168.110.201:9083"

);

查看当前已创建的资源：

SHOW RESOURCES;

也可以删除已存在的资源，例如删除名为hive0的资源：

DROP RESOURCE "hive0";

第二步，我们指定前面创建好的Hive资源来创建对应集群的Hive外表：

use starrocks;

CREATE EXTERNAL TABLE `hive_external_table` (

  `siteid` int,

  `sitename` varchar(32),

  `pv` bigint

) ENGINE=HIVE

PROPERTIES (

  "resource" = "hive01",

  "database" = "default",

  "table" = "orcfile_table"

);

查询Hive外表：

select * from hive_external_table;

这里注意，Hive的元数据信息会被缓存在StarRocks中，缓存的刷新时间默认为7200秒（参数为fe.conf中的hive_meta_cache_refresh_interval_s），缓存的失效时间默认为86400秒（参数为fe.conf中的hive_meta_cache_ttl_s）。

若Hive中数据变化后在StarRocks中未同步，我们可以在查询Hive外表前手动刷新外表的元数据信息：

REFRESH EXTERNAL TABLE hive_external_table;

若Hive中只有某些Partition新增数据，我们也可以指定Partition进行刷新，例如：

REFRESH EXTERNAL TABLE hive_t PARTITION ('date_id=01', 'date_id=02');

其中hive_t是Starrocks中的外表名称，'date_id=01'、 'date_id=02'是Hive中的Partition名称。

在创建外表时注意：

• 列名需要与Hive表一一对应；
• 列的顺序不需要与Hive表一致；
• 可以只选择Hive表中的部分列，但分区列必须要全部包含；
• 外表的分区列无需通过partition by语句指定，需要与普通列一样定义到描述列表中。我们不需要指定分区信息，StarRocks会自动从Hive同步。
• StarRocks不会自动同步Hive表的Schema变更，如果Hive中表结构发生变化，StarRocks仍然只能查询原有结构的数据。若要修改外表的表结构，当前只能重新创建外表。

4 StarRocks外部表

与上面的三类外部表不同，StarRocks-StarRocks外部表目前不是作为跨集群或者跨服务查询的方式，而是作为向外部StarRocks集群导入数据的方式。StarRocks以此来初步解决用户的读写分离需求，提供更好的资源隔离。

例如我们当前有两套StarRocks集群，集群1的Leader信息为：

IP：192.168.110.101

用户名：root

密码：root

版本：1.19.1

集群2的Leader信息为：

IP：192.168.110.98

rpc_port：9020（端口配置见fe/fe.conf）

用户名：root

密码：root

版本：1.19.0

StarRocks本质上还是一个MVCC的系统，在数据导入时会生成一个个数据版本，StarRocks在后台会不断进行版本的合并。若业务中导入相对频繁，除会占用较多集群资源外，还会导致数据版本较多，进而可能会影响到集群的查询性能。为解决这个问题，其中的一种方案就是使用两套集群，一套主要用来进行数据导入及内部ETL，另一套不断同步数据对外提供查询。

结合业务场景我们展开模拟，我们假设使用集群1完成数据导入及ETL工作，然后在集群1中建立集群2的外部表，通过外部表将数据导入至集群2的目标表中。

首先在集群2中建立目标表：

create database starrocks02;

CREATE TABLE IF NOT EXISTS starrocks02.starrocks02_target_table (

    event_time DATETIME NOT NULL COMMENT "datetime of event",

    event_type INT NOT NULL COMMENT "type of event",

    user_id INT COMMENT "id of user",

    channel INT COMMENT ""

)

DISTRIBUTED BY HASH(user_id) BUCKETS 10

PROPERTIES (

  "replication_num" = "1"

);

在集群1中创建集群2表starrocks2_target_table的外部表：

CREATE EXTERNAL TABLE external_starrocks_table(

    event_time DATETIME NOT NULL COMMENT "datetime of event",

    event_type INT NOT NULL COMMENT "type of event",

    user_id INT COMMENT "id of user",

channel INT COMMENT ""

)

ENGINE=olap

DISTRIBUTED BY HASH(user_id) BUCKETS 10

PROPERTIES

(

    "host" = "192.168.110.98",

    "port" = "9020",

    "user" = "root",

    "password" = "root",

    "database" = "starrocks02",

    "table" = "starrocks02_target_table"

);

这时，我们就可以对集群1中的外部表external_starrocks_table进行数据插入，数据插入操作会同步到集群2的目标表starrocks02_target_table中：

集群1：

insert into external_starrocks_table values('2021-11-10',27,1001,01),('2021-11-10',27,1001,01),('2021-11-11',31,1002,02);

集群2：

select * from starrocks02.starrocks02_target_table;

mysql> select * from starrocks02.starrocks02_target_table;

+------------------------------+------------------+---------------+------------+

| event_time                   | event_type    | user_id       | channel |

+------------------------------+------------------+---------------+------------+

| 2021-11-10 00:00:00   |         27          |    1001       |      1        |

| 2021-11-10 00:00:00   |         27          |    1001       |      1        |

| 2021-11-11 00:00:00   |         31          |    1002       |      2        |

+------------------------------+------------------+---------------+------------+

在集群1中，我们还可以将计算结果写入目标表中，举一个简单的例子：

insert into external_starrocks_table select * from table02;

前面提到过，StarRocks自己的外部表不是用来进行联邦查询的，目前也不支持对StarRocks外表进行查询。当前使用StarRocks外部表的限制还有：

1）仅可以在外表上执行insert into 和show create table操作，不支持其他数据写入方式，也不支持查询和DDL；

2）创建外表语法和创建普通表一致，但其中的列名等信息请保持同其对应的目标表一致。

外表会周期性从目标表同步元信息（同步周期为10秒），在目标表执行的DDL操作可能会延迟一定时间反应在外表上。

ALTER TABLE starrocks02_target_table ADD COLUMN status INT KEY NULL AFTER event_type;

在等待目标表表结构变更及元数据同步完成后，我们在集群1执行：

show create table external_starrocks_table\G

mysql> show create table external_starrocks_table\G

*************************** 1. row ***************************

Table: external_starrocks_table

Create Table: CREATE EXTERNAL TABLE `external_starrocks_table` (

  `event_time` datetime NOT NULL COMMENT "datetime of event",

  `event_type` int(11) NOT NULL COMMENT "type of event",

  `status` int(11) NULL COMMENT "",

  `user_id` int(11) NULL COMMENT "id of user",

  `channel` int(11) NULL COMMENT ""

) ENGINE=OLAP_EXTERNAL

DUPLICATE KEY(`event_time`, `event_type`, `status`, `user_id`)

COMMENT "OLAP_EXTERNAL"

DISTRIBUTED BY HASH(`user_id`) BUCKETS 10

PROPERTIES (

"replication_num" = "1",

"in_memory" = "false",

"storage_format" = "DEFAULT"

"host" = "192.168.110.98",

"port" = "9020",

"user" = "root",

"password" = "",

"database" = "starrocks02",

"table" = "starrocks02_target_table"

);

可以发现元信息已同步，这点跟Hive外表有所区别，即在StarRocks外表创建后，若目标表表结构发生变化，StarRocks外部表会周期性同步变化，我们无需重建外部表。

表设计部分的基本内容我们就介绍完了，这部分咱们还是先把涉及的面给铺开到，一些细节或更深入的应用，大家可以在后续的学习中参考社区文档进行拓展研究。社区文档地址：

StarRocks @ StarRocks_intro @ StarRocks DocsStarRockshttps://docs.starrocks.com/zh-cn/main/introduction/StarRocks_intro下一章，我们就会展开对StarRocks数据导入导出的介绍。