02 hive 数据类型、运算符、建库、建表

1 数据类型

1.1 基本类型

数据类型	大小	范围	示例
TINYINT	1byte	-128 ~ 127	100Y
SMALLINT	2byte	-32,768 ~ 32,767	100S
INT/INTEGER	4byte	-2,147,483,648 ~ 2,147,483,647	100
BIGINT	8byte	-9,223,372,036,854,775,808 ~ 9,223,372,036,854,775,807	100L
FLOAT	4byte	单精度浮点数	3.1415926
DOUBLE	8byte	双精度浮点数	3.1415926
DECIMAL	-	高精度浮点数	DECIMAL(9,8)
BOOLEAN	-	布尔型，TRUE/FALSE	true
BINARY	-	二进制类型	-

1.1.1数字类型

整数类型
-2,147,483,648 ~ 2,147,483,647之间的整数类型默认是INT型，除非指定了格式100Y、100S、100L会自动转换为TINYINT、SMALLINT、BIGINT

浮点数类型

​ 浮点数默认会当作DOUBLE型；

​ Hive中的DECIMAL基于Java中的BigDecimal，DECIMAL不指定精度时默认为DECIMAL(10,0)；

1.1.2 字符串类型

string （后面常用string）

类型可以用单引号（'）或双引号（"）定义，这个类型是以后我们定义字符串的常用类型。

varchar

​ varchar类型由长度定义，范围为1-65355，如果存入的字符串长度超过了定义的长度，超出部分会被截断。

​ 尾部的空格也会作为字符串的一部分，影响字符串的比较。

char
char是固定长度的，最大长度255，而且尾部的空格不影响字符串的比较。

三种类型对尾部空格的区别，参考如下例子，每个字段都插入同样的字符并且在尾部有不同的空格。

示例：

--建表
create table char_a (
c1 char(4),
c2 char(5),
str1 string,
str2 string,
var1 varchar(4),
var2 varchar(6));
--插入数据
insert into char_a values('ccc ','ccc  ','ccc ','ccc    ','ccc ','ccc   ');
--查询
select c1=c2,str1=str2,var1=var2 from char_a;

1.1.3 日期与时间戳

timestamp
timestamp表示UTC时间，可以是以秒为单位的整数；带精度的浮点数，最大精确到小数点后9位，纳秒级；java.sql.Timestamp格式的字符串 YYYY-MM-DD hh:mm:ss.fffffffff
Date
Hive中的Date只支持YYYY-MM-DD格式的日期，其余写法都是错误的，如需带上时分秒，请使用timestamp。

示例：

--建带有timestamp格式字段的表
create table time_dual1(time timestamp);
--插入一个数据（一个数据占一个文件）
insert into table time_dual1 values('2017-06-01 10:30:49.223');
--建带有date格式字段的表
create table date_dual(d date);
--插入一个数据（一个数据占一个文件）
--虽然命令没报错，但却没有将数据写入文件。
insert into table date_dual values('2017-05-31 12:18:48.807');
--将日期覆盖插入的date_dual（将目录下的所有都删掉，再写入）
insert overwrite table date_dual values('2017-05-31');

获取当前timestamp：

​ current_timestamp() --返回值: timestamp

获取当前日期：

​ current_date() --返回值：date

格式化timestamp/date/string 为字符串：

date_format(date/timestamp/string ts, string fmt) 
--返回值：字符串   

示例：

--当前日期格式化
select date_format(current_date(),'yyyy-MM-dd HH:mm:ss');
--当前时间格式化
select date_format(current_timestamp(), 'yyyy-MM-dd HH:mm:ss');
--字符串格式化
select date_format('2018-01-30 14:23:40', 'yyyy-MM-dd');

Unix时间戳：

​ 从1970-01-01 00:00:00 UTC到指定时间的秒数，例如：1530752400

【当前时间或时间字符串】 转 【Unix时间戳】

--获取当前timestamp的Unix时间戳
select unix_timestamp(current_timestamp);
--获取指定字符串的Unix时间戳
select unix_timestamp('2018-07-05 09:00:00');

【Unix时间戳】转【时间字符串】

from_unixtime(bigint unixtime[, string format])
--unixtime：从1970-01-01 00:00:00 UTC到指定时间的秒数
--format：目标转换格式
--返回值: string
--说明: 转化UNIX时间戳（从1970-01-01 00:00:00 UTC到指定时间的秒数）到当前时区的时间格式

示例：

--转换成 yyyy-MM-dd HH:mm:ss
select from_unixtime(1530755469);
--转换成指定格式的字符串
select from_unixtime(1530755469, "yyyy-MM-dd");

1.2 复杂类型

STRUCT

STRUCT类似于java的类变量使用，Hive中定义的struct类型也可以使用点来访问。从文件加载数据时，文件里的数据分隔符要和建表指定的一致。

struct(val1, val2, val3, ...) ，只有字段值；

named_struct(name1, val1, name2, val2, ...)，带有字段名和字段值；一般用struct都是带有 字段名和字段值

\
\ARRAY 相当于java的数组，通过arr[下标] 获取元素数据**

ARRAY表示一组相同数据类型的集合，下标从零开始，可以用下标访问。如：arr[0]

\
\MAP 相当于java的map**

MAP是一组键值对的组合，可以通过KEY访问VALUE，键值之间同样要在创建表时指定分隔符。

如：map_col['name']

