大表join小表，独钟爱mapjoin；

MapJoin通常用于一个很小的表和一个大表进行join的场景，具体小表有多小，由参数hive.mapjoin.smalltable.filesize来决定，该参数表示小表的总大小，默认值为25000000字节，即25M。

在0.7版本之后，默认自动会转换Map Join；

Mapjoin分为两个阶段：

1. 通过mapreduce local task，将小表读入内存，生成HashTableFiles上传至Distributed Cache中，这里会将HashTableFiles进行压缩。
2. MapReduce Job在map节点，每个mapper从distributed cache读取HashTableFiles到内存中，顺序扫描大表，在map阶段直接进行join，将数据传递给下一个mapreduce任务。

SQL在Join执行阶段会将Join Key相同的数据分发到同一个执行Instance上处理。如果某个Key上的数据量比较多，会导致该Instance执行时间比其他Instance执行时间长。执行日志中该Join Task的大部分Instance都已执行完成，但少数几个Instance一直处于执行中，这种现象称之为长尾。

小表长尾：使用mapjoin解决。

热点值长尾：可以取出热点数据和非热点数据分别处理，最后合并。

空值长尾：使用coalesce(left_table.key,rand()*9999)将key为空的情况赋予随机值，来避免空值造成长尾。

map长尾：map端读取文件不均匀造成长尾。想办法合并源文件，或者取消split每个小文件占一个spilt。只能调节splitsize增加mapper数量，使数据分片更小，期望获得更好分配。

reduce长尾：由于Distinct操作的存在，数据无法在Map端的Shuffle阶段根据Group By先做一次聚合操作,减少传输的数据量，而是将所有的数据都传输到Reduce端，当Key的数据分发不均匀时，就会导致Reduce端长尾，特别当多个Distinct同时出现在一段SQL代码中时，数据会被分发多次，不仅会造成数据膨胀N倍，也会把长尾现象放大N倍。

解决方法：使用group by替代distinct，group by以及存在，则将distinct拆分为另外的sql。

虽然hive提供了丰富的函数支持，但在实际业务中，数据往往难以被现有函数处理（比如用户日志的json数据，通用函数往往不能很好的处理），基于这种情况，hive提供了3个抽象接口。

• UDF操作作用于单个数据行，并且产生一个数据行作为输出。大多数函数都属于这一类（比如数学函数和字符串函数）。一对一。
• UDAF 接受多个输入数据行，并产生一个输出数据行。像COUNT和MAX这样的函数就是聚集函数。多对一。
• UDTF 操作作用于单个数据行，并且产生多个数据行，一个表作为输出。lateral view explore()。一对多。

自定义函数的实现流程：

继承对应的类（GenericUDF），在evaluate函数里面编写业务代码。

打包，并上传到服务器。

在hive中创建临时/永久函数，使用自定义函数类似hive自带的函数。

1. 内存数据库derby，安装小，但数据存在内存，不稳定，只有允许一个连接，重新进入hive数据不一样。一般不使用。
2. mysql数据库，数据存储格式可以自己设置，持久化好，查询方便，允许多个连接。一般使用这种。

内部表：加载数据到hive所在的hdfs目录中，删除时，元数据和数据文件都删除。

外部表：不加载数据到hive所在的hdfs目录，删除时，只删除表结构。（从hdfs角度可以删除外部表数据，所以使用hdfs操作需谨慎）。

外部表相对来说更加安全些，数据组织也更加灵活，方便共享源数据。

扩展：什么时候使用内部表，什么时候使用外部表

1. 每天采集的ng日志和埋点日志,在存储的时候建议使用外部表，因为日志数据是采集程序实时采集进来的，一旦被误删，恢复起来非常麻烦。而且外部表方便数据的共享。
2. 抽取过来的业务数据，其实用外部表或者内部表问题都不大，就算被误删，恢复起来也是很快的，如果需要对数据内容和元数据进行紧凑的管理, 那还是建议使用内部表。也就是说，即使这些业务数据是在原始数据层，也可以使用内部表。
3. 在做统计分析时候用到的中间表，结果表可以使用内部表，因为这些数据不需要共享，使用内部表更为合适。并且很多时候结果分区表我们只需要保留最近3天的数据，用外部表的时候删除分区时无法删除数据。

