hive 操作笔记

1、查看数据库的描述及路径

hive> describe database database_name;

2、建表 创建脚本 /opt/createtable.sql

create table IF NOT EXISTS lining_test.emp( empno int, ename string, job string, mgr int, hiredate string, sal double, comm double, deptno int ) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

执行脚本

hive>source /opt/createtable.sql;

加载数据

load data local inpath '/opt/test/emp.txt' overwrite into table emp;

查询

use lining_test;select * from emp;

增加列

use lining_test;alter table emp add columns(year string);

3、分区表 创建脚本/opt/createparttable.sql

create table lining_test.emp_part( empno int, empname string, job string, mgr int, hiredate string, salary double, comm double, deptno int) partitioned by (year string, month string) row format delimited fields terminated by '\t';

执行脚本

hive>source /opt/createparttable.sql;

加载数据

load data local inpath '/opt/emp2.txt' into table lining_test.emp_part partition(year='2018',month='8'); load data local inpath '/opt/emp3.txt' into table lining_test.emp_part partition(year='2018',month='3'); load data local inpath '/opt/emp4.txt' into table lining_test.emp_part partition(year='2017',month='12');

查询

use lining_test;select * from emp_part;

查询表结构

describe lining_test.emp_part;

4、直接用hive命令

hive -e "use lining_test;ALTER TABLE emp CHANGE payyear month string;" hive -f "select * from db_hive01.emp"

5、导出结果

insert overwrite local directory '/opt/test/' row format delimited fields terminated by '\t' select * from emp_part; hive -e 'select * from emp_part' >> ./emp_export.txt

6、导出到HDFS

insert overwrite directory '/export_data' select * from lining_test.emp;

有local的速度明显比没有local慢

7、显示所有函数：

hive>show functions;

查看函数的用法；

hive>describe function fun_name;

常用函数

select upper(empname) from emp; select unix_timestamp(trackTime) from bflog limit 3 ; select year(hiredate) from emp ; select month(hiredate) from emp ; select hour(hiredate) from emp ; select substr(hiredate,1,4) from .emp ; select split(hiredate,'-')[1] from emp ; select reverse(hiredate) from emp ; select concat(empno,'-',empname) from emp ; case when 条件1 then ... when 条件2 then ... else end

自定义UDF

add jar /opt/test/mylower.jar ; CREATE TEMPORARY FUNCTION mylower AS 'org.gh.hadoop.hive.MyLower';

8、内连接： hive> SELECT sales., things. FROM sales JOIN things ON (sales.id = things.id);

9、查看hive为某个查询使用多少个MapReduce作业 hive> Explain SELECT sales., things. FROM sales JOIN things ON (sales.id = things.id);

10、左连接： hive> SELECT sales., things. FROM sales LEFT OUTER JOIN things ON (sales.id = things.id);

11、Map连接：Hive可以把较小的表放入每个Mapper的内存来执行连接操作 hive> SELECT /+ MAPJOIN(things) / sales., things. FROM sales JOIN things ON (sales.id = things.id);

12、insert 导入

INSERT OVERWRITE TABLE stations_by_year SELECT year, COUNT(DISTINCT station) GROUP BY year

13、创建视图：

CREATE VIEW valid_records AS SELECT * FROM records2 WHERE temperature !=9999;

14、hive中使用正则表达式 选出所有列名以price作为前缀的列 （1） hive>select 'price.*' from product; （2） 用Like或者RLike

15、order by 全局排序

insert overwrite local directory '/opt/test/local' row format delimited fields terminated by '\t' select * from emp order by empno;

16、sort by 与 distributed by 类似MR中partition，进行分区，结合sort by使用每个reduce内部进行排序，全局不是排序， distribute by 一定是放在sort by 前面，且必须要指定mapreduce.job.reduces数量，否则导出结果还是在一个文件中

set mapreduce.job.reduces=3; insert overwrite local directory '/opt/test/local' row format delimited fields terminated by '\t' select * from emp distribute by deptno sort by empno;

当distributed by和sort by 字段一样的时候，直接使用cluster by

17、聚合函数 可以通过设置属性hive.map.aggr值为true来提高聚合的性能： hive>hive.map.aggr=true;

18、word count

select word,count(*) from( select explode(split(sentence,' ')) as word from article )t group by word;