Doris---hive导入Doris数据流程

Doris---hive导⼊Doris数据流程-- 1、Doris建表
use dim;
CREATE TABLE IF NOT EXISTS `dim_tab_name` (
`inc_day` date NULL COMMENT "⽇期(分区)"
,`dept_code` varchar(100) NULL COMMENT "⽹点代码"
,`dept_name` varchar(100) NULL COMMENT "⽹点名称" ,.............
,`valid_dt` varchar(100) NULL COMMENT "⽣效⽇期"
,`invalid_tm` varchar(100) NULL COMMENT "失效⽇期"
) ENGINE=OLAP
DUPLICATE KEY(`inc_day`, `dept_code`)
COMMENT "...的维表(历史快照)"
PARTITION BY RANGE(`inc_day`)(
START ("20210801") END ("20211011") EVERY (INTERVAL 1 day)
)
DISTRIBUTED BY HASH(`dept_code`) BUCKETS 32
PROPERTIES(
"storage_medium" = "SSD",
"dynamic_partition.enable" = "true",
"dynamic_partition.time_unit" = "DAY",
"dynamic_partition.end" = "1",
"dynamic_partition.prefix" = "p",
"dynamic_partition.buckets" = "32"
);
⼆、brokerload批量导数
-- 查看brokerload批量导数任务的⽇志
use dim;SHOW LOAD WHERE LABEL = 'doris_dim_tab_name'
--执⾏brokerload批量导数
load label dim.doris_dim_tab_name
(
--tez的⽂件⽬录不匹配，问题，导致数据导⼊不成功
data infile("hdfs://ip:port/hive/warehouse/dim/dim_tab_name/inc_day=2021[08|09|10]*/*")
INTO TABLE `dim_tab_name`
FORMAT AS "parquet"
-- 这⾥的顺序需要和hive中保持⼀致
(
dept_code,dept_name,.......,valid_dt,invalid_tm
)
COLUMNS FROM PATH AS (inc_day) --从hdfs路径中获取分区的字段值
set (
--建议字段映射
inc_day=inc_day
,dept_code=dept_code
,dept_name=dept_name
..................
,valid_dt=valid_dt
,invalid_tm=invalid_tm
)
)
-- 通过SHOW BROKER 查看⽬前集群的Broker名称
WITH BROKER hdfs_broker
PROPERTIES
(
"max_filter_ratio" = "0.01"
);
三、创建etl调度任务
----⽬标源，导⼊前准备语句：
truncate table dim.dim_tab_name partition(p$[time(yyyyMMdd,-1d)])
----⽬标源，Stream Load参数：
"strict_mode":"false","max_filter_ratio":"0.10","exec_mem_limit":"12884901888","timeout":"10000"。