Rendering Light with Logic: Ray Tracer in SQL

This project, hosted at ClickHouse/RayTracer, is a sophisticated path tracer implemented entirely through ClickHouse SQL queries. It leverages the database's native PNG output format to render images directly from a query result.

The Core Constraint: No User-Defined Functions (UDFs) and no external application code are used during the render. A single SELECT statement is responsible for calculating every single pixel.

The primary demonstration renders the word "ClickHouse" in chrome, glassy letters—a nod to Andrew Kensler's legendary Pixar business-card ray tracer. In the main scene, these letters float above a procedurally generated landscape, casting shadows and reflecting the environment.

🌐 Related SQL Experiments

• NoiSQL: Creating music via SQL.
• SQL Mandelbrot: Fractal generation.
• Click-V: A RISC-V emulator in SQL.
• DOOMHouse: A 3D "Doom-style" engine built in ClickHouse SQL.

---

🛠️ Technical Architecture

The renderer treats the image as a dataset where pixels are rows.

The Pipeline

1. Generation: numbers_mt(width * height * samples) creates a row for every sample of every pixel.
2. Coordinates: X and Y positions are derived using pixel % width and intDiv(pixel, width).
3. Aggregation: Samples are averaged via GROUP BY pixel, producing final r, g, b values (ranging from $0$ to $1$).
4. Parallelism: Because coordinates are explicit, no ORDER BY is required, allowing the workload to scale across all available CPU cores.

Linear Algebra & Logic

Vectors are represented as Tuple(Float64, Float64, Float64). To keep the SQL readable, linear algebra operations are wrapped in short lambda aliases:

• va, vs, vm, vd, vn, vc, vref (handling dot products, normalization, addition, etc.).

The Loop Mechanism

Initially, the project used WITH RECURSIVE CTEs. Now, every ray is advanced through mirror bounces using a fold step over range(maxDepth). This ensures that rows remain independent and fully parallel.

The "Let" Binding Trick: Since ClickHouse WITH lambdas are call-by-name (which can cause the query tree to explode), the author uses a one-element array to bind values:

arrayMap(x -> body, [expr])[1]

---

📐 Geometry & Terrain

Constructive Solid Geometry (CSG)

The scene utilizes various primitives and boolean operations:

The engine supports union and difference operations (e.g., subtracting a box from a ring to create the gaps in the letter 'C').

Procedural Terrain

The landscape is defined by a height field: $z = \text{amp} \cdot \text{fBm}(x, y)$ Where $\text{fBm}$ (Fractional Brownian Motion) sums multiple octaves of lattice value-noise.

Terrain Features:

• Ray-Marching: The algorithm skips empty air and uses linear interpolation at the surface crossing to prevent "step-banding."
• Shading: Uses a height-based color ramp:
  • $\text{Water} \rightarrow \text{Sand} \rightarrow \text{Grass} \rightarrow \text{Rock} \rightarrow \text{Snow}$
• Lighting: A combination of a warm sun, cool sky-ambient light, distance fog, and marched shadows (both self-shadowing and cast shadows from letters).

---

🖼️ Gallery & Execution

Rendered Scenes

• ClickBulb: A desk lamp made of spheres that moves through the scene.
• Pixar Homage: Chrome letters over a checkerboard.
• CSG Primitives: Letters carved from basic shapes.
• Combined: The hero image featuring the terrain.

How to Run

Queries are parameterized. Image dimensions are pulled from getSetting, and samples per pixel are passed via {SAMPLES:UInt32}.

Example Command:

clickhouse local --output_format_image_width 2560 --output_format_image_height 1200 \
--param_SAMPLES 8 --queries-file queries/clickhouse_terrain.sql out.png

Query Reference

Note on Generation: The SQL files are produced by Python scripts in generators/. While image size and samples are runtime parameters, the scene geometry and bounce depth are baked into the SQL during generation.

Example generation: python3 generators/gen_combined.py 2560 1200 8 2 scene.sql (where 2 is the bounce depth).