Tutorial | Repartition a non-partitioned dataset#

In Dataiku, partitioning refers to the splitting of a dataset along meaningful dimensions. Each partition contains a subset of the dataset. With partitioning, you can process different portions of a dataset independently and have some incrementality for operations based on a dataset.

We recommend that you have a good understanding of the two partitioning models of Dataiku before reading this article:

  • File-based partitioning

  • Column-based partitioning for non-file datasets

There is one case which is not covered by these two models: when you have a files-based dataset (i.e: a filesystem, HDFS, S3 or uploaded dataset), where the files don’t map directly to partitions.

For example, if you have a collection of files containing unordered timestamped data, and want to partition on the date. In the regular files-based partitioning model, each file must belong to a single partition, which is not the case here.

To solve this, you have to use the partition redispatch feature of Dataiku. This feature allows you to go from a non-partitioned dataset to a partitioned dataset. Each row is assigned to a partition dynamically based on columns.

Note

Unlike regular builds, when you build a redispatched dataset, you build all partitions at once. Normally in Dataiku, each activity builds a single dataset partition.

Get started#

In this tutorial, you will partition a non-partitioned dataset using the partition redispatch feature in two ways:

  • Via a Sync recipe (on an existing column)

  • Via a Prepare recipe (on a newly-created column)

Dataiku screenshot of the final flow annotated to show the tutorial objectives.

Create the project#

We’ll be using an extract from the “Blue book for Bulldozers” Kaggle competition data. It is a dataset where each row represents the sale of a used bulldozer.

  1. From the homepage of the Design node, click +New Project > DSS tutorials > Advanced Designer > Redispatch partitions.

Note

You can also download the starter project from this website and import it as a zip file.

Redispatch by year (Sync recipe)#

The source dataset of bulldozers is not partitioned (it is one CSV file). To redispatch it, let’s first use a Sync recipe, enabling a specific repartitioning mode.

Create the Sync recipe#

  1. From the Flow, select the bulldozer_small_extract dataset, and click on Sync from the visual recipes section of the Actions bar.

  2. Name the output dataset bulldozer_partitioned.

  3. Click Create Recipe, accepting the default storage location.

The name of the output dataset might seem strange because, at this point, we’re leaving the output dataset unpartitioned. That’s because, since there is no partitioning yet in the project, Dataiku cannot suggest an existing partitioning scheme. We’ll fix that shortly!

Partition the output dataset by year#

Let’s partition the yet-to-be-built output dataset.

  1. Navigate to the Settings tab of the bulldozer_partitioned dataset.

  2. In the Partitioning subtab, click Activate Partitioning.

Partitioning tab of the output dataset.

Let’s add a time-based (year) dimension.

  1. Click Add Time Dimension.

  2. Provide YearMade as the dimension name. YEAR should already be selected as the period.

  3. Click on the Click to insert in pattern link to automatically prepare the file paths pattern.

  4. Save the settings, and return to the parent Sync recipe.

Setting the pattern that determines partitioning.

Warning

The name of the partitioning dimension MUST match the name of the column in the source dataset. In the source dataset, the column is called YearMade, so that’s how we name the partitioning dimension.

Note

The pattern here was %Y/.*. You can learn more about defining patterns for file-based partitioned datasets in the reference documentation.

Activate redispatch mode#

Now that our output dataset is partitioned, a new option has appeared in the Sync recipe: Redispatch partitioning according to input columns.

This is the option that we want: it will tell Dataiku to dispatch each row of the input dataset to its own partition, and that a build must actually build ALL partitions.

  1. On the Configuration tab of the Sync recipe, check the option to Redispatch partitioning according to input columns.

    You’ll notice that the Run button is still grayed out. Even if the recipe will build ALL partitions, you must still select one because that’s the “normal” way of building.

  2. Click on the Click to select partitions link, and enter anything for YearMade (for example: 1200, even though there is no bulldozer where YearMade=1200).

  3. Click Run.

You should expect a warning to update the schema. For now, update the schema.

Redispatch setting for a Sync recipe.

Explore partitions#

Once the job is complete, we can explore the output dataset.

  1. Within the Settings tab of the bulldozer_partitioned dataset, navigate to the Partitioning subtab.

  2. Click List partitions.

Partition status on the newly partitioned dataset.

Dataiku now detects 48 partitions. Each partition now only has the bulldozers of a given year.

  1. In the Explore tab, click on the left to open the sample configuration.

  2. Under Use partitions, click Select partitions.

  3. Click to retrieve the full list of partitions.

  4. Select 2003 and 2004 for demonstration.

  5. Click Update Sample, and note how the sample size reduces.

Selecting desired partitions in the Sampling pane.

Redispatch by day (Prepare recipe)#

We have successfully redispatched an unpartitioned and unsegregated dataset, based on the values in one column.

What if we need to redispatch, but not based directly on the values in an existing column? What if we need some preprocessing? The good news is, you can! Just like the Sync recipe, the Prepare recipe allows you to redispatch. Let’s redispatch our dataset by the day on which the sale was made.

Prepare the partition identifier column#

The saledate column contains the date of sale, but it is not in the format we want. Let’s fix this with a Prepare recipe.

  1. From the bulldozer_small_extract dataset, create a new Prepare recipe.

  2. Name the output dataset bulldozer_by_saledate.

  3. From the saledate column dropdown, add the suggested Parse date processor.

  4. Click Use Date Format to accept the first suggested format.

  5. Leave the output column empty to parse the column in place.

Dataiku screenshot of a Prepare recipe step parsing a date.

After this step, saledate is now a timestamp column. For our partitioning example, we want a partition identifier in the form “yyyy-MM-dd”.

  1. Click on the header of the saledate column, and select More actions > Reformat date.

  2. Reduce the date format to yyyy-MM-dd.

  3. Name the output column day_of_sale.

Dataiku screenshot of a Prepare recipe step formatting a date.

We now have the column we want to use as the partition identifier. This is one advantage of using a Prepare recipe instead of a Sync recipe!

Let’s save our recipe, and partition the output dataset by the new identifier column.

Partition the dataset, activate, and run#

Now partition the bulldozer_by_saledate dataset by the day_of_sale column, and adjust the Prepare recipe to redispatch those partitions.

Try this on your own, and refer to the detailed notes below if necessary.

Detailed steps
  1. From the Settings tab of the bulldozer_by_saledate dataset, navigate to the Partitioning subtab.

  2. Click Activate Partitioning.

  3. Click Add Time Dimension.

  4. Provide day_of_sale as the name of the partitioning dimension and DAY as the period.

  5. Click to insert the pattern %Y/%M/%D/.*.

  6. Click Save.

    Dataiku screenshot of the partitioning subtab.
  7. Return back to the parent Prepare recipe.

  8. In the Advanced tab, click to enable the partition redispatch option.

  9. From the Script tab, run the recipe.

Note

Once again, the actual value that appears for day_of_sale does not matter when redispatching partitions.

After running the Prepare recipe, you’ll see in the Status tab of the output dataset that you now have 588 partitions!

Recap#

We’ve demonstrated two methods for using the partition redispatch feature in order to create a partitioned dataset from a non-partitioned input.

By using a Prepare recipe rather than a Sync recipe, we’ve been able to create our partitioning dimension column, rather than being restricted to the columns already present in the dataset.

Redispatch partitioning is a powerful feature that allows you to deal with datasets that have not been designed in a way that makes them easily partitionable, and transform them into partitioned datasets.

Redispatch partitioning is also available on non-file sources and targets.

The redispatch recipe is automatically rerun each time you use a Recursive smart build, if the input data has changed (provided that the input data is files-based).