Skip to content

Instantly share code, notes, and snippets.

@45deg
Last active April 29, 2021 02:47
Show Gist options
  • Save 45deg/6aa5d019454578c50731abd69445f671 to your computer and use it in GitHub Desktop.
Save 45deg/6aa5d019454578c50731abd69445f671 to your computer and use it in GitHub Desktop.
Ray Tracing on BigQuery (for free of charge)
-- 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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment