question

PalashAich-7056 avatar image
0 Votes"
PalashAich-7056 asked MehmetBASERDEM-9440 answered

Error while trying to read data from ADLS to SQL Server using Polybase External table

Hello there,

I have a CSV file available in ADLS Gen 2 location. While trying to query the data from SQL server On-Prem, I am getting below error. I have another file, and it is working fine. What should I modify in the file format to get rid of this error.

Error:
HdfsBridge::recordReaderFillBuffer - Unexpected error encountered filling record reader buffer: HadoopExecutionException: No closing string delimiter.

File Format:
FORMAT_TYPE = DELIMITEDTEXT
, FORMAT_OPTIONS (FIELD_TERMINATOR = N',' , STRING_DELIMITER = N'"', DATE_FORMAT = N'yyyy-MM-dd HH:mm'
, USE_TYPE_DEFAULT = False)

CSV File Data:
b7b99200-b790-eb11-b1ac-000d3a9e5194,"4/7/2021 9:02:58 AM","4/7/2021 9:02:58 AM",0,1,,,,,,1,1,,1,1,,1,1,,1,1,,1,,1,False,False,False,,False,False,False,False,False,True,False,False,False,False,,,,,,,,,,,,,dba4ce79-af8c-eb11-b1ac-000d3a8efdd4,"systemuser",,,dba4ce79-af8c-eb11-b1ac-000d3a8efdd4,"systemuser",,,,,a7fd3dd7-fb36-eb11-a813-000d3a8b6b66,"businessunit",,,,,c788a9b1-2137-eb11-a813-000d3a8b6b66,"transactioncurrency",,,,,,,,,dba4ce79-af8c-eb11-b1ac-000d3a8efdd4,"systemuser",,,,,dba4ce79-af8c-eb11-b1ac-000d3a8efdd4,"systemuser",,2782500.0,,,,2782500.0,,,,,,,,,,,,,,,"2021-03-29T17:54:48Z",,,,,,,,,,,"2021-04-07T02:35:30Z",,,,,,,,"Corporation",,,,,,,1,,,,,,"2021-03-29T17:48:44.0000000+00:00",,1,,,,"S Raikar",,,,,,,,,,,,,63d61000-742e-46a1-a30f-e6ed720b1ff5,,,,,,1,,,,,,,,,,,,,,,,,,,,,,,,,,,,,"Raikar",,,,"2021-04-07T04:35:33Z",fe62b29c-e53b-4aac-b240-deba1f0f9350,,,,,,,,"001a000001GDSFjAAP",,,,7605457,b7b99200-b790-eb11-b1ac-000d3a9e5194,,,,,,"S Raikar",,,,,,1



sql-server-generalsql-server-transact-sqlazure-data-lake-storage
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @PalashAich-7056, welcome to Microsoft Q&A forum.

Could you please provide the script of external table as well to replicate this issue?

0 Votes 0 ·

Hi @PalashAich-7056, awaiting inputs from you on this.

0 Votes 0 ·
PalashAich-7056 avatar image
0 Votes"
PalashAich-7056 answered PalashAich-7056 published

Hi @AnuragSharma-MSFT ,

Below is the table create script.

