ClickHouse data collector

What does this MR do and why?

ClickHouse data collector

Introduces feature flag clickhouse_data_collector #420257 (closed)

Screenshots or screen recordings

No user-facing changes

How to set up and validate locally

Make sure you have GitLab Ultimate license on your local.

1. Install and run the ClickHouse server as per these directions !124295 (merged)

• If you do the one-line install rather than installing via apt, you can remove the password from the yml file because the default user it comes with doesn't have a password. There is a way to set up other users or set a password, but for the purposes of testing I don't think it's necessary.

2. Run the ClickHouse client, connecting to the gitlab_clickhouse_test database.
3. Create the events table into the gitlab_clickhouse_test database (run the code as in the db/click_house/main/20230705124511_create_events.sql file in clickhouse client)
4. Create the contribution_analytics_events table - run db/click_house/main/20230724064832_create_contribution_analytics_events.sql file in this MR in clickhouse client
5. Create the materialised view - run db/click_house/main/20230724064918_contribution_analytics_events_materialized_view.sql in this MR in clickhouse client

OR as @ahegyi says: just do #414938 (closed)

6. Find a project with some MR activity (code pushes, etc) or seed some.
7. Fill out the table:

def format_row(event)
  namespace = event.project.try(:project_namespace) || event.group
  path = namespace.traversal_ids.join('/')

  action = Event.actions[event.action]
  [
    event.id,
    "'#{path}/'",
    event.author_id,
    event.target_id,
    "'#{event.target_type}'",
    action,
    event.created_at.to_f,
    event.updated_at.to_f
  ].join(',')
end


values = []
Event.all.each do |event|
  values << "(#{format_row(event)})"
end

insert_query = <<~SQL
INSERT INTO events
(id, path, author_id, target_id, target_type, action, created_at, updated_at)
VALUES
#{values.join(',')}
SQL

ClickHouse::Client.execute(insert_query, :main)

8. Tail logs tail -f log/development.log
9. To get the data:

• PG: make sure Feature.enabled?(:clickhouse_data_collection) is false
• CH: make sure Feature.enabled?(:clickhouse_data_collection) is true

Using the GDK group flightjs:

{
  group(fullPath: "flightjs") {
    contributions(from: "2023-06-01", to: "2023-08-01") {
      nodes {
        user {
          id
        }
        totalEvents
        repoPushed
      }
    }
  }
}

Try with the FF off first, note the result, and then with FF on. The resulting data should be the same. If the FF is off, you'll see a MATERIALIZED VIEW query hit the Postgres DB. If the FF is on, it will be missing.

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

• I have evaluated the MR acceptance checklist for this MR.

Related to #414610 (closed)

ee/lib/gitlab/contribution_analytics/click_house_data_collector.rb

@@ -2,7 +2,10 @@
 
 module Gitlab
   module ContributionAnalytics
-    class ClickHouseDataCollector < PostgresqlDataCollector
+    class ClickHouseDataCollector
+      # Use AR module for preventing SQL injection
+      include ActiveRecord::ConnectionAdapters::Quoting
+
       attr_reader :group, :from, :to
 
       def initialize(group:, from:, to:)
@@ -11,28 +14,40 @@ def initialize(group:, from:, to:)
         @to = to
       end
 
-      def base_query
+      def totals_by_author_target_type_action
         clickhouse_query = <<~CLICKHOUSE
-SELECT count(*) as count_all,
-       "contribution_analytics_events"."author_id" AS events_author_id,
-       "contribution_analytics_events"."target_type" AS events_target_type,
-       "contribution_analytics_events"."action" AS events_action
+SELECT count(*) AS count,
+       "contribution_analytics_events"."author_id" AS author_id,
+       "contribution_analytics_events"."target_type" AS target_type,
+       "contribution_analytics_events"."action" AS action
 FROM (
   SELECT
     id,
-    argMax(author_id, contribution_analytics_events.updated_at) as author_id,
-    argMax(target_type, contribution_analytics_events.updated_at) as target_type,
-    argMax(action, contribution_analytics_events.updated_at) as action
+    argMax(author_id, contribution_analytics_events.updated_at) AS author_id,
+    argMax(target_type, contribution_analytics_events.updated_at) AS target_type,
+    argMax(action, contribution_analytics_events.updated_at) AS action
   FROM contribution_analytics_events
-    WHERE startsWith(path, '24/')
-    AND "contribution_analytics_events"."created_at" >= '2022-05-01'
-    AND "contribution_analytics_events"."created_at" <= '2023-05-07'
+    WHERE startsWith(path, '#{group_path}/')
+    AND "contribution_analytics_events"."created_at" >= '#{format_date(from)}'
+    AND "contribution_analytics_events"."created_at" <= '#{format_date(to)}'
   GROUP BY id
 ) contribution_analytics_events
 GROUP BY "contribution_analytics_events"."action","contribution_analytics_events"."target_type","contribution_analytics_events"."author_id"
         CLICKHOUSE
 
-        ClickHouse::Client.select(clickhouse_query, :main)
+        ClickHouse::Client.select(clickhouse_query, :main).each_with_object({}) do |row, hash|
+          hash[[row['author_id'], row['target_type'], row['action']]] = row['count']
+        end
+      end
+
+      private
+
+      def group_path
+        @group_path ||= quote(group.traversal_ids.join('/'))
+      end
+
+      def format_date(date)
+        quote(date.utc.strftime('%Y-%m-%d'))
       end
     end
   end