parse_path

Applies to VAST Cluster 5.5.

Functions › Text basics › Paths. Splits a file path into a list of its components: the directories, then the file name. By default both / and \ separate components. It has no aliases.

Syntax

parse_path(path)
parse_path(path, separator)

Returns: VARCHAR[], a list of strings (a Python list in the driver's results).

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_path. 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 number of components 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_path"'

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

[]
[]
[(5,)]

What it does

parse_path splits the path at each separator and returns the parts in order:

The separator argument. Without it, / and \ both separate components, as with 'both_slash'. 'forward_slash' splits only on /, and 'backslash' only on \; the other character is then part of a component. On the cluster, 'system' splits only on /, like 'forward_slash':

The names are case-sensitive: parse_path('a/b', 'BACKSLASH') returns ['a', 'b'], because 'BACKSLASH' isn't recognised and both characters separate. Because an unknown or misspelt name ('BACKSLASH', 'backslashes', 'forward') isn't an error, it goes unnoticed. Check the spelling against the list above.

In Python code, write each \ of a path as \\ inside the string ("parse_path('a/b\\c')"), so that the SQL receives one backslash. Python also prints each backslash in the results doubled: 'b\\c' is the three-character string b\c.

NULLs and edge cases

The same cases, as the cluster returns them. The third and fourth lines run every separator mode on a path with both separators; Python prints b\c as 'b\\c'. They use the table and helpers from Set up the examples:

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

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

Examples

The components of each path, and how many there are:

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

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

The same paths split only on /. The Windows path stays in one piece:

run(f"SELECT id, parse_path(path, 'forward_slash') 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)]

Keep only the paths with at least five components:

run(f"SELECT id, path FROM {TABLE} WHERE length(parse_path(path)) >= 5 ORDER BY id LIMIT 10")

[(2, '/home/ada/projects/notes.tar.gz')]

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

• In the SELECT list, the scan reads the column and the Query Engine computes parse_path() 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_path() 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 length(parse_path(path)) >= 5")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│ (length(parse_path(path)) │
│           >= 5)           │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│            path           │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Store the part you filter on. If you filter on a path's depth, its top-level directory or its file name, 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 depth >= 5 has no FILTER step:

plan(f"SELECT id FROM {TABLE} WHERE depth >= 5")

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

• Combine it with a pushed-down condition. In id > 100 AND length(parse_path(path)) >= 5, id > 100 is still evaluated in VAST DataBase, so parse_path() 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.
• Unknown separator names aren't errors. A value other than the lower-case mode names (an upper-case 'BACKSLASH' included) is accepted, and so is NULL; both split on / and \. See What it does.
• 'system' means / on the cluster. Use 'backslash' or the default for Windows paths.
• Empty components are dropped. '/a//b/' gives ['/', 'a', 'b']: the doubled and trailing separators are gone, so you can't always rebuild the original string from the list.

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

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

Similar functions

To split a string on a separator of your choice instead, see string_split.

run("SELECT parse_path('/home/ada/notes.txt'), parse_dirname('/home/ada/notes.txt'), parse_dirpath('/home/ada/notes.txt'), "
    "parse_filename('/home/ada/notes.txt'), parse_filename('/home/ada/notes.txt', true)")

[(['/', 'home', 'ada', 'notes.txt'], '/', '/home/ada', 'notes.txt', 'notes')]

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_dirname, parse_dirpath and parse_filename: one part of a path.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.