总结：恢复比较麻烦甚至难以恢复的数据，肯定需要使用外部表；其它时候更加业务需求判断是否选择内部表。

insert into：将数据写到表中（追加）

override write：将数据覆盖之前的内容

条件函数

1. if(boolean testCondition, T valueTrue, T valueFalseOrNull)
2. nvl(T)
3. coalesce(T v1, T v2, …)
4. case a when b then c [when d then e]* [else f] end
5. isnull(a)
6. isnotnull(a)

hive主要是做离线分析的。（扩展知识面试不问别回：可以使用spark引擎）

hive建表有三种方式

1. 直接建表
2. 查询建表法（通过AS查询语句完成建表：将子查询的结果存在新表，有数据，一般用于中间表）
3. like建表法（会创建结果完全相同的表，但是没有数据）

hive表有两种：内部表和外部表

ps：面试小技巧：不能给自己挖坑，面试官问什么答什么，除非是感觉自己是相关方面的大佬，否则不能随便引导话题（尤其是菜鸟，知识不够深，面试官多反问几次就直接破功了）

线上业务每天产生的业务日志（压缩后>=3G）,每天需要加载到hive的log表中，将每天产生的业务日志在压缩之后load到hive的log表时，最好使用的压缩算法是哪个，并说明其原因？

选择lzo，因为算法可以切分，压缩率比较高，解压缩速度很快，非常适合日志。

可以重新建表为分区分桶表（分区是创建不同目录，分桶是创建不同文件）

union 会对结果去重合并，同时进行默认规则的排序

union all不会对结果去重处理，不进行排序

表现：任务进度长时间维持在99%（或100%），查看任务监控页面，发现只有少量（1个或几个）reduce子任务未完成。因为其处理的数据量和其他reduce差异过大。

原因：某个reduce的数据输入量远远大于其他reduce数据的输入量

sql本身导致的倾斜

group by

1. 使group by的维度变得更细
2. 在原分组key上添加随机数聚合，然后对结果去掉随机数后再分组聚合
3. 在join时如果有大量null的join key，则可以将null转换成随机值，避免聚集

count(distinct)

使用group by替代，可以先将值为空的记录单独处理（特殊值），再和其它计算结果进行union

不同数据类型关联产生数据倾斜

情形：比如用户表中 user_id 字段为 int，log 表中 user_id 字段既有 string 类型也有 int 类型。当按照 user_id 进行两个表的 Join 操作时。

后果：处理此特殊值的 reduce 耗时；只有一个 reduce 任务

默认的 Hash 操作会按 int 型的 id 来进行分配，这样会导致所有 string 类型 id 的记录都分配到一个 Reducer 中。

解决方式：把数字类型转换成字符串类型

大小表的数据倾斜问题

map join

业务数据本身特性（存在热点key）

join的每路输入都比较大，且长尾是热点值导致的，可以对热点值和非热点值分别进行处理，再合并数据

key本身不均匀

可以在key上加随机数，或者增加reduce task数量（缩小粒度，期望数据变得均匀）

开启数据倾斜时负载均衡：set hive.groupby.skewindata=true;

思想：先随机分发并处理，在按照key group by来分发处理。

操作：当选项设定为 true，生成的查询计划会有两个 MRJob。

第一个 MRJob 中，Map 的输出结果集合会随机分布到 Reduce 中，每个 Reduce 做部分聚合操作，并输出结果，这样处理的结果是相同的 GroupBy Key 有可能被分发到不同的Reduce 中，从而达到负载均衡的目的；

