Hive DDL 数据定义语言

2021年1月28日 9点热度 0条评论 来源: yljphp

数据库

创建一个数据库

CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name
  [COMMENT database_comment]
  [LOCATION hdfs_path]
  [WITH DBPROPERTIES (property_name=property_value, ...)];
hive (default)> create database ylj_db;
OK
Time taken: 1.036 seconds

数据库在HDFS上的默认存储路径是/user/hive/warehouse/*.db。

查询数据库

显示数据库

hive (default)> show databases;
OK
database_name
default
ylj_db
Time taken: 0.347 seconds, Fetched: 2 row(s)

过滤显示查询的数据库

hive (default)> show databases like 'ylj*';
OK
database_name
ylj_db
Time taken: 0.034 seconds, Fetched: 1 row(s)

查看数据库详情

hive (default)> desc database ylj_db;
OK
db_name comment location        owner_name      owner_type      parameters
ylj_db          hdfs://hadoop101:9000/user/hive/warehouse/ylj_db.db     root    USER
Time taken: 0.042 seconds, Fetched: 1 row(s)

显示数据库详细信息,extended

hive (default)> desc database extended ylj_db;
OK
db_name comment location        owner_name      owner_type      parameters
ylj_db          hdfs://hadoop101:9000/user/hive/warehouse/ylj_db.db     root    USER
Time taken: 0.041 seconds, Fetched: 1 row(s)

切换当前数据库

hive (default)> use ylj_db;
OK
Time taken: 0.039 seconds

修改数据库

ALTER (DATABASE|SCHEMA) database_name SET DBPROPERTIES (property_name=property_value, ...);   -- (Note: SCHEMA added in Hive 0.14.0)
 
ALTER (DATABASE|SCHEMA) database_name SET OWNER [USER|ROLE] user_or_role;   -- (Note: Hive 0.13.0 and later; SCHEMA added in Hive 0.14.0)
  
ALTER (DATABASE|SCHEMA) database_name SET LOCATION hdfs_path; -- (Note: Hive 2.2.1, 2.4.0 and later)

用户可以使用ALTER DATABASE命令为某个数据库的DBPROPERTIES设置键-值对属性值,来描述这个数据库的属性信息。数据库的其他元数据信息都是不可更改的,包括数据库名和数据库所在的目录位置。

hive (ylj_db)> alter database ylj_db set dbproperties('create_time' = '2019-04-30 15:28:56');
OK
Time taken: 0.201 seconds
hive (ylj_db)> desc database extended ylj_db;
OK
db_name comment location        owner_name      owner_type      parameters
ylj_db          hdfs://hadoop101:9000/user/hive/warehouse/ylj_db.db     root    USER    {create_time=2019-04-30 15:28:56}
Time taken: 0.029 seconds, Fetched: 1 row(s)

删除数据库

DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];
删除空数据库
hive (ylj_db)> drop database ylj_db;
删除不存在的数据库

如果删除的数据库不存在,最好采用 if exists判断数据库是否存在

hive (ylj_db)> drop database ylj_db1;
FAILED: SemanticException [Error 10072]: Database does not exist: ylj_db1
hive (ylj_db)> drop database if exists ylj_db1;
OK
Time taken: 0.01 seconds
删除非空数据库

如果数据库不为空,可以采用cascade命令,强制删除

hive (ylj_db)> create table ttt(id int);
OK
Time taken: 0.303 seconds
hive (ylj_db)> drop database ylj_db;
FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. InvalidOperationException(message:Database ylj_db is not empty. One or more tables exist.)
hive (ylj_db)> drop database ylj_db cascade;
Moved: 'hdfs://hadoop101:9000/user/hive/warehouse/ylj_db.db/ttt' to trash at: hdfs://hadoop101:9000/user/root/.Trash/Current
Moved: 'hdfs://hadoop101:9000/user/hive/warehouse/ylj_db.db' to trash at: hdfs://hadoop101:9000/user/root/.Trash/Current
OK
Time taken: 1.054 seconds

创建表

建表语法

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name 
[(col_name data_type [COMMENT col_comment], ...)] 
[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] 
[ROW FORMAT row_format] 
[STORED AS file_format] 
[LOCATION hdfs_path]

字段解释说明

CREATE TABLE

创建一个指定名字的表。如果相同名字的表已经存在,则抛出异常;用户可以用 IF NOT EXISTS 选项来忽略这个异常。

EXTERNAL
可以让用户创建一个外部表,在建表的同时指定一个指向实际数据的路径(LOCATION)。
Hive创建内部表时,会将数据移动到数据仓库指向的路径;若创建外部表,仅记录数据所在的路径,不对数据的位置做任何改变。在删除表的时候,内部表的元数据和数据会被一起删除,而外部表只删除元数据,不删除数据。

COMMENT
为表和列添加注释。

PARTITIONED BY
创建分区表,这个分区跟MR分区不同。

在Hive Select查询中一般会扫描整个表内容,会消耗很多时间做没必要的工作。有时候只需要扫描表中关心的一部分数据,因此建表时引入了partition概念。分区表指的是在创建表时指定的partition的分区空间。

Hive可以对数据按照某列或者某些列进行分区管理,所谓分区我们可以拿下面的例子进行解释。
当前互联网应用每天都要存储大量的日志文件,几G、几十G甚至更大都是有可能。存储日志,其中必然有个属性是日志产生的日期。在产生分区时,就可以按照日志产生的日期列进行划分。把每一天的日志当作一个分区。
将数据组织成分区,主要可以提高数据的查询速度。至于用户存储的每一条记录到底放到哪个分区,由用户决定。即用户在加载数据的时候必须显示的指定该部分数据放到哪个分区。

CLUSTERED BY
创建分桶表。对于每一个表(table)或者分区, Hive可以进一步组织成桶,也就是说桶是更为细粒度的数据范围划分。Hive也是 针对某一列进行桶的组织。Hive采用对列值哈希,然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中。

(1)获得更高的查询处理效率。桶为表加上了额外的结构,Hive 在处理有些查询时能利用这个结构。具体而言,连接两个在(包含连接列的)相同列上划分了桶的表,可以使用 Map 端连接 (Map-side join)高效的实现。比如JOIN操作。对于JOIN操作两个表有一个相同的列,如果对这两个表都进行了桶操作。那么将保存相同列值的桶进行JOIN操作就可以,可以大大较少JOIN的数据量。

(2)使取样(sampling)更高效。在处理大规模数据集时,在开发和修改查询的阶段,如果能在数据集的一小部分数据上试运行查询,会带来很多方便。

SORT BY
sort by 只能保证在同一个reduce中的数据可以按指定字段排序。
使用sort by 你可以指定执行的reduce个数 (set mapreduce.job.reduce=) 这样可以输出更多的数据。
对输出的数据再执行归并排序,即可以得到全部结果。需要注意的是,N个Reduce处理的数据范围是可以重叠的,所以最后排序完的N个文件之间数据范围是有重叠的。

ROW FORMAT

DELIMITED [FIELDS TERMINATED BY char] [COLLECTION ITEMS TERMINATED BY char]
        [MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char] 
   | 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是Serialize/Deserilize的简称,目的是用于序列化和反序列化。

STORED AS
指定存储文件类型,常用的存储文件类型:SEQUENCEFILE(二进制序列文件)、TEXTFILE(文本)、RCFILE(列式存储格式文件)如果文件数据是纯文本,可以使用STORED AS TEXTFILE。如果数据需要压缩,使用 STORED AS SEQUENCEFILE。

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

LIKE
允许用户复制现有的表结构,但是不复制数据

内部表(管理表)

默认创建的表都是所谓的管理表,有时也被称为内部表。因为这种表,Hive会(或多或少地)控制着数据的生命周期。Hive默认情况下会将这些表的数据存储在由配置项hive.metastore.warehouse.dir(例如,/user/hive/warehouse)所定义的目录的子目录下。
当我们删除一个管理表时,Hive也会删除这个表中数据。管理表不适合和其他工具共享数据。

普通创建

默认参数时,表hive文件存储格式textfile,默认路径是/user/hive/warehouse/库名.db/表名

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

指定了存储路径时:

create table if not exists student2(id int,name string) row format delimited fields terminated  by '\t' stored as textfile location '/user/hive/warehouse/student22';

根据查询结果创建表

先向student表中导入一部分数据

hive (ylj_test)> load data local inpath '/opt/module/hive-1.2.1/datas/student.txt' overwrite into table student;
Loading data to table ylj_test.student
Table ylj_test.student stats: [numFiles=1, numRows=0, totalSize=43, rawDataSize=0]
OK
Time taken: 1.248 seconds
hive (ylj_test)> select * from student;
OK
student.id      student.name
2       lisi
3       wangwu
4       张三
5       李四
6       王五
Time taken: 0.411 seconds, Fetched: 5 row(s)

创建表student3,数据源是表student中查询出来的结果

hive (ylj_test)> create table if not exists student3 as select id,name from student;
Query ID = root_20190430002131_d0831c70-fd3a-4fad-8f23-71eeaa417108
Total jobs = 3
Launching Job 1 out of 3
Number of reduce tasks is set to 0 since there's no reduce operator
Starting Job = job_1554120237694_0001, Tracking URL = http://hadoop101:8088/proxy/application_1554120237694_0001/
Kill Command = /opt/module/hadoop-2.7.2/bin/hadoop job  -kill job_1554120237694_0001
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 0
2019-04-30 00:22:08,635 Stage-1 map = 0%,  reduce = 0%
2019-04-30 00:22:18,850 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 1.83 sec
MapReduce Total cumulative CPU time: 1 seconds 830 msec
Ended Job = job_1554120237694_0001
Stage-4 is selected by condition resolver.
Stage-3 is filtered out by condition resolver.
Stage-5 is filtered out by condition resolver.
Moving data to: hdfs://hadoop101:9000/user/hive/warehouse/ylj_test.db/.hive-staging_hive_2019-04-30_00-21-31_761_4709603611711121476-1/-ext-10001
Moving data to: hdfs://hadoop101:9000/user/hive/warehouse/ylj_test.db/student3
Table ylj_test.student3 stats: [numFiles=1, numRows=5, totalSize=43, rawDataSize=38]
MapReduce Jobs Launched: 
Stage-Stage-1: Map: 1   Cumulative CPU: 1.83 sec   HDFS Read: 2864 HDFS Write: 116 SUCCESS
Total MapReduce CPU Time Spent: 1 seconds 830 msec
OK
id      name
Time taken: 53.034 seconds

从控制台输出日志,可以看出程序启动的一个MR任务。

HDFS上多了对应的文件

根据已存在的表创建表

hive (ylj_test)> create table if not exists student4 like student;
OK
Time taken: 0.296 seconds

只是创建表结构,不导入数据。

查询表的类型

hive (ylj_test)> desc formatted  student2;
OK
col_name        data_type       comment
# col_name              data_type               comment             
                 
id                      int                                         
name                    string                                      
                 
# Detailed Table Information             
Database:               ylj_test                 
Owner:                  root                     
CreateTime:             Tue Apr 30 00:11:50 CST 2019     
LastAccessTime:         UNKNOWN                  
Protect Mode:           None                     
Retention:              0                        
Location:               hdfs://hadoop101:9000/user/hive/warehouse/student22      
Table Type:             MANAGED_TABLE            
Table Parameters:                
        transient_lastDdlTime   1556554310          
                 
# 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.156 seconds, Fetched: 28 row(s)

信息中Table Type等于MANAGED_TABLE,表名它是一个管理表(内部表)

外部表

因为表是外部表,所以Hive并非认为其完全拥有这份数据。删除该表并不会删除掉这份数据,不过描述表的元数据信息会被删除掉。

使用场景

每天将收集到的网站日志定期流入HDFS文本文件。在外部表(原始日志表)的基础上做大量的统计分析,用到的中间表、结果表使用内部表存储,数据通过SELECT+INSERT进入内部表。

创建并查看

hive (ylj_test)> create external table if not exists ext_student(id int,name string) row format delimited fields terminated by '\t';
OK
Time taken: 0.089 seconds
hive (ylj_test)> desc formatted ext_student;
OK
col_name        data_type       comment
# col_name              data_type               comment             
                 
id                      int                                         
name                    string                                      
                 
# Detailed Table Information             
Database:               ylj_test                 
Owner:                  root                     
CreateTime:             Tue Apr 30 00:32:39 CST 2019     
LastAccessTime:         UNKNOWN                  
Protect Mode:           None                     
Retention:              0                        
Location:               hdfs://hadoop101:9000/user/hive/warehouse/ylj_test.db/ext_student        
Table Type:             EXTERNAL_TABLE           
Table Parameters:                
        EXTERNAL                TRUE                
        transient_lastDdlTime   1556555559          
                 
# 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.105 seconds, Fetched: 29 row(s)

信息中Table Type等于EXTERNAL_TABLE,表名它是一个外部表。

管理表与外部表的互相转换

把表ext_student转换为内部表

hive (ylj_test)> alter table ext_student set tblproperties('EXTERNAL'='FALSE');
OK
Time taken: 0.224 seconds
hive (ylj_test)> desc formatted ext_student;
Table Type:             MANAGED_TABLE

把表ext_student转换为外部表

hive (ylj_test)> alter table ext_student set tblproperties('EXTERNAL'='TRUE');
OK
Time taken: 0.224 seconds
hive (ylj_test)> desc formatted ext_student;
Table Type:             EXTERNAL_TABLE

修改表

重命名表

hive (ylj_db)> alter table stu_part2 rename to stu_part_2;
OK
Time taken: 0.188 seconds

增加/修改/替换列信息

更新列
ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name]
增加和替换列
ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...) 

注:ADD是代表新增一字段,字段位置在所有列后面(partition列前),REPLACE则是表示替换表中所有字段。

删除表

hive (ylj_db)> drop table stu_part_2;
OK
Time taken: 0.631 seconds

参考

LanguageManual DDL
Hive 基础(1):分区、桶、Sort Merge Bucket Join

    原文作者:yljphp
    原文地址: https://blog.csdn.net/yljphp/article/details/89714692
    本文转自网络文章,转载此文章仅为分享知识,如有侵权,请联系管理员进行删除。