Hive除了支持STRUCT、ARRAY、MAP这些原生集合类型，还支持集合的组合，不支持集合里再组合多个集合。

示例：

1）创建带有复合结构的表

CREATE TABLE complex
(
id int,
struct_col struct<name:string,country:string>,
array_col array<string>,
map_col map<string,string>,
union_col map<string,array<string>>
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
COLLECTION ITEMS TERMINATED BY '-'
MAP KEYS TERMINATED BY ':';

其中：

--这个子句表明hive使用字符‘,’作为列分隔符 
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' 
--这个子句表明hive使用字符 ‘-’ 作为集合元素间分隔符(一个字段各个item的分隔符)
COLLECTION ITEMS TERMINATED BY '-' 
--这个子句表明hive使用字符作为map的键和值之间分隔符
MAP KEYS TERMINATED BY ':'

2）插入数据

insert overwrite table complex
select
100,
NAMED_STRUCT('name','hainiu','country','cn') as struct_col,
array('99','21','33') as array_col,
map('english','aaaa') as map_col,
map('english',array('99','21','33')) as union_col;

3）用HQL查询

--查询struct
select struct_col.name from complex;
--查询数组第一个元素
select array_col[0] from complex;
--查询map中key对应的value值
select map_col['english'] from complex;
--查询复杂结构map<STRING,array<STRING>>中key对应的value值
select union_col['english'] from complex;
--查询复杂结构map<STRING,array<STRING>>中key对应的value值（数组）中的第一个元素
select union_col['english'][0] from complex;

             > dfs -ls /hive/warehouse/c32pan.db/complex;
Found 1 items
-rwxrwxrwx   2 xinniu supergroup         53 2021-06-28 16:23 /hive/warehouse/c32pan.db/complex/000000_0
hive (c32pan)> select * from complex;
OK
100     {"name":"hainiu","country":"cn"}        ["99","21","33"]        {"english":"aaaa"}      {"english":["99","21","33"]}
Time taken: 0.177 seconds, Fetched: 1 row(s)
hive (c32pan)> desc complex;
OK
id                      int                                         
struct_col              struct<name:string,country:string>                          
array_col               array<string>                               
map_col                 map<string,string>                          
union_col               map<string,array<string>>                           
Time taken: 0.042 seconds, Fetched: 5 row(s)
hive (c32pan)> 
             > select struct_col from complex;
OK
{"name":"hainiu","country":"cn"}
Time taken: 0.169 seconds, Fetched: 1 row(s)
hive (c32pan)> select struct_col.country from complex;
OK
cn
Time taken: 0.124 seconds, Fetched: 1 row(s)
hive (c32pan)> select struct_col.name from complex;
OK
hainiu
Time taken: 0.131 seconds, Fetched: 1 row(s)
hive (c32pan)> 
             > 
             > select array_col[1] from complex;
OK
21
Time taken: 0.407 seconds, Fetched: 1 row(s)
hive (c32pan)> select map_col['english'] from complex;
OK
aaaa
Time taken: 0.166 seconds, Fetched: 1 row(s)
hive (c32pan)> 
             > select union_col['english'][1] from complex;
OK
21
Time taken: 0.167 seconds, Fetched: 1 row(s)
hive (c32pan)> select union_col['english'] from complex;
OK
["99","21","33"]
Time taken: 0.141 seconds, Fetched: 1 row(s)
-- 如果key不存在，返回null
hive (c32pan)> select union_col['english1'] from complex;
OK
NULL

2 操作符(Relational Operators)

2.1 关系操作符

以下操作符比较操作数(operands)从而产生TRUE/FALSE值。

运算符	操作数	描述
A = B	所有基本类型	如果表达A等于表达B，结果TRUE ，否则FALSE。
A != B	所有基本类型	如果A不等于表达式B表达返回TRUE ，否则FALSE。
A < B	所有基本类型	TRUE，如果表达式A小于表达式B，否则FALSE。
A <= B	所有基本类型	TRUE，如果表达式A小于或等于表达式B，否则FALSE。
A > B	所有基本类型	TRUE，如果表达式A大于表达式B，否则FALSE。
A >= B	所有基本类型	TRUE，如果表达式A大于或等于表达式B，否则FALSE。
A IS NULL	所有类型	TRUE，如果表达式的计算结果为NULL，否则FALSE。
A IS NOT NULL	所有类型	FALSE，如果表达式A的计算结果为NULL，否则TRUE。
A LIKE B	字符串	TRUE，如果字符串模式A匹配到B，否则FALSE。关系型数据库中的like功能。
A RLIKE B	字符串	B是否在A里面，在是TRUE，否则是FALSE（B可以是Java正则表达式）
A REGEXP B	字符串	等同于RLIKE.

-- 模糊查询的
select if(struct_col.name='hainiu1',1,0),struct_col.name from complex where map_col['english'] like '%a%';
-- 正则匹配的
select if(struct_col.name='hainiu1',1,0),struct_col.name from complex where map_col['english'] RLIKE '\\S';
-- 判断为null
select if(struct_col.name='hainiu1',1,0),struct_col.name from complex where map_col['english1'] is null;
-- 判断不为空
select if(struct_col.name='hainiu1',1,0),struct_col.name from complex where map_col['english'] is not null;
hive (c33pan)> select map_col['english'] from complex;
OK
aaaa
Time taken: 0.082 seconds, Fetched: 1 row(s)
hive (c33pan)> select map_col['english'] is null from complex;
OK
false
Time taken: 0.076 seconds, Fetched: 1 row(s)
hive (c33pan)> select map_col['english'] is not null from complex;
OK
true
Time taken: 0.066 seconds, Fetched: 1 row(s)
hive (c33pan)> select map_col['english'] like 'a%' from complex;
OK
true
Time taken: 0.072 seconds, Fetched: 1 row(s)
hive (c33pan)> select map_col['english'] like '%a%' from complex;
OK
true
Time taken: 0.066 seconds, Fetched: 1 row(s)
hive (c33pan)> 
             > select 'asdfa21445' rlike '\\S';
OK
true
Time taken: 0.051 seconds, Fetched: 1 row(s)
hive (c33pan)> select 'asdfa21445' rlike '\\D';
OK
true
Time taken: 0.046 seconds, Fetched: 1 row(s)
hive (c33pan)> 
             > select 'asdfa21445' rlike '\\d'; -- 有数字
OK
true
Time taken: 0.063 seconds, Fetched: 1 row(s)
hive (c33pan)> select 'asdfa21445' rlike '\\s'; -- 有空白字符
OK
false
Time taken: 0.049 seconds, Fetched: 1 row(s)
hive (c33pan)> select if(1=1, 'ok', 'no ok');
OK
ok

if(struct_col.name='hainiu1',1,0)

2.2 算数运算符

这些运算符支持的操作数各种常见的算术运算。所有这些返回数字类型。

下表描述了在Hive中可用的算术运算符：

运算符	操作	描述
A + B	所有数字类型	A加B的结果
A - B	所有数字类型	A减去B的结果
A * B	所有数字类型	A乘以B的结果
A / B	所有数字类型	A除以B的结果
A % B	所有数字类型	A除以B.产生的余数
A & B	所有数字类型	A和B的按位与结果
A | B	所有数字类型	A和B的按位或结果
A ^ B	所有数字类型	A和B的按位异或结果
~A	所有数字类型	A按位非的结果

2.3 逻辑运算符

运算符是逻辑表达式。所有这些返回TRUE或FALSE。

运算符	操作	描述
A AND B	boolean	TRUE，如果A和B都是TRUE，返回true，否则FALSE。
A OR B	boolean	TRUE，如果A或B或两者都是TRUE，返回true，否则FALSE。
NOT A	boolean	TRUE，如果A是FALSE，返回true，否则FALSE。

算术运算符和逻辑运算符使用的位置是根据自己的使用情况去定。但是一定符合语法规则。

2.4 复杂的运算符

这些运算符提供一个表达式来接入复杂类型的元素。

运算符	操作	描述
A[n]	A是一个数组，n是一个int	它返回数组A的第n个元素，第一个元素的索引0。
M[key]	M 是一个 Map<K, V> 并 key 的类型为K	它返回对应于映射中关键字的值。
S.x	S 是一个结构体	它返回S的x字段

3 聚合函数

将多行聚合为一行

返回类型	函数	描述
BIGINT	count(*), count(expr),	count(*) - 返回检索行的总数。
DOUBLE	sum(col), sum(DISTINCT col)	返回该组或该组中的列的不同值的分组和所有元素的总和。
DOUBLE	avg(col), avg(DISTINCT col)	返回上述组或该组中的列的不同值的元素的平均值。
DOUBLE	min(col)	返回该组中的列的最小值。
DOUBLE	max(col)	返回该组中的列的最大值。

​ count(1) count(*) count(字段)

​ count(1) count(*) 是包含null值

​ count(字段) 不包含null值

​ count(1) 稍微比 count(*) 快点

hive -e 'sql语句';   -- 执行SQL语句（hql语句）
hive -f sql文件      -- 执行SQL文件（hql语句）
-- 将集群上的user_install_status前100条写入到文件
hive -e "use xinniu;select * from user_install_status_other where aid >='8d304' limit 100;" > ./data.txt 
-- 创建表
CREATE TABLE `user_install_status_limit`(
  `aid` string, 
  `pkgname` string, 
  `uptime` bigint, 
  `type` int, 
  `country` string, 
  `gpcategory` string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
;
--将导出的100条data数据放到表的hdfs目录
hadoop fs -put data.txt /hive/warehouse/xinniu.db/user_install_status_limit

其中：

aid:每个用户的标识

pkgname:用户安装某个手机应用的包名

uptime：更新时间

type:系统预装应用还是 用户自己装的应用

country：用户所在的国家

gpcategory:应用的类型，游戏类、社交类。。。

--  不同用户数                   统计用户id以8d304开头的记录数 
select count(DISTINCT aid), sum(if(aid like '8d304%',1,0)) from user_install_status_limit;
--------------------
--  统计用户id以8d304开头的记录数 
select sum(1) from user_install_status_limit where aid like '8d304%';
8d304aaaa   xxx             
8d305aaaa   xxx 
8d304bbbb   xxx 
8d304cccc   xxx 
--------------------
-- sum(每个国家的不同用户数)
select sum(a.n) from (
-- 统计每个国家的不同用户数
select count(DISTINCT aid) n from user_install_status_limit group by country
) a;
--------------------
-- 不同用户数
select count(1) from (
select 1 from user_install_status_limit group by aid
) a;
8d304aaaa   xxx             
8d304aaaa   xxx 
8d304bbbb   xxx 
8d304cccc   xxx 
--------------------
-- 查询按照国家分组后的最大值
select max(a.n) from (
select country,count(1) n from user_install_status_limit group by country
) a;
BR      5
CN      12
CO      3
max() --> 12 
-- 查询按照国家分组后的最大值和国家码
-- select * from 数据集1 t1 inner join 数据集2 t2  on t1.xx=t2.xx
select t1.maxn, t2.country from 
(
select max(a.n) as maxn from (
select country,count(1) n from user_install_status_limit group by country
) a
) t1 inner join 
(select country,count(1) n from user_install_status_limit group by country) t2 
on t1.maxn=t2.n;
select max(a.n) as maxn from (
select country,count(1) n from user_install_status_limit group by country
) a
12 
select country,count(1) n from user_install_status_limit group by country
BR      5
CN      12
CO      3
-- 查询按照国家分组后的最大值和国家码
--select * from 数据集1 t1 where t1.xx in (数据集2);
select t1.country, t1.n from 
(select country,count(1) n from user_install_status_limit group by country) t1 
where t1.n in (
select max(a.n) as maxn from (
select country,count(1) n from user_install_status_limit group by country
) a
);
-- group by 后查询的结果只能是 group by字段、聚合函数、常量
select country, count(*), 111 from user_install_status_limit group by country;

4 DDL（Data Definition Language）

数据定义包括schema的定义和查看，hive的主要DDL语句包括：

--创建语句
CREATE DATABASE/SCHEMA, TABLE, VIEW, FUNCTION, INDEX
--删除语句
DROP DATABASE/SCHEMA, TABLE, VIEW, INDEX
--清空语句
TRUNCATE TABLE
--修改语句
ALTER DATABASE/SCHEMA, TABLE, VIEW
--查看列表
SHOW DATABASES/SCHEMAS, TABLES, TBLPROPERTIES, PARTITIONS, FUNCTIONS, INDEX[ES], COLUMNS
--查看创建语句
SHOW CREATE TABLE
--查看结构语句
DESCRIBE DATABASE/SCHEMA, table_name, view_name

4.1 Hive索引机制

1）建立索引

​ 在指定列上建立索引，会产生一张索引表（Hive的一张物理表），里面的字段包括，索引列的值、该值对应的HDFS文件路径、该值在文件中的偏移量;

2）利用索引查询数据

​ ① 在执行索引字段查询时候，首先额外生成一个MR job，根据对索引列的过滤条件，从索引表中过滤出索引列的值对应的hdfs文件路径及偏移量，输出到hdfs上的一个文件中。

​ ② 然后根据生成的临时文件中的hdfs路径和偏移量，筛选原始input文件，生成新的split,作为整个job的split,这样就达到不用全表扫描的目的。

每次查询时候都要先用一个job扫描索引表，如果索引列的值非常稀疏，那么索引表本身也会非常大；

3）更新索引

​ 索引表不会自动rebuild，如果表有数据新增或删除，那么必须手动rebuild索引表数据；

-- 建立索引 （给 user_install_status_limit(aid) 建个索引表，索引名称：index_aid  ）
create index index_aid on table user_install_status_limit(aid) as   'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler' with deferred rebuild 
IN TABLE index_table_user;                   

-- 更新索引数据
alter index index_aid on user_install_status_limit rebuild;     
-- 设置输入文件不合并
SET hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat;
-- 自动开启索引
SET hive.optimize.index.filter=true;
SET hive.optimize.index.filter.compact.minsize=0;
-- 查看索引
SHOW INDEX on user_install_status_limit;    
-- 删除索引
DROP INDEX index_aid on user_install_status_limit;  

user_install_status_limit 表数据

select * from user_install_status_limit where aid = '8d30729cd242a479';

表里有两个文件，其中一个文件有 aid= '8d30729cd242a479', 一个文件没有

user_install_status_limit 没加索引时查询

select count(*) from user_install_status_limit where aid = '8d30729cd242a479';

使用索引时查询

步骤：

1）建立索引

2）更新索引

更新完成后，索引表就有了数据

3）查询

在不开启索引时查询，还是扫描全表

4）开启索引

不扫描全表：

---------------------------

使用时注意事项：

在离散字段上添加索引，当文件特别大的时候，建立索引表，索引表也特别大，当扫描索引表的时候，也会非常耗时，就降低了索引的性能；

4.2 数据库操作

创建数据库

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

其中：

DATABASE 和 SCHEMA 的使用是一样的，CREATE DATABASE 是(HIVE-675)增加的。

WITH DBPROPERTIES 是(HIVE-1836)增加的，可以指定一下数据属性数据。

--创建带有属性的数据库
create database testdb_otherinfo WITH DBPROPERTIES ('creator' = 'hainiu', 'date' = '2015-11-30');
--显示创建语句
show create database testdb_otherinfo;

操作示例：

show databases;

#默认有一个default名字的数据库。

--创建一个名字为test的数据库
create database test;
--显示所有数据库
show databases;
--如果已经存在会报错
create database test;
FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. Database test already exists
--可以增加if not exists 达到不存在再创建
create database if not exists test;
--describe命令（简写 desc）查看数据库定义元数据相关信息，可以查看数据location地址
describe database test;
--显示创建语句
show create database test;

--使用数据库
use hainiu;
--查看当前数据库
select current_database();

查看当前数据库也可以看hive后面的括号()内的内容

--切换到test数据库
use test;
select current_database();

也可通过 hive.cli.print.current.db 参数将当前数据打印到CLI提示符

set hive.cli.print.current.db=true;

也可以直接配置到hive-site.xml中永久生效，set命令保障当前session生效

修改数据库

ALTER (DATABASE|SCHEMA) database_name SET DBPROPERTIES (property_name=property_value, ...);

ALTER (DATABASE|SCHEMA) database_name SET OWNER [USER|ROLE] user_or_role;

删除数据库

DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];

删除数据库是会校验数据库下是否有表存在，如果在RESTRICT 模式有表存在则不能删除，修改为CASCADE模式可以级联删除数据库和数据库下所有表。删除操作要谨慎，删除前最好做一下检测和备份。

4.3 创建表

4.3.1 创建表语法

建表格式：

-- EXTERNAL 代表外部表                                  
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] 
-- 表所对应的hdfs目录
  [LOCATION hdfs_path]  

