使用大容量插入将文件从Azure Blob存储加载到Azure SQL数据库 [英] Loading file from Azure Blob Storage into Azure SQL Database using BULK INSERT

问题描述

在此公告之后 https://azure.microsoft.com/en-gb/updates/preview-loading-files-from-azure-blob-storage-into-sql-database/

我尝试了提供

I tried the example as such provided in this GitHub sample and receiving the following error,

-- Create credential with Azure Blob SAS
CREATE DATABASE SCOPED CREDENTIAL xxxstorcred
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = '?sv=2015-12-11&ss=bfqt&srt=sco&sp=rwdl&st=2017-03-14T17%3A52%3A00Z&se=2017-05-31T16%3A52%3A00Z&sig=f45435435TzrsdsdsdC8wonjDMBG0T0GJj717XetLEWReZ64eOrQE%3D';

-- Create external data source with with the roow URL of the Blob storage Account and associated credential.
CREATE EXTERNAL DATA SOURCE xxxstor
WITH (  TYPE = BLOB_STORAGE, 
        LOCATION = 'https://xxxstor.blob.core.windows.net', 
        CREDENTIAL= xxxstorcred);
--CREATE DESTINATION TABLE (if not exists)
DROP TABLE IF EXISTS Product;
GO

CREATE TABLE dbo.Product(
    Name nvarchar(50) NOT NULL,
    Color nvarchar(15) NULL,
    Price money NOT NULL,
    Size nvarchar(5) NULL,
    Quantity int NULL,
    Data nvarchar(4000) NULL,
    Tags nvarchar(4000) NULL
)
GO

--LOAD

-- INSERT CSV file into Product table
BULK INSERT Product
FROM 'random/product.csv' --random is the container name
WITH (  DATA_SOURCE = 'xxxstor',
        FORMAT='CSV', CODEPAGE = 65001, --UTF-8 encoding
        FIRSTROW=2,
        TABLOCK); 

无法批量加载，因为文件"random/product.csv"无法 开了.操作系统错误代码1117(请求无法 由于I/O设备错误而执行.)

Cannot bulk load because the file "random/product.csv" could not be opened. Operating system error code 1117(The request could not be performed because of an I/O device error.).

我想念什么?

推荐答案

我已经尝试过从您提供的github示例中获取t-sql.它工作正常.根据我的测试，有两种可能性会导致此错误:

I have tried the t-sql from the github sample that you provide. It works fine. From my test, there are two possibilities that will cause this error:

1)容器名称不正确

2)SAS SECRET不正确

2) the SAS SECRET is incorrect

根据您的描述，我认为您的SAS机密不正确.这是您使用的秘密:

Based on your description, I think your SAS secret is incorrect. Here is the secret that you used:

SECRET ='?sv = 2015-12-11& ss = bfqt& srt = sco& sp = rwdl& st = 2017-03-14T17％3A52％3A00Z& se = 2017-05-31T16％3A52％3A00Z& ; sig = f45435435TzrsdsdsdC8wonjDMBG0T0GJj717XetLEWReZ64eOrQE％3D';

SECRET = '?sv=2015-12-11&ss=bfqt&srt=sco&sp=rwdl&st=2017-03-14T17%3A52%3A00Z&se=2017-05-31T16%3A52%3A00Z&sig=f45435435TzrsdsdsdC8wonjDMBG0T0GJj717XetLEWReZ64eOrQE%3D';

作为我的测试，我们需要删除?" .请尝试以下秘密:

As my test, we need to remove '?'. Please try below secret:

SECRET ='sv = 2015-12-11& ss = bfqt& srt = sco& sp = rwdl& st = 2017-03-14T17％3A52％3A00Z& se = 2017-05-31T16％3A52％3A00Z& sig = f45435435TzrsdsdsdC8wonjDMBG0T0GJj717XetLEWReZ64eOrQE％3D';

SECRET = 'sv=2015-12-11&ss=bfqt&srt=sco&sp=rwdl&st=2017-03-14T17%3A52%3A00Z&se=2017-05-31T16%3A52%3A00Z&sig=f45435435TzrsdsdsdC8wonjDMBG0T0GJj717XetLEWReZ64eOrQE%3D';

有关如何生成SAS，请参考这篇文章.

For how to generate SAS, please refer to this article.

这篇关于使用大容量插入将文件从Azure Blob存储加载到Azure SQL数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！