Hive入门全程干货

╰半橙微兮° 2024-04-18 16:37 58阅读 0赞

# 一、Hive简介 #

1.  **Facebook为了解决海量日志数据的分析而开发了Hive，后来开源给了Apache软件基金会。**
2.  Hive是一种用类SQL语句来协助读写、管理那些存储在分布式存储系统上大数据集的数据仓库软件。
3.  Hive的几个特点：

1. Hive最大的特点是通过类SQL来分析大数据，而避免了写MapReduce程序来分析数据，这样使得分析数据更
    容易。
    2. 数据是存储在HDFS上的，Hive本身并不提供数据的存储功能。
    3. Hive是将数据映射成数据库和一张张的表，库和表的元数据信息一般存在关系型数据库上（比如MySQL）。
    4. 数据存储方面：它能够存储很大的数据集，并且对数据完整性、格式要求并不严格。
    5. 数据处理方面：因为Hive语句最终会生成MapReduce任务去计算，所以不适用于实时计算的场景，它适用于
    离线分析。

# 二、Hive架构 #

**Hive的核心**

Hive的核心是驱动引擎，驱动引擎由四部分组成：

*  解释器：解释器的作用是将HiveSQL语句转换为语法树（AST）。
 *  编译器：编译器是将语法树编译为逻辑执行计划。
 *  优化器：优化器是对逻辑执行计划进行优化。
 *  执行器：执行器是调用底层的运行框架执行逻辑执行计划。

**Hive的底层存储**

Hive的数据是存储在HDFS上的。Hive中的库和表可以看作是对HDFS上数据做的一个映射。所以Hive必须是运行在一个Hadoop集群上的。

Hive语句的执行过程

*  Hive中的执行器，是将最终要执行的MapReduce程序放到YARN上以一系列Job的方式去执行。

Hive的元数据存储

*  Hive的元数据是一般是存储在MySQL这种关系型数据库上的，Hive和MySQL之间通过MetaStore服务交互。

<table> 
 <thead> 
  <tr> 
   <th>元数据项</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>DB_ID</td> 
   <td>数据库的编号</td> 
  </tr> 
  <tr> 
   <td>DESC</td> 
   <td>数据的描述</td> 
  </tr> 
  <tr> 
   <td>DB_LOCATION_URI</td> 
   <td>数据库在HDFS上的目录</td> 
  </tr> 
  <tr> 
   <td>NAME</td> 
   <td>数据库名</td> 
  </tr> 
  <tr> 
   <td>OWNER_NAME</td> 
   <td>创建用户</td> 
  </tr> 
  <tr> 
   <td>OWNER_TYPE</td> 
   <td>创建者的类型</td> 
  </tr> 
  <tr> 
   <td>Owner</td> 
   <td>库、表的所属者</td> 
  </tr> 
  <tr> 
   <td>LastAccessTime</td> 
   <td>最后修改时间</td> 
  </tr> 
  <tr> 
   <td>Table Type</td> 
   <td>表类型（内部表、外部表）</td> 
  </tr> 
  <tr> 
   <td>CreateTime</td> 
   <td>创建时间</td> 
  </tr> 
  <tr> 
   <td>Location</td> 
   <td>存储位置</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>表的字段信息</td> 
  </tr> 
 </tbody> 
</table>

Hive有很多种客户端。

*  cli命令行客户端：采用交互窗口，用hive命令行和Hive进行通信。
 *  HiveServer2客户端：用Thrift协议进行通信，Thrift是不同语言之间的转换器，是连接不同语言程序间的协议，通过JDBC或者ODBC去访问Hive。
 *  HWI客户端：hive自带的一个客户端，但是比较粗糙，一般不用。
 *  HUE客户端：通过Web页面来和Hive进行交互，使用的比较多。

# 三、Hive基本数据类型 #

Hive支持关系型数据中大多数基本数据类型，同时Hive中也有特有的三种复杂类型。  
下面的表列出了Hive中的常用基本数据类型：

<table> 
 <thead> 
  <tr> 
   <th>数据类型</th> 
   <th>长度</th> 
   <th>备注</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>Tinyint</td> 
   <td>1字节的有符号整数</td> 
   <td>-128~127</td> 
  </tr> 
  <tr> 
   <td>SmallInt</td> 
   <td>2个字节的有符号整数</td> 
   <td>-32768~32767</td> 
  </tr> 
  <tr> 
   <td>Int</td> 
   <td>4个字节的有符号整数</td> 
   <td>-2147483648 ~ 2147483647</td> 
  </tr> 
  <tr> 
   <td>BigInt</td> 
   <td>8个字节的有符号整数</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>Boolean</td> 
   <td>布尔类型，true或者false</td> 
   <td>true、false</td> 
  </tr> 
  <tr> 
   <td>Float</td> 
   <td>单精度浮点数</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>Double</td> 
   <td>双精度浮点数</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>String</td> 
   <td>字符串</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>TimeStamp</td> 
   <td>整数</td> 
   <td>支持Unix timestamp，可以达到纳秒精度</td> 
  </tr> 
  <tr> 
   <td>Binary</td> 
   <td>字节数组</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>Date</td> 
   <td>日期</td> 
   <td>0000-01-01 ~ 9999-12-31，常用String代替</td> 
  </tr> 
  <tr> 
   <td>- - -</td> 
   <td>- - -</td> 
   <td>- - -</td> 
  </tr> 
 </tbody> 
