Currently our API's schema validation has a janky approach of attempting to do its best to validate JSON field's assumed types to the table's ClickHouse schema. However, given the number and complexity of ClickHouse types, building our own parser is deemed unrealistic and impossile to keep up to date or matching whatever clickhouse version our api is interacting with. As such, we have a few options (excluding building our own parser):
Option 1: Fast and Permissive
We don't attempt to validate types – or at least not all of them. At most we do a small subset of them with highly permissive fallbacks if they fail softly. This approach would basically just be our API doing fast JSON validation, maybe checking to see that for that table all required columns were present, and that perhaps a small portion of the typed columns looked roughly correct.
Pros
- Super fast
- Can be very light on memory if not serializing/deserializing JSON (although if in our custom format would need deserialized/reserialized anyway for proper ClickHouse insert format – unless our API/SDK build the ClickHouse JSON insert format by default anyway)
- Never rejects data ClickHouse would have accepted (no false negatives on schema validation)
Cons
- Non-standardized data being given to clients (ie timestamp normalization, etc) that normally happens in ClickHouse
- Nullable columns with ClickHouse's schema setting defaults not being filled in for streams to clients
- Allows for false positives on schema validation (allowing in events that don't meet the schema requirements and are rejected by ClickHouse at buffered ingest), which leads to cascading side-effects including:
- Invalid data being included in our streams to connected clients, which now must know how to handle errors
- Slowing down the buffered ingest process with higher failure rates
- Improperly communicating to a client sending data for insertion that it was accepted when it was rejected later
Option 2: ClickHouse NULL Engine Validation
Our process takes the tables it detects from the schema refresh and builds NULL engine tables in ClickHouse for internal use by our process only, which a different NULL table for each schema. Our API then does its normal roles/permissions checks on ingest, but then attempts to insert the data into the ClickHouse NULL engine table matching its desired table. This (I think?) can be done quickly/synchronously compared to how normal buffered inserts/ingest need to work, so it could give us a semi-instantaneous response on if the data was accepted (which does nothing – the data is just discarded in ClickHouse (or eventually could flow to materialized views, etc, and we could skip our buffer/ingest step 👀 )) or rejected. We then return the reject/accept and if accepted pass it along to continue our normal process.
Pros
- Tracks with the schema/versions of the actual version of ClickHouse a client is using
- Exactly follows and validates ClickHouse schema validation with 100% accuracy, no false positives or negatives
- Sets us up nicely for a potential later path with Materialized Views for more advanced realtime pipes and queries and internal ClickHouse table ingestion, without our own worker process
Cons
- Requires a full round-trip to the Clickhouse server and back, which may not be on the same host or network as our API Gateway, introducing significant delay in the hot ingestion path
- Could potentially bottleneck or strain ClickHouse compared to us doing best-effort pre-validation and less invasive buffered ingests
- Not particularly familiar with NULL engines and would need more research on them, but (I think) they still wouldn't return standardized data or data with defaults injected for clients to consume
Option 3: chDB embedded for schema/type validation
We'd use chDB (or parts of it, see below) inside the binary in our hot api ingest path. We'd use a NULL Engine (or just pure type-checking logic, see below again) with the schema we had for each table and would validate the incoming data against it, consuming the result including the normalized column data and pre-filled defaults for us, all locally on each edge API server. We'd return accept/reject based on that, and pass the now perfectly normalized and validated data on to our same WAL for buffered ingest to the real, beefier ClickHouse server wherever it may be and for clients to stream.
As for how to do this, we have two options:
3a: Embed chDB into our Go binary (either forcing us to abandon no CGO and make building and testing etc more complicated) or using purego and downloading the libchdb binary and linking it during runtime (I think?). TBD on if this would even matter a whole lot, given most people seem to deploy from container images nowadays anyway.
3b. Use the libchdb C++ pieces chDB has pulled out of ClickHouse and packaged into a dependency and reference them directly to do just the schema validation, type coersion, etc parts – NOT running the full in-memory database with its own process and workers to manage.
Pros
- Follows real ClickHouse implementation perfectly (assuming the chDB versions match ClickHouse's, but this is doable especially at runtime) so no false positive/negatives
- Gives us realtime WAL data with defaults and proper formatting for clients to consume
- Could (if using full chDB) let our WaveHouse binary/docker image be run on its own without ClickHouse for development or small deployment modes – much easier than setting up ClickHouse too – and would let us literally FORCE all CH SQL queries, setup, etc to flow through WaveHouse proxy layer – no way to bypass it at all
- No latency to other locations to do validation
- Doesn't put any extra strain on the beefy main ClickHouse server
Cons
- Much more complicated to pull off
- Potentially more surface area to fail, especially with linking and bundling and then running chDB or a portion of it
- CGO/purego complexities
- Unclear if 3b is even possible, and 3a would mean a subprocess to keep healthy, with its own RAM and CPU desires Go couldn't manage or garbage collect – so a lot more memory hungry and resource contention, also still potentially bottleneck on insert validation
Current Plan
Right now, we've agreed that Option 3 is the most compelling long-term, but that it would be too much of a burden to attempt right now. Thus, we are picking Option 1 (already more or less implemented and in progress) but are keeping this issue open to track both the downsides of our current implementation in option 1 (like no defaults filled and imperfect validation) and our desire to look into and attempt Option 3 further.
Currently our API's schema validation has a janky approach of attempting to do its best to validate JSON field's assumed types to the table's ClickHouse schema. However, given the number and complexity of ClickHouse types, building our own parser is deemed unrealistic and impossile to keep up to date or matching whatever clickhouse version our api is interacting with. As such, we have a few options (excluding building our own parser):
Option 1: Fast and Permissive
We don't attempt to validate types – or at least not all of them. At most we do a small subset of them with highly permissive fallbacks if they fail softly. This approach would basically just be our API doing fast JSON validation, maybe checking to see that for that table all required columns were present, and that perhaps a small portion of the typed columns looked roughly correct.
Pros
Cons
Option 2: ClickHouse NULL Engine Validation
Our process takes the tables it detects from the schema refresh and builds NULL engine tables in ClickHouse for internal use by our process only, which a different NULL table for each schema. Our API then does its normal roles/permissions checks on ingest, but then attempts to insert the data into the ClickHouse NULL engine table matching its desired table. This (I think?) can be done quickly/synchronously compared to how normal buffered inserts/ingest need to work, so it could give us a semi-instantaneous response on if the data was accepted (which does nothing – the data is just discarded in ClickHouse (or eventually could flow to materialized views, etc, and we could skip our buffer/ingest step 👀 )) or rejected. We then return the reject/accept and if accepted pass it along to continue our normal process.
Pros
Cons
Option 3: chDB embedded for schema/type validation
We'd use chDB (or parts of it, see below) inside the binary in our hot api ingest path. We'd use a NULL Engine (or just pure type-checking logic, see below again) with the schema we had for each table and would validate the incoming data against it, consuming the result including the normalized column data and pre-filled defaults for us, all locally on each edge API server. We'd return accept/reject based on that, and pass the now perfectly normalized and validated data on to our same WAL for buffered ingest to the real, beefier ClickHouse server wherever it may be and for clients to stream.
As for how to do this, we have two options:
3a: Embed chDB into our Go binary (either forcing us to abandon no CGO and make building and testing etc more complicated) or using purego and downloading the
libchdbbinary and linking it during runtime (I think?). TBD on if this would even matter a whole lot, given most people seem to deploy from container images nowadays anyway.3b. Use the
libchdbC++ pieces chDB has pulled out of ClickHouse and packaged into a dependency and reference them directly to do just the schema validation, type coersion, etc parts – NOT running the full in-memory database with its own process and workers to manage.Pros
Cons
Current Plan
Right now, we've agreed that Option 3 is the most compelling long-term, but that it would be too much of a burden to attempt right now. Thus, we are picking Option 1 (already more or less implemented and in progress) but are keeping this issue open to track both the downsides of our current implementation in option 1 (like no defaults filled and imperfect validation) and our desire to look into and attempt Option 3 further.