Get-AzStorageBlobQueryResult
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.
Syntax
NamePipeline (Default)
Get-AzStorageBlobQueryResult
[-Blob] <String>
[-Container] <String>
-QueryString <String>
-ResultFile <String>
[-SnapshotTime <DateTimeOffset>]
[-VersionId <String>]
[-InputTextConfiguration <PSBlobQueryTextConfiguration>]
[-OutputTextConfiguration <PSBlobQueryTextConfiguration>]
[-PassThru]
[-Force]
[-TagCondition <String>]
[-Context <IStorageContext>]
[-ServerTimeoutPerRequest <Int32>]
[-ClientTimeoutPerRequest <Int32>]
[-DefaultProfile <IAzureContextContainer>]
[-ConcurrentTaskCount <Int32>]
[-WhatIf]
[-Confirm]
[<CommonParameters>]
BlobPipeline
Get-AzStorageBlobQueryResult
-BlobBaseClient <BlobBaseClient>
-QueryString <String>
-ResultFile <String>
[-InputTextConfiguration <PSBlobQueryTextConfiguration>]
[-OutputTextConfiguration <PSBlobQueryTextConfiguration>]
[-PassThru]
[-Force]
[-TagCondition <String>]
[-Context <IStorageContext>]
[-ServerTimeoutPerRequest <Int32>]
[-ClientTimeoutPerRequest <Int32>]
[-DefaultProfile <IAzureContextContainer>]
[-ConcurrentTaskCount <Int32>]
[-WhatIf]
[-Confirm]
[<CommonParameters>]
ContainerPipeline
Get-AzStorageBlobQueryResult
[-Blob] <String>
-BlobContainerClient <BlobContainerClient>
-QueryString <String>
-ResultFile <String>
[-SnapshotTime <DateTimeOffset>]
[-VersionId <String>]
[-InputTextConfiguration <PSBlobQueryTextConfiguration>]
[-OutputTextConfiguration <PSBlobQueryTextConfiguration>]
[-PassThru]
[-Force]
[-TagCondition <String>]
[-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
$inputconfig = New-AzStorageBlobQueryConfig -AsCsv -HasHeader
$outputconfig = New-AzStorageBlobQueryConfig -AsJson
$queryString = "SELECT * FROM BlobStorage WHERE Name = 'a'"
$result = Get-AzStorageBlobQueryResult -Container $containerName -Blob $blobName -QueryString $queryString -ResultFile "c:\resultfile.json" -InputTextConfiguration $inputconfig -OutputTextConfiguration $outputconfig -Context $ctx
$result
BytesScanned FailureCount BlobQueryError
------------ ------------ --------------
449 0
This command queries a blob successfully 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
$blob = Get-AzStorageBlob -Container $containerName -Blob $blobName -SnapshotTime "2020-07-29T11:08:21.1097874Z" -Context $ctx
$inputconfig = New-AzStorageBlobQueryConfig -AsCsv -ColumnSeparator "," -QuotationCharacter """" -EscapeCharacter "\" -RecordSeparator "`n" -HasHeader
$outputconfig = New-AzStorageBlobQueryConfig -AsJson -RecordSeparator "`n"
$queryString = "SELECT * FROM BlobStorage WHERE _1 LIKE '1%%'"
$result = $blob | Get-AzStorageBlobQueryResult -QueryString $queryString -ResultFile $localFilePath -InputTextConfiguration $inputconfig -OutputTextConfiguration $outputconfig
$result
BytesScanned FailureCount BlobQueryError
------------ ------------ --------------
187064971 1 {ParseError}
$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
Parameter properties
Type: String
Default value: None Supports wildcards: False DontShow: False
Parameter sets
NamePipeline
Position: 0 Mandatory: True Value from pipeline: False Value from pipeline by property name: False Value from remaining arguments: False
ContainerPipeline
Position: 0 Mandatory: True Value from pipeline: False Value from pipeline by property name: False Value from remaining arguments: False
-BlobBaseClient
BlobBaseClient Object
Parameter properties
Type: BlobBaseClient
Default value: None Supports wildcards: False DontShow: False
Parameter sets
BlobPipeline
Position: Named Mandatory: True Value from pipeline: False Value from pipeline by property name: True Value from remaining arguments: False
-BlobContainerClient
BlobContainerClient Object
Parameter properties
Parameter sets
ContainerPipeline
Position: Named Mandatory: True Value from pipeline: False Value from pipeline by property name: True Value from remaining arguments: False
-ClientTimeoutPerRequest
The client side maximum execution time for each request in seconds.
Parameter properties
Type: Nullable<T> [ Int32 ]
Default value: None Supports wildcards: False DontShow: False Aliases: ClientTimeoutPerRequestInSeconds
Parameter sets
(All)
Position: Named Mandatory: False Value from pipeline: False Value from pipeline by property name: False Value from remaining arguments: False
-ConcurrentTaskCount
The total amount of concurrent async tasks.
The default value is 10.
Parameter properties
Type: Nullable<T> [ Int32 ]
Default value: None Supports wildcards: False DontShow: False
Parameter sets
(All)
Position: Named Mandatory: False Value from pipeline: False Value from pipeline by property name: False Value from remaining arguments: False
-Confirm
Prompts you for confirmation before running the cmdlet.
Parameter properties
Type: SwitchParameter
Default value: None Supports wildcards: False DontShow: False Aliases: cf
Parameter sets
(All)
Position: Named Mandatory: False Value from pipeline: False Value from pipeline by property name: False Value from remaining arguments: False
-Container
Container name
Parameter properties
Type: String
Default value: None Supports wildcards: False DontShow: False
Parameter sets
NamePipeline
Position: 1 Mandatory: True Value from pipeline: False Value from pipeline by property name: False Value from remaining arguments: False
-Context
Azure Storage Context Object
Parameter properties
Type: IStorageContext
Default value: None Supports wildcards: False DontShow: False
Parameter sets
(All)
Position: Named Mandatory: False Value from pipeline: True Value from pipeline by property name: True Value from remaining arguments: False
-DefaultProfile
The credentials, account, tenant, and subscription used for communication with Azure.
Parameter properties
Type: IAzureContextContainer
Default value: None Supports wildcards: False DontShow: False Aliases: AzureRmContext, AzureCredential
Parameter sets
(All)
Position: Named Mandatory: False Value from pipeline: False Value from pipeline by property name: False Value from remaining arguments: False
-Force
Force to overwrite the existing file.
Parameter properties
Type: SwitchParameter
Default value: None Supports wildcards: False DontShow: False
Parameter sets
(All)
Position: Named Mandatory: False Value from pipeline: False Value from pipeline by property name: False Value from remaining arguments: False
-InputTextConfiguration
The configuration used to handled the query input text. Create configuration object the with New-AzStorageBlobQueryConfig.
Parameter properties
Parameter sets
(All)
Position: Named Mandatory: False Value from pipeline: False Value from pipeline by property name: False Value from remaining arguments: False
-OutputTextConfiguration
The configuration used to handled the query output text. Create configuration object the with New-AzStorageBlobQueryConfig.
Parameter properties
Parameter sets
(All)
Position: Named Mandatory: False Value from pipeline: False Value from pipeline by property name: False Value from remaining arguments: False
-PassThru
Return whether the specified blob is successfully queried.
Parameter properties
Type: SwitchParameter
Default value: None Supports wildcards: False DontShow: False
Parameter sets
(All)
Position: Named Mandatory: False Value from pipeline: False Value from pipeline by property name: False Value from remaining arguments: False
-QueryString
Query string, see more details in: https://learn.microsoft.com/azure/storage/blobs/query-acceleration-sql-reference
Parameter properties
Type: String
Default value: None Supports wildcards: False DontShow: False
Parameter sets
(All)
Position: Named Mandatory: True Value from pipeline: False Value from pipeline by property name: False Value from remaining arguments: False
-ResultFile
Local file path to save the query result.
Parameter properties
Type: String
Default value: None Supports wildcards: False DontShow: False
Parameter sets
(All)
Position: Named Mandatory: True Value from pipeline: False Value from pipeline by property name: False Value from remaining arguments: False
-ServerTimeoutPerRequest
The server time out for each request in seconds.
Parameter properties
Type: Nullable<T> [ Int32 ]
Default value: None Supports wildcards: False DontShow: False Aliases: ServerTimeoutPerRequestInSeconds
Parameter sets
(All)
Position: Named Mandatory: False Value from pipeline: False Value from pipeline by property name: False Value from remaining arguments: False
-SnapshotTime
Blob SnapshotTime
Parameter properties
Parameter sets
NamePipeline
Position: Named Mandatory: False Value from pipeline: False Value from pipeline by property name: False Value from remaining arguments: False
ContainerPipeline
Position: Named Mandatory: False Value from pipeline: False Value from pipeline by property name: False Value from remaining arguments: False
-TagCondition
Optional Tag expression statement to check match condition. The blob request will fail when the blob tags does not match the given expression.See details in https://learn.microsoft.com/en-us/rest/api/storageservices/specifying-conditional-headers-for-blob-service-operations#tags-conditional-operations .
Parameter properties
Type: String
Default value: None Supports wildcards: False DontShow: False
Parameter sets
(All)
Position: Named Mandatory: False Value from pipeline: False Value from pipeline by property name: False Value from remaining arguments: False
-VersionId
Blob VersionId
Parameter properties
Type: String
Default value: None Supports wildcards: False DontShow: False
Parameter sets
NamePipeline
Position: Named Mandatory: False Value from pipeline: False Value from pipeline by property name: False Value from remaining arguments: False
ContainerPipeline
Position: Named Mandatory: False Value from pipeline: False Value from pipeline by property name: False Value from remaining arguments: False
-WhatIf
Shows what would happen if the cmdlet runs.
The cmdlet is not run.
Parameter properties
Type: SwitchParameter
Default value: None Supports wildcards: False DontShow: False Aliases: wi
Parameter sets
(All)
Position: Named Mandatory: False Value from pipeline: False Value from pipeline by property name: False Value from remaining arguments: False
CommonParameters
This cmdlet supports the common parameters: -Debug, -ErrorAction, -ErrorVariable,
-InformationAction, -InformationVariable, -OutBuffer, -OutVariable, -PipelineVariable,
-ProgressAction, -Verbose, -WarningAction, and -WarningVariable. For more information, see
about_CommonParameters .
Outputs
