broker load 数据导入

broker load

hive

-- hive表
drop table bda.trade;
CREATE TABLE IF NOT EXISTS bda.trade (
  `trade_id` int comment 'trade_id',
  `cost` double comment 'cost',
  `create_time` timestamp comment 'create_time'
  )
partitioned by(dt string)
stored as orc
TBLPROPERTIES ("orc.compression"="SNAPPY");

set hive.exec.dynamic.partition.mode=nonstrict;
-- 插入数据
insert into bda.trade partition(dt) values 
(1,14.4,"2022-05-01 05:01:15","2022-05-04"),
(2,88.6,"2022-05-02 07:44:52","2022-05-05"),
(3,11.1,"2022-05-03 14:21:25","2022-05-06");

select * from bda.trade ;

https://img-blog.csdnimg.cn/000197cea4b241438b150cd00b8117b9.png

starrocks

-- starrocks表
drop table trade_sr;
CREATE TABLE IF NOT EXISTS trade_sr (
  `trade_id` int NULL,
  `dt` datetime null,
  `cost` double NULL
) ENGINE=OLAP
unique  KEY(trade_id,dt)
PARTITION BY RANGE(dt)(
START ("2022-01-01") END ("2022-12-31") 
EVERY (INTERVAL 1 day)
)
DISTRIBUTED BY HASH(trade_id) BUCKETS 3;

导入数据

LOAD LABEL bda.test
(
    DATA INFILE("hdfs://hadoop102:8020/user/hive/warehouse/bda.db/trade/dt=2022-05-05/*")
    INTO TABLE trade_sr
    COLUMNS TERMINATED BY "\\x01"
     FORMAT AS "orc"
    (trade_id, cost, create_time)
    set(
    dt=create_time
    )
)
WITH BROKER 'broker1'
(
    "username" = "atguigu"
)
PROPERTIES
(
    "timeout" = "3600",
    "max_filter_ratio"="0"
);

show load where label = 'test';

https://img-blog.csdnimg.cn/c24be215a52742d1ac3f83022c4931f7.png

select * from trade_sr;

https://img-blog.csdnimg.cn/e4c457dc0b2c4e439980ee9e5830d424.png