greenplum数据拷贝

1. copy的方式

PostgreSQL的COPY语句从外部文件拷贝数据到数据表中。它比INSERT语句插入多行的效率更高，但是行仍需流过Master。所有数据都在一个命令中被拷贝，它并不是一种并行处理。COPY命令的数据输入来自于一个文件或者标准输入使用COPY适合于增加相对较小的数据集合（例如多达上万行的维度表）或者一次性数据装载。在编写脚本处理装载少于1万行的少量数据时使用COPY。因为COPY是一个单一命令，在使用这种方法填充表时没有必要禁用自动提交。

使用者可以运行多个并发的COPY命令以提高性能。

• 创建csv文件，给予写权限

  cd /usr/local/greenplum-db-6.20.3
  mkdir data
  touch deliver.csv
  chmod 777 deliver.csv
  

• 切换到gpadmin用户

  su gpadmin
  

• 进入gp安装目录

  cd /usr/local/greenplum-db-6.20.3/bin
  

• 进入gp

  ./psql -p 5432 postgres
  

• 查看数据库列表

  \l
  

• 选择进入数据库

  \c gp_sydb
  

• 查看数据表

  \d
  

• 查看表空间大小

  select pg_size_pretty(pg_table_size('c_cs_e_deliver_receiver'));
  

• 导出csv文件

  copy c_cs_e_deliver to '/usr/local/greenplum-db-6.20.4/data/deliver.csv' with csv header;
  

• 根据csv文件加载表数据

  copy c_cs_e_deliver_1 from '/usr/local/greenplum-db-6.20.4/data/deliver.csv' with csv header;
  

2 使用Gpfdist的外部表

装载大型事实表的最快方式是使用gpfdist,gpfdist是一个使用http协议的文件服务器程序，它以并行的方式向greenplum数据库的segment供应外部文件。一个gpfdist的实例每秒能供应200MB数据并且很多gpfdist进程可以同时进行。每一个供应要被装载数据的一部分，当使用者使用insert into table select * from <> 进行装载时，insert 语句会被master解析分给主segment。segment连接gpfdist服务器且并行检索数据，解析并验证数据，从分布键数据计算一个hash值并基于hash把行发送到对应的segment。通过许多segment和gpfdit参与装载处理，可以以非常高的速率被装载。

• 同一数据库

insert into c_cs_e_deliver_receiver_1 select * from c_cs_e_deliver_receiver;

• 文件作为输入源

  • 基于uri创建外部表

    CREATE EXTERNAL TABLE receiver_file_view (
    "id" varchar(32),
      "pid" varchar(32),
      "ftype" varchar(32) ,
      "fvalue" varchar(256)
     )
    LOCATION ('file://host-2.15:5432/usr/local/greenplum-db-6.20.3/data/receiver.csv')
    FORMAT 'CSV' (HEADER);
    

  • 使用外部表装载数据

    insert into receiver_new select * from receiver_file_view;
    

辅助工具

• 查看查询进程活动

  select pg_stat_activity.pid,now()-pg_stat_activity.query_start as duration,pg_stat_activity.query
  from pg_stat_activity;
  

• 关闭进程

  select pg_cancel_backend(9649);
  

• 查看分片

  SELECT * FROM gp_segment_configuration;