StarRocks数据导出到Hive

StarRocks数据导出到Hive

Hive建表

# Hive建表列分隔符本次使用逗号
# 如果Hive正式表需要ORC或Parquet格式，需要先将SR导出的数据导入到TEXTFILE格式tmp表，然后再insert到ORC或Parquet格式正式表
# 如果Hive正式表需要TEXTFILE格式，SR可以直接EXPORT到正式表

# 临时表
CREATE TABLE xx_tmp (
    id INT
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n'  
STORED AS TEXTFILE; 

# 正式表
CREATE TABLE xx (
    id INT
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n'  
STORED AS ORC;

SR导出到HDFS

# SR导出的数据是csv文件

EXPORT TABLE ads_fms_point.ads_fund_conformance_his_waybill_cost 
TO "hdfs://10.82.192.4:8020/user/hive/warehouse/ads_fms_point.db/ads_fund_conformance_his_waybill_cost_tmp/" 
PROPERTIES
(
    "column_separator"=",",
    "load_mem_limit"="2147483648",
    "timeout" = "3600"
)
WITH BROKER
(
    "username" = "admin"
);

# 获取queryid
SELECT LAST_QUERY_ID();

# 查看导出状态
SHOW EXPORT WHERE queryid = "xx";

导入Hive

# 数据load到tmp表
LOAD DATA  INPATH '/user/hive/warehouse/ads_fms_point.db/ads_fund_conformance_his_waybill_cost_tmp/*'  INTO TABLE ads_fms_point.ads_fund_conformance_his_waybill_cost_tmp;

# 验证tmp表
select count(*) from ads_fms_point.ads_fund_conformance_his_waybill_cost_tmp;
hdfs dfs -ls /user/hive/warehouse/ads_fms_point.db/ads_fund_conformance_his_waybill_cost_tmp

# 写入正式表
insert into ads_fms_point.ads_fund_conformance_his_waybill_cost select * from ads_fms_point.ads_fund_conformance_his_waybill_cost_tmp;

# msck repair table ads_fms_point.ads_fund_conformance_his_waybill_cost;

ir table ads_fms_point.ads_fund_conformance_his_waybill_cost;