Hive入门

最新推荐文章于 2021-11-25 10:37:36 发布

提灯寻梦在南国

最新推荐文章于 2021-11-25 10:37:36 发布

阅读量405

点赞数

分类专栏：大数据 Hive 文章标签： Hive特征 DDL DML

原文链接：https://blog.csdn.net/yu0_zhang0/article/details/81776459

版权

大数据同时被 2 个专栏收录

45 篇文章 4 订阅

订阅专栏

Hive

4 篇文章 0 订阅

订阅专栏

前言

本篇博客开始，南国开始着手写一个系列的Hive知识点。结合自己日常的学习笔记和网上好的资料，对于Hive比较重要的知识点做一个由浅到深的剖析。话不多说，干货送上~

Hive基本概念

先来谈谈自己的理解：
有些人可能会说Hive不就是写SQL的吗，那我们其实可以从另一个角度来理解:Hive就是那么强大啊，只要写SQL就能解决问题，其实这些人说的也没错Hive确实就是写SQL的，对于传统的 DBA人员或者会写SQL就很容易上手了，但是您知道他的底层细节吗，怎么优化呢？和传统的关系型数据库又有什么区别呢？等等一系列问题。

Hive是一个构建在Hadoop之上的数据仓库软件,它可以使已经存储的数据结构化，它提供类似sql的查询语句HiveQL对数据进行分析处理。 Hive将HiveQL语句转换成一系列成MapReduce作业并执行（SQL转化为MapReduce的过程你知道吗？）。用户可以很方便的使用命令行和JDBC程序的方式来连接到hive。目前，Hive除了支持MapReduce计算引擎，还支持Spark和Tez这两中分布式计算引擎。常用于离线批处理。

Hive的产生背景
大数据的时代，海量的数据对于传统的关系型数据库来说维护起来成本非常高，那该如何是好，Hadoop分布式的框架，可以使用廉价的机器部署分布式系统把数据存储再HDFS之上，通过MR进行计算，分析，这样是可以的，但是，MR大家应该知道，MapReduce编程带来的不便性，编程十分繁琐，在大多情况下，每个MapReduce程序需要包含Mapper、Reduceer和一个Driver，之后需要打成jar包扔到集群上运行。如果mr写完之后，且该项目已经上线，一旦业务逻辑发生了改变，可能就会带来大规模的改动代码，然后重新打包，发布，非常麻烦(这种方式，也是最古老的方式)
当大量数据都存放在HDFS上，如何快速的对HDFS上的文件进行统计分析操作？

一般来说，想要做会有两种方式：

学Java、学MapReduce(MR程序写起来十分冗余死板不断的写map方法和reduce方法)
做DBA的：写SQL(希望能通过写SQL这样的方式来实现，这种方式较好)

然而，HDFS中最关键的一点就是，数据存储HDFS上是没有schema的概念的(schema:相当于表里面有列、字段、字段名称、字段与字段之间的分隔符等，这些就是schema信息)然而HDFS上的仅仅只是一个纯的文本文件而已，那么，没有schema，就没办法使用sql进行查询了啊。
因此，在这种背景下，就有问题产生：如何为HDFS上的文件添加Schema信息？如果加上去，是否就可以通过SQL的方式进行处理了呢？于是强大的Hive出现了。

Hive深入剖析

再来看看 Hive官网地址给我们的介绍：
官方第一句话就说明了Apache Hive 是构建在Apache Hadoop之上的数据仓库。有助于对大型的数据集进行读、写和管理。
那我们先对这句话进行剖析：
首先Hive是构建在Hadoop之上的，其实就是Hive中的数据其实是存储在HDFS上的（加上LOCAL关键字则是在本地），默认在/user/hive/warehouse/table,有助于对大型数据集进行读、写和管理，那也就是意味着传统的关系型数据库已经无法满足现在的数据量了，需要一个更大的仓库来帮助我们存储，这里也引出一个问题：Hive和关系型数据库的区别，后面我们再来聊。

Hive的特征

1.可通过SQL轻松访问数据的工具，从而实现数据仓库任务，如提取/转换/加载（ETL），报告和数据分析。
2.它可以使已经存储的数据结构化
3.可以直接访问存储在Apache HDFS™或其他数据存储系统（如Apache HBase™）中的文件
4.Hive除了支持MapReduce计算引擎，还支持Spark和Tez这两中分布式计算引擎（这里会引申出一个问题，哪些查询跑mr哪些不跑？）
5.它提供类似sql的查询语句HiveQL对数据进行分析处理。
6.数据的存储格式有多种，比如数据源是二进制格式，普通文本格式等等

而hive强大之处不要求数据转换成特定的格式，而是利用hadoop本身InputFormat API来从不同的数据源读取数据，同样地使用OutputFormat API将数据写成不同的格式。所以对于不同的数据源，或者写出不同的格式就需要不同的对应的InputFormat和Outputformat类的实现。

1.以stored as textfile为例，其在底层java API中表现是输入InputFormat格式:TextInputFormat以及输出OutputFormat格式:HiveIgnoreKeyTextOutputFormat.这里InputFormat中定义了如何对数据源文本进行读取划分，以及如何将切片分割成记录存入表中。而Outputformat定义了如何将这些切片写回到文件里或者直接在控制台输出。

2.不仅如此，Hive的SQL还可以通过用户定义的函数（UDF），用户定义的聚合（UDAF）和用户定义的表函数（UDTF）进行扩展。（注意理解几个函数之间的区别）

3.Hive中不仅可以使用逗号和制表符分隔值（CSV / TSV）文本文件，还可以使用Sequence File、RC、ORC、Parquet （知道这几种存储格式的区别），

4.当然Hive还可以通过用户来自定义自己的存储格式，基本上前面说的到的几种格式完全够了。

5.Hive旨在最大限度地提高可伸缩性（通过向Hadoop集群动态添加更多机器扩展），性能，可扩展性，

6.容错性以及与其输入格式的松散耦合。

Hive的数据存储结构

在这里插入图片描述

