Specifies the maximum amount of disk storage, in bytes, that any Impala query can consume on any host using the "spill to disk" mechanism that handles queries that exceed the memory limit.

Syntax:

Specify the size in bytes, or with a trailing m or g character to indicate megabytes or gigabytes. For example:

-- 128 megabytes.
set SCRATCH_LIMIT=134217728

-- 512 megabytes.
set SCRATCH_LIMIT=512m;

-- 1 gigabyte.
set SCRATCH_LIMIT=1g;

Usage notes:

A value of zero turns off the spill to disk feature for queries in the current session, causing them to fail immediately if they exceed the memory limit.

The amount of memory used per host for a query is limited by the MEM_LIMIT query option.

The more DataNodes in the cluster, the less memory is used on each host, and therefore also less scratch space is required for queries that exceed the memory limit.

Type: numeric, with optional unit specifier

Default: -1 (amount of spill space is unlimited)

Related information:

SQL Operations that Spill to Disk, MEM_LIMIT Query Option