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在使用的時候是有優化技巧的，下面就給大家介紹幾點：

盡量使用分區表。分區有助于提高資料處理的效率，快速讀取資料。帶分區與不帶分區的表，在雲上存儲是有差別的，如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目錄下的資料檔案，而不是全表資料。

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

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’；

節約存儲。 在odps中存儲也是要收費的，為了幫助大家節省存儲空間，odps提供了資料生命周期的功能，即資料到達一段時間後，會自動被删除。 設定的方法非常簡單，隻需要一條語句即可：

alter table   <table_name> set lifecycle days;

其中：days 為生命周期時間，隻接受正整數，機關是天。當currentdate-lastmodifiedtime 達到這個天數之後，資料會被回收。

生命周期可以加到臨時表或者有分區的表上，這樣幫助大家節省存儲費用。