to_base64

Applies to VAST Cluster 5.5.

Functions › Binary, hash and UUID functions › Binary data and encodings. Writes the bytes of a BLOB as Base64 text, a VARCHAR. base64 is the same function. from_base64 reads the text back into bytes.

Syntax

to_base64(b)
base64(b)

Returns: VARCHAR.

Aliases: base64.

Set up the examples

The examples connect with the ADBC driver, as in VAST Query Engine (ADBC), and work in a schema called docs_sql_fn_binary_hash_uuid_to_base64. Set QE_ENDPOINT, AWS_ACCESS_KEY_ID, AWS_SECRET_ACCESS_KEY and VASTDB_BUCKET (SQL reference), and run the blocks in order in one Python session. Below each block is the output you should see.

This block connects, defines the run() and plan() helpers, and creates a table of six files: a name and the first bytes of each. Row 2 has empty bytes, and row 3 has no values:

import os
import re

import adbc_driver_manager
import adbc_driver_manager.dbapi as dbapi
import adbc_driver_vastdb

BUCKET = os.environ["VASTDB_BUCKET"]
SCHEMA = f'"{BUCKET}/docs_sql_fn_binary_hash_uuid_to_base64"'

conn = dbapi.connect(
    driver=adbc_driver_vastdb.get_driver_path(),
    db_kwargs={
        "vast.db.endpoint": os.environ["QE_ENDPOINT"],
        "vast.db.access_key": os.environ["AWS_ACCESS_KEY_ID"],
        "vast.db.secret_key": os.environ["AWS_SECRET_ACCESS_KEY"],
    },
    autocommit=True,
)

def server_message(e):
    """The server's message in a driver error, without the request ID or the echoed statement."""
    text = re.sub(r"\[req=0x[0-9a-f]+\]: ", "", str(e))
    found = (re.search(r'message: "(.*?)", request_id', text, re.S)
             or re.search(r"description: (.*)\)", text, re.S))
    message = found.group(1) if found else text.strip().splitlines()[-1]
    message = re.sub(r"^Plan query: '.*?' failed with: ", "", message, flags=re.S)
    message = re.split(r"\\n|\n", message)[0].replace('\\"', '"')
    return message.replace(BUCKET, "<bucket>")

def run(sql):
    """Run one statement. Print the rows it returns, or the error."""
    try:
        with conn.cursor() as cur:
            cur.execute(sql)
            rows = cur.fetchall() if cur.description else []
    except (adbc_driver_manager.Error, OSError) as e:
        print(f"{type(e).__name__}: {server_message(e)}")
    else:
        print(rows)

def plan(sql):
    """Print the plan without its ~N Rows lines: the table's row count, which can still be 0 just after an insert."""
    with conn.cursor() as cur:
        cur.execute("EXPLAIN " + sql)
        steps = cur.fetchall()[0][1]
    print("\n".join(line for line in steps.splitlines() if "Rows" not in line))

TABLE = f"{SCHEMA}.files"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, name VARCHAR, data BLOB)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, 'report.pdf', '\\x25\\x50\\x44\\x46'::BLOB), "
    "(2, 'empty.bin', ''::BLOB), "
    "(3, NULL, NULL), "
    "(4, 'h\u00e9llo.txt', encode('h\u00e9llo')), "
    "(5, 'header.bin', '\\x00\\x01'::BLOB), "
    "(6, 'a.txt', '\\x61'::BLOB)")

[]
[]
[(6,)]

What it does

to_base64(b) writes every three bytes of b as four characters from the standard Base64 alphabet: A-Z, a-z, 0-9, + and /. When the length isn't a multiple of three, the output is padded with = to a multiple of four:

• to_base64('a'::BLOB) is 'YQ==', to_base64('ab'::BLOB) is 'YWI=' and to_base64('abc'::BLOB) is 'YWJj'.
• to_base64('\xFB\xFF'::BLOB) is '+/8=': the + and / characters, not the URL-safe - and _.
• The output is one line, however long: 60,000 bytes give 80,000 characters, with no line breaks.

A string constant is cast to BLOB, so to_base64('hello') is 'aGVsbG8='. A VARCHAR column isn't: to encode text, take its bytes with encode first, as in to_base64(encode(name)).

NULLs and edge cases

The same cases, as the cluster returns them. They use the table and helpers from Set up the examples:

