Skip to content

:hedgehog: PostHog is the leading platform for building self-driving products. Our developer tools – AI observability, analytics, session replay, flags, experiments, error tracking, logs, and more – capture all the context agents need to diagnose problems, uncover opportunities, and ship fixes. Steer it all from Slack, web, desktop, or the MCP.

25.1 KB
Raw Blame
from __future__ import annotations

import re
import random
import logging
from collections.abc import Callable, Iterable, Iterator
from dataclasses import dataclass, replace
from datetime import timedelta
from typing import Any, Literal, TypeVar, cast

from django.core.cache import cache
from django.utils.timezone import now

from clickhouse_driver import Client

from posthog.cache_utils import cache_for
from posthog.clickhouse.client import sync_execute
from posthog.clickhouse.client.connection import ClickHouseUser
from posthog.clickhouse.cluster import ClickhouseCluster, FuturesMap, HostInfo, get_cluster
from posthog.clickhouse.kafka_engine import trim_quotes_expr
from posthog.clickhouse.materialized_column_types import (
    MATERIALIZATION_VALID_TABLES,
    ColumnName,
    TablesWithMaterializedColumns,
)
from posthog.clickhouse.query_tagging import Feature, Product, tags_context
from posthog.models.event.sql import EVENTS_DATA_TABLE
from posthog.models.person.sql import PERSONS_TABLE
from posthog.models.property import PropertyName, TableColumn, TableWithProperties
from posthog.models.utils import generate_random_short_suffix
from posthog.settings import (
    CLICKHOUSE_DATABASE,
    MATERIALIZED_COLUMNS_CACHE_TIMEOUT,
    MATERIALIZED_COLUMNS_USE_CACHE,
    TEST,
)

logger = logging.getLogger(__name__)

T = TypeVar("T")

DEFAULT_TABLE_COLUMN: Literal["properties"] = "properties"

SHORT_TABLE_COLUMN_NAME = {
    "properties": "p",
    "group_properties": "gp",
    "person_properties": "pp",
    "group0_properties": "gp0",
    "group1_properties": "gp1",
    "group2_properties": "gp2",
    "group3_properties": "gp3",
    "group4_properties": "gp4",
}


def _clear_materialized_columns_cache(table: TablesWithMaterializedColumns) -> None:
    """Clear the cache for materialized columns after mutations."""
    cache_key = get_materialized_columns_cache_key(table)
    cache.delete(cache_key)


def get_materialized_columns_cache_key(table: TablesWithMaterializedColumns) -> str:
    return f"materialized_columns:v3:{table}"


