Output formatting not correct impala

[#1]

HI ,

Query result format is not correct ,also one column data is not populating in Impala .

0: jdbc:hive2://neocc9hddn20.prod.com:2> select mtcn_nr,attemptid from compliance.core_Rtra_attempts where year=2017 and month=2 limit 5;
0289234372
0724186390
9440690205
3311840200
1729311363
+----------+------------+--+
| mtcn_nr | attemptid |
+----------+------------+--+
+----------+------------+--+
No rows selected (1.156 seconds)

while same is workijng fine in hive

0: jdbc:hive2://neocc9hdmn01.prod.com:1> select mtcn_nr,attemptid from compliance.core_Rtra_attempts where year=2017 and month=2 limit 5;
+-------------+----------------------+--+
| mtcn_nr | attemptid |
+-------------+----------------------+--+
| 8169184643 | 3000000000435887282 |
| 7747378580 | 3000000000435891774 |
| 3628896948 | 3000000000435891859 |
| 7025164684 | 3000000000435892109 |
| 5086328484 | 3000000000435893173 |
+-------------+----------------------+--+
5 rows selected (3.048 seconds)

what could be the reason ?

[#2 · Accepted solution]

Please use the impala JDBC driver not hive jdbc driver for accessing the impala via beeline.

root@nightly59-unsecure-3 ~]# mkdir -p /root/impala-jdbc/jdbc
[root@nightly59-unsecure-3 ~]# hadoop classpath
/etc/hadoop/conf:/opt/cloudera/parcels/CDH-5.9.3-1.cdh5.9.3.p0.56/lib/hadoop/libexec/../../hadoop/lib/*:/opt/clo...
[root@nightly59-unsecure-3 ~]# cd /root/impala-jdbc/jdbc/
[root@nightly59-unsecure-3 jdbc]# ll
total 13584
-rwxr-xr-x 1 root root 1554773 Apr 10 23:46 ImpalaJDBC4.jar
-rwxr-xr-x 1 root root 1307923 Apr 10 23:46 TCLIServiceClient.jar
-rwxr-xr-x 1 root root   46725 Apr 10 23:45 commons-codec-1.3.jar
-rwxr-xr-x 1 root root   60686 Apr 10 23:45 commons-logging-1.1.1.jar
-rwxr-xr-x 1 root root 7670596 Apr 10 23:45 hive_metastore.jar
-rwxr-xr-x 1 root root  596600 Apr 10 23:45 hive_service.jar
-rwxr-xr-x 1 root root  352585 Apr 10 23:46 httpclient-4.1.3.jar
-rwxr-xr-x 1 root root  181201 Apr 10 23:46 httpcore-4.1.3.jar
-rwxr-xr-x 1 root root  275186 Apr 10 23:46 libfb303-0.9.0.jar
-rwxr-xr-x 1 root root  347531 Apr 10 23:46 libthrift-0.9.0.jar
-rwxr-xr-x 1 root root  367444 Apr 10 23:46 log4j-1.2.14.jar
-rwxr-xr-x 1 root root  294796 Apr 10 23:46 ql.jar
-rwxr-xr-x 1 root root   23671 Apr 10 23:46 slf4j-api-1.5.11.jar
-rwxr-xr-x 1 root root    9693 Apr 10 23:46 slf4j-log4j12-1.5.11.jar
-rwxr-xr-x 1 root root  792964 Apr 10 23:46 zookeeper-3.4.6.jar
[root@nightly59-unsecure-3 jdbc]# export HADOOP_CLASSPATH=`hadoop classpath`:/opt/cloudera/parcels/CDH-*/lib/hive/lib:/root/impala-jdbc/jdbc/*
[root@nightly59-unsecure-3 jdbc]# beeline
beeline> !connect 'jdbc:impala://nightly59-unsecure-3.gce.cloudera.com:21050;AuthMech=0'
Connecting to jdbc:impala://nightly59-unsecure-3.gce.cloudera.com:21050;AuthMech=0
Enter username for jdbc:impala://nightly59-unsecure-3.gce.cloudera.com:21050;AuthMech=0: 
Enter password for jdbc:impala://nightly59-unsecure-3.gce.cloudera.com:21050;AuthMech=0: 
Connected to: Impala (version 2.7.0-cdh5.9.x)
Driver: ImpalaJDBC (version 02.05.37.1057)
Error: [Simba][JDBC](11975) Unsupported transaction isolation level: 4. (state=HY000,code=11975)
0: jdbc:impala://nightly59-unsecure-3.gce.clo> show tables;
+------------+--+
|    name    |
+------------+--+
| customers  |
| sample_07  |
| sample_08  |
| web_logs   |
+------------+--+
4 rows selected (0.177 seconds)
0: jdbc:impala://nightly59-unsecure-3.gce.clo> select * from customers limit 5;
+--------+---------------------+--+
|   id   |        name         |
+--------+---------------------+--+
| 75012  | Dorothy Wilk        |
| 17254  | Martin Johnson      |
| 12532  | Melvin Garcia       |
| 42632  | Raymond S. Vestal   |
| 77913  | Betty J. Giambrone  |
+--------+---------------------+--+
5 rows selected (0.523 seconds)

For the detail information, please check this document.

https://www.cloudera.com/documentation/enterprise/latest/topics/impala_jdbc.html

[#3]

@MSharma

You have to apply the below command before use the table across the services

INVALIDATE METADATA [[db_name.]table_name]

https://www.cloudera.com/documentation/enterprise/5-8-x/topics/impala_invalidate_metadata.html

[#4]

Hi saranvisa ,

this is working fine if i run this query in impala-shell ,but not working  if use beeline for impala.

[neocc9hddn20.prod.com:21000] > select mtcn_nr,attemptid from compliance.core_Rtra_attempts where year=2017 and month=2 limit 5;
Query: select mtcn_nr,attemptid from compliance.core_Rtra_attempts where year=2017 and month=2 limit 5
+------------+---------------------+
| mtcn_nr | attemptid |
+------------+---------------------+
| 6406187702 | 3000000000435887933 |
| 2503805652 | 3000000000435888578 |
| 8154005138 | 3000000000435891262 |
| 1984616774 | 3000000000435893168 |
| 1953542532 | 3000000000435894625 |
+------------+---------------------+

[#5 · Accepted solution]

Please use the impala JDBC driver not hive jdbc driver for accessing the impala via beeline.

root@nightly59-unsecure-3 ~]# mkdir -p /root/impala-jdbc/jdbc
[root@nightly59-unsecure-3 ~]# hadoop classpath
/etc/hadoop/conf:/opt/cloudera/parcels/CDH-5.9.3-1.cdh5.9.3.p0.56/lib/hadoop/libexec/../../hadoop/lib/*:/opt/clo...
[root@nightly59-unsecure-3 ~]# cd /root/impala-jdbc/jdbc/
[root@nightly59-unsecure-3 jdbc]# ll
total 13584
-rwxr-xr-x 1 root root 1554773 Apr 10 23:46 ImpalaJDBC4.jar
-rwxr-xr-x 1 root root 1307923 Apr 10 23:46 TCLIServiceClient.jar
-rwxr-xr-x 1 root root   46725 Apr 10 23:45 commons-codec-1.3.jar
-rwxr-xr-x 1 root root   60686 Apr 10 23:45 commons-logging-1.1.1.jar
-rwxr-xr-x 1 root root 7670596 Apr 10 23:45 hive_metastore.jar
-rwxr-xr-x 1 root root  596600 Apr 10 23:45 hive_service.jar
-rwxr-xr-x 1 root root  352585 Apr 10 23:46 httpclient-4.1.3.jar
-rwxr-xr-x 1 root root  181201 Apr 10 23:46 httpcore-4.1.3.jar
-rwxr-xr-x 1 root root  275186 Apr 10 23:46 libfb303-0.9.0.jar
-rwxr-xr-x 1 root root  347531 Apr 10 23:46 libthrift-0.9.0.jar
-rwxr-xr-x 1 root root  367444 Apr 10 23:46 log4j-1.2.14.jar
-rwxr-xr-x 1 root root  294796 Apr 10 23:46 ql.jar
-rwxr-xr-x 1 root root   23671 Apr 10 23:46 slf4j-api-1.5.11.jar
-rwxr-xr-x 1 root root    9693 Apr 10 23:46 slf4j-log4j12-1.5.11.jar
-rwxr-xr-x 1 root root  792964 Apr 10 23:46 zookeeper-3.4.6.jar
[root@nightly59-unsecure-3 jdbc]# export HADOOP_CLASSPATH=`hadoop classpath`:/opt/cloudera/parcels/CDH-*/lib/hive/lib:/root/impala-jdbc/jdbc/*
[root@nightly59-unsecure-3 jdbc]# beeline
beeline> !connect 'jdbc:impala://nightly59-unsecure-3.gce.cloudera.com:21050;AuthMech=0'
Connecting to jdbc:impala://nightly59-unsecure-3.gce.cloudera.com:21050;AuthMech=0
Enter username for jdbc:impala://nightly59-unsecure-3.gce.cloudera.com:21050;AuthMech=0: 
Enter password for jdbc:impala://nightly59-unsecure-3.gce.cloudera.com:21050;AuthMech=0: 
Connected to: Impala (version 2.7.0-cdh5.9.x)
Driver: ImpalaJDBC (version 02.05.37.1057)
Error: [Simba][JDBC](11975) Unsupported transaction isolation level: 4. (state=HY000,code=11975)
0: jdbc:impala://nightly59-unsecure-3.gce.clo> show tables;
+------------+--+
|    name    |
+------------+--+
| customers  |
| sample_07  |
| sample_08  |
| web_logs   |
+------------+--+
4 rows selected (0.177 seconds)
0: jdbc:impala://nightly59-unsecure-3.gce.clo> select * from customers limit 5;
+--------+---------------------+--+
|   id   |        name         |
+--------+---------------------+--+
| 75012  | Dorothy Wilk        |
| 17254  | Martin Johnson      |
| 12532  | Melvin Garcia       |
| 42632  | Raymond S. Vestal   |
| 77913  | Betty J. Giambrone  |
+--------+---------------------+--+
5 rows selected (0.523 seconds)

For the detail information, please check this document.

https://www.cloudera.com/documentation/enterprise/latest/topics/impala_jdbc.html

[#6 · Accepted solution]

Please use the impala jdbc not hive jdbc like this:

[root@nightly59-unsecure-3 ~]# mkdir -p /root/impala-jdbc/jdbc
[root@nightly59-unsecure-3 ~]# hadoop classpath
/etc/hadoop/conf:/opt/cloudera/parcels/CDH-5.9.3-1.cdh5.9.3.p0.56/lib/hadoop/libexec/../../hadoop/lib/*:/opt/clo...
[root@nightly59-unsecure-3 ~]# cd /root/impala-jdbc/jdbc/
[root@nightly59-unsecure-3 jdbc]# ll
total 13584
-rwxr-xr-x 1 root root 1554773 Apr 10 23:46 ImpalaJDBC4.jar
-rwxr-xr-x 1 root root 1307923 Apr 10 23:46 TCLIServiceClient.jar
-rwxr-xr-x 1 root root   46725 Apr 10 23:45 commons-codec-1.3.jar
-rwxr-xr-x 1 root root   60686 Apr 10 23:45 commons-logging-1.1.1.jar
-rwxr-xr-x 1 root root 7670596 Apr 10 23:45 hive_metastore.jar
-rwxr-xr-x 1 root root  596600 Apr 10 23:45 hive_service.jar
-rwxr-xr-x 1 root root  352585 Apr 10 23:46 httpclient-4.1.3.jar
-rwxr-xr-x 1 root root  181201 Apr 10 23:46 httpcore-4.1.3.jar
-rwxr-xr-x 1 root root  275186 Apr 10 23:46 libfb303-0.9.0.jar
-rwxr-xr-x 1 root root  347531 Apr 10 23:46 libthrift-0.9.0.jar
-rwxr-xr-x 1 root root  367444 Apr 10 23:46 log4j-1.2.14.jar
-rwxr-xr-x 1 root root  294796 Apr 10 23:46 ql.jar
-rwxr-xr-x 1 root root   23671 Apr 10 23:46 slf4j-api-1.5.11.jar
-rwxr-xr-x 1 root root    9693 Apr 10 23:46 slf4j-log4j12-1.5.11.jar
-rwxr-xr-x 1 root root  792964 Apr 10 23:46 zookeeper-3.4.6.jar
[root@nightly59-unsecure-3 jdbc]# export HADOOP_CLASSPATH=`hadoop classpath`:/opt/cloudera/parcels/CDH-*/lib/hive/lib:/root/impala-jdbc/jdbc/*
[root@nightly59-unsecure-3 jdbc]# beeline
beeline> !connect 'jdbc:impala://nightly59-unsecure-3.gce.cloudera.com:21050;AuthMech=0'
Connecting to jdbc:impala://nightly59-unsecure-3.gce.cloudera.com:21050;AuthMech=0
Enter username for jdbc:impala://nightly59-unsecure-3.gce.cloudera.com:21050;AuthMech=0: 
Enter password for jdbc:impala://nightly59-unsecure-3.gce.cloudera.com:21050;AuthMech=0: 
Connected to: Impala (version 2.7.0-cdh5.9.x)
Driver: ImpalaJDBC (version 02.05.37.1057)
Error: [Simba][JDBC](11975) Unsupported transaction isolation level: 4. (state=HY000,code=11975)
0: jdbc:impala://nightly59-unsecure-3.gce.clo> show tables;
+------------+--+
|    name    |
+------------+--+
| customers  |
| sample_07  |
| sample_08  |
| web_logs   |
+------------+--+
4 rows selected (0.177 seconds)
0: jdbc:impala://nightly59-unsecure-3.gce.clo> select * from customers limit 5;
+--------+---------------------+--+
|   id   |        name         |
+--------+---------------------+--+
| 75012  | Dorothy Wilk        |
| 17254  | Martin Johnson      |
| 12532  | Melvin Garcia       |
| 42632  | Raymond S. Vestal   |
| 77913  | Betty J. Giambrone  |
+--------+---------------------+--+
5 rows selected (0.523 seconds)

For the detail information, please check this document.
https://www.cloudera.com/documentation/enterprise/latest/topics/impala_jdbc.html