run("SELECT to_base64(NULL), to_base64(''::BLOB), to_base64('a'::BLOB), to_base64('ab'::BLOB), to_base64('abc'::BLOB)")
run("SELECT to_base64('\\xFB\\xFF'::BLOB), to_base64('hello'), base64('hello'::BLOB)")
run("SELECT length(to_base64(repeat('\\xAA'::BLOB, 60000))), strpos(to_base64(repeat('\\xAA'::BLOB, 60000)), chr(10))")
run(f"SELECT to_base64(name) FROM {TABLE} LIMIT 1")

[(None, '', 'YQ==', 'YWI=', 'YWJj')]
[('+/8=', 'aGVsbG8=', 'aGVsbG8=')]
[(80000, 0)]
InternalError: Binder Error: No function matches the given name and argument types 'to_base64(VARCHAR)'. You might need to add explicit type casts.

Examples

The bytes of each file as Base64, with base64 giving the same text:

run(f"SELECT id, to_base64(data), base64(data) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 'JVBERg==', 'JVBERg=='), (2, '', ''), (3, None, None), (4, 'aMOpbGxv', 'aMOpbGxv'), (5, 'AAE=', 'AAE='), (6, 'YQ==', 'YQ==')]

The UTF-8 bytes of each name as Base64. encode turns the VARCHAR into a BLOB first:

run(f"SELECT id, name, to_base64(encode(name)) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 'report.pdf', 'cmVwb3J0LnBkZg=='), (2, 'empty.bin', 'ZW1wdHkuYmlu'), (3, None, None), (4, 'héllo.txt', 'aMOpbGxvLnR4dA=='), (5, 'header.bin', 'aGVhZGVyLmJpbg=='), (6, 'a.txt', 'YS50eHQ=')]

How it runs in VAST

The Query Engine runs on the cluster's CNodes, and a query on a table starts with a scan in VAST DataBase (VAST_SCAN). VAST DataBase can evaluate a comparison of a column with a constant, but not a function of a column, so to_base64() of a column is always computed by the Query Engine:

• In the SELECT list, the scan reads the column and the Query Engine encodes it for each row it returns.
• In WHERE, the scan sends the column for every row, and a FILTER step encodes it and keeps the rows that match.

The plan of to_base64(data) = 'JVBERg==' shows the FILTER step above the scan:

plan(f"SELECT id FROM {TABLE} WHERE to_base64(data) = 'JVBERg=='")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│ (to_base64(data) = 'JVBERg│
│            ==')           │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│            data           │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Decode the constant instead of encoding the column. data = from_base64('JVBERg==') finds the same rows as to_base64(data) = 'JVBERg==', and is pushed down to VAST DataBase: from_base64 of a constant is computed once. The plan has no FILTER step:

run(f"SELECT id FROM {TABLE} WHERE data = from_base64('JVBERg==') ORDER BY id LIMIT 10")
plan(f"SELECT id FROM {TABLE} WHERE data = from_base64('JVBERg==')")

[(1,)]
┌───────────────────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│      Projections: id      │
│                           │
└───────────────────────────┘

• Encode only the rows you return. In the SELECT list, to_base64 runs on the rows the scan returns, so a WHERE condition that's pushed down, such as id = 1, cuts the work.

Limitations and errors

• BLOB only. A VARCHAR column fails with No function matches the given name and argument types 'to_base64(VARCHAR)'; use to_base64(encode(s)). Numbers, dates and UUID values fail the same way.
• Standard alphabet, with padding. There's no URL-safe variant: to_base64url and base64_encode don't exist, and fail with Scalar Function with name ... does not exist!.

run("SELECT to_base64url('a'::BLOB)")

InternalError: Catalog Error: Scalar Function with name to_base64url does not exist!

Similar functions

run("SELECT to_base64(encode('h\u00e9')), hex(encode('h\u00e9')), CAST(encode('h\u00e9') AS VARCHAR), "
    "length(to_base64(encode('h\u00e9llo'))), length(hex(encode('h\u00e9llo')))")
run("SELECT from_base64(to_base64('\\x00\\xFF'::BLOB)) = '\\x00\\xFF'::BLOB")

[('aMOp', '68C3A9', 'h\\xC3\\xA9', 8, 12)]
[(True,)]

Clean up:

run(f"DROP TABLE IF EXISTS {TABLE}")
run(f"DROP SCHEMA IF EXISTS {SCHEMA}")
conn.close()

[]
[]

See also

• Binary, hash and UUID functions: all the binary functions.
• from_base64, encode and BLOB values.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.