encode

Applies to VAST Cluster 5.5.

Functions › Binary, hash and UUID functions › Binary data and encodings. Returns the UTF-8 bytes of a string as a BLOB. It's the way to turn any text, including characters outside ASCII, into bytes; decode turns the bytes back into text.

Syntax

encode(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_encode. 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 messages: their text and the bytes stored for them. Row 2 is empty, row 3 has no values, rows 4 and 5 have characters outside ASCII, and row 5's stored bytes don't match its text:

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_encode"'

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}.messages"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, s VARCHAR, data BLOB)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, 'hello', encode('hello')), "
    "(2, '', ''::BLOB), "
    "(3, NULL, NULL), "
    "(4, 'h\u00e9llo w\u00f6rld \u20ac', encode('h\u00e9llo w\u00f6rld \u20ac')), "
    "(5, 'Stra\u00dfe \U0001F1EB\U0001F1F7', '\\xFF\\xFE'::BLOB), "
    "(6, 'a', encode('a'))")

[]
[]
[(6,)]

What it does

encode(s) returns the bytes of s in UTF-8, the encoding the VAST Query Engine uses for VARCHAR. An ASCII character is one byte; other characters take two to four bytes:

• encode('hello') is b'hello', five bytes.
• encode('héllo') is b'h\xc3\xa9llo', six bytes: é is \xC3\xA9.
• A letter followed by a combining accent keeps both: encode('e\u0301') is b'e\xcc\x81'.
• A NUL character becomes a \x00 byte: encode('a' || chr(0) || 'b') is b'a\x00b'.

The client receives the result as Python bytes. There's no choice of encoding: encode takes one argument.

NULLs and edge cases

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

run("SELECT encode(NULL), encode(''), encode('h\u00e9llo'), octet_length(encode('h\u00e9llo')), "
    "encode('e\u0301'), encode('a' || chr(0) || 'b')")
run("SELECT encode('hello', 'utf-8')")
run("SELECT encode(123)")

[(None, b'', b'h\xc3\xa9llo', 6, b'e\xcc\x81', b'a\x00b')]
InternalError: Binder Error: No function matches the given name and argument types 'encode(STRING_LITERAL, STRING_LITERAL)'. You might need to add explicit type casts.
InternalError: Binder Error: No function matches the given name and argument types 'encode(INTEGER_LITERAL)'. You might need to add explicit type casts.

Examples

The bytes of each message, and how many there are. Row 5's flag emoji is two code points of four bytes each:

run(f"SELECT id, encode(s), octet_length(encode(s)), length(s) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, b'hello', 5, 5), (2, b'', 0, 0), (3, None, None, None), (4, b'h\xc3\xa9llo w\xc3\xb6rld \xe2\x82\xac', 17, 13), (5, b'Stra\xc3\x9fe \xf0\x9f\x87\xab\xf0\x9f\x87\xb7', 16, 9), (6, b'a', 1, 1)]

Find the rows whose stored bytes are the UTF-8 encoding of a given text. encode of a constant is computed once, so the comparison is on the stored column:

run(f"SELECT id FROM {TABLE} WHERE data = encode('h\u00e9llo w\u00f6rld \u20ac') ORDER BY id LIMIT 10")

[(4,)]

Find the rows whose stored bytes don't match their text. Row 3 has NULLs, so the comparison is NULL and the row isn't returned:

run(f"SELECT id FROM {TABLE} WHERE data <> encode(s) ORDER BY id LIMIT 10")

[(5,)]

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 encode runs depends on its argument:

• encode of a constant is computed once, before the scan. data = encode('...') is a comparison of a column with a constant, which VAST DataBase evaluates: rows that don't match never leave storage.
• encode 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.

The plan of the first WHERE example has no FILTER step; the plan of the second has one:

plan(f"SELECT id FROM {TABLE} WHERE data = encode('h\u00e9llo w\u00f6rld \u20ac')")
plan(f"SELECT id FROM {TABLE} WHERE data <> encode(s)")

┌───────────────────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│      Projections: id      │
│                           │
└───────────────────────────┘
┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #2            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│    (data != encode(s))    │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│            data           │
│             s             │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Put encode on the constant side. To find bytes that encode a text, write data = encode('text'), not decode(data) = 'text': the first is pushed down to VAST DataBase, the second is a FILTER in the Query Engine.
• Store the bytes if you need them often. If you hash or compare the bytes of a text column in many queries, write encode(s) to a BLOB column when you load the data.

Limitations and errors

• VARCHAR only. A number, a date or a boolean fails with No function matches the given name and argument types; cast it to VARCHAR first. A BLOB fails too: it's already bytes.
• UTF-8 only. encode has no encoding argument; encode(s, 'latin1') fails. Convert to other encodings in your client.
• CAST(s AS BLOB) isn't the same. A cast takes ASCII text and \x escapes, and fails on other characters with Invalid byte encountered in STRING -> BLOB conversion. encode takes any text.

run(f"SELECT id, CAST(s AS BLOB) FROM {TABLE} ORDER BY id LIMIT 10")

OSError: Invalid byte encountered in STRING -> BLOB conversion of string "héllo wörld €". All non-ascii characters must be escaped with hex codes (e.g. \xAA)

Similar functions

run("SELECT encode(s), strlen(s), octet_length(encode(s)), length(s), length(hex(s)), length(to_base64(encode(s))), "
    "decode(encode(s)) = s FROM (SELECT 'h\u00e9llo' AS s)")

[(b'h\xc3\xa9llo', 6, 6, 5, 12, 8, 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.
• BLOB values: writing BLOB constants and casts.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.