map_concat

Applies to VAST Cluster 5.5.

Functions › List, struct and map functions › Build a map. Merges two or more maps into one. Where a key is in more than one map, the value from the last of them wins.

Syntax

map_concat(map1, map2 [, ...])

Returns: a map of the same types as the arguments.

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_concat. 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. langs and words hold the word count of each translation; new_langs and new_words hold updated counts. Article 1 has a new count for a language it has, article 2 a new language, article 3 nothing, and the last row has no values. A table can't return a MAP column, so the keys and values are stored as lists:

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_concat"'

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, langs VARCHAR[], words BIGINT[], new_langs VARCHAR[], new_words BIGINT[])")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, ['en', 'de'], [120, 135], ['de'], [140]), "
    "(2, ['fr', 'es'], [NULL, 140], ['it'], [90]), "
    "(3, [], [], [], []), "
    "(4, NULL, NULL, NULL, NULL)")

[]
[]
[(4,)]

What it does

map_concat starts with the pairs of the first map and adds the pairs of each next map in turn:

• A new key is added at the end: map_concat(MAP {'b': 1, 'a': 2}, MAP {'c': 3}) is [('b', 1), ('a', 2), ('c', 3)].
• A key that's already there gets the later value and keeps its place: map_concat(MAP {'a': 1}, MAP {'b': 2}, MAP {'a': 3}) is [('a', 3), ('b', 2)], with one 'a'.
• Keys are compared exactly, so 'A' and 'a' are two keys.
• A NULL map is skipped. Only when every map is NULL is the result NULL.

Every map must have the same key type and the same value type. A map literal such as MAP {'de': 99} has INTEGER values, so merging it with a map of BIGINT values from a column fails; cast the literal, as in MAP {'de': 99}::MAP(VARCHAR, BIGINT).

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_concat(MAP {'a': 1}, NULL), map_concat(NULL, MAP {'a': 1}), "
    "map_concat(NULL::MAP(VARCHAR, BIGINT), NULL::MAP(VARCHAR, BIGINT))")
run("SELECT map_concat(MAP {'a': 1}, MAP {}), map_concat(MAP {'A': 1}, MAP {'a': 2}), "
    "map_concat(MAP {'a': 1}, MAP {'b': 2}, MAP {'a': 3})")
run("SELECT map_concat(MAP {'a': 1}, MAP {'b': 'x'})")
run("SELECT map_concat(MAP {'a': 1}, MAP {'a': NULL})")
run("SELECT map_concat(MAP {'a': 1})")
run("SELECT map_concat('abc', MAP {'a': 1})")

[([('a', 1)], [('a', 1)], None)]
[([('a', 1)], [('A', 1), ('a', 2)], [('a', 3), ('b', 2)])]
InternalError: Invalid Input Error: 'value' type of map differs between arguments, expected 'MAP(VARCHAR, INTEGER)', found 'MAP(VARCHAR, VARCHAR)' instead
InternalError: Invalid Input Error: 'value' type of map differs between arguments, expected 'MAP(VARCHAR, INTEGER)', found 'MAP(VARCHAR, "NULL")' instead
InternalError: Invalid Input Error: The provided amount of arguments is incorrect, please provide 2 or more maps
InternalError: Invalid Input Error: MAP_CONCAT only takes map arguments

Examples

Merge two literal maps. 'de' is in both, so the second value wins:

run("SELECT map_concat(MAP {'en': 'English', 'de': 'German'}, MAP {'de': 'Deutsch', 'fr': 'French'})")

[([('en', 'English'), ('de', 'Deutsch'), ('fr', 'French')],)]

Apply each article's updated counts to its current ones. Article 1's German count becomes 140, article 2 gains Italian, and article 4's NULL maps give NULL:

run(f"SELECT id, map_concat(map(langs, words), map(new_langs, new_words)) FROM {TABLE} ORDER BY id LIMIT 10")

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

Add a constant pair to every row. The literal has INTEGER values, so it needs a cast to match the BIGINT column:

run(f"SELECT id, map_concat(map(langs, words), MAP {{'la': 50}}) FROM {TABLE} ORDER BY id LIMIT 10")
run(f"SELECT id, map_concat(map(langs, words), MAP {{'la': 50}}::MAP(VARCHAR, BIGINT)) "
    f"FROM {TABLE} ORDER BY id LIMIT 10")

InternalError: Invalid Input Error: 'value' type of map differs between arguments, expected 'MAP(VARCHAR, BIGINT)', found 'MAP(VARCHAR, INTEGER)' instead
[(1, [('en', 120), ('de', 135), ('la', 50)]), (2, [('fr', None), ('es', 140), ('la', 50)]), (3, [('la', 50)]), (4, [('la', 50)])]

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

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

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

plan(f"SELECT id FROM {TABLE} WHERE map_concat(map(langs, words), map(new_langs, new_words))['de'] = 140")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #4            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│     (map_extract_value    │
│  (map_concat("map"(langs, │
│  words), "map"(new_langs, │
│  new_words)), 'de') = 140)│
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│           langs           │
│           words           │
│         new_langs         │
│         new_words         │
│             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.
• Merge the lists when you write the rows. If queries always apply the same updates, store the merged keys and values, so each query builds one map instead of two.

run(f"SELECT id, map_concat(map(langs, words), map(new_langs, new_words)) FROM {TABLE} "
    "WHERE id > 1 ORDER BY id LIMIT 10")

[(2, [('fr', None), ('es', 140), ('it', 90)]), (3, []), (4, None)]

Limitations and errors

• The types must match exactly. INTEGER and BIGINT values, or VARCHAR and INTEGER keys, fail with 'value' type of map differs between arguments (the message says 'value' for key mismatches too). Cast the maps to one type.
• At least two maps. One map or none fails with The provided amount of arguments is incorrect, please provide 2 or more maps.
• Only maps. Any other argument fails with MAP_CONCAT only takes map arguments; build a map first with map or map_from_entries.
• A table can't return a MAP column, so the maps are built from list columns (data types in SQL).

run("SELECT map_concat(MAP {'a': 1}, MAP {1: 2})")
run("SELECT map_concat(MAP {'a': 1}, MAP {'b': 2.5})")
run("SELECT map_concat(MAP {'a': 1}, [1])")

InternalError: Invalid Input Error: 'value' type of map differs between arguments, expected 'MAP(VARCHAR, INTEGER)', found 'MAP(INTEGER, INTEGER)' instead
InternalError: Invalid Input Error: 'value' type of map differs between arguments, expected 'MAP(VARCHAR, INTEGER)', found 'MAP(VARCHAR, DECIMAL(2,1))' instead
InternalError: Invalid Input Error: MAP_CONCAT only takes map arguments

Similar functions

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

[([('a', 3), ('b', 2)],)]
OSError: Map keys must be unique.
OSError: Map keys must be unique.

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_keys: the keys of a merged map, in order.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.