How to get the partition keys to check data in particular partition

Question

Create table

.create table t1 ( Timestamp:datetime, From:string, TravelMode:string, Tier:string) 
.ingest inline into table t1 <| 
2010-01-01,NY,Flight,Premium 2010-01-02,Boston,Train,Economy 
2010-01-02,NY,Flight,Business 2010-01-02,Boston,Bus,Business 
2010-01-03,Philadelphia,Train,Premium 
2010-01-04,Texas,Bus,Economy 
2010-01-05,Texas,Bus,Economy 

Apply Partition Policy on "TravelMode" column

.alter table t1 policy partitioning '{ "policy": "hash(Timestamp, 100)", "partitionKey": ["TravelMode"] }'

After partitioning the table, how can I get the partitoned key values to check the data in that particular partiton?

Answer 1

Hi Bexy Morgan,

Thank you for posting query in Microsoft Q&A Platform.

In Azure Data Explorer (Kusto), you can use the partitions command to get the partition key values for a partitioned table. Here's an example:

.show table MyTable partitions

In the above example, we are using the show table command with the partitions keyword to get the partition key values for the MyTable table. This will return a list of partition key values for each partition in the table.

Once you have the partition key values, you can use them to filter the data in a particular partition. Here's an example:

MyTable | where PartitionKey == "2022-01-01"

In the above example, we are using the where clause to filter the data in the MyTable table based on the PartitionKey column. We are filtering the data to only include rows where the PartitionKey value is equal to "2022-01-01". This will return all the data in the partition for the specified partition key value.

Note that the partition key values are specific to each partitioned table and are based on the partitioning scheme that you have defined for the table. You can use the partitions command to get the partition key values for any partitioned table in Azure Data Explorer.

Hope this helps. Please let me know how it goes. Thank you.