求助：如何通过ADF将分页API的复杂JSON导入SQLDB？

Step 1: Configure REST Source with Pagination #

• Use a REST Dataset pointing to your API endpoint.
• In the source transformation's Pagination settings:
  • Select the appropriate pagination type (e.g., "Page number" if the API uses a page parameter).
  • Set start page to 1, end page to 401, increment by 1. Adjust parameters if your API uses offset-based pagination (e.g., offset starts at 0, increment by 25 per page).
• Ensure the source reads the full JSON structure, including the columns (array of column names) and rows (array of record arrays) fields.

Step 2: Flatten the Rows Array #

• Add a Flatten transformation:
  • Set Unroll root to the rows array (this creates a separate row for each record in the rows array of every page).
  • Keep the columns array as a retained column (it repeats for each flattened record).

Step 3: Generate Unique Row IDs #

• Add a Derived Column transformation to create a unique identifier for each original record:

  rowNumber() as rowId
  

  This ensures we can group all key-value pairs belonging to the same record during pivoting.

Step 4: Map Values to Column Names #

• Add another Derived Column to pair each record's values with their corresponding column names using the zip function:

  zip(columns, <flattened_row_column>) as mappedColumns
  

  Replace <flattened_row_column> with the name of the column containing the flattened record array (default name may be row or similar). This creates an array of objects with key (column name) and value (record value).

Step 5: Flatten the Mapped Columns Array #

• Add a second Flatten transformation:
  • Set Unroll root to the mappedColumns array. This splits each key-value pair into a separate row, retaining the rowId to track which original record it belongs to.

Step 6: Pivot to Create Tabular Structure #

• Add a Pivot transformation:
  • Group by: Select rowId (to group all key-value pairs from the same original record).
  • Pivot key: Select mappedColumns.key (the column names from the API).
  • Pivot value: Select mappedColumns.value (the corresponding record values).
  • Aggregation: Choose First (since each record has exactly one value per column).
  • This converts the key-value pairs into a tabular format matching your target SQL table schema.

Step 7: Clean Up and Sink to SQL DB #

• Add a Select transformation to remove unnecessary columns (e.g., rowId, columns, mappedColumns).
• Add a Sink transformation connected to your SQL DB dataset:
  • Enable Auto create table if the target table doesn't exist, or manually map columns if the table is pre-created.
  • Set the write behavior (e.g., Append or Upsert) based on your requirements.

Edge Case Handling #

• If columns vary across pages, the pivot will include all unique column names from all pages. Ensure your SQL table can accommodate additional columns or add a filter to exclude unexpected columns.
• Validate that length(columns) == length(<flattened_row_column>) for all rows using a Filter transformation to avoid mismatched key-value pairs.

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