question

MuneebMirza-5872 avatar image
0 Votes"
MuneebMirza-5872 asked MuneebMirza-5872 commented

Query Blob Content: Query CSV Syntax

I checked this API https://docs.microsoft.com/en-us/rest/api/storageservices/query-blob-contents and ran it. I was able to get complete CSV file that is stored in Azure Blob Storage.

This API also says we can request with a query

https://myaccount.blob.core.windows.net/mycontainer/myblob?comp=query

I am sending query as

<QueryRequest>
<QueryType>SQL</QueryType>
<Expression>limit 5</Expression>
</QueryRequest>


My blob is a CSV file and link mentioned above says it accepts SQL query, but I am unsure what will be used as table name or how the query will look like.

For example, if I have a csv file named custom_data.csv, suppose it has columns id, name, description, order how can I write a query saying bring me records where

  1. id=123

  2. name like '%john%'

if it is SQL, then what is to be added in FROM, can I get a proper example here?


Thanks.











azure-blob-storage
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

VaibhavChaudhari avatar image
1 Vote"
VaibhavChaudhari answered MuneebMirza-5872 commented

Try providing complete SQL query like below. If the file is CSV, table name will be "BlobStorage"

SELECT * FROM BlobStorage LIMIT 5

See the examples here to use in Expression:

https://docs.microsoft.com/en-us/azure/storage/blobs/query-acceleration-sql-reference#select-syntax


Please don't forget to Accept Answer and Up-vote if the response helped -- Vaibhav

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Yes I was able to work with that thanks. This is my request body but it is throwing exception.

<?xml version="1.0" encoding="utf-8"?>
<QueryRequest>
<QueryType>SQL</QueryType>
<Expression>select * from BlobStorage limit 5</Expression>
<InputSerialization>
<Format>
<Type>delimited</Type>
<DelimitedTextConfiguration>
<ColumnSeparator>,</ColumnSeparator>
<FieldQuote>"</FieldQuote>
<RecordSeparator>\n</RecordSeparator>
<EscapeChar>\</EscapeChar>
<HasHeaders>True</HasHeaders>
</DelimitedTextConfiguration>
</Format>
</InputSerialization>
<OutputSerialization>
<Format>
<Type>json</Type>
<JsonTextConfiguration>
<RecordSeparator>,</RecordSeparator>
</JsonTextConfiguration>
</Format>
</OutputSerialization>
</QueryRequest>

This request sends back error "InvalidXmlDocument"

If I add DelimitedTextConfiguration tag, with any kind of sub tag, it throws this exception. If I remove DelimitedTextConfiguration in InputSerialization, my request works fine. But for where clause I need to specify HasHeader tag which is failing.


Can you help me here?

0 Votes 0 ·