HIVE语法基础及实战----干货

目录

基础了解

Hive使用场景？

安装

Hive内部介绍

启动hive：

基础操作

Hiverc文件

数据类型和文件格式

数据定义

表操作

修改表

数据导出

Hql查询

Join优化

模式设计

调优

开发

Hive权限管理

Hive运行原理

---

基础了解

hive为什么会出现？

MapReduce无非就是一套计算模型，但是使用Hadoop的API来实现这种算法时、有许多细节需要用户自己来控制、这要求用户有一定的java代码能力、因此就将Hadoop放在了非程序员无法触及的位置，即使用户了解这些算法原理，不会java代码同样无法使用

所以hive提供了熟悉SQL的编程模型、减少了大量的代码工作

Hive使用场景？

Hive最适合于数据仓库程序----不需要实时响应查询、不需要记录级别的插入、更新和删除

安装

依赖：Jdk、Hadoop

解压：tar –zxf

Hive内部介绍

Lib目录：核心jar包，每个jar有特定功能

Bin：hive服务可执行文件

Thrift：可以远程访问其他进程、也提供jdbc、ODBC访问hive功能

Metastore service:元数据服务，通常情况下使用关系型数据库存储元数据

Conf：配置文件目录

启动hive：

保证hdfs正常启动

1、bin/hive --service metastore

./bin/hive

2、bin/hiveserver2

./bin/beeline -u jdbc:hive2://node212:10000 -n root --color=true

<configuration>         <property>                   <name>hive.metastore.warehouse.dir</name>                   <value>/user/hive/warehouse</value>           </property>           <property>                   <name>javax.jdo.option.ConnectionURL</name>                 <value>jdbc:mysql://node211/hive?createDatabaseIfNotExist=true</value>           </property>           <property>                   <name>javax.jdo.option.ConnectionDriverName</name>                   <value>com.mysql.jdbc.Driver</value>           </property>           <property>                   <name>javax.jdo.option.ConnectionUserName</name>                   <value>root</value>           </property>           <property>                   <name>javax.jdo.option.ConnectionPassword</name>                   <value>manzi</value>           </property> </configuration>

Mysql驱动包

基础操作

Set命令

设置hive变量和属性，Hive内部变量实际都是已字符串方式存储、用户可以在查询中引用变量${name}，Set命令可以修改和显示属性

查看属性

Set name;

Set;打印命名空间hivevar、hiveconf、system和env下的所有变量

Set –v;在set基础上加上Hadoop所定义的属性

添加/修改属性

Set name=manzi

system和env命名空间下的set属性必须加上前缀(system/env)，system空间下属性可以读写，env下的只读

hive中“一次使用”命令

./bin/hive -e "show databases;"

执行结束后退出cli

静默输出、-S-e顺序不能错

./bin/hive  -S  –e  "show databases;" ./bin/hive  -S  –e  "show databases;" > /temp/data.txt(本地目录非HDFS)

 

从文件中执行hive查询                -f

./bin/hive -f s.hql

 

在hive cli中，可以使用source来执行一个脚本文件

Hive> source s.hql(我的在目录下，所以脚本前面没有本地路径)

 

Hiverc文件

Hive的HOME目录下创建文件.hiverc文件，Cli启动时加上-i选项，hive会在HOME目录下默认执行.hiverc文件

Vim .hiverc

[root@node213 hive]# ./bin/hive -i .hiverc

 

自动补全tab

执行shell命令

用户不需要退出Cli就可以执行语法—>!开头;结束

hive> ! echo "hello manzi";

 

hive中使用hdfs命令

hive> dfs -ls /;

 

数据类型和文件格式

基本数据类型

         基本数据类型和java中的对应、在SQL方言中这些都是保留字，hive在限制字符长度上并不严格、其他SQL方言可能会限制最大输入长度，关系型数据库考虑到性能优化的因素、因为定长的字段更容易建索引、数据扫描；但是在hive中比较“宽松”，采取字段间的分隔符来对其进行判断，同时，Hadoop和hive强调优化磁盘的读写性能、而限制列的长度来说并不重要

