def backwards_local(self) -> Sequence[operations.SqlOperation]:
return [
operations.DropTable(
storage_set=StorageSetKey.OUTCOMES,
table_name="outcomes_mv_hourly_local",
),
operations.CreateMaterializedView(
storage_set=StorageSetKey.OUTCOMES,
view_name="outcomes_mv_hourly_local",
destination_table_name="outcomes_hourly_local",
columns=old_materialized_view_columns,
query="""
SELECT
org_id,
project_id,
ifNull(key_id, 0) AS key_id,
toStartOfHour(timestamp) AS timestamp,
outcome,
ifNull(reason, 'none') AS reason,
count() AS times_seen
FROM outcomes_raw_local
GROUP BY org_id, project_id, key_id, timestamp, outcome, reason
""",
),
]
def get_forward_view_migration_polymorphic_table_v3(
source_table_name: str,
table_name: str,
mv_name: str,
aggregation_col_schema: Sequence[Column[Modifiers]],
aggregation_states: str,
metric_type: str,
target_mat_version: int,
appended_where_clause: str = "",
) -> operations.SqlOperation:
aggregated_cols = [
Column("use_case_id", String(Modifiers(low_cardinality=True))),
*COMMON_AGGR_COLUMNS,
*aggregation_col_schema,
]
return operations.CreateMaterializedView(
storage_set=StorageSetKey.METRICS,
view_name=mv_name,
destination_table_name=table_name,
columns=aggregated_cols,
query=MATVIEW_STATEMENT_POLYMORPHIC_TABLE_V3 % {
"metric_type": metric_type,
"raw_table_name": source_table_name,
"aggregation_states": aggregation_states,
"target_mat_version": target_mat_version,
"appended_where_clause": appended_where_clause,
"materialization_version": target_mat_version,
},
)
def get_forward_migrations_local(
source_table_name: str,
table_name: str,
mv_name: str,
aggregation_col_schema: Sequence[Column[Modifiers]],
aggregation_states: str,
) -> Sequence[operations.SqlOperation]:
aggregated_cols = [*COMMON_AGGR_COLUMNS, *aggregation_col_schema]
return [
operations.CreateTable(
storage_set=StorageSetKey.METRICS,
table_name=table_name,
columns=aggregated_cols,
engine=table_engines.AggregatingMergeTree(
storage_set=StorageSetKey.METRICS,
order_by=
"(org_id, project_id, metric_id, granularity, timestamp, tags.key, tags.value)",
partition_by="(retention_days, toMonday(timestamp))",
settings={"index_granularity": "256"},
),
),
operations.AddColumn(
storage_set=StorageSetKey.METRICS,
table_name=table_name,
column=Column(
"_tags_hash",
Array(UInt(64),
Modifiers(materialized=INT_TAGS_HASH_MAP_COLUMN)),
),
after="tags.value",
),
operations.AddIndex(
storage_set=StorageSetKey.METRICS,
table_name=table_name,
index_name="bf_tags_hash",
index_expression="_tags_hash",
index_type="bloom_filter()",
granularity=1,
),
operations.AddIndex(
storage_set=StorageSetKey.METRICS,
table_name=table_name,
index_name="bf_tags_key_hash",
index_expression="tags.key",
index_type="bloom_filter()",
granularity=1,
),
operations.CreateMaterializedView(
storage_set=StorageSetKey.METRICS,
view_name=mv_name,
destination_table_name=table_name,
columns=aggregated_cols,
query=MATVIEW_STATEMENT % {
"raw_table_name": source_table_name,
"aggregation_states": aggregation_states,
},
),
]
def forwards_local(self) -> Sequence[operations.Operation]:
return [
operations.CreateTable(
storage_set=StorageSetKey.SESSIONS,
table_name="sessions_raw_local",
columns=raw_columns,
engine=table_engines.MergeTree(
storage_set=StorageSetKey.SESSIONS,
order_by=
"(org_id, project_id, release, environment, started)",
partition_by="(toMonday(started))",
settings={"index_granularity": "16384"},
),
),
operations.CreateTable(
storage_set=StorageSetKey.SESSIONS,
table_name="sessions_hourly_local",
columns=aggregate_columns,
engine=table_engines.AggregatingMergeTree(
storage_set=StorageSetKey.SESSIONS,
order_by=
"(org_id, project_id, release, environment, started)",
partition_by="(toMonday(started))",
settings={"index_granularity": "256"},
),
),
operations.CreateMaterializedView(
storage_set=StorageSetKey.SESSIONS,
view_name="sessions_hourly_mv_local",
destination_table_name="sessions_hourly_local",
columns=aggregate_columns,
query=f"""
SELECT
org_id,
project_id,
toStartOfHour(started) as started,
release,
environment,
quantilesIfState(0.5, 0.9)(
duration,
duration <> {MAX_UINT32} AND status == 1
) as duration_quantiles,
countIfState(session_id, seq == 0) as sessions,
uniqIfState(distinct_id, distinct_id != '{NIL_UUID}') as users,
countIfState(session_id, status == 2) as sessions_crashed,
countIfState(session_id, status == 3) as sessions_abnormal,
uniqIfState(session_id, errors > 0) as sessions_errored,
uniqIfState(distinct_id, status == 2) as users_crashed,
uniqIfState(distinct_id, status == 3) as users_abnormal,
uniqIfState(distinct_id, errors > 0) as users_errored
FROM
sessions_raw_local
GROUP BY
org_id, project_id, started, release, environment
""",
),
]
def forwards_local(self) -> Sequence[operations.SqlOperation]:
return [
operations.DropTable(
storage_set=StorageSetKey.SESSIONS,
table_name="sessions_hourly_mv_local",
),
operations.CreateMaterializedView(
storage_set=StorageSetKey.SESSIONS,
view_name="sessions_hourly_mv_local",
destination_table_name="sessions_hourly_local",
columns=aggregate_columns,
query=query,
),
]
def forwards_local(self) -> Sequence[operations.Operation]:
return [
operations.CreateTable(
storage_set=StorageSetKey.OUTCOMES,
table_name="outcomes_raw_local",
columns=raw_columns,
engine=table_engines.MergeTree(
storage_set=StorageSetKey.OUTCOMES,
order_by="(org_id, project_id, timestamp)",
partition_by="(toMonday(timestamp))",
settings={"index_granularity": "16384"},
),
),
operations.CreateTable(
storage_set=StorageSetKey.OUTCOMES,
table_name="outcomes_hourly_local",
columns=hourly_columns,
engine=table_engines.SummingMergeTree(
storage_set=StorageSetKey.OUTCOMES,
order_by=
"(org_id, project_id, key_id, outcome, reason, timestamp)",
partition_by="(toMonday(timestamp))",
settings={"index_granularity": "256"},
),
),
operations.CreateMaterializedView(
storage_set=StorageSetKey.OUTCOMES,
view_name="outcomes_mv_hourly_local",
destination_table_name="outcomes_hourly_local",
columns=materialized_view_columns,
query="""
SELECT
org_id,
project_id,
ifNull(key_id, 0) AS key_id,
toStartOfHour(timestamp) AS timestamp,
outcome,
ifNull(reason, 'none') AS reason,
count() AS times_seen
FROM outcomes_raw_local
GROUP BY org_id, project_id, key_id, timestamp, outcome, reason
""",
),
]
def get_forward_view_migration_local_consolidated(
source_table_name: str,
table_name: str,
mv_name: str,
aggregation_col_schema: Sequence[Column[Modifiers]],
aggregation_states: str,
) -> operations.SqlOperation:
aggregated_cols = [*COMMON_AGGR_COLUMNS, *aggregation_col_schema]
return operations.CreateMaterializedView(
storage_set=StorageSetKey.METRICS,
view_name=mv_name,
destination_table_name=table_name,
columns=aggregated_cols,
query=MATVIEW_STATEMENT_CONSOLIDATED % {
"raw_table_name": source_table_name,
"aggregation_states": aggregation_states,
},
)
def forwards_local(self) -> Sequence[operations.SqlOperation]:
return [
operations.DropTable(
storage_set=StorageSetKey.GENERIC_METRICS_SETS,
table_name=self.view_name,
),
operations.CreateMaterializedView(
storage_set=StorageSetKey.GENERIC_METRICS_SETS,
view_name=self.view_name,
columns=self.dest_table_columns,
destination_table_name="generic_metric_sets_local",
query="""
SELECT
use_case_id,
org_id,
project_id,
metric_id,
arrayJoin(granularities) as granularity,
tags.key,
tags.indexed_value,
tags.raw_value,
toDateTime(multiIf(granularity=0,10,granularity=1,60,granularity=2,3600,granularity=3,86400,-1) *
intDiv(toUnixTimestamp(timestamp),
multiIf(granularity=0,10,granularity=1,60,granularity=2,3600,granularity=3,86400,-1))) as timestamp,
retention_days,
uniqCombined64State(arrayJoin(set_values)) as value
FROM generic_metric_sets_raw_local
WHERE materialization_version = 1
AND metric_type = 'set'
GROUP BY
use_case_id,
org_id,
project_id,
metric_id,
tags.key,
tags.indexed_value,
tags.raw_value,
timestamp,
granularity,
retention_days
""",
),
]
def get_forward_view_migration_polymorphic_table(
source_table_name: str,
table_name: str,
mv_name: str,
aggregation_col_schema: Sequence[Column[Modifiers]],
aggregation_states: str,
metric_type: str,
) -> operations.SqlOperation:
aggregated_cols = [*COMMON_AGGR_COLUMNS, *aggregation_col_schema]
return operations.CreateMaterializedView(
storage_set=StorageSetKey.METRICS,
view_name=mv_name,
destination_table_name=table_name,
columns=aggregated_cols,
query=MATVIEW_STATEMENT_POLYMORPHIC_TABLE % {
"metric_type": metric_type,
"raw_table_name": source_table_name,
"aggregation_states": aggregation_states,
},
)
def get_forward_view_migration_local(
source_table_name: str,
table_name: str,
mv_name: str,
aggregation_col_schema: Sequence[Column[Modifiers]],
aggregation_states: str,
granularity: int,
) -> operations.SqlOperation:
aggregated_cols = [*COMMON_AGGR_COLUMNS, *aggregation_col_schema]
assert granularity is not None
return operations.CreateMaterializedView(
storage_set=StorageSetKey.METRICS,
view_name=mv_name,
destination_table_name=table_name,
columns=aggregated_cols,
query=MATVIEW_STATEMENT % {
"raw_table_name": source_table_name,
"aggregation_states": aggregation_states,
"granularity": granularity,
},
)
org_id,
project_id,
toStartOfHour(started) as started,
release,
environment,
quantilesIfState(0.5, 0.9)(
duration,
duration <> {MAX_UINT32} AND status == 1
) as duration_quantiles,
countIfState(session_id, seq == 0) as sessions,
uniqIfState(distinct_id, distinct_id != '{NIL_UUID}') as users,
countIfState(session_id, status == 2) as sessions_crashed,
countIfState(session_id, status == 3) as sessions_abnormal,
uniqIfState(session_id, errors > 0) as sessions_errored,
uniqIfState(distinct_id, status == 2) as users_crashed,
uniqIfState(distinct_id, status == 3) as users_abnormal,
uniqIfState(distinct_id, errors > 0) as users_errored
FROM
sessions_raw_local
GROUP BY
org_id, project_id, started, release, environment
"""
create_matview_v1 = operations.CreateMaterializedView(
storage_set=StorageSetKey.SESSIONS,
view_name="sessions_hourly_mv_local",
destination_table_name="sessions_hourly_local",
columns=aggregate_columns_v1,
query=query_v1,
)