Freigeben über

Leveraging Azure Data Lake Partitioning to Recalculate Previously Processed Days

  • Artikel

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 (event_date) DISTRIBUTED BY 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

  • Anonymous
    May 04, 2016
    BrianUseful 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.
    • Anonymous
      May 04, 2016
      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.
      • Anonymous
        May 04, 2016
        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.
        • Anonymous
          June 27, 2016
          Hi Marcussome 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.