Hive笔记2（Hive 数据类型，DDL 数据定义，DML 数据操作）

一、Hive 数据类型

1、基本数据类型

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

2、集合数据类型

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

3、案例实操

（1）假设某表有如下一行，我们用 JSON 格式来表示其数据结构。在 Hive 下访问的格式为

{ 
    "name": "songsong", 
    "friends": ["bingbing" , "lili"] ,       //列表Array,  
    "children": {                      //键值Map, 
        "xiao song": 18 , 
        "xiaoxiao song": 19 
    } 
    "address": {                      //结构Struct, 
        "street": "hui long guan", 
        "city": "beijing" 
    } 
    } 

（2）基于上述数据结构，我们在 Hive 里创建对应的表，并导入数据。

创建本地测试文件 test.txt 
songsong,bingbing_lili,xiao song:18_xiaoxiao song:19,hui long guan_beijing 
yangyang,caicai_susu,xiao yang:18_xiaoxiao yang:19,chao yang_beijing 

注意：MAP，STRUCT 和 ARRAY 里的元素间关系都可以用同一个字符表示，这里用“_”。

（3）Hive 上创建测试表 test

create table test( name string, friends array<string>, 
children map<string, int>, 
address struct<street:string, city:string> 
) 
row format delimited 
fields terminated by',' 
collection items terminated by '_' 
map keys terminated by ':' 
lines terminated by '\n'; 

字段解释：
row format delimited fields terminated by ‘,’ – 列分隔符 collection items terminated by ‘_’ --MAP STRUCT 和 ARRAY 的分隔符(数据分割符号)
map keys terminated by ‘:’ – MAP 中的 key 与 value 的分隔符
lines terminated by ‘\n’; – 行分隔符

（4）导入文本数据到测试表

Hadooop fs  -put test.txt      /user/hive/warehouse/test 

（5）访问三种集合列里的数据，以下分别是 ARRAY，MAP，STRUCT 的访问方式

hive (default)> select friends[1],children['xiao song'],address.city from test 
where name="songsong"; 

4、 类型转化

• 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。
BOOLEAN 类型不可以转换为任何其它的类型。

2）可以使用 CAST 操作显示进行数据类型转换例如 CAST(‘1’ AS INT)将把字符串’1’ 转换成整数 1；如果强制类型转换失败，如执行

CAST(‘X’ AS INT)，表达式返回空值 NULL。

二、DDL 数据定义

1、创建数据库

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

