KNOWLEDGE BASE

"[Hortonworks][Hardy] (71) Failed to establish connection with unknown error." occurs when using Custom SQL on Hortonworks Hadoop Hive


Published: 02 Jul 2021
Last Modified Date: 06 Jul 2021

Issue

When attempting to run a Custom SQL query to Hadoop Hive, the connection appears to be made, but data won't load into Tableau Desktop/Prep. Instead, we could see the following errors: 

On Tableau Prep: "system error"  or "[Hortonworks][Hardy] (71) Failed to establish connection with unknown error."

On Tableau Desktop: "[Hortonworks][Hardy] (71) Failed to establish connection with unknown error."

 

Environment

  • Tableau Prep Builder
  • Tableau Desktop
  • Hortonworks Hadoop Hive

Resolution

We can troubleshoot and determine if it's a Custom SQL issue by checking: 

  • Add "Limit 100" at the end of Custom SQL. 
  • Remove "WHERE" clauses 

If one of the information above resolves the issue, we can use the following options.

Option 1Use materialized view.

A materialized view is a “select query” with "join, where" performed on the database and the end result set is stored as a table in the database. The materialized view can be kept in sync with database updates. Instead of performing multiple joins, Tableau connects directly to the materialized view, allowing the database to perform the bulk of the processing of the initial query. Please see: https://kb.tableau.com/articles/issue/custom-sql-performance-issues

Option 2: Optimize the Custom SQL.

If you still see the issue after applying a filter on Tableau Desktop, you may need to add more filters to reduce the data size.
  • Limit the fields/rows selected

 

Cause

When bringing large dataset to Tableau Desktop/Prep, the Custom SQL reaches the limit of the query capacity. 

Additional Information

Please note that this is NOT a connection issue and we should only consider this article if the following conditions are met: 
1. Tableau Desktop/Prep can connect to Hortonworks Hadoop Hive without an issue 
2. It takes long time to run the Custom SQL outside of Tableau 
Did this article resolve the issue?