Hive 常用命令
ALTER TABLE ACCOUNTBALANCESEXTENDED ADD PARTITION (dt='20170612') location '/cdsh/cdsh_processed/ACCOUNTBALANCESEXTENDED/20170612'; SELECT COUNT(*),SUM(CAST('closing_balance' AS DOUBLE)) FROM ACCOUNTBALANCESEXTENDED WHERE dt='20170612';
show partitions MUREX_CNFOPT_REG_SHIBOR;
SELECT COUNT(*),SUM(CAST(TP_STRIKE AS DOUBLE)) FROM MUREX_CNFOPT_REG_SHIBOR WHERE dt='20170705';
SELECT COUNT(*),SUM(CAST((regexp_replace(closing_balance,',','')) AS DOUBLE)) FROM ACCOUNTBALANCESEXTENDED WHERE dt=20170116;
CREATE EXTERNAL TABLE `MUREX_CNFOPT_REG_SHIBOR`( `H_REP_DATE` string, `NB` string, `H_TRNDTE` string, `TRN_TYPE` string, `TP_PFOLIO` string, `TP_CNTRP` string, `TP_CNTRPFN` string, `H_CTRP_TYP` string, `TP_STRTGY` string, `H_BS` string, `H_CP` string, `TP_NOMINAL` string, `TP_NOMCUR` string, `H_OTH_NOM` string, `H_OTH_CUR` string, `TP_STRIKE` string, `MP_SPOT2` string, `TP_IPAY` string, `TP_IPAYCUR` string, `H_DTEEXP` string, `H_DTEUNDD` string, `TP_FXBASE` string, `H_DELTA2` string, `H_DELTA1` string, `H_DELTA` string, `S_GAMMA2` string, `S_GAMMA1` string, `H_GAMMA` string, `S_RHO2` string, `S_RHO1` string, `H_RHO` string, `S_THETA2` string, `S_THETA1` string, `H_THETA` string, `S_VEGA2` string, `S_VEGA1` string, `H_VEGA` string) PARTITIONED BY ( `dt` string) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\u0001' STORED AS INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' LOCATION '/cdsh/cdsh_processed/Murex_Cnfopt_Reg_Shibor';