New Source/Destination: GreenPlum

[ChenpiDog]

Connector Name

GreenPlum

What type of integration

None

Revelant Information

Both source and destination need to add greenplum.

Reference：https://docs.vmware.com/en/VMware-Greenplum/6/greenplum-database/landing-index.html#differences-compared-to-open-source-greenplum-database

Contribute

• Yes, I want to contribute

[skynet2]

Hey,

Basically i was able to connect airbyte and greenplum.

Requires greenplum 7.0+
Airbyte 0.50.34

Note Greenplum 6.X will not work because of index syntax (create index if not exists) is not supported in 6.x but its supported in 7.x.

1. Once you created your database in Greenplum before executing sync you also need to run few SQL commands

CREATE OR REPLACE FUNCTION after_table_create()
RETURNS event_trigger AS $$
DECLARE
    obj record;
    tableName text;
BEGIN
    FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands()
    LOOP
        IF obj.object_type = 'table' THEN
            tableName := obj.object_identity;
            IF tableName NOT LIKE '%airbyte%' AND tableName NOT LIKE '%_scd' and tableName not like '%_dbt_%' THEN
                EXECUTE 'ALTER TABLE ' || tableName || ' SET DISTRIBUTED BY (_airbyte_unique_key)';
            END IF;
            IF tableName LIKE '%_scd' THEN
                EXECUTE 'ALTER TABLE ' || tableName || ' SET DISTRIBUTED BY (_airbyte_unique_key_scd)';
                EXECUTE 'create unique index if not exists "ix_scd_key_' || tableName || '" on ' || tableName || '(_airbyte_unique_key_scd)';
            END IF;
        END IF;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

CREATE EVENT TRIGGER trigger_on_create_table
ON ddl_command_end
WHEN TAG IN ('CREATE TABLE', 'CREATE TABLE AS')
EXECUTE FUNCTION after_table_create();

This trigger is required otherwise airbyte cannot create final table normalization as greenplum can not create a unique index on random distribution.

2. Use standard PostgreSQL destination

[samplec0de]

Hello! This is not efficient. It worth to creating integration with Greenplum via pxf. We`ll be very glad and happy if this appears in Airbyte.