閱讀99 返回首頁    go 人物

DML語句__SQL_大數據計算服務-阿裏雲

更新表中的數據(INSERT OVERWRITE/INTO)

語法格式：

  1.     INSERT OVERWRITE|INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)]
  2.         select_statement
  3.         FROM from_statement;

備注:

  • MaxCompute 的Insert語法與通常使用的MySQL或Oracle的Insert語法有差別，在insert overwrite|into 後需要加入table關鍵字，後不是直接使用tablename。

在MaxCompute SQL處理數據的過程中，insert overwrite/into用於將計算的結果保存目標表中。insert into與insert overwrite的區別是，insert into會向表或表的分區中追加數據，而insert overwrite則會在向表或分區中插入數據前清空表中的原有數據。在使用MaxCompute 處理數據的過程中，insert overwrite/into是最常用到的語句，它們會將計算的結果保存一個表中，可以供下一步計算使用。如，可以用如下操作計算sale_detail表中不同地區的銷售額

  1.     create table sale_detail_insert like sale_detail;
  2.     alter table sale_detail_insert add partition(sale_date='2013', region='china');
  3.     insert overwrite table sale_detail_insert partition (sale_date='2013', region='china')
  4.         select shop_name, customer_id, total_price from sale_detail;

需要注意的是，在進行insert更新數據操作時，源表與目標表的對應關係依賴於在select子句中列的順序，而不是表與表之間列名的對應關係，下麵的SQL語句仍然是合法的：

  1.     insert overwrite table sale_detail_insert partition (sale_date='2013', region='china')
  2.         select customer_id, shop_name, total_price from sale_detail;
  3.     -- 在創建sale_detail_insert表時，列的順序為：
  4.     -- shop_name string, customer_id string, total_price bigint
  5.     -- 而從sale_detail向sale_detail_insert插入數據是，sale_detail的插入順序為：
  6.     -- customer_id, shop_name, total_price
  7.     -- 此時，會將sale_detail.customer_id的數據插入sale_detail_insert.shop_name
  8.     -- 將sale_detail.shop_name的數據插入sale_detail_insert.customer_id

向某個分區插入數據時，分區列不允許出現在select列表中：

  1.     insert overwrite table sale_detail_insert partition (sale_date='2013', region='china')
  2.         select shop_name, customer_id, total_price, sale_date, region  from sale_detail;
  3.     -- 報錯返回，sale_date, region為分區列，不允許出現在靜態分區的 insert 語句中。

同時，partition的值隻能是常量，不可以出現表達式。以下用法是非法的：

  1.     insert overwrite table sale_detail_insert partition (sale_date=datepart('2016-09-18 01:10:00', 'yyyy') , region='china')
  2.         select shop_name, customer_id, total_price from sale_detail;

多路輸出(MULTI INSERT)

MaxCompute SQL支持在一個語句中插入不同的結果表或者分區

語法格式：

  1.     FROM from_statement
  2.         INSERT OVERWRITE | INTO TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)]
  3.             select_statement1 [FROM from_statement]
  4.         [INSERT OVERWRITE | INTO TABLE tablename2 [PARTITION (partcol1=val3, partcol2=val4 ...)]
  5.             select_statement2 [FROM from_statement]]

說明：

  • 一般情況下，單個SQL裏最多可以寫128路輸出，超過128路報語法錯誤。
  • 在一個multi insert中，對於分區表，同一個目標分區不允許出現多次; 對於未分區表，該表不能出現多次。
  • 對於同一張分區表的不同分區，不能同時有insert overwrite和insert into操作，否則報錯返回。

如，

  1.     create table sale_detail_multi like sale_detail;
  3.     from sale_detail
  4.         insert overwrite table sale_detail_multi partition (sale_date='2010', region='china' ) 
  5.             select shop_name, customer_id, total_price where .....
  6.         insert overwrite table sale_detail_multi partition (sale_date='2011', region='china' )
  7.             select shop_name, customer_id, total_price where .....;
  8.     -- 成功返回，將sale_detail的數據插入到sales裏的2010年及2011年中國大區的銷售記錄中
  10.     from sale_detail
  11.         insert overwrite table sale_detail_multi partition (sale_date='2010', region='china' )
  12.             select shop_name, customer_id, total_price
  13.         insert overwrite table sale_detail_multi partition (sale_date='2010', region='china' )
  14.             select shop_name, customer_id, total_price;
  15.     -- 出錯返回，同一分區出現多次
  17.     from sale_detail
  18.         insert overwrite table sale_detail_multi partition (sale_date='2010', region='china' )
  19.             select shop_name, customer_id, total_price
  20.         insert into table sale_detail_multi partition (sale_date='2011', region='china' )
  21.             select shop_name, customer_id, total_price;
  22.     -- 出錯返回，同一張表的不同分區，不能同時有insert overwrite和insert into操作

