Edit

Get-AzStorageBlobQueryResult

  • Reference
Module:
Az.Storage

Applies a simple Structured Query Language (SQL) statement on a blob's contents and save only the queried subset of the data to a local file.

Note

This is the previous version of our documentation. Please consult the most recent version for up-to-date information.

Syntax

Get-AzStorageBlobQueryResult
   [-Blob] <String>
   [-Container] <String>
   [-SnapshotTime <DateTimeOffset>]
   [-VersionId <String>]
   -QueryString <String>
   -ResultFile <String>
   [-InputTextConfiguration <PSBlobQueryTextConfiguration>]
   [-OutputTextConfiguration <PSBlobQueryTextConfiguration>]
   [-PassThru]
   [-Force]
   [-Context <IStorageContext>]
   [-ServerTimeoutPerRequest <Int32>]
   [-ClientTimeoutPerRequest <Int32>]
   [-DefaultProfile <IAzureContextContainer>]
   [-ConcurrentTaskCount <Int32>]
   [-WhatIf]
   [-Confirm]
   [<CommonParameters>]
Get-AzStorageBlobQueryResult
   -BlobBaseClient <BlobBaseClient>
   -QueryString <String>
   -ResultFile <String>
   [-InputTextConfiguration <PSBlobQueryTextConfiguration>]
   [-OutputTextConfiguration <PSBlobQueryTextConfiguration>]
   [-PassThru]
   [-Force]
   [-Context <IStorageContext>]
   [-ServerTimeoutPerRequest <Int32>]
   [-ClientTimeoutPerRequest <Int32>]
   [-DefaultProfile <IAzureContextContainer>]
   [-ConcurrentTaskCount <Int32>]
   [-WhatIf]
   [-Confirm]
   [<CommonParameters>]
Get-AzStorageBlobQueryResult
   -BlobContainerClient <BlobContainerClient>
   [-Blob] <String>
   [-SnapshotTime <DateTimeOffset>]
   [-VersionId <String>]
   -QueryString <String>
   -ResultFile <String>
   [-InputTextConfiguration <PSBlobQueryTextConfiguration>]
   [-OutputTextConfiguration <PSBlobQueryTextConfiguration>]
   [-PassThru]
   [-Force]
   [-Context <IStorageContext>]
   [-ServerTimeoutPerRequest <Int32>]
   [-ClientTimeoutPerRequest <Int32>]
   [-DefaultProfile <IAzureContextContainer>]
   [-ConcurrentTaskCount <Int32>]
   [-WhatIf]
   [-Confirm]
   [<CommonParameters>]

Description

The Get-AzStorageBlobQueryResult cmdlet applies a simple Structured Query Language (SQL) statement on a blob's contents and save the queried subset of the data to a local file.

Examples

Example 1: Query a blob

PS C:\> $inputconfig = New-AzStorageBlobQueryConfig -AsCsv -HasHeader

PS C:\> $outputconfig = New-AzStorageBlobQueryConfig -AsJson

PS C:\> $queryString = "SELECT * FROM BlobStorage WHERE Name = 'a'"

PS C:\> $result = Get-AzStorageBlobQueryResult -Container $containerName -Blob $blobName -QueryString $queryString -ResultFile "c:\resultfile.json" -InputTextConfiguration $inputconfig -OutputTextConfiguration $outputconfig -Context $ctx

PS C:\> $result

BytesScanned FailureCount BlobQueryError
------------ ------------ --------------
         449            0

This command querys a blob succsssfully with input config as csv, and output config as json, and save the output to local file "c:\resultfile.json".

Example 2: Query a blob snapshot

PS C:\> $blob = Get-AzStorageBlob -Container $containerName -Blob $blobName -SnapshotTime "2020-07-29T11:08:21.1097874Z" -Context $ctx