Database:Hive中包含了多个数据库，默认的数据库为default，对应于HDFS目录是/user/hadoop/hive/warehouse，可以通过hive.metastore.warehouse.dir参数进行配置（hive-site.xml中配置）
Table: Hive 中的表又分为内部表和外部表 ,Hive 中的每张表对应于HDFS上的一个目录，HDFS目录为：/user/hadoop/hive/warehouse/[databasename.db]/table 。
Partition:分区，每张表中可以加入一个分区或者多个，方便查询，提高效率；并且HDFS上会有对应的分区目录：
/user/hadoop/hive/warehouse/[databasename.db]/table（下面会详细介绍）
Bucket：把表(或者分区)组织成桶的结构。后续我们会详细说这样做的好处。

接下来，我们重点讲一讲Hive中常见的基本语法：

DDL(Data Definition Language)

HiveQL语句自身采用的也是SQL语句的语法，所以在查看官网描述DDL的时候你会发现其实大部分和我们所学到的SQL语句相同。这里我就Hive中DDL 比较重要的区别于MySQL的知识点叙述一下，更加详细的知识点可到官网查阅。

1.创建表

下面是官网上为我们列出的语法：

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name   
 -- (Note: TEMPORARY available in Hive 0.14.0 and later)
  [(col_name data_type [COMMENT col_comment], ... [constraint_specification])]
  [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]
  [SKEWED BY (col_name, col_name, ...)                  -- (Note: Available in Hive 0.10.0 and later)]
     ON ((col_value, col_value, ...), (col_value, col_value, ...), ...)
     [STORED AS DIRECTORIES]
  [
   [ROW FORMAT row_format] 
   [STORED AS file_format]
     | STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (...)] 
      -- (Note: Available in Hive 0.6.0 and later)
  ]

  [LOCATION hdfs_path]
  [TBLPROPERTIES (property_name=property_value, ...)]   
  -- (Note: Available in Hive 0.6.0 and later)
  [AS select_statement];
 -- (Note: Available in Hive 0.5.0 and later; not supported for external tables)

初次一看着实会吓一跳，这么大一串。别着急下面我们来逐次进行分析：

1.TEMPORARY（临时表）

Hive从0.14.0开始提供创建临时表的功能，表只对当前session有效，session退出后，表自动删除。
语法：
CREATE TEMPORARY TABLE …

注意点：

如果创建的临时表表名已存在，那么当前session引用到该表名时实际用的是临时表，只有drop或rename临时表名才能使用原始表；
临时表限制：不支持分区字段和创建索引。

2 EXTERNAL（外部表）

Hive上有两种类型的表，一种是Managed Table(称为内部表，默认的)，另一种是External Table（称为外部表，加上EXTERNAL关键字）。

Hive中内部表和外部表的区别(高频面试考点)：

内部表数据由Hive自身管理，外部表数据由HDFS管理；
内部表数据存储的位置是hive.metastore.warehouse.dir（默认：/user/hive/warehouse），外部表数据的存储位置由自己制定；
删除内部表会直接删除元数据（metadata）及存储数据；删除外部表仅仅会删除元数据，HDFS上的文件并不会被删除；

如下示例：

hive> create table managed_table(
    > id int,
    > name string 
    > );

加External

hive> create external table external_table(
    > id int,
    >  name string 
    > );

查询HDFS上的数据

[hadoop@zydatahadoop001 ~]$ hdfs dfs -ls /user/hive/warehouse
Found 7 items
drwxr-xr-x   - hadoop supergroup          0 2017-12-23 00:21 /user/hive/warehouse/external_table
drwxr-xr-x   - hadoop supergroup          0 2017-12-22 14:42 /user/hive/warehouse/helloword
drwxr-xr-x   - hadoop supergroup          0 2017-12-22 17:28 /user/hive/warehouse/hive1.db
drwxr-xr-x   - hadoop supergroup          0 2017-12-22 17:40 /user/hive/warehouse/hive2.db
drwxr-xr-x   - hadoop supergroup          0 2017-12-23 00:06 /user/hive/warehouse/managed_table
drwxr-xr-x   - hadoop supergroup          0 2017-12-22 14:58 /user/hive/warehouse/word.db
drwxr-xr-x   - hadoop supergroup          0 2017-12-22 15:34 /user/hive/warehouse/wordcount.db

上述可以看到，external_table和managed_table存在。

查询MySQL上的数据

mysql> select * from tbls \G;

*************************** 4. row ***************************
            TBL_ID: 11
       CREATE_TIME: 1513958794
             DB_ID: 1
  LAST_ACCESS_TIME: 0
             OWNER: hadoop
         RETENTION: 0
             SD_ID: 11
          TBL_NAME: managed_table
          TBL_TYPE: MANAGED_TABLE
VIEW_EXPANDED_TEXT: NULL
VIEW_ORIGINAL_TEXT: NULL
*************************** 5. row ***************************
            TBL_ID: 13
       CREATE_TIME: 1513959668
             DB_ID: 1
  LAST_ACCESS_TIME: 0
             OWNER: hadoop
         RETENTION: 0
             SD_ID: 13
          TBL_NAME: external_table
          TBL_TYPE: EXTERNAL_TABLE
VIEW_EXPANDED_TEXT: NULL
VIEW_ORIGINAL_TEXT: NULL

两张表都存在，并且类型不同。

删除表

hive> drop table  managed_table;
OK
Time taken: 0.807 seconds
hive> drop table external_table;
OK

删除后查询HDFS上的数据

[hadoop@zydatahadoop001 ~]$ hdfs dfs -ls /user/hive/warehouse
Found 6 items
drwxr-xr-x   - hadoop supergroup          0 2017-12-23 00:21 /user/hive/warehouse/external_table
drwxr-xr-x   - hadoop supergroup          0 2017-12-22 14:42 /user/hive/warehouse/helloword
drwxr-xr-x   - hadoop supergroup          0 2017-12-22 17:28 /user/hive/warehouse/hive1.db
drwxr-xr-x   - hadoop supergroup          0 2017-12-22 17:40 /user/hive/warehouse/hive2.db
drwxr-xr-x   - hadoop supergroup          0 2017-12-22 14:58 /user/hive/warehouse/word.db
drwxr-xr-x   - hadoop supergroup          0 2017-12-22 15:34 /user/hive/warehouse/wordcount.db

