一、hive基本原理和使用

一、概述

1、什么是hive

​ 由Facebook开源用于解决海量结构化日志的数据统计。是基于Hadoop的一个数据仓库工具，可以将结构化的数据文件映射为一张表，并提供类SQL查询功能。本质上其实就是将HQL/SQL转化为MapReduce或者spark任务执行，然后返回结果。有以下几个本质： 1）Hive处理的数据存储在HDFS。默认在 在/ user/hive/warehouse/<databaseName>.db/<tableName>/ 下创建对应文件 2）Hive分析数据底层的实现是MapReduce或者spark等分布式计算引擎 3）执行程序运行在Yarn上

所以由此可以得出：hive是使用SQL类似的语言接口而已，其他特征和mysql之类的数据库完全是两码事

2、hive优缺点

优点： 1）操作接口采用类SQL语法，提供快速开发的能力（简单、容易上手） 2）避免了去写MapReduce，减少开发人员的学习成本。 3）Hive的执行延迟比较高，因此Hive常用于数据分析，对实时性要求不高的场合； 4）Hive优势在于处理大数据，对于处理小数据没有优势，因为Hive的执行延迟比较高。 5）Hive支持用户自定义函数，用户可以根据自己的需求来实现自己的函数。

缺点： 1）Hive的HQL表达能力有限 （1）迭代式算法无法表达 （2）数据挖掘方面不擅长 2）Hive的效率比较低 （1）Hive自动生成的MapReduce作业，通常情况下不够智能化 （2）Hive调优比较困难，粒度较粗

3、hive基本架构

​ 图1.1 hive架构

​ 如图中所示，Hive通过给用户提供的一系列交互接口，接收到用户的指令(SQL)，使用自己的Driver，结合元数据(MetaStore)，将这些指令翻译成MapReduce（或者spark），提交到Hadoop中执行，最后，将执行返回的结果输出到用户交互接口。 1）用户接口：Client CLI（hive shell）、JDBC/ODBC(java访问hive)、WEBUI（浏览器访问hive）

2）元数据：Metastore 元数据包括：表名、表所属的数据库（默认是default）、表的拥有者、列/分区字段、表的类型（是否是外部表）、表的数据所在目录等； 默认存储在自带的derby数据库中，但是性能不好，推荐使用MySQL存储Metastore 表中的数据是存储在hdfs上的，只有表元数据才存储在mysql中

3）Hadoop 使用HDFS进行存储，使用MapReduce进行计算。

4）驱动器：Driver （1）解析器（SQL Parser）：将SQL字符串转换成抽象语法树AST，这一步一般都用第三方工具库完成，比如antlr；对AST进行语法分析，比如表是否存在、字段是否存在、SQL语义是否有误。 （2）编译器（Physical Plan）：将AST编译生成逻辑执行计划。 （3）优化器（Query Optimizer）：对逻辑执行计划进行优化。 （4）执行器（Execution）：把逻辑执行计划转换成可以运行的物理计划。对于Hive来说，就是MR/Spark。

二、部署hive

1、规划

项目	信息
主机	bigdata121(192.168.50.121)
hadoop	2.8.4（伪分布式搭建，一个datanode节点）
hive	1.2.1
mysql	5.7

mysql，hadoop的部署不重复说明，看之前的文章。

2、部署

（1）hive安装配置

解压程序到/opt/modules，并重命名

 tar -zxvf apache-hive-1.2.1-bin.tar.gz -C /opt/modules/
 mv /opt/modules/apache-hive-1.2.1-bin /opt/modules/hive-1.2.1-bin

修改环境配置文件hive-env.sh

cd /opt/modules/hive-1.2.1-bin/conf
mv hive-env.sh.template hive-env.sh

添加如下配置：
（a）配置HADOOP_HOME路径,hadoop家目录
export HADOOP_HOME=/opt/modules/hadoop-2.8.4
（b）配置HIVE_CONF_DIR路径。hive配置信息的目录
export HIVE_CONF_DIR=/opt/modules/hive-1.2.1-bin/conf

修改日志输出配置 hive-log4j.properties

cd /opt/modules/hive-1.2.1-bin/conf
mv hive-log4j.properties.template hive-log4j.properties

修改为 hive.log.dir=/opt/module/hive-1.2.1-bin/logs

配置hive环境变量：

vim /etc/profile.d/hive.sh
添加以下内容：
#!/bin/bash
export HIVE_HOME=/opt/modules/hive-1.2.1-bin
export PATH=$PATH:${HIVE_HOME}/bin

启用环境变量
source /etc/profile.d/hive.sh

这样配置好后，就可以使用 hive 命令启动hive了。默认元数据存储在自带的detry数据库中

（2）设置hive的元数据存储到 mysql中

修改配置文件 conf/hive-site.xml

<configuration>
        <property>
          <name>javax.jdo.option.ConnectionURL</name>
          <value>jdbc:mysql://bigdata121:3306/metastore?createDatabaseIfNotExist=true</value>
          <description>JDBC connect string for a JDBC metastore,jdbc连接字符串,并指定存储元数据的库名为 metastore,名字可以自定义</description>
        </property>

        <property>
          <name>javax.jdo.option.ConnectionDriverName</name>
          <value>com.mysql.jdbc.Driver</value>
          <description>Driver class name for a JDBC metastore,连接驱动类</description>
        </property>

        <property>
          <name>javax.jdo.option.ConnectionUserName</name>
          <value>root</value>
          <description>username to use against metastore database,mysql用户</description>
        </property>

        <property>
          <name>javax.jdo.option.ConnectionPassword</name>
          <value>wjt86912572</value>
          <description>password to use against metastore databasem,mysql密码</description>
        </property>

        <property>
                <name>hive.metastore.warehouse.dir</name>
                <value>/user/hive/warehouse</value>
                <description>指定default数据库的在hdfs中存储路径</description>
        </property>

        <property>
                <name>hive.cli.print.header</name>
                <value>true</value>
                <description>显示表头</description>
        </property>

        <property>
                <name>hive.cli.print.current.db</name>
                <value>true</value>
                <description>显示当前所在数据库的名字</description>
        </property>

