Hive之DDL数据定义

缺乏、安全感 2023-10-11 03:27 35阅读 0赞

# Hive之DDL数据定义 #

--------------------

## 目录 ##

1.  创建数据库
2.  查询数据库
3.  修改数据库
4.  删除数据库
5.  创建表
6.  分区表
7.  修改表
8.  删除表

--------------------

### 1. 创建数据库 ###

1.  创建一个数据库，数据库在HDFS上的默认存储路径是/user/hive/warehouse/\*.db。  
    hive (default)> create database db\_hive;
2.  避免要创建的数据库已经存在错误，增加if not exists判断。（标准写法）  
    hive (default)> create database db\_hive;  
    FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. Database db\_hive already exists  
    hive (default)> create database if not exists db\_hive;
3.  创建一个数据库，指定数据库在HDFS上存放的位置  
    hive (default)> create database db\_hive2 location ‘/db\_hive2.db’;

--------------------

### 2. 查询数据库 ###

##### 1. 显示数据库 #####

1.  显示数据库

hive> show databases;

1.  过滤显示查询的数据库

hive> show databases like 'db_hive*';
    OK
    db_hive
    db_hive_1

--------------------

##### 2. 查看数据库详情 #####

1.  显示数据库信息

hive> desc database db_hive;
    OK
    db_hive		hdfs://hadoop102:9000/user/hive/warehouse/db_hive.db	atguiguUSER

1.  显示数据库详细信息，extended

hive> desc database extended db_hive;
    OK
    db_hive		hdfs://hadoop102:9000/user/hive/warehouse/db_hive.db	centosUSER

1.  切换当前数据库

hive (default)> use db_hive;

--------------------

### 3. 修改数据库 ###

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

hive (default)> alter database db_hive set dbproperties('createtime'='20170830');

在hive中查看修改结果

hive> desc database extended db_hive;
    db_name comment location        owner_name      owner_type      parameters
    db_hive         hdfs://hadoop102:8020/user/hive/warehouse/db_hive.db    atguigu USER    {createtime=20170830}

--------------------

### 4. 删除数据库 ###

1．删除空数据库

hive>drop database db_hive2;

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

hive> drop database db_hive;
    FAILED: SemanticException [Error 10072]: Database does not exist: db_hive
    hive> drop database if exists db_hive2;

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

hive> drop database db_hive;
    FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. InvalidOperationException(message:Database db_hive is not empty. One or more tables exist.)
    hive> drop database db_hive cascade;

--------------------

### 5. 创建表 ###

##### 1．建表语法 #####

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]

--------------------

##### 2．字段解释说明 #####

（1）**CREATE TABLE** 创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；用户可以用 IF NOT EXISTS 选项来忽略这个异常。  
（2）**EXTERNAL**关键字可以让用户创建一个外部表，在建表的同时指定一个指向实际数据的路径（LOCATION），Hive创建内部表时，会将数据移动到数据仓库指向的路径；若创建外部表，仅记录数据所在的路径，不对数据的位置做任何改变。在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。  
（3）**COMMENT**：为表和列添加注释。  
（4）**PARTITIONED BY**创建分区表  
（5）**CLUSTERED BY**创建分桶表  
（6）**SORTED BY**不常用  
（7）**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的简称，目的是用于序列化和反序列化。  
（8）**STORED AS**指定存储文件类型  
常用的存储文件类型：SEQUENCEFILE（二进制序列文件）、TEXTFILE（文本）、RCFILE（列式存储格式文件）  
如果文件数据是纯文本，可以使用STORED AS TEXTFILE。如果数据需要压缩，使用 STORED AS SEQUENCEFILE。  
（9）**LOCATION** ：指定表在HDFS上的存储位置。  
（10）**LIKE**允许用户复制现有的表结构，但是不复制数据。

--------------------

##### 3. 管理表 #####

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

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

（2）根据查询结果创建表（查询的结果会添加到新创建的表中）

create table if not exists student3 as select id, name from student;

（3）根据已经存在的表结构创建表

create table if not exists student4 like student;

（4）查询表的类型

hive (default)> desc formatted student2;
    Table Type:             MANAGED_TABLE

--------------------

##### 4. 外部表 #####

1．理论  
因为表是外部表，所以Hive并非认为其完全拥有这份数据。删除该表并不会删除掉这份数据，不过描述表的元数据信息会被删除掉。  
2．管理表和外部表的使用场景  
每天将收集到的网站日志定期流入HDFS文本文件。在外部表（原始日志表）的基础上做大量的统计分析，用到的中间表、结果表使用内部表存储，数据通过SELECT+INSERT进入内部表。

--------------------

### 6. 分区表 ###

分区表实际上就是对应一个HDFS文件系统上的独立的文件夹，该文件夹下是该分区所有的数据文件。Hive中的分区就是分目录，把一个大的数据集根据业务需要分割成小的数据集。在查询时通过WHERE子句中的表达式选择查询所需要的指定的分区，这样的查询效率会提高很多。

##### 1. 分区表基本操作 #####

1.  引入分区表（需要根据日期对日志进行管理）  
    /user/hive/warehouse/log\_partition/20170702/20170702.log  
    /user/hive/warehouse/log\_partition/20170703/20170703.log  
    /user/hive/warehouse/log\_partition/20170704/20170704.log
2.  创建分区表语法  
    hive (default)> create table dept\_partition(  
    deptno int, dname string, loc string  
    )  
    partitioned by (month string)  
    row format delimited fields terminated by ‘\\t’;
3.  加载数据到分区表中  
    hive (default)> load data local inpath ‘/opt/module/datas/dept.txt’ into table default.dept\_partition partition(month=‘201709’);  
    hive (default)> load data local inpath ‘/opt/module/datas/dept.txt’ into table default.dept\_partition partition(month=‘201708’);  
    hive (default)> load data local inpath ‘/opt/module/datas/dept.txt’ into table default.dept\_partition partition(month='201707’);

--------------------

### 7. 修改表 ###

##### 1. 重命名表 #####

1.  语法  
    ALTER TABLE table\_name RENAME TO new\_table\_name
2.  实操案例  
    hive (default)> alter table dept\_partition2 rename to dept\_partition3;

--------------------

##### 3. 增加/修改/替换列信息 #####

1.  语法  
    更新列  
    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则是表示替换表中所有字段。

1.  实操案例  
    （1）查询表结构  
    hive> desc dept\_partition;  
    （2）添加列  
    hive (default)> alter table dept\_partition add columns(deptdesc string);  
    （3）查询表结构  
    hive> desc dept\_partition;  
    （4）更新列  
    hive (default)> alter table dept\_partition change column deptdesc desc int;  
    （5）查询表结构  
    hive> desc dept\_partition;  
    （6）替换列  
    hive (default)> alter table dept\_partition replace columns(deptno string, dname  
    string, loc string);  
    （7）查询表结构  
    hive> desc dept\_partition;

--------------------

### 8. 删除表 ###

hive (default)> drop table dept_partition;