上述可以发现，managed_table已经不存在和而external_table还存在。。。

删除后查询MySQL上的信息
mysql> select * from tbls \G;
发现两张标的信息已经不存在了。

3 [(col_name data_type [COMMENT col_comment], … [constraint_specification])] [COMMENT table_comment]
col_name：字段名；
data_type：字段类型；
COMMENT col_comment：字段的注释；
[COMMENT table_comment]：表的注释。
下面我们创建一张表并查询注解：

hive> create table student(
    > id int comment '学号',
    > name string comment '姓'
    > )
    > comment 'this is student information table';
OK

4 PARTITIONED BY（分区）

产生背景：如果一个表中数据很多，我们查询时就很慢，耗费大量时间，如果要查询其中部分数据该怎么办呢，这是我们引入分区的概念。

Hive 中的分区表分为两种：静态分区和动态分区

（1）静态分区:
可以根据PARTITIONED BY创建分区表，一个表可以拥有一个或者多个分区，每个分区以文件夹的形式单独存在表文件夹的目录下；
分区是以字段的形式在表结构中存在，通过describe table命令可以查看到字段存在，但是该字段不存放实际的数据内容，仅仅是分区的表示。
分区建表分为2种，一种是单分区，也就是说在表文件夹目录下只有一级文件夹目录。另外一种是多分区，表文件夹下出现多文件夹嵌套模式。

1.单分区：

hive> CREATE TABLE order_partition (
    > order_number string,
    > event_time string
    > )
    > PARTITIONED BY (event_month string);
OK

将order.txt 文件中的数据加载到order_partition表中

hive> load data local inpath '/home/hadoop/order.txt' overwrite into table order_partition partition (event_month='2014-05');

hive> select * from order_partition;

10703007267488  2014-05-01 06:01:12.334+01      2014-05
10101043505096  2014-05-01 07:28:12.342+01      2014-05
10103043509747  2014-05-01 07:50:12.33+01       2014-05
10103043501575  2014-05-01 09:27:12.33+01       2014-05
10104043514061  2014-05-01 09:03:12.324+01      2014-05

注意：使用hadoop shell 加载数据也能加载数据，下面进行演示：

创建分区，也就是说在HDFS文件夹目录下会有一个分区目录，那么我们是不是直接可以在HDFS上创建一个目录，再把数据加载进去呢？

[hadoop@zydatahadoop001 ~]$ hadoop fs -mkdir -p /user/hive/warehouse/order_partition/event_month=2014-06
[hadoop@zydatahadoop001 ~]$ hadoop fs -put /home/hadoop/order.txt /user/hive/warehouse/order_partition/event_month=2014-06

上传完成后查看表order_partition
hive> select * from order_partition
    > ;
OK
10703007267488  2014-05-01 06:01:12.334+01      2014-05
10101043505096  2014-05-01 07:28:12.342+01      2014-05
10103043509747  2014-05-01 07:50:12.33+01       2014-05
10103043501575  2014-05-01 09:27:12.33+01       2014-05
10104043514061  2014-05-01 09:03:12.324+01      2014-05
Time taken: 2.034 seconds, Fetched: 5 row(s)

可以看到并没有看到我们刚刚通过hdfs上传后的数据，原因是我们将文件上传到了hdfs，hdfs是有了数据，但hive中的元数据中还没有，执行如下命令更新

msck repair table order_partition;

再次查看数据
hive> select * from order_partition;
OK
10703007267488  2014-05-01 06:01:12.334+01      2014-05
10101043505096  2014-05-01 07:28:12.342+01      2014-05
10103043509747  2014-05-01 07:50:12.33+01       2014-05
10103043501575  2014-05-01 09:27:12.33+01       2014-05
10104043514061  2014-05-01 09:03:12.324+01      2014-05
10703007267488  2014-05-01 06:01:12.334+01      2014-06
10101043505096  2014-05-01 07:28:12.342+01      2014-06
10103043509747  2014-05-01 07:50:12.33+01       2014-06
10103043501575  2014-05-01 09:27:12.33+01       2014-06
10104043514061  2014-05-01 09:03:12.324+01      2014-06

2.多分区：

hive>  CREATE TABLE order_partition2 (
    > order_number string,
    > event_time string
    > )
    >  PARTITIONED BY (event_month string, step string);
OK

加载数据:
hive> load data local inpath '/home/hadoop/order.txt' overwrite into table order_multi_partition partition (event_month='2014-05',step=1);

查询：
hive> select * from order_multi_partition;
OK
10703007267488  2014-05-01 06:01:12.334+01      2014-05 1
10101043505096  2014-05-01 07:28:12.342+01      2014-05 1
10103043509747  2014-05-01 07:50:12.33+01       2014-05 1
10103043501575  2014-05-01 09:27:12.33+01       2014-05 1
10104043514061  2014-05-01 09:03:12.324+01      2014-05 1
Time taken: 0.228 seconds, Fetched: 5 row(s)

在HDFS上查询文件结构：

[hadoop@zydatahadoop001 ~]$ hdfs dfs -ls /user/hive/warehouse/order_multi_partition/event_month=2014-05
18/01/09 
Found 1 items
drwxr-xr-x   - hadoop supergroup          0 2018-01-09 22:52 /user/hive/warehouse/order_multi_partition/event_month=2014-05/step=1

单级分区在HDFS上文件目录为单级；多分区在HDFS上文件目录为多级

（2）动态分区
先看看官方为我们解释的什么是动态分区：
Static Partition (SP) columns：静态分区；
Dynamic Partition (DP) columns 动态分区。

DP columns are specified the same way as it is for SP columns – in the partition clause. The only difference is that DP columns do not have values, while SP columns do. In the partition clause, we need to specify all partitioning columns, even if all of them are DP columns.
In INSERT … SELECT … queries, the dynamic partition columns must be specified last among the columns in the SELECT statement and in the same order in which they appear in the PARTITION() clause.

