Working with unsupported data types with PXF and External Tables

[James]

Hello:

I see the PXF JDBC connector supports only certain data types: https://gpdb.docs.pivotal.io/6-4/pxf/jdbc_pxf.html#datatypes

1) Is this limitation referring to the data types of the "remote" table, of the "local" table, or both?

2) Is this limitation still present if the External Table is Readable rather than Writeable?

3) I'd like to create a Readable External Table whose Location—as specified using PXF—points to a large (~1TB) PostgreSQL table with columns having custom data types as well as data types outside the aforementioned whitelist. What would be the best way to achieve this?

Sincerely,

James

[James]

Hello all:

Pardon me for the additional message. I just want to check in, and see if anybody could provide answers to these questions.

―James

[Jon Roberts]

I would create a view in PostgreSQL that converts your unsupported data type column into supported datatypes.  With PXF, I would then query the view instead of the underlying table.  This would be a good time to convert something like JSON into relational columns too.

Jon Roberts

--
You received this message because you are subscribed to the Google Groups "Greenplum Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to gpdb-users+...@greenplum.org.
To view this discussion on the web visit https://groups.google.com/a/greenplum.org/d/msgid/gpdb-users/f4364c86-63a9-4d25-8690-07069945996d%40greenplum.org.

[Constantinos Antzakas]

Hello James, 

I previously did some similar investigation, and as far as I can tell and understand, the data types are neither remote or local, they are JDBC data types, and apply both to Readable/Writeable External Tables. Convert the complex data types to basic/supported data types on a view, just as Jon suggested, and then access via PXF.

Cheers,

Costas Antzakas

Sent from my iPhone, excuse my typos

On 27 Feb 2020, at 7:30 pm, Jon Roberts <jrob...@pivotal.io> wrote:



To view this discussion on the web visit https://groups.google.com/a/greenplum.org/d/msgid/gpdb-users/CAH_aXGMezaue8nOV26fi0xPGAV8K6JPKYmoiZevGXNz9Cw3d7w%40mail.gmail.com.

[yuwei...@gmail.com]

It is jdbc data types.

https://www.tutorialspoint.com/jdbc/jdbc-data-types.htm

PXF uses java types to convert your Sql data types to java types then to greenplum types (readable table), vice versa on writable table).

On Thu, Feb 27, 2020 at 12:12 PM James <ja...@cropcho.com> wrote:

--

You received this message because you are subscribed to the Google Groups "Greenplum Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to gpdb-users+...@greenplum.org.
To view this discussion on the web visit https://groups.google.com/a/greenplum.org/d/msgid/gpdb-users/f4364c86-63a9-4d25-8690-07069945996d%40greenplum.org.

--

Yu-wei Sung

[Francisco Guerrero]

Hi James,

I can help answer question number 3. You can still ship data from PostgreSQL to Greenplum if the table has unsupported data formats by "shipping" the data as TEXT and then casting it back to the type you want on Greenplum. I just tried doing the following on my local system:

In some PostgreSQL/Greenplum database:

CREATE TABLE non_supported_pxf_types (acidr CIDR, acircle CIRCLE, auuid UUID);

On Greenplum using PXF:

Create a table to read data from the external postres database, read all the non-supported types as text:

CREATE EXTERNAL TABLE read_from_non_supported_pxf_types (acidr TEXT, acircle TEXT, auuid TEXT)
LOCATION ('pxf://non_supported_pxf_types?PROFILE=jdbc&SERVER=postgres')
FORMAT 'CUSTOM' (FORMATTER='pxfwritable_import');

Then create an internal table to store your data, and cast it to the original type:

CREATE TABLE store_from_external_pxf (acidr CIDR, acircle CIRCLE, auuid UUID);

Finally, insert data into the internal greenplum table

INSERT INTO store_from_external_pxf
SELECT acidr::cidr, acircle::circle, auuid::uuid
FROM read_from_non_supported_pxf_types;

Please let me know if this is useful and if you have any additional questions.

Best,

- Francisco

[Francisco Guerrero]

Forgot this step to insert some data into the postgres database:

INSERT INTO non_supported_pxf_types VALUES ('198.24.10.0/24', CIRCLE(POINT(1.2, 123.1), 10) , 'c2d29867-3d0b-d497-9191-18a9d8ee7830');