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.

Output formatting not correct impala

SOLVED Go to solution

Output formatting not correct impala

Rising Star

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 SOLUTIONS

Accepted Solutions

Re: Output formatting not correct impala

Cloudera Employee

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

Highlighted

Re: Output formatting not correct impala

Cloudera Employee

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

4 REPLIES 4

Re: Output formatting not correct impala

Champion

 

@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

 

Re: Output formatting not correct impala

Rising Star

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 |
+------------+---------------------+

Re: Output formatting not correct impala

Cloudera Employee

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

Highlighted

Re: Output formatting not correct impala

Cloudera Employee

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