1）创建一个数据库，数据库在 HDFS 上的默认存储路径是/user/hive/warehouse/*.db。

hive (default)> create database db_hive; 

2）避免要创建的数据库已经存在错误，增加 if not exists 判断。（标准写法）

hive (default)> create database db_hive; 

FAILED: Execution Error, return code 1 from 
org.apache.hadoop.hive.ql.exec.DDLTask. Database db_hive already exists 

hive (default)> create database if not exists db_hive; 

3）创建一个数据库，指定数据库在 HDFS 上存放的位置

hive (default)> create database db_hive2 location '/db_hive2.db'; 

2、查询数据库

（1）显示数据库

1）显示数据库
hive> show databases;

2）过滤显示查询的数据库
hive> show databases like ‘db_hive*’;
OK
db_hive
db_hive_1

（2）查看数据库详情

1）显示数据库信息
hive> desc database db_hive;
OK
db_hive
hdfs://hadoop102:9820/user/hive/warehouse/db_hive.db
lyhUSER

2）显示数据库详细信息，extended
hive> desc database extended db_hive;
OK
db_hive
hdfs://hadoop102:9820/user/hive/warehouse/db_hive.db
lyhUSER

（3）切换当前数据库

hive (default)> use db_hive;

3、修改数据库

• 用户可以使用 ALTER DATABASE 命令为某个数据库的 DBPROPERTIES 设置键-值对属性值，
  来描述这个数据库的属性信息。

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

在 hive 中查看修改结果

hive> desc database extended db_hive; 

db_name comment location owner_name owner_type parameters
db_hive hdfs://hadoop102:9820/user/hive/warehouse/db_hive.db
atguigu USER {createtime=20170830}

4、删除数据库

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

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

FAILED: SemanticException [Error 10072]: Database does not exist: db_hive

hive> drop database if exists db_hive2;
3）如果数据库不为空，可以采用 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;

5、创建表

建表语法

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] --根据查询来建表，比如A表中有十个字段，B中有五个字段都是A表中有的，就可以通过这个语句来从A中选取五个字段去建B表 

字段解释说明
（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 允许用户复制现有的表结构，但是不复制数据。

（1）管理表

• 理论默认创建的表都是所谓的管理表，有时也被称为内部表。因为这种表，Hive 会（或多或少地）控制着数据的生命周期。Hive 默认情况下会将这些表的数据存储在由配置项 hive.metastore.warehouse.dir(例如，/user/hive/warehouse)所定义的目录的子目录下。
• 当我们删除一个管理表时，Hive 也会删除这个表中数据。管理表不适合和其他工具共享数据，元数据和hdfs中的都会被删掉。

（2）外部表

• 因为表是外部表，所以 Hive 并非认为其完全拥有这份数据。删除该表并不会删除掉这份数据，不过描述表的元数据信息会被删除掉，在hdfs中不会被删掉。
• 管理表和外部表的使用场景每天将收集到的网站日志定期流入 HDFS 文本文件。在外部表（原始日志表）的基础上做大量的统计分析，用到的中间表、结果表使用内部表存储，数据通过 SELECT+INSERT 进入内部表。

（2.1）案例实操

（0）原始数据

（1）上传数据到 HDFS
hive (default)> dfs -mkdir /student;
hive (default)> dfs -put /opt/module/datas/student.txt /student;

（2）建表语句，创建外部表

（3）查看创建的表
hive (default)>show tables;

（4）查看表格式化数据
hive (default)> desc formatted dept;
Table Type:
EXTERNAL_TABLE

（5）删除外部表
hive (default)> drop table dept;

外部表删除后，hdfs 中的数据还在，但是 metadata 中 dept 的元数据已被删除

（3）管理表与外部表的互相转换

（1）查询表的类型
hive (default)> desc formatted student2;
Table Type:
MANAGED_TABLE

（2）修改内部表 student2 为外部表

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

（3）查询表的类型
hive (default)> desc formatted student2;
Table Type:
EXTERNAL_TABLE

（4）修改外部表 student2 为内部表

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

（5）查询表的类型
hive (default)> desc formatted student2;
Table Type:
MANAGED_TABLE

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

（4）建表时指定分隔符

如下述代码row format delimited fields terminated by ‘\t’;
以\t分割字段内容，如果要用hadoop fs -put 上传文件到表中，文件内容必须也是用\t（tab键）隔开才能在表中显示内容，不然会出现null，上传的内容也要符合字段的数据类型，不然也会出现null

create external table if not exists dept( deptno int, dname string, loc int ) 
row format delimited fields terminated by '\t'; 

6、修改表

（1）重命名表

1）语法

ALTER TABLE table_name RENAME TO new_table_name

2）实操案例

hive (default)> alter table dept_partition2 rename to dept_partition3; 

（2）增加/修改/替换列信息

1）语法
（1）更新列

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

不能用int 替换 string（实测出来的）
（2）增加和替换列

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

注：ADD 是代表新增一字段，字段位置在所有列后面(partition 列前)，
REPLACE 则是表示替换表中所有字段。 replace灵活运用可以有add和change的功能

2）实操案例
（1）查询表结构

hive> desc dept; 

（2）添加列

hive (default)> alter table dept add columns(deptdesc string); 

（3）查询表结构

hive> desc dept; 

（4）更新列

hive (default)> alter table dept change column deptdesc desc string; 

（5）查询表结构

hive> desc dept; 

（6）替换列

hive (default)> alter table dept replace columns(deptno string, dname  string, loc string); 

（7）查询表结构

hive> desc dept; 

（3）删除表

hive (default)> drop table dept; 

三、DML 数据操作

1、数据导入

（1）向表中装载数据（Load）

1）语法

hive> load data [local] inpath '数据的path' [overwrite] into table student [partition (partcol1=val1,…)]; 

（1）load data:表示加载数据
（2）local:表示从本地加载数据到 hive 表；否则从 HDFS 加载数据到 hive 表 （第二种相当于剪切）
（3）inpath:表示加载数据的路径
（4）overwrite:表示覆盖表中已有数据，否则表示追加
（5）into table:表示加载到哪张表
（6）student:表示具体的表
（7）partition:表示上传到指定分区

2）实操案例
（0）创建一张表

hive (default)> create table student(id string, name string) row format delimited fields terminated by '\t'; 

（1）加载本地文件到 hive

hive (default)> load data local inpath 
'/opt/module/hive/datas/student.txt' into table default.student; 

（2）加载 HDFS 文件到 hive 中上传文件到 HDFS

hive (default)> dfs -put /opt/module/hive/data/student.txt 
/user/lyh/hive; 

加载 HDFS 上数据

hive (default)> load data inpath '/user/lyh/hive/student.txt' into table default.student; 

（3）加载数据覆盖表中已有的数据
上传文件到 HDFS

hive (default)> dfs -put /opt/module/data/student.txt /user/lyh/hive; 

加载数据覆盖表中已有的数据

hive (default)> load data inpath '/user/lyh/hive/student.txt' overwrite into table default.student; 

（2）通过查询语句向表中插入数据（Insert）

1）创建一张表

hive (default)> create table student_par(id int, name string) row format delimited fields terminated by '\t'; 

2）基本插入数据

hive (default)> insert into table  student_par values(1,'wangwu'),(2,'zhaoliu'); 

3）基本模式插入（根据单张表查询结果）

hive (default)> insert overwrite table student_par               select id, name from student where month='201709'; 

insert into：以追加数据的方式插入到表或分区，原有数据不会删除
insert overwrite：会覆盖表中已存在的数据
注意：insert 不支持插入部分字段

4）多表（多分区）插入模式（根据多张表查询结果） 不常用

hive (default)> from student 
insert overwrite table student partition(month='201707') 
select id, name where month='201709' 
insert overwrite table student partition(month='201706') 
select id, name where month='201709';

（3）查询语句中创建表并加载数据（As Select）

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

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

（4）创建表时通过 Location 指定加载数据路径

1）上传数据到 hdfs 上

hive (default)> dfs -mkdir /student; 
hive (default)> dfs -put /opt/module/datas/student.txt /student; 

2）创建表，并指定在 hdfs 上的位置

hive (default)> create external table if not exists student5( 
id int, name string 
) 
row format delimited fields terminated by '\t' 
location '/student';

3）查询数据

hive (default)> select * from student5; 

（5）Import 数据到指定 Hive 表中

注意：先用 export 导出后，再将数据导入。

hive (default)> import table student2   from '/user/hive/warehouse/export/student';