如何通过Azure PowerShell Runbook将MDX查询结果存入Azure存储账户

一、生成CSV并上传到Azure存储 #

你可以直接将XML解析后的对象转换为CSV内容，无需保存到本地文件，通过Set-AzStorageBlobContent的-Content参数直接上传内容到Blob存储。以下是修改后的完整脚本：

Param
(
  [Parameter (Mandatory= $true)]
  [String] $Query,
  [Parameter (Mandatory= $true)]
  [String] $OperationType,
  [Parameter (Mandatory= $true)]
  [String] $ServerName,
  [Parameter (Mandatory= $true)]
  [String] $DatabaseName,
  [Parameter (Mandatory= $true)]
  [String] $SecretName,
  [Parameter (Mandatory= $true)]
  [String] $ResourceGroupName,
  [Parameter (Mandatory= $true)]
  [String] $StorageAccountName,
  [Parameter (Mandatory= $true)]
  [String] $ContainerName,
  [Parameter (Mandatory= $true)]
  [String] $BlobPath # 例如："output/partitions.csv"，包含文件夹路径和文件名
)

$cred = Get-AutomationPSCredential -Name $SecretName

# 执行MDX查询并解析为XML
$Results = [XML] (Invoke-ASCmd -Server $ServerName -Database $DatabaseName -Credential $cred -Query $Query)

# 将XML行数据转换为PS自定义对象（自动适配任意列数）
$rowObjects = $Results.return.root.row | ForEach-Object {
    $properties = @{}
    foreach ($attribute in $_.Attributes) {
        $properties[$attribute.Name] = $attribute.Value
    }
    [PSCustomObject]$properties
}

# 生成CSV内容（无类型信息）
$csvContent = $rowObjects | ConvertTo-Csv -NoTypeInformation

# 连接Azure并获取存储上下文
try {
    $AzureContext = (Connect-AzAccount -Identity).context
}
catch{
    Write-Output "无系统分配的用户标识，终止操作。"; 
    exit
}

$storageAccount = Get-AzStorageAccount -ResourceGroupName $ResourceGroupName -Name $StorageAccountName
$storageContext = $storageAccount.Context

# 直接将CSV内容上传到Blob存储
Set-AzStorageBlobContent -Context $storageContext -Container $ContainerName -Blob $BlobPath -Content ($csvContent -join "`n") -Force

Write-Output "CSV文件已成功上传到存储账户：$StorageAccountName，容器：$ContainerName，路径：$BlobPath"

二、生成Parquet并上传到Azure存储 #

如果需要生成Parquet文件，需要依赖Parquet.Net模块。由于Azure自动化Runbook中需要先导入该模块，步骤如下：

1. 在Azure自动化账户中导入模块：

   • 进入Azure自动化账户 → 左侧菜单「模块」→ 「浏览库」，搜索Parquet.Net并导入

2. 修改脚本实现Parquet生成与上传：

Param
(
  [Parameter (Mandatory= $true)]
  [String] $Query,
  [Parameter (Mandatory= $true)]
  [String] $OperationType,
  [Parameter (Mandatory= $true)]
  [String] $ServerName,
  [Parameter (Mandatory= $true)]
  [String] $DatabaseName,
  [Parameter (Mandatory= $true)]
  [String] $SecretName,
  [Parameter (Mandatory= $true)]
  [String] $ResourceGroupName,
  [Parameter (Mandatory= $true)]
  [String] $StorageAccountName,
  [Parameter (Mandatory= $true)]
  [String] $ContainerName,
  [Parameter (Mandatory= $true)]
  [String] $BlobPath # 例如："output/partitions.parquet"
)

$cred = Get-AutomationPSCredential -Name $SecretName

$Results = [XML] (Invoke-ASCmd -Server $ServerName -Database $DatabaseName -Credential $cred -Query $Query)

# 转换为PS对象
$rowObjects = $Results.return.root.row | ForEach-Object {
    $properties = @{}
    foreach ($attribute in $_.Attributes) {
        $properties[$attribute.Name] = $attribute.Value
    }
    [PSCustomObject]$properties
}

# 加载Parquet.Net模块
Import-Module Parquet.Net

# 将对象转换为Parquet字节流
$memoryStream = New-Object System.IO.MemoryStream
$writer = [Parquet.ParquetWriter]::Create($rowObjects[0].PSObject.Properties.Name, $memoryStream)
$writer.WriteGroup($rowObjects | ForEach-Object {
    $group = [Parquet.Data.DataGroup]::New($writer.Schema)
    foreach ($prop in $_.PSObject.Properties) {
        $group.Add($prop.Value)
    }
    $group
})
$writer.Close()
$memoryStream.Position = 0

# 连接Azure并上传
try {
    $AzureContext = (Connect-AzAccount -Identity).context
}
catch{
    Write-Output "无系统分配的用户标识，终止操作。"; 
    exit
}

$storageAccount = Get-AzStorageAccount -ResourceGroupName $ResourceGroupName -Name $StorageAccountName
$storageContext = $storageAccount.Context

Set-AzStorageBlobContent -Context $storageContext -Container $ContainerName -Blob $BlobPath -Stream $memoryStream -Force

Write-Output "Parquet文件已成功上传到存储账户：$StorageAccountName，容器：$ContainerName，路径：$BlobPath"

关键说明 #

• 两种方案均无需本地文件，直接在内存中处理内容后上传，适配Azure Runbook的无本地存储环境
• XML转PS对象的逻辑会自动适配查询返回的任意列数，无需修改代码
• Parquet方案需要确保自动化账户中已导入Parquet.Net模块，若模块导入失败可尝试手动上传NuGet包并导入

内容的提问来源于stack exchange，提问作者Josh Sama