Performance tuning your environment is recommended as a regular part of maintaining your system.
Basic knowledge of Treasure Data.
Basic knowledge of the Hive query language.
Leveraging Time-based Partitioning
All imported data is automatically partitioned into hourly buckets, based on the ‘time’ field within each data record. By specifying the time range to query, you avoid reading unnecessary data and can thus speed up your query significantly.
WHERE time <=> Integer
When the ‘time’ field within the WHERE clause is specified, the query parser will automatically detect the partitions to process. This auto detection will not work if you specify the time with
float instead of
An easier way to slice the data is to use TD_TIME_RANGE UDF.
However, if you use division in TD_TIME_RANGE, time partition optimization doesn’t work. For instance, the following conditions disable optimization.
Set Custom Schema
All tables have two fields:
You can also set custom schema on the tables.
After setting the schema, queries issued with named columns instead of ‘v’ will use the schema information to achieve a more optimized execution path. In particular, GROUP BY performance will improve significantly.
DISTRIBUTE BY…SORT BY v. ORDER BY
In Hive, ORDER BY slows because it forces all the data to go into the same reducer node. By doing this, Hive ensures that the entire dataset is totally ordered.
Sometimes we do not require total ordering. For example, suppose you have a table called
user_action_tablewhere each row has
time. Your goal is to order them by time per user_id.
If you are doing this with ORDER BY, you would run
However, you can achieve the same result with
This is because all the rows belonging to the same user_id go to the same reducer (“DISTRIBUTE BY user_id”) and in each reducer, rows are sorted by time (“SORT BY time”). This is faster than the other query because it uses multiple reducers as opposed to a single reducer.
You can learn more about the differences between ORDER BY and SORT BY.
Avoid SELECT count(DISTINCT field) FROM tbl
This query looks familiar to SQL users, but this query is very slow because only one reducer is used to process the request.
Rewrite the query to leverage multiple reducers:
Considering the Cardinality within GROUP BY
Often, GROUP BY can be faster if you carefully order a list of fields within the GROUP BY clause in order of high cardinality.
SELECT GROUP BY uid, gender
SELECT GROUP BY gender, uid
Efficient Top-k Query Processing using each_top_k
Efficient processing of Top-k queries is a crucial requirement in many interactive environments that involve massive amounts of data. The TD Hive extension
each_top_k helps run Top-k processing efficiently.
Suppose the following table as the input
Then, list top-2 students for each class
The standard way using SQL window function would be as follows:
An alternative and efficient way to compute top-k items using
each_top_k is as follows:
`CLUSTER BY x` is a synonym of `DISTRIBUTE BY x CLASS SORT BY x` and required when using `each_top_k`.
`each_top_k` is beneficial where the number of grouping keys are large. If the number of grouping keys are not so large (e.g., less than 100), consider using `rank() over` instead.
The function signature of
each_top_k is follows:
Any number types or timestamp are accepted for the type of
value but it MUST be not NULL. Do null handling like
if(value is null, -1, value) to avoid null.
k is less than 0, reverse order is used and tail-K records are returned for each
The ranking semantics of
each_top_k follows SQL’s
dense_rank and then limits results by
k. See Hivemall user guide for further information.
Exploding Multiple Arrays at the Same Time with TD_NUMERIC_RANGE and TD_ARRAY_INDEX
The combination of
TD_ARRAY_INDEX allows you to emit all the elements of an array into multiple rows using the LATERAL VIEW.