Day 06-hive介绍与使用

Hive

hive简介

1.1 数据仓库

数据仓库，英文名称为Data Warehouse，可简写为DW。是一个用于存储,分析,报告的数据系统.数据仓库的目的是构建面向分析的集成化数据环境,分析结果为企业提供决策支持.

通过上图我们可以发现,数据仓库的定位很明确,就是面向分析,支持分析的的系统,这种系统我们称为OLAP(on-Line Analytic Processing 联机分析处理系统).

1.2 数据库和数据仓库区别

数据库和数据仓库的区别实际就是OLTP和OLAP的区别

OLTP系统的典型应用就是RDBMS,也就是我们俗称的数据库，当然这里要特别强调此数据库表示的是关系型数据库，Nosql数据库并不在讨论范围内.

OLAP系统的典型应用就是DW,也就是我们俗称的数据仓库。

1. 数据仓库不是大型的数据库，虽然数据仓库存储数据规模大。
2. 数据仓库的出现，并不是要取代数据库。
3. 数据库一般存储业务数据，数据仓库存储的一般是历史数据。
4. 数据库是为捕获数据而设计，数据仓库是为分析数据而设计

1.3 什么是Hive

Apache Hive是一款建立在Hadoop之上的开源数据仓库系统，可以将存储在Hadoop文件中的结构化、半结构化数据文件映射为一张数据库表，基于表提供了一种类似SQL的查询模型，称为Hive查询语言(HQL)，用于访问和分析存储在Hadoop文件中的大型数据集。

Hive核心是将HQL转换为MapReduce程序，然后将程序提交到Hadoop群集执行。

Hive由Facebook实现并开源。

通过上图我们发现Hive其实就干了两件事,第一将数据映射成一张表,第二将SQL语句转换为MR程序.

为什么使用Hive

使用Hadoop MapReduce直接处理数据所面临的问题
	人员学习成本太高需要掌握java语言
     MapReduce实现复杂查询逻辑开发难度太大
使用Hive处理数据的好处
	操作接口采用类SQL语法，提供快速开发的能力（简单、容易上手)
	避免直接写MapReduce，减少开发人员的学习成本
	支持自定义函数，功能扩展很方便
	背靠Hadoop，擅长存储分析海量数据集

Hive和Hadoop的关系

从功能来说，数据仓库软件，至少需要具备两种能力︰存储数据的能力、分析数据的能力

Apache Hive作为一款大数据时代的数据仓库软件，当然也具备上述两种能力。

只不过Hive并不是自己实现了上述两种能力，而是借助Hadoop。

Hive利用HDFS存储数据，利用MapReduce查询分析数据。

Hive的最大的魅力在于用户专注于编写HQL,Hive帮您转换成为MapReduce程序完成对数据的分析。

1.3 Hive的架构

Metadata:元数据(Metadata)，又称中介数据、中继数据，为描述数据的数据( data about data )，主要是描述数据属性( property )的信息，用来支持如指示存储位置、历史数据、资源查找、文件记录等功能。

Hive Meatadata:Hive的元数据。包含用Hive创建的database、table、表的位置、类型、属性，字段顺序类型等元信息。元数据存储在关系型数据库中。如hive内置的Derby、或者第三方如MySQL等。

Metastore:元数据服务。Metastore服务的作用是管理metadata元数据，对外暴露服务地址，让各种客户端通过连接metastore服务，由metastore再去连接MySQL数据库来存取元数据。有了metastore服务，就可以有多个客户端同时连接，而且这些客户端不需要知道MySQL数据库的用户名和密码，只需要连接metastore 服务即可。某种程度上也保证了hive元数据的安全。

1.4 Hive与Mysql对比

Hive虽然具有RDBMS数据库的外表，包括数据模型、SQL语法都十分相似，但应用场景却完全不同。Hive只适合用来做海量数据的离线分析。Hive的定位是数据仓库，面向分析的OLAP系统。因此时刻告诉自己，Hive不是大型数据库，也不是要取代MySQL承担业务数据处理。

第二章 hive安装和部署

2.1 Hive地址

1．Hive官网地址 http://hive.apache.org/

2．文档查看地址 https://cwiki.apache.org/confluence/display/Hive/GettingStarted

3．下载地址 http://archive.apache.org/dist/hive/

4．github地址 https://github.com/apache/hive

2.2 前提准备

由于Apache Hive是一款基于Hladoop的数据仓库软件，通常部署运行在Linux系统之上。因此不管使用何种方式配置Hive Metastore，必须要先保证服务器的基础环境正常，Hadoop集群健康可用。

1.服务器的基础环境

集群时间同步、防火墙关闭、主机Host映射、免密登录、JDK安装

2.Hadoop集群健康可用

启动Hive之前必须先启动Hadoop集群。特别要注意，需等待HDFS安全模式关闭之后再启动运行ive。
Hive不是分布式安装运行的软件，其分布式的特性主要借由Hadoop完成。包括分布式存储、分布式计算。
配置Hive操作Hadoop权限
vi /opt/apps/hadoop-3.1.1/etc/hadoop/core-site.xml 
<property>
<name>dfs.permissions.enabled</name>
<value>false</value>
</property>

<property>
<name>hadoop.proxyuser.root.hosts</name>
<value>*</value>
</property>

<property>
<name>hadoop.proxyuser.root.groups</name>
<value>*</value>
</property>

3.MySQL数据库准备

因为我们使用远程连接模式,要把元数据存储到Mysql中.所以需要安装mysql并开启远程连接权限.
Mysql安装我们已经安装完毕,没有安装详情参照Linux02课件.
这里直接开启远程权限
1.登录  mysql -uroot  -proot
2.在mysql中设置允许简单密码
  set global validate_password_policy=0;
  set global validate_password_length=1;
3.开放远程连接权限
  grant all privileges on *.* to 'root'@'%' identified by 'root' with grant option;
4.刷新权限 
  flush privileges;

这样就开启成功,开启成功后可以在windows上用图形化工具连接一下查看是否成功
补充数据库修改密码命令 SET PASSWORD FOR 'root'@'linux01' = PASSWORD('root'); 

2.3 上传解压

将资料中的apache-hive-3.1.2.tar.gz上传到linux01的/opt/apps文件夹 可以使用rz命令或者xftp工具
上传后进入到apps文件夹 进行解压
tar -zxvf apache-hive-3.1.2.tar.gz
解压后名称比较长 我们简单修改一下
mv apache-hive-3.1.2 hive-3.1.2

如果hadoop版本比较高需要解决Hadoop和hive中guava版本差异(我们用的hadoop版本是3.1.1不需要修改)
在hive的lib文件夹下 guava的jar包版本是19.0  
而高版本的的hadoop的/opt/apps/hadoop-3.1.1/share/hadoop/common/lib
下的guava包是27.0 
可以删除hive下的guava 将hadoop的复制过来

