Querying data from external table mapped to Azure Data Lake Storage Gen2

[Simona_ZW]

Hello,

I need help querying data from an external table. The data is stored in Azure Data Lake Storage Gen2 in avro format. I was able to create an external table providing the link to the storage container and using the schema generated by infere_storage_schema(). However, when I query a sample of the data, I get a series of numbers instead of the expected text. The column data type, as inferred by the infere_storage_shema() is dynamic. The column is expected to have string data in json format.

I have manually mapped some of the numbers to their respective characters and I do get the expected text.

How can I do this in the query though?  I am just starting with Azure Data Explorer and KQL but I have looked through all the operators, command and functions and I am not able to get the right syntax.

Thank you

[#2]

To add more info, it seems the data point I expect to be text is not an avro primitive type (string) , but rather a record complex type.

[#3 · Accepted solution]

It turns out I needed to use dataformat: apacheavro instead of dataformat:avro when creating the external table.