第二个 MRJob 再根据预处理的数据结果按照 GroupBy Key 分布到 Reduce 中（这个过程可以保证相同的原始 GroupBy Key 被分布到同一个 Reduce 中），最后完成最终的聚合操作。

控制空值分布

将为空的key转变为字符串加随机数或村随机数，将因空值而造成倾斜的数据分布到多个Reduce。

ps：对于；异常值如果不需要的化，最好提前在where条件力过滤掉，这样可以式计算量大大减少

压缩和存储调优

存储格式+压缩算法（parquet/orc+snappy）。（选择那种存储格式还要考虑到cdh和hdp技术栈的支持）

1. map阶段输出数据压缩 ，在这个阶段，优先选择一个低CPU开销的算法。
2. 对最终输出结果压缩，也可以在hive建表时指定表的文件格式和压缩编码

合理利用分区分桶

分区是将表的数据在物理上分成不同的目录，以便查询时可以精准指定所要读取的分区目录，从而降低读取的数据量

分桶是将表数据按指定列的hash散列后分在了不同的文件中，将来查询时，hive可以根据分桶结构，快速定位到一行数据所在的分桶文件，从而提高读取效率。

参数调优

sql调优

1where条件优化

2union优化

尽量不要使用union（union去掉重复的记录），而是使用union all，然后再用group by去重。

3count distinct优化

count distinct，使用子查询

将distinct替换成group by实现。

4用in来代替join

如果需要根据一个表的字段来约束另一个表，尽量用in来代替join。in要比join快

使用in子查询

5优化子查询

消灭子查询内的group by、count(distinct),max,min。可以减少job的数量。

6join优化

reduce join：连接发生在reduce阶段，适用于大表join大表。

map join：连接发生在map阶段，适用于大表连接小表，大表数据从文件读取，小表数据放在内存中（hive进行了优化，自动判断小表然后进行缓存）。

SMB join：sort-merge-bucket join 对大表连接大表的优化，用桶表的概念来进行优化。在一个桶内发生笛卡尔积连接（需要是对两个桶表进行join）

数据倾斜调优

转如何解决数据倾斜问题。

小文件调优

小文件的产生有三个地方，map输入，map输出，reduce输出，小文件过多也会影响hive的分析效率：

设置map输入的小文件合并

设置map输出和reduce输出进行合并的相关参数：

delete：删除匹配where条件的数据，只能在支持ACID的表上使用。

drop：删除库、表、分区。如果库、表不为空，不能直接删除，需要清空或者加cascade关键字强行删除。

truncate：仅删除表中数据，保留表结构。不能删除外部表，如果的确需要删除外部表，可以将外部表转成内部表或者删除hdfs文件。

order by：

升序：asc，降序：desc，默认升序

对输入做全局排序，因此只有一个reducer（多个reducer无法保证全局有序），然而只有一个reducer会导致当输入规模较大时，消耗较长的计算时间。

distribute by

控制map结果的分发，它会将具有相同字段的map输出分发到一个reduce节点上做处理。即就是，某种情况下，我们需要控制某个特定行到某个reducer中，这种操作一般是为后续可能发生的聚集操作做准备。

sort by：

局部排序。sort by会根据数据量的大小启动一到多个reducer来干活，并且，它会在进入reduce之前为每个reducer都产生一个排序文件。这样的好处是提高了全局排序的效率。但并不保证全局有序。

sort by为每个 reduce产生一个排序文件。在有些情况下，你需要控制某个特定行应该到哪个 reducer，这通常是为了进行后续的聚集操作。distribute by刚好可以做这件事。因此， distribute by经常和 sort by配合使用。并且hive规定distribute by 语句要写在sort by语句之前。

cluster by

但distribute by和sort by所指定字段相同时，即可以使用cluster by。

hive全排序优化

select time from (select time from tablename distribute by time sort by time desc limit 50 ) t order by time desc limit 50;

首先执行子查询，然后order by全局查询。

