Unable to select the table in hive

[#1]

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>

[#2]

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

[#3]

@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]

[#4]

@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.

[#5]

@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

[#6]

@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)

 

[#7]

@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 

[#8]

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

[#9]

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

[#10]

@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)