External Tables در Sql Server

External Tables در SQL Server 

در SQL Server برای دسترسی به داده‌های خارج از پایگاه داده محلی (Local Database) راهکارهای مختلفی وجود دارد. یکی از مهم‌ترین این راهکارها، قابلیت External Tables است.

این قابلیت از نسخه‌های جدید SQL Server (به‌ویژه SQL Server 2016 به بعد و با استفاده از PolyBase) معرفی شده و اجازه می‌دهد داده‌هایی که روی منابع بیرونی مثل فایل‌ها (CSV, Parquet)، سیستم‌های توزیع‌شده (Hadoop, Azure Blob Storage) یا حتی دیتابیس‌های دیگر (مانند Oracle, Teradata و Azure SQL Database) ذخیره شده‌اند، به صورت یک جدول عادی در SQL Server ظاهر شوند.

External Table جدولی در SQL Server است که داده‌هایش در پایگاه داده محلی ذخیره نشده، بلکه در یک منبع خارجی قرار دارد، اما شما می‌توانید با T-SQL مانند یک جدول عادی روی آن کوئری اجرا کنید.

مثال :

فرض کنید یک فایل CSV بزرگ در Azure Blob Storage دارید.

به‌جای اینکه هر بار فایل را Import کنید، می‌توانید یک External Table بسازید که مستقیماً به آن فایل اشاره کند.

بعد از آن کافی است:

SELECT * FROM SalesExternal;

و داده‌ها مثل یک جدول داخلی نمایش داده می‌شوند.

مزایای External Tables در sql

• دسترسی مستقیم

  به داده‌های خارجی بدون نیاز به Import/Export دستی.

• یکپارچگی

  کاربران می‌توانند با همان زبان T-SQL روی داده‌های بیرونی کار کنند.

• پردازش حجم بالا

  در محیط‌های Big Data (مثل Hadoop یا Azure Data Lake) استفاده می‌شود.

• تسهیل در ETL

  برای سناریوهای Data Warehouse، External Table پلی میان منابع داده‌ای مختلف ایجاد می‌کند.

• افزایش کارایی توسعه

  توسعه‌دهنده نیازی به یادگیری زبان یا ابزار جداگانه ندارد.

ساختار اجزای External Table

برای ایجاد یک External Table، چند جزء اصلی لازم است:

• External Data Source

  تعریف منبع داده (مانند فایل سیستم، HDFS، Azure Blob Storage یا دیتابیس دیگر).

• External File Format

  تعیین فرمت فایل (CSV, Parquet, ORC و غیره).

• External Table

  تعریف جدولی که به آن منابع اشاره می‌کند.

ایجاد External Table (CREATE EXTERNAL TABLE)

سینتکس کلی

CREATE EXTERNAL TABLE [schema_name].[table_name] 
(
    column_name data_type [,...n]
)
WITH
(
    LOCATION = 'path_or_folder',
    DATA_SOURCE = external_data_source_name,
    FILE_FORMAT = external_file_format_name,
    REJECT_TYPE = value,
    REJECT_VALUE = value
);

• LOCATION: مسیر فایل یا پوشه داده.
• DATA_SOURCE: منبع داده خارجی که قبلاً ساخته شده.
• FILE_FORMAT: فرمت فایل.
• REJECT_TYPE و REJECT_VALUE: برای مدیریت ردیف‌های معیوب.

مراحل ایجاد External Table

گام ۱: ایجاد External Data Source

مثال برای Azure Blob Storage:

CREATE EXTERNAL DATA SOURCE MyAzureBlob
WITH
(
    TYPE = HADOOP,
    LOCATION = 'wasbs://container1@myaccount.blob.core.windows.net/'
);

گام ۲: تعریف فرمت فایل

برای داده‌های CSV:

CREATE EXTERNAL FILE FORMAT MyCSVFormat
WITH
(
    FORMAT_TYPE = DELIMITEDTEXT,
    FORMAT_OPTIONS (
        FIELD_TERMINATOR = ',',
        STRING_DELIMITER = '"',
        FIRST_ROW = 2
    )
);

