parse_dirpath

Applies to VAST Cluster 5.5.

Functions › Text basics › Paths. Returns the directory part of a file path: everything before its last separator. It has no aliases.

Syntax

parse_dirpath(path)
parse_dirpath(path, separator)

Returns: VARCHAR.

Aliases: none.

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_parse_dirpath. 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 file paths, with the directory part stored next to each path. One path is a Windows path and one ends with a separator; 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_parse_dirpath"'

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, path VARCHAR, folder VARCHAR)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, '/data/logs/app.log', '/data/logs'), "
    "(2, '/home/ada/projects/notes.tar.gz', '/home/ada/projects'), "
    "(3, 'C:\\Users\\jose\\poems.docx', 'C:\\Users\\jose'), "
    "(4, 'exports/2026/', 'exports/2026'), "
    "(5, NULL, NULL)")

[]
[]
[(5,)]

What it does

parse_dirpath cuts the path at its last separator and returns what comes before it, with the separators as they were written. A path with no separator is a file name alone and gives an empty string:

The part after the last separator is what parse_filename returns, so for 'a/b/' that's ''. To get only the top-level directory, '/' for '/data/logs/app.log', use parse_dirname.

The separator argument. With 'backslash', only \ separates components: parse_dirpath('a\b\c', 'backslash') returns 'a\b'. With 'forward_slash', or 'system' (which means / on the cluster), only / does: parse_dirpath('a/b\c', 'forward_slash') returns 'a'. The default, 'both_slash' and a value that isn't a mode name ('x') cut at the last / or \: parse_dirpath('a/b\c') returns 'a/b'. A NULL separator doesn't make the result NULL: parse_dirpath('a/b', NULL) returns 'a'.

In Python code, write each \ of a path as \\ inside the string, so that the SQL receives one backslash. Python prints each backslash in the results doubled: 'C:\\dir' is the string C:\dir.

NULLs and edge cases

The same cases, as the cluster returns them. The third line runs the other separator modes on a path with both separators. They use the table and helpers from Set up the examples:

run("SELECT parse_dirpath(NULL), parse_dirpath(''), parse_dirpath('c.txt'), parse_dirpath('/c.txt'), parse_dirpath('a/b/')")
run("SELECT parse_dirpath('a\\b\\c', 'backslash'), parse_dirpath('a/b', NULL)")
run("SELECT parse_dirpath('a/b\\c'), parse_dirpath('a/b\\c', 'forward_slash'), parse_dirpath('a/b\\c', 'both_slash'), "
    "parse_dirpath('a/b\\c', 'system'), parse_dirpath('a/b\\c', 'x')")
run("SELECT parse_dirpath(123)")

[(None, '', '', '', 'a/b')]
[('a\\b', 'a')]
[('a/b', 'a', 'a/b', 'a', 'a/b')]
InternalError: Binder Error: No function matches the given name and argument types 'parse_dirpath(INTEGER_LITERAL)'. You might need to add explicit type casts.

Examples

The directory part of each path, and the file name that follows it:

run(f"SELECT id, parse_dirpath(path), parse_filename(path) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, '/data/logs', 'app.log'), (2, '/home/ada/projects', 'notes.tar.gz'), (3, 'C:\\Users\\jose', 'poems.docx'), (4, 'exports/2026', ''), (5, None, None)]

Counting the files in each directory needs GROUP BY, which the Query Engine doesn't run in VAST Cluster 5.5: the statement fails before any data is read. Group in Trino or Spark, or read the two columns and group them in your application (see What isn't supported). To count the files in one directory, filter with WHERE parse_dirpath(path) = '…' and use count(*) without GROUP BY. The grouped query returns this error:

run(f"SELECT parse_dirpath(path) AS dir, count(*) FROM {TABLE} WHERE path IS NOT NULL "
    "GROUP BY dir ORDER BY dir LIMIT 10")

InternalError: The operator 'HASH_GROUP_BY' type=9 is currently not supported.

Keep only the files in one directory:

run(f"SELECT id, path FROM {TABLE} WHERE parse_dirpath(path) = '/data/logs' ORDER BY id LIMIT 10")

[(1, '/data/logs/app.log')]

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

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

The plan of the WHERE example shows the FILTER step above the scan:

plan(f"SELECT id FROM {TABLE} WHERE parse_dirpath(path) = '/data/logs'")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│  (parse_dirpath(path) = ' │
│        /data/logs')       │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│            path           │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Store the directory if you filter on it often. Compute it when you write the rows and keep it in its own column. A comparison of a raw column with a constant is pushed down to VAST DataBase, so rows that don't match never leave storage. The plan for folder = '/data/logs' has no FILTER step:

plan(f"SELECT id FROM {TABLE} WHERE folder = '/data/logs'")

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

• A pattern on the raw path doesn't help. path LIKE '/data/logs/%' isn't pushed down either: its plan has a FILTER step too. Store the directory instead.

plan(f"SELECT id FROM {TABLE} WHERE path LIKE '/data/logs/%'")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│prefix(path, '/data/logs/')│
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│            path           │
│             id            │
│                           │
└───────────────────────────┘

• Combine it with a pushed-down condition. In id > 100 AND parse_dirpath(path) = '/data/logs', id > 100 is still evaluated in VAST DataBase, so parse_dirpath() runs only on the rows that pass it.

Limitations and errors

• Strings only. A number, a decimal, a date, a boolean, a BLOB or a list fails with No function matches the given name and argument types.
• No root for a file in the root. parse_dirpath('/c.txt') returns '', the same as for a file name with no directory. Use parse_dirname ('/' for an absolute path) to tell them apart.
• A trailing separator is the last separator. parse_dirpath('a/b/') returns 'a/b', not 'a'.

run("SELECT parse_dirpath('\\xAA'::BLOB)")
run("SELECT parse_dirpath(['a', 'b'])")

InternalError: Binder Error: No function matches the given name and argument types 'parse_dirpath(BLOB)'. You might need to add explicit type casts.
InternalError: Binder Error: No function matches the given name and argument types 'parse_dirpath(VARCHAR[])'. You might need to add explicit type casts.

Similar functions

run("SELECT parse_dirpath('/home/ada/notes.txt'), parse_dirname('/home/ada/notes.txt'), "
    "parse_filename('/home/ada/notes.txt'), parse_path('/home/ada/notes.txt')")
run("SELECT parse_dirpath('C:\\dir\\file.tar.gz'), parse_dirname('C:\\dir\\file.tar.gz'), "
    "parse_filename('C:\\dir\\file.tar.gz'), parse_path('C:\\dir\\file.tar.gz')")

[('/home/ada', '/', 'notes.txt', ['/', 'home', 'ada', 'notes.txt'])]
[('C:\\dir', 'C:', 'file.tar.gz', ['C:', 'dir', 'file.tar.gz'])]

Clean up:

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

[]
[]

See also

• Text basics: all the text functions.
• parse_path: the separator modes in detail.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.