Hive（一）数据类型以及DDL数据定义

Hive数据类型

一、基本数据类型

Hive数据类型	Java数据类型
TINYINT	byte
SMALINT	short
INT	int
BIGINT	long
BOOLEAN	boolean
FLOAT	float
DOUBLE	double
STRING	string
TIMESTAMP
BINARY

对于Hive的String类型相当于数据库的varchar类型，该类型是一个可变的字符串。

二、集合数据类型

数据类型	描述	示例
STRUCT	相当于java语言当中没有方法的对象，只有属性。例如，如果某个列的数据类型是STRUCT{first STRING, last STRING},那么第1个元素可以通过字段.first来引用。	struct()
MAP	MAP是一组键-值对元组集合，使用数组表示法可以访问数据。例如，如果某个列的数据类型是MAP，其中键->值对是'first'->'John'和'last'->'Doe'，那么可以通过字段名['last']获取最后一个元素	map()
ARRAY	数组是一组具有相同类型和名称的变量的集合。这些变量称为数组的元素，每个数组元素都有一个编号，编号从零开始。例如，数组值为[‘John’, ‘Doe’]，那么第2个元素可以通过数组名[1]进行引用。	array()

三、类型转化

• 可以使用CAST操作显示进行数据类型转换
  CAST('1' AS INT)将把字符串'1'转换成整数1；如果强制类型转换失败，如执行CAST('X' AS INT)，表达式返回空值NULL。

DDL数据定义

一、创建数据库

• 创建一个数据库，数据库在HDFS上的默认存储路径是/opt/hive/warehouse/*.db
  create database if not exists db_name;
• 创建一个数据库，指定数据库在HDFS上存放的位置
  create database if not exists db_name location 'hdfs路径';

二、查询数据库

• 显示数据库
  show databases;
• 过滤显示查询的数据库
  show databases like 'hivetest*';
• 查看数据库详情
  desc database hivetest;
• 切换当前数据库
  use 目标数据库名称;

三、删除数据库

• 删除空数据库
  drop database 数据库名称;
• 如果删除的数据库不存在，最好采用if exists判断数据库是否存在
  drop database if exists 数据库名称;
• 如果数据库不为空，可以采用cascade命令强制删除
  drop database 数据库名称 cascade;

四、创建表

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]

补充
在hive中建表时，需要决定如何存储和读取表中的数据。数据在存储时需要被转换成一种格式（序列化），读取时需要将这种格式再转换成我们能理解的数据（反序列化）。为了完成这个转化，hive使用了一个叫做SerDe的工具。

• CREATE TABLE创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；用户可以用IF NOT EXISTS选项来忽略这个异常。
• EXTERNAL关键字可以让用户创建一个外部表，在建表的同时指定一个指向实际数据的路径（LOCATION），Hive创建内部表时，会将数据移动到数据仓库指向的路径；若创建外部表，仅记录数据所在的路径，不对数据的位置做任何改变。在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。
• COMMENT：为表和列添加注释。
• PARTITIONED BY创建分区表
• CLUSTERED BY创建分桶表
• SORTED BY不常用
• ROW FORMAT DELIMITED [FIELDS TERMINATED BY char] [COLLECTION ITEMS TERMINATED BY char] [MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]：通过定义ROW FORMAT来告诉hive我们想要自定义数据的存储格式或者用特定的方式存储复杂的数据结构，例如指定了字段（列）之间的分隔符、集合中各个元素之间的分隔符、记录（行）之间的分隔符
• STORED AS指定存储文件类型
  常用的存储文件类型：SEQUENCEFILE（二进制序列文件）、TEXTFILE（文本）、RCFILE（列式存储格式文件）
  如果文件数据是纯文本，可以使用STORED AS TEXTFILE。
  如果数据需要压缩，使用STORED AS SEQUENCEFILE。
• LOCATION：指定表在HDFS上的存储位置
• LIKE允许用户复制现有的表结构，但是不复制数据

1、内部表
默认创建的表都是所谓的管理表，有时也被称为内部表。Hive默认情况下会将这些表的数据存储在由配置项hive.metastore.warehouse.dir(例如，/opt/hive/warehouse)所定义的目录的子目录下。删除一个管理表时，Hive也会删除这个表中数据，管理表不适合和其他工具共享数据。

• 普通建表

create table if not exists student2(
id int, name string
)
row format delimited fields terminated by '\t';

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

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

• 根据已存在的表结构创建表

create table if not exists student4 like student;

• 查询表的类型

desc formatted student2;

2、外部表
删除外部表并不会删除掉这份数据，不过表述表的元数据信息会被删除掉。
（1）管理表和外部表的使用场景
假设一家电商公司的数据分析工程师，需要处理和分析大量的用户订单数据，这些数据每天都会生成，并且需要进行负责的分析和报告。创建管理表来存储最近一个月的用户订单数据，这些数据需要定期更新，旧数据将会被新数据替换。创建外部表来存储所有的历史订单数据，这些数据由另一个团队管理，并且存储在一个共享的HFDS中，该工程师需要对这些数据进行查询和分析，不需要管理数据的存储。
（2）管理表和外部表的相互转换

• 修改内部表student为外部表

alter table student set tblproperties('EXTERNAL'='TRUE');

• 修改外部表student为内部表

alter table student set tblproperties('EXTERNAL'='FALSE');

注意：('EXTERNAL'='TRUE')和('EXTERNAL'='FALSE')为固定写法，区分大小写！

五、分区表

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

create table dept_partition(
deptno int, dname string, loc string
)
partitioned by (month string)
row format delimited fields terminated by ',';

创建了一个分区表dept_partition，其中包含三个字段：部门编号（deptno）、部门名称（dname）和部门位置（loc）。此外，表通过month字段进行分区。
2、加载数据到分区表

load data local inpath '/opt/dept.txt' into table default.dept_partition partition(month='201707’);
load data local inpath '/opt/dept.txt' into table default.dept_partition partition(month='201708’);
load data local inpath '/opt/dept.txt' into table default.dept_partition partition(month='201709’);

3、查询分区表中的数据

• 单分区查询

select * from dept_partition where month='201709';

• 多分区联合查询

select * from dept_partition where month='201709'
union
select * from dept_partition where month='201708'
union
select * from dept_partition where month='201707';

4、增加分区

alter table dept_partition add partition(month='201706') ;

alter table dept_partition add partition(month='201705') partition(month='201704');

5、删除分区

alter table dept_partition drop partition (month='201704');

alter table dept_partition drop partition (month='201705'), partition (month='201706')

6、查看分区表有多少分区

show partitions dept_partition;

7、查看分区表结构

desc formatted dept_partition;

六、修改表

1、重命名表

ALTER TABLE table_name RENAME TO new_table_name

2、增加/修改/替换列信息
（1）更新列

ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name]

（2）增加和替换列

ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...) 

• ADD是代表新增一字段，字段位置在所有列后面(partition列前)，REPLACE则是表示替换表中所有字段。

3、删除表

drop table dept_partition;

• 外部表不能简单的通过这个命令删除，这个命令只能删除外部表的元数据，没有办法删除HDFS上面的数据
• 彻底删除外部表的方法：

（1）转换为内部表再删除

ALTER TABLE xxx SET TBLPROPERTIES('EXTERNAL'='False');
drop table xxx;

（2）删除元数据，使用HDFS删除数据