Bulk Loading CSV From Blob Storage to SQL Database

Question

There are many similar questions to this but none seem to have my exact problem and none of the suggested solutions work for me.

I have an Azure SQL database and Azure blob storage and am trying to get data from a CSV file into an existing table (same data structure, column order etc).

The csv file is formatted without an index or headers and was generated from my pandas dataframe in Python with: df.to_csv(csv_path, index=False, header=False) and then uploaded to blob storage.

And the code I have used to try and insert the data in SQL is:

CREATE DATABASE SCOPED CREDENTIAL AccessAzure
WITH
     IDENTITY = 'SHARED ACCESS SIGNATURE'
,    SECRET = 'sv=<my_token>'
;

CREATE EXTERNAL DATA SOURCE GeneralBlob
WITH
(    LOCATION   = 'https://<my_storage_account>.blob.core.windows.net/general/'
,    CREDENTIAL = AccessAzure
,    TYPE       = BLOB_STORAGE
)
;

BULK INSERT <existing_table>
FROM 'data.csv' 
WITH (DATA_SOURCE = 'GeneralBlob',
      FORMAT = 'CSV')
;

Everything runs without errors except the very last part, where I get:

Cannot bulk load. The file "data.csv" does not exist or you don't have file access rights.

I have tested my SAS token etc by passing https://<my_storage_account>.blob.core.windows.net/general/data.csv?sv=<my_token> just in my browser, and that prompts a download for my CSV. So it does exist, and with the token I should have file access rights, but nonetheless I still get that error in SQL.

I have also tried

SELECT * FROM OPENROWSET(
   BULK 'data.csv',
   DATA_SOURCE = 'GeneralBlob',
   FORMAT = 'CSV'
   ) AS DataFile;

but it complains about a lack of format file and I can't find a suitable resource to tell me how to make one of those for my CSV. It also doesn't seem to me that that will work when the bulk insert doesn't anyway.

I'd really welcome any help here!!

SECRET = 'sv=<my_token>' st, sv, se is a part of the SAS key. Check SECRET format, please. — Roman Patutin
– Roman Patutin, Commented Nov 12, 2019 at 12:57
All I can see is: his works, I've done it, but the "free text" SAS key is very fussy. and there are never any clues in any error messages to help — Nick.Mc
– Nick.Mc, Commented Nov 13, 2019 at 8:04
I got it working with OPENROWSET, so it doesn't seem to have been a problem with the SAS key — Loz
– Loz, Commented Nov 14, 2019 at 12:50

Loz · Accepted Answer · 2019-11-13 07:56:23Z

I got a connection working with OPENROWSET by writing some code to make a format file out of my CSV. The working SQL code is:

SELECT * FROM OPENROWSET(
BULK 'data.csv',
DATA_SOURCE = 'GeneralBlob',
FORMAT = 'CSV',
FORMATFILE = 'data.fmt',
FORMATFILE_DATA_SOURCE = 'GeneralBlob'
) AS DataFile;

and the Python function I wrote to make the format file from a DataFrame was:

def make_fmt_file(df, filename):
    num_cols = len(df.columns))
    with open(filename, 'w') as f:
        f.write('10.0\n')
        f.write(f'{num_cols}\n')
        for i, column in enumerate(df.columns):
            dataType = 'SQLCHAR' # Only seems to work with SQLCHAR
            collation = '""'
            if i+1 < len(df.columns):
                line = f'{i+1}\t{dataType}\t0\t0\t\","\t{i+1}\t{column}\t{collation}'
            else:
                line =f'{i+1}\t{dataType}\t0\t0\t\"\\r\\n"\t{i+1}\t{column}\t{collation}'
            f.write(line+'\n')

Mjcookie92 · Accepted Answer · 2019-11-12 13:34:53Z

0

Have you tried changing the external data source type to HADOOP & the location looks incorrect.

https://learn.microsoft.com/en-us/sql/t-sql/statements/create-external-data-source-transact-sql?view=sql-server-ver15

"Use HADOOP when the external data source is Cloudera, Hortonworks, or Azure Blob Storage."

https://learn.microsoft.com/en-us/sql/t-sql/statements/create-external-data-source-transact-sql?view=sql-server-ver15#e-create-external-data-source-to-reference-azure-blob-storage

Try -

CREATE EXTERNAL DATA SOURCE GeneralBlob WITH 
(
TYPE = HADOOP, 
LOCATION = N'wasbs://general@<my_storage_account>.blob.core.windows.net',
CREDENTIAL = AccessAzure
)

edited Nov 12, 2019 at 13:34

answered Nov 12, 2019 at 13:17

Mjcookie92

431 silver badge8 bronze badges

1 Comment

Loz Over a year ago

With that I got the error: Incorrect syntax near 'HADOOP'.

Collectives™ on Stack Overflow

Bulk Loading CSV From Blob Storage to SQL Database

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related