Column Based Table Vs Row Based Table

[#1]

Hi everyone!
The question is What is better for the performace, we have a table with a PK and a measure variable day by day:
- To have the PK and one column with the measure day by day (365 columns for a given year)

- To have the PK, the date column and one columsn with the measure.

Basically I need to know what would be better for HIVE. An structure based on rows or based on columns.

[#2 · Accepted solution]

I recommend the 2nd option where you have 3 columns only: (PK, DATE, MEASURE).

You cannot update records on Hive, so having the 365 columns will leave 364 columns unused, and this causes extra storage on your files (like separators chars, schema information, etc).

Also, for read performance, 3 columns is still better than 365. Hive reads the full record every time you do a query, it then selects the columns you want, and applies the filter from the WHERE statement. This select/filter will happen with 3 or 365 columns, so 3 will be faster.

Also, you're queries would be shorter, as you only need to filter the query by date (instead of looking for columns that have measure data). And, if you use columnar storage files (like Parquet), this filter may be faster.