Hive 里边字段的分隔符用的什么？为什么用 ？有遇到过字段里 边有 的情况吗，怎么处理的？为什么不用 Hive 默认的分隔符？

hive 默认的字段分隔符为 ascii 码的控制符001（^A，键盘ctrl+a）。

易于人类理解， 经常被使用为分割作用。

有遇到过，建表的时候用 fields terminated by ‘ ’，进行适配。（自定义 InputFormat，替换为其他分隔符再做后续处理）

默认的^A分隔格式不容易为人理解。

分区表：是目录。原来的一个大表存储的时候分成不同的数据目录进行存储。如果说是单分区表，那么在表的目录下就只有一级子目录，如果说是多分区表，那么在表的目录下有多少分区就有多少级子目录。不管是单分区表，还是多分区表，在表的目录下，和非最终分区目录下是不能直接存储数据文件的 。

分桶表：是文件。原理和hashpartitioner 一样，将hive中的一张表的数据进行归纳分类的时候，归纳分类规则就是hashpartitioner。（需要指定分桶字段，指定分成多少桶）。

分区和分桶的区别除了存储格式不一样之外，最主要的作用是：

1. 分区表：细化数据管理，缩小mapreduce程序需要扫描的数据量。
2. 分桶表：提高join查询的效率，在一份数据会被经常用来做连接查询的时候建立分桶，分桶字段就是连接字段；提高采样的效率。

有了分区为什么还要分桶？

1. 获得更高的查询处理效率。桶为表加上了额外的结构，hive在处理有些查询时能利用这个结构（使用分桶字段）。
2. 使取样（sampling）更高效。在处理大规模数据集时，在开发和修改查询的阶段，如果能在数据集的一小部分数据上试运行查询，会带来很多方便。
3. 分桶是相对分区进行更细粒度的划分。分桶将表或者分区的某列值进行hash进行区分，如要按照name属性分为3个桶，就是对name属性值的hash值对3取模，按照取模结果对数据分桶。
4. 与分区不同，分区依据的不是真实数据表文件中的列，而是我们指定的伪列（目录），但是分桶依据的是数据表中真实的列而不是伪列。

在map开始之前先将小表数据加载到distribute cache中，然后再map函数中join数据，join完成后，不再进行reduce阶段而是直接存储结果。

row_number() over(partition by 分区的字段 order by 排序的字段) as rank_id;

row_number() over(distribute by 分区的字段 sort by 排序的字段) as rank_id;

partition by只能和order by组合使用。

distribute by只能和sort by组合使用。

1. 数据的压缩与存储格式
2. 合理利用分区分桶
3. sql优化
4. 数据倾斜
5. 合并小文件
6. 查看sql的执行计划

外部表：在hive中删除表不会删除源数据，对数据安全，但对存储空间的消耗可能较大。

内部表（管理表）：删除表，也会同时删除元数据，对数据不那么安全，但可能比较适合临时表和可以快速恢复数据的表。

结论：恢复数据较难甚至不能恢复的肯定使用外部表，其余的情况为空间考虑建议使用内部表。

1. round(double d, int)：按精度取值函数
2. rand(int seed)：随机函数
3. abs(double d)：绝对值函数
4. cast(expr as ):类型转换函数
5. date_add：增加时间函数
6. next_day：下一个日期函数，常用下一个周一(MO)
7. month(string date)：取月
8. hour(string date)：取小时
9. date_format(date/timestamp/string ts, string fmt)：时间格式化函数
10. if(boolean testCondition, T valueTrue, T valueFalseOrNull)：判断函数
11. isnull(a)：是否为空函数
12. concat(string|binary A, string|binary B …)：字符串连接函数
13. split(string str, string pat)：字符串切分函数
14. count(*)：统计函数
15. sum(col)：求和函数
16. avg(col)：求平均值函数
17. max(col)/min(col)：求最大/小函数
18. explode(ARRAY array|MAP map|ARRAY a)：打散行的函数
19. lateral view：侧视图函数，经常结合explode使用，获取多个列的数据。

