在Azure数据库中导入BCP文件

Question

我有一个Azure函数，它检索包含多个BCP文件的zip文件，这些文件将它们解压缩并添加为Blob。

我现在想将BCP文件导入我的SQL数据库，但不确定如何进行。我知道我可以使用以下脚本并运行SqlCommand：

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

但是这显然不起作用，因为SQL服务器没有访问本地函数的D：驱动器。

我应该如何加载数据？是否可以将BCP文件加载到内存中，然后传递SQL命令？或者我可以直接将文件传递到SQL服务器？

我发现，对于备份/恢复，我可以从URL=''执行。如果我可以将其用于批量插入，那么我可以只引用blob url，但看起来我不能？




Answer 1

下面是步骤，这些步骤在这里被记录下来

--创建外部数据源

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

您还可以将文件上载到容器中并参照它，如下所示。这里week3是一个容器

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

现在您可以使用OpenRowset和BulkInsert，如下所示

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