Skip to main content

uala-statistics Documentation

Clickhouse​

!!!warning Warning All the following instructions and informations are related to Clickhouse 20.8 (2021) thing could be changed now, since the last release is that. !!!

To compute statistics in real time as fast as possible it has been decided to use Clickhouse a high-performance, column-oriented SQL database management system (DBMS) for online analytical processing (OLAP).

Clickhouse is a great database to perform fast SELECT queries on a huge amount of data, it's fast and it provides a lot of built in functions to create reports and it was already used in the company.

In order to be so efficient Clickhouse has some problems when it comes to manipulate the records: it doesn't have UPDATE or DELETE (at least not for our Engine Tables used) and INSERT is an expensive operation.

INSERT problems​

In Clickhouse inserting new records is an expensive operation in the background a lot of elaborations are computed in order to provide such efficient SELECT queries

!!! From the documentation INSERT performance considerations !!!

The steps used today to "efficiently" insert new records in Clickhouse is:

  • Store all the new records in a S3 file if the table doesn't have a lot of daily new records (product_categories) or store the new records in multiple S3 files divided by Clickhouse partition if the table has a lot of new daily records (appointments and line_items)
  • Use Buffer table when only very few records are added daily (countries).

How to UPDATE old records​

In order to perform an UPDATE operations a new record is added with some keys equal to the old record so that Clickhouse recognize that there is a duplicate record and the DB will automatically remove the oldest version of the record.

For example the table line_times is created with:

ENGINE = ReplacingMergeTree(epoch_updated_at)
ORDER BY (country_code, id)

That means that 2 records are recognized as duplicates if they have the same country_code and id then Clickhouse will use the epoch_updated_at value to knows which one is the most recent.

That procedure is performed automatically by Clickhouse but it's not immediate. To be sure to remove all the duplicates an OPTIMIZE TABLE query is executed.

How to DELETE​

