近期在写SQL时得到的一些经验

约定不等于承诺〃 2022-12-22 00:51 124阅读 0赞

这阵子写SQL得到的一些经验，有些可能比较实用，有些可能比较笨重，但是总的来说都是减少了资源使用率，加快处理进程

## 1、在登录日志中拿到近n日日活 ##

想法：当时的想法很简单啦，就是日期锁定在近n天然后采用row\_number或者直接distinct进行去重计算uid即可

问题：但是当做的时候发现还是有点小问题，因为给出的模板是要有四列，日期、日活、七日活、三十日活，这四列数据都要围绕其中的日期这一条件进行对应，那怎么把n日活归并到当前日期来呢，也就是说把上周的周活数据放到昨天（T+1）的行中。。

实现：其实这里采用了一个比较笨的方法，在老同学昂的帮助下，使用一个日期维度join完成数据缝合  
1、首先有一个日期维度，或者说需要一个日期列，这里采用了表dim\_date来表示  
2、利用dim\_date作为左表，取前n天预备，关联去重后的登录表，on的条件为 datediff()<=n  
3、采用append的方式插入聚合表  
这样方法可能比较笨，但是做周活和月活，尤其是非自然周和非自然月时，十分有效且资源最大程度节省（?）

## 2、去除100partition 限制 ##

The maximum number of dynamic partitions is controlled by hive.exec.max.dynamic.partitions
    and hive.exec.max.dynamic.partitions.pernode. Maximum was set to: 100

这里是因为hive默认的partition数量是100，当你的查询语句将落地形成超过100个分区时就会报这个错误，解决方法也比较简单，set参数就好

SET hive.exec.max.dynamic.partitions=100000;   
    SET hive.exec.max.dynamic.partitions.pernode=100000;

## 3、去除最小内存限制 ##

org.apache.spark.SparkException: Job aborted due to stage failure: Task 0 in stage
    3.0 failed 4 times, most recent failure: Lost task 0.3 in stage 3.0 (TID 78, hdp57
    .car.bj2.yongche.com): org.apache.hadoop.hive.ql.metadata.HiveException: parquet
    .hadoop.MemoryManager$1: New Memory allocation 1046759 bytes is smaller than
    the minimum allocation size of 1048576 bytes.

设置参数

set parquet.memory.min.chunk.size=100000;

## 4、对留存计算的优化 ##

思路：做中间表，比如7日留存中间表记录当日的day-7留存数据，这样计算时就可以保证数据是点对点的，一天对应一天的数据，比如11月1日的七日留存，放在中间表11月8日的分区中，在进行提取时也比较简单方便，相较于之前的大量数据进行去重计算，要快的多，只不过是以append的方式进行添加数据了（之前是overwrite）

## 5、对于大小表join和小大表join的测试和理解 ##

之前经常有说法，将小表放左边，右表是大表的情况下，可以提高join效率，我想，最直接的提高效率就是相同资源下节省时间，于是开始了一波测试。

**我们暂时不考虑小表是否能放入内存；**

我准备了一张小表，我不确定小表的定义是什么，暂时我们称之为数据量小的表，于是我们的table\_1被我写入了两条数据，大小还不到1k，应该算是小表了吧

我又准备了一张大表，我也不确定大表的定义是什么，暂时我们称之为数据量大的表，于是table\_2 被我写入了1亿条数，大概10个G，应该算是大表了吧

**让我们开始进行愉快的实验吧！**

环境：hive on spark

set hive.exec.dynamic.partition.mode=nonstrict;
    set spark.serializer=org.apache.spark.serializer.KryoSerializer;
    set spark.executor.instances=3;
    set spark.executor.cores=8;
    set spark.executor.memory=10g;
    set spark.executor.memoryOverhead=2g;

select a.uid from table_1 as a left join table_2 as b on a.uid=b.uid

cost : 1m34s

select b.uid from table_2 as a left join table_1 as b on a.uid=b.uid

cost : 22.84s

神奇吧，小表在左居然比小表在右还要耗时  
可以参考链接：[https://blog.csdn.net/qq\_26442553/article/details/80865014][https_blog.csdn.net_qq_26442553_article_details_80865014]

## 6、资源不足以支持超大量的数据进行去重 ##

思路：可以考虑将去重键进行拆分，拆分后进行union合并，比如将uid%5= 0、1、2、3、4五个任务，对这五个任务进行去重计算，之后合并。

[https_blog.csdn.net_qq_26442553_article_details_80865014]: https://blog.csdn.net/qq_26442553/article/details/80865014