HiveDDL

一、数据类型

1.基本数据类型

Hive数据类型	Java数据类型	长度	例子
TINYINT	byte	1byte有符号整数	20
SMALINT	short	2byte有符号整数	20
INT	int	4byte有符号整数	20
BIGINT	long	8byte有符号整数	20
BOOLEAN	boolean	布尔类型，true或者false	TRUE  FALSE
FLOAT	float	单精度浮点数	3.14159
DOUBLE	double	双精度浮点数	3.14159
STRING	string	字符系列。可以指定字符集。可以使用单引号或者双引号。	‘now is the time’ “for all good men”
TIMESTAMP		时间类型
BINARY		字节数组

对于Hive的String类型相当于数据库的varchar类型，该类型是一个可变的字符串，不过它不能声明其中最多能存储多少个字符，理论上它可以存储2GB的字符数。

2.集合数据类型

数据类型	描述	语法示例
STRUCT	和c语言中的struct类似，都可以通过“点”符号访问元素内容。例如，如果某个列的数据类型是STRUCT{first STRING, last STRING},那么第1个元素可以通过字段.first来引用。	struct() 例如struct<street:string, city:string>
MAP	MAP是一组键-值对元组集合，使用数组表示法可以访问数据。例如，如果某个列的数据类型是MAP，其中键->值对是’first’->’John’和’last’->’Doe’，那么可以通过字段名[‘last’]获取最后一个元素	map() 例如map<string, int>
ARRAY	数组是一组具有相同类型和名称的变量的集合。这些变量称为数组的元素，每个数组元素都有一个编号，编号从零开始。例如，数组值为[‘John’, ‘Doe’]，那么第2个元素可以通过数组名[1]进行引用。	Array() 例如array<string>

Hive有三种复杂数据类型ARRAY、MAP 和 STRUCT。ARRAY和MAP与Java中的Array和Map类似，而STRUCT与C语言中的Struct类似，它封装了一个命名字段集合，复杂数据类型允许任意层次的嵌套。

3.类型转化

Hive的原子数据类型是可以进行隐式转换的，类似于Java的类型转换，例如某表达式使用INT类型，TINYINT会自动转换为INT类型，但是Hive不会进行反向转化，例如，某表达式使用TINYINT类型，INT不会自动转换为TINYINT类型，它会返回错误，除非使用CAST操作。

1．隐式类型转换规则如下

（1）任何整数类型都可以隐式地转换为一个范围更广的类型，如TINYINT可以转换成INT，INT可以转换成BIGINT。

（2）所有整数类型、FLOAT和STRING类型都可以隐式地转换成DOUBLE。

（3）TINYINT、SMALLINT、INT都可以转换为FLOAT。

（4）BOOLEAN类型不可以转换为任何其它的类型。

2．可以使用CAST操作显示进行数据类型转换

例如CAST('1' AS INT)将把字符串'1' 转换成整数1；如果强制类型转换失败，如执行CAST('X' AS INT)，表达式返回空值 NULL。

select '1'+2, cast('1'as int) + 2;

二、 DDL

1.创建数据库

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

1)创建一个数据库，数据库在HDFS上的默认存储路径是

2.)创建一个数据库，指定数据库在HDFS上存放的位置

create database db_hive2 location '/db_hive2.db';

 2.查询数据库

1）显示数据库

show databases ;

2）过滤显示查询的数据库

show databases like 't*';

3.查看数据库详情

1）显示数据库信息

desc database default;

2）显示数据库详细信息，extended

desc database extended test;

 3）切换当前数据库

use test;

4.修改数据库

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

ALTER DATABASE test set DBPROPERTIES ("111"="222","444"="555");

在hive中查看修改结果

desc database extended test;

5.​​​​​​​​​​​​​​删除数据库

1）删除空数据库

drop test2;

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

drop database if exists test2;

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

drop database test2 cascade;

 

​​​​​​​6.创建表

1)建表语法

1.CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name 
#创建一个指定名字的表。IF NOT EXISTS 选项来忽略表已存在异常。
#EXTERNAL关键字可以让用户创建一个外部表，在建表的同时可以指定一个指向实际数据的路径（LOCATION），在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。

2.[(col_name data_type [COMMENT col_comment], ...)]  COMMENT：为表和列添加注释。
[COMMENT table_comment]  

3.[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]  #PARTITIONED BY创建分区表