Clickhouse doesn't have a real DELETE (for the Table Engines that we are using) but it's still possible to remove records performing an ALTER TABLE query (that design is to point out that deleting a record is expensive and it shouldn't be done frequently).

ALTER TABLE <table>
ON CLUSTER <cluster>
DELETE WHERE <condition>

### How to perform Migrations

The version of the gem used to integrate Clickhouse with Ruby On Rails supports only not clustered tables. For that reason it's possible to run the migrations normally in the local machine but not in dev staging and prod, since there the tables are clustered.

So in the local environment is possible to run:

rails db:migrate
rails db:schema:load

And those commands will work as for any other Database.

In the other environments to change the tables, create new tables and delete old one requires to:

  1. Write the query in a file in the deployments folder to keep an history of the commands (it's like the migrations folder)
  2. Update statistics_tables_prod.sql (it's like schema.rb)
  3. Access to the workload where the Clickhouse client service is running
  4. Write the Clickhouse queries manually one by one.

!!!Support Data Database

It's a Postgres Database used by uala-statistics to store the "support data" used to compute La Biosthetique's statistics. !!!

Jobs​

To keep in async the data stored in Clickhouse with the data stored in the Main Database the application uala-statistics runs some Jobs:

  1. FeedModels: Those Jobs insert records on the Clickhouse Data Tables (appointments, line_items, venues etc) those records can be new ones or the updated versions of records already present in Clickhouse (it doesn't have an update). !!! The Clickhouse Data Tables are tables used only to populate the Clickhouse Report Tables, they aren't queried directly. !!! The FeedModels Jobs have 3 steps:
    1. They perform an heavy query on Postgres to retrieve all the records retrieving data using multiple LEFT OUTER JOIN and adjusting the data using CASE WHEN
    2. All the data retrieved from Postgres are saved on a S3 file (or if we know that those data are millions we use multiple S3 files one for each Clickhouse partition)
    3. Load in Clickhouse the records in the S3 files, those data can be loaded directly in the Clickhouse Data Tables or in Buffer tables.
  2. RemoveDuplicates: It forces Clickhouse to remove the duplicates from the Clickhouse Data Tables running OPTIMIZE TABLE queries
  3. FeedReports: those jobs repopulate completely the Clickhouse Reports Tables. !!! The Clickhouse Reports Tables are the tables queried to compute the Reports. !!!
  4. StoreRecordsToDelete: The purpose of those jobs is to weekly remove from Clickhouse the records that are hard deleted from the Main Database so that they won't be present in Clickhouse.

To be sure that the Jobs FeedModels, RemoveDuplicates and FeedReports are executed in that specific order they use a State Machine to coordinate, that state machine is identified by date saved in the table ImportProcess, the actual state is saved in Redis and the state machine triggers the next Jobs when it changes state. A state change is performed when all the Jobs from the previous stage are done.

uala-statistics Jobs useful commands​

To debug the uala-statistics Jobs there are some useful commands that can be run in the production console:

  • To retrieve the state and jobs remaining in a specific day

    state = SupportData::ImportProcess.find_by("date = ?", '2022-08-11')
    [state.state, state.current_state_remaining_count]

  • To understand the state and jobs remaining in multiple days

    SupportData::ImportProcess.where("date > '2022-08-11'").order(:date).map{|e| [e.date, e.state, e.current_state_remaining_count]}

  • To execute only the FeedModels Jobs for some days (if data are lost) from any ImportProcess State

    (Date.new(2022, 8, 11)..Date.yesterday).each{ |date| ExecuteImportProcessJob.perform_later(event_name: 'feed_models', date: date) }

  • To execute only FeedReports Jobs and the ImportProcess State machine is in the feed_report state. Example some Jobs failed and didn't retry, maybe because the Pods restarted

    import_process = SupportData::ImportProcess.by_date(date)
    import_process.remove_duplicates!

    In that way the ImportProcess state machine will change the state executing RemoveDuplicates that it's fast and should be always done before feeding the reports table and then FeedReports will be executed like in a normal transition

uala-statistics Jobs useful monitoring websites​

  • Datadog (Error Tracking / Logs) to see if Jobs raised some exceptions
  • Sidekiq page to understand how many Jobs are Failed, still to be executed etc

==- Jobs Known issues

The Jobs have different issues without a clear solution or with a complex and long solution.

  • FeedModels issues:

    1. The queries to SELECT the data from Postgres are too complex, they require too much time and often they dies. That because to populate a CH table it's required to collect data from multiple sources. Those Jobs where implemented in that way because the Clickhouse JOIN was considered slow and it was considered better to move the JOIN operations on Postgres.
    2. There are records updated without a real reason because some tables (Ex Staff Members, Customers) have an updated_at field that change daily so it's hard to filter the records that are really updated and the ones that aren't
    3. INSERT are inefficient on Clickhouse so it's better to insert multiple records in one step, but retrieving multiple records from Postgres is problematic.

  • FeedModels issues:

    1. A known problem (that should be solved) is that the queries performed to populate the Clickhouse Reports Tables could consume too much RAM.

  • StoreRecordsToDelete issues:

    1. For some reason after migrating Clickhouse on the AWS cloud those jobs are way slower.
    2. To detect the Postgres hard deleted records the only way is to detect the IDs that are in Clickhouse and not in Postgres and delete them, but store multiple IDs in memory could consume to much RAM.
    3. A Clickhouse DELETE query is really expensive so is should be performed few times and for as much records as possible.

Possible solutions​

In the discussion https://github.com/uala/uala-backend/discussions/5202 are described some possible solutions to make the Uala Statistics FeedModels Jobs more stable. ==-

Authentication​

The uala-statistics EPs support Multi Venue Reports that means that the a single report can be created using data from multiple venues, if the user can access them.

To authorize and EP call uala-statistics calls an internal EP saved in AUTHENTICATION_URL to check if the User Authentication Token has the ACLs to get the reports for all the requested Venues.

  • If the Authentication Token is invalid the EPs would fail with 401.
  • If the Authentication Token grants access only to a subset of the requested Venues only that subset will be used to compute the reports.

The AUTHENTICATION_URL response for a specific Authentication Token is cached for 1 hour, that means that an old token can be invalid for uala-main EPs but is still valid for uala-statistics.

How to setup the local environment​

To be able to setup uala-statistics locally and run rspec you should run the following commands:

RAILS_ENV=test bundle exec rails db:create:support_data
RAILS_ENV=test bundle exec rails db:schema:load:support_data
RAILS_ENV=test bundle exec rails db:create:brands
RAILS_ENV=test bundle exec rails db:schema:load:brands
docker run -d -p 8123:8123 -p 9000:9000 --name clickhouse-server yandex/clickhouse-server:20.8
docker run --entrypoint '/usr/bin/tail' --name clickhouse-client -d --link clickhouse-server:clickhouse-server yandex/clickhouse-client:20.8 -f /dev/null
RAILS_ENV=test bundle exec rails db:create
RAILS_ENV=test bundle exec rails db:schema:load