EngineeringSenior DeveloperSQL Deep Dive

Azure Data Lake & ETL cho SQL Server

2 min read6 lessons in this module

Mục tiêu

Hướng dẫn cách đưa dữ liệu từ SQL Server (on‑prem hoặc Azure SQL) vào Azure Data Lake Gen2, thực hiện ETL bằng Azure Data Factory, Azure Synapse hoặc Azure Databricks, và tạo data warehouse cho phân tích.

1. Kiến trúc tổng quan

SQL Server (on‑prem) ──► Self‑hosted IR ──► Azure Data Factory (Copy Activity) ──► Azure Data Lake (raw)

Azure Data Lake (raw) ──► Azure Data Factory (Mapping Data Flow) ──► Azure Data Lake (curated)

Azure Synapse / Databricks ──► Đọc từ curated layer ──► Transform & Load vào Synapse DW / Azure SQL DW

2. Thiết lập Self‑hosted Integration Runtime (IR)

  1. Tạo IR trong portal ADF → Download và install trên máy on‑prem.
  2. Đăng ký IR với Managed Identity hoặc Service Principal có quyền Read/Write trên Data Lake.
  3. Kiểm tra kết nối tới SQL Server bằng Test connection.

3. Copy Activity – Đưa dữ liệu raw

json
{
  "name": "CopySQLToADLS",
  "type": "Copy",
  "inputs": [{ "referenceName": "OnPremSQL", "type": "DatasetReference" }],
  "outputs": [{ "referenceName": "ADLSRaw", "type": "DatasetReference" }],
  "typeProperties": {
    "source": { "type": "SqlSource", "sqlReaderQuery": "SELECT * FROM dbo.Orders WHERE ModifiedDate > @lastWatermark" },
    "sink": { "type": "AzureBlobFS", "storeSettings": { "type": "AzureBlobFSWriteSettings" } }
  }
}
  • Watermark: lưu @lastWatermark trong Azure Key Vault hoặc SQL table để chỉ sao chép thay đổi.
  • Định dạng: lưu dưới dạng Parquet hoặc CSV (Parquet gợi ý cho downstream).

4. Data Flow – Chuyển đổi (Curated Layer)

  • Mapping Data Flow cho cleaning, type conversion, lookup.
  • Thêm Derived Column để tính trường OrderYear = year(OrderDate).
  • Ghi kết quả vào adls://curated/orders/ dưới dạng Parquet partitioned by OrderYear.

5. Tải vào Synapse DW (hoặc Azure SQL DW)

sql
CREATE EXTERNAL TABLE dbo.Orders (
    OrderId BIGINT,
    CustomerId BIGINT,
    OrderDate DATETIME2,
    Total MONEY,
    OrderYear INT
)
WITH (
    LOCATION = 'curated/orders/',
    DATA_SOURCE = AzureDataLakeSource,
    FILE_FORMAT = ParquetFormat
);
  • Sử dụng PolyBase để query trực tiếp từ Data Lake.
  • Tối ưu distribution bằng HASH(OrderId).

6. Lưu trữ & Quản lý dữ liệu

  • Retention: dữ liệu raw giữ 30 ngày, curated giữ 365 ngày.
  • Lifecycle Management: Azure Blob Storage policy tự động chuyển raw sang Cool tier sau 7 ngày.
  • Security: bật Encryption at rest (Azure Storage encryption) và Access control (RBAC + SAS).

7. Kiểm tra & Giám sát

  • ADF Monitoring → Pipeline run status, Activity run details.
  • Log Analytics: gửi logs từ ADF, Data Lake, Synapse.
  • Alert: khi pipeline thất bại > 3 lần liên tiếp, gửi email/Teams.

File thuộc mục SQL Deep‑Dive của senior‑dotnet‑wiki.