dap initdb error with Redshift: "ERROR - database connection error: unknown type: pg_catalog.json"

[prj_site_commun]

I'm using this command:

dap initdb --connection-string postgresql://USER:PASSWORD@HOST:PORT/DB --namespace NS --table access_tokens

But I get this error (it's the same for any table):

2023-12-14 04:55:37,658 - ERROR - database connection error: unknown type: pg_catalog.json

I think this is looking for a data type called "json" in pg_catalog.pg_type and complaining that it's not there.  Is that right?  I've looked at pg_type myself and verified that there is no json entry.  Are there any special options needed in Redshift to enable the json type?

[ColinMurtaugh]

This doc may be helpful: 

https://docs.aws.amazon.com/redshift/latest/dg/federated-data-types.html

The tl;dr is that it looks like Redshift doesn't support the JSON data type natively. I'd consider using an RDS postresql database instead. 

--Colin

[LeventeHunyadi]

While the interfaces of PostgreSQL and AWS Redshift are superficially similar, there are subtle differences. Some data types that exist in PostgreSQL are not supported by Redshift. The same applies to query types. For example, DAP client library makes use of combined INSERT or UPDATE statements (a.k.a. MERGE), and these are not supported by Redshift either.

If you want to insert data into a Redshift table, the following steps might help:

• Trigger an incremental query with the output format Parquet.
• Upload the Parquet files to AWS S3.
• Create a temporary table in Redshift (CREATE TEMP TABLE) that has the exact same structure as the table to be updated.
• Use the COPY command in Redshift to load the data into the temporary table.
• Use DELETE to remove those rows in the target table that have a matching primary key in the temporary table.
• Use INSERT to add all rows in the temporary table to the target table.

We are experimenting with automating the above process, and as such DAP client library may support this flow in the future. However, when (or if) this feature is added is subject to the product roadmap.

As of today, DAP client library only supports PostgreSQL and MySQL as target databases. Future versions will come with Microsoft SQL Server and Oracle connectivity enabled. For more information about which database engines are officially supported, check out the client library documentation.