其中：

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

2）EXTERNAL 关键字可以让用户创建一个外部表，在建表的同时指定一个指向实际数据的路径（LOCATION）；

3）COMMENT可以为表与字段增加描述

4）ROW FORMAT

-- 分隔符设置
-- 字段间分隔符
DELIMITED [FIELDS TERMINATED BY char]
-- 集合间分隔符
[COLLECTION ITEMS TERMINATED BY char]
-- map k v 间分隔符
[MAP KEYS TERMINATED BY char] 
-- 行分隔符
[LINES TERMINATED BY char]
     --序列化和反序列化设置
   | SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]

​ SerDe是Serialize/Deserilize的简称，目的是用于序列化和反序列化。

​ Serialize把hive使用的java object转换成能写入hdfs的字节序列，或者其他系统能识别的流文件。

​ Deserilize把字符串或者二进制流转换成hive能识别的java object对象。

​ 用户在建表的时候可以自定义 SerDe 或者使用自带的 SerDe。如果没有指定 ROW FORMAT 或者 ROW FORMAT DELIMITED，将会使用自带的 SerDe。在建表的时候，用户还需要为表指定列，用户在指定表的列的同时也会指定自定义的 SerDe，Hive 通过 SerDe 确定表的具体的列的数据。

5）STORED AS

