Doris数据库系列2-数据湖功能-注册Hive和MySQL外部表

关键词： 大数据、MPP数据库、Doris、数据湖、外部表

整体说明

Doris的数据湖功能-注册Hive和MySQL外部表，图示如下：

一、数据表注册Doris并使用

1.1、 Hive外部表

1.1.1、 Hive数据表注册Doris

官方文档地址：https://doris.apache.org/zh-CN/docs/lakehouse/datalake-analytics/hive

通过连接 Hive Metastore，或者兼容 Hive Metatore 的元数据服务，Doris 可以自动获取 Hive 的库表信息，并进行数据查询。

mysql -h 192.168.1.1 -uroot -P 9030 -A -p'doris123'

-- 进入SQL环境执行，相关参数都在hdfs-site.xml，hive-site.xml里
-- 特别说明: 'metadata_refresh_interval_sec' = '3600'，指3600秒也就是1小时刷新一次缓存
DROP CATALOG IF EXISTS hive;
CREATE CATALOG hive PROPERTIES (
    'type'='hms',
    'hive.metastore.uris' = 'thrift://hadoop02:9083',
    'hive.version' = '1.2.1000.2.6.5.0-292',
    'dfs.nameservices'='bigdata',
    'dfs.ha.namenodes.bigdata'='nn1,nn2',
    'dfs.namenode.rpc-address.bigdata.nn1'='hadoop01:8020',
    'dfs.namenode.rpc-address.bigdata.nn2'='hadoop02:8020',  'dfs.client.failover.proxy.provider.bigdata'='org.apache.hadoop.hdfs.server.namenode.ha.ConfiguredFailoverProxyProvider',
    'metadata_refresh_interval_sec' = '3600'
);
-- show catalogs;
-- 查询结果，可以看到注册成功
+-----------+-------------+----------+-----------+-------------------------+---------------------+------------------------+
| CatalogId | CatalogName | Type     | IsCurrent | CreateTime              | LastUpdateTime      | Comment                |
+-----------+-------------+----------+-----------+-------------------------+---------------------+------------------------+
|     16012 | hive        | hms      | No        | 2024-10-16 17:39:12.856 | 2024-10-16 17:39:47 |            |
|         0 | internal    | internal | Yes       | NULL                    | NULL                | Doris internal catalog |
+-----------+-------------+----------+-----------+-------------------------+---------------------+------------------------+

1.1.2、 Doris直接查询Hive表

mysql -h 192.168.1.1 -uroot -P 9030 -A -p'doris123'

方式一 ：

-- 进入SQL环境执行
switch hive; -- 切换hive
use u_dw; -- 选择hive的库
select * from table_name; -- 查询hive表

方式二 ：

select * from hive.u_dw.table_name

1.1.3、 Doris直接操作Hive表

• 前置条件

版本号 >= 2.1.3

• 环境设置

在sql环境里执行

set global enable_nereids_planner = true;
set global enable_fallback_to_original_planner = false;

• 修改 Hive catalog，增加权限

指定hdfs用户，此用户权限较高，否则可能会报没权限的错误

drop CATALOG `hive`;
CREATE CATALOG `hive` PROPERTIES (
"type" = "hms",
"hive.version" = "1.2.1000.2.6.5.0-292",
"hive.metastore.uris" = "thrift://hadoop02:9083",
"dfs.nameservices" = "bigdata",
"dfs.namenode.rpc-address.bigdata.nn2" = "hadoop02:8020",
"dfs.namenode.rpc-address.bigdata.nn1" = "hadoop01:8020",
"dfs.ha.namenodes.bigdata" = "nn1,nn2",
"dfs.client.failover.proxy.provider.bigdata" = "org.apache.hadoop.hdfs.server.namenode.ha.ConfiguredFailoverProxyProvider",
"metadata_refresh_interval_sec" = "3600",
"hadoop.username" = "hdfs",
"fs.defaultFS" = "hdfs://bigdataedu"
);

• 表格式要求
  ORC（默认）
  Parquet
  Text （自 2.1.7 和 3.0.3 版本开始支持）
  安装版本是 2.1.6 所以不支持。

• DDL 和 DML 示例

DDL

