Hive命令及操作

时间：2024-01-17 18:44:14


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('\001')


如果需要将查询结果导出到本地文件，可以直接使用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之间的分隔符修改为 \001，所以，在hql streaming和输出为hive 外部表时，最好将streaming的最后输出的分隔符修改为\001，并且hive外部表的分隔符应该设置为\001，注意：不要使用^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 '\001' 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 '\t'  select * from test;




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




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





　　
相关文章