</configuration>

接着自己下载 mysql-connector-java-5.1.27.jar（版本可自行选择），就是一个jdbc的jar包而已，然后放到 /opt/modules/hive-1.2.1-bin/lib 下。

接着，初始化元数据的数据库在mysql中的数据，并迁移detry的数据到mysql中

schematool -dbType mysql -initSchema

这个命令在 /opt/modules/hive-1.2.1-bin/bin  下

最后出现类似下面的字眼，表示初始化完成

schemeTool completed

如果出现下面的问题：

Metastore connection URL:        jdbc:mysql://bigdata121:3306/metastore?createDatabaseIfNotExist=true
Metastore Connection Driver :    com.mysql.jdbc.Driver
Metastore connection User:       root
Starting metastore schema initialization to 1.2.0
Initialization script hive-schema-1.2.0.mysql.sql
Error: Duplicate entry '1' for key 'PRIMARY' (state=23000,code=1062)
org.apache.hadoop.hive.metastore.HiveMetaException: Schema initialization FAILED! Metastore state would be inconsistent !!
*** schemaTool failed ***

解决方法：

到mysql手动删除刚才创建的数据库 drop database metastore，然后再执行一次上面的初始化命令即可

初始化完成后，就可以启动hive了。

（3）hdfs存储hive数据位置

默认是存储在 /user/hive/warehouse/下，每个库有自己独立的目录，以 databaseName.db 命名，库目录下则是 每个表，每个表以表名命名目录，目录下存储表数据。

（4）hive配置metaServer

​ 默认情况下，hive都是直接根据hive-site.xml中配置的元数据库的连接信息直接连接mysql的，而且同时只能一个hive客户端连接元数据库（怕有事务问题）。而metaserver是一个元数据库server，它直接连接mysql，其他hive客户端通过metaserver来间接连接mysql，读取元数据信息。此时允许多个hive客户端连接，且不需要指定连接MySQL的地址、用户名密码等信息。当然作为hive client，可以在配置文件中指定连接mysql的用户信息，但是一般需要创建多个mysql用户，用于给不同的hive client连接mysql，比较繁琐。一般用在公司内部 ​ 一般在生产环境中，都是在一台主机上配置了一个metaServer，然后在其他多台主机上配置hive client。而metaServer所在主机权限要求高，不允许其他人任意访问，而hive client的主机则是可以给低权限的人使用。一定程度上保护了元数据库mysql的隐秘性。

主机	功能
bigdata121(192.168.50.121)	hive metastore,mysql
bigdata122(192.168.50.122)	hive client

配置： 1>bigdata121:

vim conf/hive-site.xml
<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
<configuration>
        <property>
          <name>javax.jdo.option.ConnectionURL</name>
          <value>jdbc:mysql://bigdata121:3306/metastore?createDatabaseIfNotExist=true</value>
          <description>JDBC connect string for a JDBC metastore,jdbc连接字符串,并指定存储元数据的库名为 metastore,名字可以自定义</description>
        </property>

        <property>
          <name>javax.jdo.option.ConnectionDriverName</name>
          <value>com.mysql.jdbc.Driver</value>
          <description>Driver class name for a JDBC metastore,连接驱动类</description>
        </property>

        <property>
          <name>javax.jdo.option.ConnectionUserName</name>
          <value>root</value>
          <description>username to use against metastore database,mysql用户</description>
        </property>

        <property>
          <name>javax.jdo.option.ConnectionPassword</name>
          <value>wjt86912572</value>
          <description>password to use against metastore databasem,mysql密码</description>
        </property>

        <property>
                <name>hive.metastore.warehouse.dir</name>
                <value>/user/hive/warehouse</value>
                <description>指定default数据库的在hdfs中存储路径</description>
        </property>

        <property>
                <name>hive.cli.print.header</name>
                <value>true</value>
                <description>显示表头</description>
        </property>

        <property>
                <name>hive.cli.print.current.db</name>
                <value>true</value>
                <description>显示当前所在数据库的名字</description>
        </property>

        <!-- 指定zk集群的地址以及端口,用于访问hbase中的hmaster-->
        <property>
                <name>hive.zookeeper.quorum</name>
                <value>bigdata121,bigdata122,bigdata123</value>
                <description>The list of ZooKeeper servers to talk to. This is only needed for read/write locks.</description>
        </property>

        <property>
                <name>hive.zookeeper.client.port</name>
                <value>2181</value>
                <description>The port of ZooKeeper servers to talk to. This is only needed for read/write locks.</description>
        </property>

    <!--=========这里就是metastore server 的配置-->
        <!--指定metastoreserver的服务端地址-->
        <property>
                <name>hive.metastore.uris</name>
                <value>thrift://bigdata121:9083</value>
        </property>
</configuration>

配置完成后，启动metastore server:

hive --service metastore &
netstat -tnlp | grep 9083   看对应端口起来没

2> bigdata122：

vim conf/hive-site.xml
</configuration>
        <property>
                <name>hive.metastore.warehouse.dir</name>
                <value>/user/hive/warehouse</value>
                <description>指定default数据库的在hdfs中存储路径	</description>
        </property>

        <property>
                <name>hive.cli.print.header</name>
                <value>true</value>
                <description>显示表头</description>
        </property>

        <property>
                <name>hive.cli.print.current.db</name>
                <value>true</value>
                <description>显示当前所在数据库的名字</description>
        </property>

        <!-- 指定zk集群的地址以及端口,用于访问hbase中的hmaster-->
        <property>
                <name>hive.zookeeper.quorum</name>
                <value>bigdata121,bigdata122,bigdata123</value>
                <description>The list of ZooKeeper servers to talk to. This is only needed for read/write locks.</description>
        </property>

        <property>
                <name>hive.zookeeper.client.port</name>
                <value>2181</value>
                <description>The port of ZooKeeper servers to talk to. This is only needed for read/write locks.</description>
        </property>

        <!--指定metastoreserver的连接URL，用于hiveclient连接元数据库-->
        <property>
                <name>hive.metastore.uris</name>
                <value>thrift://bigdata121:9083</value>
        </property>
