从Blob中提取数据并将其加载到SQL Server中的表中，最简单的方法是什么? [英] What is the easiest way to pull data from a blob and load it into a table in SQL Server?
问题描述
我有数百个压缩文件位于不同的文件夹中，可以使用MS Storage Explorer访问这些文件.我只是在Azure中设置一个SQL Server数据库.现在，我试图弄清楚如何从每个文件夹中的每个文件中提取数据，将其解压缩，解析并将其加载到表中.数据每天都会到来，因此在该月的几天中，这些文件夹的名称分别为"1"，"2"，"3"等，以"31"表示.另外，在一年的12个月中，我每个月都有文件夹"1"到"12".最后，我有一个名为"2017"，"2018"和"2019"的文件夹.我可以请团队中的某些人编写Python代码来完成这项工作，但似乎应该有一种更简单的方法.如果有更简单的方法可以执行这种操作，则我想避免编写数千行Python代码.TIA.
I have hundreds of zipped files sitting in different folders, which I can access using MS Storage Explorer. I just setup a SQL Server DB in Azure. Now I am trying to figure out how I can pull data from each file in each folder, unzip it, parse it, and load it into tables. The data is coming in daily, so the folders are named '1', '2', '3', etc. '31', for the days of the month. Also, I have monthly folders '1' through '12', for the 12 months of the year. Finally, I have folders named '2017', '2018', and '2019'. I could ask some people on my team to write Python code to do the work, but it seems like there should be an easier way. I would like to avoid writing thousands of lines of Python code, if there is a simpler way to do this kind of thing. TIA.
推荐答案
我将创建Azure Logic应用
I would create Azure Logic App that would
- HTTP触发器(将手动运行)
- 遍历存储帐户中存在的所有blob
- 对于每个元素，请使用blob连接器，该连接器具有名为提取存档"的操作，可将压缩文件提取到blob中.
- 使用Azure SQL BCP将其提取，如果格式与CSV不同，则使用Azure数据工厂
稍后，如果您期望新的Blob执行类似的流程，则使用New或经过修改的Blob触发器.
Later if you expect new blobs do similar flow except use New or modified blob trigger.
对于带有逻辑应用的BLOB中的Azure SQL BCP示例，请检查此文章 https://marczak.io/posts/azure-loading-csv-to-sql/
For Azure SQL BCP from BLOB example with Logic Apps check this article https://marczak.io/posts/azure-loading-csv-to-sql/
如果您想全面介绍Azure Logic应用，请查看此视频 https://youtu.be/ZvsOzji_8ow在这里，还有一个新的Blob触发器演示.
And if you want general intro into Azure Logic Apps check this video https://youtu.be/ZvsOzji_8ow In here there is also new blob trigger demo.
如果您需要数据工厂，请随时查看此视频.它也对SQL演示有Blob.您也可以从逻辑应用程序触发数据工厂. https://youtu.be/EpDkxTHAhOs
In case you need data factory feel free to check this video. It has blob to sql demo too. Also you can trigger data factory from logic apps too. https://youtu.be/EpDkxTHAhOs
这篇关于从Blob中提取数据并将其加载到SQL Server中的表中，最简单的方法是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
