Data Cookbook Kitchen

In my previous post, I redefined the dot product of two vectors. But there’s more fun to be had doing linear algebra with ClickHouse! Let’s define some linear algebra operations for pure educational value.

Turning things around: Outer product

If you multiply two vectors element by element, you get a matrix. This is known as the outer product. It is denoted by u ⊗ v and can also be written as uvT. Ths is easy to define:

CREATE OR REPLACE FUNCTION myOuterProduct AS (u, v) ->
    arrayMap((x) -> multiply(x, v), u);

Linear transformation: Multiplying a vector and a matrix

A linear transformation is obtained by multiplying a matrix with a (column) vector. This is very similar to the previous example, only using a different multiplication operator. Each component of the result is the dot product of the corresponding matrix row with the input vector.

CREATE OR REPLACE FUNCTION myMatrixProduct AS (m, v) ->
    arrayMap((x) -> arrayDotProduct(x, v), m);

Bilinear forms

Likewise, a bilinear form is a scalar valued function of two vectors. You can define it like so: B(u, v) = uTMv where M is a matrix. In code, it looks like this:

CREATE OR REPLACE FUNCTION myBilinearForm AS (u, m, v) ->
    arrayDotProduct(u, arrayMap((x) -> arrayDotProduct(x, v), m));

The inner arrayMap expression gets the result of Mv and this is then multiplied by u.

Vector cross product

For two vectors A and B in three-dimensional space, their cross product C = A × B can be defined using the Levi-Civita symbol εijk as follows: Ci = εijk AjBk

CREATE OR REPLACE FUNCTION myCrossProduct AS (v1, v2) ->
    arrayMap((m) -> arrayDotProduct(v1, arrayMap((x) -> arrayDotProduct(x, v2), m)),
        [[[0, 0, 0], [0, 0, 1], [0, -1, 0]], [[0, 0, -1], [0, 0, 0], [1, 0, 0]], [[0, 1, 0], [-1, 0, 0], [0, 0, 0]]]
    );

Here the Levi-Civita symbol is inlined as a 3 x 3 x 3 tensor.

Conclusion

This shows once more the flexibility of ClickHouse’s SQL dialect. With SQL UDFs and arrays, complex linear algebra transformations can be implemented as one-liners!

“This image is taken from Page 377 of Praktisches Kochbuch für die gewöhnliche und feinere Küche” by Medical Heritage Library, Inc. is licensed under CC BY-NC-SA 2.0 .