--- template: overrides/blogs.html tags: - analytics - database --- # SQL tips !!! info 作者：Void，发布于2021-07-20，阅读时间：约10分钟，微信公众号文章链接：[:fontawesome-solid-link:](https://mp.weixin.qq.com/s/s3nV00URWHvlqrRQ-2An5A) ## 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的子查询一定要有别名。 ```sql select * from (select * from table) a ``` - Hive中的limit会在所有逻辑执行完后才limit，可以使用如下设置优化(会对数据源进行抽样)： ```sql set hive.limit.optimize.enable=true; ``` - Hive传递参数： ```sql set hivevar:var=1; select ${var} 其中，若参数本身是字符串还需要加上引号，如date_sub('${timestamp}',7) ``` - 数据倾斜(如某些字段中的值过于集中)会使少量reduce子任务执行时间过长，会极大的影响查询效率。 - 尽量每个子表都过滤数据 - Merge过多的小文件： ```sql 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 小结笔者并不是一个专业的数据仓库的开发人员，以上经验只是基于自己的实际使用。在每天忙碌的查询之余，了解下数据库之间的不同，之前的特性与差异也是一件有趣的事情。