sql - 将 CSV 从 Blob 存储批量加载到 SQL 数据库

Question

有很多类似的问题，但似乎没有一个问题与我的问题完全相同，并且建议的解决方案都不适合我。

我有一个 Azure SQL 数据库和 Azure Blob 存储，并尝试将数据从 CSV 文件获取到现有表中(相同的数据结构、列顺序等)。

csv 文件的格式没有索引或标题，是通过 Python 中的 pandas 数据框生成的: df.to_csv(csv_path, index=False, header=False)然后上传到 Blob 存储。

我用来尝试在 SQL 中插入数据的代码是:

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')
;

一切都运行没有错误，除了最后一部分，我得到:

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

我已经通过测试测试了我的 SAS token 等 https://<my_storage_account>.blob.core.windows.net/general/data.csv?sv=<my_token>就在我的浏览器中，这会提示下载我的 CSV。所以它确实存在，并且使用 token 我应该具有文件访问权限，但尽管如此，我仍然在 SQL 中遇到该错误。

我也尝试过

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

但它提示缺少格式文件，而且我找不到合适的资源来告诉我如何为我的 CSV 制作其中一个。在我看来，当批量插入不起作用时，这也不起作用。

我真的很欢迎任何帮助!!

Answer 1

我通过编写一些代码从 CSV 中创建格式文件，获得了与 OPENROWSET 的连接。有效的 SQL 代码是:

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

我编写的用于从 DataFrame 制作格式文件的 Python 函数是:

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')