基本操作：

查詢：

SELECT [ALL | DISTINCT] select_expr, select_expr, ... FROM table_reference [WHERE where_condition] [GROUP BY col_list] [ORDER BY order_condition] [DISTRIBUTE BY distribute_condition [SORT BY sort_condition] ] [LIMIT number]　

更新：

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

表關聯：

join_table:table_reference join table_factor [join_condition]| table_reference {left outer|right outer|full outer|inner} join table_reference join_conditiontable_reference:table_factor| join_tabletable_factor:tbl_name [alias]| table_subquery alias| ( table_references )join_condition:on equality_expression ( and equality_expression )

演示內容：

使用DML：

*查詢已有數據

*使用表連接查詢數據

*覆蓋更新

*追加更新

?

//上傳演示數據 odps@ sdrtest>tunnel upload /root/.odpscmd/t_people.txt t_people; //查看演示數據 odps@ sdrtest>select * from t_people; +------------+------+ | id | name | +------------+------+ | 1 | Michael Jordan | | 2 | Angela Dorthea Merkel | | 3 | Bruce Willis | | 4 | Kim Kardashian | | 5 | Jhon Knight | | 6 | Maria Sharapova | | 7 | Chiang Kai-shek | | 8 | Jennifer Aniston | | 9 | David Beckham | | 10 | Dragon Lady | +------------+------+ odps@ sdrtest>select count(*),id from t_people group by id having count(*) > 0;ID = 20190414051444876gfk3c692 Job Queueing. ----------------------------------------------------------------------------------------------STAGES STATUS TOTAL COMPLETED RUNNING PENDING BACKUP M1_job_0 ................. TERMINATED 1 1 0 0 0 ----------------------------------------------------------------------------------------------STAGES STATUS TOTAL COMPLETED RUNNING PENDING BACKUP---------- M1_job_0 ................. TERMINATED 1 1 0 0 0---------------------------------------------------------------------------------- R2_1_job_0 ............... TERMINATED 1 1 0 0 0 ---------------------------------------------------------------------------------------------- STAGES: 02/02 [==========================>>] 100% ELAPSED TIME: 8.17 s ---------------------------------------------------------------------------------------------- Summary: resource cost: cpu 0.00 Core * Min, memory 0.00 GB * Min inputs:sdrtest.t_people: 10 (776 bytes) outputs: Job run time: 5.000 Job run mode: fuxi job Job run engine: execution engine M1:instance count: 1run time: 3.000instance time:min: 0.000, max: 0.000, avg: 0.000input records:TableScan1: 10 (min: 10, max: 10, avg: 10)output records:StreamLineWrite1: 10 (min: 10, max: 10, avg: 10)writer dumps:StreamLineWrite1: (min: 0, max: 0, avg: 0) R2_1:instance count: 1run time: 5.000instance time:min: 0.000, max: 0.000, avg: 0.000input records:StreamLineRead1: 10 (min: 10, max: 10, avg: 10)output records:AdhocSink1: 10 (min: 10, max: 10, avg: 10)reader dumps:StreamLineRead1: (min: 0, max: 0, avg: 0)+------------+------------+ | _c0 | id | +------------+------------+ | 1 | 1 | | 1 | 2 | | 1 | 3 | | 1 | 4 | | 1 | 5 | | 1 | 6 | | 1 | 7 | | 1 | 8 | | 1 | 9 | | 1 | 10 | +------------+------------+ //使用表連接查詢數據 odps@ sdrtest>select t1.* from t_people t1 join t_people t2 on t1.id=t2.id; ID = 20190414052009767gcpxmnimJob Queueing. ----------------------------------------------------------------------------------------------STAGES STATUS TOTAL COMPLETED RUNNING PENDING BACKUP M1_job_0 ................. TERMINATED 1 1 0 0 0 ----------------------------------------------------------------------------------------------STAGES STATUS TOTAL COMPLETED RUNNING PENDING BACKUP---------- M1_job_0 ................. TERMINATED 1 1 0 0 0---------------------------------------------------------------------------------- J2_1_job_0 ............... TERMINATED 1 1 0 0 0 ---------------------------------------------------------------------------------------------- STAGES: 02/02 [==========================>>] 100% ELAPSED TIME: 8.56 s ---------------------------------------------------------------------------------------------- Summary: resource cost: cpu 0.00 Core * Min, memory 0.00 GB * Min inputs:sdrtest.t_people: 10 (776 bytes) outputs: Job run time: 6.000 Job run mode: fuxi job Job run engine: execution engine M1:instance count: 1run time: 3.000instance time:min: 0.000, max: 0.000, avg: 0.000input records:TableScan1: 10 (min: 10, max: 10, avg: 10)output records:StreamLineWrite1: 10 (min: 10, max: 10, avg: 10)StreamLineWrite2: 10 (min: 10, max: 10, avg: 10)writer dumps:StreamLineWrite1: (min: 0, max: 0, avg: 0)StreamLineWrite2: (min: 0, max: 0, avg: 0) J2_1:instance count: 1run time: 6.000instance time:min: 0.000, max: 0.000, avg: 0.000input records:StreamLineRead1: 10 (min: 10, max: 10, avg: 10)StreamLineRead2: 10 (min: 10, max: 10, avg: 10)output records:AdhocSink1: 10 (min: 10, max: 10, avg: 10)reader dumps:StreamLineRead1: (min: 0, max: 0, avg: 0)StreamLineRead2: (min: 0, max: 0, avg: 0)+------------+------+ | id | name | +------------+------+ | 1 | Michael Jordan | | 2 | Angela Dorthea Merkel | | 3 | Bruce Willis | | 4 | Kim Kardashian | | 5 | Jhon Knight | | 6 | Maria Sharapova | | 7 | Chiang Kai-shek | | 8 | Jennifer Aniston | | 9 | David Beckham | | 10 | Dragon Lady | +------------+------+ //創建新表用于演示追加更新: odps@ sdrtest>create table t_people_new like t_people; odps@ sdrtest>read t_people_new; +------------+------------+ | id | name | +------------+------------+ +------------+------------+ odps@ sdrtest>insert into table t_people_new select * from t_people; //查看新建的表內寫入的數據： odps@ sdrtest>read t_people_new>; +------------+------------+ | id | name | +------------+------------+ | 1 | Michael Jordan | | 2 | Angela Dorthea Merkel | | 3 | Bruce Willis | | 4 | Kim Kardashian | | 5 | Jhon Knight | | 6 | Maria Sharapova | | 7 | Chiang Kai-shek | | 8 | Jennifer Aniston | | 9 | David Beckham | | 10 | Dragon Lady | +------------+------------+ //演示追加更新 <into> odps@ sdrtest>insert into table t_people_new select * from t_people; Job Queueing. ----------------------------------------------------------------------------------------------STAGES STATUS TOTAL COMPLETED RUNNING PENDING BACKUP M1_job_0 ................. TERMINATED 1 1 0 0 0 ---------------------------------------------------------------------------------------------- STAGES: 01/01 [==========================>>] 100% ELAPSED TIME: 5.26 s ---------------------------------------------------------------------------------------------- Summary: resource cost: cpu 0.00 Core * Min, memory 0.00 GB * Min inputs:sdrtest.t_people: 10 (776 bytes) outputs:sdrtest.t_people_new: 10 (776 bytes) Job run time: 3.000 Job run mode: fuxi job Job run engine: execution engine M1:instance count: 1run time: 3.000instance time:min: 0.000, max: 0.000, avg: 0.000input records:TableScan1: 10 (min: 10, max: 10, avg: 10)output records:TableSink1: 10 (min: 10, max: 10, avg: 10)OK odps@ sdrtest>read t_people_new; +------------+------------+ | id | name | +------------+------------+ | 1 | Michael Jordan | | 2 | Angela Dorthea Merkel | | 3 | Bruce Willis | | 4 | Kim Kardashian | | 5 | Jhon Knight | | 6 | Maria Sharapova | | 7 | Chiang Kai-shek | | 8 | Jennifer Aniston | | 9 | David Beckham | | 10 | Dragon Lady | | 1 | Michael Jordan | | 2 | Angela Dorthea Merkel | | 3 | Bruce Willis | | 4 | Kim Kardashian | | 5 | Jhon Knight | | 6 | Maria Sharapova | | 7 | Chiang Kai-shek | | 8 | Jennifer Aniston | | 9 | David Beckham | | 10 | Dragon Lady | +------------+------------+//演示覆蓋更新 <overwrite> odps@ sdrtest>insert overwrite table t_people_new select * from t_people;----------------------------------------------------------------------------------------------STAGES STATUS TOTAL COMPLETED RUNNING PENDING BACKUP M1_job_0 ................. TERMINATED 1 1 0 0 0 ---------------------------------------------------------------------------------------------- STAGES: 01/01 [==========================>>] 100% ELAPSED TIME: 5.24 s ---------------------------------------------------------------------------------------------- Summary: resource cost: cpu 0.00 Core * Min, memory 0.00 GB * Min inputs:sdrtest.t_people: 10 (776 bytes) outputs:sdrtest.t_people_new: 10 (776 bytes) Job run time: 3.000 Job run mode: fuxi job Job run engine: execution engine M1:instance count: 1run time: 3.000instance time:min: 0.000, max: 0.000, avg: 0.000input records:TableScan1: 10 (min: 10, max: 10, avg: 10)output records:TableSink1: 10 (min: 10, max: 10, avg: 10)OK odps@ sdrtest>read t_people_new; +------------+------------+ | id | name | +------------+------------+ | 1 | Michael Jordan | | 2 | Angela Dorthea Merkel | | 3 | Bruce Willis | | 4 | Kim Kardashian | | 5 | Jhon Knight | | 6 | Maria Sharapova | | 7 | Chiang Kai-shek | | 8 | Jennifer Aniston | | 9 | David Beckham | | 10 | Dragon Lady | +------------+------------+ odps@ sdrtest>

　　

others....

轉載于:https://www.cnblogs.com/sdrbg/p/10702290.html

總結

以上是生活随笔為你收集整理的ODPS SQL for 数据操作语言DML的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。