'How do you setup a Synapse Serverless SQL External Table over partitioned data?

I have setup a Synapse workspace and imported the Covid19 sample data into a PySpark notebook.

blob_account_name = "pandemicdatalake"
blob_container_name = "public"
blob_relative_path = "curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.parquet"
blob_sas_token = r""
# Allow SPARK to read from Blob remotely
wasbs_path = 'wasbs://%s@%s.blob.core.windows.net/%s' % (blob_container_name, blob_account_name, blob_relative_path)

spark.conf.set(
    'fs.azure.sas.%s.%s.blob.core.windows.net' % (blob_container_name, blob_account_name),
    blob_sas_token)
df = spark.read.parquet(wasbs_path)

I have then partitioned the data by country_region, and written it back down into my storage account.

df.write.partitionBy("country_region") /
    .mode("overwrite") /
    .parquet("abfss://[email protected]/synapsepoc/Covid19/")

enter image description here

All that works fine as you can see. So far I have only found a way to query data from the exact partition using OPENROWSET, like this...

SELECT
    TOP 100 *
FROM
    OPENROWSET(
        BULK 'https://synapsepoc.dfs.core.windows.net/synapsepoc/Covid19/country_region=Afghanistan/**',
        FORMAT = 'PARQUET'
    ) AS [result]

I want to setup an Serverless SQL External table over the partition data, so that when people run a query and use "WHERE country_region = x" it will only read the appropriate partition. Is this possible, and if so how?

azureazure-synapse


Solution 1:[1]

You need to get the partition value using the filepath function like this. Then filter on it. That achieves partition elimination. You can confirm by the bytes read compared to when you don’t filter on that column.

CREATE VIEW MyView
As
SELECT
    *, filepath(1) as country_region
FROM
    OPENROWSET(
        BULK 'https://synapsepoc.dfs.core.windows.net/synapsepoc/Covid19/country_region=*/*',
        FORMAT = 'PARQUET'
    ) AS [result]

GO

Select * from MyView where country_region='Afghanistan'

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1

Related Questions