hive 数仓的设计

1.按天去划分表
通常是指在表名后加一个时间戳，例表名为test_2019_01_01或test20190101等。
对于hive来说这种情况下，一般使用分区表来完成，例：

CREATE TABLE supply(
id int,
part string,
quantity int)
PARTITIONED BY (day int);

ALTER TABLE supply ADD PARTITION (day = 20190101);
ALTER TABLE supply ADD PARTITION (day = 20190102);
ALTER TABLE supply ADD PARTITION (day = 20190103);

SELECT part, quantity FROM supply 
WHERE day >= 20190102 AND day <= 20190103 AND quantity < 4;

这样查询起来简单明了。

2.分区
hive的分区来说m，创建很多的分区可以优化一些查询，但同时可能会对其他一些查询不利，对一个表创建很多分区，最终可能超出NameNode的处理能力。
解决这种情况的一个好的方式是按照不同的时间粒度来确定合适大小的数据积累量，例：

CREATE TABLE weblogs (
url STRING,
time LONG,
state STRING,
city STRING)
PARTITIONED BY (day int);

SELECT * FROM weblogs WHERE day = 20190101;

另一种解决方式是使用两个级别的分区并且使用不同的维度，比如第一个分区按照day划分，二级分区可能按照州(state)这样的地理区域划分：

CREATE TABLE weblogs (
url STRING,
time LONG,
city STRING)
PARTITIONED BY (day INT, state STRING);

SELECT * FROM weblogs WHERE day = 20190101;

如果不能找到好的合适的分区方式，可以考虑使用分桶存储。

3.主键和标准化
关系型数据库一般使用主键索引和标准化来存储数据，但hive并没有主键或自增键。hive不标准化是为了最小化磁盘寻道，非标准化数据允许被扫描或写入磁盘存储区域，优化磁盘驱动器的I/O性能。但可能导致数据重复。

4.同一份数据多种处理
hive提供了一种语法，可以从一个数据源产生多个数据聚合，无需每次聚合都重新扫描一次。
例：

INSERT OVERWRITE TABLE sales
SELECT * FROM history WHERE action = 'ppp';

INSERT OVERWRITE TABLE credits 
SELECT * FROM history WHERE action = 'rrr';

从一个源表读取数据到两个不同的表中，语法正确，但执行效率低下，可以使用下面的SQL达到同样的目的：

FROM history
INSERT OVERWRITE TABLE sales SELECT * WHERE action = 'ppp'
INSERT OVERWRITE TABLE creaits SELECT * WHERE action = 'rrr';

5.分桶表数据存储
分桶是将数据集分解成更容易管理的一种方式。
假如一个表创建过多的分区，可能会导致一些SQL执行失败：

CREATE TABLE weblog (url STRING, source_ip STRING)
PARTITIONED BY (dt STRING, user_id INT);

FROM raw_weblog
INSERT OVERWRITE TABLE page_view 
PARTITION (dt = '2019-01-01', user_id)
SELECT server_name, url, source_ip, dt, user_id;

如果我们对表weblogs进行分桶，并使用user_id作为分桶字段，则字段值会根据用户指定的值进行哈希分发到桶中，同一个user_id下的记录通常会存储到同一个桶内。假设用户数要比桶数多得多，那么每个桶内就将会包含多个用户的记录：

CREATE TABLE weblog (user_id INT, url STRING, source_ip STRING)
PARTITIONED BY (dt STRING)
CLUSTERED BY (user_id) INTO 96 BUCKETS;

FROM raw_logs
INSERT OVERWRITE TABLE weblog
PARTITION (dt = '2019-01-01')
SELECT user_id, url, source_ip WHERE dt = '2019-01-01';

分桶的优点：桶的数量是固定的，没有数据波动，适合抽样，有利于执行高效的map-side JOIN。

hive学习