2.4 配置

配置hive系统环境变量 让Hive命令可以在任意位置运行

vi /etc/profile  
export  JAVA_HOME=/opt/apps/jdk1.8.0_191
export  HADOOP_HOME=/opt/apps/hadoop-3.1.1
export  HIVE_HOME=/opt/apps/hive-3.1.2
export  PATH=$PATH:$JAVA_HOME/bin:$HADOOP_HOME/bin:$HADOOP_HOME/sbin:$HIVE_HOME/bin

Hive总共有三种配置方式:内嵌模式,本地模式,远程模式. 三种方式主要区别在于Metastore是否需要单独启动,及Metadata存储在哪种数据库中.我们主要使用的是远程模式,这也是企业中使用的模式.

2.4.1 内嵌模式(了解)

内嵌模式(Embedded Metastore )是metastore默认部署模式。

此种模式下，元数据存储在内置的Derby数据库，并且Derby数据库和metastore服务都嵌入在主HiveServer进程中，当启动HiveServer进程时，Derby和metastore都会启动。不需要额外起Metastore服务。

但是一次只能支持一个活动用户，适用于测试体验，不适用于生产环境。

具体配置(了解 不演示)

#修改Hive环境变量文件 添加Hadoop_HOME
cd /opt/apps/hive-3.1.2/conf
mv hive-env.sh.template hive-env.sh
vi hive-env.sh  
#在最后加上
export HADOOP_HOME=/opt/apps/hadoop-3.1.1
export HIVE_CONF_DIR=/opt/apps/hive-3.1.2/conf
export HIVE_AUX_JARS_PATH=/opt/apps/hive-3.1.2/lib
#初始化元数据
schematool -initSchema -dbType derby
#启动hive服务
hive

2.4.2 本地模式(了解)

本地模式(Local Metastore ) 下，Metastore服务与主HiveServer进程在同一进程中运行，但是存储元数据的数据库在单独的进程中运行，并且可以在单独的主机上。metastore服务将通过JDBC与metastore数据库进行通信。本地模式采用外部数据库来存储元数据，推荐使用MySQL。

hive根据hive.metastore.uris参数值来判断，如果为空，则为本地模式。缺点是︰每启动一次hive服务，都内置启动了一个metastore。

本地模式特征就是∶需要安装数据库MySQL来存储元数据，但是不需要配置启动Metastore服务。

注意:Hive3版本需要用户手动进行元数据初始化动作。

具体配置(了解 不演示)

#修改Hive环境变量文件 添加Hadoop_HOME
cd /opt/apps/hive-3.1.2/conf
mv hive-env.sh.template hive-env.sh
vi hive-env.sh  
export HADOOP_HOME=/opt/apps/hadoop-3.1.1
export HIVE_CONF_DIR=/opt/apps/hive-3.1.2/conf
export HIVE_AUX_JARS_PATH=/opt/apps/hive-3.1.2/lib

在/opt/apps/hive-3.1.2/lib下上传mysql驱动包

vi hive-site.xml
<configuration>
  <property>
  <name>javax.jdo.option.ConnectionURL</name>
  <value>jdbc:mysql://linux01:3306/hive?createDatabaseIfNotExist=true&amp;useSSL=false</value>
  </property>
  <property>
  <name>javax.jdo.option.ConnectionDriverName</name>
  <value>com.mysql.jdbc.Driver</value>
  </property>
  <!-- mysql的用户名和密码 -->
  <property>
  <name>javax.jdo.option.ConnectionUserName</name>
  <value>root</value>
  </property>
  <property>
  <name>javax.jdo.option.ConnectionPassword</name>
  <value>root</value>
  </property>

  <!--关闭元数据授权-->
  <property>
  <name>hive.metastore.event.db.notification.api.auth</name>
  <value>false</value>
  </property>
  <!--关闭元数据存储版本的验证-->
  <property>
  <name>hive.metastore.schema.verification</name>
  <value>false</value>
  </property>
</configuration>

开启hive操作hadoop权限
见2.2

初始化元数据
schematool -initSchema -dbType mysql -verbos

启动服务
hive

2.4.3 远程模式(重点)

   远程模式(Remote Metastore )下，Metastore服务在其自己的单独JVM上运行，而不在HiveServer的JVM中运行。如果其他进程希望与Metastore服务器通信，则可以使用thrift Network API进行通信。

  远程模式下，需要配置hive.metastore.uris 参数来指定metastore服务运行的机器ip和端口，并且需要单独手动启动metastore服务。元数据也采用外部数据库来存储元数据，推荐使用MySQL。

 在生产环境中，建议用远程模式来配置Hive Metastore。在这种情况下，其他依赖hive的软件都可以通过Metastore访问hive。由于还可以完全屏蔽数据库层，因此这也带来了更好的可管理性/安全性。

具体配置(重要)

1.修改Hive环境变量文件 添加Hadoop_HOME

#进入到hive下的conf文件夹
cd /opt/apps/hive-3.1.2/conf
#将hive环境变量文件模版 改为可用文件
mv  hive-env.sh.template  hive-env.sh
#在最后添加Hadoop_Home
export HADOOP_HOME=/opt/apps/hadoop-3.1.1
export HIVE_CONF_DIR=/opt/apps/hive-3.1.2/conf
export HIVE_AUX_JARS_PATH=/opt/apps/hive-3.1.2/lib

2.创建Hive的核心配置文件 进行配置

