hive基础及表操作

集合数据类型

数据类型	描述	语法示例
STRUCT	和C语言中的struct或”对象”类似，都可以通过”点”符号访问元素内容。如，某列’desc’的数据类型是struct{time bigint,net string}，那么第一个元素可以通过 desc.time 来引用。	struct{‘1533777’,’4G’}
MAP	map是一组键值对元组集合，使用数组表示法（如[‘key’]可以访问元素）。如，某列’desc’的数据类型是map，键值对是’time’->’1533777’，’net’->’4G’，那么可以通过desc[‘net’]来引用。	map(‘time’,’1533777’,’net’,’4G’)
ARRAY	数组是一组具有相同类型和名称的变量的集合，这些变量成为数组的元素。每个数组元素都有一个编号，编号从0开始。如，某列’desc’的数组值为[‘1533777’,’4G’],则第二个元素可以通过desc[1]来访问。	array(‘1533777’,’4G’)

文本文件数据编码

下列是hive常用的分隔符。

分隔符	描述
\n	对文本文件，每行都是一条记录，可以通过换行符切割记录。
^A(ctrl+A)	用于分割字段（列）。在CREATE TABLE 语句中可以通过八进制编码\001表示。
^B	用于分隔Array或者struct中的元素，或用于Map中键-值对之间的分隔。在CREATE TABLE 语句中可以通过八进制编码\002表示。
^C	用于Map中键和值之间的分隔。在CREATE TABLE 语句中可以通过八进制编码\003表示。

CREATE TABLE employees (
    name STRING,
    salary FLOAT,
    subordinates ARRAY<STRING>,
    deductions MAP<STRING,FLOAT>,
    address STRUCT<street:STRING,city:STRING>)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\001'
COLLECTION ITEMS TERMINATED BY '\002'
MAP KEYS TERMINATED BY '\003'
LINES TERMINATED BY '\n'
STORED AS TEXTFILE;

修改表

表操作

• 删除表

DROP TABLE IF EXISTS tmp_xx;

• 清除表数据

TRUNCATE TABLE tmp_xx;

• 重命名

ALTER TABLE tmp_xx RENAME TO tmp_yy;

分区操作

• 增加分区

ALTER TABLE log_messages ADD IF NOT EXISTS 
PARTITION (year = 2018, month = 12, day= 1) LOCATION '/user/john/table/2018/12/01'
PARTITION (year = 2018, month = 12, day= 2) LOCATION '/user/john/table/2018/12/02'

• 修改分区

ALTER TABLE log_messages PARTITION(year = 2018, month = 12, day= 1)
SET LOCATION '/user/john/table/2018/12/01';

• 删除分区

ALTER TABLE log_messages DROP IF EXISTS PARTITION (year = 2018, month = 12, day= 1);

列操作

• 修改列

ALTER TABLE table_name CHANGE old_col_name new_col_name column_type;

• 增加/删除列

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

Join语句

笼统的说，Hive中的Join可分为Common Join（Reduce阶段完成join）和Map Join（Map阶段完成join）。

Reduce side Join

如果不指定MapJoin或者不符合MapJoin的条件，那么Hive解析器会将Join操作转换成Common Join,即：在Reduce阶段完成join.
整个过程包含Map、Shuffle、Reduce阶段。

• Map阶段
  读取源表的数据，Map输出时候以Join on条件中的列为key，如果Join有多个关联键，则以这些关联键的组合作为key;

  Map输出的value为join之后所关心的(select或者where中需要用到的)列；同时在value中还会包含表的Tag信息，用于标明此value对应哪个表；

  按照key进行排序

• Shuffle阶段
  根据key的值进行hash,并将key/value按照hash值推送至不同的reduce中，这样确保两个表中相同的key位于同一个reduce中

• Reduce阶段
  根据key的值完成join操作，期间通过Tag来识别不同表中的数据。

reduce-side-join 的缺陷在于会将key相同的数据发送到同一个partition中进行运算，大数据集的传输需要长时间的IO，同时任务并发度收到限制，还可能造成数据倾斜。

Map side join

MapJoin通常用于一个很小的表和一个大表进行join的场景，具体小表有多小，由参数hive.mapjoin.smalltable.filesize来决定，该参数表示小表的总大小，默认值为25000000字节，即25M。这种方法，要使用Hadoop中的DistributedCache把小数据分布到各个计算节点，每个map节点都要把小数据加载到内存，按关键字建立索引。

Hive0.7之前，需要使用hint提示 /+ mapjoin(table) /才会执行MapJoin,否则执行Common Join，但在0.7版本之后，默认自动会转换Map Join，由参数hive.auto.convert.join来控制，默认为true.

仍然以9.1中的HQL来说吧，假设a表为一张大表，b为小表，并且hive.auto.convert.join=true,那么Hive在执行时候会自动转化为MapJoin。

• 首先执行一个Local Task（在客户端本地执行的Task），负责扫描小表b的数据，将其转换成一个HashTable的数据结构，并写入本地的文件中，之后将该文件加载到DistributeCache。

• 一个没有Reduce的MR，启动MapTasks扫描大表a,在Map阶段，根据a的每一条记录去和DistributeCache中b表对应的HashTable关联，并直接输出结果。

• 由于MapJoin没有Reduce，所以由Map直接输出结果文件，有多少个Map Task，就有多少个结果文件。

参考文档：http://lxw1234.com/archives/2015/06/313.htm

left semi-join

hive中不支持in…exists结构，为解决这个问题可以使用 left semi-join。

--sql
select a.*
from table_a as a
where a.day in (select day
from table_b);

--hql
select a.*
from table_a as a LEFT SEMI JOIN table_b as b 
on a.key = b.key