SQL_ODPS-MaxCompute-odpscmd-1.0

dSQL_ODPS-MaxCompute-odpscmd-1.0

注：这篇每块代码按顺序操作 每块可按顺序看
目的：熟悉一下日志

（一）简单的语句操作

普通表

--复制表结构
odps@ work_test_1>create table t_denile_2 like t_deniel;

ID = 000000001447167gm4yr392
OK

-- 修改表名
odps@ work_test_1>alter table t_denile_2  rename to t_deniel_2;

-- select
odps@ work_test_1>select * from t_deniel;

ID = 0000000015051653g6f0s392
Log view:
http://logview
Summary:
resource cost: cpu 0.00 Core * Min, memory 0.00 GB * Min
inputs:
outputs:

+------------+------------+
| id         | name       |
+------------+------------+
| 15         | 张三         |
| 16         | 李四         |
| 18         | 王五         |
+------------+------------+
3 records (at most 10000 supported) fetched by instance tunnel.

-- 读表 不知道为什么用read会乱码 
odps@ work_test_1>read t_deniel;
+------------+------------+
| id         | name       |
+------------+------------+
| 15         | ????       |
| 16         | ????       |
| 18         | ????       |
+------------+------------+


-- left join （必须带on 不允许没有mapjoin的笛卡尔积）
odps@ work_test_1>select * from t_deniel a left join t_deniel_3 b on a.id=b.id;

ID = 00000000151944507gwtc5mim
Log view:
http://logview
Job Queueing...
Summary:
resource cost: cpu 0.02 Core * Min, memory 0.02 GB * Min
inputs:
        work_test_1.t_deniel_3: 3 (448 bytes)
        work_test_1.t_deniel: 3 (448 bytes)
outputs:
Job run time: 1.000
Job run mode: service job
Job run engine: execution engine
M1:
        instance count: 1
        run time: 0.000
        instance time:
                min: 0.000, max: 0.000, avg: 0.000
        input records:
                TableScan2: 3  (min: 3, max: 3, avg: 3)
        output records:
                StreamLineWrite1: 3  (min: 3, max: 3, avg: 3)
        metrics_output_count:
                Filter1: 3  (min: 3, max: 3, avg: 3)
                StreamLineWrite1: 3  (min: 3, max: 3, avg: 3)
                TableScan2: 3  (min: 3, max: 3, avg: 3)
        metrics_inner_time_ms:
                Filter1: 0  (min: 0, max: 0, avg: 0)    MaxInstance: 0
                StreamLineWrite1: 4  (min: 4, max: 4, avg: 4)   MaxInstance: 0
                TableScan2: 0  (min: 0, max: 0, avg: 0) MaxInstance: 0
M3_2:
        instance count: 1
        run time: 1.000
        instance time:
                min: 1.000, max: 1.000, avg: 1.000
        input records:
                StreamLineRead2: 3  (min: 3, max: 3, avg: 3)
                TableScan1: 3  (min: 3, max: 3, avg: 3)
        output records:
                AdhocSink1: 3  (min: 3, max: 3, avg: 3)
        metrics_output_count:
                AdhocSink1: 3  (min: 3, max: 3, avg: 3)
                HashJoin1: 3  (min: 3, max: 3, avg: 3)
                HashJoin1#0: 3  (min: 3, max: 3, avg: 3)
                Project1: 3  (min: 3, max: 3, avg: 3)
                StreamLineRead2: 3  (min: 3, max: 3, avg: 3)
                TableScan1: 3  (min: 3, max: 3, avg: 3)
        metrics_inner_time_ms:
                AdhocSink1: 28  (min: 28, max: 28, avg: 28)     MaxInstance: 0
                HashJoin1: 0  (min: 0, max: 0, avg: 0)  MaxInstance: 0
                HashJoin1#0: 0  (min: 0, max: 0, avg: 0)        MaxInstance: 0
                Project1: 0  (min: 0, max: 0, avg: 0)   MaxInstance: 0
                StreamLineRead2: 0  (min: 0, max: 0, avg: 0)    MaxInstance: 0
                TableScan1: 0  (min: 0, max: 0, avg: 0) MaxInstance: 0