</configuration>

hive client的配置就比较简单了，不需要再配置mysql的连接信息了，直接配置hive在hdfs的数据目录，以及hive metastore server的连接地址就可以了

配置完成后，直接hive启动client就可以连接metastore，然后使用hive了。 不需要启动hiveserver，因为本身hive命令就是一个hiveserver

（5）hive配置hiveserver

​ 默认情况下，hive只允许hive client连接hive，因为这种情况hive不需要启动server服务，每个hive client本质上都是在自己本地启动了一个hive server，然后连接metastore server，读取hdfs的数据目录，这样就可以使用hive服务了。 ​ 如果需要通过jdbc这样的方式连接hive，那么就需要将hive以后台服务的形式启动了，需要提供对应的地址以及端口给jdbc连接。要注意，这个hiveserver和前面的metastore server没有啥关系，两个是独立的进程。而且hive server启动之后，内部也会有一个内置的metastore server，这个才是自己用的，外部无法使用。所以这两者是不同使用场景下的产物，注意区分。 在bigdata121(192.168.50.121) 上配置hive server

<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
<configuration>
        <property>
          <name>javax.jdo.option.ConnectionURL</name>
          <value>jdbc:mysql://bigdata121:3306/metastore?createDatabaseIfNotExist=true</value>
          <description>JDBC connect string for a JDBC metastore,jdbc连接字符串,并指定存储元数据的库名为 metastore,名字可以自定义</description>
        </property>

        <property>
          <name>javax.jdo.option.ConnectionDriverName</name>
          <value>com.mysql.jdbc.Driver</value>
          <description>Driver class name for a JDBC metastore,连接驱动类</description>
        </property>

        <property>
          <name>javax.jdo.option.ConnectionUserName</name>
          <value>root</value>
          <description>username to use against metastore database,mysql用户</description>
        </property>

        <property>
          <name>javax.jdo.option.ConnectionPassword</name>
          <value>wjt86912572</value>
          <description>password to use against metastore databasem,mysql密码</description>
        </property>

        <property>
                <name>hive.metastore.warehouse.dir</name>
                <value>/user/hive/warehouse</value>
                <description>指定default数据库的在hdfs中存储路径</description>
        </property>

        <property>
                <name>hive.cli.print.header</name>
                <value>true</value>
                <description>显示表头</description>
        </property>

        <property>
                <name>hive.cli.print.current.db</name>
                <value>true</value>
                <description>显示当前所在数据库的名字</description>
        </property>

        <!-- 指定zk集群的地址以及端口,用于访问hbase中的hmaster-->
        <property>
                <name>hive.zookeeper.quorum</name>
                <value>bigdata121,bigdata122,bigdata123</value>
                <description>The list of ZooKeeper servers to talk to. This is only needed for read/write locks.</description>
        </property>

        <property>
                <name>hive.zookeeper.client.port</name>
                <value>2181</value>
                <description>The port of ZooKeeper servers to talk to. This is only needed for read/write locks.</description>
        </property>

    	// metastore这里没有用， 
        <!--指定metastoreserver的服务端地址-->
        <property>
                <name>hive.metastore.uris</name>
                <value>thrift://bigdata121:9083</value>
        </property>

        <!--指定hiveserver的服务端地址,端口-->
        <property>
                <name>hive.server2.thrift.port</name>
                <value>10000</value>
        </property>

        <property>
                <name>hive.server2.thrift.bind.host</name>
                <value>bigdata121</value>
                <!--更改为自己的主机名字-->
        </property>
        <property>
                <name>hive.server2.long.polling.timeout</name>
                <value>5000</value>
        </property>

</configuration>

启动hiveserver：

hive --service hiveserver2 &
n | grep 10000  查看端口起来没

接着再bigdata122上测试下jdbc，在hive的bin下有个命令：

bin/beeline

[root@bigdata122 hive-1.2.1-bin]# bin/beeline 
Beeline version 1.2.1 by Apache Hive

beeline> !connect jdbc:hive2://bigdata121:10000  这里就是测试jdbc连接hive server了

SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/opt/modules/hbase-1.3.1/lib/phoenix-4.14.2-HBase-1.3-client.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/opt/modules/hadoop-2.8.4/share/hadoop/common/lib/slf4j-log4j12-1.7.10.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
SLF4J: Actual binding is of type [org.slf4j.impl.Log4jLoggerFactory]
Connecting to jdbc:hive2://bigdata121:10000
Enter username for jdbc:hive2://bigdata121:10000: 
Enter password for jdbc:hive2://bigdata121:10000: 
Connected to: Apache Hive (version 1.2.1)
Driver: Hive JDBC (version 1.2.1)
Transaction isolation: TRANSACTION_REPEATABLE_READ
0: jdbc:hive2://bigdata121:10000>    这里显示已经成功连接
0: jdbc:hive2://bigdata121:10000> show databases;
+----------------+--+
| database_name  |
+----------------+--+
| default        |
| test           |
+----------------+--+

三、hive的基本使用

1、hive命令行工具基本使用

hive -options
直接输出 hive 命令即可进入hive操作命令行
常用options：
--database databaseName： 指定进入某个库
-e SQL：不进入hive，直接执行sql命令，结果在命令行返回
-f xxx.sql：执行文件中的sql，结果直接返回

进入hive之后常用的操作命令：

exit/quit：退出hive
dfs xxx ：在hive中对hdfs进行操作
！ shellCommand：在hive中直接执行shell命令
~/.hivehistory：这是记录hive历史命令的文件

2、hive参数配置的方式

（1）可以在 hive-site.xml 中配置参数
（2）在启动hive时通过 -hiveconf param=value，参数只对本次启动有效
（3）在hive中通过 set param=value;  配置，可以通过 set param查看参数配置的value

上述三种设定方式的优先级依次递增。即配置文件<命令行参数<参数声明。注意某些系统级的参数，例如log4j相关的设定，必须用前两种方式设定，因为那些参数的读取在会话建立以前已经完成了。

