Python RunSQL Examples

Example #1

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') ",
    ])
]

Example #2

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),
]

Example #3

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),
]

Example #4

File: 0017_events_dead_letter_queue.py Project: GalDayan/posthog

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),
]

Example #5

File: 0015_materialized_column_comments.py Project: GalDayan/posthog

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))

Example #6

File: 0008_plugin_log_entries.py Project: akbansa/posthog

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),
]

Example #7

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()),
]

Example #8

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

Example #9

File: 0016_collapsing_person_distinct_id.py Project: akbansa/posthog

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

Example #10

File: 0022_person_distinct_id2.py Project: akbansa/posthog

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, ),
]

Example #11

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),
]

Example #12

File: 0009_person_deleted_column.py Project: vishivish18/posthog

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),
]

Example #13

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()),
]

Example #14

from infi.clickhouse_orm import migrations

operations = [migrations.RunSQL("SELECT 1")]

Example #15

from infi.clickhouse_orm import migrations  # type: ignore

from ee.clickhouse.sql.events import EVENTS_TABLE_SQL

operations = [
    migrations.RunSQL(EVENTS_TABLE_SQL),
]

Example #16

File: 0002_events_materialized.py Project: zegl/posthog

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),
]

Example #17

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),
]

Example #18

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()),
]

Example #19

File: 0010_cohortpeople.py Project: samcaspus/posthog

from infi.clickhouse_orm import migrations

from ee.clickhouse.sql.cohort import CREATE_COHORTPEOPLE_TABLE_SQL

operations = [migrations.RunSQL(CREATE_COHORTPEOPLE_TABLE_SQL)]

Example #20

File: 0018_group_analytics_schema.py Project: GalDayan/posthog

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),
]

Example #21

File: 0003_person.py Project: zegl/posthog

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)
]

Example #22

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),
]

Example #23

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
        """
    )
]

Example #24

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())]
    )

Example #25

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),
]

Example #26

File: 0023_dead_letter_queue_tags.py Project: akbansa/posthog

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),
]

Example #27

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),
]

Example #28

File: 0020_session_recording_events_window_id.py Project: GalDayan/posthog

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),
]

Example #29

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),
]

Example #30

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)
]