如何从Oracle迁移到Greenplum 第二篇

pg_dump  -s  -n pivotal >pivotal.sql

psql –af pivotal.sql >import.log 2>&1

ALTER DATABASE pivotal SET gp_create_table_random_default_distribution=on;
ALTER DATABASE pivotal  SET gp_default_storage_options='appendonly=true,blocksize=32768,compresstype=zlib,compresslevel=5,checksum=true,orientation=row';

pivotal=# CREATE TABLE test2(a int,b int,primary key (b,a)) DISTRIBUTED BY (a);
ERROR:  PRIMARY KEY and DISTRIBUTED BY definitions incompatible
HINT:  When there is both a PRIMARY KEY, and a DISTRIBUTED BY clause, the DISTRIBUTED BY clause must be equal to or a left-subset of the PRIMARY KEY

psql -af usr/local/greenplum-db/share/postgresql/contrib/dblink.sql

wget http://www.onexsoft.com/software/sqluldr2linux64.zip
vi $ORACLE_HOME/network/admin/tnsnames.ora

export NLS_LANG="SIMPLIFIED CHINESE"_CHINA.ZHS16GBK
sqluldr2 user='username'/'password'@tnsname query="select /* parallel(2) */ * from PICCPROD.T_CONTRACT_MASTER" text=CSV file="/data/ods/test.dat" log=/tmp/sqluldr2.log

CREATEcreate EXTERNALexternal TABLEtable ext.t_contract_master_ext (like t_contract_master) 
LOCATION('gpfdist://10.111.224.1:9999/T_CONTRACT_MASTER.dat') FORMAT 'CSV' (HEADER delimiter as ',') ENCODING 'GB18030' LOG ERRORS SEGMENT REJECT LIMIT 2 rows;

for i in *.dat;do tr < $i -d '\000' > nonull/$i & <&-;done

/usr/local/bin/iconv -f GB18030 -t GB18030 --byte-subst="<0x%x>" T_CLAIM_CASE.dat

CREATE TABLE hello(id int,name varchar2(32),price number);
INSERT INTO hello SELECT rownum,rownum,rownum*0.1 FROM dual Connect By Rownum <= 100;

CREATE TABLE hello(id int,name varchar(32),price numeric);
INSERT INTO hello select i,i,i*0.1 FROM generate_series(1,100) i;

select count(*) from hello;

select count(*),sum(price),avg(price) from hello;

WITH foo AS (SELECT stragg(lower(standard_hash(id||name||to_char(price,'fm999999990.999999999'), 'MD5')) order by lower(standard_hash(id||name||to_char(price,'fm999999990.999999999'), 'MD5')) asc) AS total_md5 from hello ) SELECT lower(standard_hash(total_md5, 'MD5')) AS md5 FROM foo;

WITH foo AS (select string_agg(md5(id||name||to_char(price,'fm999999990.999999999')) order by md5(id||name||to_char(price,'fm999999990.999999999')) asc) AS total_md5  FROM hello) SELECT md5(total_md5) AS md5 FROM foo;

WITH foo AS (SELECT standard_hash(''||id||name||to_char(price,'fm999999990.999999999'),'MD5') AS hash FROM hello),
foo1 as (SELECT sum(to_number(substr(hash, 1, 8),'xxxxxxxx')) AS hash_p1,sum(to_number(substr(hash, 9, 8),'xxxxxxxx')) AS hash_p2,sum(to_number(substr(hash, 17, 8),'xxxxxxxx')) AS hash_p3,sum(to_number(substr(hash, 25, 8),'xxxxxxxx')) AS hash_p4 FROM foo)
SELECT lower(standard_hash(sum(hash_p1)||sum(hash_p2)||sum(hash_p3)||sum(hash_p4),'MD5')) AS md5 FROM foo1;

WITHwith foo ASas (SELECTselect md5(''||id||name||to_char(price,'fm999999990.999999999')) ASas hash FROMfrom hello),
foo1 ASas (SELECTselect sum(('x' || substring(hash, 1, 8))::bit(32)::bigint) ASas hash_p1,sum(('x' || substring(hash, 9, 8))::bit(32)::bigint) as hash_p2,sum(('x' || substring(hash, 17, 8))::bit(32)::bigint) ASas hash_p3,sum(('x' || substring(hash, 25, 8))::bit(32)::bigint) ASas hash_p4 FROMfrom foo)
SELECTselect md5(''||sum(hash_p1)||sum(hash_p2)||sum(hash_p3)||sum(hash_p4)) ASas md5 FROMfrom foo1;

WITH oracle_checksum AS (SELECT id,md5(textin(record_out(foo2))) AS row_md5 FROM ora_hello AS foo2),
gp_checksum AS (SELECT id,md5(textin(record_out(foo3))) AS row_md5 FROM gp_hello AS foo3),
compare_result AS (SELECT oracle_checksum.id AS ora_id ,oracle_checksum.row_md5 AS ora_md5,gp_checksum.id AS gp_id,gp_checksum.row_md5 AS gp_md5 FROM oracle_checksum full outer join gp_checksum on oracle_checksum.row_md5=gp_checksum.row_md5)
SELECT * FROM compare_result WHERE ora_md5 is null or gp_md5 is null;

CREATE OR REPLACE FUNCTION checksum(tablename character varying)
RETURNS text AS
$BODY$
DECLARE
   v_tablename varchar := $1;
   v_md5 varchar;
   v_check_sql varchar;
BEGIN
            v_check_sql := $$with foo as (select md5(textin(record_out(foo2))) as hash FROM $$||v_tablename ||$$ as foo2), foo1 as (select sum(('x' || substring(hash, 1, 8))::bit(32)::bigint) as hash_p1,sum(('x' || substring(hash, 9, 8))::bit(32)::bigint) as hash_p2,sum(('x' || substring(hash, 17, 8))::bit(32)::bigint) as hash_p3,sum(('x' || substring(hash, 25, 8))::bit(32)::bigint) as hash_p4 from foo) select md5(''||sum(hash_p1)||sum(hash_p2)||sum(hash_p3)||sum(hash_p4)) from foo1;$$;
            EXECUTE v_check_sql INTO v_md5;
            RETURN v_md5;
END;
$BODY$
LANGUAGE 'plpgsql' immutable ;