Support Questions
Find answers, ask questions, and share your expertise

hive-json integration

New Contributor

Hi, I have made hive setup in Ambari. Later I have created tables in hive by importing csv files from hdfs. Similar case with ORC files. But I were unable to create a table in hive by importing json files.

In my library(/usr/hdp/hive-client/lib), I have hive-json-serde.jar file but still I am unable to create a table in hive by importing json files. Can anyone please look over this issue and answer me

Thanks,

Srimukh Reddy.N

3 REPLIES 3

Re: hive-json integration

Hi @srimukh reddy

Use this command and try reading the file. add <jarname>;

Once you have added it then you will be able to use this jar in hive. Also this solution has to be performed each and everytime you login to hive. If you wanted to use this jar permanently then you may need to add this jar in hiverc file.

Check this link below :

http://hadooped.blogspot.in/2013/08/hive-hiverc-file.html

Hope it helps!!

Re: hive-json integration

New Contributor

Thanks @Bala Vignesh N V for the response but still I am unable to solve the issue

I have tried to create a table in hive from json file using

CREATE EXTERNAL TABLE phani ( `_id` STRING, `userId` STRING, `tenantId` STRING, `type` STRING, `createDate` STRING, `metadata` STRUCT< `nonPersonalCount`:STRING, `count`:INT, `minId`:INT, `maxId`:INT >, `linkId` STRING, `data` array<struct<`smsId`:STRING,`body`:STRING,`address`:STRING>> ) ROW FORMAT SERDE 'org.apache.hadoop.hive.contrib.serde2.JsonSerde' STORED AS ORC LOCATION "/phani";

and I got the following error

      java.sql.SQLException: Error while processing statement: FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. java.lang.NullPointerException
java.lang.Exception: java.sql.SQLException: Error while processing statement: FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. java.lang.NullPointerException

