setting compression on hive table

hive compression hiveql avro snappy
Vikas Saxena picture Vikas Saxena · Aug 7, 2015 · Viewed 8.5k times · Source

I have a hive table based on avro schema. The table was created with the following query

CREATE EXTERNAL TABLE datatbl PARTITIONED BY (date String, int time) ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.avro.AvroSerDe' WITH SERDEPROPERTIES ( 'avro.schema.url'='path to schema file on HDFS') STORED as INPUTFORMAT 'org.apache.hadoop.hive.ql.io.avro.AvroContainerInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.avro.AvroContainerOutputFormat' LOCATION '<path on hdfs>'

So far we have been inserting data into the table by setting the following properties

hive> set hive.exec.compress.output=true; hive> set avro.output.codec=snappy;

However, if someone forgets to set the above two properties the compression is not achieved. I was wondering if there is a way to enforce compression on table itself so that even if the above two properties are not set the data is always compressed?

Answer

dbustosp picture dbustosp · Aug 7, 2015

Yes, you can set the properties in the table. Try the following: 

 CREATE EXTERNAL TABLE datatbl PARTITIONED BY (date String, int time)  
 ROW FORMAT SERDE   'org.apache.hadoop.hive.serde2.avro.AvroSerDe'  
 WITH SERDEPROPERTIES (   'avro.schema.url'='path to schema file on
 HDFS')   STORED as INPUTFORMAT  
 'org.apache.hadoop.hive.ql.io.avro.AvroContainerInputFormat'  
 OUTPUTFORMAT  
 'org.apache.hadoop.hive.ql.io.avro.AvroContainerOutputFormat' LOCATION
 '<path on hdfs>'
 TBLPROPERTIES ( "orc.compress"="SNAPPY" );

Related questions

Compress file on S3

I have a 17.7GB file on S3. It was generated as the output of a Hive query, and it isn't compressed. I know that by compressing it, it'll be about 2.2GB (gzip). How can I download this file locally as …

Read More
How to delete and update a record in Hive

I have installed Hadoop, Hive, Hive JDBC. which are running fine for me. But I still have a problem. How to delete or update a single record using Hive because delete or update command of MySQL is not working in …

Read More
How to know Hive and Hadoop versions from command prompt?

How can I find which Hive version I am using from the command prompt. Below is the details- I am using Putty to connect to hive table and access records in the tables. So what I did is- I opened …

Read More