类型	长度
Tinyint	1byte
Smalint	2
Int	4
Bigint	8
Boolean	True\false
Float	单精度
Double	双精度
String	字符序列、可以指定字符集
Timestamp	整数、浮点数或字符串
Binary	字节数组

集合数据类型

Hive中的列支持map、struct、array

文本文件数据编码

Hive默认记录和字段分隔符

分隔符	描述
\n	换行分隔符
^A (Ctrl+A)	列分割符、八进制编码\001
^B	用于array和struct之间的元素分割八进制\002
^C	用于map之间键和值的分割八进制\003

 

数据定义

Hive数据库

         本质上就是表的一个目录或命名空间，如果不显示指定数据库、默认为default

创建

Hive会为每个数据库创建一个目录、数据库中的表以子目录形式展示(default数据库没有自己的目录)

hive> create database if not exists manzi;

 

查看

hive> show databases;

 

修改

hive> alter database manzi set dbproperties('edited-by'='manzi');

 

删除

hive> drop database manzi;

 

表操作

创建表

hive> create table if not exists manzi.emp(     > name string comment 'empName',     > age int comment 'age',     > gender string comment 'sex')     > comment 'manzi test create emp'     > location '/user/hive/warehouse/manzi.db/emp';

 

复制表

hive> create table if not exists manzi.emp2 like manzi.emp;

 

查看表

hive> use manzi; --指定使用数据库 hive> show tables;--当前数据库表列表 hive> show tables 'default.*';--查看指定数据库下面的表

删除表

hive> drop table tab_name;

修改表

表重命名

hive> alter table emp3 rename to emp;

修改字段

hive> alter table emp change column name uname string;

增加列

hive> alter table emp add columns(paawd string);

 

 

内部表

当前创建的表默认都是内部表、表数据会存放在hive.metastore.warehouse.dir所定义的目录的表子目录下，删除一个内部表时，会删除表数据和元数据、同时内部表不方便数据和其他工作共享数据，比如其他工具创建的数据存在于hdfs中，hive想利用这一部分数据，是没有权限的，内部表location数据目录时，hdfs上的数据文件会被剪切到表目录下，原始文件将不存在

外部表

外部表就可以解决内部表数据共享问题，创建一个外部表指向数据存在位置、而不真正控制数据所有权

示例：创建外部表、字段以逗号分隔

hive> create external table if not exists stocks (     > number string comment 'class number',     > userid string comment 'user number',     > type string comment 'gender',     > scores string comment 'scores',     > dateStr string comment 'date',     > age string comment 'age'     > )     > row format delimited fields terminated by ','     > STORED AS TEXTFILE location '/user/hive/warehouse/manzi';--目录

外部表删除的时候，元数据被删除、数据文件还是会保留

数据导出

数据导出有两种方式：

直接拷贝数据存储文件dfs –cp sourcepath targetpath 查询语句导出： hive> insert overwrite local directory '/opt/manzi/hive/data' –目录     > select * from emp;

 

查看表类型

hive> describe extended stocks;

分区表、管理表

使用分区来水平分散压力、将数据从物理转移到和使用最频繁用户最接近的地方、以及实现其他目的、所以hive有分区表概念，分区表还可以将数据以一种符合逻辑的形式进行组织，比如分层存储，减少扫描成本、提高检索效率

分区表又分动态分区和静态分区，两者在创建方式上一样、一张表可以被同时静态分区和动态分区、但是动态分区要放在静态分区的后面(因为hdfs上动态分区目录下不能包含静态分区的子目录)

分区字段不能出现在表中，添加分区会添加伪列，其实分区就相当于将满足条件的数据放到一个文件夹

创建

hive> create table emp(     > id int,     > name string,     > likes array<string>,     > address map<string,string>     > )     > partitioned by (gender string)     > row format delimited     > fields terminated by ','     > collection items terminated by '-'     > map keys terminated by ':';

创建时分区字段指定类型不赋值partitioned by(col_name col_type)

