hive ---HQL知识

63 阅读 0 评论 42 点赞

我是靠谱客的博主善良小馒头，最近开发中收集的这篇文章主要介绍hive ---HQL知识，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

一、数据库
1. 创建database
create database if not exists db_hive ;
create database if not exists db_hive_03 location '/user/root/hive/warehouse/db_hive_03.db';

2. 查询所有database
show databases;

3. 数据库删除
drop database db_hive_08;数据库中有表存在不能这样删除。
drop database if exists db_hive_08 cascade;用级联删除。

4. 常见命令
show命令的主要作用是查看database、table、function等组件的名称信息，也就是通过show命令我们可以知道我们的hive中有那些database；当前database中有那些table。等等。和mysql的show命令类型。

describe命令的主要作用是获取database、table、partition的具体描述信息，包括存储位置、字段类型等信息。

explain命令的主要作用是获取hql语句的执行计划，我们可以通过分析这些执行计划来优化hql语句。

二、表
数据库名：db_hive
表名：stu

数据类型：
HQL支持基本类型和复杂类型两大类数据类型。
基本类型包括TINYINT(1byte), SMALLINT(2byte), INT(4byte), BIGINT(8byte), FLOAT(4byte), DOUBLE(8byte), BOOLEAN(-), STRING(2G)。
复杂类型包括ARRAY(一组有序数组，类型必须一致), MAP(无序键值对，键值内部字段类型必须相同，而且要求key的类型为基本数据类型), STRUCT(一组字段，类型任意)。

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

desc stu;
desc extended stu;
desc formatted db_hive.stu; 查出的信息经格式化后的。

[ROW FORMAT row_format], row_format格式：
       delimited fields terminated by '01' collection terminated by '02' map keys terminated by '03' lines terminated by '04' NULL DEFINED AS 'N'
   [STORED AS file_format], file_format格式：
       sequencefile
       textfile(default)
       rcfile
       orc
       parquet
       avro

create table if not exists db_hive.my_table(
id int,
name string,
age int,
history string
)
comment '表级别注释‘

CREATE [EXTERNAL] TABLE [IF NOT EXISTS]
[db_name.]table_name (
col1_name col1_type
[COMMENT col1_comment],....
) -- 指定表的名称和表的具体列信息

[COMMENT table_comment] --表的描述信息

[PARTITIONED BY (col_name col_type [COMMENT col_comment],...)] --表的分区信息
[CLUSTERED BY (col_name, col_name,....) [SORTED BY (col_name [ASC|DESC],...])] INTO num_buckets BUCKETS] -- 表的分桶信息
[ROW FORMAT row_format] -- 表的数据分割信息，格式化信息
[STORED AS file_format] -- 表数据的存储序列化信息
[LOCATION hdfs_path]; -- 数据存储的文件夹地址信息

CREATE [EXTERNAL] TABLE [IF NOT EXISTS]
[db_name.]table_name LIKE existing_table_or_view_name -- 指定要创建的表和已存在表/视图的名称
[LOCATION hdfs_path]; -- 数据文件存储的hdfs文件地址信息

CREATE [EXTERNAL] TABLE [IF NOT EXISTS]
[db_name.]table_name -- 指定要创建的表名
.... 指定partition&bucket等信息，指定数据分割符号。
[AS select_statement]; -- 导入的数据

删除表的时候，内部表不管是否指定location，均会删除文件夹，外部表一定不会删除。

select命令和表中sql中的定义是一样的，都是查询数据表的数据，命令格式如下所示：
[WITH CommonTableExpression (, CommonTableExpression)*] -- 公用的table表达式，简称CTE
SELECT [ALL | DISTINCT] select_expr, select_expr, ... -- 返回的查询列表
FROM table_reference -- from语句，一般可以放到with之后，select之前
[WHERE where_condition] -- where过滤条件
[GROUP BY col_list [HAVING having_condition]] -- 分组条件
[ORDER|SORT BY colName (ASC|DESC)] -- 排序条件
[LIMIT number] -- limit条件

DISTINCT：去重。

From语句主要是指定从那个数据表中查询数据，有两种使用方式：分别可以在select后和with后&select前使用，语法格式：
1. [with ...] select ... from ...
2. [with ...] from ... select ...
示例：
select * from students;
from students select *;

select * from students limit 5; 前5条数据

Common Table Expression(CTE)主要作用是保存临时结果，作为查询语句的公用部分，方便后面的select查询中重复使用。语法规则为:
with cte_name [AS (select statment)] (,cte_name [AS (select statment)])* select ....

