从FTP完全/增量加载数据的最佳实践-CSV文件到Azure SQL数据库 [英] Best practices to Full/ Incremental load data from FTP - csv files into azure sql database

问题描述

您好，专家，
我计划使用azure sql数据库(注册订阅)建立我的公司数据仓库，并使用Power BI可视化数据.我目前的情况是有2个来源生成csv和ftp文件，并将它们存储在本地网络上.

这是我建议的步骤:

1.源系统将csv/ftp文件导出到ftp服务器

2.使用Azure Data Factory将csv/ftp文件加载到Azure SQL数据库中

3. Power BI连接到Azure数据库以可视化数据(完成)

我担心的是:

1.我不知道该使用哪种工具任务2(任务1将由IT团队处理)

2.在使用第2步时，我需要准备一个本地数据库来存储文件，然后再使用所选工具将数据从该服务器加载到我的Azure sql吗?

我需要帮助来建议哪种情况是将数据(完全加载和增量加载)加载到Azure SQL数据库的最佳方法.任何人都可以有这样做的经验，请帮助给我一些最佳实践的建议.感谢任何评论！

解决方案

你好，

https://blog.portiva.nl/2017/08/28/azure-runbook-export-csv-direct-to-ftp/

https://khangtran.wordpress.com/2017/06/12/automation-with-azure-data-factory- adf/

https://msdn.microsoft.com/en-us/library/dn879362.aspx?f=255&MSPPError=-2147217396

拥有datetime列可能有助于实现增量数据加载.

https://blogs.msdn.microsoft.com/bigdatasupport/2016/01/23/incremental-数据从天蓝色表存储到天蓝色sql使用天蓝色数据- factory/ 一个>

SQLCoffee.com

Hi experts,
I am planing to build my company data warehouse using azure sql database( registered the subscription) and visualize data with Power BI as well. My current situation is have 2 sources that generate csv and ftp files and store them on local network. 

This is my proposed steps:

1. Source systems export the csv/ftp files to ftp server

2. Load csv/ftp files to azure sql database by using Azure Data Factory 

3. Power BI connect to Azure database to visualize data ( done)

My concerns are:

1. I dont know exactly which tool to handle  task 2 ( task 1 will be handled by IT team)

2. At the step 2 will i need to prepare a on-premise database to store files before using the selected tool to load data from this server to my azure sql ?

I need help to advice which scenario is best way to load the data ( both full load and incremental load) to azure sql database. Could anyone has prior experience to do this, please help to advice me some best practices to do. Appreciate any comments!.  

解决方案

Hello,

You can use Azure runbook for step #1

https://blog.portiva.nl/2017/08/28/azure-runbook-export-csv-directly-to-ftp/

You can also use Azure Automation (runbooks) to automate Azure Data Factory tasks.

https://khangtran.wordpress.com/2017/06/12/automation-with-azure-data-factory-adf/

You may need to install the data management gateway to access on-premises data sources.

https://msdn.microsoft.com/en-us/library/dn879362.aspx?f=255&MSPPError=-2147217396

Having a datetime column may help achieve an incremental data load.

https://blogs.msdn.microsoft.com/bigdatasupport/2016/01/23/incremental-data-load-from-azure-table-storage-to-azure-sql-using-azure-data-factory/

Hope this helps.

Regards,

Alberto Morillo
SQLCoffee.com

这篇关于从FTP完全/增量加载数据的最佳实践-CSV文件到Azure SQL数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！