求连续3天登陆用户：

1、先排名；

2、然后用date对排名做差，差值相等（比如15-1=14，16-2=14，17-3=14）的就是连续几天登陆的用户；

3、根据user_id和date_diff分区，筛选连续登录大于等于3天的用户，为了方便统计可以将分区内的最小/大值取出，作为开始登陆时间/最后登陆时间。

这里求的连续登陆用户包括以前连续登陆但现在没有登陆的用户，如果要现在依然在登陆，可以选择最后登陆时间为现在时间的用户，即当前连续登陆3天的用户。

1. LEFT SEMI JOIN 是 IN/EXISTS 子查询的一种更高效的实现。
2. LEFT SEMI JOIN 的限制是， JOIN 子句中右边的表只能在 ON 子句中设置过滤条件，在 WHERe 子句、SELECt 子句或其他地方都不行。
3. 因为 left semi join 是 in(keySet) 的关系，遇到右表重复记录，左表会跳过，而 join 则会一直遍历。这就导致右表有重复值得情况下 left semi join 只产生一条，join 会产生多条，也会导致 left semi join 的性能更高。
4. left semi join 是只传递表的 join key 给 map 阶段，因此left semi join 中最后 select 的结果只许出现左表。因为右表只有 join key 参与关联计算了，而left join on 默认是整个关系模型都参与计算了

使用了reduce操作，shuffle自带排序操作。

因为distinct只使用一个reduce进行处理，而group by使用多个reduce进行处理，所以对于大数据（hive就是处理大数据的）能用group by的就不要使用distinct。

hive不怕数据大，就怕数据倾斜。

使用group by优化distinct操作，distinct只会使用一个reduce，而group by则会使用多个reduce。体会到了大数据不怕大数据量，最怕数据倾斜。

常见的优化思路：

1. 存储格式和数据压缩：存储格式主要有parquet和orc，选择哪种主要看其它整体架构支持哪个。数据压缩有snappy和lzo等。
2. 合理利用分区、分桶
3. hive参数优化
4. sql优化
5. 数据倾斜
6. 合并小文件
7. 查看hql的执行计划

mr引擎：计算模式简单，中间结果都需要进行落盘存储，比较慢，但性能稳定，配置要求低

spark引擎：采用弹性分布式数据集模型，中间结果缓存在内存中，迭代计算效率更高，DAG优化，速度比较快。

对于超大量数据的话，hiveOnSpark可能会有内存溢出的情况。

hive中的join可以分为两种情况：common join和map join，map join适合大表join小表，并且hive中实现了自动化寻找小表并使用map join。

1、common join（reduce阶段完成join）

整个过程包含map、shuffle、reduce阶段。

• map阶段：读取表的数据，map输出的key是join on条件中的列，如果join有多个关联键，则以这些关联键的组合作为key；输出的value为join之后需要输出或者作为条件的列，同时还会包含表的tag信息，用于标明此value对应的表，按照key进行排序
• shuffle阶段：根据key取hash值，并将key- value按照哈希值分发到不同的reduce中。
• reduce阶段：根据key的值完成join操作，并且通过tag来时表不同表中的数据，在合并过程中，将表编号扔掉。

2、map join（在map阶段完成join）

将小表加载到distribute cache中，然后在map阶段即可进行join。读取大表数据，然后读取distribute cache中的key-value，实现join。

1.从本地导入：

2.从hdfs导入

1. 用户提交查询等任务给Driver。
2. 编译器获得该用户的任务plan。
3. 编译器compiler根据用户任务去metaStore中获取需要的hive的元数据信息。
4. 编译器compiler得到元数据信息，对任务进行编译，先将hql转换为抽象语法树，然后将抽象语法树转换成查询块，将查询块转化为逻辑的查询计划，重写逻辑查询计划，将逻辑计划转化为物理的计划（mapreduce），最后选择最佳的策略。
5. 将最终的计划提交给Driver。
6. Driver将计划plan转交给ExecutionEngine执行，获取元数据信息，提交给resourcemanager执行该任务，任务会直接读取hdfs中的文件进行相应的操作
7. 取得并返回执行结果

