【Hive】DDL语句详解二-言无不尽

Hive–DDL

建表的关键：分区，分桶，切割，存储方式，存储位置，表属性

DDL建表关键总结：

LazySimpleSerDe建表

create [external] table 表名(
		字段名 字段类型  Comment'字段的描述信息',
  	字段名 字段类型  Comment'字段的描述信息',
    字段名 字段类型  Comment'字段的描述信息'  -- 最后一行没有逗号
)Comment '表的描述信息'
row format delimited 
fields terminated by ','
collection items terminated by '#'
map keys terminated by ':'
lines terminated by ''   -- LazySimpleSerizlizer 4项结束

所有关键字汇总

create [external] table 表名(
		字段名 字段类型  Comment'字段的描述信息',
  	字段名 字段类型  Comment'字段的描述信息',
    字段名 字段类型  Comment'字段的描述信息'  -- 最后一行没有逗号
)Comment '表的描述信息'
partitioned by 中不存在的字段 字段类型)  -- 分区
clustered by (表中已有字段) sorted by (表中已有字段 desc/asc) into 桶的个数 buckets  -- 分桶
row format delimited fields terminated by ','  -- 切割
stored as orc  -- (stored as TestFile  行存储方式)  orc是列存储方式
location 'HDFS目录'
tblproperties ('orc.compress'='snappy');  -- 为snappy压缩协议，还有zlib,Gzip,Bzip等协议;

上篇文章说了DDL语句的基本类型，以及创建数据库，修改数据库，创建表，删除表，修改表字段，创建分区表（静态分区和动态分区），如何向表中插入数据(三种插入方式，严格来讲是5种方式),数据的导出(insert overwrite)，内部表和外部表的联系与区别，创建表的另外几种方法。这里书接上回，来讲讲DDL的分桶表(向分桶表中插入数据)，以及三种复杂类型(array，struct，map)

Bucket分桶表

文言文:

分桶表相关:
概述:
分桶 = 分文件.
目的/好处:

1. 把1个整体(大文件) 拆分成 n个小文件, 减少join的次数, 提高查询效率.
2. 方便采样, 抽样.
   细节:
3. 分桶字段必须是表中已有的字段.
4. 分桶表不能通过load方式加载数据, 因为要把文件拆分成n份, 所以必须通过 insert + select方式实现, 它的底层会转MR.
5. 分桶的原理: 哈希取模法
分桶的公式: 分桶字段的哈希值 % 桶的个数 = 结果, 结果是啥, 就进那个桶
   例如: 按照性别(男, 女)分桶, 分成2个桶, 执行流程如下:
   select abs(hash(‘男’)) % 2 => 0, 1
   select abs(hash(‘男’)) % 2 => 0, 1
6. 哈希值 就是 根据字段的内容计算出来的1个整数(可能是正数, 也可能是负数), 类似于: 每个学生都有自己的 学号一样.
7. 同一对象(内容)哈希值一定相同, 不同对象哈希值一般不同.
   例如: 重地和通话, 儿女和农丰… 去Java中执行.

创建分桶表

clustered by (gender) sorted by (age desc) into 2 buckets

将表按照性别分桶，并按照年龄排序(默认升序，放到两个桶中

create table stu_bucket(
    id int,
    name string,
    gender string,
    age int,
    sno string
)clustered by (gender) sorted by (age desc) into 2 buckets  -- 将表按照性别分桶，并按照年龄排序(默认升序，放到两个桶中
row format delimited fields terminated by ',';

分桶表和分区表的区别※

1.字段选择

分区字段必须是表中没有的字段，分桶字段必须是表中有的字段.

2.作用

分区=分文件夹 —> 降低扫描次数

分桶=分文件 —>减少join次数

相同点

都是提高了查询效率

数据源

放到百度网盘了

Array数据类型

掌握这种类型的建表字段语法就行了，arry<string, string> — 表明是数组存储方式，而且是string类型。

操作有，判断数组中是否包含’hangzhou’字符串，根据下标进行查找数组元素，查询数组addrs中元素的个数，

源文件中数据格式为: "zhangsan    beijing,shanghai,tianjin,hangzhou" 

建表

-- 创建test_array，通过在node:9870节点上传到HDFS中
create table test_array(
    name string,
    addrs array<string>  -- 这里使用 < 尖括号，string为数组内的数据格式
) row format delimited
    fields terminated by '\t'
    collection items terminated by ',' ;  -- 这里是切割集合中的元素

相关操作Operation

select * from test_array;

select name from test_array where array_contains(addrs,'hangzhou');  -- 判断数组中是否包含'hangzhou'字符串

select name,addrs,addrs[1] from test_array;  -- 根据下标进行查找数组元素

select size(addrs) from test_array;  -- 查询数组addrs中元素的个数

select * from test_array where array_contains(addrs, 'tianjin')=true;  -- 注意=true可以省略

Struct数据类型

-- 源文件中数据格式为: "1#周杰轮:11"   建表存储.

建表

reate table test_struct(
    name string,
    info struct<name:string,age:int>  -- 结构体, 即: 键值对形式, 可以有无数组键值对组合
)row format delimited
    fields terminated by '#'
    collection items terminated by ':';

相关操作Operation

select info.name,info.age from test_struct;  -- 通过info.来查询struct结构体的键和值

Map数据类型

掌握这种类型的建表字段语法就行了，map<string, string> — 表明是键值对存储方式，键和值都是string类型。

操作就比较多了，查询map集合中所有的键，查询map集合中所有的值，查询map类型的KV对数量，查看map_keys函数产生的数组是否包含某个元素，

原始数据为: "1,zhangsan,father:xiaoming#mother:xiaohuang#brother:xiaoxu,28"

建表

create table test_map(
    id int,
    name string,
    members map<string,string>  -- 键值对方式存储，键和值都是string类型
)row format delimited
    fields terminated by ','
    collection items terminated by '#'
    map keys terminated by ':';  -- 这里map的数据类型可以理解为 数组里面存储字典 类型的复合结构

相关操作

select members["mother"] from test_map;  -- 查询father、mother这两个map的key

select map_keys(members) from test_map;  -- 查询map集合中所有的键，使用map_keys函数 查询结果为数组形式

select map_values(members) from test_map;  -- 查询map集合中所有的值，使用map_values函数

select size(members) from test_map;  -- 查询map类型的KV对数量

select * from test_map where array_contains(map_keys(members),'brother');  -- 查看map_keys函数产生的数组是否包含某个元素

select * from test_map where array_contains(map_values(members), '如花');  -- 注意map_values函数产生的集合是个数组，可以使用arry_contains()