hive动态分区shell_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<

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

原文：https://blog.csdn.net/yeweiouyang/article/details/38589281

标签：sell,province,product,num,日期,hive,si,分区表,id

来源： https://blog.csdn.net/maenlai0086/article/details/96446983