MaxCompute 往分区表插入数据

一、创建分区表user

CREATE TABLE user 
( user_id BIGINT, gender BIGINT COMMENT '0 unknow,1 male, 2 Female', age BIGINT) 
PARTITIONED BY (region string, dt string);

二、添加分区

ALTER TABLE user ADD IF NOT EXISTS PARTITION(region='guangzhou',dt='20180725');

三、往分区插入数据

  方式一：直接插入

INSERT INTO user PARTITION(region='guangzhou',dt='20180725') VALUES(1,1,24);

  方式二：利用select 子句插入    

CREATE TABLE user_input(user_id BIGINT, gender BIGINT, age BIGINT);
INSERT INTO user_input VALUES(2,1,26);
INSERT INTO user_input VALUES(3,2,26);
INSERT INTO user PARTITION(region='guangzhou',dt='20180725') SELECT user_id,gender,age FROM user_input;

  方式三：利用 tunnel 工具上传数据文件

四、查询分区数据

SELECT * FROM user WHERE region='guangzhou'and dt='20180725';

+------------+------------+------------+------------+------------+
| user_id    | gender     | age        | region     | dt         |
+------------+------------+------------+------------+------------+
| 2          | 1          | 26         | guangzhou  | 20180725   |
| 3          | 2          | 26         | guangzhou  | 20180725   |
| 1          | 1          | 24         | guangzhou  | 20180725   |
+------------+------------+------------+------------+------------+
3 records (at most 10000 supported) fetched by instance tunnel.