如何将ADLS中的JSON格式发票数据合理存储至ALD DB表中？

Hey there! Let's walk through how to model your JSON invoice data from ADLS into Azure SQL DB (I assume you meant Azure SQL DB instead of ALD DB) in a way that fits your needs, even if you're not deep into unstructured data. Below are three common approaches, each with tradeoffs depending on how you plan to use the data:

If you need to run frequent reports, filter by client details, or analyze transaction trends, this is the most robust approach. We’ll break the nested JSON into standard relational tables to leverage SQL’s strengths.

Table Structure: #

• Invoices: Stores top-level invoice metadata
• Clients: Linked to invoices (one-to-one, since each invoice has a single client)
• Transactions: Linked to invoices (one-to-many, since each invoice has multiple transactions)

Example SQL Schema: #

-- Core invoice table
CREATE TABLE Invoices (
    BusinessGUID UNIQUEIDENTIFIER PRIMARY KEY,
    IngressTimestamp DATETIME NOT NULL
);

-- Client details (one-to-one with invoices)
CREATE TABLE Clients (
    BusinessGUID UNIQUEIDENTIFIER PRIMARY KEY FOREIGN KEY REFERENCES Invoices(BusinessGUID),
    ClientName NVARCHAR(100) NOT NULL,
    ClientAge INT
);

-- Transaction line items (one-to-many with invoices)
CREATE TABLE Transactions (
    TransactionID INT IDENTITY(1,1) PRIMARY KEY,
    BusinessGUID UNIQUEIDENTIFIER FOREIGN KEY REFERENCES Invoices(BusinessGUID),
    ProductName NVARCHAR(100) NOT NULL,
    Amount DECIMAL(18,2) NOT NULL
);

Pros & Cons: #

• ✅ Blazing fast query performance for structured filters/aggregations
• ✅ Follows ACID compliance for data integrity
• ❌ Requires more work to parse JSON into multiple tables during sync

If you want to keep nested data intact but still need quick access to top-level fields, this middle-ground approach works well. We’ll store structured metadata as regular columns, and tuck nested client/transactions data into JSON columns.

Example SQL Schema: #

CREATE TABLE Invoices (
    BusinessGUID UNIQUEIDENTIFIER PRIMARY KEY,
    IngressTimestamp DATETIME NOT NULL,
    ClientInfo NVARCHAR(MAX) CHECK (ISJSON(ClientInfo) = 1), -- Stores client JSON object
    Transactions NVARCHAR(MAX) CHECK (ISJSON(Transactions) = 1) -- Stores transactions JSON array
);

Example Query to Extract Data: #

-- Get client name and total transaction amount per invoice
SELECT 
    BusinessGUID,
    JSON_VALUE(ClientInfo, '$.name') AS ClientName,
    (SELECT SUM(JSON_VALUE(t.value, '$.amount')) FROM OPENJSON(Transactions) t) AS TotalAmount
FROM Invoices;

Pros & Cons: #

• ✅ Easier sync logic (no need to split JSON into multiple tables)
• ✅ Still allows querying nested data with SQL’s built-in JSON functions
• ❌ JSON columns have slower query performance than normalized tables (can mitigate with JSON indexes)

If you just need to get data into the database fast and don’t plan on running complex queries, you can store the entire JSON object in a single column.

Example SQL Schema: #

CREATE TABLE Invoices (
    BusinessGUID UNIQUEIDENTIFIER PRIMARY KEY,
    InvoiceData NVARCHAR(MAX) CHECK (ISJSON(InvoiceData) = 1) NOT NULL -- Stores full invoice JSON
);

Example Query: #

-- Fetch invoices from clients over 50
SELECT InvoiceData
FROM Invoices
WHERE JSON_VALUE(InvoiceData, '$.client.age') > 50;

Pros & Cons: #

• ✅ Zero parsing needed during sync—just copy the JSON directly
• ❌ Poor performance for complex queries or aggregations
• ❌ Hard to maintain data consistency for nested fields

For all these approaches, you can use Azure Data Factory (ADF) or Azure Synapse Pipelines to automate the sync:

• For normalized tables: Use ADF’s JSON parsing activity to split the JSON into streams, then map each stream to a table.
• For hybrid/full JSON: Use a copy activity to load JSON directly into the relevant columns—no parsing needed.

• Go with Option 1 if you’re building analytics dashboards or need to join this data with other relational tables.
• Go with Option 2 if you need a balance between simplicity and query flexibility.
• Go with Option 3 only for temporary storage or quick proof-of-concept projects.

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