Manual Slicing

Manual slicing provides full control over how the data is divided by allowing the specification of exact values for slice boundaries as a space-separated list. Choose slice boundaries such that each slice is under 10 GB to prevent long-running slices and ensure consistent forward progress.

1 2500 5000 7500 99999

Automatic Slicing

Automatic slicing analyzes data distribution and calculates optimal boundaries. 

The following inputs are required:

• Slice Column: The column to slice on (e.g., customer_id, order_date, last_name).

• Number of Slices: The desired number of slices (e.g., 10).

• Prefix(chars): Number of leading digits or characters used to calculate slice boundaries.

To configure and run automatic slicing:

1. Navigate to the Table Setup tab in XL Ingest and select the table from the list on the left.

2. In the Table Settings panel, select a column to use for slicing. 

3. Click Auto-Slice in the Define Slices section to open the Auto-Slice dialog.

4. Click Schedule Count to retrieve table statistics from the source (count, max, min, size, time).

5. Enter the desired No. of Slices (total number of partitions to create).

   Note: The actual number of slices created in automatic slicing may vary based on your data distribution.

6. Enter Prefix (chars) to control slice boundary granularity. This value determines how many leading characters or digits are used to group values into slices.

7. Review the calculated values including records per slice, slice size, and time per slice.

8. Click Schedule Slice to generate the slices.

9. Navigate to the Table Status tab, select the table, and click Sync to begin extraction.

Using SQL on the Source to Compute Slice Boundaries

When using manual slicing, you can calculate boundaries with assistance from the source SQL database. You can create boundaries for slices of roughly equal size (an approximation of an equi-depth histogram, a type of histogram where the bars are of roughly equal length, and the boundaries of each bar vary in width) using SQL with the NTILE window function as follows:

Set number_of_buckets to the (size of your data / 10GB ) + 1  to create slices of size just under 10GB for large tables.

WITH RankedData AS (
	SELECT
	    your_column,
	    NTILE(number_of_buckets) OVER (ORDER BY your_column)
	    AS bucket_number
	FROM your_table
),
BucketBoundaries AS (
	SELECT
            bucket_number,
            MIN(your_column) AS lower_bound,
            MAX(your_column) AS upper_bound,
            COUNT(*) AS row_count
	FROM
	    RankedData
	GROUP BY
	    bucket_number
)
SELECT
	bb.bucket_number,
	bb.lower_bound,
	bb.upper_bound,
	bb.row_count
FROM
	BucketBoundaries bb
ORDER BY
	bb.bucket_number;

Note: If your slice column uses UUID or other data types that do not support MIN/MAX functions directly, cast the column to a string type.

Consider the following table:

CREATE TABLE orderdetails (
    orderNumber      int(11)        NOT NULL DEFAULT '0',
    productCode      varchar(15)    CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '',
    quantityOrdered  int(11)        DEFAULT NULL,
    priceEach        decimal(10,2)  DEFAULT NULL,
    orderLineNumber  smallint(6)    DEFAULT NULL,
    PRIMARY KEY (orderNumber, productCode)
);

To calculate 10 slices on orderNumber column, you can run:

WITH RankedData AS (
   SELECT
       orderNumber,
       NTILE(10) OVER (ORDER BY orderNumber) AS bucket_number
   FROM
       orderdetails
),
BucketBoundaries AS (
   SELECT
       bucket_number,
       MIN(orderNumber) AS lower_bound,
       MAX(orderNumber) AS upper_bound
   FROM
       RankedData
   GROUP BY
       bucket_number
)
SELECT
   CONCAT(
       GROUP_CONCAT(lower_bound ORDER BY bucket_number SEPARATOR ' '),
       ' ',
       MAX(upper_bound) + 1
   ) AS boundaries
FROM
   BucketBoundaries;

Note: This example uses MySQL syntax. If using PostgreSQL, replace GROUP_CONCAT with STRING_AGG. For other databases, refer to your database documentation for the equivalent aggregation function.

This query returns a space-separated list of boundaries:

10100 10135 10167 10194 10226 10263 10293 10322 10355 10389 10426

The orderNumber column in this example has a minimum value of 10100 and a maximum value of 10425. If new records may be inserted during extraction, configure the last boundary to a higher value to ensure all records are included.