3、hive数据类型

（1）基本数据类型

hive数据类型	java数据类型	长度	例子
BOOLEAN	boolean	布尔类型，true或者false	TRUE FALSE
TINYINT	byte	1byte有符号整数	20
SMALINT	short	2byte有符号整数	20
INT	int	4byte有符号整数	20
BIGINT	long	8byte有符号整数	20
FLOAT	float	单精度浮点数	3.14159
DOUBLE	double	双精度浮点数	3.14159
STRING	string	字符系列。可以指定字符集。可以使用单引号或者双引号。	‘now is the time’ “for all good men”
TIMESTAMP		时间类型
BINARY		字节数组

（2）集合数据类型

数据类型	描述	语法实例
STRUCT	和c语言中的struct类似，都可以通过“点”符号访问元素内容。例如，如果某个列的数据类型是STRUCT{first STRING, last<br/>STRING},那么第1个元素可以通过字段.first来引用。	struct()
MAP	MAP是一组键-值对元组集合，使用数组表示法可以访问数据。例如，如果某个列的数据类型是MAP，其中键->值对是’first’->’John’和’last’->’Doe’，那么可以通过字段名[‘last’]获取最后一个元素	map()
ARRAY	数组是一组具有相同类型和名称的变量的集合。这些变量称为数组的元素，每个数组元素都有一个编号，编号从零开始。例如，数组值为[‘John’, ‘Doe’]，那么第2个元素可以通过数组名[1]进行引用。	Array()

其中map和struct有点相似，但其实struct中不存在key的概念，但是类似key，就相当于struct内部本身就定义了一个个的属性的名称，我们填充的只是这些已有属性对应的value而已。所以key对于struct来说是已存在的。而map就很好理解了，key和value都是任意的，都是，两个都是数据，填充数据时需要同时输入key和value。

使用例子： 假设某表有一行数据如下（转化为json格式显示）：

{
    "name": "songsong",
    "friends": ["bingbing" , "lili"] ,       //列表Array, 
    "children": {                      //键值Map,
        "xiao song": 18 ,
        "xiaoxiao song": 19
    }
    "address": {                      //结构Struct,
        "street": "hui long guan" ,
        "city": "beijing" 
    }
}

原始数据如下：

songsong,bingbing_lili,xiao song:18_xiaoxiao song:19,hui long guan_beijing
yangyang,caicai_susu,xiao yang:18_xiaoxiao yang:19,chao yang_beijing

不同字段的数据用“，”分隔，同一字段内的多个值用“_”分隔，KV之间用“:”分隔

创建测试表（后面会讲用法，先看看）：

create table test(
name string,
friends array<string>,
children map<string, int>,        map就是定义KV的类型即可
address struct<street:string, city:string>  结构体是在定义时就指定有哪些key，然后填充value
)
row format delimited fields terminated by ','  
collection items terminated by '_'             
map keys terminated by ':'                    
lines terminated by '\n';    

字段解释：
row format delimited fields terminated by ','  -- 列分隔符
collection items terminated by '_'  	--MAP STRUCT 和 ARRAY 的分隔符(数据分割符号)
map keys terminated by ':'				-- MAP中的key与value的分隔符
lines terminated by '\n';					-- 行分隔符

导入数据：

hive (default)> load data local inpath '/opt/module/datas/test.txt' into table test;

查询数据：

hive (default)> select friends[1],children['xiao song'],address.city from test where name="songsong";
OK
_c0     _c1     city
lili    18      beijing
Time taken: 0.076 seconds, Fetched: 1 row(s)

（3）类型转化

Hive的原子数据类型是可以进行隐式转换的，类似于Java的类型转换，例如某表达式使用INT类型，TINYINT会自动转换为INT类型，但是Hive不会进行反向转化，例如，某表达式使用TINYINT类型，INT不会自动转换为TINYINT类型，它会返回错误，除非使用CAST操作。 1）隐式类型转换规则如下。 （1）任何整数类型都可以隐式地转换为一个范围更广的类型，如TINYINT可以转换成INT，INT可以转换成BIGINT。 （2）所有整数类型、FLOAT和STRING类型都可以隐式地转换成DOUBLE。 （3）TINYINT、SMALLINT、INT都可以转换为FLOAT。 （4）BOOLEAN类型不可以转换为任何其它的类型。 2）可以使用CAST操作显示进行数据类型转换，例如CAST('1' AS INT)将把字符串'1' 转换成整数1；如果强制类型转换失败，如执行CAST('X' AS INT)，表达式返回空值 NULL。

下面开始讲HQL的使用

四、DDL数据定义

1、创建数据库

create database [if not exists] DBNAME [location PATH];

if not exists：库存在就不创建，不存在就创建
location PATH：指定库在hdfs中的存储路径

2、修改数据库

    用户可以使用ALTER DATABASE命令为某个数据库的DBPROPERTIES设置键-值对属性值，来描述这个数据库的属性信息。数据库的其他元数据信息都是不可更改的，包括数据库名和数据库所在的目录位置。\
    
alter database DBNAME set dbproperties('param'='value');
如：修改创建时间
alter database db_hive set dbproperties('createtime'='20180830');

3、查看数据库

查看数据库基本信息：show databases
查看详细信息：desc database DBNAME
查看数据库更详细的信息：desc database extended DBNAME

4、删除数据库

drop database [if exists] DBNAME [cascade]
if exists:最好用判断库是否存在，不然容易报错。
cascade：强制删除非空的库，默认只能删除空的库

5、创建表

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name 
[(col_name data_type [COMMENT col_comment], ...)] 
[COMMENT table_comment] 
[PARTITIONED BY (col_name data_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]
[LIKE DBNAME]

字段解释：
（1）EXTERNAL：创建一个外部表，默认创建的是内部表。后面有讲两者的区别
（2）COMMENT：为表和列添加注释，后面有说
（3）PARTITIONED BY创建分区表，后面有说
（4）CLUSTERED BY创建分桶表，后面有说
（5）SORTED BY不常用：mr内部排序，后面有说
（6）ROW FORMAT 
DELIMITED [FIELDS TERMINATED BY char] [COLLECTION ITEMS TERMINATED BY char] [MAP KEYS TERMINATED BY char] [LINES TERMINATE D BY char] 
   | SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]
