question Re: Multiple methods to create AVRO based Hive table in Archives of Support Questions (Read Only)

Multiple methods to create AVRO based Hive table

nrbndsdb0509 — Mon, 12 Jun 2017 21:44:20 GMT

We can create the same table using one of the below two queries:

I have seen that they both result in the same table.

so how do they differ? and if they differ, when do I use one over the other?

CREATE TABLE sample_table
STORED AS AVRO
TBLPROPERTIES('avro.schema.url' = '<some location>');

CREATE TABLE sample_table
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.avro.AvroSerDe'
WITH SERDEPROPERTIES ('avro.schema.url'='file:///tmp/schema.avsc')
STORED as INPUTFORMAT 'org.apache.hadoop.hive.ql.io.avro.AvroContainerInputFormat'
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.avro.AvroContainerOutputFormat';

Re: Multiple methods to create AVRO based Hive table

bhopp — Tue, 13 Jun 2017 04:26:26 GMT

They are the same. Hive has a few shortcuts for common SerDes and Avro is one of them. You can use one of the following, or specify your own INPUTFORMAT and OUTPUTFORMAT classes.

file_format:
  : SEQUENCEFILE
  | TEXTFILE    -- (Default, depending on hive.default.fileformat configuration)
  | RCFILE      -- (Note: Available in Hive 0.6.0 and later)
  | ORC         -- (Note: Available in Hive 0.11.0 and later)
  | PARQUET     -- (Note: Available in Hive 0.13.0 and later)
  | AVRO        -- (Note: Available in Hive 0.14.0 and later)
  | INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname

Re: Multiple methods to create AVRO based Hive table

nrbndsdb0509 — Tue, 13 Jun 2017 09:47:02 GMT

Thank you!

Then if I am to use one of the common SerDes, Avro in this case, I can get by with just

CREATE TABLE sample_table
STORED AS AVRO
TBLPROPERTIES('avro.schema.url' = '<some location>');

rather than use the longer format?

Re: Multiple methods to create AVRO based Hive table

bhopp — Wed, 14 Jun 2017 01:40:17 GMT

Yep, that should work just fine. Longer format is useful if you want to use custom SerDes such as the OpenCSV serde, but the table created using STORED AS AVRO will be identical as the one created explicitly using the org.apache.hadoop.hive.serde2.avro.AvroSerDe.

Re: Multiple methods to create AVRO based Hive table

nrbndsdb0509 — Wed, 14 Jun 2017 09:32:42 GMT

Thank you so much for the explanations!