# Catalog query timing out **URL:** \ **Category:** Uncategorized\ **Created:** [January 17, 2018, 5:47am UTC](https://community.dremio.com/t/catalog-query-timing-out/651 "2018-01-17T05:47:14Z")\ **Posts on this page:** 17\ **Page:** 1
**Author:** ![rwidjaja](https://avatars.discourse-cdn.com/v4/letter/r/a6a055/32.png) [@rwidjaja](https://community.dremio.com/u/rwidjaja)\ **Post date:** [January 17, 2018, 5:47am UTC](https://community.dremio.com/t/catalog-query-timing-out/651/1 "2018-01-17T05:47:14Z")
it takes a long time for tableau to refresh metadata and sometimes it failed to refresh because it exceeded 60 seconds of planning time. the cluster is sized with 10G of ram for the JVM small data set sometimes it ran out of memory during connection/refresh the connection from tableau. ---
**Author:** ![rwidjaja](https://avatars.discourse-cdn.com/v4/letter/r/a6a055/32.png) [@rwidjaja](https://community.dremio.com/u/rwidjaja)\ **Post date:** [January 17, 2018, 5:47am UTC](https://community.dremio.com/t/catalog-query-timing-out/651/2 "2018-01-17T05:47:46Z")
[cc24a193-b890-46d3-96c6-b782d15d0807.zip](https://us1.discourse-cdn.com/flex020/uploads/dremio/original/1X/9a85bc0a4b8073445c50949512ab262dfeca9e8e.zip) (1.6 KB) ---
**Author:** ![laurent](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/laurent/32/16_2.png) [@laurent](https://community.dremio.com/u/laurent)\ **Post date:** [January 17, 2018, 5:09pm UTC](https://community.dremio.com/t/catalog-query-timing-out/651/3 "2018-01-17T17:09:52Z")
We are aware that in some situations, metadata queries from Tableau take a very long time and we are working on optimizing this. However people usually don’t observe memory issues for these requests. Would you mind sharing the content of server.log, server.out and server.gc (if present)? Also if you can give some details about your HDFS and Hive sources (how many datasets, average size, average number of columns and average number of partitions), it would also help better diagnose the issue. ---
**Author:** ![rwidjaja](https://avatars.discourse-cdn.com/v4/letter/r/a6a055/32.png) [@rwidjaja](https://community.dremio.com/u/rwidjaja)\ **Post date:** [January 17, 2018, 10:38pm UTC](https://community.dremio.com/t/catalog-query-timing-out/651/4 "2018-01-17T22:38:44Z")
Hi Laurent the environment is quite simple HDP 2.6 running on aws 16 core 64G of ram data set, \< 100Mb of total data size, 4 datasets, (Customer, Product, Date and Clicks) I created a mixed of 3 HDFS source, 1 Hive (fact -\> Clickstream) single file The Dremio is on 3 cluster 1 master/ Coordinator + 2 Executor (40G each) ---
**Author:** ![rwidjaja](https://avatars.discourse-cdn.com/v4/letter/r/a6a055/32.png) [@rwidjaja](https://community.dremio.com/u/rwidjaja)\ **Post date:** [January 17, 2018, 10:44pm UTC](https://community.dremio.com/t/catalog-query-timing-out/651/5 "2018-01-17T22:44:02Z")
[dremio.tar.zip](https://us1.discourse-cdn.com/flex020/uploads/dremio/original/1X/5afd9d00ba7b11d8dd676c6de27212fa63ab7d80.zip) (233.7 KB) the file is in tar+gzip format ---
**Author:** ![laurent](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/laurent/32/16_2.png) [@laurent](https://community.dremio.com/u/laurent)\ **Post date:** [January 17, 2018, 11:31pm UTC](https://community.dremio.com/t/catalog-query-timing-out/651/6 "2018-01-17T23:31:11Z")
Looking at the `server.log`/`queries.json` files, we are able to observe the metadata queries seem to complete normally. What I can obvserve however is some cases of very long (\>60s) planning for some queries involving `Workspace.Clickstream_Analytic` dataset. As the query doesn’t seem complex, I wonder if you can share the dataset definition, so we can try to reproduce the issue in-house and possibly come up with a fix. ---
**Author:** ![rwidjaja](https://avatars.discourse-cdn.com/v4/letter/r/a6a055/32.png) [@rwidjaja](https://community.dremio.com/u/rwidjaja)\ **Post date:** [January 18, 2018, 12:09am UTC](https://community.dremio.com/t/catalog-query-timing-out/651/7 "2018-01-18T00:09:37Z")
certainly, I have the data file too if you want. its about 20Mb total, not sure if I can upload that size. but here is the ddl [omniture.tar.zip](https://us1.discourse-cdn.com/flex020/uploads/dremio/original/1X/f8ed453273a2b3e331584f27d2c14fc5404fd4e1.zip) (1.4 KB) ---
**Author:** ![laurent](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/laurent/32/16_2.png) [@laurent](https://community.dremio.com/u/laurent)\ **Post date:** [January 18, 2018, 1:11am UTC](https://community.dremio.com/t/catalog-query-timing-out/651/8 "2018-01-18T01:11:14Z")
Thanks for the DDL ! As for `Workspace.Clickstream_Analytic`, can you just confirm if the dataset is a virtual dataset in the `Workspace` space (and what the SQL query associated would be), or if this is just one of the datasets you gave the DDL? ---
**Author:** ![rwidjaja](https://avatars.discourse-cdn.com/v4/letter/r/a6a055/32.png) [@rwidjaja](https://community.dremio.com/u/rwidjaja)\ **Post date:** [January 18, 2018, 6:09am UTC](https://community.dremio.com/t/catalog-query-timing-out/651/9 "2018-01-18T06:09:06Z")
That is correct. Clickstream\_Analytics is a join between all those fact + dims ---
**Author:** ![laurent](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/laurent/32/16_2.png) [@laurent](https://community.dremio.com/u/laurent)\ **Post date:** [January 18, 2018, 5:06pm UTC](https://community.dremio.com/t/catalog-query-timing-out/651/10 "2018-01-18T17:06:41Z")
We would need the SQL query which is defining this dataset if possible, as it is most likely causing the issue when planning the final query. ---
**Author:** ![rwidjaja](https://avatars.discourse-cdn.com/v4/letter/r/a6a055/32.png) [@rwidjaja](https://community.dremio.com/u/rwidjaja)\ **Post date:** [January 18, 2018, 6:03pm UTC](https://community.dremio.com/t/catalog-query-timing-out/651/11 "2018-01-18T18:03:53Z")
SELECT ipaddress, client\_lang, CASE WHEN length(substr(client\_lang, 1, 5)) \> 0 THEN substr(client\_lang, 1, 5) ELSE NULL END AS “Language”, isp, user\_agent, http\_response, city, country, state, local\_radio, local\_tv, Product\_Line, Category, Customer\_ID, Birth\_Date, Gender, FullDateAlternateKey, daynumberofmonth, nested\_0.“year” AS “year”, Count\_cookieid, Sum\_pageview, Sum\_timespent, Count\_txn\_id FROM ( SELECT nested\_2.ipaddress AS ipaddress, nested\_2.client\_lang AS client\_lang, nested\_2.isp AS isp, nested\_2.user\_agent AS user\_agent, nested\_2.http\_response AS http\_response, nested\_2.city AS city, nested\_2.country AS country, nested\_2.state AS state, nested\_2.local\_radio AS local\_radio, nested\_2.local\_tv AS local\_tv, nested\_2.Product\_Line AS Product\_Line, nested\_2.Category AS Category, nested\_2.Customer\_ID AS Customer\_ID, nested\_2.Birth\_Date AS Birth\_Date, nested\_2.Gender AS Gender, join\_CalendarDate.FullDateAlternateKey AS FullDateAlternateKey, join\_CalendarDate.daynumberofmonth AS daynumberofmonth, join\_CalendarDate.“year” AS “year”, count(distinct nested\_2.cookieid) AS Count\_cookieid, SUM(nested\_2.pageview) AS Sum\_pageview, SUM(nested\_2.timespent) AS Sum\_timespent, COUNT(nested\_2.txn\_id) AS Count\_txn\_id FROM ( SELECT nested\_1.txn\_id AS txn\_id, nested\_1.txn\_time AS txn\_time, CASE WHEN length(substr(nested\_1.txn\_time, 1, 10)) \> 0 THEN substr(nested\_1.txn\_time, 1, 10) ELSE NULL END AS DateKey, nested\_1.ipaddress AS ipaddress, nested\_1.pageview AS pageview, nested\_1.cookieid AS cookieid, nested\_1.client\_lang AS client\_lang, nested\_1.res\_vert AS res\_vert, nested\_1.res\_horz AS res\_horz, nested\_1.http\_response AS http\_response, nested\_1.isp AS isp, nested\_1.user\_agent AS user\_agent, nested\_1.timespent AS timespent, nested\_1.city AS city, nested\_1.country AS country, nested\_1.state AS state, nested\_1.local\_radio AS local\_radio, nested\_1.local\_tv AS local\_tv, nested\_1.url AS url, nested\_1.Product\_Line AS Product\_Line, nested\_1.Category AS Category, nested\_1.Customer\_ID AS Customer\_ID, join\_Customer.B AS Birth\_Date, join\_Customer.C AS Gender FROM ( SELECT nested\_0.txn\_id AS txn\_id, nested\_0.txn\_time AS txn\_time, nested\_0.ipaddress AS ipaddress, nested\_0.pageview AS pageview, nested\_0.cookieid AS cookieid, nested\_0.Customer\_ID AS Customer\_ID, nested\_0.client\_lang AS client\_lang, nested\_0.res\_vert AS res\_vert, nested\_0.res\_horz AS res\_horz, nested\_0.http\_response AS http\_response, nested\_0.isp AS isp, nested\_0.user\_agent AS user\_agent, nested\_0.timespent AS timespent, nested\_0.city AS city, nested\_0.country AS country, nested\_0.state AS state, nested\_0.local\_radio AS local\_radio, nested\_0.local\_tv AS local\_tv, nested\_0.url AS url, extract\_pattern(nested\_0.url, ‘\w+’, 4, ‘INDEX’) AS Product\_Line, join\_Product.B AS Category FROM ( SELECT txn\_id, txn\_time, ipaddress, pageview, url, cookieid, CASE WHEN length(substr(cookieid, 2, 36)) \> 0 THEN substr(cookieid, 2, 36) ELSE NULL END AS Customer\_ID, client\_lang, res\_vert, res\_horz, http\_response, isp, user\_agent, timespent, city, country, state, local\_radio, local\_tv FROM Workspace.Clickstream ) nested\_0 INNER JOIN Workspace.Product AS join\_Product ON nested\_0.url = join\_Product.A ) nested\_1 INNER JOIN Workspace.Customer AS join\_Customer ON nested\_1.Customer\_ID = join\_Customer.A ) nested\_2 INNER JOIN Workspace.CalendarDate AS join\_CalendarDate ON nested\_2.DateKey = join\_CalendarDate.FullDateAlternateKey GROUP BY nested\_2.ipaddress, nested\_2.client\_lang, nested\_2.isp, nested\_2.user\_agent, nested\_2.http\_response, nested\_2.city, nested\_2.country, nested\_2.state, nested\_2.local\_radio, nested\_2.local\_tv, nested\_2.Product\_Line, nested\_2.Category, nested\_2.Customer\_ID, nested\_2.Birth\_Date, nested\_2.Gender, join\_CalendarDate.FullDateAlternateKey, join\_CalendarDate.daynumberofmonth, join\_CalendarDate.“year” ) nested\_0 ---
**Author:** ![laurent](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/laurent/32/16_2.png) [@laurent](https://community.dremio.com/u/laurent)\ **Post date:** [January 18, 2018, 6:05pm UTC](https://community.dremio.com/t/catalog-query-timing-out/651/12 "2018-01-18T18:05:48Z")
Thank you very much. We will work on reproducing the issue and finding a fix. I’ll keep you posted with the progress. ---
**Author:** ![rwidjaja](https://avatars.discourse-cdn.com/v4/letter/r/a6a055/32.png) [@rwidjaja](https://community.dremio.com/u/rwidjaja)\ **Post date:** [January 18, 2018, 6:09pm UTC](https://community.dremio.com/t/catalog-query-timing-out/651/13 "2018-01-18T18:09:44Z")
sure, to give you more context. CalendarDate SELECT A AS DateKey, B AS FullDateAlternateKey, C AS daynumberofweek, D AS daynameofweek, E AS daynumberofmonth, F AS daynumberofyear, G AS monthname, H AS numberofyear, I AS quarter, J AS "year" FROM HDFS.“user”.dremio.sample.data.omniture.date\_omniture."date\_omniture.tsv" Customer SELECT \* FROM HDFS.“user”.dremio.sample.data.omniture.users."users.tsv" Product SELECT \* FROM HDFS.“user”.dremo.sample.data.omniture.products."products.tsv" Clickstream SELECT \* FROM Hive.dremio.clickstream ---
**Author:** ![rwidjaja](https://avatars.discourse-cdn.com/v4/letter/r/a6a055/32.png) [@rwidjaja](https://community.dremio.com/u/rwidjaja)\ **Post date:** [January 18, 2018, 6:11pm UTC](https://community.dremio.com/t/catalog-query-timing-out/651/14 "2018-01-18T18:11:33Z")
[date\_omniture.zip](https://us1.discourse-cdn.com/flex020/uploads/dremio/original/1X/84e1bbbd67d67cf4922dc6a832df140ad45311e9.zip) (14.3 KB) ---
**Author:** ![rwidjaja](https://avatars.discourse-cdn.com/v4/letter/r/a6a055/32.png) [@rwidjaja](https://community.dremio.com/u/rwidjaja)\ **Post date:** [January 18, 2018, 6:11pm UTC](https://community.dremio.com/t/catalog-query-timing-out/651/15 "2018-01-18T18:11:52Z")
[products.zip](https://us1.discourse-cdn.com/flex020/uploads/dremio/original/1X/8f1206678c337e7830e4ffeddd248f44b6606b8c.zip) (652 Bytes) ---
**Author:** ![rwidjaja](https://avatars.discourse-cdn.com/v4/letter/r/a6a055/32.png) [@rwidjaja](https://community.dremio.com/u/rwidjaja)\ **Post date:** [January 18, 2018, 6:12pm UTC](https://community.dremio.com/t/catalog-query-timing-out/651/16 "2018-01-18T18:12:12Z")
[users.zip](https://us1.discourse-cdn.com/flex020/uploads/dremio/original/1X/34847defd2be81aed3ffa2e02fac2671aa2b5018.zip) (931.4 KB) ---
**Author:** ![rwidjaja](https://avatars.discourse-cdn.com/v4/letter/r/a6a055/32.png) [@rwidjaja](https://community.dremio.com/u/rwidjaja)\ **Post date:** [January 18, 2018, 6:18pm UTC](https://community.dremio.com/t/catalog-query-timing-out/651/17 "2018-01-18T18:18:45Z")
and for the fact table [https://drive.google.com/file/d/1q8dhlhL7EUQ97X7vRGj449vYTJ3lhHcw/view?usp=sharing](https://drive.google.com/file/d/1q8dhlhL7EUQ97X7vRGj449vYTJ3lhHcw/view?usp=sharing)