This option determines the join distribution that Impala uses when any of the tables involved in a join query is missing statistics.
Impala optimizes join queries based on the presence of table statistics,
which are produced by the Impala COMPUTE STATS statement.
By default, when a table involved in the join query does not have statistics,
Impala uses the "broadcast" technique that transmits the entire contents
of the table to all executor nodes participating in the query. If one table
involved in a join has statistics and the other does not, the table without
statistics is broadcast. If both tables are missing statistics, the table
that is referenced second in the join order is broadcast. This behavior
is appropriate when the table involved is relatively small, but can lead to
excessive network, memory, and CPU overhead if the table being broadcast is
large.
Because Impala queries frequently involve very large tables, and suboptimal
joins for such tables could result in spilling or out-of-memory errors,
the setting DEFAULT_JOIN_DISTRIBUTION_MODE=SHUFFLE lets you
override the default behavior. The shuffle join mechanism divides the corresponding rows
of each table involved in a join query using a hashing algorithm, and transmits
subsets of the rows to other nodes for processing. Typically, this kind of join is
more efficient for joins between large tables of similar size.
The setting DEFAULT_JOIN_DISTRIBUTION_MODE=SHUFFLE is
recommended when setting up and deploying new clusters, because it is less likely
to result in serious consequences such as spilling or out-of-memory errors if
the query plan is based on incomplete information. This setting is not the default,
to avoid changing the performance characteristics of join queries for clusters that
are already tuned for their existing workloads.
Type: integer
The allowed values are BROADCAST (equivalent to 0)
or SHUFFLE (equivalent to 1).
Examples:
The following examples demonstrate appropriate scenarios for each setting of this query option.
-- Create a billion-row table.
create table big_table stored as parquet
as select * from huge_table limit 1e9;
-- For a big table with no statistics, the
-- shuffle join mechanism is appropriate.
set default_join_distribution_mode=shuffle;
...join queries involving the big table...
-- Create a hundred-row table.
create table tiny_table stored as parquet
as select * from huge_table limit 100;
-- For a tiny table with no statistics, the
-- broadcast join mechanism is appropriate.
set default_join_distribution_mode=broadcast;
...join queries involving the tiny table...
compute stats tiny_table;
compute stats big_table;
-- Once the stats are computed, the query option has
-- no effect on join queries involving these tables.
-- Impala can determine the absolute and relative sizes
-- of each side of the join query by examining the
-- row size, cardinality, and so on of each table.
...join queries involving both of these tables...
Related information:
COMPUTE STATS Statement, Joins in Impala SELECT Statements, Performance Considerations for Join Queries