</table>

# 四、DDL语法 #

## 1. 数据库操作 ##

1.  创建一个数据库会在HDFS上创建一个目录，Hive里数据库的概念类似于程序中的命名空间，用数据库来组织表，在大量Hive的情况下，用数据库来分开可以避免表名冲突。Hive默认的数据库是default。
2.  hive不能使用关键字、数字开始的字符串来作库表名，不区分大小写。

#### 1.1 创建数据库例子： ####

hive> create database [if not exists] db1;

\[\]中的可以不写，判断是否存在

#### 1.2 查看数据库定义： ####

`Describe` 命令来查看数据库定义，包括：数据库名称、数据库在HDFS目录、HDFS用户名称。

hive> desc database db1;
    OK
    db1		hdfs://cluster1/user/hive/warehouse/db1.db	root	USER	
    Time taken: 0.059 seconds, Fetched: 1 row(s)

db1是数据库名称。

hdfs://cluster1/user/hive/warehouse/db1.db 是db1库对应的存储数据的HDFS上的根目录。

#### 1.3 查看数据库列表: ####

hive> show databases;
    OK
    db1
    default
    Time taken: 0.036 seconds, Fetched: 2 row(s)

#### 1.4 删除数据库 ####

删除数据库时，如果库中存在数据表，是不能删除的，要先删除所有表，再删除数据库。添加上`cascade`后，就可以先自动删除所有表后，再删除数据库。（友情提示：慎用啊！）删除数据库后，HDFS上数据库对应的目录就被删除掉了。

hive> drop database if exists db1 cascade;

#### 1.5 切换当前数据库 ####

hive> use db1;

## 2. 表操作 ##

#### 2.1 创建表 ####

**内外部表区别：**  
1、默认创建内部表，创建外部表需要external。  
2、一般使用外部表(长期存在的表、数据量大的、不希望把数据块删除的数据)，临时表或者确定使用即可清空全部数据(数据库和元数据)则可以使用内部表。  
3、内部表删除时将会删除元数据和hdfs中表对应的目录，而外部表删除时只会删除元数据，hdfs中的数据目录保留。  
4、`alter table userinfo set tblproperties('EXTERNAL'='true');` \#\#\#内部表转外部表，true一定要大写;  
`alter table log3 set tblproperties('EXTERNAL'='false');` \#\#false大小写都没有关系  
**分区表：**  
静态分区：加载数据到指定分区的值。  
动态分区：数据未知，根据分区的值来确定需要创建的分区。  
混合分区：静态和动态都有。  
动态分区的属性：

set hive.exec.dynamic.partition=true #设定动态分区
    set hive.exec.dynamic.partition.mode=strict/nonstrict #设定严格模式与否
    set hive.exec.max.dynamic.partitions=1000 #设定最大分区数
    set hive.exec.max.dynamic.partitions.pernode=100 #设定单节点最大分区数

strict:严格模式必须至少一个静态分区  
nonstrict：可以所有的都为动态分区，但是建议尽量评估动态分区的数量。

**桶表：**

###### 2.1.1 创建普通表（内部表） ######

hive> create table if not exists userinfo  
            > (
            >   userid int,
            >   username string,
            >   cityid int,
            >   createtime date    
            > )
            > row format delimited fields terminated by '\t'
            > stored as textfile;
            OK
            Time taken: 2.133 seconds

以上例子是创建表的一种方式，如果表不存在，就创建表userinfo。row format delimited fields terminated by ‘\\t’ 是指定列之间的分隔符；stored as textfile是指定文件存储格式为textfile。  
创建表一般有几种方式：

*  create table 方式：以上例子中的方式。
 *  create table as select 方式：根据查询的结果自动创建表，并将查询结果数据插入新建的表中。
 *  create table like tablename1 方式：是克隆表，只复制tablename1表的结构。复制表和克隆表会在下面的Hive数据管理部分详细讲解。

###### 2.1.2 创建外部表 ######

外部表是没有被hive完全控制的表，当表删除后，数据不会被删除。

hive> create external table iislog_ext (
            >  ip string,
            >  logtime string    
            > )
            > ;

###### 2.1.3 创建分区表 ######

Hive 分区表有  
Hive查询一般是扫描整个目录，但是有时候我们关心的数据只是集中在某一部分数据上，比如我们一个Hive查询，往往是只是查询某一天的数据，这样的情况下，可以使用分区表来优化，一天是一个分区，查询时候，Hive只扫描指定天分区的数据。

普通表和分区表的区别在于：一个Hive表在HDFS上是有一个对应的目录来存储数据，普通表的数据直接存储在这个目录下，而分区表数据存储时，是再划分子目录来存储的。一个分区一个子目录。主要作用是来优化查询性能。（可创建多级分区）

