Kusto partitioning and sharding

[Bergen]

I dont know where i should post my quetion so i hope this is the right place.

I have a table which contains data which is ingested out-of-order, if i understand correctly the IngestionTime is used for sharding and partitioning the data in the Kusto DB.

If my data is ingested out-of-order and has its own "ReceivedTime" column which is a datetime. And i want to do my time based queries on the "ReceivedTime" i understand that i should partition my table on the "ReceivedTime" to make it as fast as possible, is that correct? 

I read you should be able to do someting like this by doing a Query through the fabric KQL DB Query with code option or the Azure Data Explorer:

.alter-merge table ['MyTable'] policy partitioning = 
{
    "PartitionKeys": [
        {
            "ColumnName": "ReceivedTime",
            "Kind": "UniformRange",
            "Properties": {
                "Reference": "2024-01-01T00:00:00",
                "RangeSize": "12.00:00:00",
                "OverrideCreationTime": false
            }
        }
    ]
}

When i try running this query i get a Syntax error: A recognition error occurred.

Can someone please tell me if im correct in saying i should partition the data as it should greatly increase performance of my time based queries? If so how i should do it since i dont see the issue with my query? 

Just in case it helps someone else, I found my info on Partitioning policy - Kusto | Microsoft Learn 

[v-nmadadi-msft]

Hi @Bergen 

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[Bergen]

@v-nmadadi-msft Yes i read about that option, but the issue is i get a syntax error when trying to run the following query 

.alter-merge table ['MyTable'] policy partitioning = 
{
    "PartitionKeys": [
        {
            "ColumnName": "ReceivedTime",
            "Kind": "UniformRange",
            "Properties": {
                "Reference": "2024-01-01T00:00:00",
                "RangeSize": "12.00:00:00",
                "OverrideCreationTime": true
            }
        }
    ]
}

I tried running it in the KQL DB Query with code option or the Azure Data Explorer.

[v-nmadadi-msft · Accepted solution]

Hi @Bergen ,

Can you please make a slight modification to the query and check if you are able to bypass the error.

.alter-merge table ['MyTable'] policy partitioning = @"{
    ""PartitionKeys"": [
        {
            ""ColumnName"": ""ReceivedTime"",
            ""Kind"": ""UniformRange"",
            ""Properties"": {
                ""Reference"": ""2024-01-01T00:00:00"",
                ""RangeSize"": ""12.00:00:00"",
                ""OverrideCreationTime"": true
            }
        }
    ]
}"

If this post helps, then please consider Accepting as solution to help the other members find it more quickly and consider giving a KUDOS. Feel free to reach out if you need further assistance.
Thank you




 

[v-nmadadi-msft]

Hi @Bergen  ,
Thanks for reaching out to the Microsoft fabric community forum.

The Document mentioned this point:

If you need retention and caching policies to align with the datetime values in the column, instead of aligning with the time of ingestion, set the OverrideCreationTime property to true
So kindly re check by modifying the Property to true.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly and consider giving a KUDOS. Feel free to reach out if you need further assistance.
Thank you