Hive之DDL

对数据库操作：

创建数据库：

create database db_hive1;

在创建的时候没有指定路径（hdfs的存储路径）会使用默认路径

使用关键字 ** location** 可指定路径但是该目录是在/user下新建一个当前连接用户名的目录，在该目录下边创建所给出的相应目录

create database db_hive3 location 'db_2/sss';

查看数据库信息：

1.查看基本信息：

DESCRIBE DATABASE db_hive1;

2.查看详尽信息：

DESCRIBE DATABASE EXTENDED db_hive1;

相比之下只是多出一些信息（parameters）

这列信息所展示的是我们在创建数据库的时候附加的信息

修改数据库：
用户可以使用alter database命令修改数据库某些信息，其中能够修改的信息包括dbproperties、location、owner user。需要注意的是：修改数据库location，不会改变当前已有表的路径信息，而只是改变后续创建的新表的默认的父目录。

--修改dbproperties
ALTER DATABASE database_name SET DBPROPERTIES (property_name=property_value, ...);

删除数据库：

1.简单语法：

drop database 数据库名

2.复杂语法：

DROP DATABASE [IF EXISTS] database_name [RESTRICT|CASCADE];

IF EXISTS：存在才删除 不存在不报错

RESTRICT：严格模式，若数据库不为空，则会删除失败。（需要手动删除表和数据）（默认）

CASCADE：级联模式，若数据库不为空，则会将库中的表一并删除。

对表操作：

创建表：

1.普通建表：

create table ind1(name string,age int);

完整语法：

CREATE [TEMPORARY] [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]
[ROW FORMAT row_format] 
[STORED AS file_format]
[LOCATION hdfs_path]
[TBLPROPERTIES (property_name=property_value, ...)]

关键字解释：

（1）TEMPORARY

临时表，该表只在当前会话可见，会话结束，表会被删除。

（2）EXTERNAL（重点）

加上该关键字就是创建外部表的意思（内部表也叫管理表：管理表意味着Hive会完全接管该表，包括元数据和HDFS中的数据，外部表：而外部表则意味着Hive只接管元数据，而不完全接管HDFS中的数据。）

（3）数据类型：

** 基础数据类型：**

Hive

说明

定义

t****inyint

1byte有符号整数

s****mallint

2byte有符号整数

i****nt

4byte有符号整数

b****igint

8byte有符号整数

b****oolean

布尔类型，true或者false

f****loat

单精度浮点数

d****ouble

双精度浮点数

decimal

十进制精准数字类型

decimal(16,2)

varchar

字符序列，需指定最大长度，最大长度的范围是[1,65535]

varchar(32)

string

字符串，无需指定最大长度

t****imestamp

时间类型

b****inary

二进制数据

** 复杂数据类型：**

类型

说明

定义

取值

array

数组是一组相同类型的值的集合

array<string>

arr[0]

map

map是一组相同类型的键-值对集合

map<string, int>

map['key']

struct

结构体由多个属性组成，每个属性都有自己的属性名和数据类型

struct<id:int, name:string>

struct.id

类型转换：

Hive的基本数据类型可以做类型转换，转换的方式包括隐式转换以及显示转换。

隐式转换：

Hive官方说明：隐式转换官方文档https://cwiki.apache.org/confluence/display/hive/languagemanual+types

** 显式转换：**

可以借助cast函数完成显示的类型转换

select cast('12345'as int);

主要就是cast函数的调用

（4）ROW FORMAT（序列化&反序列化）

DELIMITED 关键字表示对文件中的每个字段按照特定分割符进行分割，其会使用默认的SERDE对每行数据进行序列化和反序列化。

** SERDE关键字可用于指定其他内置的SERDE或者用户自定义的SERDE。例如JSON SERDE**，可用于处理JSON字符串。

2.Create Table As Select（CTAS）建表：

    该语法允许用户利用select查询语句返回的结果，直接建表，表的结构和查询语句的结构保持一致，且保证包含select查询语句放回的内容

create table inc as select *from stu;