–创建经销商操作日志表

create table user_action_log
        (
        companyId INT comment   '公司ID',
        userid INT comment   '销售ID',
        originalstring STRING comment   'url', 
        host STRING comment   'host',
        absolutepath STRING comment   '绝对路径',
        query STRING comment   '参数串',
        refurl STRING comment   '来源url',
        clientip STRING comment   '客户端Ip',
        cookiemd5 STRING comment   'cookiemd5',
        timestamp STRING comment   '访问时间戳'
        )
        partitioned by (dt string)
        row format delimited fields terminated by ','
        stored as textfile;

这个例子中，这个日志表以dt字段分区，dt是个虚拟的字段，dt下并不存储数据，而是用来分区的，实际数据存储时，dt字段值相同的数据存入同一个子目录中，插入数据或者导入数据时，同一天的数据dt字段赋值一样，这样就实现了数据按dt日期分区存储。

当Hive查询数据时，如果指定了dt筛选条件，那么只需要到对应的分区下去检索数据即可，大大提高了效率。所以对于分区表查询时，尽量添加上分区字段的筛选条件。

###### 2.1.4 创建桶表 ######

桶表也是一种用于优化查询而设计的表类型。创建通表时，指定桶的个数、分桶的依据字段，hive就可以自动将数据分桶存储。查询时只需要遍历一个桶里的数据，或者遍历部分桶，这样就提高了查询效率。举例：

\------创建订单表

create table user_leads
    (
    leads_id string,
    user_id string,
    user_id string,
    user_phone string,
    user_name string,
    create_time string
    )
    clustered by (user_id) sorted by(leads_id) into 10 buckets 
    row format delimited fields terminated by '\t' 
    stored as textfile;

对这个例子的说明：

*  clustered by是指根据user\_id的值进行哈希后模除分桶个数，根据得到的结果，确定这行数据分入哪个桶中，这样的分法，可以确保相同user\_id的数据放入同一个桶中。而经销商的订单数据，大部分是根据user\_id进行查询的。这样大部分情况下是只需要查询一个桶中的数据就可以了。
 *  sorted by 是指定桶中的数据以哪个字段进行排序，排序的好处是，在join操作时能获得很高的效率。
 *  into 10 buckets是指定一共分10个桶。

在HDFS上存储时，一个桶存入一个文件中，这样根据user\_id进行查询时，可以快速确定数据存在于哪个桶中，而只遍历一个桶可以提供查询效率。

— 分桶表读写过程

待完善。。。。。。

#### 2.2 查看有哪些表 ####

–查询库中表

show tables;

– 查询分区表

show  partitions tablename;
    --直接写到文件中
    show partitions tablename > result.txt; //覆盖写
    show partitions tablename >> result.txt; //追加写

`Show TABLES '*info';` \--可以用正则表达式筛选要列出的表

#### 2.3 查看表定义 ####

###### 2.3.1 查看简单定义： ######

describe userinfo;

###### 2.3.2 查看表详细信息： ######

describe formatted userinfo;

执行结果如下所示：

<table> 
 <thead> 
  <tr> 
   <th>备注</th> 
   <th>col_name</th> 
   <th>data_type</th> 
   <th>comment</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>列信息</td> 
   <td># col_name</td> 
   <td>data_type</td> 
   <td>comment</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>NULL</td> 
   <td>NULL</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>userid</td> 
   <td>int</td> 
   <td></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>username</td> 
   <td>string</td> 
   <td></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>cityid</td> 
   <td>int</td> 
   <td></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>createtime</td> 
   <td>date</td> 
   <td></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td></td> 
   <td>NULL</td> 
   <td>NULL</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td># Detailed Table Information</td> 
   <td>NULL</td> 
   <td>NULL</td> 
  </tr> 
  <tr> 
   <td>所在库</td> 
   <td>Database:</td> 
   <td>user_db</td> 
   <td>NULL</td> 
  </tr> 
  <tr> 
   <td>所属HUE用户 Owner: admin NULL</td> 
   <td></td> 
   <td></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>表创建时间</td> 
   <td>CreateTime:</td> 
   <td>Tue Aug 16 06:05:14 PDT 2019</td> 
   <td>NULL</td> 
  </tr> 
  <tr> 
   <td>最后访问时间</td> 
   <td>LastAccessTime:</td> 
   <td>UNKNOWN</td> 
   <td>NULL</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>Protect Mode:</td> 
   <td>None</td> 
   <td>NULL</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>Retention:</td> 
   <td>0</td> 
   <td>NULL</td> 
  </tr> 
  <tr> 
   <td>表数据文件在HDFS上路径</td> 
   <td>Location:</td> 
   <td>hdfs://cluster1/user/hive/warehouse/db1.db/userinfo</td> 
   <td>NULL</td> 
  </tr> 
  <tr> 
   <td>表类型(内部表或者外部表)</td> 
   <td>Table Type:</td> 
   <td>MANAGED_TABLE</td> 
   <td>NULL</td> 
  </tr> 
  <tr> 
   <td>表分区信息</td> 
   <td>Table Parameters:</td> 
   <td>NULL</td> 
   <td>NULL</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td></td> 
   <td>transient_lastDdlTime</td> 
   <td>1471352714</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td></td> 
   <td>NULL</td> 
   <td>NULL</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td># Storage Information</td> 
   <td>NULL</td> 
   <td>NULL</td> 
  </tr> 
  <tr> 
   <td>序列化反序列化类</td> 
   <td>SerDe Library:</td> 
   <td>org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe</td> 
   <td>NULL</td> 
  </tr> 
  <tr> 
   <td>mapreduce中的输入格式</td> 
   <td>InputFormat:</td> 
   <td>org.apache.hadoop.mapred.TextInputFormat</td> 
   <td>NULL</td> 
  </tr> 
  <tr> 
   <td>mapreduce中的输出格式</td> 
   <td>OutputFormat:</td> 
   <td>org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat</td> 
   <td>NULL</td> 
  </tr> 
  <tr> 
   <td>压缩</td> 
   <td>Compressed:</td> 
   <td>No</td> 
   <td>NULL</td> 
  </tr> 
  <tr> 
   <td>总占用数据块个数</td> 
   <td>Num Buckets:</td> 
   <td>-1</td> 
   <td>NULL</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>Bucket Columns:</td> 
   <td>[]</td> 
   <td>NULL</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>Sort Columns:</td> 
   <td>[]</td> 
   <td>NULL</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>Storage Desc Params:</td> 
   <td>NULL</td> 
   <td>NULL</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td></td> 
   <td>field.delim</td> 
   <td>\t</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td></td> 
   <td>serialization.format</td> 
   <td>\t</td> 
  </tr> 
  <tr> 
   <td>—</td> 
   <td>—</td> 
   <td>—</td> 
   <td>—</td> 
  </tr> 
 </tbody> 
