CODEWITHSUNDEEP
sqlazurecsvserverlessazure-synapse
Mauro Minella
Mauro Minella

Reputation: 179

Azure Synapse SQL Serverless, how to create external table from CSV with fields longer than 8Kb?

I have a CSV with more than 500 fields, hosted on an Azure storage account; however I just need a couple of columns, which may contain values longer than 8Kbytes. For this reason, I started by writing a simple query in Azure Synapse SQL Serverless like this:

SELECT TOP 100 C1, C2 FROM OPENROWSET(
        BULK 'https://mysa.blob.core.windows.net/my_file.csv',
        FORMAT = 'CSV',
        PARSER_VERSION = '2.0'
    ) AS [result]

It fails with the error "String or binary data would be truncated while reading column of type 'VARCHAR'". But it does not JUST report this warning, it does not return ANY rows because of this warning. So, a simple solution is to disable warnings; of course that value is truncacted to 8Kb, but the query doesn't fail this way:

SET ANSI_WARNINGS OFF

SELECT TOP 100 * FROM OPENROWSET(
   BULK 'https://mysa.blob.core.windows.net/my_file.csv',
   FORMAT = 'CSV',
   PARSER_VERSION = '2.0'
   AS [result] 

SET ANSI_WARNINGS ON

Now I need some help to get the final target, which is to build an EXTERNAL TABLE, rather than just a SELECT, leaving the CSV where it is (in other words: I don't want to create a materialized view or a CETAS or a SELECT INTO which would duplicate data).

If I run it this way:

CREATE EXTERNAL TABLE my_CET (
    C1 NVARCHAR(8000),
    C2 NVARCHAR(8000)
    )
    WITH (
    LOCATION = 'my_file.csv',
    DATA_SOURCE = [my_data_source],
    FILE_FORMAT = [SynapseDelimitedTextFormat]
    )

, it seems working because it successfully creates an external table, however if I try to read it, I get the error "External table my_CET is not accessible because location does not exist or it is used by another process.". If I try setting ANSI_WARNINGS OFF, it tells me "The option 'ANSI_WARNINGS' must be turned ON to execute requests referencing external tables.". As said I don't need all the 500 fields hosted in the CSV but just a couple of them, including the one which I should truncate data to 8KB as I did in the above example.

If I use a CSV file where no field is larger than 8KB, the external table creation works correctly, but I couldn't manage to make it work when some values are longer than 8Kb.

Upvotes: 0

Views: 1862

Answers (1)

user18794018
user18794018

Reputation:

I think when creating an external table from a csv you have to bring in all the columns. I am sure someone can correct me if I am wrong.

Depending on what you want to do, you could create a view from the external table using a select query. e.g.

CREATE VIEW my_CET_Vw
AS
SELECT C1,
    C2
FROM my_CET

Upvotes: 0

Related Questions