Hive基础一（数据库，表，分区表，视图，导入导出数据）

最新推荐文章于 2023-08-01 23:25:35 发布

login_sonata

最新推荐文章于 2023-08-01 23:25:35 发布

阅读量2.7k

点赞数

分类专栏： Hive 文章标签： hive 分区表数据库导出数据大数据

本文链接：https://blog.csdn.net/login_sonata/article/details/74894685

版权

Hive 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

注意Hive版本问题，可能有些命令不适用或者漏掉了。查看Hive版本的方法是在Hive命令行中输入：

hive> set system:sun.java.command;

1，Hive的数据单元

Databases：数据库，概念等同于关系型数据库的Schema；
Tables：表，概念等同于关系型数据库的表，不多解释；
Partitions：分区，概念类似于关系型数据库的表分区，没有那么多分区类型，只支持固定分区，将同一组数据存放至一个固定的分区中。
Buckets (or Clusters)：分桶，同一个分区内的数据还可以细分，将相同的KEY再划分至一个桶中，这个有点类似于HASH分区，只不过这里是HASH分桶，也有点类似子分区吧。

2，Hive中的数据库(Database)

进入Hive命令行，执行show databases;命令，可以列出hive中的所有数据库，进入Hive-Cli之后，默认进到default数据库下。使用use databasename;可以切换到某个数据库下，同mysql。

创建数据库。 比如，创建名为lxw1234的数据库：

CREATE DATABASE IF NOT EXISTS lxw1234 
COMMENT 'lxw的大数据田地-lxw1234.com' 
localtion 'hdfs://namenode/user/lxw1234/lxw1234.db/';

创建时候可以指定数据库在HDFS上的存储位置。

修改数据库属性和所有者 ：

ALTER (DATABASE|SCHEMA) database_name 
SET DBPROPERTIES (property_name=property_value, …);

ALTER (DATABASE|SCHEMA) database_name
SET OWNER [USER|ROLE] user_or_role;

删除数据库：
```
DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];
```
默认情况下，RESTRICT关键字就是默认情况，Hive不允许删除一个里面有表存在的数据库，如果想删除数据库，要么先将数据库中的表全部删除，要么可以使用CASCADE关键字，使用该关键字后，Hive会自己将数据库下的表全部删除。

3，Hive中的表(Table)

进入Hive-Cli，使用use databasename;切换到指定数据库之后，执行show tables; 即可查看该数据库下所有的表，可以使用desc tablename;来查看表的结构，用desc formatted tablename;命令更详细，其中包括了存储路径。

内部表和外部表
Hive中的表分为内部表(MANAGED_TABLE)和外部表(EXTERNAL_TABLE)。
- 区别： 内部表DROP时候会删除HDFS上的数据; 外部表DROP时候不会删除HDFS上的数据;
- 内部表适用场景：适用于Hive中间表、结果表、以及不需要从外部（如本地文件、HDFS）load数据的情况。
- 外部表适用场景：源表，需要定期将外部数据映射到表中。
- 适用场景举例说明：每天将收集到的网站日志定期流入HDFS文本文件，一天一个目录，在Hive中建立外部表作为源表，通过添加分区的方式，将每天HDFS上的原始日志映射到外部表的天分区中，在外部表（原始日志表）的基础上做大量的统计分析，用到的中间表、结果表使用内部表存储，数据通过SELECT+INSERT进入内部表。
创建表，常用代码：
```
CREATE EXTERNAL TABLE t_lxw1234 ( 
id INT, 
ip STRING COMMENT '访问者IP', 
avg_view_depth DECIMAL(5,1), 
bounce_rate DECIMAL(6,5) 
) COMMENT 'lxw的大数据田地-lxw1234.com' 
PARTITIONED BY (day STRING) 
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY ',' 
STORED AS textfile 
LOCATION 'hdfs://cdh5/tmp/lxw1234/'; 
```
说明：
- 关键字EXTERNAL：表示该表为外部表，如果不指定EXTERNAL关键字，则表示内部表。
- 关键字COMMENT： 为表和列添加注释。
- 关键字PARTITIONED BY： 表示该表为分区表，分区字段为day,类型为string。表分区下一章单独讲。
- 关键字ROW FORMAT DELIMITED： 指定表的分隔符，通常后面要与以下关键字连用：
  - FIELDS TERMINATED BY ','：指定每行中字段分隔符为逗号。
  - LINES TERMINATED BY '\n'：指定行分隔符。
  - COLLECTION ITEMS TERMINATED BY ','：指定集合中元素之间的分隔符。
  - MAP KEYS TERMINATED BY ':'：指定数据中Map类型的Key与Value之间的分隔符，举个例子：
