Loading...
墨滴

BulletTech官方微信

2021/08/28  阅读:26  主题:自定义主题1

Sql tips

Sql tips

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 小结

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

BulletTech官方微信

2021/08/28  阅读:26  主题:自定义主题1

作者介绍

BulletTech官方微信