輸出到動態分區(DYNAMIC PARTITION)

在insert overwrite到一張分區表時，可以在語句中指定分區的值。也可以用另外一種更加靈活的方式，在分區中指定一個分區列名，但不給出值。相應的，在select子句中的對應列來提供分區的值。

語法格式：

  1.     insert overwrite table tablename partition (partcol1, partcol2 ...) select_statement from from_statement;

說明：

  • 目前，在使用動態分區功能的SQL中，在分布式環境下，單個進程最多隻能輸出512個動態分區，否則引發運行時異常；
  • 在現階段，任意動態分區SQL不允許生成超過2000個動態分區，否則引發運行時異常；
  • 動態生成的分區值不允許以為NULL，否則會引發異常；
  • 如果目標表有多級分區，在運行insert語句時允許指定部分分區為靜態，但是靜態分區必須是高級分區；

下麵，我們使用一個簡單的例子來說明動態分區：

  1.     create table total_revenues (revenue bigint) partitioned by (region string);
  2.     insert overwrite table total_revenues partition(region)
  3.         select total_price as revenue, region
  4.             from sale_detail;

按照這種寫法，在SQL運行之前，是不知道會產生哪些分區的，隻有在select運行結束後，才能由region字段產生的值確定會產生哪些分區，這也是為什麼叫做”動態分區”的原因。

其他示例：

  1.     create table sale_detail_dypart like sale_detail;
  2.     insert overwrite table sale_detail_dypart partition (sale_date, region)
  3.         select * from sale_detail;
  4.     -- 成功返回
  6.     insert overwrite table sale_detail_dypart partition (sale_date='2013', region)
  7.         select shop_name,customer_id,total_price,region from sale_detail;
  8.     -- 成功返回，多級分區，指定一級分區
  10.     insert overwrite table sale_detail_dypart partition (sale_date='2013', region)
  11.         select shop_name,customer_id,total_price from sale_detail;
  12.     -- 失敗返回，動態分區插入時，動態分區列必須在select列表中
  14.     insert overwrite table sales partition (region='china', sale_date)
  15.         select shop_name,customer_id,total_price,region from sale_detail;
  16.     -- 失敗返回，不能僅指定低級子分區，而動態插入高級分區

SELECT操作

語法格式：

  1.     SELECT [ALL | DISTINCT] select_expr, select_expr, ...
  2.         FROM table_reference
  3.         [WHERE where_condition]
  4.         [GROUP BY col_list]
  5.         [ORDER BY order_condition]
  6.         [DISTRIBUTE BY distribute_condition [SORT BY sort_condition] ]
  7.         [LIMIT number]

在使用select語句時需要注意如下幾點：

  • select操作從表中讀取數據，要讀的列可以用列名指定，或者用*代表所有的列，一個簡單的select如下:
  1.     select * from sale_detail;

或者隻讀取sale_detail的一列shop_name

  1.     select shop_name from sale_detail;

在where中可以指定過濾的條件，如

  1.     select * from sale_detail where shop_name like 'hang%';

備注:

  • 請注意，當使用Select語句屏顯時，目前最多隻能顯示1000行結果。當select作為子句時，無此限製，select子句會將全部結果返回給上層查詢。
  • where子句：支持的過濾條件包括：
過濾條件 描述
> , < , =, >=, <=, <>
like, rlike
in, not in 如果在in/not in條件後加子查詢，子查詢隻能返回一列值，且返回值的數量不能超過1000。

在select語句的where子句中可以指定分區範圍，這樣可以僅僅掃描表的指定部分，避免全表掃描。如下所示：

  1.     select sale_detail.*
  2.         from sale_detail
  3.         where sale_detail.sale_date >= '2008' and sale_detail.sale_date <= '2014';

MaxCompute SQL的where子句不支持between條件查詢。

  • 在table_reference中支持使用嵌套子查詢，如：
  1.     select * from (select region from sale_detail) t where region = 'shanghai';
  • distinct：如果有重複數據行時，在字段前使用distinct，會將重複字段去重，隻返回一個值，而使用all將返回字段中所有重複的值，不指定此選項時默認效果和all相同。使用distinct隻返回一行記錄，如
  1.     select distinct region from sale_detail;
  2.     select distinct region, sale_date from sale_detail;
  3.     -- distinct多列，distinct的作用域是select的列集合，不是單個列。
  • group by：分組查詢, 一般group by是和聚合函數配合使用。在select中包含聚合函數時：
  1. 用group by的key可以是輸入表的列名;
  2. 也可以是由輸入表的列構成的表達式，不允許是select語句的輸出列的別名;
  3. 規則1的優先級高於規則2。當規則1和規則2發生衝突時，即group by的key即是輸入表的列或表達式，又是select的輸出列，以規則1為準。

