Friday, March 2, 2018

Impala can't insert on Avro format

https://www.cloudera.com/documentation/enterprise/5-6-x/topics/impala_avro.html

Impala supports using tables whose data files use the Avro file format. Impala can query Avro tables, and in Impala 1.4.0 and higher can create them, but currently cannot insert data into them. For insert operations, use Hive, then switch back to Impala to run queries.


[cdh-vm.dbaglobe.com:21000] > create table t1(id1 int) stored as avro;
Query: create table t1(id1 int) stored as avro
Fetched 0 row(s) in 1.59s

[cdh-vm.dbaglobe.com:21000] > insert into t1 values(1);
Query: insert into t1 values(1)
Query submitted at: 2018-03-02 21:45:36 (Coordinator: http://cdh-vm.dbaglobe.com:25000)
Query progress can be monitored at: http://cdh-vm.dbaglobe.com:25000/query_plan?query_id=6847cc676e330ec0:c1d2b6d300000000
WARNINGS: Writing to table format AVRO is not supported. Use query option ALLOW_UNSUPPORTED_FORMATS to override.


[cdh-vm.dbaglobe.com:21000] > select * from t1;
Query: select * from t1
Query submitted at: 2018-03-02 21:46:00 (Coordinator: http://cdh-vm.dbaglobe.com:25000)
Query progress can be monitored at: http://cdh-vm.dbaglobe.com:25000/query_plan?query_id=974e823123b8e334:bec6f70600000000
Fetched 0 row(s) in 0.06s

[cdh-vm.dbaglobe.com:21000] > set ALLOW_UNSUPPORTED_FORMATS=true;
ALLOW_UNSUPPORTED_FORMATS set to true

[cdh-vm.dbaglobe.com:21000] > insert into t1 values(1);
Query: insert into t1 values(1)
Query submitted at: 2018-03-02 21:47:23 (Coordinator: http://cdh-vm.dbaglobe.com:25000)
Query progress can be monitored at: http://cdh-vm.dbaglobe.com:25000/query_plan?query_id=f148965b772acd2e:fa4afc8700000000
Modified 0 row(s) in 1.04s

[cdh-vm.dbaglobe.com:21000] > select * from t1;
Query: select * from t1
Query submitted at: 2018-03-02 21:47:26 (Coordinator: http://cdh-vm.dbaglobe.com:25000)
Query progress can be monitored at: http://cdh-vm.dbaglobe.com:25000/query_plan?query_id=3c4ed8a0106b5cd1:6a45ee0c00000000
+------+
| id1  |
+------+
| NULL |
+------+
Fetched 1 row(s) in 0.36s

[donghua@cdh-vm temp]$ beeline -u jdbc:hive2://cdh-vm.dbaglobe.com:10000 -n donghua
Java HotSpot(TM) 64-Bit Server VM warning: ignoring option MaxPermSize=512M; support was removed in 8.0
Java HotSpot(TM) 64-Bit Server VM warning: ignoring option MaxPermSize=512M; support was removed in 8.0
scan complete in 2ms
Connecting to jdbc:hive2://cdh-vm.dbaglobe.com:10000
Connected to: Apache Hive (version 1.1.0-cdh5.14.0)
Driver: Hive JDBC (version 1.1.0-cdh5.14.0)
Transaction isolation: TRANSACTION_REPEATABLE_READ
Beeline version 1.1.0-cdh5.14.0 by Apache Hive

0: jdbc:hive2://cdh-vm.dbaglobe.com:10000> select * from t1;
Error: java.io.IOException: org.apache.avro.AvroRuntimeException: java.io.IOException: Checksum failure (state=,code=0)

0: jdbc:hive2://cdh-vm.dbaglobe.com:10000> truncate table t1;
ERROR : FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. Exception while processing
Error: Error while processing statement: FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. Exception while processing (state=08S01,code=1)

-- From Impala
[cdh-vm.dbaglobe.com:21000] > drop table t1;
Query: drop table t1

0: jdbc:hive2://cdh-vm.dbaglobe.com:10000> create table t1 (id1 int) stored as avro;
No rows affected (0.136 seconds)

0: jdbc:hive2://cdh-vm.dbaglobe.com:10000> insert into t1 values(1);

0: jdbc:hive2://cdh-vm.dbaglobe.com:10000> select * from t1;
+---------+--+
| t1.id1  |
+---------+--+
| 1       |
+---------+--+