Skip to content

SQL tips

Info

作者：Void，发布于2021-08-18，阅读时间：约3分钟，微信公众号文章链接：

1 引言

SQL是操作数据库的语言。作为数据科学从业者，写SQL可能和干饭一样是我们每天必做的事情之一。SQL并不复杂，但是仍有一些细节，一些经验值得分享。
本文记录了在和数据库打交道的每一天中，觉得有用的一些经验及知识。

2 MySQL, SQL Server, Oracle

这三兄弟可以说是知名度最高，门槛较低的数据库，也适合个人，小数据量的存储。适合自己上手玩玩，存点感兴趣的数据，写点query找找feel。

3 Teradata, Snowflake

这两个数据库是工作中接触到的数据库。Teradata的一大特点是并行化，有别于Hadoop的Master-Slave结构，它基于大规模并行处理（MPP）架构，可以分配负载，分隔任务。

Teradata SQL的语法并没有太多不同，它没有limit，只能用top。另外，各子句的执行顺序为: 

where->join->group by->qualify->select

这边和Hive不同的是，Hive的多表join在where之前，同时Hive会把左表载入内存，所以我们需要先对每个表做筛选再join来优化查询。
关于Teradata的优化，我们要做的是最小化I/O，确保每个步骤过滤掉尽可能多的数据。

Snowflake是比较新的数据库，公司刚上市不久，之前所述的数据库都有几十年的历史了。它的特点是云数据库，在万物上云的今天，上云已经成为了一种必须。
除了它的技术架构比较新颖，对于用户体验来说，Snowflake有如下优点：

  • 功能繁多(一些EDA的图表等)，美观的UI。
  • 功能强大的查询语言，如支持qualify等，或者说没什么缺陷和硬伤。

4 Hadoop

在Hadoop生态圈中，用的较多是Hive和Spark。Hive是基于Hadoop的一个数据仓库工具，可以用Hive SQL执行MapReduce操作。目前大规模的数据存储(国内外互联网)感觉还是以Hadoop为主。
在工作中也用了一段时间的Hive了，这里总结下踩过的坑。

  • Hive的子查询一定要有别名。
select * from
(select * from table) a
  • Hive中的limit会在所有逻辑执行完后才limit，可以使用如下设置优化(会对数据源进行抽样)：
set hive.limit.optimize.enable=true;
  • Hive传递参数：
set hivevar:var=1;
select ${var}
其中，若参数本身是字符串还需要加上引号，如date_sub('${timestamp}',7)
  • 数据倾斜(如某些字段中的值过于集中)会使少量reduce子任务执行时间过长，会极大的影响查询效率。
  • 尽量每个子表都过滤数据
  • Merge过多的小文件：
set hive.execution.engine=tez;
set hive.merge.tezfiles=true; -- for namespace issue
set tez.output.compression.type=BLOCK;
set hive.merge.size.per.task=256000000;
set hive.merge.smallfiles.avgsize=256000000;
set hive.exec.compress.output=true;
  • Hive的update，delete比较麻烦，需要更改设置。
  • 可以通过YARN查看任务进程，如：
yarn application -list

5 小结

笔者并不是一个专业的数据仓库的开发人员，以上经验只是基于自己的实际使用。在每天忙碌的查询之余，了解下数据库之间的不同，之前的特性与差异也是一件有趣的事情。

Viewed times

Comments