DWS与Greenplum一些语法差异总结
一.建表语法
1. DWS建表语法-分区表
create table green ( id int, school varchar(50), sale int, place varchar(50), begintime timestamp )with(orientation = column,compression = low) distribute by hash(id) partition by range(begintime) ( partition p1 values less than('2019-06-05 00:00:00'), partition p2 values less than('2019-06-06 00:00:00') );
普通表:
create table blue ( id int, school varchar(50), endtime timestamp )with(orientation=column,compression=low) distribute by hash(id);
2. Greenplum建表语法-分区表
create table green ( id int, school varchar(50), sale int, place varchar(50), begintime timestamp )with(appendonly = true, orientation = column) distributed by (id) partition by range(begintime) ( partition p1 start('2019-06-05 00:00:00') inclusive end('2019-06-06 00:00:00') exclusive, partition p2 start('2019-06-06 00:00:00') inclusive end('2019-06-07 00:00:00') exclusive, default partition default_p );
普通表:
create table blue ( id int, school varchar(50), endtime timestamp )with(appendonly = true, orientation = column) distributed by (id);
3. 建表语法差异点:
差异点 |
DWS |
GreenPlum |
行列存方式写法不同 |
orientation = column,compression = low |
appendonly = true, orientation = column |
分布列写法不同 |
distribute by hash(id) |
distributed by (id) |
分区写法不同 |
支持范围分区 |
支持范围分区,list分区,多级分区和快速分区 |
对分区表的查询方式不同 |
查询某个分区select * from green partition (p1) |
查询某个分区: select * from green_1_prt_p1 |
二.创建视图:(DWS和Greenplum的视图创建无差异)
create view person as select a.sex as sexa,b.sex as sexb from test_person a left join test_person1 b on a.id = b.id;
查询视图
select * from person;
三.创建函数:(下面这个函数两者无差异)
DWS:
create or replace function increment_plsql(i integer) returns integer as $$ begin return i+1; end $$ LANGUAGE plpgsql;
Greenplum:
create or replace function increment_plsql(i integer) returns integer as $$ begin return i+1; end $$ LANGUAGE plpgsql;
四.Greenplum导出文件
1.greenplum中可使用pg_dump备份数据库中的元数据
pg_dump -U {user_name} -W {password} -p {port} {database_name} -s -C -f dump.sql
2. greenplum中使用pg_dump备份数据库中的数据
pg_dump -U {user_name} -W {password} -p {port} {database_name} -a -C -f dump_data.sql
DWS可使用gsql客户端中提供的gs_dump工具,用法与pg_dump类似。
五.使用copy命令导入导出数据
Greenplum的copy用法:
1.csv格式导出,默认分隔符是","
copy test_person to '/home/gpadmin/jinyuze/test_person.csv' with csv;
2.csv格式导出,分隔符设置为"|"
copy test_person to '/home/gpadmin/jinyuze/test_person1.csv' with csv delimiter as '|';
使用copy命令从文件中导入数据到一张表中
1.copy jinyuze from '/home/gpadmin/jinyuze/result/test_person1.csv' with delimiter as '|'; 2.copy jinyuze from '/home/gpadmin/jinyuze/result/test_person.csv' with csv;
注:公有云DWS默认是安全模式,是禁止直接使用copy命令进行导入导出数据的,如果想要使用copy,需要在命令前面添加 "\",例如:
\copy jinyuze from '/home/gpadmin/jinyuze/result/test_person.csv' with csv;
- 点赞
- 收藏
- 关注作者
评论(0)