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.

Unable to select the table in hive

Highlighted

Unable to select the table in hive

Hi Team,

I am unable to access (select) the tables (both external and internal) in hive and permissions are managed by ranger.

 

Please find the error 

 


0: jdbc:hive2://w0lxqhdp03:2181,w0lxq> select * from pcr_project;
Error: Error while compiling statement: FAILED: NullPointerException null (state=42000,code=40000)
0: jdbc:hive2://w0lxqhdp03:2181,w0lxq> explain select * from pcr_project;
Error: Error while compiling statement: FAILED: NullPointerException null (state=42000,code=40000)
0: jdbc:hive2://w0lxqhdp03:2181,w0lxq>

9 REPLIES 9
Highlighted

Re: Unable to select the table in hive

Hello,

Please try the 'full path' to the table, i.e.:

 

select * from dbname.tablename

 

Or

use dbname; (switch to said db)

select * from tablename; (select from table in db selected above)

 

Best,
Lyubomir

Highlighted

Re: Unable to select the table in hive

@lyubomirangelo  still getting the same error

 

select * from asop.test;
Error: Error while compiling statement: FAILED: NullPointerException null (state=42000,code=40000)

 

Full trace from hive server:

 

2020-01-20T07:27:50,724 INFO [fb06c5bc-0ca8-4f8f-93d8-76bd188d1e4c HiveServer2-Handler-Pool: Thread-115]: session.SessionState (:()) - Resetting thread name to HiveServer2-Handler-Pool: Thread-115
2020-01-20T07:27:50,724 WARN [HiveServer2-Handler-Pool: Thread-115]: thrift.ThriftCLIService (:()) - Error executing statement:
org.apache.hive.service.cli.HiveSQLException: Error while compiling statement: FAILED: NullPointerException null
at org.apache.hive.service.cli.operation.Operation.toSQLException(Operation.java:335) ~[hive-service-3.1.0.3.0.1.0-187.jar:3.1.0.3.0.1.0-187]
at org.apache.hive.service.cli.operation.SQLOperation.prepare(SQLOperation.java:199) ~[hive-service-3.1.0.3.0.1.0-187.jar:3.1.0.3.0.1.0-187]
at org.apache.hive.service.cli.operation.SQLOperation.runInternal(SQLOperation.java:262) ~[hive-service-3.1.0.3.0.1.0-187.jar:3.1.0.3.0.1.0-187]
at org.apache.hive.service.cli.operation.Operation.run(Operation.java:247) ~[hive-service-3.1.0.3.0.1.0-187.jar:3.1.0.3.0.1.0-187]
at org.apache.hive.service.cli.session.HiveSessionImpl.executeStatementInternal(HiveSessionImpl.java:541) ~[hive-service-3.1.0.3.0.1.0-187.jar:3.1.0.3.0.1.0-187]
at org.apache.hive.service.cli.session.HiveSessionImpl.executeStatementAsync(HiveSessionImpl.java:527) ~[hive-service-3.1.0.3.0.1.0-187.jar:3.1.0.3.0.1.0-187]
at org.apache.hive.service.cli.CLIService.executeStatementAsync(CLIService.java:315) ~[hive-service-3.1.0.3.0.1.0-187.jar:3.1.0.3.0.1.0-187]
at org.apache.hive.service.cli.thrift.ThriftCLIService.ExecuteStatement(ThriftCLIService.java:562) ~[hive-service-3.1.0.3.0.1.0-187.jar:3.1.0.3.0.1.0-187]
at org.apache.hive.service.rpc.thrift.TCLIService$Processor$ExecuteStatement.getResult(TCLIService.java:1557) ~[hive-exec-3.1.0.3.0.1.0-187.jar:3.1.0.3.0.1.0-187]
at org.apache.hive.service.rpc.thrift.TCLIService$Processor$ExecuteStatement.getResult(TCLIService.java:1542) ~[hive-exec-3.1.0.3.0.1.0-187.jar:3.1.0.3.0.1.0-187]
at org.apache.thrift.ProcessFunction.process(ProcessFunction.java:39) ~[hive-exec-3.1.0.3.0.1.0-187.jar:3.1.0.3.0.1.0-187]
at org.apache.thrift.TBaseProcessor.process(TBaseProcessor.java:39) ~[hive-exec-3.1.0.3.0.1.0-187.jar:3.1.0.3.0.1.0-187]
at org.apache.hive.service.auth.TSetIpAddressProcessor.process(TSetIpAddressProcessor.java:56) ~[hive-service-3.1.0.3.0.1.0-187.jar:3.1.0.3.0.1.0-187]
at org.apache.thrift.server.TThreadPoolServer$WorkerProcess.run(TThreadPoolServer.java:286) ~[hive-exec-3.1.0.3.0.1.0-187.jar:3.1.0.3.0.1.0-187]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) ~[?:1.8.0_112]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) ~[?:1.8.0_112]
at java.lang.Thread.run(Thread.java:745) [?:1.8.0_112]
Caused by: java.lang.NullPointerException
at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.checkResultsCache(SemanticAnalyzer.java:15019) ~[hive-exec-3.1.0.3.0.1.0-187.jar:3.1.0.3.0.1.0-187]
at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.analyzeInternal(SemanticAnalyzer.java:12315) ~[hive-exec-3.1.0.3.0.1.0-187.jar:3.1.0.3.0.1.0-187]
at org.apache.hadoop.hive.ql.parse.CalcitePlanner.analyzeInternal(CalcitePlanner.java:358) ~[hive-exec-3.1.0.3.0.1.0-187.jar:3.1.0.3.0.1.0-187]
at org.apache.hadoop.hive.ql.parse.BaseSemanticAnalyzer.analyze(BaseSemanticAnalyzer.java:285) ~[hive-exec-3.1.0.3.0.1.0-187.jar:3.1.0.3.0.1.0-187]
at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:664) ~[hive-exec-3.1.0.3.0.1.0-187.jar:3.1.0.3.0.1.0-187]
at org.apache.hadoop.hive.ql.Driver.compileInternal(Driver.java:1863) ~[hive-exec-3.1.0.3.0.1.0-187.jar:3.1.0.3.0.1.0-187]
at org.apache.hadoop.hive.ql.Driver.compileAndRespond(Driver.java:1810) ~[hive-exec-3.1.0.3.0.1.0-187.jar:3.1.0.3.0.1.0-187]
at org.apache.hadoop.hive.ql.Driver.compileAndRespond(Driver.java:1805) ~[hive-exec-3.1.0.3.0.1.0-187.jar:3.1.0.3.0.1.0-187]