这些定义的输出数据的格式，分别依次是 字段分隔符、值分隔符、KV分隔符、行分隔符
（7）STORED AS指定存储文件类型
大体上分为行存储和列式存储，后面有讲，默认是textfile，即行存储文本格式
（8）LOCATION ：指定表在HDFS上的存储位置。指定数据加载路径，将文件放到该路径下，无需load即可自动加载数据。如果是自己独立创建分区目录，则需要手动load
（9）LIKE：允许用户复制现有的表结构，但是不复制数据

例子：
create table if not exists student2(
id int, name string
)
row format delimited fields terminated by '\t'
stored as textfile
location '/user/hive/warehouse/student2';

6、查看表的信息

查看表基本信息：desc TABLENAME
查看详细信息：desc formatted  TABLENAME

7、创建外部表和内部表

内部表和外部表的区别：

外部表：在使用drop删除外部表时，只会删除表结构，不会删除数据
内部表：也称为管理表，在使用drop删除外部表时，表结构和数据都会删除

一般来说，外部表用来存储原始收集过来的数据，不允许轻易删除，所以使用外部表来保存很适合。后面基于外部表数据分析，需要存储一些中间结果表时，这时候用内部表很适合，可以随时删除表结构和数据。

查看表的类型

desc formatted TABLEName;
.....
Table Type:     EXTERNAL_TABLE(外部表)/ MANAGED_TABLE(内部表)
.....

8、分区表操作

分区表其实就将一个表的数据划分为几个分区，每个分区在表的存储目录下独立创建一个分区目录，目录命名为：“分区key=value”的形式，如：“month=201902”，目录下存储该文件的数据文件。并且查询时可通过where语句来查询指定分区的数据。一定程度上减少了一次性读取数据的压力。比如按天分区，每天的数据单独存储。也便于管理。 （1）创建分区表

create table dept_partition(
deptno int, dname string, loc string
)
partitioned by (month string)          
row format delimited fields terminated by '\t';

其中partitioned by (month string)   就是定义分区的字段名称以及类型。注意这个字段并不是数据本身的字段，而是人为定义的。而且使用select查看表数据时，会显示该字段。

（2）加载数据到指定分区

load data local inpath '/opt/module/datas/dept.txt' into table default.dept_partition partition(month='201809'); 
必须以month=value的形式指定,否则报错,会在表目录下创建目录名为 month=201809 ，然后将数据文件保存到该目录下

load data local inpath '/opt/module/datas/dept.txt' into table default.dept_partition partition(month='201807');
如果分区已存在，则会将新的数据文件放到同一分区目录下 

（3）查看分区表数据

select * from dept_partition where month='201809';
_u3.deptno      _u3.dname       _u3.loc _u3.month
10      ACCOUNTING      NEW YORK        201809

可以看到month是作为字段显示出来的

（4）增加分区

创建单个分区:
hive (default)> alter table dept_partition add partition(month='201806') ;

同时创建多个分区:
hive (default)>  alter table dept_partition add partition(month='201805') partition(month='201804');
多个分区之间用空格分隔开

（5）删除分区

删除单个分区：
hive (default)> alter table dept_partition drop partition (month='201804');

同时删除多个分区：
hive (default)> alter table dept_partition drop partition (month='201805'), partition (month='201806');
多个分区之间用逗号分隔开

（6）查看分区表有多少分区

hive>show partitions dept_partition;

（7）查看分区表结构

hive>desc formatted dept_partition;
     desc dept_partition 查看简单的信息
.......
# Partition Information          
# col_name              data_type               comment             
month                   string  
........

（8）二级分区表

创建二级分区表：
create table dept_partition2(
deptno int, dname string, loc string
)
partitioned by (month string, day string)
row format delimited fields terminated by '\t';
其实就是定义了两个分区字段

加载数据到二级分区：
hive (default)> load data local inpath '/opt/module/datas/dept.txt' into table default.dept_partition2 partition(month='201809', day='13');

查看数据：
hive (default)> select * from dept_partition2 where month='201809' and day='13';

（9）分区表的存储结构

会在表目录下，创建 month=xxx 作为名字的目录，二级分区类似

（10）手动创建分区目录并上传数据的处理方式 手动创建分区目录并上传数据，是没办法直接通过select查看到数据的。处理方式如下

方式1：msck repair table tb_name 修复表
方式2：手动通过 alter table tb add parition(xxxx)添加分区
方式3：手动使用load加载数据到指定分区

9、修改表

修改表名：
ALTER TABLE table_name RENAME TO new_table_name

更新列：更新单个字段
ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name]
如： alter table dept_partition change column deptdesc desc int;

增加列：
alter table dept_partition add columns(deptdesc string);

替换列：注意这里是替换整个表的所有字段
alter table dept_partition replace columns(deptno string, dname string, loc string);

10、删除表

drop table TB_NAME

五、DML数据操作

1、数据导入

（1）load加载数据

hive>load data [local] inpath '/opt/module/datas/student.txt' [overwrite] into table student [partition (partcol1=val1,…)];

local:表示从本地加载数据到hive表；否则从HDFS加载数据到hive表。通过hdfs加载数据到表中时，会将文件剪切到表所在的路径下。无论是外部表还是内部表
overwrite:表示覆盖表中已有数据，否则表示追加,如果没有overwrite的话，同名的文件加载进去时候，会重命名为 tableName_copy_1
partition:表示上传到指定分区

（2）insert插入数据

创建一张分区表方便用实例讲解：
hive (default)> create table student2(id int, name string) partitioned by (month string) row format delimited fields terminated by '\t';

基本插入： insert into table  student partition(month='201809') values(1,'wangwu');

单表查询插入：
insert overwrite table student2 partition(month='201808')
select id, name from student where month='201809';