</table>

#### 2.4 修改表 ####

常用的对表的修改操作有：修改表名、添加字段、删除字段、修改字段等

###### 2.4.1 修改表名 ######

–将表名从userinfo改为user\_info

alter table userinfo rename to user_info;

###### 2.4.2 添加字段 ######

—在user\_info表添加一个字段provinceid，int 类型

alter table user_info add columns (provinceid int );

###### 2.4.3 删除字段 ######

— 删除字段只能是覆盖重写，并不是真正的删除

replace columns
    alter table user_info replace columns(
     userid int,
     username string,
     createtime date    
    );

###### 2.4.4 修改字段 ######

alter table user_info 
    replace columns (
    userid int,
    username string,
    cityid int,
    joindate date,
    provinceid int
    );

修改字段，只是修改了Hive表的元数据信息（元数据信息一般是存储在MySql中），并不对存在于HDFS中的表数据做修改。  
并不是所有的Hive表都可以修改字段，只有使用了native SerDe (序列化反序列化类型)的表才能修改字段

#### 2.5 删除表 ####

–如果表存在，就删除。

drop table if exists user_info;

if exists 可以不写  
或者

truncate table tableName;

删除表中所有数据，而不破坏表结构  
或者

insert overwrite table A select id,name from A where id !=2;

依然是以覆盖的方式删除某一行数据

#### 2.6 关于分区表 ####

1、查看分区

show partitions 表名;

2、添加分区

alter table part1 add partition(dt='2019-09-10');
    alter table part1 add partition(dt='2019-09-13') partition(dt='2019-09-12');
    alter table part1 add partition(dt='2019-09-11') location  '/user/hive/warehouse/db1.db/part1/dt=2019-09-10';

3、分区名称修改

alter table part1 partition(dt='2019-09-10') rename to partition(dt='2019-09-14');

4、修改分区路径

alter table part1 partition(dt='2019-09-14') set location '/user/hive/warehouse/db1.db/part1/dt=2019-09-09';    --错误使用
    alter table part1 partition(dt='2019-09-14') set location 'hdfs://hadoo01:9000/user/hive/warehouse/db1.db/part1/dt=2019-09-09';  --绝对路径

5、删除分区

alter table part1 drop partition(dt='2019-09-14');
    alter table part1 drop partition(dt='2019-09-12'),partition(dt='2019-09-13');

# 五、DML语法 #

#### 1. 向Hive中加载数据 ####

几种常用的方法---------------------------------------------------------

###### 1.1 加载到普通表 ######

1.  可以将本地文本文件内容批量加载到Hive表中，要求文本文件中的格式和Hive表的定义一致，包括：字段个数、字段顺序、列分隔符都要一致。  
    这里的user\_info表的表定义是以\\t作为列分隔符，所以准备好数据后，将文本文件拷贝到hive客户端机器上后，执行加载命令。

load data local inpath '/home/hadoop/userinfodata.txt' overwrite into table user_info;

*  local关键字表示源数据文件在本地，源文件可以在HDFS上，如果在HDFS上，则去掉local，inpath后面的路径是类似”hdfs://namenode:9000/user/datapath”这样的HDFS上文件的路径。  
    overwrite关键字表示如果hive表中存在数据，就会覆盖掉原有的数据。如果省略overwrite，则默认是追加数据。  
    加载完成数据后，在HDFS上就会看到加载的数据文件。

