greenplum到oracle的dbi

为了解决greenplum到oracle的数据库的数据互联互通的问题，特提供的dbi_link做了研究与测试，dbi_link的基本原理是用Perl的DBI和相应数据库的DBD来访问异构数据库，实现数据的互访与数据传递，这次研究的目的是想解决oracle数据仓库到greenplum的数据传输的问题，这次的研究大概可以分为以下三个步骤：

一、配置：有关配置详情请见postgresql的专家我们兄弟公司何伟平(laser)同事写的一篇文章“使用dbi-link在PG里访问异构数据库http://www.pgsqldb.org/mwiki/index.php/%E4%BD%BF%E7%94%A8dbi-link%E5%9C%A8PG%E9%87%8C%E8%AE%BF%E9%97%AE%E5%BC%82%E6%9E%84%E6%95%B0%E6%8D%AE%E5%BA%93”，其中就如何配置dbi_link使postgresql访问异构数据访问有详细的说明与讲解，在这里要对laser表示感谢，在我研究greenplum的期间给于了大量的帮助与支持。

二、调试：由于目前dbi_link连接指的是postgresqlr与导构数据库数的联接，虽然greenplum是基于postgresql的数据库，但greenplum为使数据库有分布式与并行的能力屏蔽了postgresql的部分功能，所以在利用dib_link连接oracle的配置过程中运行:

SELECT make_accessor_functions('dbi:Oracle:laser;host=10.0.0.1;sid=dw1;port=1521', 'laser', 'oracle', '--- AutoCommit: 1 RaiseError: 1 ',NULL,NULL,NULL,'oracle_dw1');

时会报错，这其中一个问题是在greenplum中为了解决分布式的问题屏蔽了函数pg_catalog.currval(),这个函数的目的是当插入一个数据表记录时设为SERIAL类型字段会自动产生一个序列值

在数据没有提交的情况下要取这个值同时向另外一个表插入一条记录，其pk也为这个值时用的，为了绕过这个问题就得用到greenplum中的sequence的NEXTVAL()，具体代码见附件。

当调通后，当oracle指定schema下面的数据表大于2个时，在取oracle的数据字典，在greenplum上创建相应的对像时会报下面的错：

server closed the connection unexpectedly

        This probably means the server terminated abnormally

        before or while processing the request.

The connection to the server was lost. Attempting reset: Succeeded.

使客户端到pg服务端的连接断掉，这个问题目前还没有找到原因，猜测是有可能连接数的问题，还没有得到证实。

三、测试：测试主要是在pg的客户端对oracle数据库进行查询，createtable XXXX as select XXXX的方式传数据进行测试；另外测试是在greenplum与postgresql两个数据库上进行：

通过上面的测试发现greenplum通过dbi_link进行数据迁移是走不通的，基于这个原因，将不再进行进一

步的测试。

利用gpfdist 导入数据

