【Hive】Hive基本操作及示例

版权声明:本文为博主原创文章,转载请注明出处。 https://blog.csdn.net/gongxifacai_believe/article/details/80144470

1、数据库操作 (1)创建数据库

create database db_hive_01 ;
create database if not exists db_hive_02 ;     标准方式
create database if not exists db_hive_03 location '/user/beifeng/hive/warehouse/db_hive_03.db' ;     指定数据库位置

(2)查看数据库

show databases ;
show databases like 'db_hive*' ;      模糊查找

(3)使用数据库

use db_hive ;

(4)查看数据库字段格式

desc database db_hive_03 ;
desc database extended db_hive_03 ;

(5)删除数据库

drop database db_hive_03 ;
drop database db_hive_03 cascade;
drop database if exists db_hive_03 ;

2、表操作 (1)创建表

create table IF NOT EXISTS default.bf_log_20150913(
     ip string COMMENT 'remote ip address' ,
     user string ,
     req_url string COMMENT 'user request url')
COMMENT 'BeiFeng Web Access Logs'
ROW FORMAT DELIMITED FIELDS TERMINATED BY ' '
STORED AS TEXTFILE ;
create table IF NOT EXISTS default.bf_log_20150913_sa
AS select ip,req_url from default.bf_log_20150913 ;

(2)加载数据到表

load data local inpath '/opt/datas/bf-log.txt' into table default.bf_log_20150913;

LOCAL:从本地文件加载数据到hive表;否则从HDFS加载数据到hive表。 OVERWRITE:是否覆盖表中已有数据。 (3)ETL介绍 E(Extract 提取)-----------创建表,加载原数据到表 T(Transform 转换)------用python等脚本语言处理表中数据 L(Load 加载)--------------将处理后的结果加载到子表等目的端 3、示例表 员工表

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

部门表

