DBT and Cockroachdb

[fire]

Is your feature request related to a problem? Please describe.

I want to use CRDB and DBT together.

See https://dagster.io/blog/dagster-dbt and https://www.getdbt.com/

Describe the solution you'd like A clear and concise description of what you want to happen.

Full support of cockroachdb in DBT.

Documentation / blog posts about combining the two.

Describe alternatives you've considered A clear and concise description of any alternative solutions or features you've considered.

Use postgresql.

Additional context Add any other context or screenshots about the feature request here.

CRDB in theory is compatible with postgres, but not always.

Jira issue: CRDB-3495

[blathers-crl[bot]]

Hello, I am Blathers. I am here to help you get the issue triaged.

I was unable to automatically find someone to ping.

If we have not gotten back to your issue within a few business days, you can try the following:

• Join our community slack channel and ask on #cockroachdb.
• Try find someone from here if you know they worked closely on the area and CC them.

_{:owl: Hoot! I am a Blathers, a bot for CockroachDB. My owner is otan.}

[rafiss]

Thanks for this issue! Could you share some more information about what errors or bugs you have encountered when trying to use DBT with CockroachDB?

[fire]

There is no documentation. No mention of anyone wanting it. So I was going to post the issue and see if anyone expressed interest.

[IS-Josh]

I'd be very interested in this.

[IS-Josh]

Hi, I just tried to run a DBT model on Cockraochdb.

On the latest version 20.1 DBT wont run on cockraochdb.

The error that is raised is

Encountered an error: Database Error there is already a transaction in progress

[IS-Josh]

Update - I've been able to get DBT to connect to Cockroachdb, and have modified a query against the system catalog to work on Cockroachdb, however the way Cockroachdb handles transactions is different to regular postgres. When the postgres driver opens a connection it has an implicit begin, DBT adds in secondary begin statements and these are not allowed in Cockroachdb, and the transaction errors. I've been playing around with over-riding some of the code in the connector to remove the begin statement with some luck, however one of the macro's to do with dropping tables if exists must use the same connection and I get an error. I'm sure someone who know's what they are doing a little more than me could figure out how to resolve the issue. In the mean time i'll continue to tinker, feel free to contact me.

[matheusccouto]

I am still experiencing this issue (Database Error there is already a transaction in progress). Did anyone came up with a workaround?

[IS-Josh]

Hi Matheus.. If you copy. the Postgres connector and remove transaction support it seems to work ok. I was able to do that as well as provide custom materializations to specify table locality.

leveraging transactions the way cockroach handles them is a lot more involved and above my time and capacity limit

[mingfang]

Are there any progress with getting CockroachDB working with DBT? This problem is a blocker for my company to adopt CockroachDB since all of our DB work in done in DBT.

[ogirardot]

any news or pointer for contributions ?

[Oixver]

3 years since this issue was opened. Any updates?

[alexeyegorov]

To support this request: I would also love to introduce CRDB for geospatial data in our company, but I won't do it without DBT support. :/