probe6.py

#!/usr/bin/env python3
"""probe6.py - force nullable *uncompressed* blocks for the counter types.

`null_sentinel()` is verified for SmallInt, Float, Double and the variable-width
types. Date, Time, Timestamp and Oid never reached a nullable-uncompressed
encoding in probe5, so their sentinel is an assumption sitting in shipped code.

Uncompressed is only chosen when a dictionary does not pay (every value
distinct) *and* truncation does not pay either - i.e. the block's value range
needs the type's full width. So each column below is all-distinct and spread
across as much of the type's domain as the engine will accept, with NULLs mixed
in. Where the Python binding caps the range (datetime.date stops at year 9999)
the rows go in through SQL instead.
"""
import datetime
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, "probes6")
N = 20_000
STEP = ((1 << 31) - 1) // N    # Oid tops out at 2**31-1, not 2**32-1


def build_oid(hp):
    path = os.path.join(OUT, "oid_wide.hyper")
    tdef = TableDefinition(TableName("Extract", "E"), [
        TableDefinition.Column("v", SqlType.oid(), NULLABLE)])
    vals = [None if i % 7 == 0 else i * STEP for i in range(N)]
    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 vals])
            ins.execute()
    return path


def build_sql(hp, label, sqltype_sql, exprs):
    """Rows built by SQL so values can exceed what Python's datetime allows."""
    path = os.path.join(OUT, f"{label}.hyper")
    with Connection(hp.endpoint, path, CreateMode.CREATE_AND_REPLACE) as c:
        c.execute_command('CREATE SCHEMA "Extract"')
        c.execute_command(f'CREATE TABLE "Extract"."E" ("v" {sqltype_sql})')
        chunk = []
        for i, e in enumerate(exprs):
            chunk.append("(NULL)" if i % 7 == 0 else f"({e})")
            if len(chunk) == 5000:
                c.execute_command(f'INSERT INTO "Extract"."E" VALUES {",".join(chunk)}')
                chunk = []
        if chunk:
            c.execute_command(f'INSERT INTO "Extract"."E" VALUES {",".join(chunk)}')
    return path


def main():
    shutil.rmtree(OUT, ignore_errors=True)
    os.makedirs(OUT)
    made, failed = [], []
    with HyperProcess(telemetry=Telemetry.DO_NOT_SEND_USAGE_DATA_TO_TABLEAU,
                      parameters={"default_database_version": "4"}) as hp:
        # Each column spans the widest range its type accepts, all-distinct,
        # with NULLs - the most favourable possible conditions for the engine
        # to give up on truncation and store values uncompressed.
        jobs = [
            ("oid", lambda: build_oid(hp)),                       # 0 .. 2**31-1
            ("date", lambda: build_sql(hp, "date", "DATE", [      # year 1 .. 294276
                f"DATE '0001-01-01' + {i * 5374}" for i in range(N)])),
            ("ts", lambda: build_sql(hp, "ts", "TIMESTAMP", [
                f"TIMESTAMP '0001-01-01 00:00:00' + {i * 5374} * INTERVAL '1 day'"
                for i in range(N)])),
            ("time", lambda: build_sql(hp, "time", "TIME", [      # the whole day
                f"TIME '00:00:00' + {i * 4319999} * INTERVAL '1 microsecond'"
                for i in range(N)])),
        ]
        for label, fn in jobs:
            try:
                made.append(os.path.basename(fn()))
            except Exception as e:
                failed.append(f"{label}: {str(e).splitlines()[0][:100]}")
    for m in made:
        print(f"  ok   {m}")
    for m in failed:
        print(f"  FAIL {m}")
    return 0


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