Ray Tracing on BigQuery (for free of charge)

_raytracing_on_bigquery.png

raytrace.sql

-- Dot product for 3d point
CREATE TEMPORARY FUNCTION DOT
  (a STRUCT<x FLOAT64, y FLOAT64, z FLOAT64>,
   b STRUCT<x FLOAT64, y FLOAT64, z FLOAT64>)
  AS (
    a.x*b.x + a.y*b.y + a.z*b.z
  )
;
-- Linear combination aP + bQ
CREATE TEMPORARY FUNCTION COMB
  (a FLOAT64, p STRUCT<x FLOAT64, y FLOAT64, z FLOAT64>,
   b FLOAT64, q STRUCT<x FLOAT64, y FLOAT64, z FLOAT64>)
  AS (
    STRUCT( a*p.x+b*q.x AS x, a*p.y+b*q.y AS y, a*p.z+b*q.z AS z)
  )
;
-- Gets unit vector
CREATE TEMPORARY FUNCTION UNIT
  (r STRUCT<x FLOAT64, y FLOAT64, z FLOAT64>)
  AS (
    STRUCT( r.x/SQRT(DOT(r,r)) AS x, r.y/SQRT(DOT(r,r)) AS y, r.z/SQRT(DOT(r,r)) AS z)
  )
;
-- Clamps within [0, 255]
CREATE TEMPORARY FUNCTION CLAMP255
  (f FLOAT64)
  AS (
    CASE WHEN f < 0 THEN 0
         WHEN f > 1 THEN 255
         ELSE CAST(ROUND(IFNULL(f, 0) * 255.) AS INT64)
    END
  )
;