java.lang.Exception: java.sql.SQLException: Error while processing statement: FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. java.lang.NullPointerException
	at org.apache.ambari.view.hive20.resources.jobs.JobService.getOne(JobService.java:147)
	at sun.reflect.GeneratedMethodAccessor526.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at com.sun.jersey.spi.container.JavaMethodInvokerFactory$1.invoke(JavaMethodInvokerFactory.java:60)
	at com.sun.jersey.server.impl.model.method.dispatch.AbstractResourceMethodDispatchProvider$ResponseOutInvoker._dispatch(AbstractResourceMethodDispatchProvider.java:205)
	at com.sun.jersey.server.impl.model.method.dispatch.ResourceJavaMethodDispatcher.dispatch(ResourceJavaMethodDispatcher.java:75)
	at com.sun.jersey.server.impl.uri.rules.HttpMethodRule.accept(HttpMethodRule.java:302)
	at com.sun.jersey.server.impl.uri.rules.RightHandPathRule.accept(RightHandPathRule.java:147)
	at com.sun.jersey.server.impl.uri.rules.SubLocatorRule.accept(SubLocatorRule.java:137)
	at com.sun.jersey.server.impl.uri.rules.RightHandPathRule.accept(RightHandPathRule.java:147)
	at com.sun.jersey.server.impl.uri.rules.SubLocatorRule.accept(SubLocatorRule.java:137)
	at com.sun.jersey.server.impl.uri.rules.RightHandPathRule.accept(RightHandPathRule.java:147)
	at com.sun.jersey.server.impl.uri.rules.SubLocatorRule.accept(SubLocatorRule.java:137)
	at com.sun.jersey.server.impl.uri.rules.RightHandPathRule.accept(RightHandPathRule.java:147)
	at com.sun.jersey.server.impl.uri.rules.SubLocatorRule.accept(SubLocatorRule.java:137)
	at com.sun.jersey.server.impl.uri.rules.RightHandPathRule.accept(RightHandPathRule.java:147)
	at com.sun.jersey.server.impl.uri.rules.ResourceClassRule.accept(ResourceClassRule.java:108)
	at com.sun.jersey.server.impl.uri.rules.RightHandPathRule.accept(RightHandPathRule.java:147)
	at com.sun.jersey.server.impl.uri.rules.RootResourceClassesRule.accept(RootResourceClassesRule.java:84)
	at com.sun.jersey.server.impl.application.WebApplicationImpl._handleRequest(WebApplicationImpl.java:1542)
	at com.sun.jersey.server.impl.application.WebApplicationImpl._handleRequest(WebApplicationImpl.java:1473)
	at com.sun.jersey.server.impl.application.WebApplicationImpl.handleRequest(WebApplicationImpl.java:1419)
	at com.sun.jersey.server.impl.application.WebApplicationImpl.handleRequest(WebApplicationImpl.java:1409)
	at com.sun.jersey.spi.container.servlet.WebComponent.service(WebComponent.java:409)
	at com.sun.jersey.spi.container.servlet.ServletContainer.service(ServletContainer.java:558)
	at com.sun.jersey.spi.container.servlet.ServletContainer.service(ServletContainer.java:733)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:848)
	at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:684)
	at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1507)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:330)
	at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:118)
	at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:84)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
	at org.apache.ambari.server.security.authorization.AmbariAuthorizationFilter.doFilter(AmbariAuthorizationFilter.java:287)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
	at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:113)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
	at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:103)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
	at org.springframework.security.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:113)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
	at org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:54)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
	at org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:45)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
	at org.apache.ambari.server.security.authentication.AmbariDelegatingAuthenticationFilter.doFilter(AmbariDelegatingAuthenticationFilter.java:132)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
	at org.apache.ambari.server.security.authorization.AmbariUserAuthorizationFilter.doFilter(AmbariUserAuthorizationFilter.java:91)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
	at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:87)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
	at org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:192)
	at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:160)
	at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:237)
	at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:167)
	at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1478)
	at org.apache.ambari.server.api.MethodOverrideFilter.doFilter(MethodOverrideFilter.java:72)
	at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1478)
	at org.apache.ambari.server.api.AmbariPersistFilter.doFilter(AmbariPersistFilter.java:47)
	at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1478)
	at org.apache.ambari.server.view.AmbariViewsMDCLoggingFilter.doFilter(AmbariViewsMDCLoggingFilter.java:54)
	at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1478)
	at org.apache.ambari.server.view.ViewThrottleFilter.doFilter(ViewThrottleFilter.java:161)
	at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1478)
	at org.apache.ambari.server.security.AbstractSecurityHeaderFilter.doFilter(AbstractSecurityHeaderFilter.java:125)
	at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1478)
	at org.apache.ambari.server.security.AbstractSecurityHeaderFilter.doFilter(AbstractSecurityHeaderFilter.java:125)
	at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1478)
	at org.eclipse.jetty.servlets.UserAgentFilter.doFilter(UserAgentFilter.java:82)
	at org.eclipse.jetty.servlets.GzipFilter.doFilter(GzipFilter.java:294)
	at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1478)
	at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:499)
	at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:137)
	at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:557)
	at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:231)
	at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1086)
	at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:427)
	at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:193)
	at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1020)
	at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:135)
	at org.apache.ambari.server.controller.AmbariHandlerList.processHandlers(AmbariHandlerList.java:212)
	at org.apache.ambari.server.controller.AmbariHandlerList.processHandlers(AmbariHandlerList.java:201)
	at org.apache.ambari.server.controller.AmbariHandlerList.handle(AmbariHandlerList.java:150)
	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:116)
	at org.eclipse.jetty.server.Server.handle(Server.java:370)
	at org.eclipse.jetty.server.AbstractHttpConnection.handleRequest(AbstractHttpConnection.java:494)
	at org.eclipse.jetty.server.AbstractHttpConnection.headerComplete(AbstractHttpConnection.java:973)
	at org.eclipse.jetty.server.AbstractHttpConnection$RequestHandler.headerComplete(AbstractHttpConnection.java:1035)
	at org.eclipse.jetty.http.HttpParser.parseNext(HttpParser.java:641)
	at org.eclipse.jetty.http.HttpParser.parseAvailable(HttpParser.java:231)
	at org.eclipse.jetty.server.AsyncHttpConnection.handle(AsyncHttpConnection.java:82)
	at org.eclipse.jetty.io.nio.SelectChannelEndPoint.handle(SelectChannelEndPoint.java:696)
	at org.eclipse.jetty.io.nio.SelectChannelEndPoint$1.run(SelectChannelEndPoint.java:53)
	at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:608)
	at org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:543)
	at java.lang.Thread.run(Thread.java:745)
