cube-js / cube-js/cube

`Funnels` package incompatible with ClickHouse

Open
#1,433 3 comments 0 reactions 0 assignees View on GitHub
data modeling driver:clickhouse help wanted
Dominant language
Rust
Stars
20.8k
Forks
2.1k
Avg merge
1d 2h
Merged PRs (30d)
181

Description

**Problem**

When run generate SQL for clickhouse(version 19.16.19.85), it shows an error:
![image](https://user-images.githubusercontent.com/258202/100057614-cadbb080-2e62-11eb-8f4a-6426b27eafac.png)

myfunel.js
**Related Cube.js schema**
```javascript
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**

```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
```

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.