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.

Failing when trying to insert overwrite to directory stored as Avro

Highlighted

Failing when trying to insert overwrite to directory stored as Avro

Explorer
Hi,
 
I am trying to insert overwrite to a directory on S3 to avro format which is failing, tried both the approches query1 and query 2 as given below.
Please let me know if I am doing something wrong. 
 
 

Hive version -- /opt/cloudera/parcels/CDH-5.4.5-1.cdh5.4.5.p0.7/jars/hive-common-1.1.0-cdh5.4.5.jar!/hive-log4j.properties

Beeline version -- Beeline version 1.1.0-cdh5.4.5 by Apache Hive

Query 1: 
 
insert overwrite directory 's3a://**********/' 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' select * from <hdfs_table>;
 
Query 2: 
insert overwrite directory 's3a://**********/' stored as AVRO select * from <hdfs_table>;
 
ERROR:
NoViableAltException(220@[])
at org.apache.hadoop.hive.ql.parse.HiveParser.regularBody(HiveParser.java:41104)
at org.apache.hadoop.hive.ql.parse.HiveParser.queryStatementExpressionBody(HiveParser.java:40330)
at org.apache.hadoop.hive.ql.parse.HiveParser.queryStatementExpression(HiveParser.java:40206)
at org.apache.hadoop.hive.ql.parse.HiveParser.execStatement(HiveParser.java:1526)
at org.apache.hadoop.hive.ql.parse.HiveParser.statement(HiveParser.java:1062)
at org.apache.hadoop.hive.ql.parse.ParseDriver.parse(ParseDriver.java:199)
at org.apache.hadoop.hive.ql.parse.ParseDriver.parse(ParseDriver.java:166)
at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:394)
at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:306)
at org.apache.hadoop.hive.ql.Driver.compileInternal(Driver.java:1111)
at org.apache.hadoop.hive.ql.Driver.runInternal(Driver.java:1159)
at org.apache.hadoop.hive.ql.Driver.run(Driver.java:1048)
at org.apache.hadoop.hive.ql.Driver.run(Driver.java:1038)
at org.apache.hadoop.hive.cli.CliDriver.processLocalCmd(CliDriver.java:207)
at org.apache.hadoop.hive.cli.CliDriver.processCmd(CliDriver.java:159)
at org.apache.hadoop.hive.cli.CliDriver.processLine(CliDriver.java:370)
at org.apache.hadoop.hive.cli.CliDriver.executeDriver(CliDriver.java:756)
at org.apache.hadoop.hive.cli.CliDriver.run(CliDriver.java:675)
at org.apache.hadoop.hive.cli.CliDriver.main(CliDriver.java:615)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at org.apache.hadoop.util.RunJar.run(RunJar.java:221)
at org.apache.hadoop.util.RunJar.main(RunJar.java:136)
FAILED: ParseException line 1:65 cannot recognize input near 'ROW' 'FORMAT' 'SERDE' in statement
 
Any help is appreciated !!!
2 REPLIES 2
Highlighted

Re: Failing when trying to insert overwrite to directory stored as Avro

Contributor
With CDH5.4.5, you can specify file format only if you insert into a local
directory. For example,

insert overwrite local directory '...' stored as inputformat
'org.apache.hadoop.hive.ql.io.avro.AvroContainerInputFormat' OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.avro.AvroContainerOutputFormat' select * from
;

Re: Failing when trying to insert overwrite to directory stored as Avro

Explorer

Is there anyway to write the resultant output to a directory on S3 as AVRO ?

 

Currently I am performing this in 4 steps:

1. create table <hdfs_temp_table> stored as AVRO select * from<s3_table>;

2. create table <s3_table> like <hdfs_temp_table> stored as AVRO location 's3a://';

3. insert overwrite table <s3_table> select * from <hdfs_temp_table>;

4. drop table <hdfs_temp_table>;

 

Instead of performing in 4 steps I would like to perform this in 1 step.

Don't have an account?
Coming from Hortonworks? Activate your account here