[本文2700字左右,预计阅读需要15-20分钟,操作预计10-15分钟]
在前面的文章里,我们大致了解了Hive,Hive定义了简单的类SQL查询语言,称为HQL,Hive可以将结构化的数据文件映射为一张数据库表, HQL用起来和SQL非常的类似。我们可以把Hive当成一个“数据库”,它也具备传统数据库的数据单元,数据库(Database)和表(Table)。 下面我们来了解一下Hive中的数据库(Database)和表(Table)。 Hive 的数据库和表 从图上可以看出,Hive作为一个“数据库”,在结构上积极向传统数据库看齐,也分数据库(Database),库中包含以下数据模型:Table(内部表)、External Table(外部表)、Partition(分区)、Bucket(分桶)。 Hive的数据都是存储在HDFS上的,默认有一个根目录,在hive-site.xml中,由参数hive.metastore.warehouse.dir指定。默认值为/user/hive/warehouse。可以根据实际的情况来修改这个配置。Hive的Table(内部表)中所有的数据都存放在这个目录中,但是并不包括 External Table(外部表)。 Hive 中的数据库(Database) 1查询所有的数据库
进入Hive命令行,执行show databases;命令,可以列出hive中的所有数据库,默认有一个default数据库,进入Hive-Cli之后,即到default数据库下。 使用use databasename;可以切换到某个数据库下,操作同MySQL;示例:
hive> show databases;OKdataaccessdefaulttestTime taken: 0.037 seconds, Fetched: 4 row(s)hive> use dataaccess;OKTime taken: 0.05 seconds
Hive中的数据库在HDFS上的存储路径为:
${hive.metastore.warehouse.dir}/databasename.db
比如,名为dataaccess的数据库存储路径为:
/user/hive/warehouse/dataaccess.db
# 在HDFS中查看数据库文件夹
root@ds02:~# hadoop fs -ls /user/hive/warehouse20/06/02 10:47:07 WARN util.NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicableFound 2 itemsdrwxrwxrwx - root supergroup 0 2020-06-01 14:52 /user/hive/warehouse/dataaccess.dbdrwxrwxrwx - root supergroup 0 2020-06-01 14:39 /user/hive/warehouse/test.db
2
创建Hive的数据库
Hive建库语法为:
CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name [COMMENT database_comment] [LOCATION hdfs_path] [WITH DBPROPERTIES (property_name=property_value, ...)];
说明:
IF NOT EXISTS:加上这句话代表判断数据库是否存在,不存在就会创建,存在就不会创建(生产环境建议使用)。
关键字COMMENT:数据库的描述
关键字LOCATION:创建数据库的地址,不加默认在/user/hive/warehouse/路径下
关键字WITH DBPROPERTIES:数据库的属性
示例:
创建名为datastorage的数据库
hive> create database if not exists datastoragecomment 'hive datastorage database'location '/dataTest/demo/datastorage.db';OKTime taken: 0.107 seconds
# 在HDFS中查看数据库文件夹
root@ds02:~# hadoop fs -ls /dataTest/demo20/06/02 10:51:08 WARN util.NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicableFound 2 itemsdrwxr-xr-x - root supergroup 0 2020-06-02 10:22 /dataTest/demo/datastorage.db
创建时候可以通过LOCATION hdfs_path指定数据库在HDFS上的存储位置。
注意:使用HDFS超级用户创建数据库后,该数据库在HDFS上的存储路径的用户为超级用户,如果该数据库是为某个或者某些用户使用的,则需要修改路径用户,或者在Hive中进行授权。
3查看数据库结构
可以查看数据库的描述信息和文件目录位置路径信息
示例:
hive> desc database datastorage;OKdatastoragehive datastorage databasehdfs://cluster1/dataTest/demo/datastorage.dbrootUSERTime taken: 0.41 seconds, Fetched: 1 row(s)
4
修改数据库
修改数据库属性:
ALTER (DATABASE|SCHEMA) database_nameSET DBPROPERTIES (property_name=property_value, …);
修改数据库用户:
ALTER (DATABASE|SCHEMA) database_nameSET OWNER [USER|ROLE] user_or_role;
5
删除数据库
删除数据库语法:
DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];
说明:
RESTRICT关键字是默认的,Hive不允许删除一个里面有表存在的数据库,如果想删除数据库,要么先将数据库中的表全部删除
CASCAD关键字,级联删除数据库(当数据库还有表时,级联删除表后再删除数据库),即Hive会将该数据库下的表全部删除后再删除数据库。
Hive 中的表(Table) 1查询所有的表
使用use databasename;
切换到数据库之后,执行show tables;
即可查看该数据库下所有的表:
示例:
hive> use datastorage;OKTime taken: 0.031 secondshive> show tables;OKkafka2hdfst_site_logt_site_log2table1Time taken: 0.074 seconds, Fetched: 4 row(s)
2
创建 Hive 的表
Hive 的建表语法:
CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name [(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][STORED AS DIRECTORIES][ROW FORMAT row_format][STORED AS file_format] | STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (...)] [LOCATION hdfs_path][TBLPROPERTIES (property_name=property_value, ...)] [AS select_statement];
示例:
hive> CREATE EXTERNAL TABLE table_test ( > id INT, > ip STRING COMMENT 'IP', > avg_view_depth DECIMAL(5,1), > bounce_rate DECIMAL(6,5) > ) COMMENT 'datastorage table' > PARTITIONED BY (day STRING) > ROW FORMAT DELIMITED > FIELDS TERMINATED BY ',' > STORED AS textfile > LOCATION '/dataTest/demo/datastorage.db/table_test/ ';OKTime taken: 0.16 seconds
说明:
关键字EXTERNAL:
表示该表为外部表,如果不指定EXTERNAL关键字,则表示内部表(
内部表和外部表最大的区别:
内部表DROP时候会删除HDFS上的数据;
外部表DROP时候不会删除HDFS上的数据;
内部表适用场景:
Hive中间表、结果表、一般不需要从外部(如本地文件、HDFS上load数据)的情况。
外部表适用场景:
源表,需要定期将外部数据映射到表中。
例如:
在Hive中建立外部表作为源表,通过添加分区的方式,将每天HDFS上的原始日志映射到外部表的天分区中;在外部表(原始日志表)的基础上做大量的统计分析,用到的中间表、结果表使用内部表存储,数据通过SELECT+INSERT进入内部表。
)
关键字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之间的分隔符
关键字STORED AS:
指定表在HDFS上的文件存储格式,可选的文件存储格式有:
TEXTFILE //文本,默认值
SEQUENCEFILE // 二进制序列文件
RCFILE //列式存储格式文件 Hive0.6以后开始支持
ORC //列式存储格式文件,比RCFILE有更高的压缩比和读写效率,Hive0.11以后开始支持
PARQUET //列出存储格式文件,Hive0.13以后开始支持
关键词LOCATION:
指定表在HDFS上的存储位置。
关于Hive的数据类型和文件格式等在之前的文章从零开始学习大数据系列(三十五)Hive的数据类型与文件格式中有详细的介绍
3查看表结构
默认情况下,表的存储路径为:
${hive.metastore.warehouse.dir}/databasename.db/tablename/
可以使用desc formatted tablename; 命令查看表的详细信息,其中包括了列名、列类型和存储路径等等详细信息。
示例:
hive> desc formatted table_test;OK# col_name data_type comment id int ip string IP avg_view_depth decimal(5,1) bounce_rate decimal(6,5) # Partition Information # col_name data_type comment day string # Detailed Table Information Database: datastorage Owner: root CreateTime: Tue Jun 02 14:21:23 CST 2020 LastAccessTime: UNKNOWN Retention: 0 Location: hdfs://cluster1/dataTest/demo/datastorage.db/table_testTable Type: EXTERNAL_TABLE Table Parameters: COLUMN_STATS_ACCURATE{\"BASIC_STATS\":\"true\"}EXTERNAL TRUE comment datastorage table numFiles 0 numPartitions 0 numRows 0 rawDataSize 0 totalSize 0 transient_lastDdlTime1591078883 # 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 , serialization.format, Time taken: 0.257 seconds, Fetched: 42 row(s)
4
修改表
修改表名
ALTER TABLE table_name RENAME TO new_table_name;
修改表属性
ALTER TABLE table_name SET TBLPROPERTIES table_properties;
表的属性列表,以key=value的形式表示,key为property_name,value为property_value,列表中每组key=value之间用逗号分隔。
table_properties:
: (property_name = property_value, property_name = property_value, ... )
示例:
修改注释信息Comment
ALTER TABLE table_name SET TBLPROPERTIES ('comment' = new_comment);
5删除表
删除Hive表语法:
DROP TABLE [IF EXISTS] table_name;
注意:
在删除内部表的时候,Hive将会把属于表的元数据和数据全部删掉;而删除外部表的时候,Hive仅仅删除外部表的元数据,数据是不会删除的!