1.  创建表的时候，使用location指定表所对应的目录即可。

create table if not exists db1.user_info2(
    id int comment "this is userid",
    name string
    )
    row format delimited fields terminated by ' '
    lines terminated by '\n'
    stored as textfile --存储格式
    location '/user/hive/warehouse/db1.db/user_info'  --只能是hdfs中的目录，直接为表加载数据
    ;

1.  直接将hdfs中数据使用命令上传到表所对应的目录即可。

hadoop fs -put userinfodata /user/hive/warehouse/db1.db/user_info2;

###### 1.2 加载到分区表 ######

load data local inpath '/home/hadoop/actionlog.txt' overwrite into table user_action_log 
    PARTITION (dt='2019-09-09');

在严格模式下必须指定分区，也可以直接put上去，没有问题  
partition 是指定这批数据放入分区2019-09-09中。

###### 1.3 加载到分桶表 ######

\------先创建普通临时表

create table user_leads_tmp
    (
    leads_id string,
    user_id string,
    user_id string,
    user_phone string,
    user_name string,
    create_time string
    )
    row format delimited fields terminated by ',' 
    stored as textfile;

\------数据载入临时表

load data local inpath '/home/hadoop/lead.txt' overwrite into table user_leads_tmp;

\------导入分桶表

set hive.enforce.bucketing = true;
    insert overwrite table user_leads select * from  user_leads_tmp;
    set hive.enforce.bucketing = true;

这个配置非常关键，为true就是设置为启用分桶。

###### 1.4 导出数据 ######

–导出数据，是将hive表中的数据导出到本地文件中。

insert overwrite local directory '/home/hadoop/user_info.bak2016-08-22 '
    select * from user_info;

去掉local关键字，也可以导出到HDFS上。

###### 1.5 插入数据 ######

insert select 语句

上一节分桶表数据导入，用到从user\_leads\_tmp表向user\_leads表中导入数据，用到了insert数据。

insert overwrite table user_leads select * from  user_leads_tmp;

这里是将查询结果导入到表中，overwrite关键字是覆盖目标表中的原来数据。如果缺省，就是追加数据。  
如果是插入数据的表是分区表，那么就如下所示：

insert overwrite table user_leads PARTITION (dt='2017-05-26') 
    select * from  user_leads_tmp;

**一次遍历多次插入**

from user_action_log
    insert overwrite table log1 select companyid,originalstring  where companyid='100006'
    insert overwrite table log2 select companyid,originalstring  where companyid='10002'

每次hive查询，都会将数据集整个遍历一遍。当查询结果会插入多个表中时，可以采用以上语法，将一次遍历写入多个表，以达到提高效率的目的。

###### 1.6 复制表 ######

复制表是将源表的结构和数据复制并创建为一个新表，复制过程中，可以对数据进行筛选，列可以进行删减。

create table user_leads_bak
        row format delimited fields terminated by '\t'
        stored as textfile
        as
        select leads_id,user_id,'2019-08-22' as bakdate
        from user_leads
        where create_time<'2019-08-22';

上面这个例子是对user\_leads表进行复制备份，复制时筛选了2019-08-22以前的数据，减少几个列，并添加了一个bakdate列。

###### 1.7 克隆表 ######

克隆表时会克隆源表的所有元数据信息，但是不会复制源表的数据。

–克隆表user\_leads，创建新表user\_leads\_like

create table user_leads_like like  user_leads;

###### 1.8 备份表 ######

备份是将表的元数据和数据都导出到HDFS上。

export table user_action_log partition (dt='2016-08-19')
    to '/user/hive/action_log.export'

这个例子是将user\_action\_log表中的一个分区，备份到HDFS上，to后面的路径是HDFS上的路径。

###### 1.9 还原表 ######

将备份在HDFS上的文件，还原到user\_action\_log\_like表中。

import table user_action_log_like from '/user/hive/action_log.export';

# 六、HQL语法 #

HQL, Hibernate Query Language的缩写，提供更加丰富灵活、更为强大的查询能力；HQL更接近SQL语句查询语法。

## 1. 查询语句 ##

在SQL中，查询语句一般为：

select
    from
    join
    on
    where 
    group by
    [grouping sets/with cube/with rollup]
    having
    order by/sort by 
    limit
    union/union all
    ;
    
    执行顺序：-----------
    FROM 
    <left_table>
    ON 
    <join_condition>
    <join_type>
     JOIN 
    <right_table>
    WHERE 
    <where_condition>
    GROUP BY 
    <group_by_list>
    HAVING 
    <having_condition>
    SELECT
    DISTINCT 
    <select_list>
    ORDER BY 
    <order_by_condition>
    LIMIT 
    <limit_number>

