Database Reference
In-Depth Information
SELECT * WHERE ci.state_code = 'AK'
PARTITION (state = 'AZ')
SELECT * WHERE ci.state_code = 'AZ'
PARTITION (state = 'AR')
SELECT * WHERE ci.state_code = 'AR';
When you use this format, the table is scanned only once. Each record in
the source table is evaluated against each
WHERE
clause. If it matches, it
is inserted into the associated partition. Because the record is compared
againsteachclause(evenifit'salreadymatchedtoaprevious
WHERE
clause),
records can be inserted into multiple partitions, or none at all if it doesn't
match any clauses.
You can also do dynamic partitioning. This is based on matching the last
columns in the
SELECT
statement against the partition. For example, in
the following
FROM
. . .
INSERT INTO
statement, the country code has a
hard-coded value, meaning it is static. However, the state partition does
not have a hard-coded value, which makes it dynamic. The
state_code
column is used to dynamically determine what partition the record should
be placed in. This isn't based on matching the column name; it's based on
ordinal position in the
SELECT
. In this case, there is one partition column,
so the last column in the
SELECT
list is used. If there were two partition
columns, the last two columns would be used, and so on:
FROM customer_import ci
INSERT INTO TABLE customer
PARTITION (country='US', state)
SELECT name, city, postalCode, purchases,
state_code;
WARNING
Be careful when using dynamic partitioning. It can be easy to
inadvertently create a massive number of partitions and impact
performance negatively. By default, it operates in strict mode, which
means at least some of the partition columns must be static. This can
avoid runaway partition creation.