Caused by: java.sql.SQLException: Error while processing statement: FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. java.lang.NullPointerException
	at org.apache.hive.jdbc.HiveStatement.waitForOperationToComplete(HiveStatement.java:348)
	at org.apache.hive.jdbc.HiveStatement.execute(HiveStatement.java:251)
	at org.apache.ambari.view.hive20.HiveJdbcConnectionDelegate.execute(HiveJdbcConnectionDelegate.java:49)
	at org.apache.ambari.view.hive20.actor.StatementExecutor.runStatement(StatementExecutor.java:91)
	at org.apache.ambari.view.hive20.actor.StatementExecutor.handleMessage(StatementExecutor.java:72)
	at org.apache.ambari.view.hive20.actor.HiveActor.onReceive(HiveActor.java:38)
	at akka.actor.UntypedActor$$anonfun$receive$1.applyOrElse(UntypedActor.scala:167)
	at akka.actor.Actor$class.aroundReceive(Actor.scala:467)
	at akka.actor.UntypedActor.aroundReceive(UntypedActor.scala:97)
	at akka.actor.ActorCell.receiveMessage(ActorCell.scala:516)
	at akka.actor.ActorCell.invoke(ActorCell.scala:487)
	at akka.dispatch.Mailbox.processMailbox(Mailbox.scala:238)
	at akka.dispatch.Mailbox.run(Mailbox.scala:220)
	at akka.dispatch.ForkJoinExecutorConfigurator$AkkaForkJoinTask.exec(AbstractDispatcher.scala:397)
	at scala.concurrent.forkjoin.ForkJoinTask.doExec(ForkJoinTask.java:260)
	at scala.concurrent.forkjoin.ForkJoinPool$WorkQueue.runTask(ForkJoinPool.java:1339)
	at scala.concurrent.forkjoin.ForkJoinPool.runWorker(ForkJoinPool.java:1979)
	at scala.concurrent.forkjoin.ForkJoinWorkerThread.run(ForkJoinWorkerThread.java:107)

Re: hive-json integration

New Contributor
Also In cmd I have the following error when starting the hive

[root@cluster-hive-nl8l bin]# hive
log4j:WARN No such property [maxFileSize] in org.apache.log4j.DailyRollingFileAppender.


Logging initialized using configuration in file:/etc/hive/2.6.2.0-205/0/hive-log4j.properties
Exception in thread "main" java.lang.NoSuchMethodError: org.apache.hadoop.hive.metastore.RetryingMetaStoreClient.getProxy(Lorg/apache/hadoop/hive/conf/HiveConf;Lorg/apache/hadoop/hive/metastore/HiveMetaHookLoader;Ljava/util/concurrent/ConcurrentHashMap;Ljava/lang/String;)Lorg/apache/hadoop/hive/metastore/IMetaStoreClient;
        at org.apache.hadoop.hive.ql.metadata.Hive.createMetaStoreClient(Hive.java:3510)
        at org.apache.hadoop.hive.ql.metadata.Hive.getMSC(Hive.java:3542)
        at org.apache.hadoop.hive.ql.metadata.Hive.getAllFunctions(Hive.java:3781)
        at org.apache.hadoop.hive.ql.metadata.Hive.reloadFunctions(Hive.java:220)
        at org.apache.hadoop.hive.ql.metadata.Hive.<clinit>(Hive.java:212)
        at org.apache.hadoop.hive.ql.session.SessionState.start(SessionState.java:528)
        at org.apache.hadoop.hive.cli.CliDriver.run(CliDriver.java:681)
        at org.apache.hadoop.hive.cli.CliDriver.main(CliDriver.java:625)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at org.apache.hadoop.util.RunJar.run(RunJar.java:233)
        at org.apache.hadoop.util.RunJar.main(RunJar.java:148)

I do have the following jars in lib 