```
create table score(
name string,
score map< string,int>
) ROW FORMAT DELIMITED 
FIELDS TERMINATED BY '\t'
COLLECTION ITEMS TERMINATED BY ',' 
MAP KEYS TERMINATED BY ':';
```
    对应的要加载的文本数据为：
```
biansutao '数学':80,'语文':89,'英语':95 
jobs '语文':60,'数学':80,'英语':99 
```
- 关键字STORED AS： 指定表在HDFS上的文件存储格式，可选的文件存储格式有：
  - TEXTFILE：文本，默认值。
  - SEQUENCEFILE：二进制序列文件。
  - RCFILE、ORC、PARQUET：几种列式存储格式文件。
- 关键字LOCATION： 指定表在HDFS上的存储位置。

删除表
方式1，只删除表中数据：

insert overwrite table t_table1 select * from t_table1 where 1=0;

方式2，截断表（删除所有行）：

TRUNCATE TABLE table_name;
TRUNCATE TABLE table_name PARTITION (dt='20080808');

方式3，删除表（同时删元数据，删除外部表时，存储的数据不会被删除）：

DROP TABLE IF EXISTS table_name;

4，Hive中的分区表

Hive中的表分区就是将同一组数据放到同一个HDFS目录下，当查询中过滤条件指定了某一个分区值时候，只将该分区对应的目录作为Input，从而减少MapReduce的输入数据，提高查询效率。

创建分区表
```
CREATE EXTERNAL TABLE t_lxw1234 (
id INT,
ip STRING COMMENT '访问者IP',
avg_view_depth DECIMAL(5,1),
bounce_rate DECIMAL(6,5)
) COMMENT 'lxw的大数据田地-lxw1234.com'
PARTITIONED BY (month STRING, day STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS textfile;
```
说明：
在创建表时候，使用PARTITIONED BY关键字来指定该表为分区表，后面括号中指定了分区的字段和类型，分区字段可以有多个，在HDFS中对应多级目录，比如，上面的表t_lxw1234的分区month='2015-06',day='2015-06-15'对应HDFS上的路径为：
/user/hive/warehouse/default.db/t_lxw1234/month=2015-06/day=2015-06-15/
当查询中指定了month='2015-06' AND day='2015-06-15'，MapReduce直接从该目录中读取数据，如果只指定了month='2015-06'，那么MapReduce将/month=2015-06/下所有的子目录都作为Input。

添加分区

往分区中追加数据：

INSERT INTO TABLE t_lxw1234 
PARTITION (month = '2015-06',day = '2015-06-15')
SELECT * FROM dual;

覆盖分区数据：

INSERT overwrite TABLE t_lxw1234 
PARTITION (month = '2015-06',day = '2015-06-15')
SELECT * FROM dual;

使用ALTER TABLE添加分区：

ALTER TABLE t_lxw1234 
ADD PARTITION (month = '2015-06',day = '2015-06-15') 
location 'hdfs://namenode/tmp/lxw1234/month=2015-06/day=2015-06-15/';

动态分区：
关系型数据库（如Oracle）中，对分区表Insert数据时候，数据库自动会根据分区字段的值，将数据插入到相应的分区中，Hive中也提供了类似的机制，即动态分区(Dynamic Partition)，只不过，使用Hive的动态分区，需要进行相应的配置，具体可看http://lxw1234.com/archives/2015/06/286.htm
然后语句类似这样：
```
INSERT overwrite TABLE t_lxw1234_partitioned 
PARTITION (month,day) 
SELECT url,substr(day,1,7) AS month,day 
FROM t_lxw1234;
```
注意：在PARTITION (month,day)中指定分区字段名即可，同时SELECT子句的最后两个字段，必须对应前面PARTITION (month,day)中指定的分区字段，包括顺序。
查看分区表及对应的HDFS路径
使用命令 show partitions t_lxw1234; 查看对应表的所有分区。如果要查看该分区的详细信息（包括该分区在HDFS上的路径）的话，命令如下：
desc formatted t_lxw1234 partition (month = '2015-01' , day = '2015-01-25');
删除分区表
```
ALTER TABLE t_lxw1234 
DROP PARTITION (month = '2015-01', day = '2015-01-25');
```
注意：也分内部表和外部表，如果该分区表为外部表，则分区对应的HDFS目录数据不会被删除。