​ SEQUENCEFILE --包含键值对的二进制的文件存储格式，支持压缩，可以节省存储空间

​ | TEXTFILE --最普通的文件存储格式，内容是可以直接查看（默认的）

​ | AVRO --带有schema文件格式的， 一行的数据是个map，添加字段方便

​ | RCFile --是列式存储文件格式，适合压缩处理。对于有成百上千字段的表而言，RCFile更合适。

​ | ORC -- 带有压缩和轻量级索引, 一行数据是个数组，查询快，不适合添加字段

​ | INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname

​ 如果文件数据是纯文本，可以使用 STORED AS TEXTFILE。

​ 如果数据需要压缩，使用 STORED AS SEQUENCEFILE、RCFile、ORC 。

​ TEXTFILE对应的是

​ org.apache.hadoop.mapred.TextInputFormat和

​ org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat。

​ SEQUENCEFILE对应的是

​ org.apache.hadoop.mapred.SequenceFileInputFormat和

​ org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat。

​ RCFILE对应的是

​ org.apache.hadoop.hive.ql.io.RCFileInputFormat和

​ org.apache.hadoop.hive.ql.io.RCFileOutputFormat。

RCFile (适合列多的， 支持压缩，连续相同数据不重复存储)

​ RCFile文件格式是FaceBook开源的一种Hive的文件存储格式（可压缩），首先将表分为几个行组，对每个行组内的数据进行按列存储，每一列的数据都是分开存储，正是先水平划分，再垂直划分的理念。

