StarRocks-同步hive数据

官方文档对csv，orc ,json ，parquet都支持的比较好。写文章的时候SR我们生产用的是3.1.x版本

1、同步hive表textfile格式的数据

1. hive表结构

CREATE EXTERNAL TABLE ads_d_app_rank_inc(`filename` string COMMENT '解析的文件名称',`rank` string COMMENT 'app排名',`app_name` string COMMENT 'app名称'
)
COMMENT 'app使用人数排名'
PARTITIONED BY (`date_id` string COMMENT '日分区格式yyyyMMdd'
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
STORED AS TEXTFILE;

1. SR 表结构

CREATE TABLE ads_d_app_rank_inc
(`filename` VARCHAR(255) COMMENT '解析的文件名称',`rank` VARCHAR(50) COMMENT 'app排名',`app_name` VARCHAR(255) COMMENT 'app名称',`date_id` DATE COMMENT '日分区格式yyyyMMdd'
)
COMMENT 'app使用人数排名'
PARTITION BY RANGE(`date_id`)
(PARTITION p20231128 VALUES [('2023-11-28'), ('2023-11-29')),PARTITION p20231129 VALUES [('2023-11-29'), ('2023-11-30'))
)
DISTRIBUTED BY HASH(`filename`) BUCKETS 10
PROPERTIES ("replication_num" = "1","in_memory" = "false","storage_format" = "DEFAULT"
);

1. 数据导入到SR

load label test.ads_d_app_rank_inc_20241122_label(
data infile("hdfs://bdtest/warehouse/tablespace/external/hive/ads_d_app_rank_inc/date_id=20231128/*")
into table ads_d_app_rank_inc
COLUMNS TERMINATED BY ","
format as "csv"
(filename, rank, app_name)
columns from path as (date_id) 
)
with broker properties("timeout" = "3600")

在导入数据的时候，这里指定一下 COLUMNS TERMINATED BY ","  ，否则可能因为分隔符报错。