Azure Blob到Azure SQL数据库:无法批量加载，因为文件"xxxx.csv"不能打开.操作系统错误代码5(访问被拒绝.) [英] Azure blob to Azure SQL Database: Cannot bulk load because the file "xxxx.csv" could not be opened. Operating system error code 5(Access is denied.)

问题描述

我正在尝试在azure sql数据库中批量加载azure blob存储中的某些数据.文件内容为:

 客户，年龄，性别'C1093826151'，'4'，'M''C352968107'，'2'，'M''C2054744914'，'4'，'F' 

该文件位于名为 silver 的容器中.在银色的容器中，我有File1.fmt，其内容是:

  14.031 SQLCHAR 0 7，"1位客户"2 SQLCHAR 0 100，"2岁SQL_Latin1_General_CP1_CI_AS3 SQLCHAR 0 100"\ r \ n";3个性别SQL_Latin1_General_CP1_CI_AS 

我有多余的一行添加了fmt文件的末尾.

我创建了一个SAS令牌，将全部启用和允许，如下面的屏幕截图所示:

datalake上的防火墙规则如下图:

下面是我的sql脚本(我删除了SAS令牌开头的?，因为我的银色容器是公共的，所以我知道我需要SAS令牌):

 通过密码创建主密钥加密='safepassword';去删除外部数据源MyAzure发票DROP DATABASE SCOPED CREDENTIAL上传发票创建数据库共享凭证上传发票IDENTITY =共享访问签名"，SECRET ='sv = 2019-12-12 ********************************** 88％3D';-dl--DROP外部数据源MyAzure发票创建外部数据源MyAzure发票和 (TYPE = BLOB_STORAGE，位置='https://mydatalake.blob.core.windows.net/silver'，CREDENTIAL =上传发票); 

着陆桌:

  CREATE TABLE [ext].[customer]([customer_id] [int] IDENTITY(1,1)NOT NULL，[客户] [varchar](100)NOT NULL，[age] [int] NOT NULL，[性别] [varchar](50)非空)在[PRIMARY]上去 

以下是我尝试将文件加载到sql数据库中的方法:

 -1选择*从OPENROWSET(BULK'bs140513_032310-demo.csv'，DATA_SOURCE ='MyAzureInvoices'，格式="CSV"，FORMATFILE ='File1.fmt'，FORMATFILE_DATA_SOURCE ='MyAzureInvoices')AS DataFile;-2去选择*从OPENROWSET(BULK'bs140513_032310-demo.csv'，DATA_SOURCE ='MyAzureInvoices'，SINGLE_CLOB)AS DataFile;去-3批量插入分机客户来自'bs140513_032310-demo.csv'和 (DATA_SOURCE ='MyAzureInvoices'，FORMAT ='CSV'); 

它们都给出相同的错误:

  Msg 4861，级别16，状态1，第2行无法批量加载，因为文件"bs140513_032310-demo.csv"不能打开.操作系统错误代码5(访问被拒绝.). 

我已经尝试了3天，但我迷路了.谢谢你的帮助注意:

断开连接时，它可以访问文件:

*

• 我这样更改了 File1.fmt ，将 cutomer列的长度从7更改为100，并且将 age列的长度更改了从100到7:

  14.031 SQLCHAR 0 100，"1位客户"2 SQLCHAR 0 7，"2岁SQL_Latin1_General_CP1_CI_AS3 SQLCHAR 0 100"\ r \ n";3个性别" 

3. 我使用以下语句进行查询:

  SELECT * FROM OPENROWSET(BULK'bs140513_032310-demo.csv'，DATA_SOURCE ='MyAzureInvoices'，格式="CSV"，FORMATFILE ='File1.fmt'，FORMATFILE_DATA_SOURCE ='MyAzureInvoices')AS DataFile; 

结果显示:


4. 不要直接批量插入到您的真实表中.
• 我总是将CSV文件中的ext.customer_Staging(没有IDENTITY列)插入到临时表中
• 可能会编辑/清理/处理您导入的数据
• 然后使用T-SQL语句将数据复制到真实表中:

 使用(TABLOCK)(客户，年龄，性别)插入ext.customer_Staging选择*从OPENROWSET(BULK'bs140513_032310-demo.csv'，DATA_SOURCE ='MyAzureInvoices'，格式="CSV"，FORMATFILE ='File1.fmt'，FORMATFILE_DATA_SOURCE ='MyAzureInvoices')AS DataFile;去将INSERT INTO ext.customer(名称，地址)选择客户，年龄，性别从ext.customer_Staging 

I am trying to bulk load in azure sql database some data in an azure blob storage. The file content is:

 customer,age,gender
'C1093826151','4','M'
'C352968107','2','M'
'C2054744914','4','F'

the file is in a container called silver. in the silver container I have the File1.fmt which content is:

14.0  
3
1       SQLCHAR       0       7       ","      1     customer       ""  
2       SQLCHAR       0       100     ","      2     age            SQL_Latin1_General_CP1_CI_AS 
3       SQLCHAR       0       100     "\r\n"   3     gender         SQL_Latin1_General_CP1_CI_AS

I have the extra line add the end of the fmt file.

I have created a SAS token will all enabled and allowed like the screenshot below:

The firewall rules on datalake are as the picture below:

Below are my sql scripts (I removed the ? at the beginning of the SAS token, as my silver container is public, I know I should need the SAS token):

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'safepassword';
go
DROP EXTERNAL DATA SOURCE MyAzureInvoices

DROP DATABASE SCOPED CREDENTIAL UploadInvoices

CREATE DATABASE SCOPED CREDENTIAL UploadInvoices
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 'sv=2019-12-12**********************************88%3D'; -- dl

--DROP EXTERNAL DATA SOURCE MyAzureInvoices

CREATE EXTERNAL DATA SOURCE MyAzureInvoices
    WITH (
        TYPE = BLOB_STORAGE,
        LOCATION = 'https://mydatalake.blob.core.windows.net/silver',
        CREDENTIAL = UploadInvoices
    );

Landing table:

CREATE TABLE [ext].[customer](
    [customer_id] [int] IDENTITY(1,1) NOT NULL,
    [customer] [varchar](100) NOT NULL,
    [age] [int] NOT NULL,
    [gender] [varchar](50) NOT NULL
) ON [PRIMARY]
GO

and these are the ways I tried to load the file into the sql database:

-- 1
    SELECT * FROM OPENROWSET(
   BULK 'bs140513_032310-demo.csv',
   DATA_SOURCE = 'MyAzureInvoices',
   FORMAT = 'CSV',
   FORMATFILE='File1.fmt',
   FORMATFILE_DATA_SOURCE = 'MyAzureInvoices'
   ) AS DataFile;   
-- 2    
    go
    SELECT * FROM OPENROWSET(
   BULK 'bs140513_032310-demo.csv',
   DATA_SOURCE = 'MyAzureInvoices',
   SINGLE_CLOB) AS DataFile;
   go
-- 3
BULK INSERT ext.customer
FROM 'bs140513_032310-demo.csv'
WITH (
DATA_SOURCE = 'MyAzureInvoices', FORMAT = 'CSV' );

They all give the same error:

Msg 4861, Level 16, State 1, Line 2
Cannot bulk load because the file "bs140513_032310-demo.csv" could not be opened. Operating system error code 5(Access is denied.).

I have tried for 3 days and I am lost. Thanks for your help NB:

While being disconnected, it can access the files:

*

• https://mydatalake.blob.core.windows.net/silver/File1.fmt https://mydatalake.blob.core.windows.net/silver/bs140513_032310-demo.csv

mydatalake is fake, but I can access with the real name

解决方案

I think this error message is misleading.
I've created a same test as you, and encountered the same error.
But after I edited the bs140513_032310-demo.csv and File1.fmt, it works well.

1. I changed the bs140513_032310-demo.csv like this:

2. I changed the File1.fmt like this, I changed the cutomer column length from 7 to 100 and age column length from 100 to 7 :

14.0  
3
1       SQLCHAR       0       100       ","      1     customer       ""
2       SQLCHAR       0       7         ","      2     age            SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR       0       100       "\r\n"   3     gender         ""

3. I use the following statement to query:

   SELECT * FROM OPENROWSET(
   BULK 'bs140513_032310-demo.csv',
   DATA_SOURCE = 'MyAzureInvoices',
   FORMAT = 'CSV',
   FORMATFILE='File1.fmt',
   FORMATFILE_DATA_SOURCE = 'MyAzureInvoices'
   ) AS DataFile; 

The result shows:


4. Don't BULK INSERT into your real tables directly.
• I would always insert into a staging table ext.customer_Staging (without the IDENTITY column) from the CSV file
• possibly edit / clean up / manipulate your imported data
• and then copy the data across to the real table with a T-SQL statement like:

INSERT into  ext.customer_Staging with (TABLOCK) (customer, age, gender)
   SELECT * FROM OPENROWSET(
   BULK 'bs140513_032310-demo.csv',
   DATA_SOURCE = 'MyAzureInvoices',
   FORMAT = 'CSV',
   FORMATFILE='File1.fmt',
   FORMATFILE_DATA_SOURCE = 'MyAzureInvoices'
   ) AS DataFile;
   go

INSERT INTO ext.customer(Name, Address) 
   SELECT customer, age, gender
   FROM ext.customer_Staging

这篇关于Azure Blob到Azure SQL数据库:无法批量加载，因为文件"xxxx.csv"不能打开.操作系统错误代码5(访问被拒绝.)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！