4.[CLUSTERED BY (col_name, col_name, ...) #CLUSTERED BY创建分桶表

5.[SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS] #SORTED BY不常用，对桶中的一个或多个列另外排序

6.[ROW FORMAT row_format]		#列分隔符

7.[STORED AS file_format] 	#指定文件存储格式		

8.[LOCATION hdfs_path]		#指定表在hdfs的存储路径

9.[TBLPROPERTIES (property_name=property_value, ...)]	 #表的属性

10.[AS select_statement]	#用select结果来创建一个表

2)字段解释说明 

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

（2）EXTERNAL关键字可以让用户创建一个外部表，在建表的同时可以指定一个指向实际数据的路径（LOCATION），在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。

（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的简称， hive使用Serde进行行对象的序列与反序列化。

（8）STORED AS指定存储文件类型

常用的存储文件类型：SEQUENCEFILE（二进制序列文件）、TEXTFILE（文本）、RCFILE（列式存储格式文件）

如果文件数据是纯文本，可以使用STORED AS TEXTFILE。如果数据需要压缩，使用 STORED AS SEQUENCEFILE。

（9）LOCATION ：指定表在HDFS上的存储位置。

（10）AS：后跟查询语句，根据查询结果创建表。

（11）LIKE允许用户复制现有的表结构，但是不复制数据。

7.​​​​​​​管理表

1)普通创建表

create table test
(id int comment "ID",name string comment "Name")
comment "Test Table"
row format delimited fields terminated by '\t' #表的分列条件
location "/xxx" #指定hdfs下映射的名字及路径
tblproperties ("aaa"="bbb");

2)查看表的详细信息

desc formatted test;

# col_name	data_type	comment
id	int	ID
name	string	Name
# Detailed Table Information
Database:	test
OwnerType:	USER
Owner:	atguigu
CreateTime:	Fri Mar 11 17:42:55 CST 2022
LastAccessTime:	UNKNOWN
Retention:	0
Location:	hdfs://hadoop102:8020/xxx
Table Type:	MANAGED_TABLE
Table Parameters:
	COLUMN_STATS_ACCURATE	{\"BASIC_STATS\":\"true\",\"COLUMN_STATS\":{\"id\":\"true\",\"name\":\"true\"}}
	aaa	bbb
	bucketing_version	2
	comment	Test Table
	numFiles	0
	numRows	0
	rawDataSize	0
	totalSize	0
	transient_lastDdlTime	1646991775
# 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

3）重命名表

alter table test rename to test2;

4）​​​​​​​增加/修改/替换列信息

        （0）更新列信息

alter table test2 change id id string;

        （1）增加一列或多列

alter table test2 add columns (class string comment "class.NO");

        （2）重写列信息

alter table test2 replace columns (id double comment"ID",name string);

5）删除表

drop table test2;

6）用查询结果建表

create table stu_result as select * from stu_par where id=1001;

8.内外部表

1）外部表

当创建表的时候使用external关键字时就会建立一张外部表

create external table if not exists test(id int,name string);

hive只对表的元数据有管理权限，而对这张表的数据没有管理权限，数据的生命不由hive决定，在删表的时候只会删除掉表的元数据，对数据本身没有影响。

内部表向外部表转换

alter table test set tblproperties ("EXTERNAL"="TRUE");

外部表转换为内部表

alter table test set tblproperties ("EXTERNAL"="FALSE");

9.分区表

1.1）建立分区表

create table stu_par
(id int,name string)
partitioned by (class string)
row format delimited fields terminated by '\t';

1.2）向表中插入数据

#hdfs
load data inpath '/data/student.txt' [overwrite] into table stu_par partition(class='03');
#本地
load data [local] inpath '/opt/module/dates/student.txt' into table stu_par partition(class='01');

1.3）查表时，选择发分区，可以减少数据扫描量

select * from stu_par where class="01";

select * from stu_par where id=1001;

 1.4）查询分区表的分区

show partitions stu_par;

1.5）如果提前准备数据，但是没有元数据，修复方式

        （1）添加分区

alter table stu_par add partition (class="03");

         （2）命令行直接修复

msck repair table stu_par;

           (3)上传上传时带分区

load data local inpath '/opt/module/dates/student.txt' into table stu_par partition(class='01');

2.1）建立二级分区表

create table stu_par2
(id int,name string)
partitioned by (grade string, class string)
row format delimited fields terminated by '\t';

2.2)插入数据，指定到二级分区

load data local inpath '/opt/module/dates/student.txt' into table stu_par2 partition(grade='01',class='03');

3.分区的增删改查

3.1）增加分区

alter table stu_par add partition(class="05");

3.2）一次增加多个分区

alter table stu_par add partition (class="06") partition(class="07");

3.3）删除分区

alter table stu_par drop partition (class="05");

3.4）删除多个分区

alter table stu_par drop partition (class="06"),partition (class="07");