一看到English可能让大家头疼，哈哈，下面为大家解释这段话的意思：

DP列的指定方式与SP列相同 - 在分区子句中（ Partition关键字后面），唯一的区别是，DP列没有值，而SP列有值（ Partition关键字后面只有key没有value）；
在INSERT … SELECT …查询中，必须在SELECT语句中的列中最后指定动态分区列，并按PARTITION（）子句中出现的顺序进行排列；
所有DP列 - 只允许在非严格模式下使用。在严格模式下，我们应该抛出一个错误。
如果动态分区和静态分区一起使用，必须是动态分区的字段在前，静态分区的字段在后。

下面举几个例子进行演示：

演示前先进行设置：hive 中默认是静态分区，想要使用动态分区，需要设置如下参数，可以使用临时设置，你也可以写在配置文件（hive-site.xml）里，永久生效。临时配置如下
开启动态分区（默认为false，不开启）

set hive.exec.dynamic.partition=true;  （开启动态分区）
set hive.exec.dynamic.partition.mode=nonstrict;
（指定动态分区模式，默认为strict，即必须指定至少一个分区为静态分区，nonstrict模式表示允许所有的分区字段都可以使用动态分区。）

创建员工的动态分区表，分区字段为deptno；

CREATE TABLE emp_dynamic_partition (
empno int,
ename string,
job string,
mgr int,
hiredate string,
salary double,
comm double
)
PARTITIONED BY (deptno int)
ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t";

如果使用静态分区，根据deptno要写多条语句如下：

CREATE TABLE emp_partition (
empno int,
ename string,
job string,
mgr int,
hiredate string,
salary double,
comm double
)
PARTITIONED BY (deptno int)
ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t";


insert into table emp_partition partition(deptno=10)
select empno,ename ,job ,mgr ,hiredate ,salary ,comm from emp where deptno=10;

insert into table emp_partition partition(deptno=20)
select empno,ename ,job ,mgr ,hiredate ,salary ,comm from emp where deptno=20;

insert into table emp_partition partition(deptno=30)
select empno,ename ,job ,mgr ,hiredate ,salary ,comm from emp where deptno=30;

查询结果：
hive> select * from emp_partition;
OK
7782    CLARK   MANAGER 7839    1981/6/9        2450.0  NULL    10
7839    KING    PRESIDENT       NULL    1981/11/17      5000.0  NULL    10
7934    MILLER  CLERK   7782    1982/1/23       1300.0  NULL    10
7369    SMITH   CLERK   7902    1980/12/17      800.0   NULL    20
7566    JONES   MANAGER 7839    1981/4/2        2975.0  NULL    20
7788    SCOTT   ANALYST 7566    1987/4/19       3000.0  NULL    20
7876    ADAMS   CLERK   7788    1987/5/23       1100.0  NULL    20
7902    FORD    ANALYST 7566    1981/12/3       3000.0  NULL    20
7499    ALLEN   SALESMAN        7698    1981/2/20       1600.0  300.0   30
7521    WARD    SALESMAN        7698    1981/2/22       1250.0  500.0   30
7654    MARTIN  SALESMAN        7698    1981/9/28       1250.0  1400.0  30
7698    BLAKE   MANAGER 7839    1981/5/1        2850.0  NULL    30
7844    TURNER  SALESMAN        7698    1981/9/8        1500.0  0.0     30
7900    JAMES   CLERK   7698    1981/12/3       950.0   NULL    30

我们再来看看使用动态分区的效果并注意他的基本语法与静态分区的区别：

insert into table emp_dynamic_partition partition(deptno)
select empno,ename ,job ,mgr ,hiredate ,salary ,comm, deptno from emp;
一条语句完成
hive> select * from emp_dynamic_partition;
OK
7782    CLARK   MANAGER 7839    1981/6/9        2450.0  NULL    10
7839    KING    PRESIDENT       NULL    1981/11/17      5000.0  NULL    10
7934    MILLER  CLERK   7782    1982/1/23       1300.0  NULL    10
7369    SMITH   CLERK   7902    1980/12/17      800.0   NULL    20
7566    JONES   MANAGER 7839    1981/4/2        2975.0  NULL    20
7788    SCOTT   ANALYST 7566    1987/4/19       3000.0  NULL    20
7876    ADAMS   CLERK   7788    1987/5/23       1100.0  NULL    20
7902    FORD    ANALYST 7566    1981/12/3       3000.0  NULL    20
7499    ALLEN   SALESMAN        7698    1981/2/20       1600.0  300.0   30
7521    WARD    SALESMAN        7698    1981/2/22       1250.0  500.0   30
7654    MARTIN  SALESMAN        7698    1981/9/28       1250.0  1400.0  30
7698    BLAKE   MANAGER 7839    1981/5/1        2850.0  NULL    30
7844    TURNER  SALESMAN        7698    1981/9/8        1500.0  0.0     30
7900    JAMES   CLERK   7698    1981/12/3       950.0   NULL    30

查看HDFS上文件目录结构

[hadoop@zydatahadoop001 ~]$ hdfs dfs -ls /user/hive/warehouse
Found 5 items
drwxr-xr-x   - hadoop supergroup          0 2018-01-09 20:30 /user/hive/warehouse/emp
drwxr-xr-x   - hadoop supergroup          0 2018-01-10 00:38 /user/hive/warehouse/emp_dynamic_partition
drwxr-xr-x   - hadoop supergroup          0 2018-01-10 00:34 /user/hive/warehouse/emp_partition
drwxr-xr-x   - hadoop supergroup          0 2018-01-09 22:52 /user/hive/warehouse/order_multi_partition
drwxr-xr-x   - hadoop supergroup          0 2018-01-09 22:42 /user/hive/warehouse/order_partition

mixed SP & DP columns（混合使用动态分区和静态分区）

create table student(
id int,
name string,
tel string,
age int
)
row format delimited fields terminated by '\t';

insert into student values(1,'zhangsan','18310982765',20),(2,'lisi','18282823434',30),(3,'wangwu','1575757668',40);

