本文介绍一下Hive中的数据库(Database/Schema)和表(Table)的基础知识,由于篇幅原因,这里只是一些常用的、基础的。

Hive的数据库和表

先看一张草图:

Hive结构

Hive结构

从图上可以看出,Hive作为一个“数据库”,在结构上积极向传统数据库看齐,也分数据库(Schema),每个数据库下面有各自的表组成。

1.  Hive在HDFS上的默认存储路径

Hive的数据都是存储在HDFS上的,默认有一个根目录,在hive-site.xml中,由参数hive.metastore.warehouse.dir指定。默认值为/user/hive/warehouse.

2.  Hive中的数据库(Database)

  • 进入Hive命令行,执行show databases;命令,可以列出hive中的所有数据库,默认有一个default数据库,进入Hive-Cli之后,即到default数据库下。
  • 使用use databasename;可以切换到某个数据库下,同mysql;
  1. hive> show databases;
  2. OK
  3. default
  4. lxw1234
  5. usergroup_mdmp
  6. userservice_mdmp
  7. Time taken: 0.442 seconds, Fetched: 4 row(s)
  8. hive> use lxw1234;
  9. OK
  10. Time taken: 0.023 seconds
  11. hive>
  • Hive中的数据库在HDFS上的存储路径为

${hive.metastore.warehouse.dir}/databasename.db

比如,名为lxw1234的数据库存储路径为:

/user/hive/warehouse/lxw1234.db

  • 创建Hive数据库

使用HDFS超级用户,进入Hive-Cli,语法为:

  1. CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name
  2. [COMMENT database_comment]
  3. [LOCATION hdfs_path]
  4. [WITH DBPROPERTIES (property_name=property_value, ...)];

比如,创建名为lxw1234的数据库:

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

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

注意:使用HDFS超级用户创建数据库后,该数据库在HDFS上的存储路径的属主为超级用户,如果该数据库是为某个或者某些用户使用的,则需要修改路径属主,或者在Hive中进行授权。

  • 修改数据库

修改数据库属性:

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];

默认情况下,Hive不允许删除一个里面有表存在的数据库,如果想删除数据库,要么先将数据库中的表全部删除,要么可以使用CASCADE关键字,使用该关键字后,Hive会自己将数据库下的表全部删除。RESTRICT关键字就是默认情况,即如果有表存在,则不允许删除数据库。

3.  Hive中的表(Table)

3.1 查看所有的表

进入Hive-Cli,使用use databasename;切换到数据库之后,执行show tables; 即可查看该数据库下所有的表:

  1. hive> show tables;
  2. OK
  3. lxw1
  4. lxw1234
  5. table1
  6. t_site_log

3.2 表的存储路径

默认情况下,表的存储路径为:

${hive.metastore.warehouse.dir}/databasename.db/tablename/

可以使用desc formatted tablename;命令查看表的详细信息,其中包括了存储路径:

Location:               hdfs://cdh5/hivedata/warehouse/lxw1234.db/lxw1234

 

3.3 内部表和外部表

Hive中的表分为内部表(MANAGED_TABLE)和外部表(EXTERNAL_TABLE)。

  • 内部表和外部表最大的区别

内部表DROP时候会删除HDFS上的数据;

外部表DROP时候不会删除HDFS上的数据;

  • 内部表适用场景:

Hive中间表、结果表、一般不需要从外部(如本地文件、HDFS上load数据)的情况。

  • 外部表适用场景:

源表,需要定期将外部数据映射到表中。

  • 我们的使用场景:

每天将收集到的网站日志定期流入HDFS文本文件,一天一个目录;

在Hive中建立外部表作为源表,通过添加分区的方式,将每天HDFS上的原始日志映射到外部表的天分区中;

在外部表(原始日志表)的基础上做大量的统计分析,用到的中间表、结果表使用内部表存储,数据通过SELECT+INSERT进入内部表。

3.4 创建表

创建表的语法选项特别多,这里只列出常用的选项。

其他请参见Hive官方文档:

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-CreateTable

以一个例子来说吧:

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 //列式存储格式文件 Hive0.6以后开始支持

ORC //列式存储格式文件,比RCFILE有更高的压缩比和读写效率,Hive0.11以后开始支持

PARQUET //列出存储格式文件,Hive0.13以后开始支持

  • 关键词LOCATION

指定表在HDFS上的存储位置。