无知的比较：R和Teradata SQL(附赠TD经验几枚)

今年夏天的时候，刚刚开始被SQL虐，写了一篇很无知且更多是吐槽意味的blog post: 关于R的若干SQL等价问题。当时被若干朋友批评，我还浑然不觉个中精要。现在用Teradata也有半年多的时间了，越来越习惯了SQL的表述方式，也越来越体会到Teradata作为一个强大的数据仓库系统，是有多么的伟大...这感觉，就是只玩过几个G数据的乡下人进城，猛然看到各路英雄都是动辄几个T的数据，只能暂时以原来落后的思维方式、勉强挥舞着新型工具...好在个性不是特别愚钝，终究还是可以慢慢地领悟到T级数据的奥妙之处，终究用着新武器也越来越顺手了。

这一段时间，也充分证明了我是master in economics而绝对不是 in cs。数据库系统的原理终究学的不深——我哪儿知道MySQL的SQL和Teradata的SQL差了那么多呀...后来慢慢的去听同事传授TD使用经验，慢慢的去看老板传过来的代码，慢慢的一次次处理掉 no more spool space的错误和一次次接到SQL语句效率低强制退出的警告信之后，才逐渐地越来越了解TD的原理和脾气。工欲善其事，必先利其器，这些都是沉重的学费。

所以各位如果没有看过那篇「无知者无畏」状post的，就不要看了。直接接受我诚挚的道歉然后看下文吧。Teradata下简称TD。绝非专业知识，只是个人有限的了解，不对之处请及时批评。

有次跟同事聊，问他们为什么不在本机上装个TD测试用...然后被狠狠鄙视了一番——TD没有单机版！天生就是架在云上的。这东西还真是个原生的分布式数据仓库。

TD和oracle的关系也比较简单：一个是数据仓库，一个是数据库，功能设计什么的压根就不一样。这么说吧，oracle支撑的是ebay的网站运行，所以必然涉及大量的查询、插入、删除等请求。更麻烦的是，以ebay的访问量，这些请求都是同时过来的，这就要求系统并发性要好一点（专业人士可以绕道了，我只是浅薄的知道一点东西...）。体验过12306买火车票排队的大家，想必都知道这个系统并发起来的厉害。ebay若是也来个排队，消费者还不疯掉...

为了应对这样的任务，oracle的数据库设计自然是要按那「三大范式」来。这个就不多说了，再说就暴露了...

TD则是把oracle的数据定期地导出来存着，所以除了简单的复制数据之外，还要对数据进行一定程度的清理和整理，并不完全是最最原始的数据。然后到了食物链上端数据分析师手里，面对的数据很多都是已经弄的很整齐的了。说是食物链上端，只是因为这大概是分工中需要用到原始数据的最后一拨人，且这拨人用到的最多的就是查询（甚至是整表查询）和计算，所以我们写SQL的时候更多是考虑到这些需求，利用TD在这方面的性能优势——我已经很少在SAS或者R里面进行数据整理的工作了，性能跟TD完全不是一个量级的。

下面是TD使用的若干经验，不过这东西只有自己碰壁了才知道个中真滋味，我就是缩短一下解决问题的进程，不用太折腾到处搜来搜去。

No more spool space。当你的SQL没有语法错误，那么最常见的运行不下去的情况就是 no more spool space了，这大概是每个用TD的不管新鸟老鸟都会经历的痛苦历程。这个错误就像R里面报"cannot allocate a vector of size ***"，或者你玩游戏正high的时候系统告诉你内存不足。解决的思路就是＂空间换时间＂，就是看你具体怎么换了。

1. 多表join查询的时候，就要看这些表是怎么merge的——TD会去算是一大一小join，还是两个大表join。前者TD会复制小表到每个大表的＂节点＂上（大表肯定要分块存起来嘛），所以可以事先加collect statistics on *** column ***。后者就要费点脑子了，争取两个表的排序（PI）一致，这样TD join的时候就不需要对两个表都重新排列了是不是（merge join）？每一次重排都会占掉大量的临时空间呢。再者，查询结果储存到另外的永久或者临时表里面，就要注意primary index（简称PI）的选择，不要让TD再把查询结果重排...

2. 除了看primary index，有时候还要去注意partition by。有些已经建好的超级长的表需要去看是怎么真正＂分块＂存储的。对于partition by 的字段设定一个where条件，会让TD很快的知道你要查询和join的是哪些部分，大大缩短范围。一般说来，最常见的partition by就是时间了，缩短一个时间范围也不失为良策嘛。

3. 擅用cast()可以避免很多跟数据类型有关的错误，这个就不赘述了。

4. No space on ***说明没有永久表的存储空间了，这个就得去删过于古老的表和去要新的空间了。

5. 每段SQL不要太长，join不宜太多。熟悉TD的脾气之后，就张弛有度了，擅用临时表。

6. 多用group by少用distinct。

7. 最后终极野蛮办法，如果实在是没法两个大表join又没有partition by的话...手动按PI拆其中某个表吧。

----例行碎碎念----
那些在LinkedIn上endorse我R的朋友们，我真心感觉承受不起呀！至今依旧觉得我的R很烂，代码只停留在＂可运行＂的水平，效率大都很糟糕，基本就是折磨CPU的...哎，非科班出身终究是有莫大的差距呀。