这样的形式，在hive中大部分相同  
对子查询的支持也并不友好  
**注意事项：**  
尽量不要使用子查询、尽量不要使用 in 和 not in  
查询尽量避免join连接查询，但是这种操作咱们是永远避免不了的。  
查询永远是小表驱动大表（永远是小结果集驱动大结果集）  
几种常见的join：  
内连接（inner join）  
外连接（outer join）  
左连接（left join）从hive0.8开始  
右连接（right join）  
全连接（full join）

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2NTI1OTA2_size_16_color_FFFFFF_t_70]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2NTI1OTA2_size_16_color_FFFFFF_t_70 1]  
![(full.png)\]][full.png]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2NTI1OTA2_size_16_color_FFFFFF_t_70 2]  
上周，我读到一篇文章，认为还有比维恩图更好的解释方式。我发现确实如此，换一个角度解释，更容易懂。

所谓"连接"，就是两张表根据关联字段，组合成一个数据集。问题是，两张表的关联字段的值往往是不一致的，如果关联字段不匹配，怎么处理？比如，表 A 包含张三和李四，表 B 包含李四和王五，匹配的只有李四这一条记录。

很容易看出，一共有四种处理方法。  
只返回两张表匹配的记录，这叫内连接（inner join）。  
返回匹配的记录，以及表 A 多余的记录，这叫左连接（left join）。  
返回匹配的记录，以及表 B 多余的记录，这叫右连接（right join）。  
返回匹配的记录，以及表 A 和表 B 各自的多余记录，这叫全连接（full join）。

下图就是四种连接的图示。我觉得，这张图比维恩图更易懂，也更准确。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2NTI1OTA2_size_16_color_FFFFFF_t_70 3]  
上图中，表 A 的记录是 123，表 B 的记录是 ABC，颜色表示匹配关系。返回结果中，如果另一张表没有匹配的记录，则用 null 填充。

这四种连接，又可以分成两大类：内连接（inner join）表示只包含匹配的记录，外连接（outer join）表示还包含不匹配的记录。所以，左连接、右连接、全连接都属于外连接。

此外，还存在一种特殊的连接，叫做"交叉连接"（cross join），指的是表 A 和表 B 不存在关联字段，这时表 A（共有 n 条记录）与表 B （共有 m 条记录）连接后，会产生一张包含 n x m 条记录的新表（见下图）。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2NTI1OTA2_size_16_color_FFFFFF_t_70 4]

常用：  
left join  
inner join

left join:以左表为基本表，右表关联不上用null替代  
left join \\ left semi join \\ left outer join

left semi join 和 left join 区别：  
1、都是左表连接，但是semi join右表关联不左表也不会出来，left join不一样  
2、semi join只能查询左表信息，left join可以查询所有  
3、semi join是left join的一种优化  
4、semi join一般使用查询存在的情况

### 1.1 表查询 ###

**准备阶段：**  
准备数据  
数据表hivedata1

1,a
    2,b
    3,c
    4,d
    7,y
    8,u

数据表hivedata2

2,bb
    3,cc
    7,yy
    9,pp

建表语句

create table if not exists u1(
    id int,
    name string
    )
    row format delimited fields terminated by ','
    ;

create table if not exists u2(
    id int,
    name string
    )
    row format delimited fields terminated by ','
    ;

导入数据

load data local inpath '/home/hivedata1' into table u1;
    load data local inpath '/home/hivedata2' into table u2;

### 1.1.1 Join查询 ###

1.  查询u1表中存在，但是在u2表中不存在的（按id）

set hive.exec.mode.local.auto=true; -- 本地模式运行
    select
    u1.*
    from u1 u1
    left join u2 u2
    on u1.id = u2.id
    where u2.id is null
    ;

这里应用leftjoin：右表关联不上的用null代替

set hive.exec.mode.local.auto=true;
    select
    u1.*
    from u1 u1
    left outer join u2 u2 --左外链接
    on u1.id = u2.id
    where u2.id is null
    ;

查询结果：  
![在这里插入图片描述][20190910175835130.png]  
**左半连接**

set hive.exec.mode.local.auto=true;
    select
    u1.id
    from u1 u1
    left semi join u2 u2 --左半连接
    on u1.id = u2.id
    ;

相当于in，找到u1表中与u2相同的id  
结果：  
![在这里插入图片描述][20190910185919314.png]

right semi join在hive中不支持，其它的right join\\right outer join都支持。

**full outer join取并集：**

set hive.exec.mode.local.auto=true;
    select
    u1.*,
    u2.*
    from u1 u1
    full outer join u2 u2
    on u1.id = u2.id
    ;

按照id将两张表合并，且不匹配的字符按null处理  
结果：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2NTI1OTA2_size_16_color_FFFFFF_t_70 5]  
**inner join 两表取交集，若有多张表加‘，’分割**

set hive.exec.mode.local.auto=true;
    select
    u1.*,
    u2.*
    from u1 u1
    inner join u2 u2
    on u1.id = u2.id
    ;

结果：  
![在这里插入图片描述][20190910190830121.png]  
**小表标识：**  
hive提供小表标识：使用的是STREAMTABLE(小表别名)  
当小表不得不放在右边时，我们可以给小标加上小表标识，这样就会把小表放进缓存里，执行时会更快

set hive.exec.mode.local.auto=true;
    select
    /*+STREAMTABLE(d)*/
    d.name,
    e.name
    from u1 e
    join u2 d 
    on d.id = e.id
    ;