创建分区表stu_mixed_partition

create table stu_age_partition(
id int,
name string,
tel string
)
partitioned by (ds string,age int)
row format delimited fields terminated by '\t';

insert into stu_age_partition partition(ds='2010-03-03',age)
select id,name,tel,age from student;

结果：
hive> select * from stu_age_partition;
OK
1       zhangsan        18310982765     2010-03-03      20
2       lisi    18282823434     2010-03-03      30
3       wangwu  1575757668      2010-03-03      40
Time taken: 0.149 seconds, Fetched: 3 row(s)

查看HDFS上的目录结构：
[hadoop@zydatahadoop001 data]$ hdfs dfs -ls /user/hive/warehouse/stu_age_partition/ds=2010-03-03

drwxr-xr-x   - hadoop supergroup          0 2018-01-10 01:04 /user/hive/warehouse/stu_age_partition/ds=2010-03-03/age=20
drwxr-xr-x   - hadoop supergroup          0 2018-01-10 01:04 /user/hive/warehouse/stu_age_partition/ds=2010-03-03/age=30
drwxr-xr-x   - hadoop supergroup          0 2018-01-10 01:04 /user/hive/warehouse/stu_age_partition/ds=2010-03-03/age=40

5 ROW FORMAT(行格式)

先看看官网对于ROW FORMAT是怎样描述的呢?

 : DELIMITED 
[FIELDS TERMINATED BY char [ESCAPED BY char]]       [COLLECTION ITEMS TERMINATED BY char]
[MAP KEYS TERMINATED BY char] 
[LINES TERMINATED BY char]
[NULL DEFINED AS char]   
-- (Note: Available in Hive 0.13 and later)
  | SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]

先看看官网给我们的解释：用户在建表的时候可以自定义 SerDe 或者使用自带的 SerDe。如果没有指定 ROW FORMAT 或者 ROW FORMAT DELIMITED，将会使用自带的 SerDe。在建表的时候，用户还需要为表指定列，用户在指定表的列的同时也会指定自定义的 SerDe，Hive 通过 SerDe 确定表的具体的列的数据。
（这里我们可能又有疑惑了SerDe到底是什么，官网给出的解释，有兴趣的同学可以看看。）

那么问题又来了上面这句话又是什么意思呢？
让我们来一起看看到底是神马东东：

DELIMITED：分隔符（可以自定义分隔符）；
FIELDS TERMINATED BY char:每个字段之间使用的分割；
例：-FIELDS TERMINATED BY ‘\n’ 字段之间的分隔符为\n;
COLLECTION ITEMS TERMINATED BY char:集合中元素与元素（array）之间使用的分隔符（collection单例集合的跟接口）；
MAP KEYS TERMINATED BY char：字段是K-V形式指定的分隔符；
LINES TERMINATED BY char：每条数据之间由换行符分割（默认[ \n ]）。

一般情况下LINES TERMINATED BY char我们就使用默认的换行符\n，只需要指定FIELDS TERMINATED BY char。

创建demo1表，字段与字段之间使用\t分开，换行符使用默认\n：
hive> create table demo1(
    > id int,
    > name string
    > )
    > ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
OK

创建demo2表，并指定其他字段：
hive> create table demo2 (
    > id int,
    > name string,
    > hobbies ARRAY <string>,
    > address MAP <string, string>
    > )
    > ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
    > COLLECTION ITEMS TERMINATED BY '-'
    > MAP KEYS TERMINATED BY ':';
OK

6 STORED AS（存储格式）

例如，我们把在Hive中创建的数据表加载到指定的目录中。

hive>  CREATE EXTERNAL TABLE dbtaobao.user_log(user_id INT,item_id INT,cat_id INT,merchant_id INT,brand_id INT,month STRING,day STRING,action INT,age_range INT,gender INT,province STRING) COMMENT 'Welcome to xmu dblab,Now create dbtaobao.user_log!' ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' STORED AS TEXTFILE LOCATION '/dbtaobao/dataset/user_log';

7 Create Table As Select (CTAS)

创建表（拷贝表结构及数据，并且会运行MapReduce作业）
1 .复制整张表：

hive> create table emp2 as select * from emp;
运行的mr：
Query ID = hadoop_20171223093737_43cbeae2-654d-4832-ad5f-13a53732af34
Total jobs = 3
Launching Job 1 out of 3
Number of reduce tasks is set to 0 since there's no reduce operator

 select * from emp2;
OK
7369    SMITH   CLERK   7902    1980/12/17      800.0   NULL    20
7499    ALLEN   SALESMAN        7698    1981/2/20       1600.0  300.0   30
7521    WARD    SALESMAN        7698    1981/2/22       1250.0  500.0   30
7566    JONES   MANAGER 7839    1981/4/2        2975.0  NULL    20
7654    MARTIN  SALESMAN        7698    1981/9/28       1250.0  1400.0  30
7698    BLAKE   MANAGER 7839    1981/5/1        2850.0  NULL    30
7782    CLARK   MANAGER 7839    1981/6/9        2450.0  NULL    10
7788    SCOTT   ANALYST 7566    1987/4/19       3000.0  NULL    20
7839    KING    PRESIDENT       NULL    1981/11/17      5000.0  NULL    10
7844    TURNER  SALESMAN        7698    1981/9/8        1500.0  0.0     30
7876    ADAMS   CLERK   7788    1987/5/23       1100.0  NULL    20
7900    JAMES   CLERK   7698    1981/12/3       950.0   NULL    30
7902    FORD    ANALYST 7566    1981/12/3       3000.0  NULL    20
7934    MILLER  CLERK   7782    1982/1/23       1300.0  NULL    10
Time taken: 0.229 seconds, Fetched: 14 row(s)

2.复制表中的一些字段：

hive> create table emp3 as select empno,ename from emp;

hive> select * from emp3;
OK
7369    SMITH
7499    ALLEN
7521    WARD
7566    JONES
7654    MARTIN
7698    BLAKE
7782    CLARK
7788    SCOTT
7839    KING
7844    TURNER
7876    ADAMS
7900    JAMES
7902    FORD
7934    MILLER
Time taken: 0.295 seconds, Fetched: 14 row(s)

