...
| Table of Contents | ||
|---|---|---|
|
Use Cases for UDP
Where the lookup and aggregations are based on one or more specific columns, UDP can lead to:
efficient lookup and aggregation queries
efficient join queries
UDP can add the most value when records are filtered or joined frequently by non-time attributes::
a customer's ID, first name+last name+birth date, gender, or other profile values or flags
a product's SKU number, bar code, manufacturer, or other exact-match attributes
an address's country code; city, state or province; or postal code
Performance benefits become more significant on tables with >100M rows.
UDP can help with these Presto query types:
"Needle-in-a-Haystack" lookup on the partition key
Very large joins on partition keys used in tables on both sides of the join
Limitations
The benefits of UDP can be limited when used with more complex queries. The query optimizer might not always apply UDP in cases where it can be beneficial.
The max number of Partitioning keys is less than or equal to 3 keys
If the limit is exceeded, Presto causes the following error message:
'bucketed_on' must be less than 4 columns
No Streaming Imports
...
Limitations
The benefits of UDP can be limited when used with more complex queries. The query optimizer might not always apply UDP in cases where it can be beneficial.
The max number of Partitioning keys is less than or equal to 3 keys
If the limit is exceeded, Presto causes the following error message:
'bucketed_on' must be less than 4 columns
No Streaming Imports
Streaming imports do not support UDP. As a workaround, you can use a workflow to copy data from a table that’s receiving streaming import to the UDP table.
Use Cases for UDP
Where the lookup and aggregations are based on one or more specific columns, UDP can lead to:
efficient lookup and aggregation queries
efficient join queries
UDP can add the most value when records are filtered or joined frequently by non-time attributes::
a customer's ID, first name+last name+birth date, gender, or other profile values or flags
a product's SKU number, bar code, manufacturer, or other exact-match attributes
an address's country code; city, state or province; or postal code
Performance benefits become more significant on tables with >100M rows.
UDP can help with these Presto query types:
"Needle-in-a-Haystack" lookup on the partition key
Very large joins on partition keys used in tables on both sides of the join
Basic UDP Usage
CREATE TABLE Syntax for UDP
...