![在这里插入图片描述][20190910193954677.png]  
inner join 和outer join的区别：  
分区字段对outer join 中的on条件是无效，对inner join 中的on条件有效

有inner join 但是没有full inner join , 有full outer join但是没有outer join  
所有join连接，只支持等值连接(= 和 and )。不支持 != 、 < 、> 、 <> 、>=、 <= 、or

### 1.1.2 group by 、sort by 、cluster by ###

where后不能跟聚合函数,普通函数可以

group by ：分组，通常和聚合函数搭配使用。

如果语句带group by子句，则 select后面的字段要么在group by中出现，要么在聚合函数里面。

一般有group by出现将会有reducetask。

Having ： 分组以后的结果再次过滤。

limit ： 取多少条。默认100000条，文件个数10个文件

cluster by ：兼有distribute by以及sort by的升序功能。  
排序只能是升序排序（默认排序规则），不能指定排序规则为asc 或者desc。

准备数据：数据量较大，此处comment表，创表语句为

create table if not exists comment(
    id string,
    commentid string,
    dt string,
    comm string
    )
    row format delimited fields terminated by '\t'
    location '/user/hive/warehouse/wb.db/ods_bhv_comment'-- 约 5G数据
    ;

set hive.exec.mode.local.auto=true;
    select
    id,
    commentid
    from comment c
    cluster by id
    limit 10
    ;
    
    set hive.exec.mode.local.auto=true;
    select
    id,
    commentid
    from comment c
    distribute by id
    sort by id asc
    limit 10
    ;

distribute by ： 根据by后的字段和reducer个数，决定map的输出去往那个reducer。  
默认使用查询的第一列的hash值来决定map的输出去往那个reducer。如果reducer的个数为1时没有任何体现。

sort by:局部排序，只保证单个reducer有顺序。  
order by：全局排序，保证所有reducer中的数据都是有顺序。  
如果reduser个数只有一个，两者都差不多。  
两者都通常和 desc 、 asc 搭配。默认使用升序asc。

手动设置reducer个数：

set mapreduce.job.reduces=2;
    set hive.exec.mode.local.auto=true;
    select
    e.id,
    e.name
    from u1 e
    order by e.id desc 
    ;
    
    set mapreduce.job.reduces=2;
    set hive.exec.mode.local.auto=true;
    select
    e.id,
    e.name
    from u1 e
    sort by e.id desc 
    ;

order by的缺点：  
由于是全局排序，所以所有的数据会通过一个Reducer 进行处理，当数据结果较大的时候，一个Reducer 进行处理十分影响性能。

sort by的特点：  
常用于分组的时候的排序。

注意事项：  
当开启MR 严格模式的时候ORDER BY 必须要设置 LIMIT 子句 ，否则会报错

只要使用order by ，reducer的个数将是1个。  
如果sort by 和 distribute by 同时出现：那个在前面？？  
如果sort by 和 distribute by 同时出现，并且后面的字段一样、sort by使用升序时 <==> cluster by 字段

### 1.1.3 union 和 union all ###

union ：将多个结果集合并，去重，排序  
union all ：将多个结果集合并，不去重，不排序。

单个union 语句不支持：orderBy、clusterBy、distributeBy、sortBy、limit  
单个union语句字段的个数要求相同，字段的顺序要求相同、字段类型需要一样(union)。  
union子句不支持group by\\distribute by\\order by (每个union子句嵌套中可以使用)

set mapreduce.job.reduces=1;
    select
    d.id as deptno,
    d.name as dname
    from u1 d
    union
    select
    e.id as deptno,
    e.name as dname
    from u2 e
    ;

set mapreduce.job.reduces=1;
    select
    d.id as deptno,
    d.name as dname
    from u1 d
    union all
    select
    e.id as deptno,
    e.name as dname
    from u2 e
    ;

### 1.1.4 复杂数据类型 ###

array : col array<基本类型> ,下标从0开始，越界不报错，以NULL代替  
map : column map<string,string>  
struct: col struct

array案例：  
数据：  
zhangsan 78,89,92,96  
lisi 67,75,83,94

建表：

create table if not exists arr1(
    name string,
    score array<String>
    )
    row format delimited fields terminated by '\t'
    ;

![在这里插入图片描述][20190910200142273.png]  
注意terminated顺序，将集合中的数据分割？？

create table if not exists arr2(
    name string,
    score array<String>
    )
    row format delimited fields terminated by '\t'
    collection items terminated by ','
    ;

![在这里插入图片描述][20190910200320896.png]  
**查询：**

select
    *
    from arr2
    where score[1] > 80;

![在这里插入图片描述][20190910201012975.png]

select
    name,
    score[1],
    score[20]
    from arr2
    where score[1] > 80;

![在这里插入图片描述][2019091020110179.png]

select 
    name,
    score
    from arr2 
    where size(score) > 3;

![在这里插入图片描述][20190910201130377.png]  
**扩展查询：**

zhangsan 78,89,92,96  
zhangsan 78  
zhangsan 89  
zhangsan 92  
zhangsan 96