Order/Sort by语句主要用于hive中的数据排序，这两个命令的使用方式都类似sql语法中的order by。两者的主要区别是：sort by保证单reducer有序，order by保证全局有序，也就是说当reducer个数为多个的时候，使用sort by可能出现局部有序的情况。另外对于order by操作，有一个小的限制，就是当hive.mapred.mode模式为strict的时候，在order by语句后面必须跟着limit语句。语法格式如下：
(order|sort) by colname [asc|desc] (,colname [asc|desc])*

在多表查询的时候，由于表与表之间有关联性，所有hive提供了join的语法，基本类似sql的join语法。主要分为以下五类：
1. 内连接(JOIN)
2. 外链接({LEFT|RIGHT|FULL} [OUTER] JOIN)
3. 半连接(LEFT SEMI JOIN)
4. 笛卡尔连接(CROSS JOIN)
5. 其他连接方式(eg. mapjoin等)
语法格式：
left_table_reference [join type] right_table_ref [join_condition]* ([join type] right_table_ref [join_condition]*)*

1. 等值连接：hive中的所有连接条件必须为等值连接条件，不支持<>等非等值连接方式。
2. 多表连接：多表连接的时候，一般先进行left semi join，然后再进行join, 再进行外连接。(减少数据量)。
3. join过滤条件，可以将where的过滤条件移动到join的过滤条件中去，这样可以减少网络数据量。
4. join执行顺序都是从左到右，不管是那种join方式，那么一般将大的表放到右边，这样可以节省内存&减少网络传输。
5. mapjoin只适合连接表是小表的情况，是一种空间换时间的解决方案。

内连接(JOIN)主要作用是获取连接的两张表全部匹配的数据，如果不给定join_condition的话，会进行笛卡尔乘积。笛卡尔连接(CROSS JOIN)和内连接语法一样，区别在于：笛卡尔连接是对内连接的一种优化。语法格式为:
table_reference [cross] join table_factor [join_condition]

外连接的主要作用是保留一部分没有匹配的数据。左外连接(LEFT OUTER JOIN)的结果是包括左表中的所有行，如果左表中的某一个行在右表中不存在，那么则在相关联的结果集中右表的所有选择列值均设置为空值。右外连接(RIGHT OUTER JOIN)就是左外连接的反先连接，将返回右表的所有行，左表进行空值填充。全外连接(FULL OUTER JOIN)返回左表和右表的所有行，关联表中没有匹配值的直接设置为空值。语法格式为:
table_reference {left|right|full} [outer] join table_factor join_condition

半连接(LEFT SEMI JOIN)是hive特有的，hive中不支持in/exists操作，所以hive提供了一个替代方案。需要注意的是，被连接的表(右表)，不能出现在查询列/其他部分(where等)中，只能出现在on字句中。(出现也是无效的)。提出半连接的主要作用其实是提高查询效率，真正来讲的话，hive中可以使用其他连接方式来代替半连接，但是就效率而已的话，还是半连接比较高效。语法格式：
table_reference LEFT SEMI JOIN table_factor join_condition

如果所有被连接的表都是小表，那么可以使用mapjoin,将需要连接的表数据全部读入mapper端内存中。也就是说你使用mapjoin的前提就是你的连接数据比较小，mapjoin需要和其他join方式一起使用，一般情况下使用mapjoin的时候，推荐使用内连接。语法格式为:
select /*+ MAPJOIN(table_ref1) */ ... from table_ref join table_ref1 on ....;

Hive对子查询的支持有限，只支持嵌套select子句，而且只能在from和with语句块中使用子查询。语法规则如下：
.... from (select statement) [[as] tmp_name]....

Hive中导出数据主要分为两大类，分别是导出数据到linux系统和导出数据到hdfs文件系统上。另外也可以认为导出数据到其他hive表也算导出数据(不过该方式也是导入数据)。命令如下：
INSERT OVERWRITE [LOCAL] DIRECTORY directory1 [row format row_format] [stored as file_format] SELECT ... FROM ...
注意：hive导出数据支持一次导出到多个文件夹中，同多表同时导入数据一样。
insert overwrite local directory '/opt/datas/hive_emp' select * from default.emp;

union: 进行数据合并，格式为:
select1 union all select2... union all selectn
explain: hql执行计划查询。格式为: explain hql
hive -e "hql"：在linux系统中执行hive语句。
hive -f "filepath": 执行linux系统中的包含hive语句的文件。
view：hive支持视图的使用，单只支持逻辑视图，不支持物理视图。使用语法和table类型，创建语法格式为: create view viewname as select_statement.

三、表类型
hive 中有两种表类型：管理表（manage_table）、托管表（外部表）。