外部数据装载 
1外部表的方式装载 
如果数据在一个segment服务器上，则可以用file://协议或者greenplum文件服务器gpfdist：//协议， 
若数据需要从多个segment上来，则必须用greenplum文件服务器的方式： 
1.1创建greenplum文件服务器： 
1-从$GPHOME/bin下面服务copy文件gpfdist到文件服务器上； 
2-启动gpfdist:$ gpfdist -d /var/load_files -p 8081 -l /home/gpadmin/log & 
1.2创建外部表： 
例如： 
CREATE EXTERNAL TABLE ext_expenses ( name text, 
date date, amount float4, category text, desc text ) 
LOCATION (‘gpfdist://etlhost:8081/*’, 
‘gpfdist://etlhost1:8081/*’) 
FORMAT ‘TEXT’ (DELIMITER ‘,’); 
1.3装载数据： 
数据装载很简单就用insert或create table XXX as select * from 外部表的方式创建，例如： 
1-insert方式 
INSERT INTO expenses_travel 
SELECT * from ext_expenses where category=’travel’; 
2-create table…as … 方式 
CREATE TABLE expenses AS SELECT * from ext_expenses; 

2COPY命令的方式装载 
Copy命令装载数据并不是并行装载，而且所装载的数据文件必须放在master服务器上，例如： 
COPY country FROM ‘/data/gpdb/country_data’ 
WITH DELIMITER ‘|’ LOG ERRORS INTO err_country 
SEGMENT REJECT LIMIT 10 ROWS;

greenplum学习笔记_segment Mirror与数据装载

目前Segment Mirror的创建方法有两种：一种是在数据库初始化时创建，一种是在已有的数据库上追加； 
4.1初始化创建 
在greenplum数据库初始化配置文件gp_init_config中有两个参数： 
1-配置mirror的端口号的： 
MIRROR_PORT_BASE=60000 
2-配置mirror的数据库的目录： 
declare -a MIRROR_DATA_DIRECTORY= 
       (/home/gpadmin/data1/gpdb_p0  /home/gpadmin/data1/gpdb_p1  
       /home/gpadmin/data1/gpdb_p2  /home/gpadmin/data1/gpdb_p3) 
4.2追加方式创建

通过gpaddmirrors函数来追加，实例如下： 
4.2.1通过gpaddmirrors –i追加 
1-定义mirror配置文件mirror_config_file,内容如下： 
mirror[0]=test177.sqa:60001:/home/gpadmin/data1/gpdb_p0 
mirror[1]=test177.sqa:60002:/home/gpadmin/data1/gpdb_p1 
mirror[2]=test177.sqa:60003:/home/gpadmin/data1/gpdb_p2 
mirror[3]=test177.sqa:60004:/home/gpadmin/data1/gpdb_p3 
mirror[4]=test178.sqa:60005:/home/gpadmin/data1/gpdb_p0 
mirror[5]=test178.sqa:60006:/home/gpadmin/data1/gpdb_p1 
mirror[6]=test178.sqa:60007:/home/gpadmin/data1/gpdb_p2 
mirror[7]=test178.sqa:60008:/home/gpadmin/data1/gpdb_p3 
2-通过gpaddmirrors命令追加： 
Gpaddmirrors –I /home/gpadmin/mirror_config_file; 
4.2.2通过gpaddmirrors –m追加 
1-定义mirror数据目录文件mirror_data_directory_file，内容如下： 
/home/gpadmin/data1/gpdb_p0 
/home/gpadmin/data1/gpdb_p1 
/home/gpadmin/data1/gpdb_p2 
/home/gpadmin/data1/gpdb_p3 
2-通gpaddmirrors命令追加： 
Gpaddmirrors –m /home/gpadmin/mirror_data_directory_file;

五、外部数据装载 
5.1外部表的方式装载 
如果数据在一个segment服务器上，则可以用file://协议或者greenplum文件服务器gpfdist：//协议， 
若数据需要从多个segment上来，则必须用greenplum文件服务器的方式： 
5.1.1创建greenplum文件服务器： 
1-从$GPHOME/bin下面服务copy文件gpfdist到文件服务器上； 
2-启动gpfdist:$ gpfdist -d /var/load_files -p 8081 -l /home/gpadmin/log & 
5.1.2创建外部表： 
例如： 
CREATE EXTERNAL TABLE ext_expenses ( name text, 
date date, amount float4, category text, desc text ) 
LOCATION (‘gpfdist://etlhost:8081/*’, 
‘gpfdist://etlhost1:8081/*’) 
FORMAT ‘TEXT’ (DELIMITER ‘,’); 
5.1.3装载数据： 
数据装载很简单就用insert或create table XXX as select * from 外部表的方式创建，例如： 
1-insert方式 
INSERT INTO expenses_travel 
SELECT * from ext_expenses where category=’travel’; 
2-create table…as … 方式 
CREATE TABLE expenses AS SELECT * from ext_expenses; 
5.2COPY命令的方式装载 
Copy命令装载数据并不是并行装载，而且所装载的数据文件必须放在master服务器上，例如： 
COPY country FROM ‘/data/gpdb/country_data’ 
WITH DELIMITER ‘|’ LOG ERRORS INTO err_country 
SEGMENT REJECT LIMIT 10 ROWS;

GreenPlum AdminGuide 阅读笔记

1、数据库与实例

不同于oracle数据库，一个实例对应一个数据库。GP可以在一个实例上建立多个数据库。

DB2也是一个实例下可以建立多个数据库，而oracle一个实例下通常只有一个数据库，其实可以一个数据库对应多个实例。

2、导入和导出

 GP数据加载卸载数据（ load unload，其实就是导入和导出） COPY不支持 并行操作 在多个节点上同时进行，copy命令是属于PostgreSQL的命令。

Create External Table Readable External Table 是load数据

Regular Web这种表只能读取 不支持DML Writeable External Table

3、根据template创建数据库

建立数据库基于template 每个新建的GP都有一个默认的数据库template1，

如果不指定模板，template1就是你创建数据库的模板。 在template1中不要创建任何数据对象，

除非你要在其他数据库中也要创建该对象。 除了template1还有template0，postgres 2个数据库 template0中没有任何数据对象，只是包含标准的数据对象。

4、创建数据库

数据库端：CREATE DATABASE dbname

客户端：CREATEDB -h hostname -p 5432 dbname

默认是根据template1来创建，可以指定template创建

create database dbname1 template dbname2

Drop database dbname；

DROPDB -h hostname -p 5432 dbname

5、Alter DATABASE dbname

set search_path to mychema

6、查询数据库

select datname from pg_database

6、表空间

不同的表空间存放在不同的磁盘上，高访问的表空间指定放在高配置的磁盘上。

表空间放置在不同segment的文件位置，收集这些文件系统的位置称为为filespace，

filespace可以被多个表空间所使用。 在一个逻辑文件系统中，你不能控制每一个文件的位置，因此没有必要为一个逻辑文件系统指定filespace

7、Create Schema schemaName

create schema schemaName authorization username

8、select current_schema();

show search_path;

9、DROP SCHEMA myschema CASCADE

schema下的所有object

10、数据类型

CHAR VARCHAR Text没有性能上的差距，而在其他数据上CHAR VARCHAR有性能差距

一般情况下 BIGINT 可以用INT或者SMALLINT代替

在join的时候 需要考虑数据类型的一致性

11、数据约束

create table AA（
c varchar(20) primary key,
a varchar（20） not null,
b numeric check(price >0)
）
DISTRIBUTED BY　ａ

Foreign Key 在4.0版本并不完全支持，分布在不同segment上的table对外键的校验支持不好

numberic(18,4)???????

12、选择分布策略

DISTRUBUTED BY

DISTRUBUTED RANDOMLY

表有主键或者第一列作为DT列，那么使用hash算法分布（ eligible ）

最好是采用每个表都有不同的值，进行分布

13、默认是采用B—Tree索引

Create index aaa on tablename（AA）

create index bbb on tablename using bitmap （gender）

唯一索引必须是DT列，唯一索引在append-only表中不支持

位图索引在OLAP数据库中性能比较好，适用于DML操作较少的数据库，但是占用空间较大，尤其对于大表来讲，位图索引甚至会比表中的索引数据还要大，一般情况下索引占用空间只是表中索引列一部分，位图索引数据压缩比较好，convert函数将bit position转换为行的id？？？？？

索引列在100至10000个不同的值下性能比较好，人的性别不同值比较少，超过10万个不同的值，位图索引的性能将下降，位图索引占用空间的大小与索引列的不同值成比例。

列的不同值超过10万个 一般不适用位图索引，不管表中数据有多少。

对于有大量DML操作的OLTP数据库不适用于位图索引

使用位图索引需要谨慎，一般是在测试性能改进后使用。

Bitmap indexes can dramatically improve query performance for ad hoc queries. AND and OR conditions in the WHERE clause of a query can be resolved quickly by performing the corresponding Boolean operations directly on the bitmaps before converting the resulting bitmap to tuple ids. If the resulting number of rows is small, the query can be answered quickly without resorting to a full table scan.

REINDEX tablename

REINDEX indexname