CREATE EXTERNAL TABLE [dbo].[Account_CDM_Formatted]
(
[C1] [varchar](255) NULL,
[C2] [varchar](255) NULL,
[C3] [varchar](255) NULL,
[C4] [varchar](255) NULL,
[C5] [varchar](255) NULL,
[C6] [varchar](255) NULL,
[C7] [varchar](255) NULL,
[C8] [varchar](255) NULL,
[C9] [varchar](255) NULL,
[C10] [varchar](255) NULL,
[C11] [varchar](255) NULL,
[C12] [varchar](255) NULL,
[C13] [varchar](255) NULL,
[C14] [varchar](255) NULL,
[C15] [varchar](255) NULL,
[C16] [varchar](255) NULL,
[C17] [varchar](255) NULL,
[C18] [varchar](255) NULL,
[C19] [varchar](255) NULL,
[C20] [varchar](255) NULL,
[C21] [varchar](255) NULL,
[C22] [varchar](255) NULL,
[C23] [varchar](255) NULL,
[C24] [varchar](255) NULL,
[C25] [varchar](255) NULL,
[C26] [varchar](255) NULL,
[C27] [varchar](255) NULL,
[C28] [varchar](255) NULL,
[C29] [varchar](255) NULL,
[C30] [varchar](255) NULL,
[C31] [varchar](255) NULL,
[C32] [varchar](255) NULL,
[C33] [varchar](255) NULL,
[C34] [varchar](255) NULL,
[C35] [varchar](255) NULL,
[C36] [varchar](255) NULL,
[C37] [varchar](255) NULL,
[C38] [varchar](255) NULL,
[C39] [varchar](255) NULL,
[C40] [varchar](255) NULL,
[C41] [varchar](255) NULL,
[C42] [varchar](255) NULL,
[C43] [varchar](255) NULL,
[C44] [varchar](255) NULL,
[C45] [varchar](255) NULL,
[C46] [varchar](255) NULL,
[C47] [varchar](255) NULL,
[C48] [varchar](255) NULL,
[C49] [varchar](255) NULL,
[C50] [varchar](255) NULL,
[C51] [varchar](255) NULL,
[C52] [varchar](255) NULL,
[C53] [varchar](255) NULL,
[C54] [varchar](255) NULL,
[C55] [varchar](255) NULL,
[C56] [varchar](255) NULL,
[C57] [varchar](255) NULL,
[C58] [varchar](255) NULL,
[C59] [varchar](255) NULL,
[C60] [varchar](255) NULL,
[C61] [varchar](255) NULL,
[C62] [varchar](255) NULL,
[C63] [varchar](255) NULL,
[C64] [varchar](255) NULL,
[C65] [varchar](255) NULL,
[C66] [varchar](255) NULL,
[C67] [varchar](255) NULL,
[C68] [varchar](255) NULL,
[C69] [varchar](255) NULL,
[C70] [varchar](255) NULL,
[C71] [varchar](255) NULL,
[C72] [varchar](255) NULL,
[C73] [varchar](255) NULL,
[C74] [varchar](255) NULL,
[C75] [varchar](255) NULL,
[C76] [varchar](255) NULL,
[C77] [varchar](255) NULL,
[C78] [varchar](255) NULL,
[C79] [varchar](255) NULL,
[C80] [varchar](255) NULL,
[C81] [varchar](255) NULL,
[C82] [varchar](255) NULL,
[C83] [varchar](255) NULL,
[C84] [varchar](255) NULL,
[C85] [varchar](255) NULL,
[C86] [varchar](255) NULL,
[C87] [varchar](255) NULL,
[C88] [varchar](255) NULL,
[C89] [varchar](255) NULL,
[C90] [varchar](255) NULL,
[C91] [varchar](255) NULL,
[C92] [varchar](255) NULL,
[C93] [varchar](255) NULL,
[C94] [varchar](255) NULL,
[C95] [varchar](255) NULL,
[C96] [varchar](255) NULL,
[C97] [varchar](255) NULL,
[C98] [varchar](255) NULL,
[C99] [varchar](255) NULL,
[C100] [varchar](255) NULL,
[C101] [varchar](255) NULL,
[C102] [varchar](255) NULL,
[C103] [varchar](255) NULL,
[C104] [varchar](255) NULL,
[C105] [varchar](255) NULL,
[C106] [varchar](255) NULL,
[C107] [varchar](255) NULL,
[C108] [varchar](255) NULL,
[C109] [varchar](255) NULL,
[C110] [varchar](255) NULL,
[C111] [varchar](255) NULL,
[C112] [varchar](255) NULL,
[C113] [varchar](255) NULL,
[C114] [varchar](255) NULL,
[C115] [varchar](255) NULL,
[C116] [varchar](255) NULL,
[C117] [varchar](255) NULL,
[C118] [varchar](255) NULL,
[C119] [varchar](255) NULL,
[C120] [varchar](255) NULL,
[C121] [varchar](255) NULL,
[C122] [varchar](255) NULL,
[C123] [varchar](255) NULL,
[C124] [varchar](255) NULL,
[C125] [varchar](255) NULL,
[C126] [varchar](255) NULL,
[C127] [varchar](255) NULL,
[C128] [varchar](255) NULL,
[C129] [varchar](255) NULL,
[C130] [varchar](255) NULL,
[C131] [varchar](255) NULL,
[C132] [varchar](255) NULL,
[C133] [varchar](255) NULL,
[C134] [varchar](255) NULL,
[C135] [varchar](255) NULL,
[C136] [varchar](255) NULL,
[C137] [varchar](255) NULL,
[C138] [varchar](255) NULL,
[C139] [varchar](255) NULL,
[C140] [varchar](255) NULL,
[C141] [varchar](255) NULL,
[C142] [varchar](255) NULL,
[C143] [varchar](255) NULL,
[C144] [varchar](255) NULL,
[C145] [varchar](255) NULL,
[C146] [varchar](255) NULL,
[C147] [varchar](255) NULL,
[C148] [varchar](255) NULL,
[C149] [varchar](255) NULL,
[C150] [varchar](255) NULL,
[C151] [varchar](255) NULL,
[C152] [varchar](255) NULL,
[C153] [varchar](255) NULL,
[C154] [varchar](255) NULL,
[C155] [varchar](255) NULL,
[C156] [varchar](255) NULL,
[C157] [varchar](255) NULL,
[C158] [varchar](255) NULL,
[C159] [varchar](255) NULL,
[C160] [varchar](255) NULL,
[C161] [varchar](255) NULL,
[C162] [varchar](255) NULL,
[C163] [varchar](255) NULL,
[C164] [varchar](255) NULL,
[C165] [varchar](255) NULL,
[C166] [varchar](255) NULL,
[C167] [varchar](255) NULL,
[C168] [varchar](255) NULL,
[C169] [varchar](255) NULL,
[C170] [varchar](255) NULL,
[C171] [varchar](255) NULL,
[C172] [varchar](255) NULL,
[C173] [varchar](255) NULL,
[C174] [varchar](255) NULL,
[C175] [varchar](255) NULL,
[C176] [varchar](255) NULL,
[C177] [varchar](255) NULL,
[C178] [varchar](255) NULL,
[C179] [varchar](255) NULL,
[C180] [varchar](255) NULL,
[C181] [varchar](255) NULL,
[C182] [varchar](255) NULL,
[C183] [varchar](255) NULL,
[C184] [varchar](255) NULL,
[C185] [varchar](255) NULL,
[C186] [varchar](255) NULL,
[C187] [varchar](255) NULL,
[C188] [varchar](255) NULL,
[C189] [varchar](255) NULL,
[C190] [varchar](255) NULL,
[C191] [varchar](255) NULL,
[C192] [varchar](255) NULL,
[C193] [varchar](255) NULL,
[C194] [varchar](255) NULL,
[C195] [varchar](255) NULL,
[C196] [varchar](255) NULL,
[C197] [varchar](255) NULL,
[C198] [varchar](255) NULL,
[C199] [varchar](255) NULL,
[C200] [varchar](255) NULL,
[C201] [varchar](255) NULL,
[C202] [varchar](255) NULL,
[C203] [varchar](255) NULL,
[C204] [varchar](255) NULL,
[C205] [varchar](255) NULL,
[C206] [varchar](255) NULL,
[C207] [varchar](255) NULL,
[C208] [varchar](255) NULL,
[C209] [varchar](255) NULL,
[C210] [varchar](255) NULL,
[C211] [varchar](255) NULL,
[C212] [varchar](255) NULL,
[C213] [varchar](255) NULL,
[C214] [varchar](255) NULL,
[C215] [varchar](255) NULL,
[C216] [varchar](255) NULL,
[C217] [varchar](255) NULL,
[C218] [varchar](255) NULL,
[C219] [varchar](255) NULL,
[C220] [varchar](255) NULL,
[C221] [varchar](255) NULL,
[C222] [varchar](255) NULL
)
WITH (DATA_SOURCE = [AzureStorage],LOCATION = N'/account/2021-04.csv',FILE_FORMAT = [CustomFormat],REJECT_TYPE = VALUE,REJECT_VALUE = 0)
GO

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AnuragSharma-MSFT avatar image
0 Votes"
AnuragSharma-MSFT answered AnuragSharma-MSFT commented

