🐯上一節中，我們介紹了阿里云MaxComputer中的資料定義語言，這一節中，我們將學習資料操作語言，對往期內容感興趣的小伙伴可以查看如下內容👇:

• 第一篇: Hadoop之Hive資料的匯入與匯出（DML）.
• 第二篇: Hadoop之Hive查詢陳述句.
• 第三篇:Hadoop之Hive的7種Join陳述句.
• 第四篇: Hadoop之Hive的排序.
• 第五篇: Hadoop之Hive的磁區表.
• 第六篇: Hadoop之Hive的分桶表.
• 阿里云系列: 阿里云MaxComputer SQL學習之DDL.

🐅讓我們進入今日份的阿里云MaxComputer的學習，上一章節有粉絲反應沒有例子，這次我使用阿里云的DataWorks給大家演示一下，

1. MaxComputer SQL的DML

DML主要包括如下的兩種操作：

• 查詢資料：單表查詢、表連接（join）、分支條件判斷等，
• 更新資料：插入資料、覆寫插入、多路輸出等等，

1.1 查詢資料

--語法格式：
SELECT [ALL | DISTINCT] <select_expr>, <select_expr>,...
FROM <table_reference>
[WHERE <where_condition>]
[GROUP BY ?col_list>]
[HAVING <having_condition>]
[ORDER BY <order_condition>]
[DISTRIBUTE BY <distribute_condition> [SORT BY <sort_condition>] ]
[LIMIT <number>]

• 列可以用列名指定，* 代表所有的列，
• 支持嵌套子查詢，子查詢必須要有別名
• 子查詢可以與其他表或者子查詢做join

實驗

先來看看表t_dml中的資料

1. 檢查表中“浙江省”相關的資料資訊
   

2. 核查銷售時間大于或等于某日期的資料資訊
   

3. 檢查總量大于某量的城市資訊
   

4. 統計浙江省銷量大于某量的銷售城市排名 (子查詢)
   

5. 城市排名統計
   

1.2 更新資料

• 插入到普通表或者靜態磁區

--基本語法：輸出到普通表或者靜態磁區
INSERT (INTO|OVERWRITE} TABLE <table_name> [PARTITION (<pt_spec>)] [<col_name>,<col_ name>...)]
<select statement>
FROM <from_statement>

• 插入到動態磁區

--基本語法：輸出到動態磁區
INSERT (INTO|OVERWRITE) TABLE <table_name> PARTITION (<ptcol_name>[, <ptcol_name>...])
<select_statement? FROM <from_statement>;

• 動態磁區就是磁區值是由磁區欄位的值來決定，靜態磁區是磁區欄位已經指定為固定值，
• 如果目標表有多級磁區，在運行insert陳述句時允許指定部分磁區為靜態，但是靜態磁區必須是高級磁區，
• 動態磁區的磁區值不可以為NULL，

實驗

1. 插入一條記錄

insert into table t_dml select -1,'1900-01-01 00:00:00','','',0,0,0 from dual;

2. 添加磁區

3. 往磁區添加資料

4. 覆寫插入

5. 磁區表資料操作 （依次執行下面的三個操作，添加磁區、插入資料、檢查磁區表資料）

6. 清空磁區表
使用覆寫插入的方式清空非磁區表

 insert overwrite table t_dml select * from t_dml where 1=2;  --清空操作

通過洗掉的方式洗掉磁區

alter table t_dml_p drop if exists partition (sale_date='2015-01-01');

1.3 多路輸出

多路輸出的意思是一個select輸出向多個表或多個磁區插入資料

--基本語法
FROM <from statement>
INSERT OVERWRITE INTO TABLE <table_name1> [PARTITION (<pt_spec1>)]
?select_statement1>
INSERT OVERWRITE INTO TABLE <table_name2> [PARTITION (?pt_spec2>)]
?select_statement2>
. . .;

多路輸出的限制：

• 單個sql里最多可以寫255路輸出
• 對于磁區表，同一目標磁區不可以出現多次
• 對于未磁區表，該表不能做為目標表多次出現
• 對于同一張表的不同磁區，不能同時有insert overwrite 和insert into 操作

實驗

將表 t_dml 中的資料detail_id編號大于5340000的插入一張臨時表備查，并將5月1日的記錄插入磁區表 t_dml_p 的20150501磁區中去，將5月2日的資料插入20150502磁區，分為三個步驟：
1） 目標表增加磁區
2） 備份核查資料
3） 多路輸出不同磁區

1. 增加磁區，依次增加兩個磁區，操作如下

2. 創建臨時表