R2_1:
        instance count: 1
        run time: 0.000
        instance time:
                min: 0.000, max: 0.000, avg: 0.000
        input records:
                StreamLineRead1: 3  (min: 3, max: 3, avg: 3)
        output records:
                StreamLineWrite2: 3  (min: 3, max: 3, avg: 3)
        metrics_output_count:
                StreamLineRead1: 3  (min: 3, max: 3, avg: 3)
                StreamLineWrite2: 3  (min: 3, max: 3, avg: 3)
        metrics_inner_time_ms:
                StreamLineRead1: 49  (min: 49, max: 49, avg: 49)        MaxInstance: 0
                StreamLineWrite2: 36  (min: 36, max: 36, avg: 36)       MaxInstance: 0


+------------+------------+------------+------------+
| id         | name       | id2        | name2      |
+------------+------------+------------+------------+
| 15         | 张三         | 15         | 张三         |
| 16         | 李四         | 16         | 李四         |
| 18         | 王五         | 18         | 王五         |
+------------+------------+------------+------------+

分区表

--创建分区表
odps@ work_test_1>create table if not exists t_jackson(id int,name string) partitioned by(dt string);

ID = 00000000153046739g24oi692
OK

--desc查看表信息
odps@ work_test_1>desc t_jackson;

+------------------------------------------------------------------------------------+
| Owner: ALIYUN$XXXX       | Project: work_test_1                                 |
| TableComment:                                                                      |
+------------------------------------------------------------------------------------+
| CreateTime:               0000-00-00 00:00:00                                      |
| LastDDLTime:              0000-00-00 00:00:00                                      |
| LastModifiedTime:         0000-00-00 00:00:00                                      |
+------------------------------------------------------------------------------------+
| InternalTable: YES      | Size: 0                                                  |
+------------------------------------------------------------------------------------+
| Native Columns:                                                                    |
+------------------------------------------------------------------------------------+
| Field           | Type       | Label | Comment                                     |
+------------------------------------------------------------------------------------+
| id              | bigint     |       |                                             |
| name            | string     |       |                                             |
+------------------------------------------------------------------------------------+
| Partition Columns:                                                                 |
+------------------------------------------------------------------------------------+
| dt              | string     |                                                     |
+------------------------------------------------------------------------------------+

OK


--desc extended 查看外部表信息
odps@ work_test_1>desc extended t_jackson;

+------------------------------------------------------------------------------------+
| Owner: ALIYUN$XXXX       | Project: work_test_1                                 |
| TableComment:                                                                      |
+------------------------------------------------------------------------------------+
| CreateTime:               0000-00-00 00:00:00                                      |
| LastDDLTime:              0000-00-00 00:00:00                                      |
| LastModifiedTime:         0000-00-00 00:00:00                                      |
+------------------------------------------------------------------------------------+
| InternalTable: YES      | Size: 0                                                  |
+------------------------------------------------------------------------------------+
| Native Columns:                                                                    |
+------------------------------------------------------------------------------------+
| Field    | Type   | Label | ExtendedLabel | Nullable | DefaultValue | Comment      |
+------------------------------------------------------------------------------------+
| id       | bigint |       |               | true     | NULL         |              |
| name     | string |       |               | true     | NULL         |              |
+------------------------------------------------------------------------------------+
| Partition Columns:                                                                 |
+------------------------------------------------------------------------------------+
| dt              | string     |                                                     |
+------------------------------------------------------------------------------------+
| Extended Info:                                                                     |
+------------------------------------------------------------------------------------+
| TableID:                  eeeeaacfab844a49c7d9616116cbbea                         |
| IsArchived:               false                                                    |
| PhysicalSize:             0                                                        |
| FileNum:                  0                                                        |
| StoredAs:                 AliOrc                                                   |
| CompressionStrategy:      normal                                                   |
+------------------------------------------------------------------------------------+

OK

