Connect Power BI to Hadoop Direct query - HDFS vs Spark vs custom connector

[TheSuxor]

Hey guys,

Forwarning... I am a Hadoop newb!  I need some help to provide my hadoop peers the information or configurations I need to connect to Hadoop with Power BI.

So far we have only been able to sucessfully connect with ODBC.  But this does not allow direct query and when we try and import a table with 6 million rows and 90+ columns we are getting memory errors.

In the ideal scenario I would be able to use Direct Query.  But I've not been able to successfully connect via Spark or HDFS connectors.

HDFS only allows windows credentials and single signon (neither of which is used in our hadoop setup)

Spark - I cannot seem to find the right connection criteria to establish a connection.  I've tried ports 8998, 10015 and 10000.  I've also tried using HTTP and Standard.

I can almost always get through to the next screen where I type in user credentials.  Any help, ideas, or strategies that anyone can share would be very welcomed!!!

These are the types of error messages I get:

Details: "ODBC: ERROR [HY000] [Microsoft][Hardy] (34) Error from server: Bad Status: HTTP/1.1 400 Missing Required Header for CSRF protection..
ERROR [HY000] [Microsoft][Hardy] (34) Error from server: Bad Status: HTTP/1.1 400 Missing Required Header for CSRF protection.."

Details: "ODBC: ERROR [HY000] [Microsoft][Hardy] (34) Error from server: connect() failed: errno = 10061.
ERROR [HY000] [Microsoft][Hardy] (34) Error from server: connect() failed: errno = 10061."

Details: "ODBC: ERROR [HY000] [Microsoft][ThriftExtension] (0) Failed to initialize SASL client library: generic failure

ERROR [HY000] [Microsoft][ThriftExtension] (0) Failed to initialize SASL client library: generic failure"

Details: "ODBC: ERROR [HY000] [Microsoft][ThriftExtension] (4) Error occurred while contacting server: ETIMEDOUT. The connection has been configured to not use SASL for authentication. This error might be due to the server has been configured to use SASL for authentication.

ERROR [HY000] [Microsoft][ThriftExtension] (4) Error occurred while contacting server: ETIMEDOUT. The connection has been configured to not use SASL for authentication. This error might be due to the server has been configured to use SASL for authentication."

[v-yuezhe-msft]

@TheSuxor,

What is the exact version of Hadoop do you connect to ? What port do use use when creating a ODBC data source for Hadoop? Are you able to telnet this port from your machine?

Regards,
Lydia

[TheSuxor]

@v-yuezhe-msft  thanks for responding back.

When connecting ODBC port 10000 work.  Followed this guide: https://community.hortonworks.com/articles/61185/visualizing-hive-data-using-microsoft-power-bi.html 

Trying to connect via Spark so that we have Direct Query.  I've tried many different combinations of Servers and Ports can't seem to find a wining combination.  If I connect to it through Spark ODBC and not the PowerBI connector I can connect using Port 10015.

I am waiting to hear back on the version of hadoop installed.

I am also not sure what you mean by "Are you able to telnet this port from your machine?"  If this is common knowledge for hadoop users I'm sorry I'm VERY new to hadoop.

[TheSuxor · Accepted solution]

Figured out our problem and reported it to Microsoft.

There is something wrong with the preview spark connector but only on the windows app store version. If you download the latest installation executable from the website it works.

Using the thrift 2 server and the port 10015 (default) we got it setup.

We used the following syntax:

[Server name or ip address]:[Port number] (without brackets
Standard connection

For example:
1.0.0.1:10015

Or
www.contoso.com:10015

[monademarkov]

Hi, there was mention of a website to download this from. Do you have that link?

[TheSuxor]

I was referring to power Bi desktop. There was a bug at the time with power bi desktop on the windows store.... But power bi desktop downloaded from the power bi website didn't have the same bug

[monademarkov]

Hi, you mentioned downloading from a website. What website is this and can you send me a link?