azure


Polybase - maximum reject threshold (0 rows) was reached while reading from an external source: 1 rows rejected out of total 1 rows processed


[Question from customer]
I have following data in a text file. Delimited by |
A | null , ZZ
C | D
When I run this query using HDInsight:
CREATE EXTERNAL TABLE myfiledata(
col1 string,
col2 string
)
row format delimited fields terminated by '|' STORED AS TEXTFILE LOCATION 'wasb://.....';
I get the following result as expected:
A null , ZZ
C D
But when I run the same query using SQL DW Polybase, it throws error:
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.
How do I fix this?
Here's my script in SQL DW:
-- Creating external data source (Azure Blob Storage)
CREATE EXTERNAL DATA SOURCE azure_storage1
WITH
(
TYPE = HADOOP
, LOCATION ='wasbs://....blob.core.windows.net'
, CREDENTIAL = ASBSecret
)
;
-- Creating external file format (delimited text file)
CREATE EXTERNAL FILE FORMAT text_file_format
WITH
(
FORMAT_TYPE = DELIMITEDTEXT
, FORMAT_OPTIONS (
FIELD_TERMINATOR ='|'
, USE_TYPE_DEFAULT = TRUE
)
)
;
-- Creating external table pointing to file stored in Azure Storage
CREATE EXTERNAL TABLE [Myfile]
(
Col1 varchar(5),
Col2 varchar(5)
)
WITH
(
LOCATION = '/myfile.txt'
, DATA_SOURCE = azure_storage1
, FILE_FORMAT = text_file_format
)
;
We’re currently working on a way to bubble up the reason for reject to the user.
In the meantime, here's what's happening:
The default # of rows allowed to fail schema matching is 0. This means that if at least one of the rows you’re loading in from /myfile.txt doesn’t match the schema. In Hive, strings can accommodate an arbitrary amount of chars, but varchars cannot. In this case it’s failing on the varchar(5) for “null , ZZ” because that is more than 5 characters.
If you’d like to change the REJECT_VALUE in the CREATE EXTERNAL TABLE call, that will let through the other row – more info can be found here: https://msdn.microsoft.com/library/dn935021(v=sql.130).aspx

Related Links

Adding additional NICs to a Virtual Machine in Azure?
Is Azure Blob storage the right place to store many (small) communication logs?
Azure Data Factory - Use GetRunRecord(runid) to get complete Error Details
Azure Stream Analytics: Specified cast is not valid
Azure AD Connect in two Office 365 tenants
Get Active Directory Value from external AD
DocumentDB how to reduce RU's for request
Azure AD Enterprise application not showing 'automatic' provisioning mode
Wildcards in counter specifiers in Azure Diagnostic
Azure-Functions: How to serve content from the root of domain
Visual studio build error 2015 using microsoft azure sql database v12
Error publishing to Azure cloud service with osFamily=5
Azure vmss without a load balancer
Visual Studio publish to azure existing apps error
Can ApplicationInsights track events across many WebApps/LogicApps/etc?
Calling Asp.Net Web API endpoint from Azure function

Categories

HOME
opencv
performance
music
lua
blogs
google-cloud-dataproc
gluu
redhat
prometheus
streaming
antlr
logback
weblogic11g
google-adwords
http2
wget
google-maps-sdk-ios
openframeworks
signature
axure
orchardcms-1.10
lag
records
ssrs-2008
ormlite-servicestack
esri
trial
masstransit
asp.net-identity-2
shinydashboard
solarwindslem
whatsapi
dokuwiki
pvlib
capstone
gridstack
bits
bezier
angular-xeditable
nexus-5
project-centennial
gretl
pulseaudio
move
requirements-management
psycopg2
atmosphere.js
building
sizeof
heritrix
balsamiq
pygments
rsh
ng-annotate
target-platform
mutation
laravel-forge
oracle-bi
linq2db
sample-data
many-to-one
crosswalk
julian
viennacl
translucency
ppl
iife
cvs2git
boxapiv2
liferay-hook
xml.modify
declspec
srp
acts-as-taggable-on
ramdisk
emacs-jedi
qdebug
kmz
angularjs-module
uptime
html5-apps
memento
domain-mapping
ekeventkit
hpple
server.mappath
letter-spacing
semantic-zoom
android-assets
facebook-authentication
machine.config
custom-tag
android-searchmanager
newsgroup
variable-names
large-teams

Resources

Mobile Apps Dev
Database Users
javascript
java
csharp
php
android
MS Developer
developer works
python
ios
c
html
jquery
RDBMS discuss
Cloud Virtualization
Database Dev&Adm
javascript
java
csharp
php
python
android
jquery
ruby
ios
html
Mobile App
Mobile App
Mobile App