添加数据时按分区字段赋值，并不是真实字段值，可以随意取名partition (gender=’man’)

加载数据

Load data :转移数据到目标位置

Load data local:拷贝数据到目标文件夹（一般指定为目录而不是具体文件）

这样操作的原因是因为hive要求源文件和目标文件在同一文件系统中

Overwrite关键字会删除之前的目标文件

load data local inpath './emp.txt' into table emp partition (gender='man' );

通过查询方式也可以向表中插入数据

Insert overwrite table emp partition(gender=’women’,age=’30’) selelct * from emp2;

 

多列分区

同时按多个字段分区时、插入数据时需要将列都写上，顺序没有关系

load data local inpath '/path' into table emp partition (gender='women',age=30);

增加分区

增加分区只能增加值，不能增加字段

增加分区时，如果是多列分区，必须要包含所有列

hive> alter table emp add partition(gender='woman'); hive> alter table emp add if not exists partition(gender='woman') location ‘/path’;

删除分区

hive> alter table emp drop partition(age=30);

查看分区

hive> show partitions emp; hive> select * from emp where gender='women';

修改分区路径

Alter table emp partition(gender=’women’) set location ’ path’

 

修复分区

当hdfs中存在数据，并且有分区目录时，但是元数据中没有，这时使用修复分区命令

创建外部表、修复分区

切记：location位置到表名位置、分区列名得加对

hive> create external table emp     > (     > id int,     > name string,     > likes array<string>,     > address map<string,string>     > )     > partitioned by (age int)     > row format delimited     > fields terminated by ','     > collection items terminated by '-'     > map keys terminated by ':' > location '/test/emp '; hive> msck repair table emp;

执行钩子

当表的存储文件在hive之外被修改了，就会触发钩子的执行，并不是自动触发，需要以下语句

hive> alter table emp touch partition(gender='women',age=30);

 

动态分区

动态分区和静态分区创建方式一样

不同的是可能需要修改一下变量值、数据插入时不用添加分区列的值

默认是严格模式，这样设计有助于因设计错误而导致大量的分区

hive> create table emp2 like emp; hive> set hive.exec.dynamic.partition.mode=nostrict; hive> insert into table emp2 partition(gender,age) select * from emp; --注意：当使用动态分区的时候需要注意效率，每次转换的时候都需要执行mapreduce，一般转换的时候是获取某一个批次的数据

分桶

将表中记录按某个字段hash值分散到多个文件中、这些小文件称为桶,物理上就是将表数据拆分成桶的个数的小文件存储，数据按分桶字段的hash值分散存储到各个小文件

创建分桶表

hive> create table emp3(     > id int,     > name string,     > likes array<string>,     > address map<string,string>     > )     > partitioned by (gender string, age int)     > clustered by (id) into 3 buckets     > row format delimited     > fields terminated by ','     > collection items terminated by '-'     > map keys terminated by ':';

插入数据

和动态分区一致

hive> insert overwrite table emp3 partition(gender,age) select * from emp;

查询数据

hive> select * from emp3 tablesample(bucket 1 out of 3 on id);

参数说明：bucket x out of y on col

X表示从将会选择的桶的个数，y表示将会被散列的桶的个数，ID表示表字段，注意这里的x必须小于等于y

X<=y

Hql查询

Select from

最普通的查询，但是注意别名的利用

查询集合内容、结构体内容

array[index],map[“key”],struct.    Struct可以结合正则使用`struct.*`

hive> select name,likes[0],address["beijing"] from emp;

函数使用

HQL查询函数有很多、不再赘述，只列举一些常见函数操作

数学函数round、floor、ceil、rand、exp

聚合函数count、sum、avg、min、max

可以设置set hive.map.aggr=true来提高聚合性能，但是会消耗更多内存

Explode

行转列：将一列数据的多个值转换为一列数据

hive> select explode(likes) from emp;

 

Limit

用于限制返回数据条数

hive> select * from emp limit 3;

Case when then

Case…when…then…end语句和if else类似，用于处理单个列的查询结果

