One Operation, Many Engines: A Cross-Engine Query Mental Model

Summary

• Scenario
  • The project I’m on uses ClickHouse, Spark, and MongoDB as data sources at once, and I often need to write raw SQL directly to grab a target result fast.
  • But I’m not fluent in raw SQL, so I mostly ask AI to generate a snippet. It runs, but I keep getting stuck in the same place: it wrote count(*), and I can’t remember whether there’s also a count(). Which one is ClickHouse-only (it supports both)? Does count(col) count NULLs? And to check whether a value is in an array, ClickHouse wants has(), Spark uses array_contains(), Mongo is $in?

• Motivation

  • I lean on AI every time and still never retain it, because all I have is a pile of scattered syntax, not a mental model. So I wanted to learn it once from scratch and reorganize what I know.
  • Also to sort out how to write Mongo queries (I use it sometimes too), plus the exact terms for the fields in Mongo Compass’s sidebar (for example, collection, which maps to a table in the relational world).

• Final goals

  • Learn a generic way to query the common data types in ClickHouse and Spark: struct, array, byte, and the like.
  • Know the abstract purpose of an operation, then map it onto how each of the three databases writes it, for example “find a substring inside a string” or “check whether an element is in an array.”

Refusing beats quietly being wrong. A query that “looks right but is wrong” is worse than none: it returns a number, the number looks normal, and nobody notices it counted the wrong rows until it’s already in a report. There’s an engineering principle I keep in mind: any path that can produce a “plausible but wrong” result should fail loudly, not pass silently. An AI query you can’t verify sits right on that line.

The counterintuitive turn: think in operations, don’t memorize syntax

The point isn’t to memorize all three sets of syntax; it’s to re-index what you know, from “syntax” to “the intent of an operation.” What you actually do to data comes down to a dozen-odd moves: count everything, count non-NULLs, default a NULL to something, search a substring, check array membership, map/filter an array, roll multiple rows into one array, branch on a condition. Each is an intent, and each engine just spells it differently.

The journey I laid out for this learning trip

1. Place the three engines first: ClickHouse and Spark both speak SQL (so roughly 80% is portable); Mongo is NoSQL, so it’s its own independent query language.
2. Align the vocabulary: table = collection, row = document, column = field. → MongoDB operators and vocabulary
3. Learn the types first: operations on array/struct/map only make sense once you know what “array” actually means in each engine (ClickHouse arrays even start at index 1). → ClickHouse data types, Spark data types
4. Learn the portable core once: COUNT, CASE WHEN, GROUP BY/HAVING, LIKE/ILIKE, IN/BETWEEN/IS NULL, and that’s the 80%. → the portable SQL core, count(*) vs count(col), LIKE / ILIKE substring search
5. Then learn each engine’s own array syntax: ClickHouse’s arrayMap/Filter/Exists/All, multiIf, has → ClickHouse array functions; Spark’s transform/filter/exists/forall, collect_list/set → Spark array functions and collect.
6. Carry the same operations over to Mongo by analogy: $regex ≈ LIKE, $in ≈ IN, $exists checks whether a field is present.

The conclusion up front: the operation × engine table I ended up with

Entries marked † are Mongo syntax that goes beyond the original notes, pulled from standard MongoDB (query or aggregation operators). Double-check before using.

Operation (intent)	Portable SQL	ClickHouse	Spark SQL	MongoDB
Count all rows (incl. NULL)	COUNT(*)	count(*) / count()	count(*)	countDocuments({}) †
Count non-NULL	COUNT(col)	count(col)	count(col)	{col:{$ne:null}} †
Default value for NULL	—	ifNull(x,d)	NVL(x,d)	$ifNull †
Substring search	LIKE / ILIKE	LIKE / ILIKE	LIKE / rlike	{f:{$regex,$options:"i"}}
Array membership check	—	has(arr,v)	array_contains(arr,v)	{f:{$in:[v]}}
Any element matches	—	arrayExists(x->…,arr)	exists(arr,x->…)	$elemMatch †
All elements match	—	arrayAll(x->…,arr)	forall(arr,x->…)	—
Map over array elements	—	arrayMap(x->…,arr)	transform(arr,x->…)	$map †
Filter an array	—	arrayFilter(x->…,arr)	filter(arr,x->…)	$filter †
Roll rows into an array	—	groupArray() / groupUniqArray()	collect_list() / collect_set()	$push / $addToSet †
Conditional branching	CASE WHEN	if() / multiIf()	CASE WHEN ... THEN ... ELSE ...	$cond / $switch †

Closing

By the end I realized there’s very little to actually memorize: one list of operations, plus one table that translates it into each engine’s syntax. AI can help me write SQL, but I have to know what that SQL is actually asking. A query that errors out isn’t the scary one; the scary one is the query that runs perfectly clean and answers the wrong question.

Test yourself → cross-engine query exercises (with answers).

---

References

• ClickHouse SQL Reference: https://clickhouse.com/docs/en/sql-reference
• Spark SQL Built-in Functions: https://spark.apache.org/docs/latest/api/sql/
• MongoDB Query & Aggregation Operators: https://www.mongodb.com/docs/manual/reference/operator/