question

VijaySampathTrianz-9530 avatar image
0 Votes"
VijaySampathTrianz-9530 asked pituach answered

Querying data from particular partition in Azure synapse

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?

azure-synapse-analytics
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @VijaySampathTrianz-9530,

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

0 Votes 0 ·

Hello @KranthiPakala-MSFT
I am looking for ways to query specific partitions of a partitioned table.

0 Votes 0 ·

1 Answer

pituach avatar image
0 Votes"
pituach answered

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

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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??

0 Votes 0 ·
pituach avatar image pituach VijaySampathTrianz-9530 ·

Yes, you got right :-)

You can do it in one step in a complex query by using dynamic query if needed

1 Vote 1 ·