hive> select id ,name ,case     > when id <=3 then 'small'     > when id >3 and id<=6 then 'middle'     > when id >6 then 'big' end     > from emp;

避免MapReduce

Hive语句大多数情况下回触发一个MapReduce任务

Set hive.exec.mode.local.auto=true;

Where子句

Where 子句中不能只用列别名

Join have

浮点数问题

规避精度不准确问题

hive> select fnum from emp where fnum > cast (0.3 as float);

like和rlike

like语法和普通SQL一样

rlike为hive扩展，可以通过正则表达式来匹配指定条件

hive> select * from emp where name rlike '.*(manzi|man).*';

*表示重复

.表示任意字符

|表示或

Groupby having

Join优化

1. 相对于3个或者或者更多的表进行连接时，尽量使用一个相同的连接键，这样只会产生一个MapReducejob，否则会从左至右依次创建一个MapReduceJob然后向后继续关联join
2. 如果需要join关联表，将大表放在后面
3. Hive不支持in函数
4. 避免使用笛卡尔积join
5. Where子句中条件不要包含两个表的条件，避免笛卡尔积计算
6. Map join可以将小表的数据缓存在内存中，提高关联效率

select /*+ MAPJOIN(time_dim) */ count(1) from store_sales join time_dim on (ss_sold_time_sk = t_time_sk)

Order by

同其他SQL方言一样意义，全局排序—耗时，全局数据通过一个reduce排序

hive> select * from emp order by id;

 

sort by

局部排序，针对每一个reduce任务的数据进行内部排序

hive> select * from emp sort by id;

Distribute by

控制map端的输出在reduce中是怎么划分的，默认按map的key取值做hash运算，然后放到不同的reduce中处理，一般情况下和sortby结合使用

Cluster by等于distribute by + sort by

Union all

可以将两个或者多个表合并、每一个union子查询都必须具有相同的列、而且字段类型要一致

视图

视图允许保存一个查询并向对代表一样对这个查询进行操作，这是一个逻辑结构，并不存储数据。也就是说，目前的hive版本暂时不支持物化视图

从逻辑上讲可以理解为，先执行了视图的查询语句，然后对这个视图进行余下的查询

hive> create view emp_sa as select name,salary from emp;

 

使用视图可以降低查询复杂度，视图常常封装一些复杂的SQL，外部SQL只对查询视图进行查询

使用视图可以限制部分敏感数据的输出结果，也就是只查看到部分列或者where子句过滤

 

视图可以进行嵌套

视图不支持load数据的操作

视图只是保存了一份元数据、没有物理存储

索引

模式设计

按天划分表

实际上就是一种模式、通常会在表名上加一个时间戳，形成时间拉链表，这种每天一张表的设计在数据库领域中是反模式的一种方式，但是因为实际情况下数据增长很快，所以依旧被广泛使用。在hive中，分区表就解决了这个问题。

Alter table tablename add partitio(day=20190608) Alter table tablename add partitio(day=20190609)

唯一键和标准化

关系型数据库通常使用唯一键、索引、标准化来存储数据集、因为这部分数据是可以被存储到内存的。然而hive没有主键或者序列秘钥生成的自增键的概念。如果可以的话、应该避免对非标准化的数据进行join连接操作、复杂的数据模型(array\map\struct)可以实现一对多

非标准化的主要原因是最小化磁盘寻道、比如那些需要外键关系的情况

非标准化数据允许被扫描或写入到大的、连续的磁盘存储数据，从而优化磁盘驱动器的IO性能

非标准化的数据可能导致数据重复、而且有可能导致数据不一致的风险

同一份数据多种处理

以下的语句只会扫描一次history表

From history Insert overwrite into emp selelct * where action=’1’ Insert overwrite into emp2 selelct * where action=’2’

压缩使用

几乎在所有情况下，压缩都可以讲数据的存储量变小，这样可以通过降低IO来提高查询效率、hive可以无缝使用很多压缩类型。

但是压缩和解压缩都会消耗CPU资源、MapReduce任务往往是IO密集型的，因此CPU开销通常不是问题；

