Friday, March 8, 2019

Import - export data using Azure blob storage and bcp utility


Skip to end of metadata

Example of the export data file, generated using BCP utility from a local SQL server, upload to Azure blob storage and import to Azure SQL server.
we will do it in the following steps :
  1. Export data from local SQL server 
  2. Create Azure blob storage
  3. Create an Azure blob storage container
  4. Upload exported files to blob storage
  5. Import data from uploaded azure blob storage data file

1. Export data from the local SQL server: 

Using BCP utility we can export.dat file and file format file.

bcp "SELECT Name, Color, Price, Size, Quantity, Data, Tags FROM Product" queryout product.dat -S (localdb)\MSSQLLocalDB -d MemEligibiltyTest -T
Here "product.dat"  is an output filename. 
-S  Sql server hostname
-d  Database Name
-T Trusted connection.
We need to provide a format for each field. bcp asks for each field like below.
Enter prefix-length of field Name 2: 8
Enter field terminator none: none
Enter the file storage type of field Color nvarchar: cr
Enter prefix-length of field Color 2: 8
Enter field terminator none: none
Enter the file storage type of field Price money: cr
Enter prefix-length of field Price 1: 8
Enter field terminator none: none
Enter the file storage type of field Size nvarchar: cr
Enter prefix-length of field Size 2: 8
Enter field terminator none: none
Enter the file storage type of field Quantity int-null: cr
Enter prefix-length of field Quantity 1: 8
Enter field terminator none: none
Enter the file storage type of field Data nvarchar: cr
Enter prefix-length of field Data 2: 8
Enter field terminator none: none
Enter the file storage type of field Tags nvarchar: cr
Enter prefix-length of field Tags 2: 8
Enter field terminator none:
Do you want to save this format information in a file? Y/n y
Host filename bcp.fmt: product.fmt
Starting copy...
4 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total: 15 Average : (266.67 rows per sec.)

As I have provided prefix -length 8 for each field, generated files keeps 8 characters readable instead of binary character. You can see the rest of the characters are in binary format.

It creates the following two files
This creates two files product.dat and product.fmt. 1st is the data file and another is the format file.
product.dat looks like below.
format file.

2. Create Azure blob storage

we will use Powershell for azure connection.
Azure PowerShell works with PowerShell 5.1 or higher on Windows or PowerShell 6 on any platform. To check your PowerShell version, run the command:
$PSVersionTable.PSVersion
You can install Powershell Azure module using the following command
Install-Module -Name Az -AllowClobber