PS C:\> $inputconfig = New-AzStorageBlobQueryConfig -AsCsv -ColumnSeparator "," -QuotationCharacter """" -EscapeCharacter "\" -RecordSeparator "`n" -HasHeader

PS C:\> $outputconfig = New-AzStorageBlobQueryConfig -AsJson -RecordSeparator "`n" 

PS C:\> $queryString = "SELECT * FROM BlobStorage WHERE _1 LIKE '1%%'"

PS C:\> $result = $blob | Get-AzStorageBlobQueryResult -QueryString $queryString -ResultFile $localFilePath -InputTextConfiguration $inputconfig -OutputTextConfiguration $outputconfig

PS C:\> $result

BytesScanned FailureCount BlobQueryError
------------ ------------ --------------
   187064971            1 {ParseError}  



PS C:\> $result.BlobQueryError

Name       Description                                                   IsFatal Position
----       -----------                                                   ------- --------
ParseError Unexpected token '1' at [byte: 3077737]. Expecting token ','.    True  7270632

This command first gets a blob object for blob snapshot, then queries the blob snapshot and show the result include query error.

Parameters

-Blob

Blob name

Type:String
Position:0
Default value:None
Accept pipeline input:False
Accept wildcard characters:False
-BlobBaseClient

BlobBaseClient Object

Type:BlobBaseClient
Position:Named
Default value:None
Accept pipeline input:True
Accept wildcard characters:False
-BlobContainerClient

BlobContainerClient Object

Type:BlobContainerClient
Position:Named
Default value:None
Accept pipeline input:True
Accept wildcard characters:False
-ClientTimeoutPerRequest

The client side maximum execution time for each request in seconds.

Type:Nullable<T>[Int32]
Aliases:ClientTimeoutPerRequestInSeconds
Position:Named
Default value:None
Accept pipeline input:False
Accept wildcard characters:False
-ConcurrentTaskCount

The total amount of concurrent async tasks. The default value is 10.

Type:Nullable<T>[Int32]
Position:Named
Default value:None
Accept pipeline input:False
Accept wildcard characters:False
-Confirm

Prompts you for confirmation before running the cmdlet.

Type:SwitchParameter
Aliases:cf
Position:Named
Default value:None
Accept pipeline input:False
Accept wildcard characters:False
-Container

Container name

Type:String
Position:1
Default value:None
Accept pipeline input:False
Accept wildcard characters:False
-Context

Azure Storage Context Object

Type:IStorageContext
Position:Named
Default value:None
Accept pipeline input:True
Accept wildcard characters:False
-DefaultProfile

The credentials, account, tenant, and subscription used for communication with Azure.

Type:IAzureContextContainer
Aliases:AzureRmContext, AzureCredential
Position:Named
Default value:None
Accept pipeline input:False
Accept wildcard characters:False
-Force

Force to overwrite the existing file.

Type:SwitchParameter
Position:Named
Default value:None
Accept pipeline input:False
Accept wildcard characters:False
-InputTextConfiguration

The configuration used to handled the query input text. Create configuration object the with New-AzStorageBlobQueryConfig.

Type:PSBlobQueryTextConfiguration
Position:Named
Default value:None
Accept pipeline input:False
Accept wildcard characters:False
-OutputTextConfiguration

The configuration used to handled the query output text. Create configuration object the with New-AzStorageBlobQueryConfig.

Type:PSBlobQueryTextConfiguration
Position:Named
Default value:None
Accept pipeline input:False
Accept wildcard characters:False
-PassThru

Return whether the specified blob is successfully queried.

Type:SwitchParameter
Position:Named
Default value:None
Accept pipeline input:False
Accept wildcard characters:False
-QueryString

Query string, see more details in: https://docs.microsoft.com/azure/storage/blobs/query-acceleration-sql-reference

Type:String
Position:Named
Default value:None
Accept pipeline input:False
Accept wildcard characters:False
-ResultFile

Local file path to save the query result.

Type:String
Position:Named
Default value:None
Accept pipeline input:False
Accept wildcard characters:False
-ServerTimeoutPerRequest

The server time out for each request in seconds.

Type:Nullable<T>[Int32]
Aliases:ServerTimeoutPerRequestInSeconds
Position:Named
Default value:None
Accept pipeline input:False
Accept wildcard characters:False
-SnapshotTime

Blob SnapshotTime

Type:Nullable<T>[DateTimeOffset]
Position:Named
Default value:None
Accept pipeline input:False
Accept wildcard characters:False
-VersionId

Blob VersionId

Type:String
Position:Named
Default value:None
Accept pipeline input:False
Accept wildcard characters:False
-WhatIf

Shows what would happen if the cmdlet runs. The cmdlet is not run.

Type:SwitchParameter
Aliases:wi
Position:Named
Default value:None
Accept pipeline input:False
Accept wildcard characters:False

Inputs

BlobBaseClient

BlobContainerClient

IStorageContext

Outputs

Boolean