在Azure数据库中导入BCP文件 [英] Importing a BCP file in Azure database

问题描述

我有一个Azure函数，该函数检索一个包含多个BCP文件的zip文件，然后将其解压缩并将其添加为blob.

I have an Azure function that retrieves a zip file that contains multiple BCP files which unzips them and adds them as blobs.

我现在想将BCP文件导入到我的SQL数据库中，但不确定如何处理.我知道我可以使用以下脚本并运行SqlCommand:

I now want to import the BCP files into my SQL database but not sure how to go about it. I know I can use following script and run an SqlCommand:

BULK INSERT RegPlusExtract.dbo.extract_class
FROM 'D:\local\data\extract_class.bsp'
WITH ( FIELDTERMINATOR = '@**@',ROWTERMINATOR = '*@@*')

但这显然不起作用，因为SQL Server无法访问本地函数的D:驱动器.

But this obviously does not work as the SQL server doesn't have access to the local function's D: drive.

我应该如何加载数据?是否可以将BCP文件加载到内存中，然后传递SQLCommand?还是可以将文件直接传递到SQL Server?

How should I go about loading the data? Is it possible to load the BCP file into memory and then pass the SQLCommand? Or can I pass the file direct to SQL server?

我发现对于备份/还原，我可以执行FROM URL = ''.如果我可以将其用于批量插入，那么我只能引用Blob网址，但看起来不行吗?

I've found out that for backup/restore I can do FROM URL = ''. If I could use this for bulk insert then I can just reference the blob url, but doesn't look like I can?

推荐答案

您将需要使用BLOB存储.下面是这些步骤，这些内容在此处记录

You will need to use BLOB storage..below are the steps and these are documented here Microsoft/sql-server-samples

-创建外部数据源

CREATE EXTERNAL DATA SOURCE MyAzureBlobStorage
WITH (  TYPE = BLOB_STORAGE, 
        LOCATION = 'https://sqlchoice.blob.core.windows.net/sqlchoice/samples/load-from-azure-blob-storage', 
--      CREDENTIAL= MyAzureBlobStorageCredential    --> CREDENTIAL is not required if a blob storage is public!
);

您还可以将文件上传到容器中，并按如下所示进行引用.以下第3周是容器

You also can upload files to a container and reference it like below.Here week3 is a container

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

现在您可以像下面那样使用OpenRowset和BulkInsert

Now you can use OpenRowset and BulkInsert like below

-- 2.1. INSERT CSV file into Product table
BULK INSERT Product
FROM 'product.csv'
WITH (  DATA_SOURCE = 'MyAzureBlobStorage',
        FORMAT='CSV', CODEPAGE = 65001, --UTF-8 encoding
        FIRSTROW=2,
        TABLOCK); 

-- 2.2. INSERT file exported using bcp.exe into Product table
BULK INSERT Product
FROM 'product.bcp'
WITH (  DATA_SOURCE = 'MyAzureBlobStorage',
        FORMATFILE='product.fmt',
        FORMATFILE_DATA_SOURCE = 'MyAzureBlobStorage',
        TABLOCK); 

-- 2.3. Read rows from product.dat file using format file and insert it into Product table
INSERT INTO Product WITH (TABLOCK) (Name, Color, Price, Size, Quantity, Data, Tags) 
SELECT Name, Color, Price, Size, Quantity, Data, Tags
FROM OPENROWSET(BULK 'product.bcp',
                DATA_SOURCE = 'MyAzureBlobStorage',
                FORMATFILE='product.fmt',
                FORMATFILE_DATA_SOURCE = 'MyAzureBlobStorage') as products; 

-- 2.4. Query remote file 
SELECT Color, count(*)
FROM OPENROWSET(BULK 'product.bcp',
                DATA_SOURCE = 'MyAzureBlobStorage',
                FORMATFILE='data/product.fmt',
                FORMATFILE_DATA_SOURCE = 'MyAzureBlobStorage') as data
GROUP BY Color;

这篇关于在Azure数据库中导入BCP文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！