map_from_entries

Applies to VAST Cluster 5.5.

Functions › List, struct and map functions › Build a map. Builds a map from a list of structs with two fields: the first field of each struct is a key, the second its value.

Syntax

map_from_entries(entries)

Returns: MAP(key type, value type), the types of the two fields.

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_maps_map_from_entries. 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 four articles. counts holds the word count of each translation as a list of structs; langs and words hold the same kind of data as two lists. Article 2 has a translation with no word count yet, article 3 has no translations, and 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_maps_map_from_entries"'

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}.articles"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} "
    "(id BIGINT, counts STRUCT(lang VARCHAR, words BIGINT)[], langs VARCHAR[], words BIGINT[])")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, [{'lang': 'en', 'words': 120}, {'lang': 'de', 'words': 135}], ['en', 'de'], [120, 135]), "
    "(2, [{'lang': 'fr', 'words': NULL}], ['fr', 'es'], [NULL, 140]), "
    "(3, [], [], []), "
    "(4, NULL, NULL, NULL)")

[]
[]
[(4,)]

What it does

Each struct in the list becomes one pair of the map, in the order of the list. Only the position of the fields counts: {'k': 'a', 'v': 1}, {'key': 'a', 'value': 1} and the unnamed ('a', 1) all give the key 'a' with the value 1, and {'v': 1, 'k': 'a'} gives the key 1 with the value 'a'.

map_from_entries is the inverse of map_entries: map_from_entries(map_entries(m)) is m. Between the two, the list functions can filter or change the pairs. Python receives a map as a list of (key, value) tuples.

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 map_from_entries(NULL), map_from_entries([{'k': 'a', 'v': NULL}]), "
    "map_from_entries([]::STRUCT(k VARCHAR, v BIGINT)[])")
run("SELECT map_from_entries([('a', 1), ('b', 2)]), map_from_entries([{'v': 1, 'k': 'a'}])")
run("SELECT map_from_entries([{'k': NULL, 'v': 1}])")
run("SELECT map_from_entries([{'k': 'a', 'v': 1}, {'k': 'a', 'v': 2}])")
run("SELECT map_from_entries([])")
run("SELECT map_from_entries([{'k': 'a'}])")
run("SELECT map_from_entries('abc')")

[(None, [('a', None)], [])]
[([('a', 1), ('b', 2)], [(1, 'a')])]
OSError: Map keys can not be NULL.
OSError: Map keys must be unique.
InternalError: Invalid Input Error: The elements of the list must be structs
InternalError: Invalid Input Error: The provided struct type should only contain 2 fields, a key and a value
InternalError: Invalid Input Error: The provided argument is not a list of structs

Examples

Build a map per article from its list of structs. The NULL list of article 4 gives NULL:

run(f"SELECT id, map_from_entries(counts) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, [('en', 120), ('de', 135)]), (2, [('fr', None)]), (3, []), (4, None)]

Build the structs in the query. list_zip pairs two lists; list_transform builds a pair from each element of one list, here each language with its length. list_zip treats NULL lists as empty, so article 4 gets an empty map:

run(f"SELECT id, map_from_entries(list_zip(langs, words)) FROM {TABLE} ORDER BY id LIMIT 10")
run(f"SELECT id, map_from_entries(list_transform(langs, x -> {{'k': x, 'v': length(x)}})) "
    f"FROM {TABLE} ORDER BY id LIMIT 10")

[(1, [('en', 120), ('de', 135)]), (2, [('fr', None), ('es', 140)]), (3, []), (4, [])]
[(1, [('en', 2), ('de', 2)]), (2, [('fr', 2), ('es', 2)]), (3, []), (4, None)]

Change a map through its entries: keep some pairs, change the values, or change the keys:

run("SELECT map_from_entries(list_filter(map_entries(MAP {'a': 1, 'b': 2}), e -> e.value > 1))")
run("SELECT map_from_entries(list_transform(map_entries(MAP {'a': 1}), "
    "e -> {'key': e.key, 'value': e.value * 10}))")
run("SELECT map_from_entries(list_transform(map_entries(MAP {'a': 1}), "
    "e -> {'key': upper(e.key), 'value': e.value}))")

[([('b', 2)],)]
[([('a', 10)],)]
[([('A', 1)],)]

Parse a string of pairs into a map:

run("SELECT map_from_entries(list_transform(string_split('a:1,b:2', ','), "
    "x -> {'key': split_part(x, ':', 1), 'value': split_part(x, ':', 2)}))")

[([('a', '1'), ('b', '2')],)]

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

• In the SELECT list, the scan sends the list column, and the Query Engine builds the map for each row it returns.
• In WHERE, a FILTER step in the Query Engine builds the map and tests it for every row the scan sends. No rows are skipped in storage.

The plan of a lookup in WHERE shows the FILTER step above the scan:

plan(f"SELECT id FROM {TABLE} WHERE map_from_entries(counts)['de'] = 135")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│     (map_extract_value    │
│ (map_from_entries(counts),│
│        'de') = 135)       │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│           counts          │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Pair a map condition with a pushed-down one. A comparison of a plain column with a constant, such as id > 1, is evaluated in VAST DataBase, so the rows it rules out never reach the FILTER step.
• Keep a value you filter on in its own column. If queries often test one key, store its value in a column when you write the rows; a comparison on that column is evaluated in VAST DataBase.

run(f"SELECT id, map_from_entries(counts)['de'] FROM {TABLE} WHERE id > 1 ORDER BY id LIMIT 10")

[(2, None), (3, None), (4, None)]

Limitations and errors

• One bad row fails the whole query. If any row's list has a duplicate key or a NULL key, the query stops with Map keys must be unique. or Map keys can not be NULL., even for rows that are fine. Leave such rows out with WHERE, or remove the duplicates with list_filter first.
• An empty list needs a type. [] alone fails; cast it, as in []::STRUCT(k VARCHAR, v BIGINT)[]. A list column always has a type.
• No aggregate builds the list of entries. map_from_entries(list(...)) over a table fails, because list() isn't available (aggregate functions).
• A map can't be stored, but its entries can. Store the pairs in a list-of-structs column and build the map when you read it, as on this page.
• Not available: map_filter, transform_keys, transform_values, map_delete and str_to_map. The examples above show how to do each with map_from_entries.

run(f"SELECT map_from_entries(list({{'k': id, 'v': id}})) FROM {TABLE}")
run("SELECT map_from_entries(list_filter([{'k': 'a', 'v': 1}, {'k': 'a', 'v': 2}], e -> e.v > 1))")
run("SELECT map_filter(MAP {'a': 1, 'b': 2}, (k, v) -> v > 1)")

InternalError: The operator 'HASH_GROUP_BY' type=9 is currently not supported.
[([('a', 2)],)]
InternalError: Catalog Error: Scalar Function with name map_filter does not exist!

Similar functions

run("SELECT map_from_entries([{'k': 'a', 'v': 1}, {'k': 'b', 'v': 2}]), map(['a', 'b'], [1, 2]), "
    "map_concat(MAP {'a': 1}, MAP {'b': 2}), map_from_entries(map_entries(MAP {'a': 1, 'b': 2}))")

[([('a', 1), ('b', 2)], [('a', 1), ('b', 2)], [('a', 1), ('b', 2)], [('a', 1), ('b', 2)])]

Clean up:

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

[]
[]

See also

• List, struct and map functions: all the map functions.
• map_entries: a map's pairs as a list of structs.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.