大数据计算服务MaxCompute的SQL

MaxCompute的SQL介绍

ODPS（Open Data Processing Service），是阿里巴巴通用计算平台提供的一种快速、完全托管的 GB/TB/PB 级数据仓库解决方案，现在已更名为MaxCompute，MaxCompute 向用户提供了完善的数据导入方案以及多种经典的分布式计算模型，能够更快速的解决用户海量数据计算问题，有效降低企业成本，并保障数据安全。
MaxCompute提供了SQL功能， 为BI工程师提供了大数据分析能力。ODPS的SQL语法与HQL非常相似，入手也非常容易。

SQL 语句分为三种：DDL、DML及SELECT 操作。

①DDL 用于表的定义及维护。相信大家都用过类似CREATE TABLE 来创建表。 在ODPS中，除了使用CREATE TABLE来创建表之外，还有另外两种方式：
CREATE TABLE STUDENT LIKE PEOPLE;
创建一张新表STUDENT ，并将PEOPLE的表结构完全复制过来，如果源表中存在分区，也会把分区结构复制过来；
CREATE TABLE STUDENT AS SELECT * FROM PEOPLE;
创建一张新表STUDENT ，将SELECT的结果作为新表结构。注意：这种方式如果源表中带有PARTITION, PARTITION 字段会被当作普通字段添加进来；

②DML 用于表数据的操作，在ODPS中最常见的数据操作场景就是查询数据，将结果插入到另一张表中，即INSERT ….SELECT 操作。 INSERT 有OVERWRITE和INSERT INTO 两种插入方式。
INSERT OVERWRITE 会覆盖目标表中的数据，而INSERT INTO会在原有数据基础上进行追加。 不建议大家使用INSERT INTO。大家想一下，如果数据分析作业失败后需要重跑，而生成数据使用的是INSERT INTO，生成表中数据就会double或更多，造成的结果可想而知。 在阿里内部这样的悲剧实实在在的发生过。

③SELECT 操作是将数据作屏显，主要用于数据探查的场景。 在MaxCompute中SELECT操作最多一次能返回1万条记录，并且如果这1万条记录超过了1M，也是不能返回了（INSERT 后边的SELECT 不会这个限制）。如果大家想把超过1万条的数据或整张表导出来，建议使用Tunnel来导出。
MaxCompute是按照使用的计算和存储资源来收费的，所以大家在使用SQL做数据分析的时候，一定会关心使用计算资源带来的成本问题。

SQL优化

1、尽量使用分区表。

分区有助于提高数据处理的效率，快速读取数据。带分区与不带分区的表，在云上存储是有区别的，如EDW_ORDER不带分区，所有数据直接存储在表目录下：
…/EDW_ORDER/FILE1
…/EDW_ORDER/FILE2
…
而EDW_ORDER_P带分区，数据是存储在不同的分区目录下：
…/EDW_ORDER_P/PT=20150301/FILE1
…/EDW_ORDER_P/PT=20150301/FILE2
…/EDW_ORDER_P/PT=20150302/FILE1
…/EDW_ORDER_P/PT=20150302/FILE2

当我们执行 SELECT … FROM EDW_ORDER_P WHERE PT=’20150302’；时作业只会扫PT=20150302目录下的数据文件，而不是全表数据。

2、使用MAPJOIN。

我们经常会做一个大表和一个或多个小表做JOIN操作，这种操作时最容易引起数据倾斜，从而导致作业SQL低。使用MAPJOIN性能就能提升很多。MAPJOIN的基本原理是：在小数据量情况下，SQL会将用户指定的小表全部加载到执行JOIN操作的程序的内存中，从而加快JOIN的执行速度。但使用MAPJOIN时有些点还需要注意：

①left outer join的左表必须是大表；
②right outer join的右表必须是大表；
③inner join左表或右表均可以作为大表；
④full outer join不能使用mapjoin；
⑤mapjoin支持小表为子查询；
⑥使用mapjoin时需要引用小表或是子查询时，需要引用别名；
⑦在mapjoin中，可以使用不等值连接或者使用or连接多个条件；
⑧目前MaxCompute在mapjoin中最多支持指定128张小表，否则报语法错误；
⑨如果使用mapjoin，则所有小表占用的内存总和不得超过512MB；
⑩多个表join时，最左边的两个表不能同时是mapjoin的表。
下面是一个使用MAPJOIN的例子：
SELECT /*+ MAPJOIN(B) */
A.AUCTION_ID,
B.AUCTION_NAME,
A.TOTAL_AMT
FROM EDW_ORDER A JOIN AUCTION B
ON A.AUCTION_ID=B.AUCTION_ID;

3、WHERE条件中分区字段的使用。

在查询条件中，为了节约I/O，我们经常使用分区字段作为查询条件，但有几种情况，还是要注意有没有用对：
WHERE pt=， expression 中包括自定义的UDF或者random 函数， 计算作业还是要扫全表数据；
a LEFT OUTER JOIN b ON a.key=b.key where a.pt=’x’ and b.pt=’x’，在这个语句中 b.pt=’x’虽然我们指定是一个分区条件，但计算作业却扫描了全表，正确的写法应该是 a LEFT JOIN (SELECT * FROM b where pt=’x’) ON a.key=b.key WHERE a.pt=’x’；

4、节约存储。

在ODPS中存储也是要收费的，为了帮助大家节省存储空间，ODPS提供了数据生命周期的功能，即数据到达一段时间后，会自动被删除。 设置的方法非常简单，只需要一条语句即可：
ALTER TABLE <table_name> SET LIFECYCLE days;
其中：days 为生命周期时间，只接受正整数，单位是天。当CurrentDate-LastModifiedTime 达到这个天数之后，数据会被回收。
生命周期可以加到临时表或者有分区的表上，这样帮助大家节省存储费用。