problems in creating external table for azure synapse analytics

Question

I am having difficulty creating external table in SMSS. I have the dataset stored in BLOB storage and try to load it from there in to external table. I keep getting this error:

HdfsBridge::recordReaderFillBuffer - Unexpected error encountered filling record reader buffer: HadoopSqlException: Error converting data type NVARCHAR to INT

i used the following query

CREATE EXTERNAL TABLE [ext].[klant](
[number] [int] NOT NULL,
    [customer_id] [int] NOT NULL,
    [status] [nvarchar](50) NOT NULL,
    [shipping.postcode] [nvarchar](50) NOT NULL,
    [date_created] [datetime2](0) NOT NULL,
    [date_completed] [datetime2](0) NOT NULL,
    [shipping.address_1] [nvarchar](50) NOT NULL,
    [shipping.city] [nvarchar](50) NOT NULL
    )






WITH (
LOCATION='/customer/',
    DATA_SOURCE = storage,  
    FILE_FORMAT = fileformat,
    REJECT_TYPE = VALUE,
    REJECT_VALUE = 0


    );

I also tried to solve this problem in ADF by copying the data from source to table, but with no luck. I hope somebody could help me with this problem. thanks in advance

Can you detail your CREATE EXTERNAL DATA SOURCE T-SQL statement? You need to use TYPE = Hadoop and LOCATION = wasbs://<container>@<storage_account>.blob.core.windows.net Please see the following tutorial on how to set this up: learn.microsoft.com/en-us/azure/synapse-analytics/… — Mike Ubezzi
– Mike Ubezzi, Commented May 1, 2020 at 5:41
CREATE EXTERNAL DATA SOURCE storage WITH ( TYPE = Hadoop, LOCATION = 'wasbs://[email protected]' ); i hope u mean this. I had set this op before i created the external table. and this gave me no error. It is only when i try to read the data — user7418776
– user7418776, Commented May 1, 2020 at 11:46

Mike Ubezzi · Accepted Answer · 2020-05-01 19:41:34Z

1

Thank you for verifying the CREATE EXTERNAL DATA SOURCE statement. Your specific issue is with one of the column definitions between what is defined in your CREATE EXTERNAL TABLE statement and what is actually in your data file.

Error converting data type NVARCHAR to INT

The data in the source data file, either the number column or customer_id column is nvarchar and trying to be stored as int. Do you have any extra characters in your data file? The error indicates a data type or column mismatch.

answered May 1, 2020 at 19:41

Mike Ubezzi

1,0026 silver badges9 bronze badges

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

Collectives™ on Stack Overflow

problems in creating external table for azure synapse analytics

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related