1

I'm trying to create a EXTERNAL TABLE, mapping it from a Blob Storage following this tutorial Load Contoso retail data to Synapse SQL.

But I'm getting this error when I query the table: Failed to execute query. Error: HdfsBridge::recordReaderFillBuffer - Unexpected error encountered filling record reader buffer: HadoopExecutionException: Too many columns in the line.

My files configurations are:

A: Create a database scoped credential

CREATE DATABASE SCOPED CREDENTIAL ServiceNow_AzureStorageCredential_ADSL
WITH
     IDENTITY = 'usr_adsl_servicnow'
    ,SECRET = 'my_key'
;

B: Create an external data source

CREATE EXTERNAL DATA SOURCE ServiceNowBlobStorage
WITH (
    TYPE = HADOOP,
    LOCATION = 'abfss://<servicenow container>@<account storage>.blob.core.windows.net',
    CREDENTIAL = ServiceNow_AzureStorageCredential_ADSL
);

C: Create the file format to be read from blob storage

CREATE EXTERNAL FILE FORMAT ServiceNowFileFormatCSV
WITH
(    FORMAT_TYPE = DELIMITEDTEXT
,    FORMAT_OPTIONS    (   FIELD_TERMINATOR = ','
                      ,    STRING_DELIMITER = '"'
                      ,    FIRST_ROW = 2
                      ,    DATE_FORMAT = 'dd/MM/yyyy HH:mm:ss'
                      ,    USE_TYPE_DEFAULT = TRUE
                      ,    Encoding = 'UTF8'
                    )
);

D: Create the external table

CREATE EXTERNAL TABLE [asb].[incidents] (
    [number] [nvarchar](30) NOT NULL,
    [opened] [datetime] NOT NULL,
    [resolved] [datetime] NULL,
    [updated] [datetime] NULL,
    [short_description] [nvarchar](2000) NOT NULL,
    [urgency] [nvarchar](65) NOT NULL,
    [resolve_time] [nvarchar](100) NULL,
    [business_service] [nvarchar](100) NULL,
    [what_is_the_system] [nvarchar](100) NULL,
    [problem] [nvarchar](65) NULL,
    [parent] [nvarchar](65) NULL,
    [where_is_the_problem] [nvarchar](100) NULL,
    [child_incidents] [nvarchar](30) NULL,
    [parent_incident] [nvarchar](65) NULL,
    [impact] [nvarchar](65) NULL,
    [severity] [nvarchar](65) NULL,
    [incident_state] [nvarchar](100) NULL,
    [company] [nvarchar](30) NULL,
    [business_duration] [nvarchar](65) NULL,
    [duration] [nvarchar](65) NULL,
    [created] [nvarchar](65) NULL,
    [catalog_item] [nvarchar](100) NULL,
    [priority] [nvarchar](100) NULL,
    [state] [nvarchar](65) NULL,
    [category] [nvarchar](30) NULL,
    [assignment_group] [nvarchar](100) NULL,
    [location] [nvarchar](200) NULL,
    [ETLLoadID] [nvarchar](65) NULL,
    [LoadDate] [nvarchar](65) NULL,
    [UpdateDate] [nvarchar](65) NULL
)
WITH
(
        LOCATION='servicenow-tables/incidents/incidents.csv'
    ,   DATA_SOURCE = ServiceNowBlobStorage
    ,   FILE_FORMAT = ServiceNowFileFormatCSV
    ,   REJECT_TYPE = VALUE
    ,   REJECT_VALUE = 0
);

I only use nvarchar data type to avoid converting error Error converting data type VARCHAR to DATETIME in this test.

My file format is:

number,opened,resolved,updated,short_description,urgency,task_type,resolve_time,business_service,what_is_the_system,problem,parent,where_is_the_problem,child_incidents,parent_incident,impact,severity,incident_state,company,business_duration,duration,created,catalog_item,priority,state,category,assignment_group,location
"INC0020620","15/05/2020 10:42:39","19/05/2020 12:49:36","26/05/2020 13:00:02","Problemas de divergência nos valores","3 - Baixo(a)","Incidente","45,2620486","PDV",,"PRB0040714","",,"0","","1 - Alto(a)","3 - Baixo(a)","Encerrado","Lojas  S.A.","18 Horas 6 Minutos","4 Dias 2 Horas 6 Minutos","15/05/2020 10:42:39","Problemas de divergência nos valores","3 - Moderado","Encerrado","Sistemas/Aplicações","TI_N2_SIS","ADMINISTRACAO"

I tried many forms to fix this but without success.

1 Answer 1

1

I just did a simple search for "," on the top line it gave 27 counts which means we have 28 fields . Doing the same on the bottom line give me 28 counts which means that we have 29 fields . So you have more extra value in the second line hence the error , I think the issue is below value

"PRB0040714","",,"0

hopt this helps

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.