Table 21 Role of the attributes in the data organization strategies and their impact on processing time and CPU usage
From: Evaluating partitioning and bucketing strategies for Hive-based Big Data Warehousing systems
| Data organization strategy | Data model | Attributes | Decrease in processing time | Decrease in CPU usage | Role of the attributes |
|---|---|---|---|---|---|
| Multiple partitioning |
SS-P DT-P |
“Od_Year” “S_Region” | Yes | Yes | Attributes are used as filters in the “where” conditions, and in the “group by” and “order by” clauses |
| SS-P |
“S_Region” “S_Nation” “S_City” | Yes | NA | Attributes are used as filters in the “where” conditions, and in the “group by” and “order by” clauses | |
| Bucketing |
SS-B DT-B | “Orderkey” | No | No | Attribute not used in the “where” conditions nor used for “group by” or “order by” |
| DT-B |
“Od_Year” “P_Brand” | Yes | Yes | Attributes are used as filters in the “where” conditions, and in the “group by” and “order by” clauses | |
| SS-B | “Suppkey” |
Yes (Hive) No (Presto) |
No (SF = 100) Yes (Hive, SF = 300) | Attribute not used in the “where” conditions nor used for “group by” or “order by”. Attribute used for joining tables | |
| SS-B |
“Orderdate” “Custkey” “Suppkey” “Partkey” | No | No | Attributes not used in the “where” conditions nor used for “group by” or “order by”. Attributes used for joining tables | |
| Partitioning and bucketing | SS-PB |
“Od_Year” “Orderkey” | No | NA | Only “Od_Year” is used in the “where” conditions, and in the “group by” and “order by” clauses |
| SS-PB |
“S_Region” “Suppkey” | Yes | NA | Only “S_Region” is used in the “where” conditions. “Suppkey” is used for joining tables | |
|
SS-PB DT-PB |
“Od_Year” “S_Region” “Suppkey” | Yes | Yes |
“Od_Year” and “S_Region” are used in the “where” conditions, and “Od_Year” is also used in the “group by” and “order by” clauses. “Suppkey” is used for joining tables in the SS-PB scenario | |
| DT-PB |
“Od_Year” “P_Brand” | Yes | NA | Attributes are used as filters in the “where” conditions, and in the “group by” and “order by” clauses |