Hive中的表可以分为内部表(托管表)和外部表，区别在于：外部表的数据不是有hive进行管理的，也就是说当删除外部表的时候，外部表的数据不会从hdfs中删除。而内部表是又hive进行管理的，在删除表的时候，数据也会删除。一般情况下，我们在创建外部表的时候会将表数据的存储路径定义在hive的数据仓库路径之外。
Hive创建表主要有三种方式，第一种直接使用create table命令，第二种使用create table .... AS select ....(会产生数据)。第三种使用create table tablename like exist_tablename.命令。

四、导入数据
一、导入数据
1. 分别导入local和hdfs的数据
       a. 分别从linux机器上导入数据
           load data local inpath '/home/hadoop/datas/13/classes.txt' into table classes;
           load data local inpath '/home/hadoop/datas/13/students.txt' into table students;
           load data local inpath '/home/hadoop/datas/13/classes.txt' overwrite into table classes;
       b. 从hdfs上导入数据
           load data inpath '/beifeng/13/students.txt' into table students;
           dfs -put /home/hadoop/datas/13/students.txt /beifeng/13/
           load data inpath '/beifeng/13/students.txt' overwrite into table students;
   2. 导入其他表的数据(多表插入)
       将学生表的学生id和classid分别导出到不同表中，
       create table test1(id int);
       create table test2(id int);
       from students insert into table test1 select studentid insert overwrite table test2 select distinct classid where classid is not null;

五、常见操作

1. 属性当前用户设置、函数查询
hive> set; 会出现所有要设置的属性信息。设置当前生效。

hive> show functions; 查询所有函数

2. 直接执行hive命令
hive> hive -help 查询hive帮助
* bin/hive -e <quoted-query-string>
eg:
bin/hive -e "select * from db_hive.student ;"

* bin/hive -f <filename>

eg:
   $ touch hivef.sql
       select * from db_hive.student ;
   $ bin/hive -f /opt/datas/hivef.sql
   $ bin/hive -f /opt/datas/hivef.sql > /opt/datas/hivef-res.txt

* bin/hive -i <filename>
与用户udf相互使用

在hive cli命令窗口中如何查看hdfs文件系统
hive (default)> dfs -ls / ;

在hive cli命令窗口中如何查看本地文件系统
hive (default)> !ls /opt/datas ；

3. hive中操作hdfs系统

hive> dfs 命令直接操作hdfs系统

六、分区

修复分区表 1. msck repair table dept_part;
2. alter table dept_part add partition(day = '20150913');

静态分区：分区的值是确定的【假设有一个程序，每天都统计销售额，按照日期进行分区每天的插入到制定的日期分区】
动态分区：分区的值是不确定的，由输入的数据确定【比如：京东有很多二级类目，每一个二级类目对应多个产品，按照二级类目分区】

是否是动态分区由set hive.exec.dynamic.partition决定【=true时为动态分区】

七、sql语句注意事项：

group by ：陷阱：排序的字段，必须在查询的字段中
where 是针对单条记录进行筛选的；
having 是针对分组结果进行筛选的（常与group by 联用）；
export 将hive中的数据导出到外部
import 将外部数据导入到hive中
export table 表名 to ‘hdfs 上的文件全路径’；

八、Hive Web界面安装
Hive提供了一个Web的操作客户端，一般我们通过该页面查看hive的信息，配置信息包括：hive.hwi.listen.host指定监听ip(默认0.0.0.0)，hive.hwi.listen.port监听端口(默认9999)，hive.hwi.war.file=xxx。另外需要将java的tool.jar文件移动到hive的lib文件夹中。启动命令为: hive --service hwi &

Hive默认提供的cli(shell)服务，如果需要启动其他服务，那么需要service参数来启动其他服务，比如thrift服务、metastore服务等。可以通过命令hive --service help查看hive支持的命令。

Hive的Shell基本常用命令主要包含退出客户端、添加文件、修改/查看环境变量、执行linux命令、执行dfs命令等。命令包括：quit、exit、set、add JAR[S] <filepath> <filepath>*、list JAR[S]、delete JAR[S] <filepath>*、! <linux-command>、dfs <dfs command>等。除了Hive的基本命令外，其他的命令主要是DDL和DML等操作数据表的命令。

九、离线数据分析平台实战培训

HQL内嵌函数只有195个函数(包括操作符，使用命令show functions查看)，基本能够胜任基本的hive开发，但是当有较为复杂的需求的时候，可能需要进行定制的HQL函数开发。HQL支持三种方式来进行功能的扩展(只支持使用java编写实现自定义函数)，分别是：UDF(User-Defined Function)、UDAF(User-Defined Aggregate Function)和UDTF(User-Defined Table-Generating Function)。当我们使用java语言进行开发完成后，将生成的jar包移到linux机器(hive机器)上,进行函数的创建，然后进行使用即可。

