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;

yesterday=$(date -d '-1 day' '+%Y-%m-%d')
lastweek=$(date -d '-1 week' '+%Y-%m-%d')

/usr/local/cloud/hive/bin/hive<<EOF 
INSERT OVERWRITE TABLE product_sell PARTITION (ds='$yesterday') 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 si.is_hidden=0 and si.ds between '$lastweek' and '$yesterday' group by s.good_receiver_province_id, si.product_id) b on (a.id=b.product_id);
EOF

这个部分难点在于hive中不知道如何调用形如shell日期变量的方式创建日期分区,mark一下!

评论 3
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值