Initialize the Block with Moose CLI

In your terminal, run:

npx moose-cli block init TopProgrammingLanguages

moose-cli block init TopProgrammingLanguages

Inspect the Generated File

Open TopProgrammingLanguages.tsTopProgrammingLanguages.py. You should see:

import { Blocks } from "@514labs/moose-lib";
 
export default {
  setup: [
    // Array of SQL statements for setting up database resources
  ],
  teardown: [
    // Array of SQL statements for tearing down resources
  ],
} as Blocks;

from moose_lib import (
    AggregationCreateOptions,
    AggregationDropOptions,
    Blocks,
    ClickHouseEngines,
    TableCreateOptions,
    create_aggregation,
    drop_aggregation,
)
 
teardown_queries = []
 
setup_queries = []
 
block = Blocks(teardown=teardown_queries, setup=setup_queries)

Implement the Materialized View

We'll create a materialized view that aggregates programming language data from StargazerProjectInfo_0_0. This view will automatically update as new data arrives, providing real-time insights into the most popular programming languages among your stargazers.

Here's how to implement it:

import {
  Blocks,
  createAggregation,
  dropAggregation,
  ClickHouseEngines,
} from "@514labs/moose-lib";
 
const MV_NAME = "TopProgrammingLanguages_MV";
const TABLE_NAME = "TopProgrammingLanguages";
 
const createTableOptions = {
  name: TABLE_NAME,
  columns: {
    language: "String",
    total_projects: "AggregateFunction(count, UInt64)",
    total_repo_size_kb: "AggregateFunction(sum, Float64)",
    average_repo_size_kb: "AggregateFunction(avg, Float64)",
  },
  engine: ClickHouseEngines.AggregatingMergeTree,
  orderBy: "language",
};
 
const selectQuery = `
SELECT
  language,
  countState(*) AS total_projects,
  sumState(repoSizeKb) AS total_repo_size_kb,
  avgState(repoSizeKb) AS average_repo_size_kb
FROM StargazerProjectInfo_0_0
GROUP BY language
`;
 
export default {
  teardown: [...dropAggregation({ viewName: MV_NAME, tableName: TABLE_NAME })],
  setup: [
    ...createAggregation({
      materializedViewName: MV_NAME,
      tableCreateOptions: createTableOptions,
      select: selectQuery,
    }),
  ],
} as Blocks;

from moose_lib import (
    AggregationCreateOptions,
    AggregationDropOptions,
    Blocks,
    ClickHouseEngines,
    TableCreateOptions,
    create_aggregation,
    drop_aggregation,
)
 
 
mv_name = "TopProgrammingLanguagesMV"
table_name = "TopProgrammingLanguages"
 
create_table_options = TableCreateOptions(
    name=table_name,
    columns={
        "language": "String",
        "total_projects": "AggregateFunction(count, Int64)",
        "total_repo_size_kb": "AggregateFunction(sum, Int64)",
        "avg_repo_size_kb": "AggregateFunction(avg, Int64)"
    },
    engine=ClickHouseEngines.AggregatingMergeTree,
    order_by="language",
)
 
select_query = f'''
SELECT
    language,
    countState(*) AS total_projects,
    sumState(repo_size_kb) AS total_repo_size_kb,
    avgState(repo_size_kb) AS avg_repo_size_kb
FROM
    StargazerRepositoryInfo_0_0
GROUP BY
    language
'''
 
teardown_queries = drop_aggregation(AggregationDropOptions(view_name=mv_name, table_name=table_name))
 
setup_queries = create_aggregation(AggregationCreateOptions(
    materialized_view_name=mv_name,
    select=select_query,
    table_create_options=create_table_options,
))
 
block = Blocks(teardown=teardown_queries, setup=setup_queries)

Explanation:

• Define the Aggregated Table: We specify createTableOptionscreate_table_options for the TopProgrammingLanguages table, which will store aggregated data such as the total number of projects and total repository size per programming language.

• Write the Aggregation Query: The selectQueryselect_query aggregates data from StargazerProjectInfo_0_0, grouping by language and calculating counts and sums.

• Set Up and Tear Down: In the exported Blocks objectBlocks() instance, we use createAggregationcreate_aggregation in setup to establish the materialized view and table. The teardown uses dropAggregationdrop_aggregation to remove them when they are no longer needed.

• AggregatingMergeTree Engine: We use this engine for the table to optimize aggregation operations. It maintains and updates aggregation states efficiently as new data is inserted.

Save and Apply the Changes

Save TopProgrammingLanguages.tsTopProgrammingLanguages.py. Moose will automatically apply the changes, creating the new table and materialized view in your database.

Verify the Materialized View

Open your ClickHouse database explorer and check that the TopProgrammingLanguages table and TopProgrammingLanguages_MV materialized view have been created. Query the TopProgrammingLanguages table to see the aggregated data:

SELECT
  language,
  countMerge(total_projects) AS total_projects,
  sumMerge(total_repo_size_kb) AS total_repo_size_kb,
  avgMerge(average_repo_size_kb) AS average_repo_size_kb
FROM TopProgrammingLanguages
ORDER BY total_projects DESC