probe2.py

#!/usr/bin/env python3
"""probe2.py - map DataBlockCompression scheme codes to block behaviour.

One table, one column per (type, cardinality, has-nulls) combination, so a
single 1000-row insert produces one block per combination. Emits a table of
    scheme -> (kind, code width, reserves code 0 for NULL)
verified against the engine rather than inferred from the enum, which is not
decoded. Run with the interpreter that has tableauhyperapi.
"""
import os
import shutil
import sys

from tableauhyperapi import (HyperProcess, Connection, Telemetry, CreateMode,
                             TableDefinition, SqlType, Inserter, NULLABLE,
                             TableName)

HERE = os.path.dirname(os.path.abspath(__file__))
OUT = os.path.join(HERE, "probes2")
N = 1000

# label -> (SqlType, value factory taking the distinct index)
TYPES = {
    "txt": (SqlType.text(), lambda k: f"v{k:06d}"),
    "big": (SqlType.big_int(), lambda k: 1000 + k),
    "dbl": (SqlType.double(), lambda k: 1000.5 + k),
    "dat": (SqlType.date(), lambda k: __import__("datetime").date(2020, 1, 1) +
            __import__("datetime").timedelta(days=k)),
    "bool": (SqlType.bool(), lambda k: bool(k % 2)),
    "sml": (SqlType.small_int(), lambda k: 100 + k),
}
CARDS = [2, 3, 5, 20, 300, 900]


def columns():
    """(name, SqlType, values) for every combination that makes sense."""
    for tlabel, (sqltype, make) in TYPES.items():
        for card in CARDS:
            if tlabel == "bool" and card > 2:
                continue
            if tlabel == "sml" and card > 300:
                continue
            for nulls in (False, True):
                name = f"{tlabel}_c{card}_{'n' if nulls else 'x'}"
                vals = [make(i % card) for i in range(N)]
                if nulls:                      # a few real NULLs, evenly spread
                    for i in range(0, N, 7):
                        vals[i] = None
                yield name, sqltype, vals


def main():
    shutil.rmtree(OUT, ignore_errors=True)
    os.makedirs(OUT)
    cols = list(columns())
    path = os.path.join(OUT, "matrix.hyper")
    tdef = TableDefinition(
        TableName("Extract", "M"),
        [TableDefinition.Column(n, t, NULLABLE) for n, t, _ in cols])
    with HyperProcess(telemetry=Telemetry.DO_NOT_SEND_USAGE_DATA_TO_TABLEAU) as hp:
        with Connection(hp.endpoint, path, CreateMode.CREATE_AND_REPLACE) as c:
            c.catalog.create_schema("Extract")
            c.catalog.create_table(tdef)
            with Inserter(c, tdef) as ins:
                ins.add_rows([[v[i] for _, _, v in cols] for i in range(N)])
                ins.execute()
    print(f"wrote {path}: {len(cols)} columns x {N} rows")
    return 0


if __name__ == "__main__":
    sys.exit(main())