Skip to content

Updates cube funnels to read schema from clickhouse

Surabhi Suman requested to merge 438036-update-cube-funnels into main

What does this MR do and why?

This updates Cube to use the Clickhouse stored funnel configurations rather than trying to retrieve them via the GitLab API (that had a downside of not being auto refreshed)

Screenshots or screen recordings

Screenshots are required for UI changes, and strongly recommended for all other merge requests.

Before After

How to set up and validate locally

  1. Update cube schema in docker

  2. Create a new funnel or update existing funnel by making an api call to /funnel-schemas endpoint in configurator

    POST http://{analytics-configurator-endpoint}/funnel-schemas
Authorization: Basic [AUTH_USERNAME] [AUTH_PASSWORD]
Content-Type: application/json

{
  "project_ids": ["gitlab_project_20001"],
  "funnels": [
    {
      "name": "completed_purchase",
      "schema": "SELECT (SELECT max(derived_tstamp) FROM gitlab_project_20001.snowplow_events) as x, windowFunnel(3600)(toDateTime(derived_tstamp), page_urlpath = ''/page1.html'', page_urlpath = ''/page2.html'') as step FROM gitlab_project_20001.snowplow_events",
      "state": "created"
    }
  ]
}

    OR

    Pull from gitlab-org/gitlab!150292 (merged) and run GDK locally. Create a new file in any repository called .gitlab/analytics/funnels/completed_purchase.yaml with the following contents.

    name: Completed Purchase
seconds_to_convert: 3600
steps:
  - name: view_page_1
    target: '/page1.html'
    action: 'pageview'
  - name: view_page_2
    target: '/page2.html'
    action: 'pageview'

  3. Check clickhouse table gitlab_project_#{id}.funnel_schemas to verify if the funnel got saved

  4. Make an API call to cubejs /v1/meta endpoint to see the cube schema defined. It shouls have a cube schema definition completed_purchase

  5. Seed clickhouse table snowplow_events with some random data

    insert into gitlab_project_20001.snowplow_events (derived_tstamp, event, user_id, user_id_type, page_urlpath)
values ('2023-02-07 10:44:10','page_view', '1', 'identify', '/page1.html'),
       ('2023-02-08 10:44:12','page_view', '1', 'identify', '/page2.html'),
       ('2023-02-10 10:44:22','page_view', generateUUIDv4(), 'cookie', null),
       ('2023-02-11 10:44:22','page_view', generateUUIDv4(), 'cookie', null);

  6. Make cube's REST api call to /v1/load endpoint. It should return a 200 OK response

    POST /cubejs-api/v1/load HTTP/1.1
Content-Type: application/json
Authorization: JWT encoded cube security header

{
    "query": {
        "measures": [
            "completed_purchase.count"
        ]
    },
    "queryType": "multi"
}

MR acceptance checklist

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

Relates to gitlab-org/gitlab#438036 (closed)

Edited by Surabhi Suman

Merge request reports