在hive下的conf文件夹下 直接vi一个hive-site.xml
vi  hive-site.xml
<configuration>
    <!-- 记录hive中的元数据信息  记录在mysql中 -->
    <property>
        <name>javax.jdo.option.ConnectionURL</name>
        <value>jdbc:mysql://linux01:3306/hive?createDatabaseIfNotExist=true&amp;useSSL=false</value>
    </property>
    <property>
        <name>javax.jdo.option.ConnectionDriverName</name>
        <value>com.mysql.jdbc.Driver</value>
    </property>
    <!-- mysql的用户名和密码 -->
    <property>
        <name>javax.jdo.option.ConnectionUserName</name>
        <value>root</value>
    </property>
    <property>
        <name>javax.jdo.option.ConnectionPassword</name>
        <value>root</value>
    </property>
    <!-- hive在HDFS上的工作目录 -->
    <property>
        <name>hive.metastore.warehouse.dir</name>
        <value>/user/hive/warehouse</value>
    </property>
    <property>
        <name>hive.exec.scratchdir</name>
        <value>/user/hive/tmp</value>
    </property>
    <property>
        <name>hive.querylog.location</name>
        <value>/user/hive/log</value>
    </property>
    <!-- 客户端远程连接的端口 -->
    <property>
        <name>hive.server2.thrift.port</name>
        <value>10000</value>
    </property>
    <property>
        <name>hive.server2.thrift.bind.host</name>
        <value>linux01</value>
    </property>
    <!-- hive服务的页面的端口 -->
    <property>
        <name>hive.server2.webui.port</name>
        <value>10002</value>
    </property>
    <property>
        <name>hive.server2.long.polling.timeout</name>
        <value>5000</value>
    </property>
    <property>
        <name>hive.server2.enable.doAs</name>
        <value>true</value>
    </property>
    <property>
        <name>datanucleus.autoCreateSchema</name>
        <value>false</value>
    </property>
    <property>
        <name>datanucleus.fixedDatastore</name>
        <value>true</value>
    </property>
    <property>
        <name>hive.execution.engine</name>
        <value>mr</value>
    </property>
    <!--关闭元数据授权-->
    <property>
        <name>hive.metastore.event.db.notification.api.auth</name>
        <value>false</value>
    </property>
    <!--关闭元数据存储版本的验证-->
    <property>
        <name>hive.metastore.schema.verification</name>
        <value>false</value>
    </property>
    <!-- 添加元数据服务配置 -->
    <property>
        <name>hive.metastore.uris</name>
        <value>thrift://linux01:9083</value>
    </property>
</configuration>

3.开启hive操作hadoop的权限

已经配置过  见2.2 前提准备

4.上传MySQL驱动包放入到Hive的lib文件夹下

Hive的lib包中默认没有mysql驱动的jar包.我们需要上传一个mysql的jdbc驱动jar包放入到hive的lib下.
cd /opt/apps/hive-3.1.2/lib
使用rz命令上传我们之前用过的jdbc的jar包即可 

2.5 hive初始化

将hive的原数据信息初始化到mysql中 用的是hive下bin中的schematool命令 我们配置了环境变量可以直接使用.
schematool -initSchema -dbType mysql

最后出现 completed初始化成功 
Initialization script completed
schemaTool completed

在mysql数据库中发现生成一个新的hive数据库 里面默认有74张表 , 说明hive初始化成功!

2.6 启动元数据服务

前台启动  
hive --service metastore
如果使用前台启动需要克隆一个session来访问 或者使用ctrl+z挂起到后台 因为元数据服务会一直在前台运行

直接后台启动
hive --service metastore &
这种启动方式 会在后台启动 可以正常访问 但是如果退出终端 就会退出
nohup hive --service metastore & 
这种方式 即使退出终端 服务也会继续运行 启动日志会在当前目录下的 nohup.out中
nohup hive --service metastore 1>/dev/null 2>1 &
这种方式将 启动日志 写入到黑洞 不显示任何内容

/dev/null ：代表空设备文件
>  ：代表重定向到哪里，例如：echo "123" > /home/123.txt
1  ：表示stdout标准输出，系统默认值是1，所以">/dev/null"等同于"1>/dev/null"
2  ：表示stderr标准错误输出
&  ：表示等同于的意思，2>&1，表示2的输出重定向等同于1


1>/dev/null：首先表示标准输出重定向到空设备文件，也就是不输出任何信息到终端，说白了就是不显示任何信息。
2>&1：接着,标准错误输出重定向标准输出，因为之前标准输出已经重定向到了空设备文件，所以标准错误输出也重定向到空设备文件。


检查启动额端口
netstat -nltp | grep 9083  端口存在说明元数据服务启动成功

2.7 HIVE的命令行客户端

Hive发展至今，总共历经了两代客户端工具。

