looker is throwing the sql syntax error in pre-build ‘Database Performance’ ,’Instance Performance’ Dashboards in the System Activity page.
For Ex: Looker is using Pre-build views ‘PDT Builds’ and ‘PDT Event Log’ in building the ‘Database performance dashboard’. ‘PDT Event Log’ is the Direct Log table where looker used to create in underlying MYSQL database and hence no issue with this explore, but ‘PDT Builds’ is the derived view(Explore) using the CTE ,where it is throwing the Syntax Error.
Query generated by Looker is:
WITH pdt_builds AS (SELECT
pdt_event_log.tid AS `tid`,
pdt_event_log.connection AS `connection`,
pdt_event_log.model_name AS `model_name`,
pdt_event_log.view_name AS `view_name`,
MIN(
pdt_event_log.AT
) AS `transaction_start`,
MAX(
pdt_event_log.AT
) AS `transaction_end`,
SUBSTR(MAX(CASE
WHEN pdt_event_log.action LIKE 'create begin' THEN '00-waiting dependencies'
WHEN pdt_event_log.action LIKE 'create ready' THEN '01-in process'
WHEN pdt_event_log.action LIKE 'create%complete' THEN '03-done'
WHEN pdt_event_log.action LIKE 'create%error' THEN CONCAT('03-', pdt_event_log.action )
ELSE CONCAT('02-', pdt_event_log.action ) -- unhandled
END ), 4) AS `status`,
MAX(
CASE
WHEN pdt_event_log.action = 'create begin' THEN COALESCE( pdt_event_log.action_data , "Unknown")
ELSE NULL
END
) AS `context`,
AS `action_list`
FROM pdt_event_log
WHERE (pdt_event_log.action) LIKE 'create%'
GROUP BY
1,
2,
3,
4)
Appreciated the Help….