Highlighted

Re: Unable to select the table in hive

Contributor

@saivenkatg55 

 

It looks like query is not retrieving any results from the specific table.Could you please attach the "show create table pcr_project" output.

 

Meanwhile verify the data and it's ownership in the hdfs path.

 

 

Re: Unable to select the table in hive

Mentor

@saivenkatg55 

 

That's the desired results once you enable Ranger plugin for hive.  As you said permissions managed in ranger.

Guessing from your scambled  URLjdbc:hive2://w0lxqhdp03:2181/w0lxq check under the ranger policies and ensure the user executing the SQL has SELECT on the underlying database w0lxq  have a look at this hive /ranger security guidance

 

HTH

Highlighted

Re: Unable to select the table in hive

@Shelton I have created the sample DB and table and provided the access in ranger but you know still i am not able to access the table.

Attaching the ranger policy screenshot as the user has all the permissions.

 

Note: We have integrated hive with LDAP. 

 

select * from test;
Error: Error while compiling statement: FAILED: NullPointerException null (state=42000,code=40000)

 

hive.PNG 

Highlighted

Re: Unable to select the table in hive

@Prakashcit  Please find the show create table output 

+----------------------------------------------------+
| createtab_stmt |
+----------------------------------------------------+
| CREATE EXTERNAL TABLE `asop.pcr_project`( |
| `project_id` int, |
| `project_name` string, |
| `imp_start_date` string, |
| `imp_end_date` string, |
| `project_type` string, |
| `region` string, |
| `all_countries` string, |
| `dept_div` string, |
| `project_status` string) |
| ROW FORMAT SERDE |
| 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' |
| WITH SERDEPROPERTIES ( |
| 'field.delim'='|', |
| 'line.delim'='\n', |
| 'serialization.format'='|') |
| STORED AS INPUTFORMAT |
| 'org.apache.hadoop.mapred.TextInputFormat' |
| OUTPUTFORMAT |
| 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' |
| LOCATION |
| 'hdfs://datalakeqa/data/operations/asop/pcr_project' |
| TBLPROPERTIES ( |
| 'bucketing_version'='2', |
| 'transient_lastDdlTime'='1579513951')

 

As checked the HDFS  data user and group are hive:hadoop but i have provided the permission in ranger 

Highlighted

Re: Unable to select the table in hive

Hi @Prakashcit 

 

Please check the hiveserver/hiveserver2.log and send us the error message if any when grepping for the user making the select.

 

If there is a hive or hdfs permission issue it should be visible in the log.

Best,
Lyubomir

Highlighted

Re: Unable to select the table in hive

Explorer

This looks like a java memory error. The reason why a select * works, but a select column doesn't, is that the select * just pulls a row of data from HDFS rather than actually executing a map-reduce job.

You might be able to solve the problem by increasing the maximum heap size:

export HADOOP_CLIENT_OPTS="-Xmx512m" 

would set the heap size to 512m, for example.

I hope this helps!

Regards,

Lewis

Tech-consultant

 

Highlighted

Re: Unable to select the table in hive

@Lewis @Shelton @Prakashcit  Hi All, I did ranger re-installation and now it is working, but if i integrate hive with ldap, again getting same null pointer exception. 

 

Note: we integrated hive with ldap not ranger, just creating same ldap user in ranger (local) and trying to match it with LDAP user. whether it will create any problem? Kindly advice on this.

 

select * from asop.test;
Error: Error while compiling statement: FAILED: NullPointerException null (state=42000,code=40000)

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