Vertica destination support

[perttus]

Feature description

Is it possible to add support for Vertica as destination?

Are you a dlt user?

I'd consider using dlt, but it's lacking a feature I need.

Use case

Replace Informatica

Proposed solution

No response

Related issues

No response

[perttus]

I tried it with Postgres adapter since the dialect is very similar to Vertica. However, that didn't work out of the box. I get error

<class 'psycopg2.OperationalError'>
server didn't return client encoding

I used Vertica docker for testing together with chess pipeline example

[destination.postgres.credentials]
database = "verticatest"
password = ""
username = "dbadmin"
host = "localhost"
port = 5433
connect_timeout = 15

Where should I input the database schema or is dataset_name used for that?

There's already support for Vertica in DBT and Airbyte which is great. Maybe those implementations could be used to fix the connections issue here.

[rudolfix]

@perttus I'm sorry for missing this! It looks like that the protocol changed. Old Vertica python client indeed used psycopg2, now it is something different... SQL dialect still looks similar though.

We'll probably support Vertica first via sql alchemy destination #21

btw. if you have any documentation that shows how to enable postgres protocol then ping us. maybe there's still a chance to run it

[perttus]

@rudolfix maybe it's possible to run some operations via psycopg2 with Vertica but the official Python client is nowadays https://github.com/vertica/vertica-python which is used also in https://github.com/vertica/dbt-vertica adapter for example. Is it possible add support with the official client? It works out of the box with sqlalchemy just by adding vertica+vertica_python to connection string instead of postgresql+psycopg2. You just need to have https://github.com/vertica/vertica-sqlalchemy-dialect installed.

So I'm able to use Vertica as a source with help of this example but not as destination