5，Hive视图相关

在真实业务场景下，视图的应用比较少，分区使用的非常多，因此建议对分区这块多花的时间来了解。

和关系型数据库一样，Hive中也提供了视图的功能，注意Hive中视图的特性，和关系型数据库中的稍有区别：

只有逻辑视图，没有物化视图；
视图只能查询，不能Load/Insert/Update/Delete数据；
视图在创建时候，只是保存了一份元数据，当查询视图的时候，才开始执行视图对应的那些子查询；

创建视图

CREATE VIEW IF NOT EXISTS v_lxw1234 (
url COMMENT 'url'
) COMMENT 'view lxw1234'
AS SELECT url FROM lxw1234
WHERE url LIKE 'http://%'
LIMIT 100;

删除视图
```
DROP VIEW IF EXISTS v_lxw1234;
```

修改视图

ALTER VIEW v_lxw1234 
AS SELECT url FROM lxw1234 limit 500;

6，导入导出数据

在Hive中建好表之后，需要将数据加载进来，以便做后续查询分析。

建表时直接指定HDFS路径
如果你的数据已经在HDFS上存在，已经为结构化数据，并且数据所在的HDFS路径不需要维护，那么可以直接在建表的时候使用location指定数据所在的HDFS路径即可。比如：
```
CREATE [EXTERNAL] TABLE t_lxw1234 (
day STRING,
url STRING)
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY ' ' 
stored as textfile 
location '/tmp/lxw1234/';
```
注意：这里内部表和外部表都可以指定，如果是内部表，那么在DROP该表的时候，同时会将LOCATION所指定的目录一起删除。

表已存在，从本地文件系统或者HDFS加载数据
如果数据在本地，或者HDFS的某一个目录下，需要加载到目标中或分区中，那么使用LOAD DATA命令即可加载数据：

//加载本地文件
LOAD DATA LOCAL INPATH '/home/lxw1234/t_lxw1234/'
INTO TABLE t_lxw1234 PARTITION (day = '2015-06-15');
//加载HDFS文件
LOAD DATA INPATH '/user/lxw1234/t_lxw1234/'
INTO TABLE t_lxw1234 PARTITION (day = '2015-06-15');

表已存在，从一个子查询中加载数据
这个比较简单，就是将一个查询结果插入到目标表或分区中：
```
//追加数据是把overwrite改成INTO
INSERT overwrite TABLE t_lxw1234 
PARTITION (day = '2015-06-15')
SELECT day,url from source_table;
```
注意：还支持多表插入，可以在同一个查询中使用多个insert子句，这样的好处是我们只需要扫描一遍源表就可以生成多个输出，具体做法是把from语句放在开头，例如：
```
from test1
insert into table test2
partition(age)
select id, name, tel, age
insert into table test3
select id, name
where age>25;
```
用子查询的数据进行建表
```
create table new_table
as select id, name
from old_table;
```
注意：1，如果select查询失败，新表是不会创建的。2，子查询建表不能建外部表。
导出Hive中的数据到文件系统
```
INSERT OVERWRITE LOCAL DIRECTORY '/tmp/lxw1234/' 
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY ',' 
SELECT * FROM t_lxw1234;
```
注意：如果指定了LOCAL关键字，则为导出到本地文件系统，否则，导出到HDFS。使用ROW FORMAT关键字可以指定导出的文件分隔符，默认是\001。

原文来自：http://lxw1234.com/archives/2015/07/365.htm

login_sonata

关注

0
点赞
踩
12

收藏

觉得还不错? 一键收藏
0
评论
Hive基础一（数据库，表，分区表，视图，导入导出数据）

相关链接： Hive基础一（数据库，表，分区表，视图，导入导出数据） Hive基础二（join原理和机制，join的几种类型，数据倾斜简单处理） Hive基础三（查询中常用的语法）注意Hive版本问题，可能有些命令不适用或者漏掉了。查看Hive版本的方法是在Hive命令行中输入：hive> set system:sun.java.command;1，Hive的数据单元Databases：数据
复制链接

扫一扫

专栏目录