question Re: How can I query HBase from Hive? in Archives of Support Questions (Read Only) https://community.cloudera.com/t5/Archives-of-Support-Questions/How-can-I-query-HBase-from-Hive/m-p/95636#M8975 <P>Make sure hbase is installed on the machine where you are running the hive cli from. In presence of hbase install, hive cli automatically includes the required hbase libraries in the classpath.</P> Mon, 19 Oct 2015 13:02:37 GMT deepesh1 2015-10-19T13:02:37Z How can I query HBase from Hive? https://community.cloudera.com/t5/Archives-of-Support-Questions/How-can-I-query-HBase-from-Hive/m-p/95633#M8972 <P>hive&gt; CREATE TABLE test3(id string, val decimal) STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler' WITH SERDEPROPERTIES ("hbase.columns.mapping" = ":key,0:val");</P><P>FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. MetaException(message:MetaException(message:java.io.IOException: java.lang.reflect.InvocationTargetException</P><P>at org.apache.hadoop.hbase.client.ConnectionFactory.createConnection(ConnectionFactory.java:240)</P><P>at org.apache.hadoop.hbase.client.ConnectionManager.createConnection(ConnectionManager.java:420)</P><P>at org.apache.hadoop.hbase.client.ConnectionManager.createConnection(ConnectionManager.java:413)</P><P>at org.apache.hadoop.hbase.client.ConnectionManager.getConnectionInternal(ConnectionManager.java:291)</P><P>at org.apache.hadoop.hbase.client.HBaseAdmin.&lt;init&gt;(HBaseAdmin.java:222)</P><P>at org.apache.hadoop.hive.hbase.HBaseStorageHandler.getHBaseAdmin(HBaseStorageHandler.java:120)</P><P>at org.apache.hadoop.hive.hbase.HBaseStorageHandler.preCreateTable(HBaseStorageHandler.java:200)</P><P>at org.apache.hadoop.hive.metastore.HiveMetaStoreClient.createTable(HiveMetaStoreClient.java:665)</P><P>at org.apache.hadoop.hive.metastore.HiveMetaStoreClient.createTable(HiveMetaStoreClient.java:658)</P><P>at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)</P><P>at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)</P><P>at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)</P><P>at java.lang.reflect.Method.invoke(Method.java:497)</P><P>at org.apache.hadoop.hive.metastore.RetryingMetaStoreClient.invoke(RetryingMetaStoreClient.java:156)</P><P>at com.sun.proxy.$Proxy5.createTable(Unknown Source)</P><P>at org.apache.hadoop.hive.ql.metadata.Hive.createTable(Hive.java:717)</P><P>at org.apache.hadoop.hive.ql.exec.DDLTask.createTable(DDLTask.java:4170)</P><P>at org.apache.hadoop.hive.ql.exec.DDLTask.execute(DDLTask.java:307)</P><P>at org.apache.hadoop.hive.ql.exec.Task.executeTask(Task.java:160)</P><P>at org.apache.hadoop.hive.ql.exec.TaskRunner.runSequential(TaskRunner.java:89)</P><P>at org.apache.hadoop.hive.ql.Driver.launchTask(Driver.java:1655)</P><P>at org.apache.hadoop.hive.ql.Driver.execute(Driver.java:1414)</P><P>at org.apache.hadoop.hive.ql.Driver.runInternal(Driver.java:1195)</P><P>at org.apache.hadoop.hive.ql.Driver.run(Driver.java:1059)</P><P>at org.apache.hadoop.hive.ql.Driver.run(Driver.java:1049)</P><P>at org.apache.hadoop.hive.cli.CliDriver.processLocalCmd(CliDriver.java:213)</P><P>at org.apache.hadoop.hive.cli.CliDriver.processCmd(CliDriver.java:165)</P><P>at org.apache.hadoop.hive.cli.CliDriver.processLine(CliDriver.java:376)</P><P>at org.apache.hadoop.hive.cli.CliDriver.executeDriver(CliDriver.java:736)</P><P>at org.apache.hadoop.hive.cli.CliDriver.run(CliDriver.java:681)</P><P>at org.apache.hadoop.hive.cli.CliDriver.main(CliDriver.java:621)</P><P>at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)</P><P>at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)</P><P>at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)</P><P>at java.lang.reflect.Method.invoke(Method.java:497)</P><P>at org.apache.hadoop.util.RunJar.run(RunJar.java:221)</P><P>at org.apache.hadoop.util.RunJar.main(RunJar.java:136)</P><P>Caused by: java.lang.reflect.InvocationTargetException</P><P>at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)</P><P>at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)</P><P>at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)</P><P>at java.lang.reflect.Constructor.newInstance(Constructor.java:422)</P><P>at org.apache.hadoop.hbase.client.ConnectionFactory.createConnection(ConnectionFactory.java:238)</P><P>... 36 more</P><P>Caused by: java.lang.UnsupportedOperationException: Unable to find org.apache.hadoop.hbase.ipc.controller.ServerRpcControllerFactory</P><P>at org.apache.hadoop.hbase.util.ReflectionUtils.instantiateWithCustomCtor(ReflectionUtils.java:36)</P><P>at org.apache.hadoop.hbase.ipc.RpcControllerFactory.instantiate(RpcControllerFactory.java:58)</P><P>at org.apache.hadoop.hbase.client.ConnectionManager$HConnectionImplementation.createAsyncProcess(ConnectionManager.java:2242)</P><P>at org.apache.hadoop.hbase.client.ConnectionManager$HConnectionImplementation.&lt;init&gt;(ConnectionManager.java:690)</P><P>at org.apache.hadoop.hbase.client.ConnectionManager$HConnectionImplementation.&lt;init&gt;(ConnectionManager.java:630)</P><P>... 41 more</P><P>Caused by: java.lang.ClassNotFoundException: org.apache.hadoop.hbase.ipc.controller.ServerRpcControllerFactory</P><P>at java.net.URLClassLoader.findClass(URLClassLoader.java:381)</P><P>at java.lang.ClassLoader.loadClass(ClassLoader.java:424)</P><P>at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:331)</P><P>at java.lang.ClassLoader.loadClass(ClassLoader.java:357)</P><P>at java.lang.Class.forName0(Native Method)</P><P>at java.lang.Class.forName(Class.java:264)</P><P>at org.apache.hadoop.hbase.util.ReflectionUtils.instantiateWithCustomCtor(ReflectionUtils.java:32)</P><P>... 45 more</P><P>)</P><P>at org.apache.hadoop.hive.hbase.HBaseStorageHandler.getHBaseAdmin(HBaseStorageHandler.java:124)</P><P>at org.apache.hadoop.hive.hbase.HBaseStorageHandler.preCreateTable(HBaseStorageHandler.java:200)</P><P>at org.apache.hadoop.hive.metastore.HiveMetaStoreClient.createTable(HiveMetaStoreClient.java:665)</P><P>at org.apache.hadoop.hive.metastore.HiveMetaStoreClient.createTable(HiveMetaStoreClient.java:658)</P><P>at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)</P><P>at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)</P><P>at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)</P><P>at java.lang.reflect.Method.invoke(Method.java:497)</P><P>at org.apache.hadoop.hive.metastore.RetryingMetaStoreClient.invoke(RetryingMetaStoreClient.java:156)</P><P>at com.sun.proxy.$Proxy5.createTable(Unknown Source)</P><P>at org.apache.hadoop.hive.ql.metadata.Hive.createTable(Hive.java:717)</P><P>at org.apache.hadoop.hive.ql.exec.DDLTask.createTable(DDLTask.java:4170)</P><P>at org.apache.hadoop.hive.ql.exec.DDLTask.execute(DDLTask.java:307)</P><P>at org.apache.hadoop.hive.ql.exec.Task.executeTask(Task.java:160)</P><P>at org.apache.hadoop.hive.ql.exec.TaskRunner.runSequential(TaskRunner.java:89)</P><P>at org.apache.hadoop.hive.ql.Driver.launchTask(Driver.java:1655)</P><P>at org.apache.hadoop.hive.ql.Driver.execute(Driver.java:1414)</P><P>at org.apache.hadoop.hive.ql.Driver.runInternal(Driver.java:1195)</P><P>at org.apache.hadoop.hive.ql.Driver.run(Driver.java:1059)</P><P>at org.apache.hadoop.hive.ql.Driver.run(Driver.java:1049)</P><P>at org.apache.hadoop.hive.cli.CliDriver.processLocalCmd(CliDriver.java:213)</P><P>at org.apache.hadoop.hive.cli.CliDriver.processCmd(CliDriver.java:165)</P><P>at org.apache.hadoop.hive.cli.CliDriver.processLine(CliDriver.java:376)</P><P>at org.apache.hadoop.hive.cli.CliDriver.executeDriver(CliDriver.java:736)</P><P>at org.apache.hadoop.hive.cli.CliDriver.run(CliDriver.java:681)</P><P>at org.apache.hadoop.hive.cli.CliDriver.main(CliDriver.java:621)</P><P>at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)</P><P>at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)</P><P>at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)</P><P>at java.lang.reflect.Method.invoke(Method.java:497)</P><P>at org.apache.hadoop.util.RunJar.run(RunJar.java:221)</P><P>at org.apache.hadoop.util.RunJar.main(RunJar.java:136)</P><P>)</P> Mon, 19 Oct 2015 11:33:00 GMT https://community.cloudera.com/t5/Archives-of-Support-Questions/How-can-I-query-HBase-from-Hive/m-p/95633#M8972 rgelhausen 2015-10-19T11:33:00Z Re: How can I query HBase from Hive? https://community.cloudera.com/t5/Archives-of-Support-Questions/How-can-I-query-HBase-from-Hive/m-p/95634#M8973 <P><A rel="user" href="https://community.cloudera.com/users/157/rgelhausen.html" nodeid="157">@Randy Gelhausen</A> - Can you try the below:</P><P>CREATE TABLE test3(id string, val decimal) STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler' WITH SERDEPROPERTIES ('hbase.columns.mapping' = ':key,0:val') TBLPROPERTIES ('hbase.table.name' = 'testhbase1');</P> Mon, 19 Oct 2015 11:50:48 GMT https://community.cloudera.com/t5/Archives-of-Support-Questions/How-can-I-query-HBase-from-Hive/m-p/95634#M8973 hrongali 2015-10-19T11:50:48Z Re: How can I query HBase from Hive? https://community.cloudera.com/t5/Archives-of-Support-Questions/How-can-I-query-HBase-from-Hive/m-p/95635#M8974 <P>Unfortunately not. Hive throws the same error about missing classes. I think the solution will involve setting environment classpath variables, but none of the examples I can find online are working with HDP 2.3.2.</P> Mon, 19 Oct 2015 11:53:03 GMT https://community.cloudera.com/t5/Archives-of-Support-Questions/How-can-I-query-HBase-from-Hive/m-p/95635#M8974 rgelhausen 2015-10-19T11:53:03Z Re: How can I query HBase from Hive? https://community.cloudera.com/t5/Archives-of-Support-Questions/How-can-I-query-HBase-from-Hive/m-p/95636#M8975 <P>Make sure hbase is installed on the machine where you are running the hive cli from. In presence of hbase install, hive cli automatically includes the required hbase libraries in the classpath.</P> Mon, 19 Oct 2015 13:02:37 GMT https://community.cloudera.com/t5/Archives-of-Support-Questions/How-can-I-query-HBase-from-Hive/m-p/95636#M8975 deepesh1 2015-10-19T13:02:37Z Re: How can I query HBase from Hive? https://community.cloudera.com/t5/Archives-of-Support-Questions/How-can-I-query-HBase-from-Hive/m-p/95637#M8976 <P>@randy Did you try <A href="http://hortonworks.com/blog/using-hive-to-interact-with-hbase-part-2/">this</A>? </P><P>In order to use the HBase library, we need to make the HBase jars and configuration available to the local Hive process (at least until <A href="https://issues.apache.org/jira/browse/HIVE-5518">HIVE-5518</A> is resolved). Do that by specifying a value for the <CODE>HADOOP_CLASSPATH</CODE> environment variable before executing the statement.</P> Mon, 19 Oct 2015 18:44:13 GMT https://community.cloudera.com/t5/Archives-of-Support-Questions/How-can-I-query-HBase-from-Hive/m-p/95637#M8976 nsabharwal 2015-10-19T18:44:13Z Re: How can I query HBase from Hive? https://community.cloudera.com/t5/Archives-of-Support-Questions/How-can-I-query-HBase-from-Hive/m-p/95638#M8977 <P>Hey <A rel="user" href="https://community.cloudera.com/users/157/rgelhausen.html" nodeid="157">@Randy Gelhausen</A>, I went through an exercise like this for a client and took notes on what I did so I can repeat. Note this was with an earlier version of HDP and I haven't tried it HDP 2.3 yet but maybe worth a shot.....</P><P><EM><STRONG>Step 1 Here is how I created the hbase table and placed data in it.</STRONG></EM></P><PRE>hbase shell hbase(main):001:0&gt;create 'short_urls', {NAME =&gt; 'u'}, {NAME =&gt; 's’} hbase(main):062:0&gt; put 'short_urls', ‘bit.ly/aaaa', 's:hits', '100' hbase(main):063:0&gt; put 'short_urls', ‘bit.ly/aaaa', 'u:url', 'hbase.apache.org' hbase(main):062:0&gt; put 'short_urls', ‘bit.ly/abcd', 's:hits', ‘123' hbase(main):063:0&gt; put 'short_urls', ‘bit.ly/abcd', 'u:url', ‘example.com/foo' hbase(main):064:0&gt; scan 'short_urls' ROW COLUMN+CELL bit.lyaaaa column=s:hits, timestamp=1412121062283, value=100 bit.lyaaaa column=u:url, timestamp=1412121071821,value=hbase.apache.org 1 row(s) in 0.0080 seconds</PRE><STRONG></STRONG><STRONG><EM>Step2: This is how I launched hive and created an external table pointing at HBase.</EM></STRONG> <PRE>hive --auxpath /usr/lib/hive-hcatalog/share/hcatalog/storage-handlers/hbase/lib/hive-hcatalog-hbase-storage-handler-0.13.0.2.1.1.0-385.jar,/usr/lib/hive-hcatalog/share/hcatalog/hive-hcatalog-core-0.13.0.2.1.1.0-385.jar,/usr/lib/hive/lib/zookeeper-3.4.5.2.1.1.0-385.jar,/usr/lib/hive/lib/guava-11.0.2.jar CREATE EXTERNAL TABLE short_urls(short_url string, url string, hit_count string)STORED BY ‘org.apache.hadoop.hive.hbase.HBaseStorageHandler’ WITH SERDEPROPERTIES("hbase.columns.mapping" = ":key, u:url, s:hits”)TBLPROPERTIES("hbase.table.name" = "short_urls”); </PRE> <STRONG><EM>Step3: From Hive now you can query HBase.</EM></STRONG><PRE>hive&gt; select * from short_urls; OK bit.lyaaaa hbase.apache.org 100 bit.lyabcd example.com/foo 123 Time taken: 0.445 seconds, Fetched: 2 row(s)</PRE> Tue, 20 Oct 2015 09:07:47 GMT https://community.cloudera.com/t5/Archives-of-Support-Questions/How-can-I-query-HBase-from-Hive/m-p/95638#M8977 drice1 2015-10-20T09:07:47Z Re: How can I query HBase from Hive? https://community.cloudera.com/t5/Archives-of-Support-Questions/How-can-I-query-HBase-from-Hive/m-p/95639#M8978 <P><A rel="user" href="https://community.cloudera.com/users/338/drice.html" nodeid="338">@Dan Rice</A> Pretty cool design pattern. Any cautions or gotcha's when enabling this?</P> Fri, 30 Oct 2015 03:19:35 GMT https://community.cloudera.com/t5/Archives-of-Support-Questions/How-can-I-query-HBase-from-Hive/m-p/95639#M8978 ccasano 2015-10-30T03:19:35Z Re: How can I query HBase from Hive? https://community.cloudera.com/t5/Archives-of-Support-Questions/How-can-I-query-HBase-from-Hive/m-p/95640#M8979 <P>with help of <A rel="user" href="https://community.cloudera.com/users/223/jelser.html" nodeid="223">@Josh Elser</A></P><P>For 2.3.2 with phoenix enabled on ambari, you need to add phoenix jar to hive lib to make it to work, then restart hive cli / hiveserver2:</P><PRE>mkdir /usr/hdp/current/hive-server2/auxlib/ cp /usr/hdp/2.3.2.0-2950/phoenix/phoenix-client.jar /usr/hdp/current/hive-server2/auxlib/ </PRE><P>without this jar, you will get erro below, as describe by <A rel="user" href="https://community.cloudera.com/users/157/rgelhausen.html" nodeid="157">@Randy Gelhausen</A></P><PRE>Caused by: java.lang.ClassNotFoundException: org.apache.hadoop.hbase.ipc.controller.ServerRpcControllerFactory</PRE><P>UPDATE: Interesting that with sandbox 2.3.2, only phoenix-server.jar works, phoenix-client.jar does not work.</P> Sat, 31 Oct 2015 06:56:30 GMT https://community.cloudera.com/t5/Archives-of-Support-Questions/How-can-I-query-HBase-from-Hive/m-p/95640#M8979 gbraccialli3 2015-10-31T06:56:30Z Re: How can I query HBase from Hive? https://community.cloudera.com/t5/Archives-of-Support-Questions/How-can-I-query-HBase-from-Hive/m-p/95641#M8980 <P>I have made the changes according to answer by @<A href="https://community.hortonworks.com/users/238/gbraccialli.html">Guilherme Braccialli</A> but after adding the jars and put the following setting in </P><P>custom hive-env </P><P>export HIVE_AUX_JARS_PATH=/usr/hdp/current/hive-webhcat/share/hcatalog/hive-hcatalog-core.jar </P><P>custome hive-site</P><P>export HIVE_AUX_JARS_PATH="${HIVE_AUX_JARS_PATH}:/usr/hdp/current/phoenix-client/phoenix-hive.jar"</P><P>but not lukc and WebCat Server is not starting : </P><P>ERROR from log:-</P><P>log4j:WARN No such property [maxFileSize] in org.apache.log4j.DailyRollingFileAppender. log4j:WARN No such property [maxBackupIndex] in org.apache.log4j.DailyRollingFileAppender. Exception in thread "main" java.lang.IllegalStateException: Variable substitution depth too large: 20 "${HIVE_AUX_JARS_PATH}:/usr/hdp/current/phoenix-client/phoenix-hive.jar" at org.apache.hadoop.conf.Configuration.substituteVars(Configuration.java:967) at org.apache.hadoop.conf.Configuration.get(Configuration.java:987) at org.apache.hadoop.hive.conf.HiveConfUtil.dumpConfig(HiveConfUtil.java:77) at org.apache.hadoop.hive.conf.HiveConfUtil.dumpConfig(HiveConfUtil.java:59) at org.apache.hive.hcatalog.templeton.AppConfig.dumpEnvironent(AppConfig.java:256) at org.apache.hive.hcatalog.templeton.AppConfig.init(AppConfig.java:198) at org.apache.hive.hcatalog.templeton.AppConfig.&lt;init&gt;(AppConfig.java:173) at org.apache.hive.hcatalog.templeton.Main.loadConfig(Main.java:97) at org.apache.hive.hcatalog.templeton.Main.init(Main.java:81) at org.apache.hive.hcatalog.templeton.Main.&lt;init&gt;(Main.java:76) at org.apache.hive.hcatalog.templeton.Main.main(Main.java:289) 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)</P><P>after </P> Thu, 26 Oct 2017 17:08:49 GMT https://community.cloudera.com/t5/Archives-of-Support-Questions/How-can-I-query-HBase-from-Hive/m-p/95641#M8980 ansarifah 2017-10-26T17:08:49Z