结果新创建的inc表中数据与stu表一致

3.Create Table Like语法

该语法允许用户复刻一张已经存在的表结构，与上述的CTAS语法不同，该语法创建出来的表中不包含数据。

create table incc LIKE inc;

建表案例：

1.创建内部表：

create table if not exists student(id int comment '学号'
                                  ,name string)
                                  row format delimited fields terminated by '\t'
location '/user/hive/warehouse/student';

上述创建的内部表是默认序列化和反序列化，并且设置列分隔符和指定HDFS中文件路径

如果删除表，则HDFS路径也会跟着删除

可以看到没有了student表的信息

2.创建外部表：

create external table student(id int comment 'id'
                             ,name string)
                             row format delimited fields terminated by '\t'
location '/user/hive/warehouse/student';

参数与上一案例相同，重点演示删除操作

（外部表只管理元数据，不管理HDFS中的数据）

上传数据：

执行删除命令后：

可以看到我们可视化界面中已经没有了student表

但是我们的HDFS中还是存在我们的数据

上述就应证了我们外部表只管理元数据

3.SERDE和复杂数据类型的使用：

下边给出JSON格式化后的文件：

{
    "name": "dasongsong",
    "friends": [
        "bingbing",
        "lili"
    ],
    "students": {
        "xiaohaihai": 18,
        "xiaoyangyang": 16
    },
    "address": {
        "street": "hui long guan",
        "city": "beijing",
        "postal_code": 10010
    }
}

我们要把JSON文件对应创建HIVE表，则需要把JSON文件中的一级字段与Hive表的字段保持一致

再根据后边的值来设计Hive表字段的类型

设计name的类型为： string

  fridends的类型为： array数组

  students的类型为：map键值对的形式

  address的类型为： struct（因为值的类型不一致所以设置为struct）

建表语句如下：

create table teacher
(
    name     string,
    friends  array<string>,
    students map<string,int>,
    address  struct<city:string,street:string,postal_code:int>
)
row format serde 'org.apache.hadoop.hive.serde2.JsonSerDe'
location '/user/hive/warehouse/teacher';

上传JSON文件（上传数据的时候要保证一条JSON数据为一行（不要格式化之后的））

让后调用hadoop fs命令上传文件（上传到hive表所在的目录下）

4.create table as select：

create table teacher1 as select * from teacher;

上述命令会按照select查询到的数据类型进行建表，让后把查询到的数据进行填充

5.create table like

create table teacher2 like teacher;

上述代码只会复刻表的结构不会复制数据

查看表：

SHOW TABLES [IN database_name] LIKE ['identifier_with_wildcards'];

查看表的基本信息：

DESCRIBE [EXTENDED | FORMATTED] [db_name.]table_name

EXTENDED：展示详细信息

FORMATTED：对详细信息进行格式化的展示

修改表：

修改信息只会修改元数据信息，HDFS文件是不会被修改的，所以文件需要手动处理

（1）重命名表：

ALTER TABLE table_name RENAME TO new_table_name

案例：

 alter table stu rename to stu1;

（2）修改列信息：

（1）添加列：

增加列默认情况下会增加到列的末尾

alter table stu1 add columns (age int);

（2）更新列：

该语句允许用户修改指定列的列名、数据类型、注释信息以及在表中的位置。

在修改表的时候会检验修改前后类型是否兼容（对比标准按照数据类型的转换进行对比）

我们也可以设置不进行检验（默认开启校验为true）

set hive.metastore.disallow.incompatible.col.type.changes=false;

alter table stu1 change age   age double comment 'age';

（3）替换列

该语句允许用户用新的列集替换表中原有的全部列。

替换列需要用到关键字replace

alter table stu1 replace columns (id int,name string);

删除表：

如果所删除的表是外部表，则只删除了元数据

drop table inc;

清空表：

清空表只对管理表（内部表），不能清空外部表，执行命令后HDFS中存储的数据就会全部清空

truncate table stu1;

hive的DDL操作至此结束！！！