ODPS SQL <for 数据操作语言DML>

基本操作：

查询：

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_condition
    table_reference:
        table_factor
        | join_table
    table_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