如果如

  1.     select region from sale_detail group by region;
  2.     -- 直接使用輸入表列名作為group by的列，可以運行
  4.     select sum(total_price) from sale_detail group by region;
  5.     -- 以region值分組，返回每一組的銷售額總量，可以運行
  7.     select region, sum(total_price) from sale_detail group by region;
  8.     -- 以region值分組，返回每一組的region值(組內唯一)及銷售額總量，可以運行
  12.     select region as r from sale_detail group by r;
  13.     -- 使用select列的別名運行，報錯返回
  15.     select 'China-' + region as r from sale_detail group by 'China-' + region;
  16.     -- 必須使用列的完整表達式
  20.     select region, total_price from sale_detail group by region;
  21.     -- 報錯返回，select的所有列中，沒有使用聚合函數的列，必須出現在group by中
  23.     select region, total_price from sale_detail group by region, total_price;
  24.     -- 可以運行

有這樣的限製是因為，在SQL解析中，group by操作通常是先於select操作的，因此group by隻能接受輸入表的列或表達式為key。

備注:

  • order by：對所有數據按照某幾列進行全局排序。如果您希望按照降序對記錄進行排序，可以使用DESC關鍵字。由於是全局排序，order by必須與limit共同使用。對在使用order by排序時，NULL會被認為比任何值都小，這個行為與Mysql一致，但是與Oracle不一致。與group by不同，order by後麵必須加select列的別名，當select某列時，如果沒有指定列的別名，將列名作為列的別名。
  1.     select * from sale_detail order by region;
  2.     -- 報錯返回，order by沒有與limit共同使用
  4.     select * from sale_detail order by region limit 100;
  6.     select region as r from sale_detail order by region;
  7.     -- 報錯返回，order by後麵必須加列的別名。
  9.     select region as r from sale_detail order by r;

  • [limit number]的number是常數，限製輸出行數。當使用無limit的select語句直接從屏幕輸出查看結果時，最多隻輸出5000行。每個項目空間的這個屏顯最大限製限製可能不同，可以通過控製台麵板控製。

  • distribute by：對數據按照某幾列的值做hash分片，必須使用select的輸出列別名。

  1.     select region from sale_detail distribute by region;
  2.     -- 列名即是別名，可以運行
  4.     select region as r from sale_detail distribute by region;
  5.     -- 報錯返回，後麵必須加列的別名。
  7.     select region as r from sale_detail distribute by r;
  • sort by：局部排序，語句前必須加distribute by。實際上sort by是對distribute by的結果進行局部排序。必須使用select的輸出列別名。
  1.     select region from sale_detail distribute by region sort by region;
  2.     select region as r from sale_detail sort by region;
  3.     -- 沒有distribute by，報錯退出。
  • order by不和distribute by/sort by共用，同時group by也不和distribute by/sort by共用，必須使用select的輸出列別名。

備注:

  • order by/sort by/distribute by的key必須是select語句的輸出列，即列的別名。在MaxCompute SQL解析中， order by/sort by/distribute by是後於select操作的，因此它們隻能接受select語句的輸出列為key。

子查詢

普通的select是從幾張表中讀數據，如select column_1, column_2 … from table_name，但查詢的對象也可以是另外一個select操作，如：

  1.     select * from (select shop_name from sale_detail) a;

備注：

  • 子查詢必須要有別名。

在from子句中，子查詢可以當作一張表來使用，與其它的表或子查詢進行join操作，如

  1.     create table shop as select * from sale_detail;
  3.     select a.shop_name, a.customer_id, a.total_price from
  4.         (select * from shop) a join sale_detail on a.shop_name = sale_detail.shop_name;

UNION ALL

語法格式：

  1.     select_statement UNION ALL select_statement

將兩個或多個select操作返回的數據集聯合成一個數據集，如果結果有重複行時，會返回所有符合條件的行，不進行重複行的去重處理。需要注意的是：MaxCompute SQL不支持頂級的兩個查詢結果合並，要改寫為一個子查詢的形式，如

  1.     select * from sale_detail where region = 'hangzhou'
  2.         union all
  3.     select * from sale_detail where region = 'shanghai';

需要改成：

  1.     select * from (
  2.         select * from sale_detail where region = 'hangzhou'
  3.         union all
  4.         select * from sale_detail where region = 'shanghai')
  5.         t;

備注:

  • union all操作對應的各個子查詢的列個數、名稱和類型必須一致。如果列名不一致時，可以使用列的別名加以解決。
  • 一般情況下，MaxCompute 最多允許128路union all，超過此限製報語法錯誤。

JOIN操作

MaxCompute 的JOIN支持多路間接，但不支持笛卡爾積，即無on條件的鏈接。語法定義：

  1.     join_table:
  2.         table_reference join table_factor [join_condition]
  3.         | table_reference {left outer|right outer|full outer|inner} join table_reference join_condition
  5.     table_reference:
  6.         table_factor
  7.         | join_table
  9.     table_factor:
  10.         tbl_name [alias]
  11.         | table_subquery alias
  12.         | ( table_references )
  14.     join_condition:
  15.         on equality_expression ( and equality_expression )*

