PNG

Description

Renders the result of a query as a PNG image. This is useful as a built-in visualization tool.

The size of the output image is fixed by the settings output_format_image_width and output_format_image_height (both default to 1024). Pixels that are not covered by the result are filled with black (in RGB and grayscale modes) or with transparent black (in RGBA mode).

The color mode is determined automatically from the column names and types of the result:

Column names are matched case-insensitively. If the color mode cannot be unambiguously determined (e.g. unknown column names, mixed v with r/g/b/a, or one of r/g/b missing), the query throws an exception.

For pixel channels, integer values are clamped to [0, 255] and floating-point values are clamped to [0, 1] and then scaled to [0, 255].

The position of each record in the image is determined by one of two modes:

• Implicit (the default — when neither x nor y is present). Each record corresponds to a single pixel; pixels are filled in scanline order: left to right, top to bottom.
• Explicit (when x and y columns are present, both of integer types). The x and y columns give the pixel coordinates. Records with coordinates outside the image are silently ignored. In case of multiple records with the same coordinates, the last one wins (painter’s algorithm).

Example usage

Implicit coordinates (row-per-pixel), RGB

SELECT
    toUInt8(x * 25) AS r,
    toUInt8(y * 25) AS g,
    toUInt8((x + y) * 12) AS b
FROM
(
    SELECT number % 10 AS x, intDiv(number, 10) AS y FROM numbers(100)
)
INTO OUTFILE 'gradient.png'
FORMAT PNG
SETTINGS output_format_image_width = 10, output_format_image_height = 10;

Explicit coordinates, grayscale

SELECT
    toInt32(x) AS x,
    toInt32(y) AS y,
    toUInt8(intensity) AS v
FROM points
INTO OUTFILE 'points.png'
FORMAT PNG
SETTINGS output_format_image_width = 512, output_format_image_height = 512;

Animation

If the result has a t column of an integer type, the format produces an animated PNG (APNG) instead of a still image. Records are grouped into frames by the value of t, which is the relative time offset of the frame. Every frame is an independent image: the canvas is empty at the start of each frame, and in the implicit coordinate mode the cursor restarts from the top-left corner. The t column can be combined with either coordinate mode.

The unit of t is given by output_format_image_time_multiplier_seconds and output_format_image_time_divisor_seconds: one unit of t is output_format_image_time_multiplier_seconds / output_format_image_time_divisor_seconds seconds. With the default values (1 and 60) one unit of t is 1/60 of a second.

A frame is displayed until the next frame begins, so its duration is the difference between two consecutive values of t. The last frame is displayed for as long as the frame before it. The animation loops forever.

SELECT
    number % 60 AS t,
    toInt32(intDiv(number, 60) % 64) AS x,
    toInt32((number * 7) % 64) AS y,
    toUInt8(255) AS v
FROM numbers(60 * 64)
INTO OUTFILE 'animation.png'
FORMAT PNG
SETTINGS output_format_image_width = 64, output_format_image_height = 64;

Streaming the frames

By default all frames are collected in memory and written out at the end of the query, which keeps one image buffer per distinct value of t and lets t arrive in any order.

The setting output_format_image_streaming_animation writes each frame out as soon as the next value of t is seen. Only one image buffer is kept in memory, and frames reach the output while the query is still running, so a viewer can display them as they are produced. In exchange:

• t must be non-decreasing; the query throws an exception otherwise. Add ORDER BY t if needed.
• The number of frames is not known when the header has to be written, so the acTL chunk declares an upper bound instead of the exact count. Browsers play such a file, but decoders that trust the declared count (for example, Pillow and some command-line APNG tools) report an error after the last real frame. An animation of a single frame is the exception: the whole result has been read by the time that frame is written, so the count is declared exactly and the output conforms to the specification.

Because an inline terminal image protocol carries the whole datastream as a single payload, the frames cannot reach the terminal early and this setting only affects how much memory is used there. The exact frame count is patched into the buffered payload before it is sent, so the caveat about the upper bound does not apply.

An animation is displayed only in the iterm terminal mode. The sixel protocol cannot represent an animation at all, and the Kitty graphics protocol animates only through a separate flow of per-frame commands, not through an animated datastream, so it would display just the first frame; both modes reject a result with a t column.

Displaying images in the terminal

By default, the PNG format writes the raw image bytes. The setting output_format_image_terminal_mode makes the format render the image directly to the terminal using an inline image protocol instead:

SELECT toUInt8(x * 25) AS r, toUInt8(y * 25) AS g, toUInt8((x + y) * 12) AS b
FROM (SELECT number % 10 AS x, intDiv(number, 10) AS y FROM numbers(100))
FORMAT PNG
SETTINGS output_format_image_width = 10, output_format_image_height = 10, output_format_image_terminal_mode = 'auto';

Format settings