多表查询插入：
from student
insert overwrite table student2 partition(month='201807')
select id, name where month='201809'
insert overwrite table student2 partition(month='201806')
select id, name where month='201809';

（3）as select 查询数据创建新表并导入数据

create table if not exists student3
as select id, name from student;

一般用在结果表中，查询完毕后保存结果到另外一张表

（4）location创建表时指定数据位置

create table if not exists student5(
id int, name string
)
row format delimited fields terminated by '\t'
location '/user/hive/warehouse/student5';

接着直接将数据上传到上面指定的目录下，就可直接select查询到数据，无需再次手动load。如果没有指定location，则默认表目录为 /user/hive/warehouse/xxxx/下，且需要手动load数据

（5）import导入数据

这里只能导入使用export导出的数据，下面有讲export
import table student2 partition(month='201809') from '/user/hive/warehouse/export/student';

2、数据导出

（1）insert导出

insert overwrite [local] directory'/opt/module/datas/export/student1'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'             
select * from student;

local：表示导出到本地文件系统，否则导出到hdfs
导出时可以指定字段的分隔符、行分隔符等

（2）hadoop 命令直接导出

因为hive的数据文件就是文本文件，可以直接通过hdfs导出
hive (default)> dfs -get /user/hive/warehouse/student/month=201809/000000_0  /opt/module/datas/export/student3.txt;

（3）export导出

export table default.student to '/user/hive/warehouse/export/student';

3、清除数据truncate

truncate table TB_NAME

六、查询

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

下面实例中涉及到几个表：

员工表emp：
empno neame job mgr hiredate comm deptno
员工号 员工名 职位 领导 雇佣日期  薪资   部门号

部门表dept：
deptno dname loc
部门号  部门名 位置

位置信息表location：
loc       loc_nmae
位置标号    位置名

1、select...from

select 字段名 from table_name
字段名可以使用：字段名 as 别名  的方式来给字段定义别名

（1）对字段进行算术操作
例子：select sal +1 from emp;

（2）常用函数
1）求总行数（count）
	hive (default)> select count(*) cnt from emp;
这里可以写成count(1)，比count(*)效率高
2）求工资的最大值（max）
	hive (default)> select max(sal) max_sal from emp;
3）求工资的最小值（min）
	hive (default)> select min(sal) min_sal from emp;
4）求工资的总和（sum）
	hive (default)> select sum(sal) sum_sal from emp; 
5）求工资的平均值（avg）
	hive (default)> select avg(sal) avg_sal from emp;

（3）limit N
限制返回前N行

2、where

where 判断条件
判断条件:
（1）比较运算符：常用的大于小于等，between and、in、is null用法和mysql类似
（2）like B：B是简单的通配符字符串，
                % 代表零个或多个字符(任意个字符)。
                _ 代表一个字符
     RLIKE B：这是使用标准的正则表达式
（3）逻辑运算符：AND OR NOT

3、分组

（1）group by

GROUP BY语句通常会和聚合函数一起使用，按照一个或者多个列队结果进行分组，然后对每个组执行聚合操作。
案例实操：
	（1）计算emp表每个部门的平均工资.以部门号分组
hive (default)> select t.deptno, avg(t.sal) avg_sal from emp t group by t.deptno;
	（2）计算emp每个部门中每个岗位的最高薪水。以部门号分组
hive (default)> select t.deptno, t.job, max(t.sal) max_sal from emp t group by t.deptno, t.job;    

注意：group by时要注意，前面select的字段，都要出现在group by里，例如
select id,name from a group id,name;   不可以写成 select id,name from a group id;

（2）having

having和where用法一样，区别如下：
（1）where针对表中的列发挥作用，查询数据；having针对查询结果中的列发挥作用，筛选数据。
（2）where后面不能写分组函数，而having后面可以使用分组函数。
（3）having只用于group by分组统计语句。

例子：
求每个部门的平均薪水大于2000的部门
hive (default)> select deptno, avg(sal) avg_sal from emp group by deptno having avg_sal > 2000;

4、join

hive仅仅支持等值连接，不支持非等值连接。注意join的on语句中，不支持使用 or 进行多条件联合

（1）内连接

select xx from A 别名1 join B 别名2 on 等值判断
例子：
根据员工表和部门表的部门编号进行连接
select e.empno, e.ename, d.deptno from emp e join dept d on e.deptno = d.deptno;

（2）左外连接：

JOIN操作符左边表中符合WHERE子句的所有记录将会被返回. 以左表为准，右表中的行没有匹配到左表的，就直接去掉，匹配到的就显示，如果右表有多行匹配左表的同一行，那么该行就会显示多次。如果左表的行在右表中没有匹配，右表的数据就会显示为null

select xx from A 别名1 left join B 别名2 on 等值判断
例子：
select e.empno, e.ename, d.deptno from emp e left join dept d on e.deptno = d.deptno;

（3）右外连接

和左连接，只不过将后面的表作为左表了。还是以左表为准进行连接

select xx from A 别名1 right join B 别名2 on 等值判断
例子：
select e.empno, e.ename, d.deptno from emp e right join dept d on e.deptno = d.deptno;

（4）满外连接

显示两表的所有记录，不匹配的就对应显示为null

select xx from A 别名1 full join B 别名2 on 等值判断
例子：
select e.empno, e.ename, d.deptno from emp e full join dept d on e.deptno = d.deptno;

（5）多表连接

连接 n个表，至少需要n-1个连接条件。例如：连接三个表，至少需要两个连接条件。

例子：
SELECT e.ename, d.deptno, l. loc_name
FROM   emp e 
JOIN   dept d
ON     d.deptno = e.deptno 
JOIN   location l
ON     d.loc = l.loc;

大多数情况下，Hive会对每对JOIN连接对象启动一个MapReduce任务。本例中会首先启动一个MapReduce job对表e和表d进行连接操作，然后会再启动一个MapReduce job将第一个MapReduce job的输出和表l;进行连接操作。
注意：为什么不是表d和表l先进行连接操作呢？这是因为Hive总是按照从左到右的顺序执行的。

5、排序

（1）全局排序order by

