Evaluating partitioning and bucketing strategies for Hive-based Big Data Warehousing systems

Journal of Big Data

Table 1 SSB execution times (in seconds): partitioning by “Od_Year” and “S_Region” (star schema (SS), star schema with partitions (SS-P), denormalized table (DT), denormalized table with partitions (DT-P))

	SF = 30		SF = 100		SF = 300		SF = 30		SF = 100		SF = 300
	HIVE						PRESTO
	SS	SS-P	SS	SS-P	SS	SS-P	SS	SS-P	SS	SS-P	SS	SS-P
Q1.1	25	21	31	22	44	25	5	2	13	4	36	8
Q1.2	24	27	29	33	42	54	5	7	13	18	34	48
Q1.3	24	21	29	22	43	26	4	2	13	4	35	8
Q2.1	32	30	47	45	531	153	8	4	19	8	59	23
Q2.2	31	28	46	39	531	152	7	4	18	6	51	17
Q2.3	30	27	44	41	531	147	7	3	17	6	49	15
Q3.1	35	26	59	34	651	162	8	4	29	9	81	27
Q3.2	30	30	45	52	677	570	6	7	17	19	51	52
Q3.3	33	37	219	75	665	578	5	7	15	16	43	48
Q3.4	34	36	222	223	675	618	6	8	15	20	43	56
Q4.1	38	33	86	70	226	205	13	6	43	15	119	40
Q4.2	49	30	70	58	141	91	9	4	26	9	69	20
Q4.3	34	29	54	44	116	70	8	5	23	14	63	36
Total	420	375	982	760	4874	2849	92	63	262	149	733	399
Diff.		− 11%		− 23%		− 42%		− 32%		− 43%		− 46%

	SF = 30		SF = 100		SF = 300		SF = 30		SF = 100		SF = 300
	HIVE						PRESTO
	DT	DT-P	DT	DT-P	DT	DT-P	DT	DT-P	DT	DT-P	DT	DT-P
Q1.1	24	20	29	21	51	29	5	2	13	3	37	8
Q1.2	24	26	29	36	45	80	5	2	14	5	38	16
Q1.3	23	21	30	21	45	30	5	2	14	3	39	8
Q2.1	25	21	36	23	79	30	4	3	10	5	36	16
Q2.2	36	24	73	32	161	50	4	3	10	6	32	16
Q2.3	25	21	35	22	62	29	4	3	10	5	29	17
Q3.1	28	21	40	23	98	31	5	2	12	3	33	11
Q3.2	28	25	41	29	93	60	5	4	12	5	29	32
Q3.3	25	25	38	29	59	62	4	5	9	6	27	44
Q3.4	25	28	38	40	72	108	5	7	12	13	33	81
Q4.1	27	22	41	24	103	34	6	3	14	6	42	20
Q4.2	29	17	42	21	107	25	6	2	14	4	49	12
Q4.3	29	21	42	24	114	34	5	4	12	7	49	19
Total	349	292	516	346	1090	602	63	43	155	71	472	299
Diff.		− 16%		− 33%		− 45%		− 32%		− 54%		− 37%