如何用 Hive 轉換時間維度

1. 獲取日期，並導入表 tableA (腳本參數為運行時間)

# 通過運行腳本，將日期參數代入drop table tableA;create table tableA as select $partition_pt as currentdate;# 建表drop table tableB;create table tableB (date string)partitioned by (pt string)ROW FORMAT DELIMITED FIELDS TERMINATED BY ;# 更新表數據insert overwrite table tableB partition (pt = $partition_pt)select currentdate as date from tableB

2. 創建時間維度表 tableC (date為欄位名，類型為string)

常用時間函數：

year(date) # 年month(date) # 月 floor((month(date)-1)/3)+1 # 季度 weekofyear(date) # 第幾周pmod(datediff(date,2012-01-01),7) # 星期

quarter()可直接獲取季度 (僅Hive 1.3.0以上支持). Example: quarter(2015-04-08) = 2

構建時間維度表（日期-年-季度-月-周-星期-日）

insert overwrite table tableCasselect date,year(date),case when floor((month(date)-1)/3)+1 = 1 then 第一季度 when floor((month(date)-1)/3)+1 = 2 then 第二季度 when floor((month(date)-1)/3)+1 = 3 then 第三季度 when floor((month(date)-1)/3)+1 = 4 then 第四季度 end as quarter,month(date) as month,weekofyear(date) as week_of_year,case when pmod(datediff(date,2012-01-01), 7) = 0 then星期天 when pmod(datediff(date,2012-01-01), 7) = 1 then星期一 when pmod(datediff(date,2012-01-01), 7) = 2 then星期二 when pmod(datediff(date,2012-01-01), 7) = 3 then星期三 when pmod(datediff(date,2012-01-01), 7) = 4 then星期四 when pmod(datediff(date,2012-01-01), 7) = 5 then星期五 when pmod(datediff(date,2012-01-01), 7) = 6 then星期六 end as week_flag,day(date) as the_datefrom tableC

3. 輸出結果

4. 更多的 Hive 時間函數 請戳官網鏈接

LanguageManual UDFcwiki.apache.org

後續還會更新「時間」相關內容，歡迎大家關注我喔