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?

Updates cube funnels to read schema from clickhouse

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

MR acceptance checklist

  • The correct type labels have been applied to this MR.
  • This MR has been made as small as possible, to improve review efficiency and code quality.
  • This MR has been self-reviewed per the code review guidelines.
  • The changes have undergone manual testing and are functioning as intended.
  • This MR has updated the Chart.yaml version number following SemVer versioning practices.
  • This MR documents any breaking changes in the MR description, and the upgrade path has been documented in the first commit as well as in MR description.

How to set up and validate

  1. Create a new funnel or update existing funnel by making an api call to /funnel-schemas endpoint in analytics-manager

    POST http://{analytics-manager-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_#{id}.snowplow_events) as x, windowFunnel(3600)(toDateTime(derived_tstamp), page_urlpath = ''/page1.html'', page_urlpath = ''/page2.html'') as step FROM gitlab_project_#{id}.snowplow_events",
          "state": "created"
        }
      ]
    }

    OR

    Run GDK locally connected with self managed product analytics cluster. Create a new file in any repository called .gitlab/analytics/funnels/completed_purchase.yaml with the following contents. The repository should be onboarded with product analytics beforehands.

    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'
  2. Check clickhouse table gitlab_project_#{id}.funnel_schemas to verify if the funnel got saved

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

    GET /cubejs-api/v1/meta HTTP/1.1
    Host: 
    Content-Type: application/json
    Authorization: JWT encoded cube security header
  4. Seed clickhouse table snowplow_events with some random data

    insert into gitlab_project_#{id}.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);
  5. 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
    Host:
    Content-Type: application/json
    Authorization: JWT encoded cube security header
    
    {
        "query": {
            "measures": [
                "completed_purchase.count"
            ]
        },
        "queryType": "multi"
    }

How to deploy upon merging

Numbered steps to explain how this change needs to be deployed. For instance, if there are any changes that should be made outside of the code changes themselves.

Edited by Surabhi Suman

Merge request reports