第一代客户端( deprecated不推荐使用）︰$HIVE_HOME/bin/hive，是一个shellUtil。主要功能∶一是可用于以交互或批处理模式运行Hive查询;二是用于Hive相关服务的启动，比如metastore服务。

第二代客户端( recommended推荐使用）∶$HIVE_HOME/bin/beeline，是一个JDBC客户端，是官方强烈推荐使用的Hive命令行工具，和第一代客户端相比，性能加强安全性提高。

2.7.1 第一代客户端Hive

本地访问

如果是在linux01上使用
先启动元数据服务 注意如果已经启动不能再次启动 会报错
nohup hive --service metastore & 
启动后直接使用hive命令即可
进入后可以使用正常的SQL语句.
退出客户端的命令 
exit;
quit;

远程访问

我们是远程连接模式是可以使用其他计算机来进行访问的.
如果使用其他计算机访问 
首先这台计算机需要安装hive  上传解压
cd /opt/apps/hive-3.1.2/conf
配置hive-env.sh
export HADOOP_HOME=/opt/apps/hadoop-3.1.1
export HIVE_CONF_DIR=/opt/apps/hive-3.1.2/conf
export HIVE_AUX_JARS_PATH=/opt/apps/hive-3.1.2/lib

vi hive-site.xml
需要在hive-site.xml中配置 元数据服务的地址 其他什么都不需要配置了
 <configuration>
    <property>
        <name>hive.metastore.uris</name>
        <value>thrift://linux01:9083</value>
    </property>
    </configuration>
 就可以直接访问
 注意没有配置hive环境变量 需要到/opt/apps/hive-3.1.2/bin 下使用hive命令
 ./hive

2.7.2 第二代客户端Beeline

本地访问

先启动Metastore服务  如果已经启动可以忽视
nohup hive --service metastore & 
然后才启动hiveserver2服务 端口号10000 在hive-site.xml中进行的配置
nohup hive --service hiveserver2 &
启动后可以使用netstat -nltp | grep 10000 来进行验证 检查10000端口
查看WEBUI页面http://linux01:10002/

使用beeline命令 进入后输入相关连接的uri
!connect jdbc:hive2://linux01:10000
需要输入用户名 root 密码为空 不输入 直接回车

退出命令 !quit  或者直接ctrl+c

可以使用 beeline -u jdbc:hive2://linux01:10000 -n root

远程访问

使用其他计算机访问的话首先保证 linux01的Metastore和 hiveserver2都已经启动

上传 解压 不再多说
这里只需要配置 hive-env.sh
export HADOOP_HOME=/opt/apps/hadoop-3.1.1
export HIVE_CONF_DIR=/opt/apps/hive-3.1.2/conf
export HIVE_AUX_JARS_PATH=/opt/apps/hive-3.1.2/lib

由于不直接访问Metastore 所以不需要配置hive-site.xml 直接使用beeline正常访问即可

2.7.3 非交互式操作(了解)

不进入到hive中 直接执hql语句或者hql文件 ,执行完毕后返回linux命令行

使用 –e 参数来直接执行hql的语句

hive -e "show databases;"

使用 –f 参数通过指定文本文件来执行hql的语句

vi a.sql
show databases;
执行sql文件
hive -f a.sql
执行sql文件 将执行结果写入到另一个文件中
hive -f a.sql > b.sql

2.8 Hive简单体验

在Hive上可以直接写SQL语句,即使我们不会HQL也可以进行简单使用,比如建库,建表.

create database hive01;
use hive01;
create table `temperature`(
     `id`  int ,
     `city` varchar(255),
     `tempera` double,
     `date` varchar(255)
);

当我们这样创建一张表的时候,mysql会存储对应的元数据信息.

有了张表后我们尝试向里面插入数据

INSERT INTO temperature VALUES (1,'beijing',37,'2022-07-20');

通过执行的日志我们发现,这条语句会转换为MR程序运行,执行时间很长.如果使用mysql的话是毫秒级别就可以执行完,但是使用hive会发现要执行很长时间.(本地MR大概要几秒,如果提交到Yarn上要几十秒);

通过这些操作我们可以得出几个结论:

• Hive SQL语法和标准SQL很类似,使得学习成本降低。
• Hive底层是通过MapReduce执行的数据插入动作,所以速度慢。
• 如果大数据这么一条一条插入的话是非常不现实的，成本极高。
• Hive应该具有自己特有的数据插入表方式，结构化文件映射成为表。

{ 基本步骤 }

1. 在HDFS上准备一个结构化的文件
2. 根据文件中数据结构创建一张hive表
3. 导入数据 ***
4. 查询表中的数据

创建文件上传到hdfs

vi  temperature.txt;

1,beijing,37,2022-07-20
2,shanghai,40,2022-07-20
3,beijing,36,2022-07-21
4,shanghai,41,2022-07-21

hdfs dfs -put temperature.txt /

建表

create table `temperature2`(
     `id`  int ,
     `city` varchar(255),
     `tempera` double,
     `date` varchar(255)
) row format delimited fields terminated by ',';  -- 指定行的分隔方式 ,

导入数据

load data inpath '/temperature.txt' into table  temperature2 ;

• 查询每日最高温度

  select date,max(tempera) from temperature2 group by date;

  结果
  ±------------±------+
  | date | _c1 |
  ±------------±------+
  | 2022-07-20 | 40.0 |
  | 2022-07-21 | 41.0 |
  ±------------±------+

通过这次体验我们发现,Hive底层的确是通过MapReduce执行引擎来处理数据的执行完一个MapReduce程序需要的时间不短.如果是小数据集，使用hive进行分析将得不偿失，延迟很高.如果是大数据集，使用hive进行分析，底层MapReduce分布式计算,才能体现效果.

第三章 Hive可视化工具

Hive自带的客户端Hive CLI, Beeline CLI 虽然不需要安装,但是编写SQL环境比较恶劣,没有提示,没有高亮,还不能保存,这里提供两种可视化工具. DBeaver和idea.

3.1 DBeaver

DBeaver社区版,开源免费.给大家的资料中,直接解压就可以使用. 点击dbeaver.exe就可以运行.这个软件不只可以连接hive还可以连接mysql.

选择hive

填写要连接的主机和用户名 ,密码可以不填,之后点击编辑驱动.

将库中原有的驱动删除,将资料中的hive-jdbc-3.12的驱动添加进来.点击确定.

测试连接

3.2 使用idea连接Hive

idea我们已经不陌生了,创建一个项目即可.

第四章 HQL基础语法

Hive中的语句叫做HQL语句,是一种类似SQL的语句,基本上和SQL相同但是某些地方也是有很大的区别.

4.1 数据库操作

创建数据库

• 1.创建一个数据库,数据库在HDFS上的默认存储路径是/hive/warehouse/*.db。

  create database hive01;

• 避免要创建的数据库已经存在错误，增加if not exists判断。（标准写法）

  create database if not exists hive01;

• 创建数据库指定位置

  create database if not exists hive01 location ‘/hive01.db’;

修改数据库

用户可以使用ALTER DATABASE命令为某个数据库的DBPROPERTIES设置键-值对属性值，来描述这个数据库的属性信息。数据库的其他元数据信息都是不可更改的，包括数据库名和数据库所在的目录位置。

alter database hive01 set dbproperties('createtime'='20220727');

查看数据库

• 显示所有数据库

  show databases;

• 显示数据库使用like过滤

  show databases like ‘h*’;

• 查看数据库详情

  desc database hive01;
  describe database extended hive01 ; – 带属性

• 切换数据库

  use hive01;

删除数据库

• 最简写法

  drop database hive01;

• 如果删除的数据库不存在，最好使用if exists判断数据库是否存在。否则会报错：FAILED:SemanticException [Error 10072]: Database does not exist: db_hive

  drop database if exists hive01;

• 如果数据库不为空，使用cascade命令进行强制删除

  drop database if exists hive01 cascade;

4.2 Hive数据类型

Hive数据类型指的是表中列的字段类型;

整体分为两类︰原生数据类型( primitive data type)和复杂数据类型( complex data type ) 。

原生数据类型包括:数值类型、时间日期类型、字符串类型、杂项数据类型.

复杂数据类型包括:array数组、map映射、struct结构.

4.2.1 原生数据类型

Java数据类型 Hive数据类型 长度
byte TINYINT 8位有符号整型。取值范围：-128~127。
short SMALLINT 16位有符号整型。取值范围：-32768~32767。
int INT 32位有符号整型。取值范围：-2 31 ~2 31 -1。
long BIGINT 64位有符号整型。取值范围：-2 63 +1~2 63 -1。
boolean BOOLEAN 布尔类型，true或者false
float FLOAT 单精度浮点数
double DOUBLE 双精度浮点数
varchar(n) 变长字符类型，n为长度。取值范围：1~65535。
 char(n) 固定长度字符类型，n为长度。最大取值255
String string 字符串类型，目前长度限制为8MB。
DATE 日期类型，格式为 yyyy-mm-dd .
DATETIME 日期时间类型。 精确到毫秒
TIMESTAMP 时间戳

对于Hive的String类型相当于数据库的varchar类型，该类型是一个可变的字符串，不过它不能声明其中最多能存储多少个字符，理论上它可以存储2GB的字符数。

隐式类型转换

• 与标准SQL类似，HQL支持隐式和显式类型转换。
• 原生类型从窄类型到宽类型的转换称为隐式转换，反之，则不允许。
• 下表描述了类型之间允许的隐式转换∶

强制类型转换

使用CAST函数 cast(数据 as 新类型)

select cast( '100' as double); 会将100字符串转换为100整数值。
如果强制转换失败，例如select cast ('aa' as int );，该函数返回NULL。

4.2.2 复杂数据类型

类型 定义 演示
Array array array(1,2,3,4)
Map map<string, string> map(“k1”,“v1”,“k2”,“v2”)
Struct struct<x:int, y:int> named_struct(‘x’, 1, ‘y’,

Hive有三种复杂数据类型ARRAY、MAP 和 STRUCT。ARRAY和MAP与Java中的Array和Map类似，而STRUCT与C语言中的Struct类似，它封装了一个命名字段集合，复杂数据类型允许任意层次的嵌套。

4.3 数据库表基本操作

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]   row format delimited fields terminated by “分隔符”
[STORED AS file_format] 
[LOCATION hdfs_path]


[] 中括号的语法表示可选。
|  表示使用的时候，左右语法二选一。
建表语句中的语法顺序要和语法树中顺序保持一致。

字段简单说明
-  CREATE TABLE 创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；用户可以用 IF NOT EXISTS 选项 来忽略这个异常。
-  EXTERNAL  外部表
-  COMMENT： 为表和列添加注释。
-  PARTITIONED BY 创建分区表
-  CLUSTERED BY 创建分桶表
-  SORTED BY  排序不常用
- ROW FORMAT row_format 行分隔
- STORED AS 指定文件存储类型
- LOCATION 指定表在HDFS上的存储位置。
- LIKE 允许用户复制现有的表结构，但是不复制数据

4.3.1 根据数据创建表

案例1:简单用户信息

1,admin,123456,男,18
2,zhangsan,abc123,男,23
3,lisi,654321,女,16


use hive01;

create table t_user(
    id int,
    uname string,
    pwd string,
    sex string,
    age int )
row format delimited fields terminated by ','; --指定 字段之间用 , 分隔

当我们创建表后 在HDFS上会产生对应的文件夹 
/user/hive/warehouse/hive01.db/t_user
但是此时里面没有任何文件 
我们可以将user.txt文件上传到这个位置

select  * from t_user;
+------------+---------------+-------------+-------------+-------------+
| t_user.id  | t_user.uname  | t_user.pwd  | t_user.sex  | t_user.age  |
+------------+---------------+-------------+-------------+-------------+
| 1          | admin         | 123456      | 男           | 18          |
| 2          | zhangsan      | abc123      | 男           | 23          |
| 3          | lisi          | 654321      | 女           | 16          |
+------------+---------------+-------------+-------------+-------------+

案例2:复杂人员信息

liuyan,tangtang_mimi,liuliu:18_yanyan:14,huilong guan_beijing
jinlian,dalang_qingqing,jinjin:18_lianlian:19_aa:20,chao yang_beijing

结构

{
    "name": "liuyan",
    "friends": ["tangtang" , "mimi"] ,       //列表Array, 
    "children": {                      //键值Map,
        "liuliu": 18 ,
        "yanyan": 14
    }
    "address": {                      //结构Struct,
        "street": "hui long guan" ,
        "city": "beijing" 
    }
}

建表

create table test(
name string,
friends array<string>,
children map<string, int>,
address struct<street:string, city:string>
)
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的分隔符

复杂类型查询操作

1.数组

- arr[index]   取值
- size(arr)     长度
- 数组角标越界返回NULL
- explode(arr)

select name,friend[0],friend[1],friend[2] from t_user2;
+----------+-----------+-----------+-------+
|   name   |    _c1    |    _c2    |  _c3  |
+----------+-----------+-----------+-------+
| liuyan   | tangtang  | mimi      | NULL  |
| jinlian  | dalang    | qingqing  | NULL  |
+----------+-----------+-----------+-------+

select name,size(friend) as frendsize from  t_user2;
+----------+------------+
|   name   | frendsize  |
+----------+------------+
| liuyan   | 2          |
| jinlian  | 2          |
+----------+------------+

2.Map

map_keys(字段)  所有key
map_values(字段) 所有value

select map_keys(children),map_values(children) from t_user2;
+------------------------+----------+
|          _c0           |   _c1    |
+------------------------+----------+
| ["liuliu","yanyan"]    | [18,14]  |
| ["jinjin","lianlian"]  | [18,19]  |
+------------------------+----------+
select map_keys(children)[0],map_values(children)[0] from t_user2;
+---------+------+
|   _c0   | _c1  |
+---------+------+
| liuliu  | 18   |
| jinjin  | 18   |
+---------+------+
-- 根据key获取值
select children['liuliu'] from t_user2
+-------+
|  _c0  |
+-------+
| 18    |
| NULL  |
+-------+

3.Struct

select name,address.city,address.street from t_user2;
+----------+----------+---------------+
|   name   |   city   |    street     |
+----------+----------+---------------+
| liuyan   | beijing  | huilong guan  |
| jinlian  | beijing  | chao yang     |
+----------+----------+---------------+

4.3.2 Hive读写文件机制(了解)

Hive读取文件机制︰首先调用InputFormat（默认TextInputFormat )，返回一条一条kv键值对记录（默认是一行对应一条键值对)。然后调用SerDe(默认LazySimpleSerDe )的Deserializer，将一条记录中的value根据分隔符切分为各个字段。

Hive写文件机制:将Row写入文件时，首先调用SerDe(默认LazySimpleSerDe )的Serializer将对象转换成字节序列，然后调用OutputFormat将数据写入HDFS文件中。

SerDe:Serializer,DeSerializer.

当我们使用 row format delimited时 使用默认的LazySimpleSerDe类来处理数据。

如果数据文件格式比较特殊可以使用ROW FORMAT SERDE serde_name指定其他的Serde类来处理数据,甚至支持用户自定义SerDe类。

LazySimpleSerDe是Hive默认的序列化类，包含4种子语法，分别用于指定字段之间、集合元素之间、map映射 kv之间、换行的分隔符号。在建表的时候可以根据数据的特点灵活搭配使用。

Hive建表时如果没有row format语法指定分隔符，则采用默认分隔符;

默认的分割符是’ \001’，是一种特殊的字符，使用的是ASCII编码的值，键盘是打不出来的。

在vi编辑器中，连续按下Ctrl+v/Ctrl+a即可输入’\001’，显示^A

在文本编辑器中将以SOH的形式显示:

1admin123456男18
2zhangsanabc123男23
3lisi654321女16

如果使用默认分隔符的文件 建表时可以不指定 row format

create table t_user03(
    id int,
    uname string,
    pwd string,
    sex string,
    age int
);

 hdfs dfs -put user03.txt /user/hive/warehouse/hive01.db/t_user03
 
 select * from t_user03;
 
 +--------------+-----------------+---------------+---------------+---------------+
| t_user03.id  | t_user03.uname  | t_user03.pwd  | t_user03.sex  | t_user03.age  |
+--------------+-----------------+---------------+---------------+---------------+
| 1            | admin           | 123456        | 男             | 18            |
| 2            | zhangsan        | abc123        | 男             | 23            |
| 3            | lisi            | 654321        | 女             | 16            |
+--------------+-----------------+---------------+---------------+---------------+

4.3.3 Location指定文件位置

Hive表默认的存储路径是通过hive-site.xml配置文件中hive.metastore.warehouse.dir属性指定的.

默认位置

/user/hive/warehouse

创建数据库,就会在warehouse下产生一个xx.db的文件夹,在哪个库下创建表,就会在对应的文件夹下产生对应表的文件夹.当然这是默认情况 我们可以在创建表时使用Location来指定表的位置.

create table t_user_location(
    id int,
    uname string,
    pwd string,
    sex string,
    age int )
row format delimited fields terminated by ','
location "/aaa";  -- 这里指定在根目录下的aaa文件夹

将数据上传到aaa文件夹中
hdfs dfs -put user.txt /aaa

Location可以指定创建库的位置 也可以指定创建表的位置.

4.3.4 查询修改删除表

显示表信息
show tables; 
show tables like 'u'; 
desc t_user;   --字段  类型 
desc formatted t_user;  -- 表详细信息
修改表名
alter table old_table_name rename to new_table_name
添加列
alter table 表名  add  列名 数据类型;
修改列
alter table 表名 change 旧列名 新列名 数据类型
删除列  想删除哪列 就不写哪列 这里的删除就是用 ()中的内容替换原本的内容
alter  table  t_user_location replace columns (
    id int,
    uname string,
    pwd string,
    sex string,
    age int);
 删除表 
 drop table 表名;

4.3.5 数据的导入

4.3.5.1 直接放到对应目录

当我们创建好一张表后,会有对应的文件夹 默认位置 /user/hive/warehouse/xx.db/表名
我们之前的方式是通过put或者mv直接将文件放入到对应的文件夹下 

user.txt
1,admin,123456,男,18
2,zhangsan,abc123,男,23
3,lisi,654321,女,16

use hive01;

create table t_user_import(
    id int,
    uname string,
    pwd string,
    sex string,
    age int
)row format delimited fields terminated by ',';

我们可以直接将user.txt 上传到指定的位置 
hdfs dfs -put user.txt /user/hive/warehouse/hive01.db/t_user_import
也可以将hdfs上的数据 移动 或者复制到指定的位置
hdfs dfs -mv  /user.txt /user/hive/warehouse/hive01.db/t_user_import
hdfs dfs -cp  /user.txt /user/hive/warehouse/hive01.db/t_user_import

表的文件夹下可以有多个文件 都可以显示

4.3.5.2 使用load方式

相比较直接暴力放数据的行为 官方更推荐使用load的方式加载数据.

将本地文件加载到对应表 注意:此处的本地不是客户端的本地 指的是HS2服务所在的主机的本地.

指定LOCAL，将在本地文件系统中查找文件路径。
若指定相对路径，将相对于用户的当前工作目录进行解释;
用户也可以为本地文件指定完整的URI-例如:file:///root/user.txt

create table t_user_load(
    id int,
    uname string,
    pwd string,
    sex string,
    age int
)row format delimited fields terminated by ',';

-- 注意这是一个复制操作 本地的user.txt还存在   
load data local inpath "/root/user.txt" into table t_user_load;
-- 同名文件可以多次执行 会自动改名
load data local inpath "/root/user.txt" into table t_user_load;
-- 查询没有任何问题
select * from t_user_load;

-- 如果加上 overwite 会将原本文件中的所有文件覆盖
load  data local inpath "/root/user.txt" overwrite into table t_user_load;
-- 查询时只显示最后一次添加的内容
select * from t_user_load;

将HDFS文件加载到对应表 也不一定是HDFS也可以是其文件系统

没有指定LOCAL关键字 如果filepath指向的是一个完整的URI，会直接使用这个URI 
如果没有指定schema，Hive会使用在hadoop配置文件中参数fs.default.name指定的(不出意外，都是HDFS)。

-- 将hdfs上的根目录的user.txt 加载  注意这是一个移动的动作 原本位置的文件没有了
load data  inpath "/user.txt" into table t_user_load;
-- 加上overwirte还是覆盖操作
load data  inpath "/user.txt" overwrite into table t_user_load;

4.3.5.3 使用insert+select

使用insert into values方式虽然也可插入数据,但是插入的数据量小,每次都需要执行MR程序效率低下,不推荐使用.

insert into t_user_load values(1,'admin','123','男',100);

我们可以使用insert+select方式

insert+select表示:将后面查询返回的结果作为内容插入到指定表中，注意OVERWRITE将覆盖已有数据。需要保证查询结果列的数目和需要插入数据表格的列数目一致.如果查询出来的数据类型和插入表格对应的列数据类型不一致，将会进行转换，但是不能保证转换一定成功，转换失败的数据将会为NULL。

create table test_insert(
    id int,
    name string
)row format delimited fields terminated by ",";

查询user表的id和name 将结果保存到test_insert中;
insert into table test_insert select id,uname from t_user_load;

查询user表的id和name 将结果保存到test_insert中 将原有数据覆盖
insert overwrite table  test_insert select id,uname from t_user_load;

Multiple Inserts 多重插入 核心:一次扫描,多次插入 目的就是减少扫描的次数 完成多次insert操作.

create table test_insert02(
    id int
)row format delimited fields terminated by ",";

create table test_insert03(
    name string
)row format delimited fields terminated by ",";

我想将user表中id一列插入到  test_insert02 表中 将user表中name一列插入到test_insert03表中.
我们可以这样写

insert into table  test_insert02 select id from t_user;
insert into table  test_insert03 select uname from t_user;
但是这样的话 会扫描t_user两次 

一次扫描,多次插入
from t_user
insert into table test_insert02
select id
insert into table test_insert03
select uname;

4.3.5.4 查询创建表加载

create  table  t_tmp  as  select id , uname , age from t_user ;

4.3.5.5 使用import导入

export将表中的数据导出到指定的目录下 / 保持表原来的目录结构 使用import快速的导入数据到表中

用于数据的备份和迁移 , 导入的数据必须是export导出的数据

-- 将t_user数据导出到hdfs上 /aaa 文件夹中
export table t_user to '/aaa';
-- 创建表t_user_import 和t_user结构一样
create t_user_import02 like t_user;
-- 使用import将导出的数据导入到 t_user_import02表中
import  table t_user_import02 from '/aaa';

4.3.6 数据的导出

4.3.6.1 insert导出

Hive支持将select查询的结果导出成文件存放在文件系统。注意:导出操作是一个OVERWRITE覆盖操作，慎重。

导出到本地

-- 将t_user表中的数据 导出到本地文件夹 这是一个覆盖操作 会覆盖指定文件夹下的所有 如果指定为/ 那么废了
insert overwrite local directory '/data/output' select * from t_user;

--以这种方式导出的文件是使用的是默认分隔符 '\001'
--我们可以自己指定分隔符
insert overwrite local directory '/data/output2'
row format delimited fields terminated by ',' 
select * from t_user;

导出到HDFS

-- 不写local导出到hdfs上 实际上使用的是 hadoop配置文件中参数fs.default.name设置的值
insert overwrite  directory '/data/output2'
row format delimited fields terminated by ',' 
select * from t_user;

4.3.6.2 其他导出方式

只要将数据获取出来保存即可 
可以选择使用hadoop下载命令 
 hdfs dfs -get /user/hive/warehouse/hive01.db/t_user  /data
 可以使用hive shell命令
 hive  -e  "use hive01 ; select * from t_user ;"  >>  res.data  保存sql结果
 Export导出到HDFS上
 export table t_user to '/aaa';

4.4 内部表和外部表

默认情况下创建的表就是内部表，Hive拥有该表的结构和文件。换句话说，Hive完全管理表（元数据和数据）的生命周期，类似于RDBMS中的表。当你删除内部表时，它会删除数据以及表的元数据。可以使用DESCRIBE FORMATTED tablename,来获取表的元数据描述信息，从中可以看出表的类型。

外部表（External table )中的数据不是Hive拥有或管理的，只管理表元数据的生命周期。要创建一个外部表，需要使用EXTERNAL语法关键字。删除外部表只会删除元数据，而不会删除实际数据。在Hive外部仍然可以访问实际数据。实际场景中，外部表搭配location语法指定数据的路径，可以让数据更安全。

主要差异

• 无论内部表还是外部表，Hive都在Hive Metastore中管理表定义、字段类型等元数据信息。

• 删除内部表时，除了会从Metastore中删除表元数据，还会从HDFS中删除其所有数据文件。

• 删除外部表时，只会从Metastore中删除表的元数据，并保持HDFS位置中的实际数据不变。

  – 创建内部表 加载数据
  create table t_user_inner(
  id int,
  uname string,
  pwd string,
  sex string,
  age int
  )row format delimited fields terminated by ‘,’;
  load data local inpath ‘/root/user.txt’ into table t_user_inner;
  – 查看表信息
  desc formatted t_user_inner ;
  – 创建外部表 加载数据
  create external table t_user_ext(
  id int,
  uname string,
  pwd string,
  sex string,
  age int
  )row format delimited fields terminated by ‘,’;
  load data local inpath ‘/root/user.txt’ into table t_user_ext;
  – 查看表信息
  desc formatted table t_user_ext;

  – 删除内部表 数据被删除了
  drop table t_user_inner;
  – 删除外部表 数据并没有被删除
  drop table t_user_ext;

  – 再次重新创建 t_user_ext 可以直接查询数据
  select * from t_user_ext;

  – 将t_user_ext 转换为内部表
  alter table t_user_ext set tblproperties(‘EXTERNAL’=‘FALSE’); – 要求KV的大小写
  – 查询表信息发现 Table Type: MANAGED_TABLE
  desc formatted t_user_ext;
  –将t_user_ext 转换为外部表
  alter table t_user_ext set tblproperties(‘EXTERNAL’=‘true’);
  – 查询表信息发现 Table Type:EXTERNAL_TABLE
  desc formatted t_user_ext;

4.5 分区表

分区表实际上就是将表中的数据以某种维度进行划分文件夹管理 ,当要查询数据的时候,根据维度直接加载对应文件夹下的数据! 不用加载整张表所有的数据再进行过滤, 从而提升处理数据的效率!

比如在一个学生表中想查询某一个年级的学生,如果不分区,需要在整个数据文件中全表扫描,但是分区后只需要查询对应分区的文件即可.

4.5.1 静态分区

所谓静态分区指的是分区的属性值是由用户在加载数据的时候手动指定的。

1.创建单分区表:

-- 创建学生表 分区字段为年级grade
CREATE TABLE  t_student (
    sid int,
    sname string
 ) partitioned by(grade int)   -- 指定分区字段
row format delimited fields terminated by ',';
-- 注意∶分区字段不能是表中已经存在的字段，因为分区字段最终也会以虚拟字段的形式显示在表结构上。
select * from t_student;
+----------------+------------------+------------------+
| t_student.sid  | t_student.sname  | t_student.grade  |
+----------------+------------------+------------------+
+----------------+------------------+------------------+

stu01.txt
1,zhangsan,1
2,lisi,1
3,wangwu,1

stu02.txt
4,zhaoliu,2
5,lvqi,2
6,maba,2

stu03.txt
7,liuyan,3
8,tangyan,3
9,jinlian,3


-- 静态分区需要用户手动加载数据 并指定分区
load  data local  inpath '/root/stu01.txt' into table t_student partition(grade=1);
load  data local  inpath '/root/stu02.txt' into table t_student partition(grade=2);
load  data local  inpath '/root/stu03.txt' into table t_student partition(grade=3);
-- 查询
select * from t_student where grade=1;
+----------------+------------------+------------------+
| t_student.sid  | t_student.sname  | t_student.grade  |
+----------------+------------------+------------------+
| 1              | zhangsan         | 1                |
| 2              | lisi             | 1                |
| 3              | wangwu           | 1                |
+----------------+------------------+------------------+

注意:文件中的数据放入到哪个分区下就属于当前分区的数据,即使数据有误,也会按照当前分区处理

stu03.txt
7,liuyan,3
8,tangyan,3
9,jinlian,3
10.aaa,4

load  data local  inpath '/root/stu03.txt' overwrite into table t_student partition(grade=3);

select * from t_student where grade=3;
-- 最后一条记录虽然写的是4 但是 放到了年级3分区下 效果也是年级3
+----------------+------------------+------------------+
| t_student.sid  | t_student.sname  | t_student.grade  |
+----------------+------------------+------------------+
| 7              | liuyan           | 3                |
| 8              | tangyan          | 3                |
| 9              | jinlian          | 3                |
| 10             | aaa              | 3                |
+----------------+------------------+------------------+

2.创建多分区表

-- 创建学生表 分区字段为年级grade 班级clazz
CREATE TABLE  t_student02 (
    sid int,
    sname string
 ) partitioned by(grade int,clazz int)   -- 指定分区字段
row format delimited fields terminated by ',';

1年级1班
stu0101.txt  
1,zhangsan,1,1
2,lisi,1,1

1年级2班
stu0102.txt
3,wangwu,1,2

2年级1班
stu0201.txt
4,zhangsan,2,1
5,lisi,2,1
6,maba,2,1

3年级1班
stu0301.txt
7,liuyan,3,1
8,tangyan,3,1
3年级2班
9,dalang,3,2
10,jinlian,3,2


load  data local  inpath '/root/stu0101.txt' into table t_student02 partition(grade=1,clazz=1);
load  data local  inpath '/root/stu0102.txt' into table t_student02 partition(grade=1,clazz=2);
load  data local  inpath '/root/stu0201.txt' into table t_student02 partition(grade=2,clazz=1);
load  data local  inpath '/root/stu0301.txt' into table t_student02 partition(grade=3,clazz=1);
load  data local  inpath '/root/stu0302.txt' into table t_student02 partition(grade=3,clazz=2);

select * from t_student02 where grade=1 and clazz=2;
+------------------+--------------------+--------------------+--------------------+
| t_student02.sid  | t_student02.sname  | t_student02.grade  | t_student02.clazz  |
+------------------+--------------------+--------------------+--------------------+
| 7                | liuyan             | 3                  | 1                  |
| 8                | tangyan            | 3                  | 1                  |
+------------------+--------------------+--------------------+--------------------+

注意:我们既然建立了分区,就要保证分区查询的的命中率,查询尽量使用设置的分区字段去查询.分区虽然避免了全表扫描,但是也可能会产生大量的小文件,有利有弊.

3.分区其他操作(了解)

-- 查看分区 
show partitions t_student02;
-- 添加分区
alter table t_student02 add  partition (grade=4,clazz=1);
-- 删除分区
alter table t_student02 drop  partition (grade=4,clazz=1);

4.5.2 动态分区

静态分区与动态分区的主要区别在于静态分区是手动指定，而动态分区是通过数据来进行判断.

详细来说:静态分区需要我们自己手动load并指定分区,如果数据很多,那么是太费劲了.而动态分区指的是分区的字段值是基于查询结果（参数位置）自动推断出来的。核心语法就是inscrt+sclect。

开启动态分区首先要在hive会话中设置如下的参数

-- 临时设置 重新连接需要重新设置
set hive.exec.dynamic.partition=true; 
set hive.exec.dynamic.partition.mode=nonstrict;

其余参数配置如下:

设置为true表示开启动态分区的功能（默认为false） 
--hive.exec.dynamic.partition=true; 

设置为nonstrict，表示允许所有分区都是动态的（默认为strict） 严格模式至少有一个静态分区
-- hive.exec.dynamic.partition.mode=nonstrict; 

每个mapper或reducer可以创建的最大动态分区个数(默认为100) 
比如：源数据中包含了一年的数据，即day字段有365个值，那么该参数就需要设置成大于365，
如果使用默认 值100，则会报错 
--hive.exec.max.dynamic.partition.pernode=100; 

一个动态分区创建可以创建的最大动态分区个数（默认值1000） 
--hive.exec.max.dynamic.partitions=1000; 

全局可以创建的最大文件个数（默认值100000） 
--hive.exec.max.created.files=100000; 

当有空分区产生时，是否抛出异常（默认false） 
-- hive.error.on.empty.partition=false;

操作步骤

1. 创建文件并上传

2. 创建外部表指向文件(相当于临时表)

3. 创建动态分区表

4. 查询外部表将数据动态存入分区表中

   创建文件并上传

   student.txt

   1,zhangsan,1,1
   2,lisi,1,1
   stu0102.txt
   3,wangwu,1,2
   4,zhangsan,2,1
   5,lisi,2,1
   6,maba,2,1
   7,liuyan,3,1
   8,tangyan,3,1
   9,dalang,3,2
   10,jinlian,3,2

   – 将文件上传到hdfs根目录
   hdfs dfs -put student.txt /stu

   创建外部表指向文件(相当于临时表)
   create external table t_stu_e(
   sid int,
   sname string,
   grade int,
   clazz int
   )row format delimited fields terminated by “,”
   location “/stu”;

   创建动态分区表
   create table t_stu_d(
   sid int,
   sname string
   )partitioned by (grade int,clazz int)
   row format delimited fields terminated by “,”;

   查询外部表将数据动态存入分区表中
   insert overwrite table t_stu_d partition (grade,clazz) select * from t_stu_e ;

   select * from t_stu_d;

4.6 分桶表

概述

分桶表也叫做桶表，叫法源自建表语法中bucket单词，是一种用于优化查询而设计的表类型。

分区提供一个隔离数据和优化查询的便利方式。不过，并非所有的数据集都可形成合理的分区。不合理的数据分区划分方式可能导致有的分区数据过多，而某些分区没有什么数据的尴尬情况 。分桶是将数据集分解为更容易管理的若干部分的另一种技术。

对Hive(Inceptor)表分桶可以将表中记录按分桶键(字段)的哈希值分散进多个文件中，这些小文件称为桶。桶以文件为单位管理数据!分区针对的是数据的存储路径；分桶针对的是数据文件。

分桶的原理

Hive采用对列值哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中。

bucket num = hash_function(bucketing_column) mod   num_buckets
 分隔编号      哈希方法(分桶字段)              取模   分桶的个数

分桶的好处

1. 基于分桶字段查询时，减少全表扫描.

2. 根据join的字段对表进行分桶操作,join时可以提高MR程序效率，减少笛卡尔积数量.

3. 分桶表数据进行高效抽样.数据量大时,使用抽样数据估计和推断整体特性.

分桶表的创建

1.准备person.txt上传到hdfs
2.创建外部表指向person.txt
3.创建分桶表
4.查询外部表将数据加载到分桶表中


person.txt 
public class Test02 {
    public static void main(String[] args) {
        for (int i = 1; i <= 10000; i++) {
            System.out.println(i + "," + "liuyan" + (new Random().nextInt(10000) + 10000));
        }
    }
}

 hdfs dfs -mkdir /person
hdfs dfs -put person.txt /person

2.创建外部表指向person.txt
create external table  t_person_e(
     id int,
     pname string
) row format delimited fields terminated by ","
    location "/person";

select  * from t_person_e;

create table  t_person(
    id int,
    pname string
)clustered by(id) sorted by (pname) into 24 buckets
row format delimited fields terminated by ",";

insert overwrite table t_person select * from t_person_e ;

桶表抽样

-- tablesample是抽样语句，语法：TABLESAMPLE(BUCKET x OUT OF y) 
-- x表示从哪个bucket开始抽取。
   例如，table总bucket数为32，tablesample(bucket 3 out of 16)
   32 / 16  = 2  代表16桶为一组  抽取 第一组的第3桶  抽取第二组的第3桶 也就是第19桶
-- y必须是table总bucket数的倍数或者因子。hive根据y的大小，决定抽样的比例。
    tablesample(bucket 3 out of 64)
    32/64 = 2分之一      64桶为一组  不够一组 取第三桶的 前百分之50
    
    select * from t_person tablesample(bucket 4 out of 12); 
    24/12 抽取2桶数据      12桶一组 抽取 第一组第4桶 第二组 第4桶 4+12 =16桶