from infi.clickhouse_orm import migrations operations = [ migrations.RunSQL("INSERT INTO `mig` (date, f1, f3, f4) VALUES ('2016-01-01', 1, 1, 'test') "), migrations.RunSQL([ "INSERT INTO `mig` (date, f1, f3, f4) VALUES ('2016-01-02', 2, 2, 'test2') ", "INSERT INTO `mig` (date, f1, f3, f4) VALUES ('2016-01-03', 3, 3, 'test3') ", ]) ]
from infi.clickhouse_orm import migrations # type: ignore from ee.clickhouse.sql.elements import ( ELEMENTS_PROPERTIES_MAT, ELEMENTS_TABLE_SQL, ELEMENTS_WITH_ARRAY_PROPS, ELEMENTS_WITH_ARRAY_PROPS_MAT, ) operations = [ migrations.RunSQL(ELEMENTS_TABLE_SQL), migrations.RunSQL(ELEMENTS_WITH_ARRAY_PROPS), migrations.RunSQL(ELEMENTS_WITH_ARRAY_PROPS_MAT), migrations.RunSQL(ELEMENTS_PROPERTIES_MAT), ]
from infi.clickhouse_orm import migrations from ee.clickhouse.sql.person import KAFKA_PERSONS_TABLE_SQL, PERSONS_TABLE_MV_SQL from posthog.settings import CLICKHOUSE_CLUSTER operations = [ migrations.RunSQL( f"DROP TABLE person_mv ON CLUSTER '{CLICKHOUSE_CLUSTER}'"), migrations.RunSQL( f"DROP TABLE kafka_person ON CLUSTER '{CLICKHOUSE_CLUSTER}'"), migrations.RunSQL( f"ALTER TABLE person ON CLUSTER '{CLICKHOUSE_CLUSTER}' ADD COLUMN IF NOT EXISTS distinct_ids Array(VARCHAR)" ), migrations.RunSQL(KAFKA_PERSONS_TABLE_SQL()), migrations.RunSQL(PERSONS_TABLE_MV_SQL), ]
from infi.clickhouse_orm import migrations from ee.clickhouse.sql.dead_letter_queue import ( DEAD_LETTER_QUEUE_TABLE_MV_SQL, DEAD_LETTER_QUEUE_TABLE_SQL, KAFKA_DEAD_LETTER_QUEUE_TABLE_SQL, ) operations = [ migrations.RunSQL(DEAD_LETTER_QUEUE_TABLE_SQL), migrations.RunSQL(KAFKA_DEAD_LETTER_QUEUE_TABLE_SQL), migrations.RunSQL(DEAD_LETTER_QUEUE_TABLE_MV_SQL), ]
from infi.clickhouse_orm import migrations from posthog.settings import CLICKHOUSE_CLUSTER already_materialized_columns = [ ("properties_issampledevent", "isSampledEvent"), ("properties_currentscreen", "currentScreen"), ("properties_objectname", "objectName"), ] operations = [] for column_name, property in already_materialized_columns: statement = f"ALTER TABLE events ON CLUSTER {CLICKHOUSE_CLUSTER} COMMENT COLUMN IF EXISTS {column_name} 'column_materializer::{property}'" operations.append(migrations.RunSQL(statement))
from infi.clickhouse_orm import migrations from ee.clickhouse.sql.plugin_log_entries import ( KAFKA_PLUGIN_LOG_ENTRIES_TABLE_SQL, PLUGIN_LOG_ENTRIES_TABLE_MV_SQL, PLUGIN_LOG_ENTRIES_TABLE_SQL, ) operations = [ migrations.RunSQL(PLUGIN_LOG_ENTRIES_TABLE_SQL()), migrations.RunSQL(KAFKA_PLUGIN_LOG_ENTRIES_TABLE_SQL()), migrations.RunSQL(PLUGIN_LOG_ENTRIES_TABLE_MV_SQL), ]
from infi.clickhouse_orm import migrations from ee.clickhouse.sql.person import COMMENT_DISTINCT_ID_COLUMN_SQL, PERSONS_DISTINCT_ID_TABLE_SQL, PERSONS_TABLE_SQL operations = [ migrations.RunSQL(PERSONS_TABLE_SQL()), migrations.RunSQL(PERSONS_DISTINCT_ID_TABLE_SQL()), # :TRICKY: This is only run on new installations, we use this to know to skip # posthog/async_migrations/migrations/0003_fill_person_distinct_id2.py # We would use table comments but can't due to clickhouse version limitations migrations.RunSQL(COMMENT_DISTINCT_ID_COLUMN_SQL()), ]
from infi.clickhouse_orm import migrations from ee.clickhouse.sql.session_recording_events import ( DISTRIBUTED_SESSION_RECORDING_EVENTS_TABLE_SQL, KAFKA_SESSION_RECORDING_EVENTS_TABLE_SQL, SESSION_RECORDING_EVENTS_MATERIALIZED_COLUMN_COMMENTS_SQL, SESSION_RECORDING_EVENTS_TABLE_MV_SQL, SESSION_RECORDING_EVENTS_TABLE_SQL, WRITABLE_SESSION_RECORDING_EVENTS_TABLE_SQL, ) from posthog.settings.data_stores import CLICKHOUSE_REPLICATION operations = [ migrations.RunSQL(SESSION_RECORDING_EVENTS_TABLE_SQL()), migrations.RunSQL( SESSION_RECORDING_EVENTS_MATERIALIZED_COLUMN_COMMENTS_SQL()), migrations.RunSQL(KAFKA_SESSION_RECORDING_EVENTS_TABLE_SQL()), migrations.RunSQL(SESSION_RECORDING_EVENTS_TABLE_MV_SQL()), ] if CLICKHOUSE_REPLICATION: operations = [ migrations.RunSQL(WRITABLE_SESSION_RECORDING_EVENTS_TABLE_SQL()), migrations.RunSQL(DISTRIBUTED_SESSION_RECORDING_EVENTS_TABLE_SQL()), ] + operations
from infi.clickhouse_orm import migrations from ee.clickhouse.sql.person import * from posthog.settings import CLICKHOUSE_CLUSTER, CLICKHOUSE_REPLICATION TEMPORARY_TABLE_NAME = "person_distinct_id_tmp_migration_0016" if CLICKHOUSE_REPLICATION: # :KLUDGE: The original migration updated person_distinct_id in ways new installs don't need to. # Also this migration fails due to repeated zk paths when replicated. # Given this, skip this migration operations = [] else: operations = [ migrations.RunSQL(PERSONS_DISTINCT_ID_TABLE_SQL().replace( PERSONS_DISTINCT_ID_TABLE, TEMPORARY_TABLE_NAME, 1)), migrations.RunSQL( f"DROP TABLE person_distinct_id_mv ON CLUSTER '{CLICKHOUSE_CLUSTER}'" ), migrations.RunSQL( f"DROP TABLE kafka_person_distinct_id ON CLUSTER '{CLICKHOUSE_CLUSTER}'" ), migrations.RunSQL(f""" INSERT INTO {TEMPORARY_TABLE_NAME} (distinct_id, person_id, team_id, _sign, _timestamp, _offset) SELECT distinct_id, person_id, team_id, if(is_deleted==0, 1, -1) as _sign, _timestamp, _offset
from infi.clickhouse_orm import migrations from ee.clickhouse.sql.person import ( KAFKA_PERSON_DISTINCT_ID2_TABLE_SQL, PERSON_DISTINCT_ID2_MV_SQL, PERSON_DISTINCT_ID2_TABLE_SQL, ) operations = [ migrations.RunSQL(PERSON_DISTINCT_ID2_TABLE_SQL()), migrations.RunSQL(KAFKA_PERSON_DISTINCT_ID2_TABLE_SQL()), migrations.RunSQL(PERSON_DISTINCT_ID2_MV_SQL, ), ]
from infi.clickhouse_orm import migrations from ee.clickhouse.sql.person import KAFKA_PERSONS_DISTINCT_ID_TABLE_SQL, PERSONS_DISTINCT_ID_TABLE_MV_SQL from posthog.settings import CLICKHOUSE_CLUSTER operations = [ migrations.RunSQL( f"DROP TABLE person_distinct_id_mv ON CLUSTER '{CLICKHOUSE_CLUSTER}'"), migrations.RunSQL( f"DROP TABLE kafka_person_distinct_id ON CLUSTER '{CLICKHOUSE_CLUSTER}'" ), migrations.RunSQL( f"ALTER TABLE person_distinct_id ON CLUSTER '{CLICKHOUSE_CLUSTER}' ADD COLUMN IF NOT EXISTS is_deleted Int8 DEFAULT 0" ), migrations.RunSQL(KAFKA_PERSONS_DISTINCT_ID_TABLE_SQL()), migrations.RunSQL(PERSONS_DISTINCT_ID_TABLE_MV_SQL), ]
from infi.clickhouse_orm import migrations from ee.clickhouse.sql.person import KAFKA_PERSONS_TABLE_SQL, PERSONS_TABLE_MV_SQL operations = [ migrations.RunSQL("DROP TABLE person_mv"), migrations.RunSQL("DROP TABLE kafka_person"), migrations.RunSQL("ALTER TABLE person ADD COLUMN IF NOT EXISTS is_deleted Boolean DEFAULT 0"), migrations.RunSQL(KAFKA_PERSONS_TABLE_SQL), migrations.RunSQL(PERSONS_TABLE_MV_SQL), ]
from infi.clickhouse_orm import migrations from ee.clickhouse.sql.events import EVENTS_TABLE_SQL from posthog.settings import CLICKHOUSE_CLUSTER, CLICKHOUSE_DATABASE operations = [ migrations.RunSQL( f"CREATE DATABASE IF NOT EXISTS {CLICKHOUSE_DATABASE} ON CLUSTER '{CLICKHOUSE_CLUSTER}'" ), migrations.RunSQL(EVENTS_TABLE_SQL()), ]
from infi.clickhouse_orm import migrations operations = [migrations.RunSQL("SELECT 1")]
from infi.clickhouse_orm import migrations # type: ignore from ee.clickhouse.sql.events import EVENTS_TABLE_SQL operations = [ migrations.RunSQL(EVENTS_TABLE_SQL), ]
from infi.clickhouse_orm import migrations from ee.clickhouse.sql.events import EVENTS_WITH_PROPS_TABLE_SQL operations = [ migrations.RunSQL(EVENTS_WITH_PROPS_TABLE_SQL), ]
from infi.clickhouse_orm import migrations from ee.clickhouse.sql.session_recording_events import ( KAFKA_SESSION_RECORDING_EVENTS_TABLE_SQL, SESSION_RECORDING_EVENTS_TABLE_MV_SQL, SESSION_RECORDING_EVENTS_TABLE_SQL, ) operations = [ migrations.RunSQL(SESSION_RECORDING_EVENTS_TABLE_SQL), migrations.RunSQL(KAFKA_SESSION_RECORDING_EVENTS_TABLE_SQL), migrations.RunSQL(SESSION_RECORDING_EVENTS_TABLE_MV_SQL), ]
from infi.clickhouse_orm import migrations from ee.clickhouse.sql.person import PERSON_STATIC_COHORT_TABLE_SQL operations = [ migrations.RunSQL(PERSON_STATIC_COHORT_TABLE_SQL()), ]
from infi.clickhouse_orm import migrations from ee.clickhouse.sql.cohort import CREATE_COHORTPEOPLE_TABLE_SQL operations = [migrations.RunSQL(CREATE_COHORTPEOPLE_TABLE_SQL)]
from infi.clickhouse_orm import migrations from ee.clickhouse.sql.groups import GROUPS_TABLE_MV_SQL, GROUPS_TABLE_SQL, KAFKA_GROUPS_TABLE_SQL operations = [ migrations.RunSQL(GROUPS_TABLE_SQL), migrations.RunSQL(KAFKA_GROUPS_TABLE_SQL), migrations.RunSQL(GROUPS_TABLE_MV_SQL), ]
from infi.clickhouse_orm import migrations from ee.clickhouse.sql.person import PERSONS_DISTINCT_ID_TABLE_SQL, PERSONS_TABLE_SQL operations = [ migrations.RunSQL(PERSONS_TABLE_SQL), migrations.RunSQL(PERSONS_DISTINCT_ID_TABLE_SQL) ]
from infi.clickhouse_orm import migrations from ee.clickhouse.sql.events import ( EVENTS_WITH_PROPS_TABLE_SQL, MAT_EVENT_PROP_TABLE_SQL, MAT_EVENTS_WITH_PROPS_TABLE_SQL, ) operations = [ migrations.RunSQL(EVENTS_WITH_PROPS_TABLE_SQL), migrations.RunSQL(MAT_EVENTS_WITH_PROPS_TABLE_SQL), migrations.RunSQL(MAT_EVENT_PROP_TABLE_SQL), ]
from infi.clickhouse_orm import migrations operations = [ migrations.RunSQL( """ CREATE MATERIALIZED VIEW events_daily_summary ENGINE = SummingMergeTree PARTITION BY toYYYYMM(date) ORDER BY (tid, cn, date) POPULATE AS SELECT toStartOfDay(event_date) AS date, tid, cn, countIf(t='pageview') as pageviews, countIf(t='event' AND ec='Outbound Link' AND ea='click') as clicks, clicks / pageviews as ctr, ctr * 100 as ctr_perc, sumIf(r, t='revenue') as revenue FROM events GROUP BY tid, cn, date """ ) ]
from infi.clickhouse_orm import migrations from ee.clickhouse.sql.events import ( DISTRIBUTED_EVENTS_TABLE_SQL, EVENTS_TABLE_MV_SQL, KAFKA_EVENTS_TABLE_SQL, WRITABLE_EVENTS_TABLE_SQL, ) from ee.clickhouse.sql.person import ( KAFKA_PERSONS_DISTINCT_ID_TABLE_SQL, KAFKA_PERSONS_TABLE_SQL, PERSONS_DISTINCT_ID_TABLE_MV_SQL, PERSONS_TABLE_MV_SQL, ) from posthog.settings import CLICKHOUSE_REPLICATION operations = [ migrations.RunSQL(KAFKA_EVENTS_TABLE_SQL()), migrations.RunSQL(KAFKA_PERSONS_TABLE_SQL()), migrations.RunSQL(KAFKA_PERSONS_DISTINCT_ID_TABLE_SQL()), migrations.RunSQL(EVENTS_TABLE_MV_SQL()), migrations.RunSQL(PERSONS_TABLE_MV_SQL), migrations.RunSQL(PERSONS_DISTINCT_ID_TABLE_MV_SQL), ] if CLICKHOUSE_REPLICATION: operations.extend( [migrations.RunSQL(WRITABLE_EVENTS_TABLE_SQL()), migrations.RunSQL(DISTRIBUTED_EVENTS_TABLE_SQL())] )
from infi.clickhouse_orm import migrations from ee.clickhouse.sql.person import KAFKA_PERSONS_TABLE_SQL, PERSONS_TABLE_MV_SQL from posthog.settings import CLICKHOUSE_CLUSTER operations = [ migrations.RunSQL(f"DROP TABLE person_mv ON CLUSTER {CLICKHOUSE_CLUSTER}"), migrations.RunSQL(f"DROP TABLE kafka_person ON CLUSTER {CLICKHOUSE_CLUSTER}"), migrations.RunSQL(f"ALTER TABLE person ON CLUSTER {CLICKHOUSE_CLUSTER} DROP COLUMN IF EXISTS distinct_ids"), migrations.RunSQL(KAFKA_PERSONS_TABLE_SQL), migrations.RunSQL(PERSONS_TABLE_MV_SQL), ]
from infi.clickhouse_orm import migrations from ee.clickhouse.sql.dead_letter_queue import DEAD_LETTER_QUEUE_TABLE_MV_SQL, KAFKA_DEAD_LETTER_QUEUE_TABLE_SQL from posthog.settings import CLICKHOUSE_CLUSTER operations = [ migrations.RunSQL( f"DROP TABLE events_dead_letter_queue_mv ON CLUSTER '{CLICKHOUSE_CLUSTER}'" ), migrations.RunSQL( f"DROP TABLE kafka_events_dead_letter_queue ON CLUSTER '{CLICKHOUSE_CLUSTER}'" ), migrations.RunSQL( f"ALTER TABLE events_dead_letter_queue ON CLUSTER '{CLICKHOUSE_CLUSTER}' ADD COLUMN IF NOT EXISTS tags Array(VARCHAR) AFTER error" ), migrations.RunSQL(KAFKA_DEAD_LETTER_QUEUE_TABLE_SQL()), migrations.RunSQL(DEAD_LETTER_QUEUE_TABLE_MV_SQL), ]
from infi.clickhouse_orm import migrations from ee.clickhouse.sql.person import KAFKA_PERSONS_TABLE_SQL, PERSONS_TABLE_MV_SQL from posthog.settings import CLICKHOUSE_CLUSTER operations = [ migrations.RunSQL(f"DROP TABLE person_mv ON CLUSTER {CLICKHOUSE_CLUSTER}"), migrations.RunSQL(f"DROP TABLE kafka_person ON CLUSTER {CLICKHOUSE_CLUSTER}"), migrations.RunSQL( f"ALTER TABLE person ON CLUSTER {CLICKHOUSE_CLUSTER} ADD COLUMN IF NOT EXISTS is_deleted Boolean DEFAULT 0" ), migrations.RunSQL(KAFKA_PERSONS_TABLE_SQL), migrations.RunSQL(PERSONS_TABLE_MV_SQL), ]
from infi.clickhouse_orm import migrations from ee.clickhouse.sql.session_recording_events import ( KAFKA_SESSION_RECORDING_EVENTS_TABLE_SQL, SESSION_RECORDING_EVENTS_TABLE_MV_SQL, ) from posthog.settings import CLICKHOUSE_CLUSTER operations = [ migrations.RunSQL( f"DROP TABLE session_recording_events_mv ON CLUSTER {CLICKHOUSE_CLUSTER}" ), migrations.RunSQL( f"DROP TABLE kafka_session_recording_events ON CLUSTER {CLICKHOUSE_CLUSTER}" ), migrations.RunSQL( f"ALTER TABLE session_recording_events ON CLUSTER {CLICKHOUSE_CLUSTER} ADD COLUMN IF NOT EXISTS window_id VARCHAR AFTER session_id" ), migrations.RunSQL(KAFKA_SESSION_RECORDING_EVENTS_TABLE_SQL), migrations.RunSQL(SESSION_RECORDING_EVENTS_TABLE_MV_SQL), ]
from infi.clickhouse_orm import migrations from ee.clickhouse.sql.events import EVENTS_TABLE_MV_SQL, KAFKA_EVENTS_TABLE_SQL from ee.clickhouse.sql.person import ( KAFKA_PERSONS_DISTINCT_ID_TABLE_SQL, KAFKA_PERSONS_TABLE_SQL, PERSONS_DISTINCT_ID_TABLE_MV_SQL, PERSONS_TABLE_MV_SQL, ) operations = [ migrations.RunSQL(KAFKA_EVENTS_TABLE_SQL), migrations.RunSQL(KAFKA_PERSONS_TABLE_SQL), migrations.RunSQL(KAFKA_PERSONS_DISTINCT_ID_TABLE_SQL), migrations.RunSQL(EVENTS_TABLE_MV_SQL), migrations.RunSQL(PERSONS_TABLE_MV_SQL), migrations.RunSQL(PERSONS_DISTINCT_ID_TABLE_MV_SQL), ]
from infi.clickhouse_orm import migrations from ee.clickhouse.sql.cohort import CREATE_COHORTPEOPLE_TABLE_SQL, DROP_COHORTPEOPLE_TABLE_SQL from posthog.settings import CLICKHOUSE_REPLICATION # run create table again with proper configuration operations = [ migrations.RunSQL(DROP_COHORTPEOPLE_TABLE_SQL), migrations.RunSQL(CREATE_COHORTPEOPLE_TABLE_SQL) ]