postgresql-9.4greenplum

From postgres to greenplum via pxf UUID and INET types aint' supported?

So I am creating external table in greenplum based on the table i have in postgres.

And When I create field

field_name uuid

I am getting an error "Field type UNSUPPORTED_TYPE" Same goes to inet type.

What exactly doesn't support uuid type? Because I've read documentation and as far as I understand both postgres and greenplum support uuid type. So I take it the problem is in pxf connection itself?

FORMAT 'CUSTOM' (FORMATTER='pxfwritable_import')

maybe i need another formatter or something?

Solution

  • The full set of types that are supported by Greenplum PXF will depend on the PXF profile that you are reading from/writing to. For example, Apache ORC does not have a native type for UUID (Apache ORC - Types), but PXF will write UUIDs as strings into ORC files:

    CREATE WRITABLE EXTERNAL TABLE pxf_uuid_test_w (col1 int, col2 uuid)
LOCATION ('pxf://orc-data/uuid-test?&PROFILE=file:orc')
FORMAT 'CUSTOM' (FORMATTER='pxfwritable_export');
-- CREATE EXTERNAL TABLE

INSERT INTO pxf_uuid_inet_test_w VALUES (1, 'ceb6817b-0ef1-4167-971a-857f10d4afde');
-- INSERT 0 1

    However, PXF does not perform the implicit cast of UUID to string when using any of the Parquet profiles (*:parquet)

    CREATE WRITABLE EXTERNAL TABLE pxf_uuid_test_w (col1 int, col2 uuid)
LOCATION ('pxf://parquet-data/uuid-test?&PROFILE=file:parquet')
FORMAT 'CUSTOM' (FORMATTER='pxfwritable_export');
-- CREATE EXTERNAL TABLE

INSERT INTO pxf_uuid_test_w VALUES (1, 'ceb6817b-0ef1-4167-971a-857f10d4afde');
-- ERROR:  PXF server error : Type 2950 is not supported  (seg0 127.0.1.1:6000 pid=191734)
-- HINT:  Check the PXF logs located in the '/pxf-base/logs' directory on host 'localhost' or 'set client_min_messages=LOG' for additional details.

    As a workaround, you can create your external table using text, varchar, or char column types and include explicit casts where needed:

    CREATE TABLE uuid_test(col1 int, col2 uuid) DISTRIBUTED BY (col1);
INSERT INTO uuid_test values (1, 'ceb6817b-0ef1-4167-971a-857f10d4afde');
-- INSERT 0 1
CREATE WRITABLE EXTERNAL TABLE pxf_uuid_test_w (col1 int, col2 text)
LOCATION ('pxf://parquet-data/uuid-test?&PROFILE=file:parquet')
FORMAT 'CUSTOM' (FORMATTER='pxfwritable_export');
-- CREATE EXTERNAL TABLE

INSERT INTO pxf_uuid_inet_test_w SELECT col1, col2::text FROM uuid_test ;
-- INSERT 0 1