formatReadableDecimalSize

Applies to VAST Cluster 5.5.

Functions › Numeric and math functions › Number formatting. Writes a number of bytes for people to read, in decimal units of 1,000: kB, MB, GB, TB and PB. For binary units of 1,024, use format_bytes.

Syntax

formatReadableDecimalSize(n)

Returns: VARCHAR.

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_numeric_formatreadabledecimalsize. 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 with their sizes in bytes. 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_numeric_formatreadabledecimalsize"'

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, size_bytes BIGINT)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, 'notes.txt', 1536), "
    "(2, 'photo.jpg', 5000000), "
    "(3, 'backup.tar', 3221225472), "
    "(4, 'empty.log', 0), "
    "(5, 'archive.parquet', 1125899906842624), "
    "(6, NULL, NULL)")

[]
[]
[(6,)]

What it does

formatReadableDecimalSize(n) divides n by 1,000 until it's below 1,000, or until it reaches PB, and writes the result with one decimal place and the unit. The kilobyte unit is written kB, with a lowercase k:

Two details:

• Below 1,000 it writes whole bytes: '0 bytes', '1 byte', '999 bytes'.
• The decimal is cut, not rounded: formatReadableDecimalSize(1075) is '1.0 kB', not '1.1 kB', and formatReadableDecimalSize(3221225472) is '3.2 GB'.

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 formatReadableDecimalSize(NULL), formatReadableDecimalSize(0), formatReadableDecimalSize(1), formatReadableDecimalSize(2)")
run("SELECT formatReadableDecimalSize(999), formatReadableDecimalSize(1000), formatReadableDecimalSize(1023), formatReadableDecimalSize(1024)")
run("SELECT formatReadableDecimalSize(1075), formatReadableDecimalSize(1500), formatReadableDecimalSize(1000000000000000000), formatReadableDecimalSize(9223372036854775807)")
run("SELECT formatReadableDecimalSize(1536::INTEGER), formatReadableDecimalSize(255::UTINYINT)")
run("SELECT formatReadableDecimalSize(1536.0::DOUBLE)")
run("SELECT formatReadableDecimalSize(18446744073709551615::UBIGINT)")
run(f"SELECT formatReadableDecimalSize('abc') FROM {TABLE} LIMIT 1")

[(None, '0 bytes', '1 byte', '2 bytes')]
[('999 bytes', '1.0 kB', '1.0 kB', '1.0 kB')]
[('1.0 kB', '1.5 kB', '1000.0 PB', '9223.3 PB')]
[('1.5 kB', '255 bytes')]
InternalError: Binder Error: No function matches the given name and argument types 'formatReadableDecimalSize(DOUBLE)'. You might need to add explicit type casts.
InternalError: Binder Error: No function matches the given name and argument types 'formatReadableDecimalSize(UBIGINT)'. You might need to add explicit type casts.
OSError: Could not convert string 'abc' to INT64

Examples

The size of each file in decimal and in binary units:

run(f"SELECT id, name, formatReadableDecimalSize(size_bytes), format_bytes(size_bytes) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 'notes.txt', '1.5 kB', '1.5 KiB'), (2, 'photo.jpg', '5.0 MB', '4.7 MiB'), (3, 'backup.tar', '3.2 GB', '3.0 GiB'), (4, 'empty.log', '0 bytes', '0 bytes'), (5, 'archive.parquet', '1.1 PB', '1.0 PiB'), (6, None, None, None)]

The files of 1 GB or more. The condition is on the raw byte count, and formatReadableDecimalSize() only formats the rows returned:

run(f"SELECT id, name, formatReadableDecimalSize(size_bytes) FROM {TABLE} WHERE size_bytes >= 1000000000 ORDER BY id LIMIT 10")

[(3, 'backup.tar', '3.2 GB'), (5, 'archive.parquet', '1.1 PB')]

The total size. sum() of a BIGINT column returns a DECIMAL, which formatReadableDecimalSize() doesn't take, so cast the sum to BIGINT:

run(f"SELECT formatReadableDecimalSize(CAST(sum(size_bytes) AS BIGINT)) FROM {TABLE}")

[('1.1 PB',)]

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

• In the SELECT list, the scan reads the column and the Query Engine computes formatReadableDecimalSize() for each row it returns.
• In WHERE, the scan sends the column for every row, and a FILTER step in the Query Engine computes formatReadableDecimalSize() and keeps the rows that match. No rows are skipped in storage.

The plan of a WHERE condition on the function shows the FILTER step above the scan:

plan(f"SELECT id FROM {TABLE} WHERE formatReadableDecimalSize(size_bytes) IS NOT NULL")

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

Make it faster

• Filter on the byte count, not on the text. size_bytes >= 1000000000 is a comparison of the raw column with a constant, which is pushed down to VAST DataBase, so rows that don't match never leave storage. Its plan has no FILTER step:

plan(f"SELECT id FROM {TABLE} WHERE size_bytes >= 1000000000")

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

• Format last. Call formatReadableDecimalSize() in the SELECT list of the rows you return, and sort and compare on the number.

Limitations and errors

• Integers up to BIGINT. A DECIMAL, DOUBLE or FLOAT fails with No function matches the given name and argument types, and so do UBIGINT and HUGEINT. Cast the value to BIGINT first: formatReadableDecimalSize(CAST(x AS BIGINT)). This includes the result of sum() on an integer column.
• Decimal units only. For units of 1,024 (KiB, MiB, GiB), use format_bytes.
• One decimal place, cut. See What it does.

run("SELECT formatReadableDecimalSize(1.5)")
run("SELECT formatReadableDecimalSize(CAST(1536.0 AS BIGINT))")

InternalError: Binder Error: No function matches the given name and argument types 'formatReadableDecimalSize(DECIMAL(2,1))'. You might need to add explicit type casts.
[('1.5 kB',)]

Similar functions

run("SELECT formatReadableDecimalSize(1536), format_bytes(1536), formatReadableSize(1536), pg_size_pretty(1536)")
run("SELECT formatReadableDecimalSize(1500000), format_bytes(1500000), formatReadableSize(1500000)")

[('1.5 kB', '1.5 KiB', '1.5 KiB', '1.5 KiB')]
[('1.5 MB', '1.4 MiB', '1.4 MiB')]

Clean up:

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

[]
[]

See also

• Numeric and math functions: all the numeric functions.
• format_bytes and pg_size_pretty.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.