创建资源
1 2 3 4 5 | create external resource "hive_test" properties ( "type" = "hive", "hive.metastore.uris" = "thrift://baidu.com:9083" ); |
查看资源
1 | show resources; |
建立外部表
1 2 3 4 5 6 7 8 9 10 11 12 | drop table if exists ods.ods_type_class; create external table `ods`.`ods_type_class`( `id` varchar(65533) comment 'ID', `class` varchar(65533) comment '类型', `dt` varchar(65533) comment '分区字段：日期,\r\n eg: 2020-01-01' )engine=hive comment "partition by (dt)" properties ( "resource" = "hive_test", "database" = "pdc_ods_labor", "table" = "labor_attendance_result_di" ); |
查看数据
1 | select * from ods.ods_type_class limit 10; |
附加问题
由于集成MongoDB数据的过程中，数据都转换为String类型，需要对日期格式进行处理，但StarRocks的from_unixtime语法不能解决以下情况，需要用到array函数
1 2 | 数据格式：Sat Mar 07 08:30:55 CST 2020 目标格式：2020-03-07 |
1 2 3 4 5 6 7 8 | create view dwd.dwd_result(id,create_date,dt) as select id, to_date(FROM_UNIXTIME(UNIX_TIMESTAMP(concat(date_array[6],'-',date_array[2],'-',date_array[3]),'%Y-%M-%d'),'yyyy-MM-dd')) create_date, dt from (select id, split(create_date," ") as date_array, dt from ods.ods_result )t; |