Greenplum利用gpload,gpfist实现数据入库

转载：http://blog.csdn.net/stoneliul/article/details/8126700

1.python版本要求2.4.4以上

[python]  view plain  copy

1. [root@test install]# python  
2. Python 2.6.2 (r262:71600, May 14 2009, 10:46:21)   
3. [GCC 4.1.2 20080704 (Red Hat 4.1.2-44)] on linux2  
4. Type "help", "copyright", "credits" or "license" for more information.  
5. >>>   

2.PyYAML 3.10包配置

Downloads ↓

YAML parser and emitter for Python

YAML is a data serialization format designed for human readability and interaction with scripting languages. PyYAML is a YAML parser and emitter for Python.

PyYAML features a complete YAML 1.1 parser, Unicode support, pickle support, capable extension API, and sensible error messages. PyYAML supports standard YAML tags and provides Python-specific tags that allow to represent an arbitrary Python object.

PyYAML is applicable for a broad range of tasks from complex configuration files to object serialization and persistance.

[sql]  view plain  copy

1. [root@test dataload]# tar -zxvf PyYAML-3.10.tar.gz  

[sql]  view plain  copy

1. [root@test PyYAML-3.10]# python setup.py install  

3.yaml-0.1.4配置

下载源代码包：http://pyyaml.org/download/libyaml/yaml-0.1.4.tar.gz。编译和安装LibYAML

[sql]  view plain  copy

1. [root@test dataload]# tar -zxvf yaml-0.1.4.tar.gz  

[sql]  view plain  copy

1. [root@test dataload]# cd yaml-0.1.4  

[sql]  view plain  copy

1. [root@test yaml-0.1.4]# ./configure  

[sql]  view plain  copy

1. [root@test yaml-0.1.4]# make && make install  

4.gpload.gpfdist工具配置

下载：greenplum-loaders-4.2.1.0-build-2-RHEL5-x86_64.zip

[sql]  view plain  copy

1. [root@test dataload]# ./greenplum-loaders-4.2.1.0-build-2-RHEL5-x86_64.bin   

[sql]  view plain  copy

1. ********************************************************************************  
2.     Do you accept the Greenplum Loaders license agreement? [yes | no]  
3. ********************************************************************************  
4.   
5.    

选择：yes 到安装完成

修改greenplum_loaders_path.sh中GPHOME_LOADERS改为你安装的路径

.bash_profile中添加环境GP变量：

[sql]  view plain  copy

1. export PGDATABASE=gptest  
2. export PGHOST=127.0.0.1  
3. export PGPORT=5432  
4. export PGUSER=gpadmin  
5. export PGPASSWORD=gpadmin  

[sql]  view plain  copy

1. source  ~/.bash_profile  
2. 在相应文件夹中启动gpfdist

5.编写数据入库yaml控制文件

[sql]  view plain  copy

1. [root@test bin]# more gpload.yml   
2. ---  
3. VERSION: 1.0.0.1  
4. DATABASE: gptest  
5. USER: gpadmin  
6. HOST: 127.0.0.1  
7. PORT: 5432  
8. GPLOAD:  
9.   INPUT:  
10.     - SOURCE:  
11.         LOCAL_HOSTNAME:  
12.           - test  
13.         PORT: 55555  
14.         FILE:  
15.           - /home/tmp/test1  
16.     - COLUMNS:  
17.         - id: int  
18.         - name: text  
19.         - aa: text  
20.         - time: timestamp without time zone  
21.         - bb: text  
22.         - cc: text  
23.         - dd: int  
24.         - ee: int  
25.         - ff: text  
26.         - gg: text  
27.         - hh: text  
28.         - ii: text  
29.         - jj: text  
30.         - kk: text  
31.         - ll: text  
32.     - FORMAT: text  
33.     - DELIMITER: ','  
34.     - ERROR_LIMIT: 25  
35.   OUTPUT:  
36.     - TABLE: test_gpload  
37.     - MODE: INSERT  

注：COLUMNS中字段应与数据库中表字段及数据类型匹配

7.执行gpload

[sql]  view plain  copy

1. [root@test bin]# gpload -f gpload.yml  
2. 2012-10-30 00:06:42|INFO|gpload session started 2012-10-30 00:06:42  
3. 2012-10-30 00:06:43|INFO|started gpfdist -p 55555 -P 55556 -f "/home/tmp/test" -t 30  
4. 2012-10-30 00:06:50|INFO|running time: 7.92 seconds  
5. 2012-10-30 00:06:50|INFO|rows Inserted          = 205092  
6. 2012-10-30 00:06:50|INFO|rows Updated           = 0  
7. 2012-10-30 00:06:50|INFO|data formatting errors = 0  
8. 2012-10-30 00:06:50|INFO|gpload succeeded  
9.