Order By：全局排序，本质就是单独启动一个MapReduce用于全局排序
1）使用 ORDER BY 子句排序
ASC（ascend）: 升序（默认）
DESC（descend）: 降序
2）ORDER BY 子句在SELECT语句的结尾。
3）案例实操
	（1）查询员工信息按工资升序排列
hive (default)> select * from emp order by sal;
	（2）查询员工信息按工资降序排列
hive (default)> select * from emp order by sal desc;

（2）按照别名排序

按照员工薪水的2倍排序
select ename, sal*2 twosal from emp order by twosal;

（3）多个列排序

按照部门和工资升序排序
hive (default)> select ename, deptno, sal from emp order by deptno, sal ;

（4）每个MapReduce内部进行局部排序sort by

这个语句是用在MapReduce中每个分区的区内排序。最终每个reduce独自输出自己的排序结果，不会进行所有reduce的再一次排序
1）设置reduce个数
	hive (default)> set mapreduce.job.reduces=3;
2）查看设置reduce个数
	hive (default)> set mapreduce.job.reduces;
3）根据部门编号降序查看员工信息
	hive (default)> select * from emp sort by empno desc;

（5）分区字段Distribute By

Distribute By：类似MR中partition，进行分区，即指定分区的字段是哪个，这里的分区和上面的partition的分区不是一个意思，要注意，这里指的是程序中的分区，上面指的是存储的分区。结合sort by使用。
注意：Hive要求DISTRIBUTE BY语句要写在SORT BY语句之前。
对于distribute by进行测试，一定要分配多reduce进行处理，否则无法看到distribute by的效果。

（1）先按照部门编号分区，再按照员工编号降序排序。
hive (default)> set mapreduce.job.reduces=3;
	hive (default)> insert overwrite local directory '/opt/module/datas/distribute-result' select * from emp distribute by deptno sort by empno desc;
这里写到文件中，因为分区了，所以会产生多个文件（每个reduce一个文件），这样便于查看分区的结果

（6）cluster by分区排序

当distribute by和sorts by字段相同时，可以使用cluster by方式。
cluster by除了具有distribute by的功能外还兼具sort by的功能。但是排序只能是倒序排序，不能指定排序规则为ASC或者DESC。即实现指定字段分区，并对该字段排序
1）以下两种写法等价
hive (default)> select * from emp cluster by deptno;
hive (default)> select * from emp distribute by deptno sort by deptno;
注意：按照部门编号分区，不一定就是固定死的数值，可以是20号和30号部门分到一个分区里面去。

6、分桶

​ 分区针对的是数据的存储路径；分桶针对的是数据文件。分桶之后，会将原先的数据文件直接分隔成多个桶文件，每个桶一个文件。分区只是将数据文件本身放到不同的分区目录而已，不会对数据文件进行分割。 ​ 分区提供一个隔离数据和优化查询的便利方式。不过，并非所有的数据集都可形成合理的分区，特别是之前所提到过的要确定合适的划分大小这个疑虑。而分桶其实就是实现将一个大的数据文件根据分区字段自动分成几个分区，这就是分桶的作用

（1）准备数据

create table stu_buck(id int, name string)
clustered by(id) 
into 4 buckets
row format delimited fields terminated by '\t';

字段解释：
clustered by(id) ：分桶的字段名
into 4 buckets：分桶个数

原始数据为：student.txt
1001    ss1
1002    ss2
1003    ss3
1004    ss4
1005    ss5
1006    ss6
1007    ss7
1008    ss8
1009    ss9
1010    ss10
1011    ss11
1012    ss12
1013    ss13
1014    ss14
1015    ss15
1016    ss16

使用分桶需要打开相应参数设置：
hive (default)> set hive.enforce.bucketing=true;
hive (default)> set mapreduce.job.reduces=-1;

接着另外创建一张同样的表（没有分桶），并导入数据：
create table stu2(id int, name string) row format delimited fields terminated by '\t';
load data local inpath '/opt/module/datas/student.txt' into table stu2;

接着通过子查询的方式导入数据到分桶表（分桶表只能通过这种方式导入数据）：
insert overwrite table stu_buck
select id, name from stu2;

查看分桶数据：
hive (default)> select * from stu_buck;
OK
stu_buck.id     stu_buck.name
1016    ss16
1012    ss12
1008    ss8
1004    ss4
1009    ss9
1005    ss5
1001    ss1
1013    ss13
1010    ss10
1002    ss2
1006    ss6
1014    ss14
1003    ss3
1011    ss11
1007    ss7
1015    ss15
可以看到一般情况下，应该是按id有序显示的，但是这里明显不是，这就是分桶的影响了

查看分桶表的信息：
desc formatted stu_buck;

（2）分桶抽样查询

对于非常大的数据集，有时用户需要使用的是一个具有代表性的查询结果而不是全部结果。Hive可以通过对表进行抽样来满足这个需求。

查询表stu_buck中的数据。
hive (default)> select * from stu_buck tablesample(bucket 1 out of 4 on id);

注：tablesample是抽样语句，语法：TABLESAMPLE(BUCKET x OUT OF y) 。
y必须是table总bucket数的倍数或者因子。hive根据y的大小，决定抽样的比例。例如，table总共分了4份，当y=2时，抽取(4/2=)2个bucket的数据，当y=8时，抽取(4/8=)1/2个bucket的数据。
（个人理解）y表示的是抽样的间隔的bucket的个数，比如2，表示中间空一个bucket，然后再抽。如果y大于bucket的个数，那么就会在第一个开始抽样的bucket中继续进行剩下的间隔性抽样。如bucket数为4，y=12，那么最后肯定只有第一个开始抽的bucket会被抽样，在这个bucket中会抽取 4/12=1/3 个数据。 
x表示从哪个bucket开始抽取。例如，table总bucket数为4，tablesample(bucket 4 out of 4)，表示总共抽取（4/4=）1个bucket的数据，抽取第4个bucket的数据。

注意：x的值必须小于等于y的值，否则
FAILED: SemanticException [Error 10061]: Numerator should not be bigger than denominator in sample clause for table stu_buck