備注:

  • equality_expression是一個等式表達式

left join 會從左表(shop)那裏返回所有的記錄，即使在右表(sale_detail)中沒有匹配的行。

  1.     select a.shop_name as ashop, b.shop_name as bshop from shop a
  2.         left outer join sale_detail b on a.shop_name=b.shop_name;
  3.     -- 由於表shop及sale_detail中都有shop_name列，因此需要在select子句中使用別名進行區分。

right outer join 右連接，返回右表中的所有記錄，即使在左表中沒有記錄與它匹配，例如：

  1.     select a.shop_name as ashop, b.shop_name as bshop from shop a
  2.         right outer join sale_detail b on a.shop_name=b.shop_name;

full outer join 全連接，返回左右表中的所有記錄，例如：

  1.     select a.shop_name as ashop, b.shop_name as bshop from shop a
  2.         full outer join sale_detail b on a.shop_name=b.shop_name;

在表中存在至少一個匹配時，inner join 返回行。 關鍵字inner可省略。

  1.     select a.shop_name from shop a inner join sale_detail b on a.shop_name=b.shop_name;
  3.     select a.shop_name from shop a join sale_detail b on a.shop_name=b.shop_name;

連接條件，隻允許and連接的等值條件，並且最多支持16路join操作。隻有在MAPJOIN中，可以使用不等值連接或者使用or連接多個條件。

  1.     select a.* from shop a full outer join sale_detail b on a.shop_name=b.shop_name
  2.         full outer join sale_detail c on a.shop_name=c.shop_name;
  3.     -- 支持多路join鏈接示例，最多支持16路join
  5.     select a.* from shop a join sale_detail b on a.shop_name != b.shop_name;
  6.     -- 不支持不等值Join鏈接條件，報錯返回。

MAPJOIN HINT

當一個大表和一個或多個小表做join時，可以使用mapjoin，性能比普通的join要快很多。mapjoin的基本原理是：在小數據量情況下，SQL會將用戶指定的小表全部加載到執行join操作的程序的內存中，從而加快join的執行速度。需要注意，使用mapjoin時：

  • left outer join的左表必須是大表；
  • right outer join的右表必須是大表；
  • inner join左表或右表均可以作為大表；
  • full outer join不能使用mapjoin；
  • mapjoin支持小表為子查詢；
  • 使用mapjoin時需要引用小表或是子查詢時，需要引用別名；
  • 在mapjoin中，可以使用不等值連接或者使用or連接多個條件；
  • 目前MaxCompute 在mapjoin中最多支持指定6張小表，否則報語法錯誤；
  • 如果使用mapjoin，則所有小表占用的內存總和不得超過512MB。請注意由於MaxCompute 是壓縮存儲，因此小表在被加載到內存後，數據大小會急劇膨脹。此處的512MB限製是加載到內存後的空間大小；
  • 多個表join時，最左邊的兩個表不能同時是mapjoin的表。

下麵是一個簡單的示例：

  1.     select /* + mapjoin(a) */
  2.         a.shop_name,
  3.         b.customer_id,
  4.         b.total_price
  5.     from shop a join sale_detail b
  6.     on a.shop_name = b.shop_name;

MaxCompute SQL不支持支持在普通join的on條件中使用不等值表達式、or 邏輯等複雜的join條件，但是在mapjoin中可以進行如上操作，例如：

  1.     select /*+ mapjoin(a) */
  2.         a.total_price,
  3.         b.total_price
  4.     from shop a join sale_detail b
  5.     on a.total_price < b.total_price or a.total_price + b.total_price < 500;

HAVING子句

由於MaxCompute SQL的WHERE關鍵字無法與合計函數一起使用，可以采用having字句。

語法格式： 

  1. SELECT column_name, aggregate_function(column_name)
  2. FROM table_name
  3. WHERE column_name operator value
  4. GROUP BY column_name
  5. HAVING aggregate_function(column_name) operator value

使用場景舉例：比如有一張訂單表Orders，包括客戶名稱（Customer,），訂單金額（OrderPrice），訂單日期（Order_date），訂單號（Order_id）四個字段。現在希望查找訂單總額少於2000的客戶。現在我們可以寫如下語句： 

  1. SELECT Customer,SUM(OrderPrice) FROM Orders
  2. GROUP BY Customer
  3. HAVING SUM(OrderPrice)<2000

最後更新：2016-12-06 12:51:48

  上一篇：go DDL語句__SQL_大數據計算服務-阿裏雲
  下一篇：go 內建函數-上__SQL_大數據計算服務-阿裏雲