@dataclass
class MaterializedColumn:
    name: ColumnName
    details: MaterializedColumnDetails
    is_nullable: bool
    column_type: str | None = None
    has_minmax_index: bool = False
    has_bloom_filter_index: bool = False
    has_ngram_lower_index: bool = False
    has_bloom_filter_lower_index: bool = False

    @property
    def type(self) -> str:
        if self.column_type is not None:
            return self.column_type
        if self.is_nullable:
            return "Nullable(String)"
        else:
            return "String"

    def get_expression_and_parameters(self) -> tuple[str, dict[str, Any]]:
        if self.column_type is not None or self.is_nullable:
            return (
                f"JSONExtract({self.details.table_column}, %(property_name)s, %(property_type)s)",
                {"property_name": self.details.property_name, "property_type": self.type},
            )
        else:
            return (
                trim_quotes_expr(f"JSONExtractRaw({self.details.table_column}, %(property)s)"),
                {"property": self.details.property_name},
            )

    @staticmethod
    def _get_all(table: TablesWithMaterializedColumns) -> list[tuple[str, str, str, bool, list[str]]]:
        # In TEST mode never do the probabilistic refresh: test-mode cache is process-local and
        # every schema mutation invalidates the key explicitly, so a random bypass would cause
        # non-deterministic extra system.columns queries that break assertNumQueries / snapshot tests.
        refresh_cache = not TEST and random.random() < 0.002  # we run around 50 of those queries per minute
        if table in MATERIALIZATION_VALID_TABLES and not refresh_cache and MATERIALIZED_COLUMNS_USE_CACHE:
            cache_key = get_materialized_columns_cache_key(table)

            try:
                cached_result = cache.get(cache_key)
                if cached_result is not None:
                    return cached_result
            except Exception:
                # If cache fails, continue to query ClickHouse
                pass

        # Get the data table name for index lookups (indexes are on data table, not distributed table)
        table_info = tables.get(table)
        data_table = table_info.data_table if table_info else table

        with tags_context(
            name="get_all_materialized_columns",
            product=Product.INTERNAL,
            feature=Feature.SCHEMA_INTROSPECTION,
        ):
            # Query columns and their indexes using multiple LEFT JOINs
            # Returns index names as an array, parsed in Python to set boolean flags
            # Note: Columns exist on both distributed and data tables, but indexes only exist on data tables
            result = sync_execute(
                """
                SELECT
                    c.name,
                    c.comment,
                    c.type,
                    c.type like 'Nullable(%%)' as is_nullable,
                    arrayFilter(x -> x != '', [i_minmax.name, i_bf.name, i_ngram.name, i_bf_lower.name]) as index_names
                FROM system.columns c
                LEFT JOIN system.data_skipping_indices i_minmax
                    ON i_minmax.database = c.database
                    AND i_minmax.table = %(data_table)s
                    AND i_minmax.name = concat('minmax_', c.name)
                LEFT JOIN system.data_skipping_indices i_bf
                    ON i_bf.database = c.database
                    AND i_bf.table = %(data_table)s
                    AND i_bf.name = concat('bloom_filter_', c.name)
                LEFT JOIN system.data_skipping_indices i_ngram
                    ON i_ngram.database = c.database
                    AND i_ngram.table = %(data_table)s
                    AND i_ngram.name = concat('ngram_bf_lower_', c.name)
                LEFT JOIN system.data_skipping_indices i_bf_lower
                    ON i_bf_lower.database = c.database
                    AND i_bf_lower.table = %(data_table)s
                    AND i_bf_lower.name = concat('bloom_filter_lower_', c.name)
                WHERE c.database = %(database)s
                  AND c.table = %(table)s
                  AND c.comment LIKE '%%column_materializer::%%'
                  AND c.comment not LIKE '%%column_materializer::elements_chain::%%'
                """,
                {"database": CLICKHOUSE_DATABASE, "table": table, "data_table": data_table},
                ch_user=ClickHouseUser.HOGQL,
            )

        if table in MATERIALIZATION_VALID_TABLES and MATERIALIZED_COLUMNS_USE_CACHE:
            try:
                cache.set(cache_key, result, MATERIALIZED_COLUMNS_CACHE_TIMEOUT)
            except Exception:
                # If cache set fails, log but don't fail the request
                logger.warning("Failed to cache materialized columns for table %s", table)

        return result

    @staticmethod
    def get_all(table: TablesWithMaterializedColumns) -> Iterator[MaterializedColumn]:
        if table not in MATERIALIZATION_VALID_TABLES:
            logger.error("HogQL trying to get materialized columns for table: %s", table)
            return

        rows = MaterializedColumn._get_all(table)
        for name, comment, column_type, is_nullable, index_names in rows:
            # Exact name matches: a prefix check would mismatch `bloom_filter_` against `bloom_filter_lower_`
            yield MaterializedColumn(
                name,
                MaterializedColumnDetails.from_column_comment(comment),
                is_nullable=bool(is_nullable),
                column_type=column_type,
                has_minmax_index=get_minmax_index_name(name) in index_names,
                has_bloom_filter_index=get_bloom_filter_index_name(name) in index_names,
                has_ngram_lower_index=get_ngram_lower_index_name(name) in index_names,
                has_bloom_filter_lower_index=get_bloom_filter_lower_index_name(name) in index_names,
            )

    @staticmethod
    def get(table: TablesWithMaterializedColumns, column_name: ColumnName) -> MaterializedColumn:
        # TODO: It would be more efficient to push the filter here down into the `get_all` query, but that would require
        # more a sophisticated method of constructing queries than we have right now, and this data set should be small
        # enough that this doesn't really matter (at least as of writing.)
        columns = [column for column in MaterializedColumn.get_all(table) if column.name == column_name]
        match columns:
            case []:
                raise ValueError("column does not exist")
            case [column]:
                return column
            case _:
                # this should never happen (column names are unique within a table) and suggests an error in the query
                raise ValueError(f"got {len(columns)} columns, expected 0 or 1")


