hivesql日期

① hive鍔ㄦ佸垎鍖哄弬鏁伴厤緗 鍒╃敤sql鎬庝箞璁劇疆

闈欐佸垎鍖篠P錛坰tatic partition錛
鍔ㄦ佸垎鍖篋P錛坉ynamic partition錛
闈欐佸垎鍖轟笌鍔ㄦ佸垎鍖虹殑涓昏佸尯鍒鍦ㄤ簬闈欐佸垎鍖烘槸鎵嬪姩鎸囧畾錛岃屽姩鎬佸垎鍖烘槸閫氳繃鏁版嵁鏉ヨ繘琛屽垽鏂銆傝︾粏鏉ヨ達紝闈欐佸垎鍖虹殑鍒楀疄鍦ㄧ紪璇戞椂鏈燂紝閫氳繃鐢ㄦ埛浼犻掓潵鍐沖畾鐨勶紱鍔ㄦ佸垎鍖哄彧鏈夊湪SQL鎵ц屾椂鎵嶈兘鍐沖畾銆

浜岋級瀹炴垬婕旂ず濡備綍鍦℉ive涓浣跨敤鍔ㄦ佸垎鍖
1銆佸壋寤轟竴寮犲垎鍖鴻〃錛屽寘鍚涓や釜鍒嗗尯dt鍜宧t琛ㄧず鏃ユ湡鍜屽皬鏃
[sql] view plain
CREATE TABLE partition_table001
(
name STRING,
ip STRING
)
PARTITIONED BY (dt STRING, ht STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t";
2銆佸惎鐢╤ive鍔ㄦ佸垎鍖猴紝鍙闇瑕佸湪hive浼氳瘽涓璁劇疆涓や釜鍙傛暟錛

② hive yyyyMMdd杞瑈yyy-MM-dd

       鍦╤ive涓錛屾垜浠緇忓父闇瑕佽繘琛屾棩鏈熺殑璁＄畻錛屽彲鏄錛岀粡甯鎬細鍑虹幇榪欐牱涓縐嶆儏鍐碉紝hive浠撳簱涓鏃ユ湡鐨勫瓨鍌ㄦ牸寮忔槸yyyyMMdd,渚嬪20200520錛屽湪榪涜屾棩鏈熺殑璁＄畻鏃訛紝hive鏀鎸佺殑鍑芥暟渚嬪傦紝add_months 浠呰瘑鍒珁yyy-MM-dd鏍煎紡鐨勫嚱鏁幫紝濡傛灉杈撳叆yyyyMMdd鐨勫弬鏁幫紝榪斿洖緇撴灉鏄痭ull銆傛墍浠ユ垜浠鍦ㄥ規棩鏈熺殑瀛楁佃繘琛岃＄畻鏃訛紝浼樺厛闇瑕佽繘琛屾牸寮忕殑杞鎹錛屽皢yyyyMMdd杞鎹㈡垚yyyy-MM-dd鏍煎紡

杞鎹㈡柟寮忓備笅錛氫翰嫻嬪彲鐢

DATE(from_unixtime(unix_timestamp('20171205','yyyymmdd'),'yyyy-mm-dd'))