'Is there a possibility to create external table in Azure SQL Data Warehouse pointing to only few folders from the specified location?

I have been trying to create an external table in Azure SQL Data Warehouse by considering only some folders instead of all from the azure Datalake location. But I'm not able to do so. Request for help in this regard.

Below is the example for this scenario.

In Azure Datalake, the location consists of the folders with data split into multiple folders based on years criteria. The folders exist from the year 1996 to 2020. My scenario is to create an external table only for the years 2018 and above.

Please suggest the best approach to accomplish this.

Thank you!!!

azureazure-data-lakeazure-sql-serverexternal-tablesazure-sql-data-warehouse


Solution 1:[1]

You have two main options:

1. Create one external table per year then union together selects from several external tables

For example you could do something conceptually like:

CREATE EXTERNAL TABLE FILES2018...
CREATE EXTERNAL TABLE FILES2019...
CREATE EXTERNAL TABLE FILES2020...

SELECT * FROM FILES2018
UNION ALL
SELECT * FROM FILES2019
UNION ALL
SELECT * FROM FILES2020

2. Use the new COPY command

This example shows how to do multiple folders.

COPY INTO t1
FROM 
    'https://myaccount.blob.core.windows.net/myblobcontainer/folder2018', 
    'https://myaccount.blob.core.windows.net/myblobcontainer/folder2019', 
    'https://myaccount.blob.core.windows.net/myblobcontainer/folder2020'
WITH...

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