Leveraging Azure Data Lake Partitioning to Recalculate Previously Processed Days


Many data flows will require partial reloading of U-SQL tables due to the need to recalculate a previously loaded result.  Often times this is a full or partial recalculation of a previous day’s results.  For this reason, partitioning your data by a time slice can make it more efficient to do this recalculation. Partitioning by time allows you to truncate a specific slice and rerun the load process for that time period in the event of a need to re-compute the data.  This is fairly typical in large data warehousing scenarios also as deletes can be problematic from a performance standpoint over time.

The question is what is the right time period to use? The answer is it depends on the size of your partitions.  Generally, for managed tables in U-SQL, you want to target about 1 GB per partition.  So, if you are bringing in say 800 mb per day then daily partitions are about right.  If instead you are bringing in 20 GB per day, you should look at hourly partitions of the data.

In this post, I’d like to take a look at two common scenarios that people run into.  The first is full re-compute of partitions data and the second is a partial re-compute of a partition.  The examples I will be using are based off of the U-SQL Ambulance Demo’s on Github and will be added to the solution for ease of your consumption.

We’ll be working off of a new table in the Ambulance Demo Database called vehiclesP.  This table will be created with two partitions on the event_date column.

CREATE TABLE vehiclesP(
vehicle_id int
, entry_id long
, event_date DateTime
, latitude float
, longitude float
, speed int
, direction string
, trip_id int?
, INDEX idx CLUSTERED (vehicle_id ASC) PARTITIONED BY BUCKETS (event_date) HASH (vehicle_id) INTO 2
);
DECLARE @pdate1 DateTime = new DateTime(2014, 9, 14, 00,00,00,00,DateTimeKind.Utc);
DECLARE @pdate2 DateTime = new DateTime(2014, 9, 15, 00,00,00,00,DateTimeKind.Utc);
ALTER TABLE vehiclesP ADD PARTITION (@pdate1), PARTITION (@pdate2);

The scenario for this first example is that you have found that all data for 2014-09-15 needs to be reloaded due to some error in the previous data. We will accomplish this be extracting the new data from ADLS into a variable, truncating the partition that we want to update and then loading the data in the variable to that partition.

DECLARE @pdate2 DateTime = new DateTime(2014, 9, 15, 00,00,00,00,DateTimeKind.Utc);
DECLARE @date2 DateTime = DateTime.Parse("2014-09-15");
DECLARE @ADL_DIR string = "[insert your path here]/Ambulance/";
DECLARE @ADL_FILESET string = @ADL_DIR + "vehicle{vid:*}_{date:MM}{date:dd}{date:yyyy}.{*}";

@data =
EXTRACT vehicle_id int,
entry_id long,
event_date DateTime,
latitude float,
longitude float,
speed int,
direction string,
trip_id int?,
vid int // virtual file set column
,
date DateTime // virtual file set column
FROM @ADL_FILESET
USING Extractors.Csv();

TRUNCATE TABLE vehiclesP PARTITION (@pdate2);

// Load the day statically.
INSERT vehiclesP PARTITION (@pdate2)
SELECT vehicle_id
, entry_id
// Do not specify: , event_date
, latitude
, longitude
, speed
, direction
, trip_id
FROM @data
WHERE date == @date2;

As your data sizes and complexity of your ETL increase, it may be more efficient to reload only the data that needs updating instead of the entire partition. The next scenario is more common in that you may want to do a partial reload of a previous partition. In this example, we found that the data for Ambulance 3 on 2014-09-14 was incorrect and we need to reload that data only. We accomplish this by capturing the data from the existing table partition we want to keep into a variable, pull data the data we want to recalculate (Ambulance 3 on 2014-09-15) from ADLS into another variable, union them together and then truncate and reload the data into our target partition.

//assume you want to reload all the data for 9/15 associated with Vehicle 3.
DECLARE @pdate2 DateTime = new DateTime(2014, 9, 15, 00,00,00,00,DateTimeKind.Utc);
DECLARE @vehicleID int = 3;
DECLARE @date2 DateTime = DateTime.Parse("2014-09-15");
DECLARE @date3 DateTime = DateTime.Parse("2014-09-16");
DECLARE @ADL_DIR string = "/[insert your path here]/Ambulance/";
DECLARE @ADL_FILESET string = @ADL_DIR + "vehicle{vid:*}_{date:MM}{date:dd}{date:yyyy}.{*}";

//get all data from partition except data we want to delete and reload
@existingdata =
SELECT vehicle_id,
entry_id,
event_date,
latitude,
longitude,
speed,
direction,
trip_id
FROM dbo.vehiclesP
WHERE vehicle_id != @vehicleID
AND event_date >= @date2 AND event_date = @date2 AND date < @date3
AND vehicle_id == @vehicleID;

TRUNCATE TABLE vehiclesP PARTITION (@pdate2);

//union together the data kept from the table and the data loaded from ADLS, insert
@datatobeloaded =
SELECT vehicle_id
, entry_id
// , event_date (don't specify)
, latitude
, longitude
, speed
, direction
, trip_id
FROM @existingdata
UNION ALL
SELECT vehicle_id
, entry_id
// , event_date (don't specify)
, latitude
, longitude
, speed
, direction
, trip_id
FROM @data;

INSERT vehiclesP
PARTITION ( @pdate2 )
SELECT vehicle_id,
entry_id,
latitude,
longitude,
speed,
direction,
trip_id
FROM @datatobeloaded;

For more information around creating tables in Azure Data Lake, please take a look at the U-SQL reference document.

Comments (4)

  1. Marcus Kellermann says:

    Brian
    Useful article, but is it possible to delete a range of partitions? Say you don’t know up front what days are in the data that you want to reprocess, or you are dealing with a tumbling time window. Can you query for distinct days within the incoming data and then issue a TRUNCATE statement over those days? Basically it needs to be more dynamic than being fixed to a specific data. I don’t see any looping logic in U-SQL that would allow a FOREACH(Date in DateRange) Truncate Partition type workflow.

    1. brimit says:

      Marcus, good feedback and thanks for the idea. One way to go about it is to encapsulate the code into a procedure and loop calls to that procedure with PowerShell. I’ll put together another post extending the idea further.

      1. Marcus Kellermann says:

        Brian,
        That leads to the next question then. Are there plans to add support for looping or branching logic? Or adding looping support to ADF? The use of Power Shell to orchestrate jobs in a production environment isn’t something that we are looking to do. It doesn’t have the ability to have the monitoring that ADF has. Surely this has to be a common workflow for system… At this point we’d be more likely to partition on the Month than the date, but in doing that, we’d be exceeding your recommendation for 1GB of data in a partition.

        1. MRys says:

          Hi Marcus

          some good requests/feedback. Note that U-SQL’s script model is fully declarative and depends on the functional lazy evaluation model to give you performance and scalability. Thus adding LOOPs and other procedural concepts is a bit more tricky. However, I suggest that you file your use cases at http://aka.ms/adlfeedback so we can assess them and the community feedback on them and prioritize them for future releases.

Skip to main content