Connect to Azure with a browser sign in token
Connect-AzAccount
o/p:
Account SubscriptionName TenantId Environment
------- ---------------- -------- -----------
Atulxxxxxxx@xxxxxx.com ABCD - Development dxxxxxx3-xxxx-xxxx-xxxx-xxxxxxxxxxxx AzureCloud
As you logged in to Azure and it shows the default subscription. If you want to execute everything in another subscription then need to select that subscription.
You can get an Azure subscription by subscription id using the following command.
PS C:\Windows\system32> $context = Get-AzSubscription -subscriptionId xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx
You can store output to a variable and use same variable to set Azure context to select particular azure subscription.
command:
PS C:\Windows\system32> set-AzContext $context
o/p:
Name Account SubscriptionName Environment TenantId
---- ------- ---------------- ----------- --------
ABCD  (xxxxxxxx-xxxx-xxx... Atul.Patel@xxxx... ABCD AzureCloud xxxxxxxx-xxxx-x...
Define a variable for the resource group.
$resourceGroupName="your-resource-group-name"
$blobStorageAccountName="yourblobstorageaccountname"
You can create a New Azure storage account using the following command. You can store azure account context to a variable.
PS C:\Windows\system32> $storageAccount=New-AzStorageAccount -ResourceGroup $resourceGroupName -Name $blobStorageAccountName -skuname Standard_LRS -Location WestUS2
o/p:
StorageAccountName ResourceGroupName Location SkuName Kind AccessTier CreationTime ProvisioningState
------------------ ----------------- -------- ------- ---- ---------- ------------ -----------
yourblobstorageaccountname your-resource-group-name westus2 StandardLRS Storage 3/5/2019 8:55:10 PM Succeeded
If  you already have a azure storage account then you can get it using following command.
PS C:\Windows\system32> $storageAccount=Get-AzStorageAccount -ResourceGroup $resourceGroupName -Name $blobStorageAccountName
PS C:\Windows\system32> $storageAccount
o/p:
StorageAccountName ResourceGroupName Location SkuName Kind AccessTier CreationTime ProvisioningState
------------------ ----------------- -------- ------- ---- ---------- ------------ -----------
yourblobstorageaccountname your-resource-group-name westus2 StandardLRS Storage 3/5/2019 8:55:10 PM Succeeded

3. Create an Azure blob storage container

Storing storage account context into variable
$ctx=$storageAccount.Context

-- Variable for Container name
$containerName="yourcontainername"

 Create new container using following command:
new-AzStorageContainer -Name $containerName -Context $ctx

Name PublicAccess LastModified
---- ------------ ------------
blogstoragename Off 3/5/2019 8:59:56 PM +00:00
context looks like below:

4. Upload exported files to blob storage

You can use the same Azure blob storage container context to upload files.
command:
set-AzStorageblobcontent -File "C:\Users\atul.patel\product.fmt" `
-Container $containerName `
-Blob "product.fmt" `
-Context $ctx

o/p:
Name BlobType Length ContentType LastModified AccessTier SnapshotTime IsDeleted
---- -------- ------ ----------- ------------ ---------- ------------ ---------
product.fmt BlockBlob 755 application/octet-stream 2019-03-05 21:37:54Z Unknown False

Another file.
set-AzStorageblobcontent -File "C:\Users\atul.patel\product.dat" `
-Container $containerName `
-Blob "product.dat" `
-Context $ctx
o/p:
Name BlobType Length ContentType LastModified AccessTier SnapshotTime IsDeleted
---- -------- ------ ----------- ------------ ---------- ------------ ---------
product.dat BlockBlob 456 application/octet-stream 2019-03-05 21:38:08Z Unknown False

Select list of upload files on Azure blob storage container.
PS C:\Windows\system32> Get-AzStorageBlob -Container $containerName -Context $ctx | select Name
Name
----
product.dat
product.fmt

5.Import data from uploaded azure blob storage data file


** Import data into Sql server db from azure blob storage

-- Import into DB
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'XXXXXXXXxxxxxxxxxxxxXXX'

--  Create credential with Azure Blob SAS

CREATE DATABASE SCOPED CREDENTIAL youcredentialname
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 'sv=2018-03-28&ss=bfqt&srt=sco&sp=xxxxxxxxxx&se=2019-03-06T23:30:14Z&st=2019-03-06T15:30:14Z&spr=https&sig=xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx';
-- NOTE: DO NOT PUT FIRST CHARACTER '?'' IN SECRET!!!

-- Create external data source
CREATE EXTERNAL DATA SOURCE blogstoragename
WITH ( TYPE = BLOB_STORAGE,
LOCATION = 'https://yourblobstorageaccountname.blob.core.windows.net/blogstoragename',
CREDENTIAL= youcredentialname --> CREDENTIAL is not required if a blob storage is public!
);

--Create table

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
)

BULK INSERT Product
FROM 'product.dat'
WITH ( DATA_SOURCE = 'blogstoragename',
FORMATFILE='product.fmt',
FORMATFILE_DATA_SOURCE = 'blogstoragename',
TABLOCK);
--Just select
SELECT Name, Color, Price, Size, Quantity, Data, Tags
FROM OPENROWSET(BULK 'product.dat',
DATA_SOURCE = 'blogstoragename',
FORMATFILE='product.fmt',
FORMATFILE_DATA_SOURCE = 'blogstoragename') as products;


References:

Posted by at 3 comments:
Labels: , , ,
Subscribe to: Posts (Atom)