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

@@ -15,25 +15,25 @@ def initialize(group:, from:, to:)
       end
 
       def totals_by_author_target_type_action
-        clickhouse_query = <<~CLICKHOUSE
-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
-  FROM contribution_analytics_events
-    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_query = <<~CH
+          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
+            FROM contribution_analytics_events
+              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"
+        CH
 
         ClickHouse::Client.select(clickhouse_query, :main).each_with_object({}) do |row, hash|
           hash[[row['author_id'], row['target_type'], row['action']]] = row['count']
@@ -43,7 +43,9 @@ def totals_by_author_target_type_action
       private
 
       def group_path
-        @group_path ||= group.traversal_ids.join('/')
+        # trailing slash required to denote end of path because we use startWith
+        # to get self and descendants
+        @group_path ||= quote("#{group.traversal_ids.join('/')}/")
       end
 
       def format_date(date)