from_base64

Applies to VAST Cluster 5.5.

Functions › Binary, hash and UUID functions › Binary data and encodings. Reads Base64 text and returns the bytes it encodes, as a BLOB. It's the reverse of to_base64. Text that isn't padded standard Base64 stops the query with an error.

Syntax

from_base64(s)

Returns: BLOB.

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_from_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 tokens: Base64 text and a note. Row 2's token is empty and row 3 has no values. The notes aren't Base64:

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_from_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}.tokens"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, b64 VARCHAR, note VARCHAR)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, 'aGVsbG8=', 'hello'), "
    "(2, '', 'empty'), "
    "(3, NULL, NULL), "
    "(4, 'AP8=', 'two bytes'), "
    "(5, 'w6k=', 'UTF-8 text'), "
    "(6, 'YQ==', 'a')")

[]
[]
[(6,)]

What it does

from_base64(s) reads every four characters of s as three bytes, using the standard Base64 alphabet (A-Z, a-z, 0-9, +, /) and = for padding:

• from_base64('aGVsbG8=') is b'hello', and from_base64('aGVsbA==') is b'hell'.
• from_base64('+/8=') is b'\xfb\xff'.
• from_base64('') is empty bytes.

The text must be exact: its length a multiple of four, padding included, with no spaces or line breaks, and only characters from the standard alphabet. Anything else stops the query with Could not decode string "..." as base64: followed by the reason. One invalid value fails the whole query, not only its row.

The result is bytes. If they're UTF-8 text, read them with decode: decode(from_base64('w6k=')) is 'é'.

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 from_base64(NULL), from_base64(''), from_base64('aGVsbA=='), from_base64('+/8=')")
run("SELECT from_base64('aGVsbG8')")
run("SELECT from_base64('aGVs bG8=')")
run("SELECT from_base64('YQ===')")
run("SELECT from_base64('-_8=')")
run("SELECT from_base64('====')")
run("SELECT from_base64(123)")

[(None, b'', b'hell', b'\xfb\xff')]
OSError: Could not decode string "aGVsbG8" as base64: length must be a multiple of 4
OSError: Could not decode string "aGVs bG8=" as base64: length must be a multiple of 4
OSError: Could not decode string "YQ===" as base64: length must be a multiple of 4
OSError: Could not decode string "-_8=" as base64: invalid byte value '45' at position 0
OSError: Could not decode string "====" as base64: invalid byte value '61' at position 0
InternalError: Binder Error: No function matches the given name and argument types 'from_base64(INTEGER_LITERAL)'. You might need to add explicit type casts.

Examples

The bytes each token encodes. Row 5's bytes are the UTF-8 encoding of é, which decode turns back into text:

run(f"SELECT id, b64, from_base64(b64) FROM {TABLE} ORDER BY id LIMIT 10")
run(f"SELECT id, decode(from_base64(b64)) FROM {TABLE} WHERE id = 5")

[(1, 'aGVsbG8=', b'hello'), (2, '', b''), (3, None, None), (4, 'AP8=', b'\x00\xff'), (5, 'w6k=', b'\xc3\xa9'), (6, 'YQ==', b'a')]
[(5, 'é')]

Text that isn't Base64 fails the query. 'hello' has five characters, not a multiple of four:

run(f"SELECT id, from_base64(note) FROM {TABLE} WHERE id = 1")

OSError: Could not decode string "hello" as base64: length must be a multiple of 4

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). Where from_base64 runs depends on its argument:

• from_base64 of a column is computed in the Query Engine for each row the scan returns. In WHERE, the scan sends the column for every row and a FILTER step tests it.
• from_base64 of a constant is computed once, before the scan. A comparison of a BLOB column with it, such as data = from_base64('JVBERg=='), is pushed down to VAST DataBase.

The plan of a condition on from_base64(b64) shows the FILTER step above the scan:

plan(f"SELECT id FROM {TABLE} WHERE from_base64(b64) = '\\x61'::BLOB")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│  (from_base64(b64) = 'a': │
│           :BLOB)          │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│            b64            │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Encode the constant instead of decoding the column. b64 = to_base64('\x61'::BLOB) compares the stored text with a constant, which VAST DataBase can evaluate, and finds the same rows as from_base64(b64) = '\x61'::BLOB when the stored text is canonical Base64:

run(f"SELECT id FROM {TABLE} WHERE b64 = to_base64('\\x61'::BLOB) ORDER BY id LIMIT 10")
plan(f"SELECT id FROM {TABLE} WHERE b64 = to_base64('\\x61'::BLOB)")

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

• Store bytes as BLOB. If you query the decoded bytes often, decode them once when you load the data and keep them in a BLOB column. A comparison of that column with a constant is pushed down.

Limitations and errors

• Exact, padded, standard Base64 only. Text without padding, with spaces or line breaks, or in the URL-safe alphabet (- and _) fails. Clean it up in your client before loading it, or before the query.
• No URL-safe variant. from_base64url doesn't exist, and fails with Scalar Function with name from_base64url does not exist!.
• Invalid text fails the query. There's no form that returns NULL instead. Leave such rows out with WHERE.
• VARCHAR only. A BLOB, a number, a date or a boolean fails with No function matches the given name and argument types.

run("SELECT from_base64url('YQ')")

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

Similar functions

run("SELECT from_base64('AP8='), from_hex('00FF'), to_base64('\\x00\\xFF'::BLOB), "
    "from_base64(to_base64('\\x00\\xFF'::BLOB)) = '\\x00\\xFF'::BLOB")

[(b'\x00\xff', b'\x00\xff', 'AP8=', 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.
• to_base64, decode and BLOB values.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.