Why are my queries on INFORMATION_SCHEMA slow?
See all drill best practice FAQs.
Users frequently query Hive tables via Drill, so this is a question that they ask the Drill team regularly.
If your schema contains Hive tables, Drill needs to query the Hive metastore. Drill maintains a cache of this metastore, but the data in the cache, by default, expires after 60 seconds. To modify this setting, see https://drill.apache.org/docs/hive-metadata-caching/.
Great discussion as INFORMATION_SCHEMA is extensively used by various BI Tools.
In addition to what Zelaine has mentioned above you should also check the Drill cluster storage plugin configuration, as misconfigured plugins or plugins that are enabled but with no data source available can significantly delay INFORMATION_SCHEMA queries.
Specifically HBase and Hive plugins should be verified.
Retrieving data ...