基于U-SQL创建视图合并多文件重复数据遇Rowset不支持问题

Got it, let's break this down for you. The issue with using Rowset variables in a view is that views are strictly defined by a single SELECT statement—you can't do variable assignments like @sourcedata = ... in their definition. Instead, we can leverage window functions to rank records and retain only the latest entry per primary key, which fits cleanly into a view.

Here's a step-by-step solution:

1. Combine All Date-Partitioned Data #

First, we need to union all your date-based files/tables into a single dataset. Assuming each day's data is stored in a table named like data_{year}_{month}_{date} (e.g., data_2024_05_20, data_2024_05_21), we use UNION ALL to merge them.

2. Rank Records by Primary Key and Update Time #

Use the ROW_NUMBER() window function to assign a rank to each record within its primary key group. We sort by your update timestamp in descending order so the latest record gets a rank of 1.

3. Create the View #

Putting it all together, here's the view definition:

CREATE VIEW latest_merged_records AS
WITH ranked_source_data AS (
    SELECT
        *,
        -- Rank records per primary key, latest first
        ROW_NUMBER() OVER (
            PARTITION BY primary_key 
            ORDER BY update_time DESC
        ) AS record_rank
    FROM (
        -- Union all your date-partitioned tables here
        SELECT * FROM data_2024_05_20
        UNION ALL
        SELECT * FROM data_2024_05_21
        UNION ALL
        SELECT * FROM data_2024_05_22
        -- Add more tables as needed for additional dates
    ) AS combined_data
)
-- Select only the top-ranked (latest) record per primary key
SELECT 
    col1, col2, primary_key, update_time -- List all your columns except record_rank
FROM ranked_source_data
WHERE record_rank = 1;

Notes for Different Databases: #

• If your database doesn't support SELECT * EXCEPT (column) (e.g., MySQL), explicitly list all columns you need instead of using * to exclude the record_rank field.
• If you have a large number of date tables, manually writing UNION ALL gets tedious. For this, you can create a separate "base view" that unions all date tables first, then build your deduplication view on top of that:

  -- Base view to combine all date data
  CREATE VIEW all_date_data AS
  SELECT * FROM data_2024_05_20
  UNION ALL
  SELECT * FROM data_2024_05_21
  UNION ALL
  SELECT * FROM data_2024_05_22;
  
  -- Deduplication view using the base view
  CREATE VIEW latest_merged_records AS
  WITH ranked_data AS (
      SELECT
          *,
          ROW_NUMBER() OVER (PARTITION BY primary_key ORDER BY update_time DESC) AS record_rank
      FROM all_date_data
  )
  SELECT col1, col2, primary_key, update_time
  FROM ranked_data
  WHERE record_rank = 1;
  

This approach ensures your view always returns the most recent version of each record, and you can easily extend it by adding new date tables to the base union as needed.

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