Azure Retail Prices REST API 分析全 Azure 服务价格差异 - JSON 转 Excel版本

笔者有以下分析需求

想知道同样服务所有区域的价格从低到高
想知道同样服务那些区域有支援

这边可以使用 Azure Retail Prices REST API 达到目的

Azure 上取得服务单价资料方式只有一个 API : https://prices.azure.com/api/retail/prices，返回的资料结构 :

{
   "BillingCurrency":"USD",
   "CustomerEntityId":"Default",
   "CustomerEntityType":"Retail",
   "Items":[
      {
         "currencyCode":"USD",
         "tierMinimumUnits":0.0,
         "retailPrice":10.56,
         "unitPrice":10.56,
         "armRegionName":"uksouth",
         "location":"UK South",
         "effectiveStartDate":"2018-11-01T00:00:00Z",
         "meterId":"0001d427-82df-4d83-8ab2-b60768527e08",
         "meterName":"E10 Disks",
         "productId":"DZH318Z0BP88",
         "skuId":"DZH318Z0BP88/001V",
         "productName":"Standard SSD Managed Disks",
         "skuName":"E10 LRS",
         "serviceName":"Storage",
         "serviceId":"DZH317F1HKN0",
         "serviceFamily":"Storage",
         "unitOfMeasure":"1/Month",
         "type":"Consumption",
         "isPrimaryMeterRegion":true,
         "armSkuName":""
      }
   ],
   "NextPageLink":"https://prices.azure.com:443/api/retail/prices?$skip=100",
   "Count":100
}

因为 Azure 上服务太多了，资料量太大，所以此 API 每个请求只能返回 100 笔资料，并提供 NextPageLink 属性给下 100 笔资料的 URL。

藉此笔者这边简单写一个 C# 下载脚本，如下
大约完整下载需要一段时间，所以提供抓完的资料 : 2020-10-12-AzurePrices.json (79MB，135101笔资料) 给读者参考。

void Main()
{
	var client = new HttpClient();
	var list = new List<Price>();
	var i = 0;
	while(true))
	{
		var url = $"https://prices.azure.com/api/retail/prices?$skip={i*100}";
		var content = client.GetStringAsync(url).Result;
		var data = JsonConvert.DeserializeObject<Retail>(content);
		
		// 如果没有下100资料，NextPageLink 会是 null，停止继续分析
		// e.g : {"BillingCurrency":"USD","CustomerEntityId":"Default","CustomerEntityType":"Retail","Items":[],"NextPageLink":null,"Count":0}
		if(data.NextPageLink == null)
			break;
		list.AddRange(data.Items);
		i++;
		Console.WriteLine(i);
	}
	
	//将结果保存到 
	var path = Path.Combine(GetDownloadFolderPath(),"AzurePrices.json");
	File.WriteAllText(path,JsonConvert.SerializeObject(list));
}

string GetDownloadFolderPath()
{
	return Registry.GetValue(@"HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Explorer\Shell Folders", "{374DE290-123F-4565-9164-39C4925E467B}", String.Empty).ToString();
}

public class Price
{
	public string currencyCode { get; set; }
	public double tierMinimumUnits { get; set; }
	public double retailPrice { get; set; }
	public double unitPrice { get; set; }
	public string armRegionName { get; set; }
	public string location { get; set; }
	public DateTime effectiveStartDate { get; set; }
	public string meterId { get; set; }
	public string meterName { get; set; }
	public string productId { get; set; }
	public string skuId { get; set; }
	public string productName { get; set; }
	public string skuName { get; set; }
	public string serviceName { get; set; }
	public string serviceId { get; set; }
	public string serviceFamily { get; set; }
	public string unitOfMeasure { get; set; }
	public string type { get; set; }
	public bool isPrimaryMeterRegion { get; set; }
	public string armSkuName { get; set; }
	public string reservationTerm { get; set; }
}

public class Retail
{
	public string BillingCurrency { get; set; }
	public string CustomerEntityId { get; set; }
	public string CustomerEntityType { get; set; }
	public List<Price> Items { get; set; }
	public string NextPageLink { get; set; }
	public int Count { get; set; }
}

接着转成 csv 的脚本，这边有提供放在 Github 已转好档案 2020-10-12-AzurePrices.xlsx

void Main()
{
	var path = @"azure json Prices Json 路径";
	var json = File.ReadAllText(path);
	var dt = JsonConvert.DeserializeObject<DataTable>(json);
	dt.WriteToCsvFile(@"C:\Users\HanYang\Downloads\AzurePrices.csv");
}

public static class DataTableExtensions
{
	public static void WriteToCsvFile(this DataTable dataTable, string filePath)
	{
		StringBuilder fileContent = new StringBuilder();

		foreach (var col in dataTable.Columns)
		{
			fileContent.Append(col.ToString() + ",");
		}

		fileContent.Replace(",", System.Environment.NewLine, fileContent.Length - 1, 1);

		foreach (DataRow dr in dataTable.Rows)
		{
			foreach (var column in dr.ItemArray)
			{
				fileContent.Append("\"" + column.ToString() + "\",");
			}

			fileContent.Replace(",", System.Environment.NewLine, fileContent.Length - 1, 1);
		}

		System.IO.File.WriteAllText(filePath, fileContent.ToString());
	}
}

接着就能做查询动作 :