​ 在一般的行存储中 select name from table，虽然只是取出一个字段的值，但是还是会遍历整个表，所以效果和select * from table 一样，在RCFile中，像前面说的情况，只会读取该行组的一行。

​ 在存储空间上：

​ RCFile采用游程编码，连续相同的数据不会重复存储，很大程度上节约了存储空间，尤其是字段中包含大量重复数据的时候。

​ 懒加载：

​ 数据存储到表中都是压缩的数据，Hive读取数据的时候会对其进行解压缩，但是会针对特定的查询跳过不需要的列，这样也就省去了无用的列解压缩。

​ 比如： select name from student where age > 10 ， 只有当前行组的 age>10，才会去解压 name。

hive表类型上可以分以下三种：

1）临时表 CREATE TEMPORARY：临时表是session内可见，将数据临时存在scratch目录，session退出后表和数据自动删除。

2）内部表 CREATE：不指定类型默认为内部表，内部表的特点是删除表时会同时删除表数据

3）外部表 CREATE EXTERNAL：创建表时指定数据目录，删除表时不会删除表数据

4.3.2 内部表与外部表

建表：

内部表 不需要指定location；

外部表： 指定 external， location

删表：

内部表： 元数据和数据都删除

外部表：只删除元数据

内部表

--创建inner_test 表
CREATE TABLE inner_test(word string, num int); 
--显示创建表语句
show create table inner_test;

创建内部表，不需要指定 location，在数据库下面产生表目录

删除内部表，表对应的hdfs目录也一并删除

外部表

