probe.py

#!/usr/bin/env python3
"""probe.py - build tiny .hyper files with known value distributions.

Run with the interpreter that has tableauhyperapi (on this machine that is
/usr/bin/python3, the CommandLineTools build). Each probe is a single-column
table small enough that the packed code stream can be read by eye, with value
counts chosen so that the run boundaries fall *inside* a byte - that is what
makes the bit order observable. A symmetric distribution cannot distinguish
LSB-first from MSB-first, because reversing the codes within a byte leaves the
histogram unchanged.
"""
import os
import shutil
import sys

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

OUT = os.path.join(os.path.dirname(os.path.abspath(__file__)), "probes")

# name -> (column type, nullable, list of values)
PROBES = {
    # 3 distinct, counts 3/5/8 -> 2-bit codes, transition inside byte 0
    "p1_str3": (SqlType.text(), NOT_NULLABLE,
                ["aaa"] * 3 + ["bbb"] * 5 + ["ccc"] * 8),
    # 2 distinct, counts 3/13 -> 1-bit codes, transition inside byte 0
    "p2_str2": (SqlType.text(), NOT_NULLABLE,
                ["aaa"] * 3 + ["bbb"] * 13),
    # 5 distinct -> 4-bit codes
    "p3_str5": (SqlType.text(), NOT_NULLABLE,
                ["a"] * 1 + ["b"] * 2 + ["c"] * 3 + ["d"] * 4 + ["e"] * 6),
    # nullable with a known null count -> where does the null live?
    "p4_null": (SqlType.text(), NULLABLE,
                ["aaa"] * 3 + [None] * 5 + ["bbb"] * 8),
    # nullable integer
    "p5_inull": (SqlType.big_int(), NULLABLE,
                 [10] * 3 + [None] * 5 + [20] * 8),
    # integers, 3 distinct
    "p6_int3": (SqlType.big_int(), NOT_NULLABLE,
                [10] * 3 + [20] * 5 + [30] * 8),
    # many distinct strings -> 16-bit codes, exercises the dictionary layout
    "p7_str300": (SqlType.text(), NOT_NULLABLE,
                  [f"s{i:04d}" for i in range(300)]),
}


def main():
    shutil.rmtree(OUT, ignore_errors=True)
    os.makedirs(OUT)
    with HyperProcess(telemetry=Telemetry.DO_NOT_SEND_USAGE_DATA_TO_TABLEAU) as hp:
        for name, (sqltype, nullable, values) in PROBES.items():
            path = os.path.join(OUT, f"{name}.hyper")
            tdef = TableDefinition(TableName("Extract", "E"),
                                   [TableDefinition.Column("v", sqltype, nullable)])
            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] for v in values])
                    ins.execute()
                # Ground truth: value -> count, in the engine's own sort order.
                rows = c.execute_list_query(
                    f'SELECT v, COUNT(*) FROM {tdef.table_name} '
                    f'GROUP BY v ORDER BY v')
            print(f"{name}: {len(values)} rows, truth={[(r[0], r[1]) for r in rows]}")
    print(f"\nwrote {len(PROBES)} probes to {OUT}")


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