Creating, deleting, and populating tables for statistics in ClickHouse

In some cases, such as when setting up a replicated cluster, you may need the function to create empty tables with a replicated engine to properly move data from regular tables later on. You can create and delete tables for storing statistics, or populate data (based on triggers in MV) using the aktool utility.

Command reference

aktool utility commands:

Init stat table.

Usage:
  aktool stats init [summary|pixel|engagement|click_map|forms|undelive|mailings|notify|channel|all] [flags]

Flags:
  -h, --help   help for init

Global Flags:
      --config string   config path (default "/opt/akd/config/main.json")
  -o, --output string   file path to output in the result of the command (default "stdout")
      --truncate        whether to truncate the file specified by --output

Prameters of commands

List of parameters (tables) for delete, fill, init commands:

Parameter	Table	Description
all	All tables for storing statistics
summary	summary_report summary_MV_%версия	Summary report
pixel	pixel_report pixelUndefMV_%версия pixelMV_%версия	Pixels
engagement	engagement_report engagement_MV	Audience growth report
click_map	click_map_report click_mapMV	Click map
forms	total_form_report formMV form_pages_view_report form_pages_viewMV group_form_report group_formMV	Forms
undelive	undelive_report undeliveMV	Undelivered report
campaigns	camp_stats	Real-time mailing statistics
notify	notify_sender_stats	Daily and weekly user reports

Create table

Create tables for the summary report:

./aktool stats init summary

Create all necessary tables for storing statistics:

./aktool stats init all

Population table

Populate tables for forms:

./aktool stats fill forms

Populate all necessary tables for storing statistics:

./aktool stats fill all

Delete table

note

To fully delete a replicated table, you need to wait until the table data is removed from ZooKeeper. The waiting time can be configured using the parameter database_atomic_delay_before_drop_table_sec in the ClickHouse server configuration.

Delete tables for undelivered report:

./aktool stats delete undelive

Delete all necessary tables for storing statistics:

./aktool stats delete all