hive cheatsheet

------ search tables

show tables "*sc*bak*"

 

------ partial select

set hive.support.quoted.identifiers=none;

SELECT
`(mo|code)?+.+`,
NULL as zbk_tmc,
NULL AS zbkrv
FROM prd_updated.mes_sc_supportdb_scheduling_udl
;

 

window merge

------

group_concat

-------
https://www.cloudera.com/documentation/enterprise/5-8-x/topics/impala_create_table.html

-------
set hive.support.quoted.identifiers=none;

-------
CREATE TABLE
cdl.bw_sc_zocsds58l_cdl_ttest1
STORED AS PARQUET AS / ROW FORMAT DELIMITED FIELDS TERMINATED BY '^' stored as textfile AS
SELECT
`(update_timestamp)?+.+`,
NULL as zbk_tmc,
NULL AS zbkrv,
update_timestamp
FROM prd_cdl.bw_sc_zocsds58l_cdl
;

-------
ALTER TABLE idl.bw_sc_zocsds58l_idl CHANGE zbkrv zbkrv string AFTER zbk_tmc;

-------
set hive.cli.print.header=true;

-------
SHOW TABLES '.*s';

 

------- row store
row format delimited fields terminated by '\t'

------- column store
STORED AS PARQUET

------- unix date to human date
SELECT from_unixtime(CAST(current_timestamp() as BIGINT), 'yyyy-MM-dd HH:mm:ss') as col from

 

----- drop partition
alter table table_name drop partition (partition_col>='')