天蓝色表存储将数据导出到平面文件或XML文件以进行SQL [英] azure table storage Export Data to Flat or XML File for SQL

问题描述

我正在寻找将数据从 SQL Azure Azure表存储导出到某些平面文件或XML文件的功能，以便我们可以将其导入SQL/MYSQL Server并进行数据分析./p>

发生的事情是，我们每小时从文本API 中获得大量交易(分区策略是每小时一次)，因为Azure表存储不支持count和Sum.因此，出于数据分析的目的，我们希望将数据导出到平面文件，然后导入到SQL Server/MySQL进行分析.

问题:

1. 任何util或Tutorial如何将数据从Azure表存储导出到平面文件?按分区进行分区又快吗?任何示例代码或实用程序?有经过验证的示例/教程吗?

2. MS SQL和Azure Table，任何可以每小时自动提取数据并将数据加载到SQL Server的工具或实用程序吗?微软在路线图上是否提供任何此类提供或导出工具?

任何支持将不胜感激.

解决方案

如果您正在寻找一种将数据从Azure表存储导出到平面文件的工具，我建议您看看Cerebrata的 Azure Management Studio (商业用途，并非免费)或派生的类TableEntity 类.如下所示:

 公共类CustomEntity:TableEntity{公共字符串Attribute1{得到;放;}公共字符串Attribute2{得到;放;}公共字符串AttributeN{得到;放;}公共静态字符串GetHeaders(字符串定界符){返回"\" Attribute1 \" +分隔符+"\" Attribute2 \" +分隔符+"\" AttributeN \";}公共字符串ToDelimited(字符串定界符){返回"\""+ Attribute1 +" \" +分隔符+"\""+ Attribute2 +" \" +分隔符+"\""+ AttributeN +" \";}} 

然后您的应用程序可以每小时查询一次表存储并将数据保存到文件中

  DateTime currentDateTime = DateTime.UtcNow;//假设PartitionKey遵循以下命名策略:YYYYMMDDHH0000var fromPartitionKey = currentDateTime.AddHours(-1).ToString("YYYYmmDDHH0000");var toPartitionKey = currentDateTime.ToString("YYYYmmDDHH0000");var filterExpression = string.Format("PartitionKey ge'{0}'and PartitionKey lt'{1}'"，fromPartitionKey，toPartitionKey);var tableName =<您的表名>";var cloudStorageAccount = new CloudStorageAccount(new StorageCredentials(< account name>"，< account key>")，true);var cloudTableClient = cloudStorageAccount.CreateCloudTableClient();var table = cloudTableClient.GetTableReference(tableName);TableQuery< CustomEntity>查询=新的TableQuery< CustomEntity>(){FilterString = filterExpression，};var实体= table.ExecuteQuery< CustomEntity>(query).ToList();如果(entities.Count> 0){StringBuilder sb = new StringBuilder();sb.Append(CustomEntity.GetHeaders(，")+"\ n");foreach(实体中的var实体){sb.Append(entity.ToDelimited(，")+"\ n");}var fileContents = sb.ToString();//现在将此字符串写入文件.} 

就将这些数据导入关系数据库而言，我敢肯定，如果环顾四周，您会发现许多能够做到这一点的实用程序.

I am looking for capability to Export data from SQL Azure Azure Table Storage to Some Flat file or XML file, so that we can import that to SQL / MYSQL Server and do the data analysis.

What happens is we get lot of transaction from our text API per hour, ( Partition Strategy is hourly), since Azure table storage doesn't support the count and Sum. So for data analysis puropose we want to export the data to Flat Files and import to SQL Server/MySQL to analyze it.

Question:

1. Any util or Tutorial how to export data from Azure table storage to Flat file? Partition by Patition basis and fast? Any sample code or Utility? Any proven example / tutorial?

2. MS SQL and Azure Table, any tool or utility which can automatically extract and load data to SQL sever every hour? Any such offering or export tool on road map by Microsoft?

Any support will be highly appreciated.

解决方案

If you're looking for a tool to export data from Azure table storage to a flat file, may I suggest you take a look at Cerebrata's Azure Management Studio (Commercial, NOT Free) or ClumsyLeaf's TableXplorer (Commercial, NOT Free). Both of these tools have the capability to export data into CSV and XML file format.

Since both of the tools are GUI based, I don't think you can automate the export process. For automation, I would suggest you look into Cerebrata's Azure Management Cmdlets as it provides a PowerShell based interface to export data into CSV or XML format.

Since I was associated with Cerebrata in the past, I can only talk about that. The tool won't export on a partition-by-partition basis but if you know the all the PartitionKey values in your table, you can specify a query to export data for each partition.

If automation is one of the key requirement, you could simply write a console application which runs once per hour and extracts the data for the past hour. You could use .Net Storage Client library to fetch the data. To do so, first define a class which derives from TableEntity class. Something like below:

public class CustomEntity : TableEntity
{
    public string Attribute1
    {
        get;
        set;
    }

    public string Attribute2
    {
        get;
        set;
    }

    public string AttributeN
    {
        get;
        set;
    }

    public static string GetHeaders(string delimiter)
    {
        return "\"Attribute1\"" + delimiter + "\"Attribute2\"" + delimiter + "\"AttributeN\"";
    }

    public string ToDelimited(string delimiter)
    {
        return "\"" + Attribute1 + "\"" + delimiter + "\"" + Attribute2 + "\"" + delimiter + "\"" + AttributeN + "\"";
    }
}

Then your application could query the table storage on an hourly basis and save the data to a file:

    DateTime currentDateTime = DateTime.UtcNow;
    //Assuming the PartitionKey follows the following strategy for naming: YYYYMMDDHH0000
    var fromPartitionKey = currentDateTime.AddHours(-1).ToString("YYYYmmDDHH0000");
    var toPartitionKey = currentDateTime.ToString("YYYYmmDDHH0000");
    var filterExpression = string.Format("PartitionKey ge '{0}' and PartitionKey lt '{1}'", fromPartitionKey, toPartitionKey);
    var tableName = "<your table name>";
    var cloudStorageAccount = new CloudStorageAccount(new StorageCredentials("<account name>", "<account key>"), true);
    var cloudTableClient = cloudStorageAccount.CreateCloudTableClient();
    var table = cloudTableClient.GetTableReference(tableName);
    TableQuery<CustomEntity> query = new TableQuery<CustomEntity>()
    {
        FilterString = filterExpression,
    };

    var entities = table.ExecuteQuery<CustomEntity>(query).ToList();
    if (entities.Count > 0)
    {
        StringBuilder sb = new StringBuilder();
        sb.Append(CustomEntity.GetHeaders(",") + "\n");
        foreach (var entity in entities)
        {
            sb.Append(entity.ToDelimited(",") + "\n");
        }
        var fileContents = sb.ToString();
        //Now write this string to a file.
    }

As far as importing this data into a relational database, I'm pretty sure if you look around you'll find many utilities which will be able to do that.

这篇关于天蓝色表存储将数据导出到平面文件或XML文件以进行SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！