گام ۳: ایجاد External Table

CREATE EXTERNAL TABLE dbo.SalesExternal
(
    OrderID INT,
    OrderDate DATE,
    CustomerName NVARCHAR(100),
    Amount DECIMAL(10,2)
)
WITH
(
    LOCATION = '/sales/2025/',
    DATA_SOURCE = MyAzureBlob,
    FILE_FORMAT = MyCSVFormat,
    REJECT_TYPE = VALUE,
    REJECT_VALUE = 5
);

 

🌟 آیا می‌خواهید به یک متخصص پایگاه داده تبدیل شوید و در دنیای فناوری اطلاعات بدرخشید؟

با دوره آموزشی SQL Server ما، شما می‌توانید به راحتی و با روشی عملی، تمام مهارت‌های لازم را یاد بگیرید!

این دوره به شما آموزش می‌دهد که چگونه داده‌ها را به بهترین شکل مدیریت کنید، گزارش‌های قدرتمند بسازید و به تحلیل‌های عمیق دست یابید.

با محتوای جذاب و پروژه‌های واقعی، شما نه تنها تئوری را یاد می‌گیرید، بلکه توانایی‌های عملی خود را نیز تقویت می‌کنید.

پس فرصت را از دست ندهید! همین امروز به جمع یادگیرندگان ما بپیوندید و اولین قدم را به سوی آینده شغلی روشن‌تر بردارید!

 ⇐همین حالا شروع کنید و به دنیای داده‌ها بپیوندید!

 

 

مثال از External Table برای دیتابیس دیگر

فرض کنید با قابلیت PolyBase می‌خواهید به Oracle متصل شوید.

ابتدا External Data Source تعریف می‌کنید:

CREATE EXTERNAL DATA SOURCE OracleDB
WITH
(
    TYPE = RDBMS,
    LOCATION = 'oracle://192.168.1.20:1521',
    CONNECTION_OPTIONS = 'Database=HR',
    CREDENTIAL = OracleCred
);

سپس External Table:

CREATE EXTERNAL TABLE dbo.EmployeesOracle
(
    EmpID INT,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    HireDate DATE
)
WITH
(
    DATA_SOURCE = OracleDB
);

 استفاده از External Tables در sql server

بعد از ایجاد، دقیقاً مانند جدول داخلی استفاده می‌کنید:

SELECT TOP 10 * FROM dbo.SalesExternal;

یا حتی Join با جداول محلی:

SELECT c.CustomerID, c.CustomerName, s.Amount
FROM dbo.Customers c
JOIN dbo.SalesExternal s
ON c.CustomerName = s.CustomerName;

مشاهده External Tables

برای دیدن لیست External Tables:

SELECT * FROM sys.external_tables;

برای مشاهده منابع داده:

SELECT * FROM sys.external_data_sources;

برای مشاهده فرمت فایل:

SELECT * FROM sys.external_file_formats;

حذف External Table (DROP EXTERNAL TABLE)

• سینتکس

DROP EXTERNAL TABLE [schema_name].[table_name];

مثال:

DROP EXTERNAL TABLE dbo.SalesExternal;

• حذف امن (SQL Server 2016+)

DROP EXTERNAL TABLE IF EXISTS dbo.SalesExternal;

اگر جدول وجود نداشت، خطا نمی‌دهد.

حذف اجزای مرتبط

اگر دیگر به Data Source یا File Format نیاز ندارید:

DROP EXTERNAL DATA SOURCE MyAzureBlob;
DROP EXTERNAL FILE FORMAT MyCSVFormat;

نکات امنیتی

• برای استفاده از External Table نیاز به دسترسی مناسب روی منبع داده (مانند Azure Storage Account یا سرور Oracle) دارید.
• Credentialها با CREATE DATABASE SCOPED CREDENTIAL تعریف می‌شوند.
• اگر مجوز مناسب روی External Data Source نداشته باشید، کوئری خطا می‌دهد.