@dataclass(frozen=True)
class MaterializedColumnDetails:
    table_column: TableColumn
    property_name: PropertyName
    is_disabled: bool

    COMMENT_PREFIX = "column_materializer"
    COMMENT_SEPARATOR = "::"
    COMMENT_DISABLED_MARKER = "disabled"

    def as_column_comment(self) -> str:
        bits = [self.COMMENT_PREFIX, self.table_column, self.property_name]
        if self.is_disabled:
            bits.append(self.COMMENT_DISABLED_MARKER)
        return self.COMMENT_SEPARATOR.join(bits)

    @classmethod
    def from_column_comment(cls, comment: str) -> MaterializedColumnDetails:
        match comment.split(cls.COMMENT_SEPARATOR, 3):
            # Old style comments have the format "column_materializer::property", dealing with the default table column.
            case [cls.COMMENT_PREFIX, property_name]:
                return MaterializedColumnDetails(DEFAULT_TABLE_COLUMN, property_name, is_disabled=False)
            # Otherwise, it's "column_materializer::table_column::property" for columns that are active.
            case [cls.COMMENT_PREFIX, table_column, property_name]:
                return MaterializedColumnDetails(cast(TableColumn, table_column), property_name, is_disabled=False)
            # Columns that are marked as disabled have an extra trailer indicating their status.
            case [cls.COMMENT_PREFIX, table_column, property_name, cls.COMMENT_DISABLED_MARKER]:
                return MaterializedColumnDetails(cast(TableColumn, table_column), property_name, is_disabled=True)
            case _:
                raise ValueError(f"unexpected comment format: {comment!r}")


@cache_for(timedelta(minutes=15), background_refresh=True)
def get_materialized_columns(
    table: TablesWithMaterializedColumns,
) -> dict[tuple[PropertyName, TableColumn], MaterializedColumn]:
    return {
        (column.details.property_name, column.details.table_column): column
        for column in MaterializedColumn.get_all(table)
    }


@cache_for(timedelta(minutes=15), background_refresh=True)
def get_enabled_materialized_columns(
    table: TablesWithMaterializedColumns,
) -> dict[tuple[PropertyName, TableColumn], MaterializedColumn]:
    return {k: column for k, column in get_materialized_columns(table).items() if not column.details.is_disabled}


@dataclass
class TableLayout:
    data_table: str

    @property
    def read_table(self) -> str:
        return self.data_table

    def map_data_nodes(self, cluster: ClickhouseCluster, fn: Callable[[Client], T]) -> FuturesMap[HostInfo, T]:
        first_shard = next(iter(cluster.shards))
        return cluster.map_any_host_in_shards({first_shard: fn})


@dataclass
class ShardedTable(TableLayout):
    dist_table: str

    @property
    def read_table(self) -> str:
        return self.dist_table

    def map_data_nodes(self, cluster: ClickhouseCluster, fn: Callable[[Client], T]) -> FuturesMap[HostInfo, T]:
        return cluster.map_one_host_per_shard(fn)


tables: dict[str, TableLayout | ShardedTable] = {
    PERSONS_TABLE: TableLayout(PERSONS_TABLE),
    "events": ShardedTable(EVENTS_DATA_TABLE(), "events"),
}


def get_minmax_index_name(column: str) -> str:
    return f"minmax_{column}"


def get_bloom_filter_index_name(column: str) -> str:
    return f"bloom_filter_{column}"


def get_ngram_lower_index_name(column: str) -> str:
    return f"ngram_bf_lower_{column}"


def get_bloom_filter_lower_index_name(column: str) -> str:
    return f"bloom_filter_lower_{column}"


@dataclass
class MinMaxIndex:
    column_name: str
    granularity: int = 1

    @property
    def name(self) -> str:
        return get_minmax_index_name(self.column_name)

    def as_add_sql(self) -> str:
        return f"ADD INDEX IF NOT EXISTS {self.name} {self.column_name} TYPE minmax GRANULARITY {self.granularity}"