HQL函数的创建一般分为以下几步：
1. 添加jar(0.13.*不支持hdfs上的jar添加，14版本才开始支持)
add jar linux_jar_path
2. 创建function，语法规则如下：
create [temporary] function [dbname.]function_name AS class_name;
3. 使用function，和使用其他函数一样。

我们可以通过drop命令删除自定义函数，语法规则如下：
drop [temporary] function [if exists] [dbname.]function_name;

UDF(User-Defined Function)支持一个输入产生一个输出，是一个最常用的自定义函数类型。实现自定义UDF要求继承类org.apache.hadoop.hive.ql.exec.UDF，并且在自定义UDF类中重载实现evaluate方法，我们可以通过重载多个evaluate方法达到函数参数多样化的需求。
实现案例：实现一个大小写转换的函数，要求函数通过参数的不同决定是进行那种转换，默认是转换为小写。

UDAF(User-Defined Aggregate Function)支持多个输入，一个输出。在原来的版本中可以通过继承UDAF类来实现自定义UDAF，但是现在hive已经将这个类标注为弃用状态。现在一般通过继承AbstractGenericUDAFResolver类来实现自定义UDAF，通过这种方式要求实现自定义的GenericUDAFEvaluator。也就是说在现在的hive版本中，实现自定义UDAF，那么需要实现两个类，分别是AbstractGenericUDAFResolver和GenericUDAFEvaluator。

AbstractGenericUDAFResolver类主要作用就是根据hql调用时候的函数参数来获取具体的GenericUDAFEvaluator实例对象，也就是说实现方法getEvaluator即可,该方法的主要作用就是根据参数的不同返回不同的evaluator实例对象，实现多态性。

GenericUDAFEvaluator类主要作用就是根据job的不同阶段执行不同的方法。hive通过GenericUDAFEvaluator.Model来确定job的执行阶段。PARTIAL1：从原始数据到部分聚合，会调用方法iterate和terminatePartial方法；PARTIAL2：从部分数据聚合和部分数据聚合，会调用方法merge和terminatePartial；FINAL：从部分数据聚合到全部数据聚合，会调用方法merge和terminate；COMPLETE：从原始数据到全部数据聚合，会调用方法iterate和terminate。除了上面提到的iterate、merge、terminate和terminatePartial以外，还有init(初始化并返回返回值的类型)、getNewAggregationBuffer(获取新的buffer对象，也就是方法之间传递参数的对象)，reset(重置buffer对象)。

实现一个自定义的sum函数。要求函数支持整形和浮点型的sum操作。

UDTF(User-Defined Table-Generating Function)支持一个输入多个输出。一般用于解析工作，比如说解析url，然后获取url中的信息。要求继承类org.apache.hadoop.hive.ql.udf.generic.GenericUDTF，实现方法：initialize(返回返回值的参数类型)、process具体的处理方法，一般在这个方法中会调用父类的forward方法进行数据的写出、close关闭资源方法，最终会调用close方法，同MR程序中的cleanUp方法。
实现功能：解析爬虫数据，从数据中读取产品id、产品名称、价格。

首先要求创建的function是永久function，不能是临时function。
第一种：修改hive-site.xml文件，添加参数hive.aux.jars.path，value为jar包的linux本地路径，要求是以file:///开头的绝对路径。
第二种：直接将jar包移动到hive的lib文件夹中。
第三种：将jar包移动到hdfs上，然后在创建function的时候指定function使用的hdfs上的jar文件绝对路径(包括hdfs://hh:8020/前缀)，这样在使用的时候，hive会自动将jar下载到本地进行缓存的。

我们可以通过修改hive的源码，进行自定义函数的添加，添加完成后，我们就不需要再手动创建函数。添加步骤如下：
1. 假设自定义函数的整个包名为com.beifeng.ql.udf.UDFTest, jar文件为beifengUserUDF.jar。将该jar包移动到hive的lib文件夹中。
2. 修改hive源文件$HIVE_HOME/src/ql/src/java/org/apache/hadoop/hive/ql/exec/FunctionRegistry.java，添加import com.beifeng.ql.udf.UDFTest; registerUDF("test", UDFTest.class,false);
3. 编译hive后，进行jar包的替换，然后就可以使用函数了。

十、 hive中操作 hdfs Linux本地

hive中操作hdfs
hive> dfs -ls /;

hive 查询Linux本地文件系统
hive> !ls /opt/modules;