3. 將表t_dml中的資料按不同條件同時輸出到新建的表和新建的t_dml_p的兩個磁區中

from t_dml
insert into table t_dml_01

select detail_id,sale_date,province,city,product_id,cnt,amt
where detail_id > 5340000

insert overwrite table t_dml_p  partition (sale_date='20150501')
select detail_id,province,city,product_id,cnt,amt
where sale_date >= '2015-05-01 00:00:00'
and sale_date <= '2015-05-01 23:59:59'

insert overwrite table t_dml_p  partition (sale_date='20150502')
select detail_id,province,city,product_id,cnt,amt
where sale_date >= '2015-05-02 00:00:00'
and sale_date <= '2015-05-02 23:59:59' ;

1.4 表關聯

下圖是連接的幾種情況：陰影部分表示最后的結果部分

SELECT [t1.col_name, ]
[t2.col_name,]
FROM
<tab_name1> t1
[LEFT OUTER] JOIN <tab_name2> t2
ON <[t1.col_name = t2.col_name]
[AND t1.col_name = t2.col_name]
......>

實驗

先展示一下t_product表，事實表t_dml包含了銷售記錄資訊，其中欄位 product_id為產品標識，可以關聯另一張維表t_product獲得產品的說明資訊，

1. 按照產品分類（category_name）統計銷售金額
   

1.5 Mapjoin（提高效率）

1. 可以使用不等值的or作為連接條件

-- 示例1：不等值 or 作為連接條件
SELECT /*+ MAPJOIN(a) */
a. shop_name
,a. total_price
,b. total_price
FROM sale_detail_sj a
JOIN sale_detail b
ON a.total_price<b.total_price
OR a.total_price + b.total_price < 500;

2. 提升性能

--示例2：提升性能
SELECT /* + MAPJOIN(a) */
a. shop_name
,b. customer_id
,b. total_price
FROM shop a
JOIN sale_detail b
ON a.shop_name = b.shop_name;

• 使用情景：一個大表和一個或多個小表做join
• 基本原理：將用戶指定的小表全部加載到執行 join 操作的程式的記憶體中，從而加快 join 的執行速度
• 注意事項：
  left outer join 的左表必須是大表
  right outer join 的右表必須是大表
  inner join 左表或右表均可以作為大表
  full outer join不能使用 mapjoin
  支持小表為子查詢，需要使用別名
  可以使用不等值連接或者使用 or 連接多個條件
  目前最多支持指定128張小表
  所有小表加載至記憶體后占用的記憶體總和不得超過512M

實驗

由于各種原因，造成銷售資訊表 t_dml 中的記錄存在一些質量問題，可能的問題包括：
1- 產品標識錯誤： 可以通過單價判斷，單價相等的標識不同，則可能存在錯誤
2- 價格錯誤：如果銷售記錄中的平均單價高于產品維表中的定價，則可能存在問題
請協助發現這些可能存在問題的記錄，

1. left outer join 實作質量問題
   
2. inner join (join) 實作質量問題
   

在做關聯時，如果關聯條件比較復雜（比如包含 or 等連接條件）或者是關聯條件中存在非等值關聯（比如大于、小于或者不等于等），則普通的 join 無法實作，可以采用帶有 mapjoin HINT 的 join 方法，

1.6 分支運算式

case when 有兩種寫法：

1. case后面接欄位

--兩種 CASE WHEN 語法：
--1. case+value
CASE <value>
WHEN <condition 1> then <result 1>
WHEN <condition 2> then <result 2>
else <result  n>
END
-- 例如：
SELECT CASE gender WHEN 1 THEN '男' WHEN 2 THEN '女' END
FROM table1；

2. case when后面接欄位(大多數用這種，更靈活)

-- 2. case when+value
CASE
WHEN <condition 1> then <result 1>
WHEN <condition 2> then <result 2>
else <result n>
END
--例如:
SELECT CASE WHEN gender=1 THEN '男' WHEN gender=2 THEN '女' END
FROM table1;

• 如果回傳結果型別不一樣，會進行型別轉換，回傳統一的結果，
• 非貪婪式，碰到第一個滿足條件的結果就會回傳，

實驗

在目前的售價上實行9折優惠，一次購買6個及以上產品的，給與8折優惠，請基于5月份資料想評估一下此次活動的成本（為了簡單可行，活動成本定義為目前銷售額減掉優惠后的銷售額）

2. 總結

在這一部分中，需要注意的點：

• 企業中所有表的查詢都需要帶上磁區，
• 記住mapjoin的原理，面試重點，

3. 參考資料

《阿里云全球培訓中心》
《阿里云DataWorks使用手冊》