PolyBase – error connecting to Hadoop file
At least with the current SQL Server 2016 CTP 2.3, PolyBase
errors out trying to connect to certain text files in Hortonworks Hadoop. You
typically get an error message similar to the following:
Update - June 14, 2016: MSFT published a Polybase focused white paper for the Azure Data Warehouse, which supports Polybase. This out lined - at least for Azure DW, the file types supported by Polybase. For Azure DW, Polybase only supports UTF-8 files. This could explain some of the failed load issues experienced.
Msg 107090, Level 16, State 1, Line 79
Query aborted-- the maximum reject threshold (0 rows) was reached while reading from an external source: 1 rows rejected out of total 1 rows processed.
OLE DB provider "SQLNCLI11" for linked server "(null)" returned message "Unspecified error".
Msg 7421, Level 16, State 2, Line 79
Cannot fetch the rowset from OLE DB provider "SQLNCLI11" for linked server "(null)".
I’ve investigated what might be causing these errors, trying
to discover if there are certain traits that cause the error.
Could not find or load main class path.....
Dennes found on Hortonworks a possible solution for the following error.
Error: Could not find or load main class org.apache.hadoop.mapreduce.v2.app.MRAppMaster
This may be beneficial for other issues, such as Could not obtain block.
Answer by Montrial Harrell · Jun 11 at 04:19 AM
Got it!! I added the below property to the mapred-site.xml file and the query ran successfully.
<property> <name>mapreduce.app-submission.cross-platform</name> <value>True</value> </property>
(this was provided by Montrial Harrell at the
Yarn ClassPath Value for Polybase Pushdown - Hortonworks Community Connection -- review the entire post, as there may be other suggestons that might be of assistance.)
...cannot be cast...
Could not obtain block:
Dennes found that the error message Could not obtain block: "...usually means the port 50010 (data node port) is not responding to SQL Server." See also adjustments noted above for "Could not find or load main class"
EOF does not match expected format:
From my investigation, if EOF (end of file) has a different
format from that declared in the CREATE EXTERNAL TABLE command, you will get an
error. If, for example, the file has a trailing line with row count, or other meta data.
Data Type Mismatch
If the declared data type does not match with the incoming
data type, you’ll get an error. In setting up a new table, it might be useful
to first declare all columns as varchar or nvarchar to minimize any potential
errors. Once you establish a working connection, create a new table with the correctly typed columns. Loading dates into PolyBase has a very specific protocol, and not all date formats are supported.
Data Size insufficient
If the size of the declared data type is not sufficiently large
for incoming data type, you’ll get an error. In setting up a new table, it
might be useful to first declare all columns as long varchar or nvarchar to
minimize any potential errors. An example might be where you declare the column to be varchar(10), but the maximum size found exceeds 10. No auto-truncate.
Line Delimiter
At least so far, I’ve been able to connect to files with the
three ‘standard’ approaches to establishing the EOL:
1) CR/LF 0D 0A (DOS/Windows)
2) LF 0A (UNIX)
3) CR 0D (Mac)
File Format
The UTF-8 format seems to be the most common format, PolyBase
works with the UTF-8 format. I have had trouble with files in the older
ANSI/ASCII format. My single attempt with
UTF-16 did not work, but that may not be due to the UTF-16 format. As noted above, Azure Polybase only supports UTF-8.
Other – still to be discovered problems
Several Hadoop files I have are not connecting to PolyBase,
and I have not resolved what the problem. As I get them resolved, I’ll add
those discoveries here.