create table IF NOT EXISTS default.dept(
     deptno int,
     dname string,
     loc string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

4、对示例表的操作 (1)加载数据到表

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

(2)创建子表

create table if not exists default.dept_ctas
as
select * from dept ;

(3)清除表中数据

truncate table dept_ctas ;

(4)Like方式创建表

create table if not exists default.dept_like
like
default.dept ;

(5)修改表的名字

alter table dept_like rename to dept_like_rename ;

(6)删除表

drop table if exists dept_like_rename ;

5、表的类型 在Hive中表的类型有管理表和托管表(外部表)。 外部表与内部表的区别: <1>内部表也称之为MANAGED_TABLE,默认存储在/user/hive/warehouse下,也可以通过location指定,删除表时,会删除表数据以及元数据。 <2>外部表称之为EXTERNAL_TABLE,在创建表时可以自己指定目录位置(LOCATION),删除表时,只会删除元数据不会删除表数据。 (1)创建外部表

create EXTERNAL table IF NOT EXISTS default.emp_ext2(
     empno int,
     ename string,
     job string,
     mgr int,
     hiredate string,
     sal double,
     comm double,
     deptno int)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
location '/user/beifeng/hive/warehouse/emp_ext2';

(2)将数据上传到外部表

dfs -put /opt/datas/emp.txt /user/beifeng/hive/warehouse/emp_ext2;

6、分区表 分区表实际上就是对应一个HDFS文件系统上的独立的文件夹,该文件夹下是该分区所有的数据文件。Hive中的分区就是分目录,把一个大的数据集根据业务需要分割成更下的数据集。在查询时通过WHERE子句中的表达式来选择查询所需要的指定的分区, 这样的查询效率会提高很多。 (1)一级分区,按月分区

create external table if not exists default.emp_partition(
     empno int,
     ename string,
     job string,
     mgr int,
     hiredate string,
     sal double,
     comm double,
     deptno int)
partitioned by (month string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

查看表格式

hive (default)> desc formatted emp_partition;
# Partition Information          
# col_name              data_type               comment             
                 
month                   string                                      

加载数据到一级分区表

load data local inpath '/opt/datas/emp.txt' into table default.emp_partition partition (month='201509');

查看一级分区表

select * from emp_partition where month='201509';

(2)统计一个季度的IP地址

select count(distinct ip) from emp_partition where month='201509'
union
select count(distinct ip) from emp_partition where month='201508'
union
select count(distinct ip) from emp_partition where month='201507';

将该语句放入sql脚本中,使用方式:bin/hive -f xx.sql (3)二级分区,按月日分区

create EXTERNAL table IF NOT EXISTS default.emp_partition(
     empno int,
     ename string,
     job string,
     mgr int,
     hiredate string,
     sal double,
     comm double,
     deptno int)
partitioned by (month string,day string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' ;

加载数据到二级分区表

load data local inpath '/opt/datas/emp.txt' into table default.emp_partition partition (month='201509',day='13') ;

查看二级分区表

select * from emp_partition where month = '201509' and day = '13' ;

(4)注意事项 <1>创建不分区表

create table IF NOT EXISTS default.dept_nopart(
     deptno int,
     dname string,
     loc string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

将数据上传到HDFS上不分区表的目录中

dfs -put /opt/datas/dept.txt /user/hive/warehouse/dept_nopart ;

查看不分区表中是否已填充数据

select * from dept_nopart ;

不分区表中已填充了HDFS上不分区表所在目录中的数据 <2>创建分区表

create table IF NOT EXISTS default.dept_part(
     deptno int,
     dname string,
     loc string)
partitioned by (day string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

将数据上传到HDFS上分区表的目录中

dfs -put /opt/datas/dept.txt /user/hive/warehouse/dept_part ;

查看不分区表中是否已填充数据

select * from dept_part ;

发现分区表中并未自动填充HDFS上分区表所在目录中的数据。原因是由于MySQL数据库中的PARTITION元数据并没有该分区表中的分区信息。解决方式有两种。 <3>第一种方式

dfs -mkdir -p /user/hive/warehouse/dept_part/day=20150913 ;
dfs -put /opt/datas/dept.txt /user/hive/warehouse/dept_part/day=20150913 ;
msck repair table dept_part ;     修复分区表

<4>第二种方式

dfs -mkdir -p /user/hive/warehouse/dept_part/day=20150914 ;
dfs -put /opt/datas/dept.txt /user/hive/warehouse/dept_part/day=20150914 ;
alter table dept_part add partition(day='20150914');   增加分区

<5>查看表中有什么分区

show partitions dept_part ;

7、加载数据

load data [local] inpath 'filepath' [overwrite] into table tablename [partition (partcol1=val1,...)];

<1>原始文件存储的位置:本地local、hdfs。 <2>对表的数据是否覆盖:覆盖overwrite、追加。 <3>分区表加载:partition (partcol1=val1,…)。 例: <1>加载本地文件到hive表

load data local inpath '/opt/datas/emp.txt' into table default.emp ;

<2>加载hdfs文件到hive中

load data inpath '/user/beifeng/hive/datas/emp.txt' overwrite into table default.emp ;

<3>加载数据覆盖表中已有的数据

load data inpath '/user/beifeng/hive/datas/emp.txt' overwrite into table default.emp ;

<4>创建表是通过insert加载

create table default.emp_ci like emp ;
insert into table default.emp_ci select * from default.emp;

5)创建表的时候通过location指定加载 8、输出数据

insert overwrite local directory '/opt/datas/hive_exp_emp'
select * from default.emp ;
insert overwrite local directory '/opt/datas/hive_exp_emp2'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' COLLECTION ITEMS TERMINATED BY '\n'
select * from default.emp ;
insert overwrite directory '/user/beifeng/hive/hive_exp_emp'
select * from default.emp ;
bin/hive -e "select * from default.emp ;" > /opt/datas/exp_res.txt

9、GROUP BY

SELECT [ALL | DISTINCT] select_expr, select_expr, ...
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list]
[CLUSTER BY col_list
  | [DISTRIBUTE BY col_list] [SORT BY col_list]
]
[LIMIT number]

例:

select * from emp limit 5 ;
select t.empno, t.ename, t.deptno from emp t where  t.sal between 800 and 1500 ;

is null / is not null /in /not in

select t.empno, t.ename, t.deptno from emp t where comm is null ;

max/min/count/sum/avg

select count(*) cnt from emp ;
select max(sal) max_sal from emp ;
select sum(sal) from emp ;
select avg(sal) from emp ;
desc function extended max;查看函数详细信息。

group by /having 分组 emp表 每个部门的平均工资

select t.deptno, avg(t.sal) avg_sal from emp t group by t.deptno ;

每个部门中每个岗位的最高薪水

select t.deptno, t.job, max(t.sal) avg_sal from emp t group by t.deptno, job ;

having where 是针对单条记录进行筛选,having 是针对分组结果进行筛选。 求每个部门的平均薪水大于2000的部门

select deptno, avg(sal) from emp group by deptno ;
select deptno, avg(sal) avg_sal from emp group by deptno having avg_sal > 2000;

10、join 对两个表进行连接,m表中的一条记录和n表中的一条记录组成一条记录。 等值join join … on

select e.empno, e.ename, d.deptno, d.dname from emp e join dept d on e.deptno = d.deptno ;

左连接 left join

select e.empno, e.ename, d.deptno, d.dname  from emp e left join dept d on e.deptno = d.deptno ;

右连接 right join

select e.empno, e.ename, e.deptno, d.dname  from emp e right join dept d on e.deptno = d.deptno ;

全连接 full join

select e.empno, e.ename, e.deptno, d.dname  from emp e full join dept d on e.deptno = d.deptno ;

11、导入与导出 Export 导出,将Hive表中的数据,导出到外部 Import 导入,将外部数据导入Hive表中

EXPORT TABLE default.emp TO '/user/beifeng/hive/export/emp_exp' ;
dfs -text /user/beifeng/hive/export/emp_exp/data/emp.txt;

该导出路径指的是HDFS上路径。

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name
  LIKE existing_table_or_view_name
  [LOCATION hdfs_path];

create table db_hive.emp like default.emp ;
import table db_hive.emp from '/user/beifeng/hive/export/emp_exp';

12、order by、sort by、distribute by、cluster by (1)order by 对全局数据的一个排序,仅仅只有一个reduce。

select * from emp order by empno desc ;

(2)sort by 对每一个reduce内部数据进行排序的,对全局结果集来说不是排序。

set mapreduce.job.reduces= 3;
select * from emp sort by empno asc ;
insert overwrite local directory '/opt/datas/sortby-res' select * from emp sort by empno asc ;

(3)distribute by 类似于MapReduce中分区partition,对数据进行分区,结合sort by进行使用。

insert overwrite local directory '/opt/datas/distby-res' select * from emp distribute by deptno sort by empno asc ;

注意:distribute by 必须要在sort by 前面。 (4)cluster by 当distribute by和sort by 字段相同时,可以使用cluster by 。

insert overwrite local directory '/opt/datas/cluster-res' select * from emp cluster by empno ;

13、UDF:User Definition Function UDF:用户自定义函数,允许用户扩展HiveQL功能; <1>UDF(User-Defined-Function) 一进一出; <2>UDAF(User-Defined Aggregation Funcation) 聚集函数,多进一出; 类似于:count/max/min。 <3>UDTF(User-Defined Table-Generating Functions) 一进多出。 编写用户定义函数需要在pom.xml文件中添加如下坐标。 pom.xml

		<!-- Hive Client -->
		<dependency>
			<groupId>org.apache.hive</groupId>
			<artifactId>hive-jdbc</artifactId>
			<version>${hive.version}</version>
		</dependency>
		<dependency>
			<groupId>org.apache.hive</groupId>
			<artifactId>hive-exec</artifactId>
			<version>${hive.version}</version>
		</dependency>

创建用户自定义函数步骤: (1)创建一个新类,继承自org.apache.hadoop.hive.ql.UDF,类中定义一个或多个函数,名字为evaluate,供hive调用,evaluate函数支持重载。 注意: <1>UDF必须要有返回类型,可以返回null,但是返回类型不能为void; <2>UDF中常用Text/LongWritable等类型,不推荐使用java类型。 (2)将创建的类打包成jar文件

add jar /opt/datas/hiveudf.jar ;

(3)以jar包中的类创建临时自定义函数

create temporary function my_lower as "com.beifeng.senior.hive.udf.LowerUDF" ;

(4)使用临时自定义函数

select ename, my_lower(ename) lowername from emp limit 5 ;

(5)以jar包中的类创建自定义函数

CREATE FUNCTION self_lower AS 'com.beifeng.senior.hive.udf.LowerUDF' USING JAR 'hdfs://hadoop-senior.ibeifeng.com:8020/user/beifeng/hive/jars/hiveudf.jar';

(6)使用自定义函数

select ename, self_lower(ename) lowername from emp limit 5;

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

扫码关注云+社区

领取腾讯云代金券