首页 > 代码库 > hive按当天日期建立分区表 | 动态往日期分区插入数据
hive按当天日期建立分区表 | 动态往日期分区插入数据
hive建立分区表,以当天日期(“2014-08-15”)作为分区依据,hql如下:
CREATE EXTERNAL TABLE IF NOT EXISTS product_sell( category_id BIGINT, province_id BIGINT, product_id BIGINT, price DOUBLE, sell_num BIGINT ) PARTITIONED BY (ds string) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' STORED AS TEXTFILE;
然后以日期作为分区依据,插入数据,shell脚本如下:
#!/bin/bash source /etc/profile; today=$(date +%Y-%m-%d) /usr/local/cloud/hive/bin/hive<<EOF INSERT OVERWRITE TABLE product_sell PARTITION (ds='$today') select a.category_id, b.good_receiver_province_id as province_id, a.id as product_id, (b.sell_amount/b.sell_num) as price, b.sell_num from product a join (select si.product_id, s.good_receiver_province_id, sum(si.order_item_amount) sell_amount, sum(si.order_item_num) sell_num from so_item si join so s on (si.order_id=s.id) where si.is_gift=0 and datediff(date_sub(from_unixtime(unix_timestamp()),1), si.ds) between 0 and 6 group by s.good_receiver_province_id, si.product_id) b on (a.id=b.product_id); EOF
这个部分难点在于hive中不知道如何调用形如shell日期变量的方式创建日期分区,mark一下!
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。