I would like to query the data in a particular partition in Azure Synapse. Is it possible to do so? what is the best way to do it?
I would like to query the data in a particular partition in Azure Synapse. Is it possible to do so? what is the best way to do it?
Welcome to Microsoft Q&A forum and thanks for posting your query.
Could you please confirm if you are referring to query the data in a particular partition in Azure Synapse Dedicated SQL pool - Partitioned tables?
Thanks
Hello @KranthiPakala-MSFT
I am looking for ways to query specific partitions of a partitioned table.
Hi
dedicated SQL pool does not use Partitioning functions and schemes as they are used in SQL Server. The partitioning is configured in the table configuration (CREATE/ ALTER). This make the question more interesting since we cannot based on the Partitioning functions for the filtering in the SELECT query.
With that being said, you still have (or can find) the configuration of the table DDL and using the CREATE TABLE statement you know what is the partitioning rule.
Next you simply write a query which uses the same filter using WHERE condition
Hi @pituach
Thanks for your reply :)
For example, if i want to query a table that is partitioned on a "column_a", Then I need to look into the table create statement to find out what is the partition range. if "column_a" is partitions as below,
0000 to 1000 --> partition 1
1001 to 2000 --> partition 2
2001 to 3000 --> partition 3
3001 to 4000 --> partition 4
then I can add the specific range in the where clause to fetch data from that partition only.
is my understanding correct??
Yes, you got right :-)
You can do it in one step in a complex query by using dynamic query if needed
7 people are following this question.