--新增分区（注意：只能新增分区，不能新增分区字段）
odps@ work_test_1>alter table work_test_1.t_jackson add if not exists partition (dt="00000101");

ID = 0000000015480634gww7z592
OK

--tunnel上传本地.csv数据至该分区
--多个分区可以tunnel upload D:\#ODPS_D2\odps_data\data_t_jackson.csv work_test_1.t_jackson/dt="00000101",region="shanghai";
odps@ work_test_1>tunnel upload D:\#ODPS_D2\odps_data\data_t_jackson.csv work_test_1.t_jackson/dt="00000101";
Upload session: 000000005127c2dbdb0b144e163b
Start upload:D:\#ODPS_D2\odps_data\data_t_jackson.csv
Using \r\n to split records
Upload in strict schema mode: true
Total bytes:32   Split input to 1 blocks
0000-00-00 00:00:00      scan block: '1'
0000-00-00 00:00:00      scan block complete, block id: 1
0000-00-00 00:00:00      upload block: '1'
0000-00-00 00:00:00      upload block complete, block id: 1
upload complete, average speed is 32 bytes/s
OK

--查询一下数据有没有传成功
odps@ work_test_1>select * from work_test_1.t_jackson where dt="00000101";

ID = 00000000155424113gpvki692
Log view:
http://logview
Job Queueing...
Summary:
resource cost: cpu 0.00 Core * Min, memory 0.00 GB * Min
inputs:
        work_test_1.t_jackson/dt=00000101: 4 (449 bytes)
outputs:
Job run time: 0.000
Job run mode: service job
Job run engine: execution engine
M1:
        instance count: 1
        run time: 0.000
        instance time:
                min: 0.000, max: 0.000, avg: 0.000
        input records:
                TableScan1: 4  (min: 4, max: 4, avg: 4)
        output records:
                AdhocSink1: 4  (min: 4, max: 4, avg: 4)
        metrics_output_count:
                AdhocSink1: 4  (min: 4, max: 4, avg: 4)
                Project1: 4  (min: 4, max: 4, avg: 4)
                TableScan1: 4  (min: 4, max: 4, avg: 4)
        metrics_inner_time_ms:
                AdhocSink1: 96  (min: 96, max: 96, avg: 96)     MaxInstance: 0
                Project1: 0  (min: 0, max: 0, avg: 0)   MaxInstance: 0
                TableScan1: 0  (min: 0, max: 0, avg: 0) MaxInstance: 0


+------------+------------+------------+
| id         | name       | dt         |
+------------+------------+------------+
| 1          | 张三         | 00000101   |
| 2          | 李四         | 00000101   |
| 3          | 王五         | 00000101   |
| 4          | 赵六         | 00000101   |
+------------+------------+------------+
4 records (at most 10000 supported) fetched by instance tunnel.

偏偏秉烛夜游
午夜星辰似奔走之友
爱你每个结痂伤口
酿成的陈年烈酒

入喉尚算可口
怎么泪水还偶尔失守
邀你细看心中缺口
裂缝中留存温柔

此时已莺飞草长爱的人正在路上
我知他风雨兼程途经日暮不赏
穿越人海只为与你相拥
此刻已皓月当空爱的人手捧星光
我知他乘风破浪去了黑暗一趟
感同身受给你救赎热望

知道你不能还要你感受
让星光加了一点彩虹
让樱花偷偷吻你额头
让世间美好与你环环相扣
此时已莺飞草长爱的人正在路上
我知他风雨兼程途经日暮不赏
穿越人海只为与你相拥
此刻已皓月当空爱的人手捧星光
我知他乘风破浪去了黑暗一趟
感同身受给你救赎热望

此时已莺飞草长爱的人正在路上
我知他风雨兼程途经日暮不赏
穿越人海只为与你相拥
此刻已皓月当空爱的人手捧星光
我知他乘风破浪去了黑暗一趟
感同身受给你救赎热望

知道你不能还要你感受
让星光加了一点彩虹
当樱花开的纷纷扬扬
当世间美好与你环环相扣


世间美好与你环环相扣
柏松 
尹初七 
柏松 
彭圣杰