内嵌查询：  
explode:展开  
explode(ARRAY) 列表中的每个元素生成一行  
explode(MAP) map中每个key-value对，生成一行，key为一列，value为一列

select name,explode(score) score from arr2;

![在这里插入图片描述][20190910201308609.png]  
这里SELECT子句外不支持UDTF，也不能嵌套在表达式中  
用lateral view解除以上限制

select 
    cj 
    from arr2 
    lateral view explode(score) score as cj
    ;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2NTI1OTA2_size_16_color_FFFFFF_t_70 6]  
lateral view : 虚拟表  
**统计每个学生的总成绩：**

select 
    name,
    sum(cj) sum_cj 
    from arr2 
    lateral view explode(score) score as cj
    group by name
    ;

![在这里插入图片描述][20190910201522303.png]

如何往array字段写入数据：  
准备数据：

create table arr_temp
    as
    select name,cj from arr2 lateral view explode(score) score as cj;

1、collect\_set函数：

create table if not exists arr3(
    name string,
    score array<int>
    )
    row format delimited fields terminated by ' '
    collection items terminated by ','
    ;

将数据写成array格式：

insert into arr3
    select 
    name,
    collect_set(cast(cj as int)) 
    from arr_temp 
    group by name;
    
    insert into arr3
    select 
    name,
    collect_list(cast(cj as int)) 
    from arr_temp 
    group by name;

map案例：  
zhangsan chinese:90,math:87,english:63,nature:76  
lisi chinese:60,math:30,english:78,nature:  
wangwu chinese:89,math:,english:81,nature:9

create table if not exists map2(
    name string,
    score map<string,int>
    )
    row format delimited fields terminated by ' '
    collection items terminated by ','
    map keys terminated by ':'
    ;

加载数据

load data local inpath '/home/hivedata/map' into table map2;

查询：  
查询数学大于35分的学生的英语和自然成绩：

select 
    m.name,
    m.score['english'] ,
    m.score['nature']
    from map2 m
    where m.score['math'] > 35
    ;

展开数据：

-- explode
    select explode(score) as (m_class,m_score) from map2;

使用lateral view explode结合查询：

select 
    name,
    collect_list(concat_ws(":",m_class,cast(m_score as string)))
    from map2 
    lateral view explode(score) score as m_class,m_score
    group by name;

# 七、Hive严格模式 #

严格模式阻挡5类查询：  
1、笛卡尔积

set hive.mapred.mode=strict;
    select
    *
    from dy_part1 d1
    join dy_part2 d2
    ;

2、分区表没有分区字段过滤

set hive.mapred.mode=strict;
    select
    *
    from dy_part1 d1
    where d1.dt='2019-09-09'
    ;
    
    //不行
    select
    *
    from dy_part1 d1
    where d1.id > 2
    ;
    
    select
    *
    from dy_part2 d2
    where d2.year >= 2019
    ;

3、order by不带limit查询

select
    *
    from log3
    order by id desc
    ;

4、(bigint和string比较)Comparing bigints and strings.

5、(bigint和double比较)Comparing bigints and doubles.

# … #

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2NTI1OTA2_size_16_color_FFFFFF_t_70]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/00b5a1b029a946b99ee7f44c769162e8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2NTI1OTA2_size_16_color_FFFFFF_t_70 1]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/9cedbc6bfa194ecabb3a53ed67d66991.png
[full.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/778d63f8c9d2461b92bd09321e9bf545.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2NTI1OTA2_size_16_color_FFFFFF_t_70 2]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/16437f0dc71542788c07dde086d97600.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2NTI1OTA2_size_16_color_FFFFFF_t_70 3]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/4cadf6616b1a40cd9c5c18f5b4514dbc.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2NTI1OTA2_size_16_color_FFFFFF_t_70 4]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/24412e06f4bf4d16a0cdd12cb9cc83b4.png
[20190910175835130.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/bc32b7d3c4424fac8ee0c24b6b106497.png
[20190910185919314.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/89f8e14daf4344398a3daba9b4398fcc.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2NTI1OTA2_size_16_color_FFFFFF_t_70 5]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/7bb34e1bbe7048fc82c50057c6fa6953.png
[20190910190830121.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/c1506e0fce3f4c96892cfa753f721e78.png
[20190910193954677.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/7cfd2b8bdeb743038879d6f0f083d2c0.png
[20190910200142273.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/69bf3da4ec224782bac5669121576ce6.png
[20190910200320896.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/b87a9f8a7bea45659318d4a98e46df02.png
[20190910201012975.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/b1b0b1ba11164efdae6cb3c0b6f83fea.png
[2019091020110179.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/a46c7b5897bf4f8d84c8a03cc442e309.png
[20190910201130377.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/4c75dc03ed9b4566aa41813b873a2754.png
[20190910201308609.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/75a6ae4db0c94c3ea8f1fe8879225949.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2NTI1OTA2_size_16_color_FFFFFF_t_70 6]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/ec28286720d64a97a3f98c19fffc4b81.png
[20190910201522303.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/66edec0642734a25b22c099090582af5.png