How to efficiently store data in hive?

Generally using RCFile format is a good choice. It organises the table in row groups and saves one or several groups (depending on your HDFS block size) in a HDFS file. Within a row group the data is stored in columnar format. You can optionally compress the RCFiles. Snappy is a good choice for compression if you like to minimize the load on your CPUs and do not require an optimal compression ratio. Using RCFile has two benefits. Hadoop can read and process row groups in parallel due to the data locality and redundancy with HDFS. A tasknode processing a row group benefits from the ability to skip columns that are irrelevant to a query reducing disk IO (and decompression load on the CPU). Alternatively, you can use text and snappy with block compression. Snappy is splittable so multiple mappers can read and split one large file. However, the performance of text is not ideal especially if you store mostly numbers as your example implies. You are better off using sequence files with compression (if for some reason you do not want to use RCFile). However, Hive stores rows as single value in sequence files and requires to load and decompress every value to investigate it no matter the query and columns required. Your example indicates that you partition by date. It may (depending on your data) be another optimisation to split by year, month, and day and not merely by every day. Lastly, later this year (2013) ORC file format will become part of most distributions and should be considered as an alternative to RCFile. ORC goes beyond RCFile and introduces columnar optimised storage (e.g. variable length encoding for integers), large block sizes (better disk IO and fewer file with lower namenode load), basic statistics on columns in a file and simple file indices to skip whole row groups if they don't match a query.

Answering the main question : compressed input or non-compressed input. If your priority is speed no matter what and memory is no issue then why compress it?? Compressed data will obviously slow down any MR job. However, if memory is an issue, which I am assuming is an "important" issue considering the fact that you won't be using hadoop unless you have really really large amount of data, then compression is the way to go. Answering the next question -> how to input a lzo compressed file to hive : SET hive.exec.compress.output=true; SET io.seqfile.compression.type=BLOCK; SET mapred.output.compression.codec = com.hadoop.compression.lzo.LzopCodec; CREATE EXTERNAL TABLE quality (id bigint,  total bigint,  error bigint) PARTITIONED BY (ds string) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' STORED AS INPUTFORMAT "com.hadoop.mapred.DeprecatedLzoTextInputFormat" OUTPUTFORMAT "http://org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat" LOCATION  '/user/uname/quality'; The other query will remain the same. Lzo is not bundled along with apache nor cloudera hadoop distribution due to some issues, therefore you will have to install and configure lzo native libraries for your hadoop cluster. You can try out the below link https://github.com/twitter/hadoop-lzo Also, I am assuming you are using lzo currently. However if you want to increase your speed, try out snappy. http://www.cloudera.com/blog/2011/09/snappy-and-hadoop/

You can use ORC File format as the underlying file format for Hive. This format has several advantages like better compression compared to other formats, lightweight indexes within the file, skip rows which don't pass predicate filtering etc which helps in efficient queries. You can output ORC Files from a MR program also as shown in http://hadoopcraft.blogspot.in/2014/07/generating-orc-files-using-mapreduce.html