-- Calculates reflection
CREATE TEMPORARY FUNCTION STEP
  (arg STRUCT<
    -- Ray info
    rt ARRAY<STRUCT<
        i INT64, -- Point of pixel
        j INT64, 
        r STRUCT<
          o STRUCT<x FLOAT64, y FLOAT64, z FLOAT64>, -- Origin
          d STRUCT<x FLOAT64, y FLOAT64, z FLOAT64>, -- Direction
          af STRUCT<x FLOAT64, y FLOAT64, z FLOAT64> -- Accumulation of colors
        >,
        final STRUCT<x FLOAT64, y FLOAT64, z FLOAT64> -- Colors of infinite direction
    >>,
    world ARRAY<STRUCT< -- World settings (shphere)
      sc STRUCT<x FLOAT64, y FLOAT64, z FLOAT64>, -- Center of sphere
      sr FLOAT64, -- Radius of sphere
      att STRUCT<x FLOAT64, y FLOAT64, z FLOAT64>, -- Attribute (color)
      mat STRING -- Material (diffuse OR metal)
    >>,
    sample_num INT64 -- Sample number (for avoiding optimization)
  >)
  AS ((
    -- Calculates hit info
    WITH hitinfo AS (
      SELECT 
        i, j, r, s,
        IF(t IS NOT NULL,
          -- Calculates intersection as the ray hits
          STRUCT(
            COMB(1.0, r.o, t, r.d) AS p,
            COMB(1.0/s.sr, COMB(1.0, r.o, t, r.d), -1.0/s.sr, s.sc) AS n
          ),
          NULL
        ) AS hit
      FROM (
        SELECT
          i, j, r,
          (
            SELECT AS STRUCT
              -- Calculates the intersection of the line and the sphere
              s, t
            FROM (SELECT
                s, (-b - SQRT(b*b - a*c))/a AS t
                FROM (SELECT 
                  s,
                  DOT(r.d, r.d) AS a,
                  DOT(oc, r.d) AS b,
                  DOT(oc, oc) - s.sr*s.sr AS c
                  FROM
                    (SELECT 
                        s, COMB(1.0, r.o, -1.0, s.sc) AS oc
                     FROM UNNEST(arg.world) AS s)
                )
                WHERE b*b - a*c > 0
            )
            WHERE t > 1e-7
            ORDER BY t
            LIMIT 1
          ).*
        FROM
          UNNEST(arg.rt)
        WHERE
          final IS NULL
      )
    )
    SELECT 
      STRUCT(ARRAY(
        SELECT AS STRUCT
          i, j,
          -- Reflects if the ray hits
          IF(hit IS NULL, NULL,
          CASE s.mat 
            WHEN 'diffuse' THEN
              -- Lambert's Law (Not accurate)
              STRUCT(
                hit.p AS o,
                -- `arg.sample_num/arg.sample_num` is workaround for the issue that RAND() returns the same value (I don't know why.)
                COMB(1.0, hit.n, 1.0,
                    UNIT(STRUCT(2*RAND() - arg.sample_num/arg.sample_num AS x,
                                2*RAND() - arg.sample_num/arg.sample_num AS y,
                                2*RAND() - arg.sample_num/arg.sample_num AS z))) AS d,
                STRUCT(r.af.x * s.att.x AS x, r.af.y * s.att.y AS y, r.af.z * s.att.z AS z) AS af
              )
            WHEN 'metal' THEN
              -- perfect reflection
              STRUCT(
                hit.p AS o,
                COMB(1,UNIT(r.d),-2*DOT(UNIT(r.d), hit.n), hit.n) AS d,
                STRUCT(r.af.x * s.att.x AS x, r.af.y * s.att.y AS y, r.af.z * s.att.z AS z) AS af
              )
          END) AS r,
          -- Nothing hits. Returns a background color
          IF(hit IS NULL,
            (SELECT STRUCT(
              (1-t/2)*r.af.x AS x,
              (1-t*0.3)*r.af.y AS y,
              r.af.z AS z
            )
            FROM (SELECT (1+r.d.y/SQRT(DOT(r.d, r.d)))/2 AS t))
          , NULL) AS final
        FROM hitinfo
        UNION ALL
        -- If rays goes away, reflections are not cauculated.
        SELECT AS STRUCT i, j, r, final
        FROM UNNEST(arg.rt)
        WHERE final IS NOT NULL
      ) AS rt,
      arg.world AS world,
      arg.sample_num AS sample_num
    )
));
/*
-- You can export table directly into a file if you have a GCS bucket.
EXPORT DATA OPTIONS(
  uri='gs://bucket/folder/*.ppm',
  format='CSV',
  header=false,
  field_delimiter=' ')
*/
WITH pixels AS (
-- Generate (400, 400) pixels
SELECT
  i, j
FROM
  UNNEST(GENERATE_ARRAY(0, 399)) AS j,
  UNNEST(GENERATE_ARRAY(0, 399)) AS i
),
world AS (
  -- Definitions of spheres
  SELECT
    STRUCT(0.0 AS x, 0.0 AS y, -1.0 AS z) AS sc, 0.5 AS sr,
    STRUCT(0.8 AS x, 0.5 AS y, 0.2 AS z) AS att, "diffuse" AS mat
  UNION ALL SELECT
    STRUCT(1.0 AS x, 0.0 AS y, -1.0 AS z) AS sc, 0.5 AS sr,
    STRUCT(0.8 AS x, 0.7 AS y, 0.9 AS z) AS att, "metal" AS mat
  UNION ALL SELECT
    STRUCT(-1.0 AS x, 0.0 AS y, -1.0 AS z) AS sc, 0.5 AS sr,
    STRUCT(0.6 AS x, 0.5 AS y, 0.9 AS z) AS att, "metal" AS mat
  UNION ALL SELECT
    STRUCT(0.4 AS x, -0.4 AS y, -0.5 AS z) AS sc, 0.1 AS sr,
    STRUCT(0 AS x, 1 AS y, 0.5 AS z) AS att, "diffuse" AS mat
  UNION ALL SELECT
    STRUCT(-0.4 AS x, -0.4 AS y, -0.5 AS z) AS sc, 0.1 AS sr,
    STRUCT(1 AS x, 1 AS y, 1 AS z) AS att, "metal" AS mat
  UNION ALL SELECT
    STRUCT(0.0 AS x, -100.5 AS y, -1.0 AS z) AS sc, 100.0 AS sr,
    STRUCT(0.5 AS x, 0.5 AS y, 0.2 AS z) AS att, "diffuse" AS mat
),
result AS (
  WITH samples AS (
    SELECT 
      -- Calculates reflection for 8 times
      -- (You can use BigQuery Scripting instead, but it takes scan bytes.)
      STEP(STEP(STEP(STEP(STEP(STEP(STEP(STEP(
        STRUCT(
          ARRAY(SELECT AS STRUCT
            i, j,
            -- Generate rays from camera
            STRUCT(
              STRUCT(0.0 AS x, 0.0 AS y, 0.0 AS z) AS o,
              STRUCT(2.0*(i+2*RAND()-s/s)/400-1 AS x, 1.0-2.0*(j+2*RAND()-s/s)/400 AS y, -1.0 AS z) AS d,
              STRUCT(1.0 AS x, 1.0 AS y, 1.0 AS z) AS af
            ) AS r,
            CAST(NULL AS STRUCT<x FLOAT64, y FLOAT64, z FLOAT64>) AS final
          FROM pixels) AS rt,
          ARRAY(SELECT AS STRUCT * FROM world) AS world,
          s AS sample_num
        )
      )))))))).rt AS rt
    FROM
      -- Makes 50 samples per pixel
      UNNEST(GENERATE_ARRAY(1,50)) AS s
  )
  SELECT i, j, final
  FROM samples, UNNEST(rt)
)
-- Generate as PMX
SELECT
  "P3 400 400 255 #", -1 AS i, -1 AS j,
UNION ALL
(
  SELECT
    FORMAT("%d %d %d #",
      -- Gamma correction
      CLAMP255(SQRT(SUM(final.x)/COUNT(final))),
      CLAMP255(SQRT(SUM(final.y)/COUNT(final))),
      CLAMP255(SQRT(SUM(final.z)/COUNT(final)))), i, j
  FROM result
  GROUP BY i, j
)
ORDER BY j, i