@dataclass
class BloomFilterIndex:
    column_name: str
    false_positive_rate: float = 0.01
    granularity: int = 1

    @property
    def name(self) -> str:
        return get_bloom_filter_index_name(self.column_name)

    def as_add_sql(self) -> str:
        return f"ADD INDEX IF NOT EXISTS {self.name} {self.column_name} TYPE bloom_filter({self.false_positive_rate}) GRANULARITY {self.granularity}"


@dataclass
class NgramLowerIndex:
    """
    There are 2 limitations in clickhouse we need to work around:
    - clickhouse does not support a case-insensitive ngram index, so we need to call lower() on both sides and index lower(col)
    - clickhouse does not support ngram indexes on nullable columns, so we need to wrap the nullable version in coalesce(col, '')
    If either of these 2 limitations change, we should simplify this.
    """

    column_name: str
    is_nullable: bool
    ngram_size: int = 3
    filter_size: int = 256
    hash_functions: int = 2
    seed: int = 0
    granularity: int = 1

    @property
    def name(self) -> str:
        return get_ngram_lower_index_name(self.column_name)

    def as_add_sql(self) -> str:
        ngram_type = f"ngrambf_v1({self.ngram_size}, {self.filter_size}, {self.hash_functions}, {self.seed})"
        if self.is_nullable:
            return f"ADD INDEX IF NOT EXISTS {self.name} lower(coalesce({self.column_name}, '')) TYPE {ngram_type} GRANULARITY {self.granularity}"
        else:
            return f"ADD INDEX IF NOT EXISTS {self.name} lower({self.column_name}) TYPE {ngram_type} GRANULARITY {self.granularity}"


@dataclass
class BloomFilterLowerIndex:
    """
    A plain bloom_filter index over lower(column) for case-insensitive equality / IN lookups.

    Same 2 ClickHouse limitations as NgramLowerIndex (case-sensitive indexes, no nullable columns),
    so we index lower(col) and wrap nullable columns in coalesce(col, '').
    """

    column_name: str
    is_nullable: bool
    false_positive_rate: float = 0.01
    granularity: int = 1

    @property
    def name(self) -> str:
        return get_bloom_filter_lower_index_name(self.column_name)

    def as_add_sql(self) -> str:
        bf_type = f"bloom_filter({self.false_positive_rate})"
        if self.is_nullable:
            return f"ADD INDEX IF NOT EXISTS {self.name} lower(coalesce({self.column_name}, '')) TYPE {bf_type} GRANULARITY {self.granularity}"
        else:
            return f"ADD INDEX IF NOT EXISTS {self.name} lower({self.column_name}) TYPE {bf_type} GRANULARITY {self.granularity}"


@dataclass
class CreateColumnOnDataNodesTask:
    table: str
    column: MaterializedColumn
    create_minmax_index: bool
    add_column_comment: bool
    create_bloom_filter_index: bool = False
    create_ngram_lower_index: bool = False
    create_bloom_filter_lower_index: bool = False

    def execute(self, client: Client) -> None:
        expression, parameters = self.column.get_expression_and_parameters()
        actions = [
            f"ADD COLUMN IF NOT EXISTS {self.column.name} {self.column.type} DEFAULT {expression}",
        ]

        if self.add_column_comment:
            actions.append(f"COMMENT COLUMN {self.column.name} %(comment)s")
            parameters["comment"] = self.column.details.as_column_comment()

        if self.create_minmax_index:
            actions.append(MinMaxIndex(self.column.name).as_add_sql())

        if self.create_bloom_filter_index:
            actions.append(BloomFilterIndex(self.column.name).as_add_sql())

        if self.create_ngram_lower_index:
            actions.append(NgramLowerIndex(self.column.name, self.column.is_nullable).as_add_sql())

        if self.create_bloom_filter_lower_index:
            actions.append(BloomFilterLowerIndex(self.column.name, self.column.is_nullable).as_add_sql())

        client.execute(
            f"ALTER TABLE {self.table} " + ", ".join(actions),
            parameters,
            settings={"alter_sync": 2 if TEST else 1},
        )


