hive 创建外表会在hdfs创建目录么 请创建hive内表student

Hive 的 DDL 操作

Create/Alter/Drop 库

1. 前置说明

Hive数据定义语言（Date Definition Language）

• Create/Drop/Alter数据库、
• Create/Drop/Truncate表、
• Alter表/分区/列、
• Create/Drop/Alter视图、
• Create/Drop/Alter索引、
• Create/Drop函数、
• Create/Drop/Grant/Revoke角色和权限等内容。

任务描述

Create/Alter/Drop 数据库

2. 基本命令

2.1 Create

创建数据库的语法

CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name  
        [COMMENT database_comment]  
        [LOCATION hdfs_path]  
        [WITH DBPROPERTIES (property_name=property_value,…)];

字段解释

• DATABASE|SCHEMA：用于限定创建数据库或数据库模式
• IF NOT EXISTS：目标对象不存在时才执行创建操作（可选）
• COMMENT：起注释说明作用
• LOCATION：指定数据库位于HDFS上的存储路径。若未指定，将使用${hive.metastore.warehouse.dir}定义值作为其上层路径位置
• WITH DBPROPERTIES：为数据库提供描述信息，如创建database的用户或时间

创建一个名为shopping的数据库，位于HDFS的/hive/shopping下，创建人为Xiaoming，创建日期为：2019-01-01

CREATE DATABASE IF NOT EXISTS shopping  
LOCATION '/hive/shopping'  
WITH DBPROPERTIES('creator'='Xiaoming','date'='2019-01-01');

查看数据库shopping的信息（若不指定关键字EXTENDED，则不会输出{}里的内容）。

-- 显示外部表并查看表信息
DESCRIBE DATABASE EXTENDED shopping;
-- 查看表信息
desc database shopping;

2.2 Alter

修改数据库的语法

注：只能修改数据库的键值对属性值。数据库名和数据库所在的目录位置不能修改

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

修改数据库shopping的键值对描述信息中的创建者信息，修改创建者为Xiaohong。

ALTER DATABASE shopping SET DBPROPERTIES('creator'='Xiaohong');

2.3 Drop

删除数据库的语法

DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];

字段解释

• DATABASE|SCHEMA：用于限定删除的数据库或数据库模式
• IF EXISTS：目标对象存在时才执行删除操作（可选）
• RESTRICT|CASCADE：RESTRICT为 Hive 默认操作方式，当database_name中不存在任何数据库对象时才能执行DROP操作；CASCADE 采用强制DROP方式，汇联通存在于database_name中的任何数据库对象和database_name一起删除（可选）

3. 任务拓展

创建数据库test1,位于HDFS的/hive/test1下,创建人creator为John,创建日期date为2019-02-25

修改数据库test1的创建人为Marry

删除数据库test1

预期输出样式

test1数据库的信息如下：  
test1        hdfs://localhost:9000/hive/test1    root    USER    {data=2019-02-25, creator=John}  
修改后test1数据库的信息如下：  
test1        hdfs://localhost:9000/hive/test1    root    USER    {date=2019-02-25, creator=Marry}  
查看当前有哪些数据库：  
default

执行命令

CREATE DATABASE IF NOT EXISTS test1
LOCATION '/hive/test1'
WITH DBPROPERTIES('creator'='John','date'='2019-02-25');
ALTER DATABASE test1 SET DBPROPERTIES('creator'='Marry');
DROP DATABASE test1;

流程验证

Create/Truncate 表

1. 任务描述

根据数据库对表进行创建/复制/删除/截断

实验平台直达链接

2. HQL语句

2.1 Create

创建表的详细语法

CREATE [TEMPOPARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.] 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]  
    [SKEWED BY (col_name,col_name,…) ON ([(col_value,col_value,…),…|col_value,col_value,…]) [STORED AS DIRECTORIES] ]  
    [  
        [ROW FORMAT DELIMITED [FIFLDS TERMINATED BY char [ESCAPED BY char]] [COLLECTION ITEMS TERMINATED BY char] [MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char] [NULL DEFINED AS char]  
        | SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value,property_name=property_value,…)]  
      ]  
　    [STORED AS file_format]  
      | STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (…)]  
    ]  
    [LOCATION hdfs_path]  
    [TBLPROPERTIES (property_name=property_value,…)]  
    [AS select_statement];

字段解释

