`Funnels` package incompatible with ClickHouse

[fuacici]

Problem

When run generate SQL for clickhouse(version 19.16.19.85)， it shows an error：

myfunel.js Related Cube.js schema

const Funnels = require(`Funnels`);

cube(`LoginF`, {
  extends: Funnels.eventFunnel({
    userId: {
      sql: `userId`,
    },
    time: {
      sql: `time_dt`,
    },
    steps: [{
      name: `fetch_step`,
      eventsView: {
        sql: `select * from oneid_ckdb.ppd_monitor_oneidcf_all where serviceName = 'loanhome/userNotLoginConfigService/queryLoginModel' `
      },
    },
    {
      name: `code_step`,
      eventsView: {
        sql: `select * from oneid_ckdb.ppd_monitor_oneidcf_all where serviceName = 'PassportSDK/passportv2-openCodeService/sendCodeApp' `
      },
      // timeToConvert: '1 day'
    }],
  },
  )
});

Related Cube.js generated SQL

SELECT
  toDateTime(
    toStartOfDay(
      toTimeZone(toDateTime(`login_f`.t), 'UTC'),
      'UTC'
    ),
    'UTC'
  ) `login_f__time_day`,
  count(`login_f`.user_id) `login_f__conversions`
FROM
  (
    WITH joined_events AS (
      select
        fetch_step_events.user_id fetch_step_user_id,
        code_step_events.user_id code_step_user_id,
        fetch_step_events.t
      FROM
        (
          select
            userId user_id,
            time_dt t
          from
            (
              select
                *
              from
                oneid_ckdb.ppd_monitor_oneidcf_all
              where
                serviceName = 'loanhome/userNotLoginConfigService/queryLoginModel'
            ) e
        ) fetch_step_events
        LEFT JOIN (
          select
            userId user_id,
            time_dt t
          from
            (
              select
                *
              from
                oneid_ckdb.ppd_monitor_oneidcf_all
              where
                serviceName = 'PassportSDK/passportv2-openCodeService/sendCodeApp'
            ) e
        ) code_step_events ON fetch_step_events.user_id = code_step_events.user_id
        AND code_step_events.t >= fetch_step_events.t
    )
    select
      user_id,
      first_step_user_id,
      step,
      max(t) t
    from
      (
        SELECT
          fetch_step_user_id user_id,
          fetch_step_user_id first_step_user_id,
          t,
          'Fetch Step' step
        FROM
          joined_events
        UNION ALL
        SELECT
          code_step_user_id user_id,
          fetch_step_user_id first_step_user_id,
          t,
          'Code Step' step
        FROM
          joined_events
      ) as event_steps
    GROUP BY
      1,
      2,
      3
  ) AS `login_f`
GROUP BY
  `login_f__time_day`
ORDER BY
  `login_f__time_day` ASC
LIMIT
  10000

[github-actions[bot]]

If you are interested in working on this issue, please leave a comment below and we will be happy to assign the issue to you. If this is the first time you are contributing a Pull Request to Cube.js, please check our contribution guidelines. You can also post any questions while contributing in the #contributors channel in the Cube.js Slack.

[paveltiunov]

@fuacici Clickhouse seems to be not supported by the Funnels module. Feel free to contribute a fix. Thanks!

[stkerr]

I believe part of the issue here is related to this line in the ClickHouse documentation:

If a condition refers columns from different tables, then only the equality operator (=) is supported so far.

Presumably the query generator would need to be updated to respect that restriction.