Presto DDL DML示例

CREATE TABLE IF NOT EXISTS employees_nopartition
 (emp_no    BIGINT  COMMENT '职员编号',
 birth_date   DATE   COMMENT '生日',
 first_name   VARCHAR COMMENT '名',
 last_name   VARCHAR COMMENT '姓',
 gender      VARCHAR COMMENT '性别',
 hire_date    DATE   COMMENT '入职日期');


CREATE TABLE IF NOT EXISTS employees_partition
 (emp_no    BIGINT  COMMENT '职员编号',
 birth_date   DATE   COMMENT '生日',
 first_name   VARCHAR COMMENT '名',
 last_name   VARCHAR COMMENT '姓',
 gender      VARCHAR COMMENT '性别',
 hire_date    DATE   COMMENT '入职日期')
 PARTITIONED BY(gd VARCHAR, hd VARCHAR);

CREATE TABLE IF NOT EXISTS test
(t1    BIGINT,
t2     DOUBLE);


CREATE TABLE IF NOT EXISTS test2
(t1    BIGINT,
t2     DOUBLE);

INSERT INTO TABLE test VALUES (1,1.0);
INSERT INTO TABLE test2 VALUES (1,1.0), (2,2.0);

INSERT OVERWRITE TABLE test VALUES (1,1.0);
INSERT OVERWRITE TABLE test2 VALUES (1,1.0), (2,2.0);

INSERT INTO TABLE test SELECT * FROM test2;
INSERT OVERWRITE TABLE test SELECT * FROM test2;

INSERT INTO TABLE employees_nopartition
VALUES(1, DATE '2015-03-11', 'dd', 'dd', 'dd', DATE '2015-03-11'),
(2, DATE '2015-03-11', 'dd', 'dd', 'dd', DATE '2015-03-11');


INSERT INTO TABLE employees_partition 
PARTITION (gd='gd', hd='hd') 
SELECT *, 'gd' gd, 'hd' hd FROM employees_nopartition;


INSERT OVERWRITE TABLE employees_partition 
PARTITION (gd='gd', hd='hd') 
SELECT *, 'gd' gd, 'hd' hd FROM employees_nopartition;


INSERT INTO TABLE employees_partition 
PARTITION (gd='gd', hd='hd') 
VALUES(1, DATE '2015-03-11', 'dd', 'dd', 'dd', DATE '2015-03-11', 'gd', 'hd');


INSERT INTO TABLE employees_partition 
PARTITION (gd='gd', hd='hd') 
VALUES(1, DATE '2015-03-11', 'dd', 'dd', 'dd', DATE '2015-03-11', 'gd', 'hd'),
(2, DATE '2015-03-11', 'dd', 'dd', 'dd', DATE '2015-03-11', 'gd', 'hd');


INSERT INTO TABLE employees_partition 
PARTITION (gd, hd) 
SELECT *, gender gd, date_format(hire_date,'%Y') hd FROM employees_nopartition;


INSERT OVERWRITE TABLE employees_partition 
PARTITION (gd, hd) 
SELECT *, gender gd, date_format(hire_date,'%Y') hd FROM employees_nopartition;


INSERT INTO TABLE employees_partition 
PARTITION (gd, hd)
VALUES(1, DATE '2015-03-11', 'dd', 'dd', 'dd', DATE '2015-03-11', 'gd', 'hd');


INSERT INTO TABLE employees_partition 
PARTITION (gd, hd)
VALUES(1, DATE '2015-03-11', 'dd', 'dd', 'dd', DATE '2015-03-11', 'gd', 'gd'),
(2, DATE '2015-03-11', 'dd', 'dd', 'dd', DATE '2015-03-11', 'gd', 'hd');

INSERT OVERWRITE TABLE employees_partition 
PARTITION (gd, hd)
VALUES(1, DATE '2015-03-11', 'dd', 'dd', 'dd', DATE '2015-03-11', 'gd', 'hd');


INSERT OVERWRITE TABLE employees_partition 
PARTITION (gd, hd)
VALUES(1, DATE '2015-03-11', 'dd', 'dd', 'dd', DATE '2015-03-11', 'gd', 'gd'),
(2, DATE '2015-03-11', 'dd', 'dd', 'dd', DATE '2015-03-11', 'gd', 'hd');


DROP TABLE IF EXISTS test;
DROP TABLE IF EXISTS test2;
DROP TABLE IF EXISTS employees_partition;
DROP TABLE IF EXISTS employees_nopartition;