Integer columns not accepted in Azure Table Storage

Question

I'm using ADF pipeline to copy data from data lake to blob storage and then to table storage. Table storage has a column which includes integer values (column: Age). On trying to query the data in table storage

https://myaccount.table.core.windows.net/Customers()?$filter=Age%20eq%2030 -> (age 30 is considered as integer)

the output is null.

On updating the query to

https://myaccount.table.core.windows.net/Customers()?$filter=Age%20eq%20'30' -> (age 30 is considered as string)

it returned right output.

I used the same adf pipeline to copy data from data lake to cosmos db sql api. When I used query

SELECT * FROM c WHERE c.Age = 30

it returned right output. I tried

SELECT * FROM c WHERE c.Age > 30

it also returned right output.

So, cosmos db is accepting integer columns whereas table storage is only accepting string values. Why is that? How do I resolve this issue in table storage so that it will accept integer columns? Please let me know. Thank you!

Did you check the age type is integer in the storage explorer? I have tested, the integer column could be queried. — George Chen
I see age type as string in the storage explorer. Why is that? The age column that I copied from ADF is of integer type. — user989988

George Chen George Chen · Accepted Answer · 2020-05-25T09:20:25

Not very sure about the cause, the below is my steps to copy a son data from data lake to azure table.

Firstly this is my test JSON:

{
    "callingimsi": "466920403025604",
    "switch1": "China",
    "switch2": "Germany",
    "testvalue":12
}

Create the JSON Dataset, then below is the default Schema, the testvalue is integer type.

Then I just create a table dataset and create the simple pipeline, set the source and sink then debug. In the table the testvalue is Int64 type it supports the query.

Integer columns not accepted in Azure Table Storage

1 Answers