七、函数

1、自带函数

1）查看系统自带的函数
hive> show functions;

2）显示自带的函数的用法
hive> desc function upper;

3）详细显示自带的函数的用法
hive> desc function extended upper;

2、自定义函数UDF

如果自带的函数满足不了需求，可以自定义函数，称为UDF--user defined function

根据用户自定义函数类别分为以下三种：
	（1）UDF（User-Defined-Function）
		一进一出
	（2）UDAF（User-Defined Aggregation Function）
		聚集函数，多进一出
		类似于：count/max/min
	（3）UDTF（User-Defined Table-Generating Functions）
		一进多出
		如lateral view explore()

自定义函数的步骤：

（1）继承org.apache.hadoop.hive.ql.UDF
	（2）需要实现evaluate函数；evaluate函数支持重载；
	（3）在hive的命令行窗口创建函数
		a）添加jar
			add jar linux_jar_path
		b）创建function，
			create [temporary] function [dbname.]function_name AS 
			class_name;
			后面就可以使用 function_name 来调用函数
	（4）在hive的命令行窗口删除函数
		Drop [temporary] function [if exists] [dbname.]function_name;

八、压缩和存储

当hive底层使用MapReduce时，使用压缩功能需要hadoop本身开启相应的配置，以及相应的压缩格式的jar包依赖。关于这内容不重复说，看之前的“hadoop-压缩”中的配置。下面讲的是hive中的配置。

1、hive压缩配置

（1）map输出开启压缩

开启map输出阶段压缩可以减少job中map和Reduce task间数据传输量。具体配置如下:
1）开启hive中间传输数据压缩功能
	hive (default)>set hive.exec.compress.intermediate=true;
2）开启mapreduce中map输出压缩功能
	hive (default)>set mapreduce.map.output.compress=true;
3）设置mapreduce中map输出数据的压缩方式,这里使用snappy
	hive (default)>set mapreduce.map.output.compress.codec= org.apache.hadoop.io.compress.SnappyCodec;
4）执行查询语句
	hive (default)> select count(ename) name from emp;

（2）reduce输出开启压缩

当Hive将输出写入到表中时，输出内容同样可以进行压缩。属性hive.exec.compress.output控制着这个功能。用户可能需要保持默认设置文件中的默认值false，这样默认的输出就是非压缩的纯文本文件了。用户可以通过在查询语句或执行脚本中设置这个值为true，来开启输出结果压缩功能。

1）开启hive最终输出数据压缩功能
	hive (default)>set hive.exec.compress.output=true;
2）开启mapreduce最终输出数据压缩
	hive (default)>set mapreduce.output.fileoutputformat.compress=true;
3）设置mapreduce最终数据输出压缩方式
	hive (default)> set mapreduce.output.fileoutputformat.compress.codec = org.apache.hadoop.io.compress.SnappyCodec;
4）设置mapreduce最终数据输出压缩为块压缩
	hive (default)> set mapreduce.output.fileoutputformat.compress.type=BLOCK;
5）测试一下输出结果是否是压缩文件,如果开启压缩的话，输出的文件就是压缩格式的
	hive (default)> insert overwrite local directory '/opt/module/datas/distribute-result' select * from emp distribute by deptno sort by empno desc;

2、hive数据文件存储格式

（1）存储格式

hive主要分为行存储和列存储两大类，主要格式有：

行存储：TEXTFILE（文本格式） 、SEQUENCEFILE（二进制格式）
列存储：ORC（Optimized Row Columnar，默认开启压缩，且采用zlib压缩）、PARQUET，这两种格式的存储原理可自行学习，这里不详细说明

行存储的特点： 查询满足条件的一整行数据的时候，列存储则需要去每个聚集的字段找到对应的每个列的值，行存储只需要找到其中一个值，其余的值都在相邻地方，所以此时行存储查询的速度更快。一行的数据都是连续存储的。
列存储的特点： 因为每个字段的数据聚集存储，在查询只需要少数几个字段的时候，能大大减少读取的数据量；每个字段的数据类型一定是相同的，列式存储可以针对性的设计更好的设计压缩算法。

​ 图8.1 hive之列和行存储

这是行和列存储的架构图，可以明显看到区别

（2）创建表指定存储格式

使用 stored as xxx 关键字指定存储格式
如：
create table log_text (
track_time string,
url string,
session_id string,
referer string,
ip string,
end_user_id string,
city_id string
)
row format delimited fields terminated by '\t'
stored as textfile ;      这里就指定为textfile格式，也是默认的存储格式

由于默认情况下，列存储都会采用压缩方式存储，而行存储则不会。所以一般占用空间排名为：
从小到大排序   ORC >  Parquet >  textFile

3、存储格式和压缩相结合

创建表指定存储格式以及压缩方式：

create table log_orc_snappy(
track_time string,
url string,
session_id string,
referer string,
ip string,
end_user_id string,
city_id string
)
row format delimited fields terminated by '\t'
stored as orc tblproperties ("orc.compress"="SNAPPY");

其中tblproperties是用来指定orc 的工作参数的，里面是有key=value的形式来指定参数。orc.compress 是指定orc使用的压缩格式，这里指定是用SNAPPY

九、hive在生产环境中的应用

​ hive在生产环境中一般用于存储结构化数据。比如一般情况下，会从生产数据库中（比如mysql）中定时读取数据写入到hive，然后其他程序再从hive中读取数据进行分析处理。所以hive是充当数据仓库的作用

9.1 mysql导入数据到hive时的bug

​ mysql和hive都支持int类型，但是两者的可解析范围是不同的，mysql中的int类型课表示的数字范围大于hive中的int，这就导致一个问题，那就是当mysql中的int的值大于这个界限时，就无法在hive中正常解析，通常也就会导入失败。 ​ 所以为了避免这种情况，建议导入hive中的表的所有字段都设置为string类型，这样可以避免这些问题。当需要将字段类型转为实际类型时，在使用时再进行转换即可 当然这点其实在spark sql中也适用，毕竟它们都并不完全兼容标准的sql