Before I go completely crazy, I decided to ask here as well. My jobs have been running fine for months now, and I never had an issue. I have a workbook that uses 4 data sources. These data sources are refreshed every day at 6AM.
The Tableau Server is on Linux.
All of a sudden, one of those data sources would not refresh anymore. I am getting this error:
com.tableausoftware.nativeapi.exceptions.TableauCancelException:
The data source refresh runs for more than 150 min and then dies. Nothing has changed in the database or in the data source (postgres view). The view contains only around 50k rows.
The other data sources just kept refreshing perfectly.
I have tried:
- Restarting the server Republishing the datasources
- Recreating the views in the source database
- Completely deleting my workbook and datasources from the server and republishing everything
- Renaming the source view
Im running out of ideas and the issue still persists with that one data source.
Here is the error Tableau throws:
This job failed on 29 Oct 2021, 02:08 after running for 155.1 min.
Reason: com.tableausoftware.nativeapi.exceptions.TableauCancelException: task canceled. The task was canceled. Tableau could not complete the extract update task because the timeout limit was exceeded.
Possible causes: too large extract data source, slow network connection, insufficient available resources in the external database, insufficient available resources on the server, etc.
I also expanded the max timeout duration before restarting Tableau, but that did not help.
I had a look at the log files but didnt find any starting point that might help me. Is there any log file I might upload here that could help?
I added a limit 1 in the end of my source view, and that did work. The refresh was sucessfull! Nevertheless, my view only contains around 50k rows, Tableau should have no issues refreshing it.
Here's the view I'm using:
CREATE OR REPLACE VIEW public.v_event_executed_methods
AS WITH wf AS (
SELECT notes.piid,
notes.wfkommentar,
notes.wfkommentaranonym,
pi.processid AS wfprocessid,
pi.creationdate AS wfcreationdate,
pi.finishdate AS wfgeschlossen,
pi.facilityid,
pi.deviceid,
pi.description,
pi.creationdate,
df.facilityname,
df.tenantname,
dd.name AS devicename,
pi.createdby,
pi.closedby
FROM ( SELECT process_notes.piid,
string_agg((((process_notes.creationdate || ':'::text) || process_notes.username::text) || ':'::text) || process_notes.content::text, ''::text) AS wfkommentar,
string_agg((process_notes.creationdate || ':'::text) || process_notes.content::text, '
'::text) AS wfkommentaranonym
FROM process_notes
GROUP BY process_notes.piid) notes
RIGHT JOIN process_information pi ON pi.piid = notes.piid
JOIN dimfacility df ON pi.facilityid = df.facilityid AND df.scd_active = true
LEFT JOIN dimdevice dd ON pi.deviceid = dd.deviceid AND dd.scd_active = true
), ccnotes AS (
SELECT hen.eventresultid,
string_agg((((hen.date || ':'::text) || ((( SELECT du.logonname
FROM dimuser du
WHERE hen.userid = du.userid
LIMIT 1))::text)) || ':'::text) || hen.note::text, '
'::text) AS cckommentar,
string_agg((hen.date || ':'::text) || hen.note::text, '
'::text) AS cckommentaranonym
FROM hist_event_note hen
GROUP BY hen.eventresultid
)
SELECT COALESCE(cc.tenantname, wf.tenantname) AS tenantname,
COALESCE(cc.facilityname, wf.facilityname) AS facilityname,
COALESCE(cc.devicename, wf.devicename) AS devicename,
cc.id,
cc.eventtype,
to_char(cc.incomingdate, 'YYYY-MM-DD HH24:MI:SS'::text)::timestamp without time zone AS incomingdate,
to_char(cc.occurrencedate, 'YYYY-MM-DD HH24:MI:SS'::text)::timestamp without time zone AS occurrencedate,
to_char(cc.closingdate, 'YYYY-MM-DD HH24:MI:SS'::text)::timestamp without time zone AS closingdate,
cc.level,
cc.severity,
cc.closingstatus,
cc.eventmethodkey,
cc.nativeprocessid,
cc.grund,
COALESCE(wf.description, cc.beschreibung)::character varying(1600) AS beschreibung,
cc.logonname,
cc.executiondate,
cc.deviceid,
cc.facilityid,
cc.executionsucess,
cc.methodkey,
cc.methodexecutionid,
cc.type,
CASE
WHEN cc.cckommentar IS NULL THEN ''::text
ELSE 'CC: '::text || cc.cckommentar
END AS cckommentar,
wf.piid,
CASE
WHEN wf.wfkommentar IS NULL THEN ''::text
ELSE 'WF: '::text || wf.wfkommentar
END AS wfkommentar,
wf.wfprocessid,
wf.wfcreationdate,
wf.wfgeschlossen,
cc.createdby AS cccreatedby,
cc.closedby AS cclosedby,
wf.createdby AS wfcreatedby,
wf.closedby AS wfclosedby,
to_char(cc.firstowningdate, 'YYYY-MM-DD HH24:MI:SS'::text)::timestamp without time zone AS firstowningdate,
to_char(COALESCE(cc.occurrencedate, wf.creationdate), 'YYYY-MM-DD HH24:MI:SS'::text)::timestamp without time zone AS aufgetreten,
COALESCE(wf.wfprocessid, cc.id::character varying(100)) AS ticketnr,
to_char(COALESCE(cc.firstowningdate, wf.creationdate, cc.incomingdate), 'YYYY-MM-DD HH24:MI:SS'::text)::timestamp without time zone AS angenommen,
cc.parameter,
CASE
WHEN wf.wfkommentaranonym IS NULL THEN ''::text
ELSE 'WF: '::text || wf.wfkommentaranonym
END AS wfkommentaranonym,
CASE
WHEN cc.cckommentaranonym IS NULL THEN ''::text
ELSE 'CC: '::text || cc.cckommentaranonym
END AS cckommentaranonym,
CASE
WHEN cc.nativeprocessid IS NULL AND wf.wfprocessid IS NULL THEN 'Ereignis'::text
WHEN cc.id IS NULL THEN 'Servicefall'::text
ELSE 'Event u. Servicefall'::text
END AS servicefallevent
FROM ( SELECT ve.id,
ve.eventtype,
ve.incomingdate,
ve.occurrencedate,
ve.closingdate,
ve.level,
ve.severity,
ve.closingstatus,
ve.eventmethodkey,
ve.devicename,
ve.tenantname,
ve.facilityname,
ve.nativeprocessid,
ve.grund,
ve.beschreibung,
vme.logonname,
vme.executiondate,
vme.deviceid,
vme.facilityid,
vme.executionsucess,
vme.methodkey,
vme.methodexecutionid,
vme.type,
cn.cckommentar,
ve.closedby,
ve.createdby,
ve.firstowningdate,
vme.parameter,
cn.cckommentaranonym
FROM v_event ve
LEFT JOIN v_method_executions vme ON ve.id = vme.eventid
LEFT JOIN ccnotes cn ON cn.eventresultid = ve.id) cc
FULL JOIN wf ON wf.wfprocessid::text = cc.nativeprocessid::text
WHERE date_part('year'::text, cc.occurrencedate) >= date_part('year'::text, CURRENT_DATE - '1 year'::interval);
Here is an EXPLAIN of statement of the View:
Nested Loop Left Join (cost=281.82..2216.11 rows=1 width=3825)
Join Filter: ((pi.processid)::text = (her.nativeprocessid)::text)
-> Nested Loop Left Join (cost=246.67..2101.28 rows=1 width=2313)
Join Filter: (hen.eventresultid = her.id)
-> Nested Loop Left Join (cost=230.81..385.99 rows=1 width=2249)
Join Filter: ((NULL::bigint) = hist_method_execution_parameters.methodexecutionid)
-> Nested Loop Left Join (cost=219.12..374.09 rows=1 width=2217)
Join Filter: (herp.eventresultid = her.id)
-> Nested Loop Left Join (cost=219.12..363.20 rows=1 width=1701)
Join Filter: (dm.methodid = her.methodid)
-> Nested Loop Left Join (cost=219.12..350.28 rows=1 width=1571)
Join Filter: (dd.deviceid = her.deviceid)
-> Nested Loop Left Join (cost=219.12..338.65 rows=1 width=1401)
Join Filter: (df.facilityid = her.facilityid)
-> Nested Loop Left Join (cost=219.12..258.34 rows=1 width=1379)
Join Filter: (her.id = ha.eventid)
-> Seq Scan on hist_event_results her (cost=0.00..37.20 rows=1 width=1038)
Filter: ((((eventtype <> 3) AND (firstownerid IS NOT NULL)) OR (eventtype = 3)) AND (date_part('year'::text, occurrencedate) >= date_part('year'::text, (CURRENT_DATE - '1 year'::interval))))
SubPlan 1
-> Limit (cost=0.00..13.00 rows=1 width=138)
-> Seq Scan on dimuser du_1 (cost=0.00..13.00 rows=1 width=138)
Filter: (COALESCE(her.firstownerid, her.creatorid) = userid)
SubPlan 2
-> Limit (cost=0.00..14.20 rows=1 width=138)
-> Seq Scan on dimuser du_2 (cost=0.00..14.20 rows=1 width=138)
Filter: ((her.closingreference)::text = ((userid)::character varying)::text)
-> HashAggregate (cost=219.12..219.74 rows=62 width=865)
Group Key: ha.eventid, du.logonname, ha.executiondate, ha.deviceid, ha.facilityid, ha.success, dm_1.methodkey, (NULL::bigint), ('manuell'::text), df_2.facilityname
-> Append (cost=36.06..217.57 rows=62 width=865)
-> Hash Join (cost=36.06..114.09 rows=12 width=374)
Hash Cond: (df_2.facilityid = ha.facilityid)
-> Seq Scan on dimfacility df_2 (cost=0.00..76.88 rows=275 width=33)
Filter: scd_active
-> Hash (cost=35.91..35.91 rows=12 width=309)
-> Hash Join (cost=22.94..35.91 rows=12 width=309)
Hash Cond: (du.userid = ha.userid)
-> Seq Scan on dimuser du (cost=0.00..12.40 rows=120 width=142)
Filter: scd_active
-> Hash (cost=22.69..22.69 rows=20 width=179)
-> Hash Right Join (cost=10.45..22.69 rows=20 width=179)
Hash Cond: (dm_1.methodid = ha.methodid)
-> Seq Scan on dimmethod dm_1 (cost=0.00..11.80 rows=90 width=146)
Filter: scd_active
-> Hash (cost=10.20..10.20 rows=20 width=49)
-> Seq Scan on hist_audit ha (cost=0.00..10.20 rows=20 width=49)
Filter: ((methodid IS NOT NULL) AND (eventid IS NOT NULL))
-> Hash Right Join (cost=24.14..102.55 rows=50 width=374)
Hash Cond: (df_3.facilityid = hme.facilityid)
-> Seq Scan on dimfacility df_3 (cost=0.00..76.88 rows=275 width=33)
Filter: scd_active
-> Hash (cost=23.51..23.51 rows=50 width=317)
-> Hash Right Join (cost=11.13..23.51 rows=50 width=317)
Hash Cond: (dm_2.methodid = hme.methodid)
-> Seq Scan on dimmethod dm_2 (cost=0.00..11.80 rows=90 width=146)
Filter: scd_active
-> Hash (cost=10.50..10.50 rows=50 width=187)
-> Seq Scan on hist_method_executions hme (cost=0.00..10.50 rows=50 width=187)
Filter: (result_id IS NOT NULL)
-> Seq Scan on dimfacility df (cost=0.00..76.88 rows=275 width=38)
Filter: scd_active
-> Seq Scan on dimdevice dd (cost=0.00..11.00 rows=50 width=186)
Filter: scd_active
-> Seq Scan on dimmethod dm (cost=0.00..11.80 rows=90 width=146)
Filter: scd_active
-> Seq Scan on hist_event_result_parameters herp (cost=0.00..10.88 rows=1 width=524)
Filter: ((key)::text = 'REASON'::text)
-> GroupAggregate (cost=11.69..11.81 rows=4 width=40)
Group Key: hist_method_execution_parameters.methodexecutionid
-> Sort (cost=11.69..11.70 rows=4 width=662)
Sort Key: hist_method_execution_parameters.methodexecutionid
-> Seq Scan on hist_method_execution_parameters (cost=0.00..11.65 rows=4 width=662)
Filter: ((key)::text = ANY ('{METHOD_COMMENT,LEVEL,REASON,PRICE}'::text[]))
-> GroupAggregate (cost=15.86..1712.36 rows=130 width=72)
Group Key: hen.eventresultid
-> Sort (cost=15.86..16.19 rows=130 width=540)
Sort Key: hen.eventresultid
-> Seq Scan on hist_event_note hen (cost=0.00..11.30 rows=130 width=540)
SubPlan 3
-> Limit (cost=0.00..13.00 rows=1 width=138)
-> Seq Scan on dimuser du_3 (cost=0.00..13.00 rows=1 width=138)
Filter: (hen.userid = userid)
-> Hash Left Join (cost=35.15..114.14 rows=50 width=1306)
Hash Cond: (pi.deviceid = dd_1.deviceid)
-> Hash Left Join (cost=23.53..102.08 rows=50 width=1136)
Hash Cond: (pi.piid = process_notes.piid)
-> Hash Join (cost=11.13..89.54 rows=50 width=1072)
Hash Cond: (df_1.facilityid = pi.facilityid)
-> Seq Scan on dimfacility df_1 (cost=0.00..76.88 rows=275 width=38)
Filter: scd_active
-> Hash (cost=10.50..10.50 rows=50 width=1050)
-> Seq Scan on process_information pi (cost=0.00..10.50 rows=50 width=1050)
-> Hash (cost=12.03..12.03 rows=30 width=72)
-> HashAggregate (cost=11.28..11.73 rows=30 width=72)
Group Key: process_notes.piid
-> Seq Scan on process_notes (cost=0.00..10.30 rows=30 width=710)
-> Hash (cost=11.00..11.00 rows=50 width=186)
-> Seq Scan on dimdevice dd_1 (cost=0.00..11.00 rows=50 width=186)
Filter: scd_active
I have added postgres as tag, since the cause might be there. What I do not understand is that "bigger" data extracts still refresh normally...
Thanks!