--创建外部表的语法格式
CREATE EXTERNAL TABLE ext_test(
col_name data_type ...)
 COMMENT 'This is ext_testtable'
 ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001' 
 STORED AS TEXTFILE
 LOCATION '<hdfs_location>';

例子：

--创建外部表ext_test
--使用字符‘\001’作为列分隔符
--文件数据是纯文本
--数据存放的hdfs地址：/user/xinniu/hive26/ext_test'
CREATE EXTERNAL TABLE ext_test(
word string, num int)
 COMMENT 'This is ext_testtable'
 ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001'
 STORED AS TEXTFILE
 LOCATION '/user/xinniu/hive26/ext_test';

建表，指定location

------------------------

删除表，表对应hdfs目录不删除

4.3.3 创建分区表

创建分区表的好处是查询时，不用全表扫描，查询时只要指定分区，就可查询分区下面的数据。

分区表可以是内部表，也可以是外部表。

--建表格式
CREATE [EXTERNAL] TABLE par_test(
col_name data_type ...)
COMMENT 'This is the par_test table'
PARTITIONED BY(day STRING, hour STRING)
[ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' ]
[LINES TERMINATED BY  '\n']
[LOCATION '/user/hainiu/data/'];

其中：

1）添加单个partition 语法格式

--在表中添加单个partition，相当于hdfs:'/……/表名/20141117/00'
alter table 分区表 add IF NOT EXISTS partition(分区字段='值1',分区字段='值2') location '指定分区在HDFS上目录结构';

3）添加多个partition 语法格式

--在表中添加多个partition
--相当于hdfs:'/……/表名/20141117/00'
--相当于hdfs:'/……/表名/20141117/01'
alter table par_test add partition(day='20141117',hour='00') location '20141117/00' partition(day='20141117',hour='01') location '20141117/01';

4）查看表分区

show partitions tableName;

5）删除partition

alter table 表名 drop if exists partition(字段1='值1',字段2='值2');

示例：

1）创建分区表

CREATE EXTERNAL TABLE student_par(
name string
)
PARTITIONED BY (age int,sex STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
LOCATION '/user/cz45/hive45/student_par';

2）给分区表添加一个分区

--添加partition
alter table student_par add IF NOT EXISTS partition(age=10,sex='boy') location '10/boy';

注意：

​ add partitions的时候，如果不指定LOCATIO路径就给你创建一个

​ 分区表的数据要放在分区中，否则是查询不到。

如果想查询哪个分区对应location，可以通过SQL元数据查询

select t1.`NAME`, t2.TBL_NAME,t4.PART_NAME, t3.LOCATION from DBS t1, TBLS t2 , SDS t3 , `PARTITIONS` t4 
where t1.DB_ID=t2.DB_ID and 
            t4.SD_ID = t3.SD_ID AND 
            t2.TBL_ID = t4.TBL_ID and 
            t1.`NAME` = 'dbname' AND 
      t2.TBL_NAME like '%tablename' 

集群提供的hive 元数据查询连接

ip: 192.168.88.195
用户名:hive_r

密码： 12345678

3）给分区表添加多个分区

--再加两个分区
alter table student_par add IF NOT EXISTS partition (age=11,sex='boy') location '11/boy' partition (age=11,sex='girl') location '11/girl';

4）如何查看任务扫描输入目录

mapreduce.input.fileinputformat.inputdir

--分区表，不扫描全表

select count(*) from student_par where age=10 and sex=boy;

history → configuration ， 输入 inputdir

-- 扫描所有分区

select count(*) from student_par ;

注意：要想实现这么个结果，需要把所有分区数据都填一下

应用

后面有数仓项目，处理数据采用增量处理方式，都是分区表

4.3.4 创建AVRO表

schema文件，用json类型表示。

{
    "type": "record",
    "name": "RunRecord",
    "namespace": "com.hainiu",
    "fields": [{
            "name": "word",
            "type": "string"
        }, {
            "name": "num",
            "type": "long"
        }
    ]
}

--将schema文件放到指定的hdfs目录上面

/user/xinniu/hive26/config/avro.schema

--根据avro.schema.url 对应的目录下的schema文件，创建与文件格式相对应的表结构
CREATE EXTERNAL TABLE IF NOT EXISTS word_avro
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.avro.AvroSerDe'
WITH SERDEPROPERTIES ('avro.schema.url'='/user/cz45/hive45/config/avro.schema')
STORED AS avro
LOCATION '/user/xinniu/hive26/word_avro';

造了一个word_table 表，通过查询word_table 表把数据导入到word_avro 表里

create table word_table(word string, num bigint);

导入word_table 表的数据：

-- 导入到 word_avro 表里

insert overwrite table word_avro

select word,num from word_table;

查询结果：

查看了一下 avro表的文件内容，发现内容是看不懂的数据

括号里面是这文件的schema

--------------------------

avro 表如何增加字段？

修改schema，上传到hdfs，就可以了

增加数据

insert into table word_avro

select word,50,num from word_table;

总结：

如果新增字段，需要给新增字段设置默认值，否则查询会报错。

优点：后续数据的字段扩展不影响以前表的使用，或者后续表的修改不影响读取以前的数据。

缺点：做在数据里面存在冗余的数据，会使数据的文件变的很大。

应用场景：最原始的etl数据使用，因为最原始的数据经常变动结果。使用这种数据格式不受影响。

5 ORC文件（有索引有压缩）

5.1 定义

　　ORC File，它的全名是Optimized Row Columnar (ORC) file，其实就是对RCFile做了一些优化。据官方文档介绍，这种文件格式可以提供一种高效的方法来存储Hive数据。它的设计目标是来克服Hive其他格式的缺陷。运用ORC File可以提高Hive的读、写以及处理数据的性能。
ORC File格式最主要的优点是在文件中存储了一些轻量级的索引数据。

5.2 ORC File文件结构

　　ORC File包含一组组的行数据，称为stripes，除此之外，ORC File的file footer还包含一些额外的辅助信息。在ORC File文件的最后，有一个被称为postscript的区，它主要是用来存储压缩参数及压缩页脚的大小。在默认情况下，一个stripe的大小为250MB。大尺寸的stripes使得从HDFS读数据更高效。

　下图显示出可ORC File文件结构：

其中：

​ Postscripts中存储该表的行数，压缩参数，压缩大小，列等信息；

​ FileFooter中包含该表的统计结果，以及各个Stripe的位置信息；

​ Stripe Footer中包含该stripe的统计结果，包括Max，Min，count等信息；

​ IndexData中保存了该stripe上数据的位置信息，总行数等信息；

​ RowData以stream的形式保存了数据的具体信息；

Hive读取数据的时候，根据FileFooter读出Stripe的信息，根据IndexData读出数据的偏移量从而读取出数据。

5.3 Stripe结构

　　从上图我们可以看出，每个Stripe都包含index data、row data以及stripe footer。Stripe footer包含流位置的目录；Row data在表扫描的时候会用到。
　　Index data包含每列的最大和最小值以及每列所在的行。行索引里面提供了偏移量，它可以跳到正确的压缩块位置。具有相对频繁的行索引，使得在stripe中快速读取的过程中可以跳过很多行，尽管这个stripe的大小很大。在默认情况下，最大可以跳过10000行。

5.4 ORC类型表与文本类型表的比对

5.4.1 ORC hql 语法及orc文件分析

1）建表 指定 stored as orc

2）在表属性（tblproperties）中可以设置以下属性参数；

3）导入数据后，可以通过hive 命令分析orc文件

hive --orcfiledump orc文件对应的hdfs目录

5.4.2 创建ORC表，导入数据

--查看user_install_status建表语句
show create table xinniu.user_install_status;

 --根据建表语句，创建user_install_status_other表
 CREATE EXTERNAL TABLE `user_install_status_other`(
`aid` string COMMENT 'from deserializer', 
`pkgname` string COMMENT 'from deserializer', 
`uptime` bigint COMMENT 'from deserializer', 
`type` int COMMENT 'from deserializer', 
`country` string COMMENT 'from deserializer', 
`gpcategory` string COMMENT 'from deserializer')
PARTITIONED BY (`dt` string)
ROW FORMAT SERDE 'org.apache.hadoop.hive.ql.io.orc.OrcSerde' 
STORED AS INPUTFORMAT 'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat' 
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat'
LOCATION 'hdfs://ns1/user/xinniu/user_install_status_other'
TBLPROPERTIES ('orc.compress'='SNAPPY', 'orc.create.index'='true');

导入数据

--建partition 分区
alter table user_install_status_other add if not exists partition (dt='20141228') location '20141228';
--给分区插入数据
insert overwrite table user_install_status_other partition(dt='20141228')
select
aid,
pkgname,
uptime,
type,
country,
gpcategory
from xinniu.user_install_status
where dt='20141228';

5.4.3 创建txt格式的表，导入数据

再建一个txt格式的表进行速度对比

--创建user_install_status_txt表
CREATE EXTERNAL TABLE `user_install_status_txt`(
`aid` string, 
`pkgname` string, 
`uptime` bigint, 
`type` int, 
`country` string, 
`gpcategory` string)
PARTITIONED BY (`dt` string)
STORED AS TEXTFILE
LOCATION 'hdfs://ns1/user/xinniu/user_install_status_txt';

导入数据

--建partition 分区
alter table user_install_status_txt add if not exists partition (dt='20141228') location '20141228';
--给分区插入数据
insert overwrite table user_install_status_txt partition(dt='20141228')
select
aid,
pkgname,
uptime,
type,
country,
gpcategory
from xinniu.user_install_status
where dt='20141228';

5.4.4 两种类型比较

查询对比

select * from user_install_status_other where dt='20141228' and aid='zw3et8hfnemzntgl';

select * from user_install_status_txt where aid='zw3et8hfnemzntgl';

在散列字段上，更能体现ORC结构的查询优势。

因为有索引，可以直接扫描orc文件，不需要执行mapreduce任务。

而txt没有索引，需要执行mapreduce任务，找到这条数据。

容量对比

5.4.5 hive可以用hdoop配置的压缩方式

具体参考core-site.xml

如何往textfile 文件中导入压缩格式的文件？

方法1：

-- 1）先设置压缩
--设置hive输出压缩
set hive.exec.compress.output=true;
set mapred.output.compress=true; 
set mapred.output.compression.codec=org.apache.hadoop.io.compress.GzipCodec; 
set io.compression.codecs=org.apache.hadoop.io.compress.GzipCodec; 
--2）再导入数据
insert overwrite table user_install_status_gz 
select
aid,
pkgname,
uptime,
type,
country,
gpcategory
from class43.user_install_status_txt
where dt='20141228'; 

导入数据的结果：

方法2：

CREATE EXTERNAL TABLE `user_install_status_gz`(
  `aid` string COMMENT 'from deserializer', 
  `pkgname` string COMMENT 'from deserializer', 
  `uptime` bigint COMMENT 'from deserializer', 
  `type` int COMMENT 'from deserializer', 
  `country` string COMMENT 'from deserializer', 
  `gpcategory` string COMMENT 'from deserializer')
STORED AS TEXTFILE 
LOCATION
  'hdfs://ns1/user/xinniu/hive/user_install_status_gz';

将文本文件，直接用gzip压缩，再上传到表的hdfs目录上。

6 Bucket（桶）表使用

对于每一个表或者分区来说，可以进一步组织成桶，其实就是更细粒度的数据范围。

Bucket是对指定列进行hash，然后根据hash值除以桶的个数进行求余，决定该条记录存放在哪个桶中。

公式：whichBucket = hash(columnValue) % numberOfBuckets

hive桶表最大限度的保证了每个桶中的文件中的数据量大致相同，不会造成数据倾斜。

总结：桶表就是对一次进入表的数据进行文件级别的划分。

6.1 创建桶表

--创建桶表的语法格式
CREATE [EXTERNAL] TABLE buckets_table(
col ....)
COMMENT 'This is the buckets_table table'
[PARTITIONED BY (`dt` string)]
CLUSTERED BY(col1) [SORTED BY(col2 [asc|desc])] INTO 2 BUCKETS
[LOCATION '.....']

对user_install_status_other 表中 20141228分区 的数据按照国家分桶，怎么分合适？

select country, count(*) as num from user_install_status_other where dt='20141228' group by country order by num desc;

示例：

,.

--创建外部表桶表,桶的数量
CREATE EXTERNAL TABLE user_install_status_buckets(
`aid` string, 
`pkgname` string, 
`uptime` bigint, 
`type` int, 
`country` string, 
`gpcategory` string)
COMMENT 'This is the buckets_table table'
PARTITIONED BY (`dt` string)
CLUSTERED BY(country) SORTED BY(uptime desc) INTO 42 BUCKETS
LOCATION 'hdfs://ns1/user/xinniu/hive26/user_install_status_buckets';
--建partition 分区
alter table user_install_status_buckets add if not exists partition (dt='20141228') location '20141228';
--给分区插入数据
insert overwrite table user_install_status_buckets partition(dt='20141228')
select
aid,
pkgname,
uptime,
type,
country,
gpcategory
from user_install_status_other
where dt='20141228';

导入数据时，因为分42个桶，产生42个reduce

查看表文件，一共42个文件：

拿个最小的文件查看内部：

文件内部按照uptime 降序排列

拿GM为例

该文件查看GM条数一样，说明GM都在这个桶里

注意

Hive 0.6.0以前需要设置以下参数，但当前版本不需要设置也可以分桶。

--可以根据bucket的个数的设置reducer的数量
set hive.enforce.bucketing=true;
--有排序的桶需要设置一下
set hive.enforce.sorting=true;

6.2 桶表抽样

​ 当数据量特别大时，对全体数据进行处理存在困难时，抽样就显得尤其重要了。抽样可以从被抽取的数据中估计和推断出整体的特性，是科学实验、质量检验、社会调查普遍采用的一种经济有效的工作和研究方法。

桶表抽样的语法如下：

table_sample: TABLESAMPLE (BUCKET x OUT OF y [ON colname])  

示例：

select * from buckets_table tablesample(bucket 11 out of 42 on country)

注意：
TABLESAMPLE语句可以跟在select…from…之后
桶的编号x从1开始计数

举例：

建表语句	查询语句	效果说明
create table () clustered by (user_id) into 32 buckets	select from .. TABLESAMPLE(BUCKET 3 OUT OF 16 ON user_id) select from .. TABLESAMPLE(BUCKET 3 OUT OF 64 ON user_id) select * from .. TABLESAMPLE(BUCKET 3 OUT OF 32 ON user_id)	建表语句有 clustered by,查询时sample字段与create的cluster by字段一致，可以直接返回对应桶的数据 取32/16=2个桶，分别是第3,3+16*1=19个桶 取32/64=1/2个桶，第3个桶的一半 取32/32=1个桶，第3个
create table () clustered by (user_id) into 32 buckets	select from .. TABLESAMPLE(BUCKET 3 OUT OF 32 ON user_name) select from .. TABLESAMPLE(BUCKET 3 OUT OF 32 ON rand())	查询时sample字段与create的cluster by字段不一致，需进行全表扫描，然后确认抽样数据 ，其中，rand()，是根据整条记录进行抽样
create table ()	select * from .. TABLESAMPLE(BUCKET 3 OUT OF 32 ON id)	建表时没有分桶，查询时需要全表扫描

桶表抽样，抽指定桶

select count(*) from user_install_status_buckets tablesample(bucket 11 out of 42 on country);

select count(*) from user_install_status_buckets tablesample(bucket 11 out of 21 on country);

普通表抽样，全盘扫描

select count(1) from user_install_status_txt tablesample(bucket 11 out of 42 on country);

结论：

​ 如果桶表分桶字段和抽样字段不一致，扫描全表

​ 如果表不是桶表，抽样扫描全表

​ 如果桶表分桶字段和抽样字段一致，不扫描全表