from_hex

Applies to VAST Cluster 5.5.

Functions › Binary, hash and UUID functions › Binary data and encodings. Reads a string of hexadecimal digits and returns the bytes it describes, as a BLOB. unhex is the same function. It's the reverse of hex.

Syntax

from_hex(s)
unhex(s)

Returns: BLOB.

Aliases: unhex.

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_hex. 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 five codes: each has a hex string and a BLOB. The last row 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_from_hex"'

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}.codes"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, hx VARCHAR, data BLOB)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, '68656C6C6F', '\\x25\\x50\\x44\\x46'::BLOB), "
    "(2, '', ''::BLOB), "
    "(3, '00ff', '\\x00\\xFF'::BLOB), "
    "(4, 'ABC', '\\x61'::BLOB), "
    "(5, NULL, NULL)")

[]
[]
[(5,)]

What it does

from_hex reads the string two digits at a time and makes one byte of each pair: from_hex('68656C6C6F') gives the five bytes of hello. Upper-case and lower-case digits give the same bytes.

An odd number of digits: the first digit makes a byte on its own, and the rest are read in pairs. from_hex('a') gives the byte 0x0A, and from_hex('abc') gives 0x0A 0xBC. Pad the string with a leading 0 if you meant something else.

Bytes, not text. The result is a BLOB, which Python shows as bytes (b'hello'). To read bytes that hold UTF-8 text as a VARCHAR, apply decode: decode(from_hex('C3A9')) gives 'é'.

NULLs and edge cases

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

run("SELECT from_hex(NULL), from_hex(''), from_hex('68656c6c6f'), from_hex('a'), from_hex('abc')")
run("SELECT from_hex(hex('hello')) = 'hello'::BLOB, decode(from_hex('C3A9')) = '\u00e9'")
run("SELECT from_hex('0x41')")
run("SELECT from_hex('zz')")
run("SELECT from_hex(123)")

[(None, b'', b'hello', b'\n', b'\n\xbc')]
[(True, True)]
OSError: Invalid input for hex digit: x
OSError: Invalid input for hex digit: z
InternalError: Binder Error: No function matches the given name and argument types 'from_hex(INTEGER_LITERAL)'. You might need to add explicit type casts.

Examples

Each code's hex string as bytes. unhex gives the same results:

run(f"SELECT id, hx, from_hex(hx), unhex(hx) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, '68656C6C6F', b'hello', b'hello'), (2, '', b'', b''), (3, '00ff', b'\x00\xff', b'\x00\xff'), (4, 'ABC', b'\n\xbc', b'\n\xbc'), (5, None, None, None)]

Find a BLOB by its hex form. The constant is turned into bytes once, and compared with the column:

run(f"SELECT id FROM {TABLE} WHERE data = from_hex('25504446') ORDER BY id LIMIT 10")

[(1,)]

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). from_hex() is computed by the Query Engine:

• On a column, in the SELECT list the Query Engine computes from_hex() for each row the scan returns. In WHERE, the scan sends the column for every row, and a FILTER step computes from_hex() and keeps the rows that match.
• On a constant, the Query Engine computes it once, before the scan. A comparison of a column with that constant, such as data = from_hex('25504446'), is then pushed down to VAST DataBase like any comparison with a constant.

The plan of a filter on from_hex() of a column shows the FILTER step above the scan:

plan(f"SELECT id FROM {TABLE} WHERE from_hex(hx) IS NOT NULL")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│ (from_hex(hx) IS NOT NULL)│
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│             hx            │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Apply from_hex to the constant, not to the column. If you store bytes, store them in a BLOB column and compare it with from_hex('...'). The plan of the second example has no FILTER step: VAST DataBase returns only the matching rows.

plan(f"SELECT id FROM {TABLE} WHERE data = from_hex('25504446')")

┌───────────────────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│      Projections: id      │
│                           │
└───────────────────────────┘

Limitations and errors

• Only hex digits. A 0x prefix, a space or any other character fails the query with Invalid input for hex digit: and the character. One bad value in a column fails the whole query, not just its row: clean the strings first, for example with replace().
• VARCHAR only. A number, a BLOB, a date, a boolean or a list fails with No function matches the given name and argument types; the error lists from_hex(VARCHAR) -> BLOB as the only candidate.

run("SELECT from_hex('41 42')")
run(f"SELECT id, from_hex(replace(hx, ' ', '')) FROM {TABLE} WHERE id = 1 LIMIT 10")

OSError: Invalid input for hex digit:  
[(1, b'hello')]

Similar functions

run("SELECT from_hex('68C3A9'), hex(encode('h\u00e9')), from_base64('aMOp'), encode('h\u00e9'), decode(from_hex('68C3A9'))")

[(b'h\xc3\xa9', '68C3A9', b'h\xc3\xa9', b'h\xc3\xa9', 'hé')]

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 functions in this family.
• Data types in SQL: BLOB columns.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.