Azure SQL数据仓库如何创建指向ADLS指定年份文件夹的外部表？

Got it, let's work through this problem together. Creating an external table in Azure SQL Data Warehouse (now part of Azure Synapse Analytics SQL pools) that only targets 2018+ folders in ADLS is totally doable—you just need to use the right path filtering or partitioning approach. Here are the two best methods, depending on your long-term needs:

Prerequisites First #

Before diving in, make sure you already have:

• An external data source pointing to your ADLS account (configured with SAS or service principal credentials)
• An external file format matching your data (Parquet, CSV, etc.)

If you haven't set these up yet, here's a quick example to get you started:

-- Create external data source (replace placeholders with your details)
CREATE EXTERNAL DATA SOURCE ADLS_DataSource
WITH (
    TYPE = HADOOP,
    LOCATION = 'abfss://your-container@your-adls-account.dfs.core.windows.net',
    CREDENTIAL = Your_ADLS_Credential -- Pre-created credential for ADLS access
);

-- Create external file format (example for Parquet; adjust for your data type)
CREATE EXTERNAL FILE FORMAT Parquet_Format
WITH (
    FORMAT_TYPE = PARQUET,
    DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
);

方案1：直接用路径通配符快速创建（适合临时/简单场景） #

If you just need a one-off table targeting 2018-2020, you can use wildcard patterns in the LOCATION parameter to filter folders directly. This works for both simple year-named folders (e.g., /2018/) and Hive-style partitioned folders (e.g., /year=2018/).

Example for Simple Year-Named Folders #

CREATE EXTERNAL TABLE dbo.Sales_Data_2018_Plus
(
    SaleID INT,
    ProductName VARCHAR(100),
    SaleAmount DECIMAL(18,2),
    SaleDate DATE
)
WITH (
    -- Use wildcards to match 2018, 2019, 2020 folders
    LOCATION = '/your-data-root/201[89]/,/your-data-root/2020/',
    DATA_SOURCE = ADLS_DataSource,
    FILE_FORMAT = Parquet_Format
);

Example for Hive-Style Partitioned Folders #

If your ADLS data uses Hive-style partitions (like year=2018), adjust the LOCATION to target those:

CREATE EXTERNAL TABLE dbo.Sales_Data_2018_Plus
(
    SaleID INT,
    ProductName VARCHAR(100),
    SaleAmount DECIMAL(18,2),
    SaleDate DATE
)
WITH (
    LOCATION = '/your-data-root/year=201*/,/your-data-root/year=2020/',
    DATA_SOURCE = ADLS_DataSource,
    FILE_FORMAT = Parquet_Format
);

方案2：分区外部表（推荐用于长期维护/可扩展场景） #

If you plan to add more years (like 2021, 2022) later, a partitioned external table is the optimal choice. It lets you add new partitions without rebuilding the entire table, and it improves query performance by pruning unnecessary partitions.

Step 1: Create the Partitioned External Table #

Define the table with a year partition column that maps to your ADLS folder structure:

CREATE EXTERNAL TABLE dbo.Sales_Data_Partitioned
(
    SaleID INT,
    ProductName VARCHAR(100),
    SaleAmount DECIMAL(18,2),
    SaleDate DATE,
    year INT -- Partition column that matches your folder structure
)
WITH (
    LOCATION = '/your-data-root/', -- Point to the root folder
    DATA_SOURCE = ADLS_DataSource,
    FILE_FORMAT = Parquet_Format,
    PARTITION (year = INT) -- Declare the partition column and data type
);

Step 2: Add Partitions for 2018, 2019, 2020 #

Now explicitly add only the partitions you need:

-- Add 2018 partition (adjust LOCATION to match your actual folder path)
ALTER EXTERNAL TABLE dbo.Sales_Data_Partitioned
ADD PARTITION (year = 2018)
LOCATION = '/your-data-root/year=2018/';

-- Add 2019 partition
ALTER EXTERNAL TABLE dbo.Sales_Data_Partitioned
ADD PARTITION (year = 2019)
LOCATION = '/your-data-root/year=2019/';

-- Add 2020 partition
ALTER EXTERNAL TABLE dbo.Sales_Data_Partitioned
ADD PARTITION (year = 2020)
LOCATION = '/your-data-root/year=2020/';

Bonus: Adding Future Years Later #

When you need to include 2021 or beyond, just run a quick ALTER command:

ALTER EXTERNAL TABLE dbo.Sales_Data_Partitioned
ADD PARTITION (year = 2021)
LOCATION = '/your-data-root/year=2021/';

Verification Tips #

After creating the table, confirm it's only pulling 2018+ data with these queries:

-- Check distinct years in the table
SELECT DISTINCT year FROM dbo.Sales_Data_Partitioned;

-- Test a sample query
SELECT TOP 10 * FROM dbo.Sales_Data_2018_Plus WHERE SaleDate >= '2018-01-01';

内容的提问来源于stack exchange，提问作者Deekshit