8 LIKE建表

使用like创建表时，只会复制表的结构，不会复制表的数据。

hive> CREATE TABLE emp (
    > empno int,
    > ename string,
    > job string,
    > mgr int,
    > hiredate string,
    > salary double,
    > comm double,
    > deptno int
    > ) ROW FORMAT DELIMITED FIELDS TERMINATED BY "\n";
OK
Time taken: 0.54 seconds

hive> create table emp1 like emp;
OK

hive> select * from emp1;
OK
查询结果可以看出没有数据

9 Skewed Tables

10 desc formatted table_name

查询表的详细信息

hive> desc formatted emp;
OK
# col_name              data_type               comment             

empno                   int                                         
ename                   string                                      
job                     string                                      
mgr                     int                                         
hiredate                string                                      
salary                  double                                      
comm                    double                                      
deptno                  int                                         

# Detailed Table Information             
Database:               default                  
Owner:                  hadoop                   
CreateTime:             Sat Dec 23 09:39:57 CST 2017     
LastAccessTime:         UNKNOWN                  
Protect Mode:           None                     
Retention:              0                        
Location:               hdfs://192.168.137.200:9000/user/hive/warehouse/emp      
Table Type:             MANAGED_TABLE            
Table Parameters:                
        COLUMN_STATS_ACCURATE   true                
        numFiles                1                   
        numRows                 0                   
        rawDataSize             0                   
        totalSize               671                 
        transient_lastDdlTime   1513993210          

# Storage Information            
SerDe Library:          org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe       
InputFormat:            org.apache.hadoop.mapred.TextInputFormat         
OutputFormat:           org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat       
Compressed:             No                       
Num Buckets:            -1                       
Bucket Columns:         []                       
Sort Columns:           []                       
Storage Desc Params:             
        field.delim             \t                  
        serialization.format    \t                  
Time taken: 0.364 seconds, Fetched: 39 row(s)

通过查询可以列出创建表时的所有信息，并且我们可以在mysql中查询出这些信息（元数据）select * from table_params;

11 查询创建表的语法

hive> show create table emp;
OK
CREATE TABLE `emp`(
  `empno` int, 
  `ename` string, 
  `job` string, 
  `mgr` int, 
  `hiredate` string, 
  `salary` double, 
  `comm` double, 
  `deptno` int)
ROW FORMAT DELIMITED 
  FIELDS TERMINATED BY '\t' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  'hdfs://192.168.137.200:9000/user/hive/warehouse/emp'
TBLPROPERTIES (
  'COLUMN_STATS_ACCURATE'='true', 
  'numFiles'='1', 
  'numRows'='0', 
  'rawDataSize'='0', 
  'totalSize'='671', 
  'transient_lastDdlTime'='1513993210')
Time taken: 0.393 seconds, Fetched: 24 row(s)

修改表（Alter Table）

[2020.1.18 更新：]
在这里插入图片描述

修改分区（Alter Partition）

1 Add Partitions（添加分区）

ALTER TABLE table_name ADD [IF NOT EXISTS] PARTITION partition_spec [LOCATION 'location'][, PARTITION partition_spec [LOCATION 'location'], ...];

partition_spec:
  : (partition_column = partition_col_value, partition_column = partition_col_value, ...)

用户可以用 ALTER TABLE ADD PARTITION 来向一个表中增加分区。当分区名是字符串时加引号。
注：添加分区时可能出现FAILED: SemanticException table is not partitioned but partition spec exists错误。
原因是，你在创建表时并没有添加分区，需要在创建表时创建分区，再添加分区。

创建dept表

hive>  create table dept(
    >  deptno int,
    > dname string,
    > loc string
    > )
    > PARTITIONED BY (dt string)
    >  ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t";
OK
Time taken: 0.953 seconds

加载数据

hive> load data local inpath '/home/hadoop/dept.txt'into table dept partition (dt='2018-08-08');
Loading data to table default.dept partition (dt=2018-08-08)
Partition default.dept{dt=2018-08-08} stats: [numFiles=1, numRows=0, totalSize=84, rawDataSize=0]
OK
Time taken: 5.147 seconds

查询结果
hive> select * from dept;
OK
10      ACCOUNTING      NEW YORK        2018-08-08
20      RESEARCH        DALLAS  2018-08-08
30      SALES   CHICAGO 2018-08-08
40      OPERATIONS      BOSTON  2018-08-08
Time taken: 0.481 seconds, Fetched: 4 row(s)

添加分区

hive> ALTER TABLE dept ADD PARTITION (dt='2018-09-09');
OK

2 删除分区（Drop Partitions）

下面是官方语法：
ALTER TABLE table_name DROP [IF EXISTS] PARTITION partition_spec[, PARTITION partition_spec, …]

hive> ALTER TABLE dept DROP PARTITION (dt=‘2018-09-09’);

3 基于分区的查询的语句

hive> select * from dept where dt='2018-08-08';
OK
10      ACCOUNTING      NEW YORK        2018-08-08
20      RESEARCH        DALLAS  2018-08-08
30      SALES   CHICAGO 2018-08-08
40      OPERATIONS      BOSTON  2018-08-08
Time taken: 2.323 seconds, Fetched: 4 row(s)

4 查看分区语句

hive> show partitions dept;
OK
dt=2018-08-08
dt=2018-09-09
Time taken: 0.385 seconds, Fetched: 2 row(s)

桶

[2020.1.18 更新]
Hive把表组织成分区(partition)。这是一分区列(partittion column)的值对表进行粗略划分的机制。使用分区可以加速数据分片(slice)的查询速度。关于分区的具体知识，我在上文中讲过了。这里，我们重点讲一下桶(bucket)。
在这里插入图片描述

DML(Data Manipulation Language)

同样，首先给出官网地址：Hive DML

LOAD（加载数据）

加载文件到表中（Loading files into tables）
-下面是官网上为我们列出的语法：
LOAD DATA [LOCAL] INPATH ‘filepath’ [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 …)]

