cardinality

Applies to VAST Cluster 5.5.

Functions › List, struct and map functions › Size. Returns the number of key-value pairs in a map. It works on maps only; for a list, use array_length.

Syntax

cardinality(map)

Returns: UBIGINT.

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_cardinality. 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.

A table can't return a MAP column, so the examples keep each map as two list columns, codes (the keys) and counts (the values), and build the map in the query with map(codes, counts). This block connects, defines the run() and plan() helpers, and creates a table of five rows. Row 3 has empty lists (an empty map) and row 4 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_cardinality"'

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}.langs"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, codes VARCHAR[], counts BIGINT[])")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, ['en', 'de'], [1, 2]), "
    "(2, ['\u00e9', '\u00fc'], [NULL, 3]), "
    "(3, [], []), "
    "(4, NULL, NULL), "
    "(5, ['k1', 'k2', 'k3'], [10, 20, 30])")

[]
[]
[(5,)]

What it does

cardinality counts the keys of a map, which is the number of key-value pairs, because a map's keys are unique:

• Every pair counts, including a key whose value is NULL: cardinality(MAP {'a': NULL}) is 1.
• An empty map (MAP {} or map()) has cardinality 0.
• Merged maps count each key once. map_concat(MAP {'a': 1}, MAP {'a': 2}) keeps one 'a', so its cardinality is 1.

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 cardinality(NULL), cardinality(NULL::MAP(VARCHAR, BIGINT)), cardinality(MAP {}), cardinality(map()), "
    "cardinality(MAP {'a': NULL})")
run("SELECT cardinality(MAP {'en': 'English', 'de': 'German'}), cardinality(map_concat(MAP {'a': 1}, MAP {'a': 2}))")
run("SELECT cardinality([1, 2, 3])")

[(None, None, 0, 0, 1)]
[(2, 1)]
InternalError: Binder Error: Cardinality can only operate on MAPs

Examples

The number of pairs in each row's map. Row 3's map is empty; row 4's lists are NULL, so its map and its count are NULL:

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

[(1, 2), (2, 2), (3, 0), (4, None), (5, 3)]

Keep only the rows whose map has more than one pair:

run(f"SELECT id FROM {TABLE} WHERE cardinality(map(codes, counts)) > 1 ORDER BY id LIMIT 10")

[(1,), (2,), (5,)]

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). A map exists only inside the query: the scan sends the list columns, and the Query Engine builds the map and counts its pairs, row by row:

• In the SELECT list, the Query Engine builds the map and computes cardinality() for each row it returns.
• In WHERE, the scan sends the lists for every row, and a FILTER step in the Query Engine builds the map, counts it 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 cardinality(map(codes, counts)) > 1")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #2            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│ (cardinality("map"(codes, │
│        counts)) > 1)      │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│           codes           │
│           counts          │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Count the key list instead. When the keys are in a list column, len(codes) > 1 returns the same rows without building the map. It still runs in a FILTER step:

run(f"SELECT id FROM {TABLE} WHERE len(codes) > 1 ORDER BY id LIMIT 10")
plan(f"SELECT id FROM {TABLE} WHERE len(codes) > 1")

[(1,), (2,), (5,)]
┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│      (len(codes) > 1)     │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│           codes           │
│             id            │
│                           │
└───────────────────────────┘

• Store the count if you filter on it often. Compute it when you write the rows and keep it in its own column: a comparison of that column with a constant is evaluated in VAST DataBase, cardinality(map(...)) > 1 isn't.

Limitations and errors

• Maps only. A list, string, number, date or struct fails with Cardinality can only operate on MAPs. For a list, use array_length or len.
• One argument. Two maps fail with Cardinality must have exactly one arguments; none fails with No function matches the given name and argument types 'cardinality()'.
• A map can't hold a key twice. Building a map with a repeated key fails at run time with Map keys must be unique., so there is nothing to count. Remove the duplicates first, or merge maps with map_concat, which keeps one value per key.
• Other SQL dialects' names aren't available. map_size fails with Scalar Function with name map_size does not exist!. Use cardinality.

run("SELECT cardinality(MAP {'a': 1}, MAP {'b': 2})")
run("SELECT cardinality(map(['a', 'a'], [1, 2]))")
run("SELECT map_size(MAP {'a': 1})")

InternalError: Binder Error: Cardinality must have exactly one arguments
OSError: Map keys must be unique.
InternalError: Catalog Error: Scalar Function with name map_size does not exist!

Similar functions

run("SELECT cardinality(MAP {'en': 'English', 'de': 'German'}), len(map_keys(MAP {'en': 'English', 'de': 'German'}))")
run("SELECT array_length([1, 2, 3]), len([1, 2, 3]), length([1, 2, 3])")

[(2, 2)]
[(3, 3, 3)]

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: build a map from two lists.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.