但是对于CPU密级型的，例如机器学习算法，通常就不适合使用压缩了

 

调优

使用explain

可以帮助我们理解hive是如何将查询转化为MapReduce任务的

首先，会打印抽象语法树、表明hive是如何将查询解析成token(符号)和literal(字面值)的，是第一步将查询转化到最终结果的一部分

 

使用explain extended

将打印更多信息

Join优化

分清楚表的大小，大表放在join的右边

表足够小的话，可以使用mapjoin，将小表加载到内存

本地模式

当hive能通过本地模式也就是单机能处理任务时，可以使用本地模式，在小的数据集情况下，执行时间可以明显缩短

Set hive.exec.model.local.auto=true;----对于所有用户可以直接配置到hive-site.xml

并行执行

Set hive.exec.parallel=true;可以开启并发执行、注意：在共享集群中，如果job中并行执行的阶段增多、name集群的利用率就会提高

严格模式

Set hive.mapred.mode=strict可以禁止三种类型的查询

1. 对于分区表，除非使用where语句中含有分区字段过滤条件来限制数据范围、否则不给执行，也就是说禁止扫描全量数据
2. 对于order by子句，要求必须结合limit同时使用。避免所有数据到同一个reduce处理
3. 限制笛卡尔积查询，使用join..on..

调整mapper、reduce个数

Hive是通过数据量来计算reduce数量的、默认情况下一个reduce处理1G

Jvm重用

Hadoop默认配置都是派生jvm实例来执行MapReduce任务的，jvm启动过程中开销很大，尤其是同一个job中tasks数量太大的情况

Set mapred.job.reuse.jvm.num.tasks=10

可以保证jvm实例在同一个job中重新使用n次，功能的唯一缺点就是jvm重用会一直占用task插槽，以便进行重用，直到任务完成后才开始释放，比如某个job不平衡，某几个map reduce任务耗时比其他task时间多得多的话，那么保留的插槽会一直空闲，无法被其他job使用，只有等整个job结束才会释放

动态分区调整

限制动态分区最大个数

开发

Hive不可能满足所有用户需求、用户可能需要开发自定义函数UDF、序列化反序列化器等功能

public class DateDimensionUDF extends UDF {     private IDimensionConverter converter = new DimensionConverterImpl();     /**      * 根据给定的日期（格式为:yyyy-MM-dd）至返回id      *      * @param day      * @return      */     public IntWritable evaluate(Text day) {         DateDimension dimension = DateDimension.buildDate(TimeUtil.parseString2Long(day.toString()), DateEnum.DAY);         try {             int id = this.converter.getDimensionIdByValue(dimension);             return new IntWritable(id);         } catch (IOException e) {             throw new RuntimeException("获取id异常");         }     } }

Hive权限管理

授权模式默认是不开启的

Set hive.security.authorrization.anabled=true开启

用户、组、角色

普通用户没有在default数据库下建表的权限，hive的用户名就是系统的用户名

 

授权

Set system:user.name;--查看当前用户

Grant create on database default to user manzi;

查看授权情况

hive> show grant user root on database default;

如果对于每一个用户进行授权，维护成本比较高，可采用对组授权或者结合使用

创建角色

Create role role_name;

授权角色到用户

Grant role role_name to user manzi;

给角色授权

Grant create on database default to role role_name;

Revoke可以取消权限

hive> revoke select on emp from user root;

Hive运行原理

1. 接收SQL
2. 词法分析\语法分析:使用antlr将SQL语句解析成抽象语法树
3. 语义分析：从metastore获取模式信息、验证SQL中表名字段名、数据类型、隐式转换以及用户自定义函数
4. 逻辑计划生成：生成逻辑计划—算子树
5. 逻辑计划优化：对算子树进行优化，剪枝(决策树进行简化)、分区剪枝、谓词下推等
6. 物理计划生成：将逻辑计划生成包含由MapReduce任务组成的DAG的物理计划
7. 物理计划执行：将DAG发送到Hadoop集群执行任务
8. 将结果返回

 

 

 

 

新版本支持将tez和spark作为执行引擎