Hi @PalashAich-7056, thanks for replying back. I tried using the same scripts and data you provided and was able to query the csv data.

Firstly I uploaded the CSV file in Blob storage configured as Data Lake Storage Gen2. Then I created the scoped credentials and used the same for querying the data as below:

 CREATE MASTER KEY ENCRYPTION BY PASSWORD='password@123';
    
 CREATE DATABASE SCOPED CREDENTIAL AzureStorageCredential
 WITH
   IDENTITY = 'XYZ',
   SECRET = '<your storage key>' ;
    
 CREATE EXTERNAL DATA SOURCE AzureStorage
 WITH 
 (
     TYPE = HADOOP,
         LOCATION = 'wasbs://yourcontainer@youstorageacc.blob.core.windows.net',
     CREDENTIAL = AzureStorageCredential
 );
    
 CREATE EXTERNAL FILE FORMAT TextFileFormat 
 WITH ( 
     FORMAT_TYPE = DELIMITEDTEXT, 
     FORMAT_OPTIONS ( 
         FIELD_TERMINATOR = N',' , STRING_DELIMITER = N'"', DATE_FORMAT = N'yyyy-MM-dd HH:mm',USE_TYPE_DEFAULT = False
     ) 
 );

Also attaching the csv file and create external table script provided by you:
86916-data.txt
87000-externaltablescript.txt

