Hive命令及操作

1.hive表操作

复制表结构

create table denserank_amt like otheravgrank_amt;
修改表名

alter table tmp rename to credit_card;

修改表结构

修改column属性
ALTER TABLE table_name 
      CHANGE [COLUMN] col_old_name col_new_name 
      column_type [COMMENT col_comment] [FIRST|AFTER column_name]
 alter table otheravg_amt change cr_tx_cod cr_tx_amt float;

增加/替换column（可以使用replace来删除不需要的字段）
ALTER TABLE table_name 
      ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...)

比如
alter table alter_test replace columns (id int, name string);  
alter table alter_test add columns(test_columen INT);

插入语句

hive> insert into table cite select * from tt;
追加数据到cite表格中
hive> insert overwrite table cite  select * from tt;
覆盖已经存在的数据

2.hive查询语句

建表
create table otheravg_amt(bus_inst_no string,canal string,sa_tx_dt string,dr_cr_cod string,cr_tx_cod float,f_fare float,counts bigint);

将查询结果插入新表，包含截取字段，求平均数
insert overwrite table otheravg_amt select substr(bus_inst_no,0,5),canal,sa_tx_dt,dr_cr_cod,avg(cr_tx_amt),avg(f_fare),avg(counts) from branch_amt group by substr(bus_inst_no,0,5),canal,sa_tx_dt,dr_cr_cod;

只取前5条记录
select * from otheravg_amt limit 5;

筛选

select deptno,sum(sal) from emp group by deptno having sum(sal)>10000;

同表同一列的数相减

select
(select count from table where name = '1'
)-
(select count from table where name = '2')
from dual;

insert into table branch_atmzc_sum
Select canal, XT_OP_TRL, SA_TX_DT,'number2-number1',b.cr_tx_amt-a.cr_tx_amt from branch_atmzc a, branch_atmzc b where a.canal = b.canal and a.XT_OP_TRL = b.XT_OP_TRL and a.SA_TX_DT = b.SA_TX_DT and a.tran_cd = 'number1' and b.tran_cd = 'number2';

mysql同一列时间依次与最大时间相减
SELECT  SUM(TIMEDIFF(paytime, (SELECT MAX(paytime) FROM 表) ))
FROM  表

3.本地文本文件数据导入hive中

load data local inpath '/mnt/pc/pcclientlogs/test.log' into table test partition (dt = '20120917');

4. hive导出查询结果到本地文件

语法：INSERT OVERWRITE [LOCAL] DIRECTORY directory1 select_statement1

查询结果可以直接导到excel中等，但需要注意的是hive导出文件时使用的分隔符，否则会出现乱码。hive是使用^A符号作为域的分隔符，原文如下：

     Data written to the filesystem is serialized as text with columns separated by ^A

在python中可以使用line.split('x01')来进行切分，也可以用line.split('01')

如果需要将查询结果导出到本地文件，可以直接使用hive，很方便。

      bin/hive -e "select * from test" >> res.csv
或者是：  
      bin/hive -f sql.q >> res.csv

其中文件sql.q写入你想要执行的查询语句

     这问题在使用hive hql streaming时也会遇到，当你使用hql strreaming 将输出直接写入到hdfs目录，然后你在该目录上创建hive 外部表时，hive的输出结果会将streaming 的key和value之间的分隔符修改为 01，所以，在hql streaming和输出为hive 外部表时，最好将streaming的最后输出的分隔符修改为01，并且hive外部表的分隔符应该设置为01，注意：不要使用^A和x01，如：

    create external table site_user_mapping_info_month(uid String,tag string,project_ids string,site_interests string) PARTITIONED BY(year String, month String) ROW FORMAT DELIMITED FIELDS TERMINATED BY '01' LOCATION '/project/site_grouping/site_user_mapping_info_month';

注意：将hive结果导入到hdfs上，不能指定分割符

1.将select的结果放到一个的的表格中（首先要用create table创建新的表格）

 insert overwrite table test

 select uid,name from test2;

2.将select的结果放到本地文件系统中

INSERT OVERWRITE LOCAL DIRECTORY '/tmp/reg_3' SELECT a.* FROM events a;

3.将select的结果放到hdfs文件系统中

INSERT OVERWRITE DIRECTORY '/tmp/hdfs_out' SELECT a.* FROM invites a WHERE a.ds='<DATE>'

如：

hive> insert overwrite local directory '/home/wyp/Documents/result' row format delimited fields terminated by '	'  select * from test;

　如果是map类型。还可以指定分割符来划分key和value

hive> insert overwrite local directory './test-04'  row format delimited  FIELDS TERMINATED BY '	'  COLLECTION ITEMS TERMINATED BY ','  MAP KEYS TERMINATED BY ':'  select * from src;