Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

Column Based Table Vs Row Based Table

Solved Go to solution
Highlighted

Column Based Table Vs Row Based Table

New Contributor

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.

1 ACCEPTED SOLUTION

Accepted Solutions

Re: Column Based Table Vs Row Based Table

Cloudera Employee
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.
1 REPLY 1

Re: Column Based Table Vs Row Based Table

Cloudera Employee
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.
Don't have an account?
Coming from Hortonworks? Activate your account here