brooklyn-data
Clickhouse Support
Overview
Support Clickhouse.
What problem would this solve?
Currently when I try to run dbt run --select dbt_artifacts it generates errors from Clickhouse such..
Code: 70. DB::Exception: Cannot convert NULL to a non-nullable type: In scope WITH dummy_cte AS (SELECT 1 AS foo) SELECT CAST(NULL, 'String') AS command_invocation_id, CAST(NULL, 'String') AS node_id, CAST(NULL, 'DateTime') AS run_started_at, CAST(NULL, 'String') AS name, CAST(NULL, 'String') AS type, CAST(NULL, 'String') AS owner, CAST(NULL, 'String') AS maturity, CAST(NULL, 'String') AS path, CAST(NULL, 'String') AS description, CAST(NULL, 'String') AS url, CAST(NULL, 'String') AS package_name, CAST(NULL, 'String') AS depends_on_nodes, CAST(NULL, 'String') AS tags, CAST(NULL, 'String') AS all_results FROM dummy_cte WHERE 1 = 0. (CANNOT_CONVERT_TYPE) (version 24.11.1.2557 (official build))
Would you be willing to contribute?
- [ ] Yes - I'm on it!
- [x] Yes, but I'd need help getting started.
- [ ] No
Disclaimer, I've never contributed to this code base, but this enhancement would also be useful to my team and I, so I took a look at the code base.
From what I can tell, each database/data sink w/ custom operators/syntax has its own set of macros, here's a macro for spark as an example.
The same exists for PostgreSQL, SQLServer, BigQuery, etc. These macros are directly used/prefixes inferred (I believe) based on your profiles.yml configuration file. That's the same place you set the post-run hook. I assume adding a macro w/ clickhouse's SQL flavor in the same locations as the spark examples (full code search here) is probably sufficient.
You'll also notice that there are default_ macros that are likely being used when this package doesn't recognize the driver/profile (example). Given that Clickhouse is "close" to PostgreSQL w/ exceptions, most of these defaults probably work as is. I expect the only places you'll need to update are:
- The insert table macro
- The seeds code since CAST looks different in Clickhouse
Lots of other instances of CAST that might need changes too.
Hi @avbhandaru , You're absolutely right, and I appreciate your thorough analysis! I've actually worked on this last week and implemented the necessary changes for supporting ClickHouse. You can find my code here. I'll be opening a PR soon after completing the required tests.
If you have the chance to run my code, I’d love to hear your feedback—there’s always room for improvement. Thanks again for your input—it’s always great to have collaborative feedback!
@Y-55 Awesome! I'll give it a shot and report back (will shoot for mid next week)