محدودیت‌ها

• External Table قابل Index شدن نیست.
• عملیات DML کامل (INSERT, UPDATE, DELETE) همیشه پشتیبانی نمی‌شود؛ بستگی به منبع داده دارد.
• Performance ممکن است کمتر از جدول داخلی باشد چون داده از منبع خارجی خوانده می‌شود.
• Synonym روی External Table مجاز است، اما Nested External Table وجود ندارد.

کاربرد External Tables در Sql Server

• Data Lake Integration

  اتصال مستقیم SQL Server به Azure Data Lake یا Blob Storage.

• ETL Pipeline

  خواندن داده خام از فایل‌های CSV یا Parquet برای بارگذاری در Data Warehouse.

• Hybrid Data Query

  ترکیب داده‌های محلی و داده‌های ذخیره‌شده روی Hadoop.

• Migration Strategy

  دسترسی به داده‌های دیتابیس‌های دیگر (Oracle, Teradata) بدون انتقال کامل.

بهترین روش‌ها

• همیشه قبل از ایجاد External Table، Data Source و File Format را به‌طور دقیق تست کنید.
• برای امنیت بیشتر از Scoped Credential استفاده کنید.
• در پروژه‌های بزرگ، نام‌گذاری استاندارد (مثل پیشوند ext_) برای External Tables انجام دهید.
• برای بهبود عملکرد، در صورت امکان داده‌های خارجی را در قالب Parquet ذخیره کنید چون فشرده و ستونی است.

اسکریپت نمونهٔ کامل

-- مرحله ۱: Credential برای دسترسی
CREATE DATABASE SCOPED CREDENTIAL AzureCred
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 'sv=2025...';

-- مرحله ۲: تعریف Data Source
CREATE EXTERNAL DATA SOURCE AzureBlobData
WITH
(
    TYPE = HADOOP,
    LOCATION = 'wasbs://mycontainer@myaccount.blob.core.windows.net/',
    CREDENTIAL = AzureCred
);

-- مرحله ۳: تعریف فرمت فایل
CREATE EXTERNAL FILE FORMAT CSVFormat
WITH
(
    FORMAT_TYPE = DELIMITEDTEXT,
    FORMAT_OPTIONS (FIELD_TERMINATOR = ',', STRING_DELIMITER = '"', FIRST_ROW = 2)
);

-- مرحله ۴: ایجاد External Table
CREATE EXTERNAL TABLE dbo.SalesExternal
(
    OrderID INT,
    OrderDate DATE,
    CustomerName NVARCHAR(100),
    Amount DECIMAL(10,2)
)
WITH
(
    LOCATION = '/sales/',
    DATA_SOURCE = AzureBlobData,
    FILE_FORMAT = CSVFormat
);

-- استفاده
SELECT TOP 20 * FROM dbo.SalesExternal;

-- حذف
DROP EXTERNAL TABLE IF EXISTS dbo.SalesExternal;
DROP EXTERNAL FILE FORMAT CSVFormat;
DROP EXTERNAL DATA SOURCE AzureBlobData;
DROP DATABASE SCOPED CREDENTIAL AzureCred;

جمع‌بندی

External Tables در SQL Server ابزاری بسیار کاربردی برای دسترسی به داده‌های خارج از دیتابیس محلی است.

با دستور CREATE EXTERNAL TABLE ساخته می‌شوند.

برای آن‌ها نیاز به Data Source و File Format داریم.

با DROP EXTERNAL TABLE حذف می‌شوند.

مزیت اصلی: دسترسی شفاف و ساده با T-SQL به منابع خارجی مثل فایل‌ها، دیتابیس‌های دیگر یا سرویس‌های ابری.

در معماری‌های مدرن داده (مانند Data Lake و Data Warehouse)، External Tables نقش کلیدی در یکپارچه‌سازی داده‌ها ایفا می‌کنند و در عین حال مدیریت آن‌ها ساده است.