ODPS 功能介绍之SQL

ODPS 提供了SQL功能， 为BI工程师提供了大数据分析能力。ODPS的SQL语法与HQL非常相似，入手也非常容易。接下来就给大家介绍一下SQL的使用及一些优化技巧。

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

DDL 用于表的定义及维护。相信大家都用过类似CREATE TABLE 来创建表。 在ODPS中，除了使用CREATE TABLE来创建表之外，还有另外两种方式：

CREATE TABLE EDW_ORDER_BACKUP LIKE EDW_ORDER;

创建一张新表EDW_ORDER_BACKUP，并将EDW_ORDER的表结构完全复制过来，如果源表中存在分区，也会把分区结构复制过来；

CREATE TABLE EDW_ORDER_BACKUP2013 AS SELECT * FROM EDW_ORDER;

创建一张新表EDW_ORDER_BACKUP2013，将SELECT的结果作为新表结构。注意：这种方式如果源表中带有PARTITION, PARTITION 字段会被当作普通字段添加进来；

DML 用于表数据的操作，在ODPS中最常见的数据操作场景就是查询数据，将结果插入到另一张表中，即INSERT ….SELECT 操作。 INSERT 有OVERWRITE和INSERT INTO 两种插入方式。

INSERT OVERWRITE 会覆盖目标表中的数据，而INSERT INTO会在原有数据基础上进行追加。 不建议大家使用INSERT INTO。大家想一下，如果数据分析作业失败后需要重跑，而生成数据使用的是INSERT INTO，生成表中数据就会double或更多，造成的结果可想而知。 在阿里内部这样的悲剧实实在在的发生过L。

SELECT 操作是将数据作屏显，主要用于数据探查的场景。 在ODPS中SELECT操作最多一次能返回1万条记录，并且如果这1万条记录超过了1M，也是不能返回了（INSERT 后边的SELECT 不会这个限制）。如果大家想把超过1万条的数据或整张表导出来，建议使用Tunnel来导出。

ODPS是按照使用的计算和存储资源来收费的，所以大家在使用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目录下的数据文件，而不是全表数据。

1. 使用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连接多个条件；

目前ODPS在mapjoin中最多支持指定6张小表，否则报语法错误；

如果使用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;

1. WHERE条件中分区字段的使用。在查询条件中，为了节约I/O，我们经常使用分区字段作为查询条件，但有几种情况，还是要注意有没有用对：

WHERE pt=<expression>， 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’；

1. 节约存储。 在ODPS中存储也是要收费的，为了帮助大家节省存储空间，ODPS提供了数据生命周期的功能，即数据到达一段时间后，会自动被删除。 设置的方法非常简单，只需要一条语句即可：

ALTER TABLE   <table_name> SET LIFECYCLE days;

其中：days 为生命周期时间，只接受正整数，单位是天。当CurrentDate-LastModifiedTime 达到这个天数之后，数据会被回收。

生命周期可以加到临时表或者有分区的表上，这样帮助大家节省存储费用。