HiveQL表的基本概念

最新推荐文章于 2023-03-10 11:03:20 发布

JinbaoSite

最新推荐文章于 2023-03-10 11:03:20 发布

阅读量509

点赞数

分类专栏：大数据大数据之旅文章标签： hive

本文链接：https://blog.youkuaiyun.com/JinbaoSite/article/details/80378598

版权

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

10 篇文章

订阅专栏

大数据之旅

8 篇文章

订阅专栏

1 管理表

管理表其实就是我们最常用的表，最普通的表，创建表的SQL语句与MySQL一致

CREATE TABLE IF NOT EXISTS manage_table(
  id STRING,
  name STRING
)
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY '|'
STORED AS TEXTFILE;

通常会使用IF NOT EXISTS来防止因表存在而创建失败报错的情况，这里设置字段之间的分割采用|的方式
创建管理表
通常情况下表里的数据存放在/user/hive/warehouse/DatabaseName.db目录下，DatabaseName表示当前你所在的database，而/user/hive/warehouse/这个目录是在hive-core.xml中的hive.metastore.warehouse.dir下设置的，设置的值默认为Hive数据存放地址。
创建管理表所在位置
当然你也可以自己指定这个表数据存放地址，不使用默认存放地址。

CREATE TABLE IF NOT EXISTS manage_table(
  id STRING,
  name STRING
)
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY '|'
STORED AS TEXTFILE
LOCATION '/study/hive/manage_table';

管理表其实就是内部表，一般数据只在Hive内部使用，我们对数据具有一定的控制权限，所以当我们对某个表执行删除命名时，会删除存放表信息的元数据以及这个表中的数据，不过如果你自己为这个表额外指定了存储位置，并不是删除这个位置下的数据，只会删除元数据。

2 外部表

外部表与管理表（内部表）的区别在于，Hive对外部表的数据不具有控制权限，当我们对外部表执行删除SQL语句的时候，只会删除存放表信息的元数据，不会删除这个表中的数据，创建一个外部表的SQL语法与内部表的语法基本一致，只需要创建的时候添加EXTERNAL关键字标识这是一个外部表即可。

CREATE EXTERNAL TABLE IF NOT EXISTS external_table(
  id STRING,
  name STRING
)
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY '|'
STORED AS TEXTFILE
LOCATION '/study/hive/external_table';

创建外部表
如果我们不指定LOCATION的话，外部表也会使用默认的存储地址。
管理表和外部表基本一样，不过我们可以通过查看表的具体信息查看这张表属于哪种表

DESCRIBE EXTENDED tableName;

查看表详细信息
使用上面命令可查看表的详细信息，location字段的值存储的是表存放位置，tableType表明这是一张管理表还是外部表。

其实有时候管理表与外部表区别并不是很大，这是因为就算是管理表里的数据，我们也可以对表所在的位置进行访问，可以不通过Hive去访问，通过Hadoop去访问也是一样的。

3 分区表

分区表的创建只是为了防止数据量比较大的时候不利于查询，于是根据表里的某些字段进行分区，可使得查询速度更快一点，创建分区表的方式与创建管理表也是一致的，只需要使用PARTITIONED BY申明是使用哪些分区进行分区即可

CREATE TABLE IF NOT EXISTS partition_table(
  id STRING,
  name STRING
)
PARTITIONED BY (year INT,month INT,day INT)
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY '|'
STORED AS TEXTFILE;

创建分区表
分区表中的数据存储方式其实就是为每个分区字段在表下创建一个目录，目录的名称采用的规则是分区字段名=分区值的方式。

当我们在使用查询的时候就可以使用分区值进行查询。

SELECT * FROM partition_table WHERE year=2018 AND month=5 AND day=1;
SELECT * FROM partition_table WHERE year=2018 AND month=5;

使用分区值对分区表进行查询
前两个值分别表示id和name，后面的值就是分区值，其实分区表里的数据就没有必要为分区字段存储值，该值已经存储在目录中，只需要声明其为分区字段就会自动显示出来。

由于Hive是基于MapReduce的，如果你不对表进行分区，当你对一张数据量非常大的表进行查询时会触发一个巨大的MapReduce任务，所以一般希望所有的表查询进行分区过滤，我们可以设置hive.mapred.mode=strict来限制查询必须使用分区进行过滤，设置hive.mapred.mode=nonstrict解除该限制。

set hive.mapred.mode=strict;
set hive.mapred.mode=nonstrict;

设置按分区过滤
查看分区表信息，可以查看prtitionKeys的值可以查看分区字段是哪些。
查看分区表详细信息
我们也可以直接查询一张表的所有分区

SHOW PARTITIONS partition_table;
SHOW PARTITIONS partition_table PARTITION(year=2018);

查看分区表分区信息
我们也可以直接为分区表直接增加一个分区，也可以为这个分区指定这个分区存放的地址

ALTER TABLE partition_table ADD PARTITION(year=2018,month=5,day=3);
ALTER TABLE partition_table ADD PARTITION(year=2018,month=5,day=4)
LOCATION '/study/hive/partition_table/2018/5/4';

增加分区
修改分区存储路径

ALTER TABLE partition_table PARTITION(year=2018,month=5,day=3)
SET LOCATION '/study/hive/partition_table/2018/5/3';

这个修改存储路径不是转存，而是类似于将这个分区存放位置指向另一个位置
修改分区存储路径
删除分区

ALTER TABLE partition_table DROP IF EXISTS PARTITION(year=2018,month=5,day=4);

这个删除分区会将元数据和分区下的所有数据，如果是外部表或者路径不在默认路径下，则不会删除分区下的所有数据。

4 外部分区表

外部分区表与分区表基本上具有一样的操作，创建的时候也是只需要创建的时候添加EXTERNAL关键字标识这是一个外部分区表即可

CREATE EXTERNAL TABLE IF NOT EXISTS partition_table(
  id STRING,
  name STRING
)
PARTITIONED BY (year INT,month INT,day INT)
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY '|'
STORED AS TEXTFILE;