一般存储在mysql中，优点是可以自己设置数据存储模式，持久化好，查询方便，多个连接。

from->join on->where->group by->select->having->order by->limit

from:需要从哪个数据表检索数据
join on:连接表，连接条件
where:过滤表中数据的条件
group by:将where过滤后的数据分组
select:查看结果集中的哪个列，或列的计算结果
having:对上面已经分组的数据进行过滤的条件
order by: 将某字段排序，返回，降序desc
limit:限制查询结果条数

ps：

1. select之后都可以使用别名（所以having的条件可以写select条件的别名）。
2. 子查询先走
3. 需要对查询后的结果进行处理，需要使用子查询。

1. on的过滤条件作用在join过程中，先根据条件过滤筛选，再连接生成临时查询结果。
2. where的过滤条件一般作用于from后面的表数据（包括join on），是先连接然后生成临时查询结果，然后再筛选。where比较依赖sql优化（谓词下推），相对自然连接加where，推荐使用join on操作。

ps：谓词下推：

优化关系 SQL 查询的一项基本技术是，将外层查询块的 WHERe 子句中的谓词移入所包含的较低层查询块（例如视图），从而能够提早进行数据过滤以及有可能更好地利用索引（分区）。

这在分区数据库环境中甚至更为重要，其原因在于，提早进行过滤有可能减少必须在数据库分区之间传递的数据量。

此优化技术在 SQL 中被称为谓词下推(Predicate pushdown) 。

1、写时模式和读时模式

传统数据库时写时模式，再load过程中，提升了查询性能，因为预先解析之后可以对列建立索引，并压缩，但这样也会花费更多的加载时间。

hive是读时模式，load data非常迅速，因为不需要读取数据进行解析，仅仅进行文件的复制或者移动。

2、数据格式

传统数据库中的存储引擎定义了自己的数据格式。所有数据都会按照一定格式进行组织存储。

hive没有定义专门的数据格式，而是由用户指定，需要指定三个属性：列分隔符，行分隔符，以及读取文件数据的方法。

3、数据更新

传统数据库中的数据通常需要经常进行修改。

hive的内容时读多写少的，因此不支持对数据的改写和删除，数据都在加载的时候确定好了。

4、执行延迟

传统数据库再处理小数据时执行延迟较低。

hive在查询数据的时候，需要扫描整个表（或分区），因此延迟较高，只有在处理大数据时才有优势。

5、索引

传统数据库有。hive比较弱，不适合实时查询。

6、执行

传统数据库时executor。hive是mapreduce

7、可扩展性

传统数据库低。hive高（hive的基础是hadoop集群）。

8、数据规模

传统数据库的数据规模小。hive的数据规模大。

1. 从本地导入：load data local inpath /home/hadoop/a.txt into table ods.test
2. 从hdfs导入：load data inpath /user/hive/warehouse/a.txt into ods.test
3. 查询导入：create table tmp_test as select name,age,sex from ods.test
4. 查询结果导入：insert into table tmp.test select name,age,sex from ods.test

select t1.c,t2.b from t1 join t2 on t1.id = t2.id

hive执行流程

编译器compiler得到元数据信息，对任务进行编译，先将hql转换为抽象语法树，

然后将抽象语法树转换成查询块，

将查询块转化为逻辑的查询计划，重写逻辑查询计划，

将逻辑计划转化为物理的计划（mapreduce），最后选择最佳的策略。

详看30题

spark sql执行流程

sql语句经过解析器Parser转换成未解析的逻辑计划，

再经过分析器Analyzer解析逻辑计划，

优化器optimizer优化逻辑计划，

经过转换器SparkPlan转换成可执行的物理计划。

区别：