--- title: Better file formats for Impala and quick SQL in Hadoop author: admin type: post date: 2013-10-23T18:03:00+00:00 url: /tutorial-better-file-formats-for-impala-and-quick-sql/ tumblr_gethue_permalink: - http://gethue.tumblr.com/post/64879465564/tutorial-better-file-formats-for-impala-and-quick-sql tumblr_gethue_id: - 64879465564 sf_thumbnail_type: - none sf_thumbnail_link_type: - link_to_post sf_detail_type: - none sf_page_title: - 1 sf_page_title_style: - standard sf_no_breadcrumbs: - 1 sf_page_title_bg: - none sf_page_title_text_style: - light sf_background_image_size: - cover sf_social_sharing: - 1 sf_sidebar_config: - left-sidebar sf_left_sidebar: - Sidebar-2 sf_right_sidebar: - Sidebar-1 sf_caption_position: - caption-right slide_template: - default categories: - Querying --- # Impala File Formats {#docs-internal-guid-798b2644-e679-81ed-508c-e3685cd16a67} Using the best file format is crucial for getting great performances. This is one reason with JSON is no supported in the [Impala application][1]. Indeed, parsing or retrieving all the text record even for one field would damage the performance badly. Impala is recommending a series of alternative[formats][2]. We show here how to create a Hive table in Avro format containing json data and a table in the new parquet format. We are using the same Yelp data from [Episode 2 of the Season 2][3] of the Hadoop Tutorial series. ## Avro The first step is to convert our data into JSON with the help of a Pig script. Open up the [Pig Editor][4] and run:
REGISTER piggybank.jar
data = load '/user/hive/warehouse/review/yelp_academic_dataset_review_clean.json'
AS (funny:INT, useful:INT, cool:INT, user_id:CHARARRAY, review_id:CHARARRAY, text:CHARARRAY, business_id:CHARARRAY, stars:INT, date:CHARARRAY, type:CHARARRAY);
data_clean = FILTER data BY business_id IS NOT NULL AND text IS NOT NULL;
STORE data_clean INTO 'impala/reviews_avro'
USING org.apache.pig.piggybank.storage.avro.AvroStorage(
'{
"schema": {
"name": "review",
"type": "record",
"fields": [
{"name":"funny", "type":"int"},
{"name":"useful", "type":"int"},
{"name":"cool", "type":"int"},
{"name":"user_id", "type":"string"}
{"name":"review_id", "type":"string"},
{"name":"text", "type":"string"},
{"name":"business_id", "type":"string"},
{"name":"stars", "type":"int"},
{"name":"date", "type":"string"},
{"name":"type", "type":"string"},
]}
}');
Then, in the [Hive Editor][5] create the table with:
CREATE TABLE review_avro
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.avro.AvroSerDe'
STORED AS
inputformat 'org.apache.hadoop.hive.ql.io.avro.AvroContainerInputFormat'
outputformat 'org.apache.hadoop.hive.ql.io.avro.AvroContainerOutputFormat'
LOCATION '/user/romain/impala/reviews_avro'
tblproperties ('avro.schema.literal'='{
"name": "review",
"type": "record",
"fields": [
{"name":"business_id", "type":"string"},
{"name":"cool", "type":"int"},
{"name":"date", "type":"string"},
{"name":"funny", "type":"int"},
{"name":"review_id", "type":"string"},
{"name":"stars", "type":"int"},
{"name":"text", "type":"string"},
{"name":"type", "type":"string"},
{"name":"useful", "type":"int"},
{"name":"user_id", "type":"string"}]}'
);
You can now go back to Impala, and use the table after having refreshed the metadata with:
REFRESH avro_table
## Parquet
Parquet is a new column-oriented binary file format, particularly efficient in Impala. Here is how to create a table from the Impala app:
CREATE TABLE review_parquet LIKE review STORED AS PARQUETFILE;
And then load data:
INSERT OVERWRITE review_parquet SELECT * FROM review;
Take the time to read about the goal of each format and how to enable compression. If you want to know more, the [Impala tuning guide][6] is a good reference too.
As usual feel free to comment on the [hue-user][7] list or [@gethue][8]!
[1]: http://gethue.tumblr.com/post/62452792255/fast-sql-with-the-impala-query-editor
[2]: http://www.cloudera.com/content/cloudera-content/cloudera-docs/Impala/latest/Installing-and-Using-Impala/ciiu_file_formats.html
[3]: http://gethue.tumblr.com/post/60937985689/hadoop-tutorials-ii-2-execute-hive-queries-and
[4]: http://gethue.tumblr.com/tagged/pig
[5]: http://gethue.tumblr.com/tagged/hive
[6]: http://www.cloudera.com/content/cloudera-content/cloudera-docs/Impala/latest/Installing-and-Using-Impala/ciiu_performance.html
[7]: http://groups.google.com/a/cloudera.org/group/hue-user
[8]: https://twitter.com/gethue