Could you please try it once and let me know if this works for you?



data.txt (1.9 KiB)
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @PalashAich-7056, just checking if you were able to look into the last reply.

0 Votes 0 ·

Thanks @AnuragSharma-MSFT

This works fine for the record. For some of the files I am facing the same issue. My file size is around 7 MB. It is not allowing me to upload here above 3 MB.

0 Votes 0 ·

Hi @PalashAich-7056, I copy pasted the same record again and again and created a file of 4.5 MB and was able to run the query successfully.

Would it be possible for you to provide the file you are using and I can try running on the same?

0 Votes 0 ·
PalashAich-7056 avatar image
0 Votes"
PalashAich-7056 answered AnuragSharma-MSFT commented

Hi @AnuragSharma-MSFT

I am unable to attach file as it is more than 7 MB. Also, I compressed the file which is of less size, however, zip file is not supporting to attach here.
Is there any other way I can provide you file please?

Thanks,
Palash

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

Is there any other way I can provide you file please?


  1. Upload it elsewhere (OneDrive, Dropbox etc) and post the link.

  2. Change the extension of the file to a permitted type - and inform us that we need to change it back.

But my guess from the error message is simply that the file is malformed.

Rather than uploading the file to have other people to look at it, you could do some troubleshooting on your own. Divide the file into two files. If one of these loads, then slash the the bad half in two etc.

Or load the file to a one-column table with one row for each file. Then do:

SELECT col FROM file WHERE len(replace(file, '"', '')) - len(file) % 2 = 1

Troubleshooting things like this is part of the programmer's trade.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MehmetBASERDEM-9440 avatar image
0 Votes"
MehmetBASERDEM-9440 answered

I have a similar issue in my case. In one of the rows of the source data, textfield has doublequote character inside. It looks like Polybase external table can't handle these type of escape-character scenarios. We are going to prune the quote characters before we use it with polybase

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.