加载数据到表中时，Hive不做任何转换。加载操作只是把数据拷贝或移动操作，即移动数据文件到Hive表相应的位置。
加载的目标可以是一个表，也可以是一个分区。如果表是分区的，则必须通过指定所有分区列的值来指定一个表的分区。
filepath可以是一个文件，也可以是一个目录。不管什么情况下，filepath被认为是一个文件集合。
以下是对于括号中的可选项：

LOCAL：表示输入文件在本地文件系统（Linux），如果没有加LOCAL，hive则会去HDFS上查找该文件。
OVERWRITE：表示如果表中有数据，则先删除数据，再插入新数据，如果没有这个关键词，则直接附加数据到表中。
PARTITION：如果表中存在分区，可以按照分区进行导入。

1.创建一张员工表 ：

hive> CREATE TABLE emp (
    > empno int,
    > ename string,
    > job string,
    > mgr int,
    > hiredate string,
    > salary double,
    > comm double,
    > deptno int
    > ) ROW FORMAT DELIMITED FIELDS TERMINATED BY "\n";
OK
Time taken: 0.54 seconds

2.把本地文件系统中emp.txt导入：

 LOAD DATA LOCAL INPATH '/home/hadoop/emp.txt' OVERWRITE INTO TABLE emp;

hive> select * from emp;
OK
7369    SMITH   CLERK   7902    1980/12/17      800.0   NULL    20
7499    ALLEN   SALESMAN        7698    1981/2/20       1600.0  300.0   30
7521    WARD    SALESMAN        7698    1981/2/22       1250.0  500.0   30
7566    JONES   MANAGER 7839    1981/4/2        2975.0  NULL    20
7654    MARTIN  SALESMAN        7698    1981/9/28       1250.0  1400.0  30
7698    BLAKE   MANAGER 7839    1981/5/1        2850.0  NULL    30
7782    CLARK   MANAGER 7839    1981/6/9        2450.0  NULL    10
7788    SCOTT   ANALYST 7566    1987/4/19       3000.0  NULL    20
7839    KING    PRESIDENT       NULL    1981/11/17      5000.0  NULL    10
7844    TURNER  SALESMAN        7698    1981/9/8        1500.0  0.0     30
7876    ADAMS   CLERK   7788    1987/5/23       1100.0  NULL    20
7900    JAMES   CLERK   7698    1981/12/3       950.0   NULL    30
7902    FORD    ANALYST 7566    1981/12/3       3000.0  NULL    20
7934    MILLER  CLERK   7782    1982/1/23       1300.0  NULL    10
Time taken: 0.938 seconds, Fetched: 14 row(s)

使用分区加载数据（PARTITION）

hive> load data local inpath '/home/hadoop/dept.txt' into table dept partition (dt='2018-09-09');
Loading data to table default.dept partition (dt=2018-09-09)
Partition default.dept{dt=2018-09-09} stats: [numFiles=1, totalSize=84]
OK
Time taken: 10.631 seconds
hive> select * form dept;
FAILED: ParseException line 1:9 missing EOF at 'form' near '*'
hive> select * from dept;
OK
10      ACCOUNTING      NEW YORK        2018-08-08
20      RESEARCH        DALLAS  2018-08-08
30      SALES           CHICAGO 2018-08-08
40      OPERATIONS      BOSTON  2018-08-08
10      ACCOUNTING      NEW YORK        2018-09-09
20      RESEARCH        DALLAS  2018-09-09
30      SALES           CHICAGO 2018-09-09
40      OPERATIONS      BOSTON  2018-09-09
Time taken: 1.385 seconds, Fetched: 8 row(s)

Inserting into （插入数据）

INSERT语句

下面是官网给出的语法

Standard syntax：
INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...) [IF NOT EXISTS]] select_statement1 FROM from_statement;

INSERT INTO TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement1 FROM from_statement;

Hive extension (multiple inserts):
FROM from_statement
INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...) [IF NOT EXISTS]] select_statement1
[INSERT OVERWRITE TABLE tablename2 [PARTITION ... [IF NOT EXISTS]] select_statement2]
[INSERT INTO TABLE tablename2 [PARTITION ...] select_statement2] ...;

官网又给我们列出一大堆语法，看着就很可怕，但是仔细整理后再来看看你会发现并没有什么，下面对其进行分析：

标准语法（Standard syntax）：INSERT OVERWRITE TABLE tablename1 select_statement1 FROM from_statement; 其实就是一个简单的插入语句。
可以使用PARTITION 关键字，进行分区插入。
OVERWRITE是否选择覆盖。
使用插入语法会跑mr作业。
multiple inserts：代表多行插入。
注：这里有两种插语法，也就是加上OVERWRITE关键字和不加的区别。

向emp1表中插入emp表中的数据

hive> insert overwrite table emp1 select * from emp;
Query ID = hadoop_20180109081212_d62e58f3-946c-465e-999d-2ddf0d76d807
Total jobs = 3
Launching Job 1 out of 3

hive> select * from emp1;
OK
7369    SMITH   CLERK   7902    1980/12/17      800.0   NULL    20
7499    ALLEN   SALESMAN        7698    1981/2/20       1600.0  300.0   30
7521    WARD    SALESMAN        7698    1981/2/22       1250.0  500.0   30
7566    JONES   MANAGER 7839    1981/4/2        2975.0  NULL    20
7654    MARTIN  SALESMAN        7698    1981/9/28       1250.0  1400.0  30
7698    BLAKE   MANAGER 7839    1981/5/1        2850.0  NULL    30
7782    CLARK   MANAGER 7839    1981/6/9        2450.0  NULL    10
7788    SCOTT   ANALYST 7566    1987/4/19       3000.0  NULL    20
7839    KING    PRESIDENT       NULL    1981/11/17      5000.0  NULL    10
7844    TURNER  SALESMAN        7698    1981/9/8        1500.0  0.0     30
7876    ADAMS   CLERK   7788    1987/5/23       1100.0  NULL    20
7900    JAMES   CLERK   7698    1981/12/3       950.0   NULL    30
7902    FORD    ANALYST 7566    1981/12/3       3000.0  NULL    20
7934    MILLER  CLERK   7782    1982/1/23       1300.0  NULL    10
Time taken: 0.211 seconds, Fetched: 14 row(s)

