I'm querying Azure table storage using the Azure Storage Explorer. I want to find all messages that contain the given text, like this in T-SQL:
message like '%SysFn%'
Executing the T-SQL gives "An error occurred while processing this request"
What is the equivalent of this query in Azure?
Azure Table storage is a service that stores non-relational structured data (also known as structured NoSQL data) in the cloud, providing a key/attribute store with a schemaless design. Because Table storage is schemaless, it's easy to adapt your data as the needs of your application evolve.
Azure Table storage is a cloud-based NoSQL datastore you can use to store large amounts of structured, non-relational data. Azure Table offers a schemaless design, which enables you to store a collection of entities in one table.
Enter an Account Name, Account Key, and Table Name on the Azure Table tab of the New Session dialog. Select either HTTP or HTTPS as the connection Protocol. Ensure that the Analysis Grid viewer is selected in the Start With drop-down list. Start retrieving data by clicking the Start button in the New Session dialog.
There's no direct equivalent, as there is no wildcard searching. All supported operations are listed here. You'll see eq, gt, ge, lt, le, etc. You could make use of these, perhaps, to look for specific ranges.
Depending on your partitioning scheme, you may be able to select a subset of entities based on specific partition key, and then scan through each entity, examining message
to find the specific ones you need (basically a partial partition scan).
While an advanced wildcard search isn't strictly possible in Azure Table Storage, you can use a combination of the "ge" and "lt" operators to achieve a "prefix" search. This process is explained in a blog post by Scott Helme here.
Essentially this method uses ASCII incrementing to query Azure Table Storage for any rows whose property begins with a certain string of text. I've written a small Powershell function that generates the custom filter needed to do a prefix search.
Function Get-AzTableWildcardFilter { param ( [Parameter(Mandatory=$true)] [string]$FilterProperty, [Parameter(Mandatory=$true)] [string]$FilterText ) Begin {} Process { $SearchArray = ([char[]]$FilterText) $SearchArray[-1] = [char](([int]$SearchArray[-1]) + 1) $SearchString = ($SearchArray -join '') } End { Write-Output "($($FilterProperty) ge '$($FilterText)') and ($($FilterProperty) lt '$($SearchString)')" } }
You could then use this function with Get-AzTableRow
like this (where $CloudTable is your Microsoft.Azure.Cosmos.Table.CloudTable
object):
Get-AzTableRow -Table $CloudTable -CustomFilter (Get-AzTableWildcardFilter -FilterProperty 'RowKey' -FilterText 'foo')
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With