Hive学习视频心得（二）数据类型、DDL、内外表、分区表、DML

1、Hive的数据类型

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

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

3）、类型转化

①、隐式类型转换规则如下

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

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

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

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

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

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

0: jdbc:hive2://hadoop102:10000> select '1'+2, cast('1'as int) + 2;
+------+------+--+
| _c0  | _c1  |
+------+------+--+
| 3.0  | 3   |
+------+------+--+

2、库DDL

1）、建库实例

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

--例
create database test
comment "Just for test"
location '/test.db'
with dbproperties("aaa"="bbb");

★★★重点★★★

这里的/test.txt表示在hdfs上创建这个文件夹，记住这个文件夹就是代表的test这个库，该文件夹下并不会出现test文件夹！

2）、查询数据库

--显示数据库信息
hive> desc database db_hive;

--显示数据库详细信息，extended
hive> desc database extended db_hive;

--切换当前数据库
hive (default)> use db_hive;

3）、修改数据库

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

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

4）、删除数据库

--删除空数据库
hive>drop database db_hive2;

--如果删除的数据库不存在，最好采用 if exists判断数据库是否存在
hive> drop database db_hive;
FAILED: SemanticException [Error 10072]: Database does not exist: db_hive
hive> drop database if exists db_hive2;

--如果数据库不为空，可以采用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;

3、表DDL

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]
[TBLPROPERTIES (property_name=property_value, ...)]
[AS select_statement]

--建表实例
create table test
(id int comment "ID" , name string comment "Name")
comment "Test Table"
row format delimited fields terminated by '\t'
location "/xxx"
tblproperties("aaa"="bbb");

--用查询结果建表
create table stu_result as select * from stu_par where id=1001;

2）、查询表

desc test;
desc formatted test;

3）、修改表

--修改一列
alter table test2 change id id string;
--添加一列或多列
alter table test2 add columns(class string comment "class NO.");
--替换多列信息
alter table test2 replace columns(id double comment "ID" , name string);

4）、删除表

--删表，删完表后hdfs上的文件夹也就没有了
drop table test2;

4、内外部表

1）、外部表

当建表的时候加上external的关键字，会建立一张外部表

--举例
create external table test
(id int,name String)
row format delimited fields terminated by "\t";

--上传本地数数据
load data local inpath "/usr/local/so
ft/hive-1.2.1/data/student.txt" into table test;

--删除表，表的数据还在
drop table test;

2）、外部表和内部表相互转换

--举例
create table test
(id int,name String)
row format delimited fields terminated by "\t";

内部表转换为外部表

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

外部表转换为内部表

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

5、分区表

1）、建立分区表

--建立一张分区表
create table stu_par(id int ,name string) partitioned by (class string) row format delimited fields terminated by '\t';

2）、分区表插入数据

--向表中插入数据
load data local inpath '/usr/local/soft/hive-1.2.1/data/student.txt' into table stu_par partition (class='01');
load data local inpath '/usr/local/soft/hive-1.2.1/data/student.txt' into table stu_par partition (class='02');

3）、分区表的作用

--查表时，选择分区，可以减少数据扫描量
select * from stu_par where class="01";
select * from stu_par where id=1001;

4）、查询分区表分区

--查询分区表的分区
show partitions stu_par;

5）、分区表的修复方式

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

--1、添加分区
alter table stu_par add partition (class="02");

--2、直接修复
msck repair table stu_par;

--3、上传直接带分区
load data local inpath '/usr/local/soft/hive-1.2.1/data/student.txt' into table stu_par partition (class='03');

6）、二级分区表

--建立二级分区表（可以一级相同，二级不同）
create table stu_par2(id int,name string) partitioned by (grade string,class string)row format delimited fields terminated by '\t';

--插入数据，指定到二级分区
load data local inpath '/usr/local/soft/hive-1.2.1/data/student.txt' into table stu_par2 partition (grade='01',class='01');

7）、分区表的增删

--增加分区
alter table stu_par add partition(class="03");

--增加多个分区
alter table stu_par add partition(class="04") partition(class="05");

--删除分区
alter table stu_par drop partition(class="04");

--删除多个分区
alter table stu_par drop partition(class="04"),partition(class="05");

6、DML数据导入

1）、从本地磁盘或者DHFS导入

load data [local] inpath '/usr/local/soft/hive-1.2.1/data/student.txt' [overwrite] into table student [partition (partcol1=val1,...)];

--举例(person表追加导入本地数据)
load data local inpath '/usr/local/soft/hive-1.2.1/data/student.txt' into table person;

--举例(person表覆盖导入本地数据)
load data local inpath '/usr/local/soft/hive-1.2.1/data/student.txt' overwrite into table person;

--举例(先在hdfs的/xxx下上传一份文件student.txt)
--hdfs的导入是移动，而本地导入是复制
load data inpath '/xxx/student.txt' overwrite into table student;

2）、insert导入

insert into table test select id,name from stu_par where class="01";

3）、建表的时候用as select

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

4）、建表的时候通过location加载

--举例(先在hdfs的/xxx下上传一份文件student.txt)
create external table student2 (id int,name string) row format delimited fields terminated by '\t' location '/xxx';

7、DML数据导出

1）、insert导出

insert overwrite local directory '/usr/local/soft/hive-1.2.1/data/export/student' select * from student2;

2）、带格式导出

insert overwrite local directory '/usr/local/soft/hive-1.2.1/data/export/student1' row format delimited fields terminated by '\t' select * from student2;

3）、#bash命令行导出

hive -e "select * from test01.student2;" > /usr/local/soft/hive-1.2.1/data/export/student3/test.txt

4）、整张表全部导出到HDFS

--整张表export到HDFS
export table student2 to '/export/student';

--从导入结果导入Hive
import table student3 from '/export/student';

8、数据删除

--只删除数据，不删除表本身
--外部表无法用truncate
truncate table student2;