按字段插入数据（这里有个坑，大家要注意了！！！）

这里我把job,ename，依然可以插入数据，是不是很神奇。。。
hive> insert overwrite table emp2 select empno,job,ename,mgr,hiredate,salary,comm,deptno from emp;

这是我又向emp2表中插入了数据：
hive> insert into table emp2 select * from emp;
hive> select * from emp2;
OK
7369    CLERK   SMITH   7902    1980/12/17      800.0   NULL    20
7499    SALESMAN        ALLEN   7698    1981/2/20       1600.0  300.0   30
7521    SALESMAN        WARD    7698    1981/2/22       1250.0  500.0   30
7566    MANAGER JONES   7839    1981/4/2        2975.0  NULL    20
7654    SALESMAN        MARTIN  7698    1981/9/28       1250.0  1400.0  30
7698    MANAGER BLAKE   7839    1981/5/1        2850.0  NULL    30
7782    MANAGER CLARK   7839    1981/6/9        2450.0  NULL    10
7788    ANALYST SCOTT   7566    1987/4/19       3000.0  NULL    20
7839    PRESIDENT       KING    NULL    1981/11/17      5000.0  NULL    10
7844    SALESMAN        TURNER  7698    1981/9/8        1500.0  0.0     30
7876    CLERK   ADAMS   7788    1987/5/23       1100.0  NULL    20
7900    CLERK   JAMES   7698    1981/12/3       950.0   NULL    30
7902    ANALYST FORD    7566    1981/12/3       3000.0  NULL    20
7934    CLERK   MILLER  7782    1982/1/23       1300.0  NULL    10
7369    SMITH   CLERK   7902    1980/12/17      800.0   NULL    20
7499    ALLEN   SALESMAN        7698    1981/2/20       1600.0  300.0   30
7521    WARD    SALESMAN        7698    1981/2/22       1250.0  500.0   30
7566    JONES   MANAGER 7839    1981/4/2        2975.0  NULL    20
7654    MARTIN  SALESMAN        7698    1981/9/28       1250.0  1400.0  30
7698    BLAKE   MANAGER 7839    1981/5/1        2850.0  NULL    30
7782    CLARK   MANAGER 7839    1981/6/9        2450.0  NULL    10
7788    SCOTT   ANALYST 7566    1987/4/19       3000.0  NULL    20
7839    KING    PRESIDENT       NULL    1981/11/17      5000.0  NULL    10
7844    TURNER  SALESMAN        7698    1981/9/8        1500.0  0.0     30
7876    ADAMS   CLERK   7788    1987/5/23       1100.0  NULL    20
7900    JAMES   CLERK   7698    1981/12/3       950.0   NULL    30
7902    FORD    ANALYST 7566    1981/12/3       3000.0  NULL    20
7934    MILLER  CLERK   7782    1982/1/23       1300.0  NULL    10
Time taken: 2.363 seconds, Fetched: 28 row(s)

查询结果前14条记录job,ename是反的。。。。。

Inserting values into tables（手动插入一条或多条记录，会跑mr作业）

官方语法
INSERT INTO TABLE tablename [PARTITION (partcol1[=val1], partcol2[=val2] …)] VALUES values_row [, values_row …]

hive>  create table stu(
    > id int,
    > name string
    > )
    > ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
OK
Time taken: 0.405 seconds
hive> select * from stu;
OK

hive> insert into table stu values(1,'zhangsan'),(2,'lisi);

hive> select * from stu;
OK
1       zhangsan
2       lisi

多表插入

(2020.1.18更新：)
在这里插入图片描述

3 数据导出（Writing data into the filesystem from queries）

Standard syntax:（标准语法）
INSERT OVERWRITE [LOCAL] DIRECTORY directory1
  [ROW FORMAT row_format] [STORED AS file_format] (Note: Only available starting with Hive 0.11.0)
  SELECT ... FROM ...

Hive extension (multiple inserts):（导出多条记录）
FROM from_statement
INSERT OVERWRITE [LOCAL] DIRECTORY directory1 select_statement1
[INSERT OVERWRITE [LOCAL] DIRECTORY directory2 select_statement2] ...

row_format
  : DELIMITED [FIELDS TERMINATED BY char [ESCAPED BY char]] [COLLECTION ITEMS TERMINATED BY char]
        [MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]
        [NULL DEFINED AS char] (Note: Only available starting with Hive 0.13)

LOCAL：加上LOCAL关键字代表导入本地系统，不加默认导入HDFS；
STORED AS:可以指定存储格式。

hive> insert overwrite local directory '/home/hadoop/data' ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' 
select * from stu;

HDFS上查看结果：
[hadoop@zydatahadoop001 data]$ pwd
/home/hadoop/data

[hadoop@zydatahadoop001 data]$ cat 000000_0 
1       zhangsan
2       lisi

导出多条记录

hive> from emp
    > INSERT OVERWRITE  LOCAL DIRECTORY '/home/hadoop/tmp/hivetmp1'
    > ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t"
    > select empno, ename  
    > INSERT OVERWRITE  LOCAL DIRECTORY '/home/hadoop/tmp/hivetmp2'
    > ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t"
    > select ename;

查询结果
[hadoop@zydatahadoop001 tmp]$ pwd
/home/hadoop/tmp
[hadoop@zydatahadoop001 tmp]$ cat hivetmp1/000000_0 
7369    SMITH
7499    ALLEN
7521    WARD
7566    JONES
7654    MARTIN
7698    BLAKE
7782    CLARK
7788    SCOTT
7839    KING
7844    TURNER
7876    ADAMS
7900    JAMES
7902    FORD
7934    MILLER
[hadoop@zydatahadoop001 tmp]$ cat hivetmp2/000000_0 
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER

参考资料：
Hive总结篇及Hive的优化