• TEMPOPARY：创建临时表，若未指定，则默认创建的是普通表
• EXTERNAL：创建外部表，若未指定，则默认创建的是内部表
• IF NOT EXISTS：若表不存在才创建，若未指定，当目标表存在时，创建操作抛出异常
• db_name.：前缀，指定表所属于的数据库。若未指定且当前数据库非db_name，则使用default数据库
• COMMENT：添加注释说明，注释内容位于单引号内
• PARTITIONED BY：针对存储有大量数据集的表，根据表内容所具有的某些共同特征定义一个标签，将这类数据存储在该标签所标识的位置，可以提高表内容的查询速度。PARTITIONED BY中的列名为伪列或标记列，不能与表中的实体列名相同，否则 hive 表创建操作报错
• CLUSTERED BY：根据列之间的相关性指定列聚类在相同桶中（BUCKETS），可以对表内容按某一列进行升序（ASC）或降序（DESC）排序（SORTED BY关键字）
• SKEWED BY：用于过滤掉特定列col_name中包含值col_value(ON(col_value,…)关键字指定的值）的记录，并单独存储在指定目录（STORED AS DIRECTORIES）下的单独文件中
• ROW FORMAT：指定 hive 表行对象（ROW Object）数据与 HDFS 数据之间进行传输的转换方式（HDFS files -> Deserializer ->Row object以及Row object ->Serializer ->HDFS files），以及数据文件内容与表行记录各列的对应。在创建表时可以指定数据列分隔符（FIFLDS TERMINATED BY 子句）、对特殊字符进行转义的特殊字符（ESCAPED BY 子句）、符合数据类型值分隔符（COLLECTION ITEMS TERMINATED BY 子句）、MAP key-value 类型分隔符（MAP KEYS TERMINATED BY）、数据记录行分隔符（LINES TERMINATED BY）、定义NULL字符（NULL DEFINED AS），同时可以指定自定义的SerDE（Serializer和Deserializer，序列化和反序列化），也可以指定默认的SerDE。如果ROW FORMAT未指定或指定为ROW FORMAT DELIMITED，将使用内部默认SerDe
• STORED AS：指定 hive 表数据在 HDFS 上的存储方式。file_format值包括TEXTFILE（普通文本文件，默认方式）、SEQUENCEFILE（压缩模式）、ORC（ORC文件格式）和AVRO（AVRO文件格式）
• STORED BY：创建一个非本地表，如创建一个 HBase 表
• LOCATION：指定表数据在 HDFS 上的存储位置。若未指定，db_name数据库将会储存在${hive.metastore.warehouse.dir}定义位置的db_name目录下
• TBLPROPERTIES：为所创建的表设置属性（如创建时间和创建者，默认为当前用户和当前系统时间）
• AS select_statement：使用select子句创建一个复制表（包括select子句返回的表模式和表数据）

在之前创建的shopping数据库创建一张商品信息表（items_info）,并查看表结构

CREATE TABLE IF NOT EXISTS shopping.items_info(  
name STRING COMMENT 'item name',  
price FLOAT COMMENT 'item price',  
category STRING COMMENT 'item category',  
brand STRING COMMENT 'item brand',  
type STRING COMMENT 'item type',  
stock INT COMMENT 'item stock',  
address STRUCT<street:STRING, city:STRING, state:STRING, zip:INT> COMMENT 'item sales address')  
COMMENT 'goods information table'  
TBLPROPERTIES ('creator'='Xiaoming','date'='2019-01-01');

-- 切换至shopping数据库
use shopping;
-- 查看items_info表结构
desc items_info;

复制表

按照已存在的表或视图定义相同结构的表或视图（使用LIKE关键字，注：只复制表定义，不复制表数据）。

复制上部创建的表items_info将其命名为items_info2

CREATE TABLE IF NOT EXISTS items_info2 LIKE items_info;

2.2 Truncate

众所周知，当我们在本地删除某一小文件后，它并没有彻底删除而是进入了回收站，需要在回收站中再将其删除才算彻底清除。

截断表就相当于直接将数据从本地删除，而不会放入回收站。

截断表（删除表中所有行）的语法

TRUNCATE TABLE table_name [PARTITION partition_spec];  
partition_spec:  
    (partition_column=partition_col_value,partition_column=partition_col_value,…)

3. 任务拓展

student表结构

INFO	TYPE	COMMENT
Sno	INT	student sno
name	STRING	student name
age	INT	student age
sex	STRING	student sex
score	STRUCT	student score

创建数据库test2

在test2中创建表student，表结构如上所示

使用LIKE关键字创建一个与student表结构相同的表student_info

删除表student

预期输出样式

表student的结构为：  
sno                     int                     student sno           
name                    string                  student name          
age                     int                     student age           
sex                     string                  student sex           
score                   struct<Chinese:float,Math:float,English:float>    student score    
表student_info的结构为：  
sno                     int                     student sno           
name                    string                  student name          
age                     int                     student age           
sex                     string                  student sex           
score                   struct<Chinese:float,Math:float,English:float>    student score   
数据库test2中有以下表：  
student_info

执行命令

CREATE DATABASE IF NOT EXISTS test2;

CREATE TABLE IF NOT EXISTS test2.student(
Sno INT COMMENT 'student sno',
name STRING COMMENT 'student name',
age INT COMMENT 'student age',
sex STRING COMMENT 'student sex',
score STRUCT<Chinese:FLOAT,Math:FLOAT,English:FLOAT> COMMENT 'student score');

CREATE TABLE IF NOT EXISTS student_info like student;

DROP TABLE student;

流程验证