@dataclass
class CreateColumnOnQueryNodesTask:
    table: str
    column: MaterializedColumn

    def execute(self, client: Client) -> None:
        client.execute(
            f"""
            ALTER TABLE {self.table}
                ADD COLUMN IF NOT EXISTS {self.column.name} {self.column.type},
                COMMENT COLUMN {self.column.name} %(comment)s
            """,
            {"comment": self.column.details.as_column_comment()},
            settings={"alter_sync": 2 if TEST else 1},
        )


def materialize(
    table: TableWithProperties,
    property: PropertyName,
    column_name: ColumnName | None = None,
    table_column: TableColumn = DEFAULT_TABLE_COLUMN,
    create_minmax_index=not TEST,
    is_nullable: bool = False,
    column_type: str | None = None,
    create_bloom_filter_index: bool = False,
    create_ngram_lower_index: bool = False,
    create_bloom_filter_lower_index: bool = False,
) -> MaterializedColumn:
    if existing_column := get_materialized_columns(table).get((property, table_column)):
        if TEST:
            return existing_column

        raise ValueError(f"Property already materialized. table={table}, property={property}, column={table_column}")

    if table_column not in SHORT_TABLE_COLUMN_NAME:
        raise ValueError(f"Invalid table_column={table_column} for materialisation")

    cluster = get_cluster()
    table_info = tables[table]

    column = MaterializedColumn(
        name=column_name or _materialized_column_name(table, property, table_column),
        details=MaterializedColumnDetails(
            table_column=table_column,
            property_name=property,
            is_disabled=False,
        ),
        is_nullable=is_nullable,
        column_type=column_type,
    )

    table_info.map_data_nodes(
        cluster,
        CreateColumnOnDataNodesTask(
            table_info.data_table,
            column,
            create_minmax_index,
            add_column_comment=table_info.read_table == table_info.data_table,
            create_bloom_filter_index=create_bloom_filter_index,
            create_ngram_lower_index=create_ngram_lower_index,
            create_bloom_filter_lower_index=create_bloom_filter_lower_index,
        ).execute,
    ).result()

    if isinstance(table_info, ShardedTable):
        cluster.map_all_hosts(
            CreateColumnOnQueryNodesTask(
                table_info.dist_table,
                column,
            ).execute
        ).result()

    _clear_materialized_columns_cache(table)
    return column


@dataclass
class UpdateColumnCommentTask:
    table: str
    columns: list[MaterializedColumn]

    def execute(self, client: Client) -> None:
        actions = []
        parameters = {}
        for i, column in enumerate(self.columns):
            parameter_name = f"comment_{i}"
            actions.append(f"COMMENT COLUMN {column.name} %({parameter_name})s")
            parameters[parameter_name] = column.details.as_column_comment()

        client.execute(
            f"ALTER TABLE {self.table} " + ", ".join(actions),
            parameters,
            settings={"alter_sync": 2 if TEST else 1},
        )


def update_column_is_disabled(
    table: TablesWithMaterializedColumns, column_names: Iterable[str], is_disabled: bool
) -> None:
    cluster = get_cluster()
    table_info = tables[table]

    columns = [MaterializedColumn.get(table, column_name) for column_name in column_names]

    cluster.map_all_hosts(
        UpdateColumnCommentTask(
            table_info.read_table,
            [replace(column, details=replace(column.details, is_disabled=is_disabled)) for column in columns],
        ).execute
    ).result()

    _clear_materialized_columns_cache(table)


def check_index_exists(client: Client, table: str, index: str) -> bool:
    [(count,)] = client.execute(
        """
        SELECT count()
        FROM system.data_skipping_indices
        WHERE database = currentDatabase()
          AND table = %(table)s
          AND name = %(name)s
        """,
        {"table": table, "name": index},
    )
    assert 1 >= count >= 0
    return bool(count)


def check_column_exists(client: Client, table: str, column: str) -> bool:
    [(count,)] = client.execute(
        """
        SELECT count()
        FROM system.columns
        WHERE database = currentDatabase()
          AND table = %(table)s
          AND name = %(name)s
        """,
        {"table": table, "name": column},
    )
    assert 1 >= count >= 0
    return bool(count)


