normalian blog

I will introduce about Microsoft Azure, ASP.NET or Java EE

How to copy comma separated CSV files into Azure SQL Database with Azure Data Factory

This topic introduces how to copy CSV files data into SQL Database with Azure Data Factory. At first, install SSMS from Download SQL Server Management Studio (SSMS) . After setting up SSMS, follow below step to copy CSV files data into SQL Database.

  • Upload CSV files into Azure Storage
  • Create a table in your SQL Database
  • Copy CSV files into your SQL Database with Azure Data Factory

Upload "USDJPY.csv" file into your Azure Storage from http://www.m2j.co.jp/market/historical.php if you haven't follow the topic and it will probably work.

Create a table in your SQL Database

You have to setup your SQL Database instance if you don't have it. After creating that, setup firewall following Azure SQL Database server-level and database-level firewall rules to access with "sqlcmd" command from your computer.
After setting up your SQL Database, execute below command from your client computer. Note to execute below with one liner, because I just add line breaks to read easily.

normalian> sqlcmd.exe -S "server name".database.windows.net -d "database name" -U "username"@"server name" -P "password" -I -Q 
"CREATE TABLE [dbo].[USDJPY]
(
    [ID] INT NOT NULL PRIMARY KEY IDENTITY(1,1), 
    [DATE] DATETIME NOT NULL, 
    [OPEN] FLOAT NOT NULL, 
    [HIGH] FLOAT NOT NULL, 
    [LOW] FLOAT NOT NULL, 
    [CLOSE] FLOAT NOT NULL
)"

You can remove the table using below command if you mistake something for this setting.

normalian> sqlcmd.exe -S "server name".database.windows.net -d "database name" -U "username"@"server name" -P "password" -I -Q "DROP TABLE [dbo].[USDJPY]"

Copy CSV files into your SQL Database with Azure Data Factory

At first, create your Azure Data Factory instance. And choose "Copy data(PREVIEW)" button like below.
f:id:waritohutsu:20170904232426p:plain

Next, choose "Run once now" to copy your CSV files.
f:id:waritohutsu:20170904232454p:plain

Choose "Azure Blob Storage" as your "source data store", specify your Azure Storage which you stored CSV files.
f:id:waritohutsu:20170904232523p:plain
f:id:waritohutsu:20170904232551p:plain

Choose your CSV files from your Azure Storage.
f:id:waritohutsu:20170904232635p:plain

Choose "Comma" as your CSV files delimiter and input "Skip line count" number if your CSV file has headers.
f:id:waritohutsu:20170904232717p:plain

Choose "Azure SQL Database" as your "destination data store".
f:id:waritohutsu:20170904232750p:plain

Input your "Azure SQL Database" info to specify your instance.
f:id:waritohutsu:20170904232823p:plain

Select your table from your SQL Database instance.
f:id:waritohutsu:20170904232852p:plain

Check your data mapping.
f:id:waritohutsu:20170904232921p:plain

Execute data copy from CSV files to SQL Database just confirming next wizards.
f:id:waritohutsu:20170904232958p:plain

After completing this pipeline, execute below command in your machine. You can get data from SQL Database.

normalian> sqlcmd.exe -S "server name".database.windows.net -d "database name" -U "username"@"server name" -P "password" -I -Q "SELECT * FROM [dbo].[USDJPY] ORDER BY 1;"

You can get some data from SQL Database if you have setup correctly.