[root@cluster-hive-nl8l lib]# ll hive-*.jar
-rw-r--r--. 1 root root   120428 Aug 26 09:54 hive-accumulo-handler-1.2.1000.2.6.2.0-205.jar
lrwxrwxrwx. 1 root root       46 Sep  4 10:50 hive-accumulo-handler.jar -> hive-accumulo-handler-1.2.1000.2.6.2.0-205.jar
-rw-r--r--. 1 root root    49725 Aug 26 09:53 hive-ant-1.2.1000.2.6.2.0-205.jar
lrwxrwxrwx. 1 root root       33 Sep  4 10:50 hive-ant.jar -> hive-ant-1.2.1000.2.6.2.0-205.jar
-rw-r--r--. 1 root root   148377 Aug 26 09:55 hive-beeline-1.2.1000.2.6.2.0-205.jar
lrwxrwxrwx. 1 root root       37 Sep  4 10:50 hive-beeline.jar -> hive-beeline-1.2.1000.2.6.2.0-205.jar
-rw-r--r--. 1 root root    40146 Aug 26 09:55 hive-cli-1.2.1000.2.6.2.0-205.jar
lrwxrwxrwx. 1 root root       33 Sep  4 10:50 hive-cli.jar -> hive-cli-1.2.1000.2.6.2.0-205.jar
-rw-r--r--. 1 root root   359939 Aug 26 09:52 hive-common-1.2.1000.2.6.2.0-205.jar
lrwxrwxrwx. 1 root root       36 Sep  4 10:50 hive-common.jar -> hive-common-1.2.1000.2.6.2.0-205.jar
-rw-r--r--. 1 root root   122558 Aug 26 09:55 hive-contrib-1.2.1000.2.6.2.0-205.jar
lrwxrwxrwx. 1 root root       37 Sep  4 10:50 hive-contrib.jar -> hive-contrib-1.2.1000.2.6.2.0-205.jar
-rw-r--r--. 1 root root 36961450 Sep  7 13:39 hive-druid-handler-2.3.0.jar
-rw-r--r--. 1 root root 22006904 Aug 26 09:54 hive-exec-1.2.1000.2.6.2.0-205.jar
lrwxrwxrwx. 1 root root       34 Sep  4 10:50 hive-exec.jar -> hive-exec-1.2.1000.2.6.2.0-205.jar
-rw-r--r--. 1 root root    54747 Apr 28  2012 hive-hbase-handler-0.9.0.jar
-rw-r--r--. 1 root root   119491 Aug 26 09:55 hive-hbase-handler-1.2.1000.2.6.2.0-205.jar
lrwxrwxrwx. 1 root root       43 Sep  4 10:50 hive-hbase-handler.jar -> hive-hbase-handler-1.2.1000.2.6.2.0-205.jar
-rw-r--r--. 1 root root    29161 Aug 26 09:56 hive-hwi-1.2.1000.2.6.2.0-205.jar
lrwxrwxrwx. 1 root root       33 Sep  4 10:50 hive-hwi.jar -> hive-hwi-1.2.1000.2.6.2.0-205.jar
-rw-r--r--. 1 root root   111628 Aug 26 09:54 hive-jdbc-1.2.1000.2.6.2.0-205.jar
lrwxrwxrwx. 1 root root       34 Sep  4 10:50 hive-jdbc.jar -> hive-jdbc-1.2.1000.2.6.2.0-205.jar
-rw-r--r--. 1 root root    43010 Oct 19  2011 hive-json-serde.jar
-rw-r--r--. 1 root root  6920621 Sep  8 09:34 hive-meta1store-2.0.0.jar
-rw-r--r--. 1 root root  6276641 Aug 26 09:53 hive-metastore1-1.2.1000.2.6.2.0-205.jar
-rw-r--r--. 1 root root  8119135 Sep  8 09:49 hive-metastore-2.3.0.jar
lrwxrwxrwx. 1 root root       24 Sep 12 12:26 hive-metastore.jar -> hive-metastore-2.3.0.jar
-rw-r--r--. 1 root root   922483 Aug 26 09:53 hive-serde-1.2.1000.2.6.2.0-205.jar
lrwxrwxrwx. 1 root root       35 Sep  4 10:50 hive-serde.jar -> hive-serde-1.2.1000.2.6.2.0-205.jar
-rw-r--r--. 1 root root  1999980 Aug 26 09:54 hive-service-1.2.1000.2.6.2.0-205.jar
lrwxrwxrwx. 1 root root       37 Sep  4 10:50 hive-service.jar -> hive-service-1.2.1000.2.6.2.0-205.jar
-rw-r--r--. 1 root root    34328 Aug 26 09:52 hive-shims-0.20S-1.2.1000.2.6.2.0-205.jar
-rw-r--r--. 1 root root    65652 Aug 26 09:52 hive-shims-0.23-1.2.1000.2.6.2.0-205.jar
-rw-r--r--. 1 root root    10459 Aug 26 09:52 hive-shims-1.2.1000.2.6.2.0-205.jar
-rw-r--r--. 1 root root   114512 Aug 26 09:52 hive-shims-common-1.2.1000.2.6.2.0-205.jar
lrwxrwxrwx. 1 root root       42 Sep  4 10:50 hive-shims-common.jar -> hive-shims-common-1.2.1000.2.6.2.0-205.jar
lrwxrwxrwx. 1 root root       41 Sep  4 10:50 hive-shims.jar -> hive-shims-0.20S-1.2.1000.2.6.2.0-205.jar
-rw-r--r--. 1 root root    13605 Aug 26 09:52 hive-shims-scheduler-1.2.1000.2.6.2.0-205.jar
lrwxrwxrwx. 1 root root       45 Sep  4 10:50 hive-shims-scheduler.jar -> hive-shims-scheduler-1.2.1000.2.6.2.0-205.jar




Can you please resolve this bala

Thanks,
Srimukh Reddy.N