@dataclass
class DropColumnTask:
    table: str
    column_names: list[str]
    try_drop_index: bool

    def execute(self, client: Client) -> None:
        actions = []

        for column_name in self.column_names:
            if self.try_drop_index:
                for get_index_name_fn in [
                    get_minmax_index_name,
                    get_bloom_filter_index_name,
                    get_ngram_lower_index_name,
                    get_bloom_filter_lower_index_name,
                ]:
                    index_name = get_index_name_fn(column_name)
                    drop_index_action = f"DROP INDEX IF EXISTS {index_name}"
                    if check_index_exists(client, self.table, index_name):
                        actions.append(drop_index_action)
                    else:
                        logger.info("Skipping %r, nothing to do...", drop_index_action)

            drop_column_action = f"DROP COLUMN IF EXISTS {column_name}"
            if check_column_exists(client, self.table, column_name):
                actions.append(drop_column_action)
            else:
                logger.info("Skipping %r, nothing to do...", drop_column_action)

        if actions:
            client.execute(
                f"ALTER TABLE {self.table} " + ", ".join(actions),
                settings={"alter_sync": 2 if TEST else 1},
            )


def drop_column(table: TablesWithMaterializedColumns, column_names: Iterable[str]) -> None:
    cluster = get_cluster()
    table_info = tables[table]
    column_names = [*column_names]

    if isinstance(table_info, ShardedTable):
        cluster.map_all_hosts(
            DropColumnTask(
                table_info.dist_table,
                column_names,
                try_drop_index=False,  # no indexes on distributed tables
            ).execute
        ).result()

    table_info.map_data_nodes(
        cluster,
        DropColumnTask(
            table_info.data_table,
            column_names,
            try_drop_index=True,
        ).execute,
    ).result()

    _clear_materialized_columns_cache(table)


@dataclass
class BackfillColumnTask:
    table: str
    columns: list[MaterializedColumn]
    backfill_period: timedelta | None
    test_settings: dict[str, Any] | None

    def execute(self, client: Client) -> None:
        # Kick off mutations which will update clickhouse partitions in the background. This will return immediately
        assignments = ", ".join(f"{column.name} = {column.name}" for column in self.columns)

        if self.backfill_period is not None:
            where_clause = "timestamp > %(cutoff)s"
            parameters = {"cutoff": (now() - self.backfill_period).strftime("%Y-%m-%d")}
        else:
            where_clause = "1 = 1"
            parameters = {}

        client.execute(
            f"ALTER TABLE {self.table} UPDATE {assignments} WHERE {where_clause}",
            parameters,
            settings=self.test_settings,
        )


def backfill_materialized_columns(
    table: TableWithProperties,
    columns: Iterable[MaterializedColumn],
    backfill_period: timedelta,
    test_settings=None,
) -> None:
    """
    Backfills the materialized column after its creation.

    This will require reading and writing a lot of data on clickhouse disk.
    """
    cluster = get_cluster()
    table_info = tables[table]

    table_info.map_data_nodes(
        cluster,
        BackfillColumnTask(
            table_info.data_table,
            [*columns],
            backfill_period if table == "events" else None,  # XXX
            test_settings,
        ).execute,
    ).result()


def _materialized_column_name(
    table: TableWithProperties,
    property: PropertyName,
    table_column: TableColumn = DEFAULT_TABLE_COLUMN,
) -> ColumnName:
    "Returns a sanitized and unique column name to use for materialized column"

    prefix = "pmat_" if table == "person" else "mat_"

    if table_column != DEFAULT_TABLE_COLUMN:
        prefix += f"{SHORT_TABLE_COLUMN_NAME[table_column]}_"
    property_str = re.sub("[^0-9a-zA-Z$]", "_", property)

    existing_materialized_column_names = {column.name for column in get_materialized_columns(table).values()}
    suffix = ""

    while f"{prefix}{property_str}{suffix}" in existing_materialized_column_names:
        suffix = "_" + generate_random_short_suffix()

    return f"{prefix}{property_str}{suffix}"