drop table hive.u_dw.temp_table_name;
create table hive.u_dw.temp_table_name as
select * from hive.u_dw.table_name;

DML

insert into hive.u_dw.temp_table_name
select * from mysql.u_dw.table_name

1.2、 MySQL外部表

1.2.1、 MySQL数据表注册Doris

mysql -h 192.168.1.1 -uroot -P 9030 -A -p'doris123'

DROP CATALOG IF EXISTS mysql;
CREATE CATALOG `mysql` PROPERTIES (
"user" = "dh_admin",
"type" = "jdbc",
"password" = "*********",
"metadata_refresh_interval_sec" = "3600",
"jdbc_url" = "jdbc:mysql://XXX.XXX.XXX.XXX:23306/XXXXXXX?autoReconnect=true&initialTimeout=3&useUnicode=true&yearIsDateType=false&tinyInt1isBit=false&rewriteBatchedStatements=true&characterEncoding=utf-8",
"driver_url" = "file:///home/mysql-connector-java-8.0.28.jar",
"driver_class" = "com.mysql.cj.jdbc.Driver"
);

说明：需要指定mysql-connector的路径。

1.2.2、 Doris直接查询Mysql表

mysql -h 192.168.1.1 -uroot -P 9030 -A -p'doris123'

方式一 ：

-- 进入SQL环境执行
switch mysql; -- 切换mysql
use u_dm; -- 选择hive的库
select * from table_name; -- 查询mysql表

方式二 ：

select * from mysql.u_dm.table_name

1…2.3、 Doris直接操作Mysql表

• 语句透传

Doris 支持通过透传的方式，直接执行 JDBC 数据源的 DDL、DML 语句和查询语句。

• DDL
  不支持直接建表，可以使用语句透传，直接执行 JDBC 数据源的 DDL

CALL EXECUTE_STMT("mysql", "create table u_dm.temp_table_name like u_dm.table_name");

• DML
  插入语句可以直接使用

insert into mysql.u_dm.temp_table_name select * from mysql.u_dm.table_name

二、 数据缓存和刷新

官方文档地址：https://doris.apache.org/zh-CN/docs/lakehouse/metacache/#%E7%BC%93%E5%AD%98%E5%88%B7%E6%96%B0

2.1、刷新指定 Catalog

REFRESH CATALOG ctl1 PROPERTIES("invalid_cache" = "true");

该命令会刷新指定 Catalog 的库列表，表列名以及所有缓存信息等。
invalid_cache 表示是否要刷新分区和文件列表等缓存。默认为 true。如果为 false，则只会刷新 Catalog 的库、表列表，而不会刷新分区和文件列表等缓存信息。该参数适用于，用户只想同步新增删的库表信息时，可以设置为 false

2.2、刷新指定 Database

REFRESH DATABASE [ctl.]db1 PROPERTIES("invalid_cache" = "true");

该命令会刷新指定 Database 的表列名以及 Database 下的所有缓存信息等。
invalid_cache 属性含义同上。默认为 true。如果为 false，则只会刷新 Database 的表列表，而不会刷新缓存信息。该参数适用于，用户只想同步新增删的表信息时

2.3、刷新指定 Table

REFRESH TABLE [ctl.][db.]tbl1;

该命令会刷新指定 Table 下的所有缓存信息等 4.定时刷新

CREATE CATALOG hive PROPERTIES (
    'type'='hms',
    'hive.metastore.uris' = 'thrift://hadoop02:9083',
    'hive.version' = '1.2.1000.2.6.5.0-292',
    'dfs.nameservices'='bigdata',
    'dfs.ha.namenodes.bigdata'='nn1,nn2',
    'dfs.namenode.rpc-address.bigdata.nn1'='hadoop01:8020',
    'dfs.namenode.rpc-address.bigdata.nn2'='hadoop02:8020',
    'dfs.client.failover.proxy.provider.bigdataedu'='org.apache.hadoop.hdfs.server.namenode.ha.ConfiguredFailoverProxyProvider',
    'metadata_refresh_interval_sec' = '3600'
);

metadata_refresh_interval_sec 表示每 3600 秒刷新一次 Catalog。相当于每隔 3600 秒，自动执行一次