Re: How to execute query with timestamp type (Hbase/Hive integeration)
Hi Mark, thanks for your response, I tried with other data type, it seems the issue occur while query the timestamp field only, not sure how the timestamp mapping work... From the hbase, I seek the data and the value of timestamp(event_time) is: Wed May 30 16:15:06 CST 2012, should I should the value as long type? Regards, Peyton -原始邮件- From: Mark Grover Sent: Thursday, May 31, 2012 11:14 AM To: user@hive.apache.org Subject: Re: How to execute query with timestamp type (Hbase/Hive integeration) Hi Peyton, It seems like something to do with timestamp mapping. What happens if you change your Hive table definition to have the event_time as int or string? Mark - Original Message - From: "Peyton Peng" To: user@hive.apache.org Sent: Wednesday, May 30, 2012 5:54:20 AM Subject: Re: How to execute query with timestamp type (Hbase/Hive integeration) Actually I can execute the first sql and it works well, all the libs you specified is under the hive lib folder, I doubt if the issue is caused by the timestamp mapping between hbase with hive.. Regards, Peyton From: shashwat shriparv Sent: Wednesday, May 30, 2012 5:26 PM To: user@hive.apache.org Subject: Re: How to execute query with timestamp type (Hbase/Hive integeration) Add these file to hive lib folder hadoop-0.20-core.jar hive/lib/hive-exec-0.7.1.jar hive/lib/hive-jdbc-0.7.1.jar hive/lib/hive-metastore-0.7.1.jar hive/lib/hive-service-0.7.1.jar hive/lib/libfb303.jar lib/commons-logging-1.0.4.jar slf4j-api-1.6.1.jar slf4j-log4j12-1.6.1.jar and then try On Wed, May 30, 2012 at 2:23 PM, Peyton Peng < pengp...@yunyou.tv > wrote: Hi, I build the hive table mapped with hbase table, CREATE TABLE http_access(key string, client_ip string, client_port int, request_method string, event_time timestamp) STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler' WITH SERDEPROPERTIES ( "hbase.columns.mapping" = ":key,client:ip,client:port,request:method,request:event_time" ); The data is store with hbase client. I get an issue while query with hive for (hbase/hive integration), while I execute sql: select ip, port, request_method from http_access , it works well with no problem, but while I execute below sql: select ip, port, event_time from http_access, I got below exception. The only difference between two sqls is: the event_time is timestamp type, I can scan the corresponding hbase table and see the value of event_time is: 1338365792142 column=request:event_time, timestamp=1338365739818, value=Wed May 30 16:15:06 CST 2012 Anyone who know what the issue is? (Not sure if I made a wrong mapping or should I just store the timestamp value as long in hbase? currently I store the value as java.util.Date) Thank you very much Regards, Peyton Exception tracking: Total MapReduce jobs = 1 Launching Job 1 out of 1 Number of reduce tasks is set to 0 since there's no reduce operator hdfs://Server:9000/user/hive/warehouse/http_access Starting Job = job_201205291421_0008, Tracking URL = http://Server:50030/jobdetails.jsp?jobid=job_201205291421_0008 Kill Command = //libexec/../bin/hadoop job -Dmapred.job.tracker=Server:9001 –kill job_201205291421_0008 Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 0 2012-05-30 16:28:01,572 Stage-1 map = 0%, reduce = 0% 2012-05-30 16:28:34,707 Stage-1 map = 100%, reduce = 100% Ended Job = job_201205291421_0008 with errors Error during job, obtaining debugging information... Examining task ID: task_201205291421_0008_m_02 (and more) from job job_201205291421_0008 Exception in thread "Thread-211" java.lang.RuntimeException: Error while reading from task log url at org.apache.hadoop.hive.ql.exec.errors.TaskLogProcessor.getErrors(TaskLogProcessor.java:130) at org.apache.hadoop.hive.ql.exec.JobDebugger.showJobFailDebugInfo(JobDebuggerjava:211) at org.apache.hadoop.hive.ql.exec.JobDebugger.run(JobDebugger.java:81) at java.lang.Thread.run(Thread.java:619) Caused by: java.io.IOException: Server returned HTTP response code: 400 for URL: http://Server:50060/tasklog?taskid=attempt_201205291421_0008_m_00_1&start=-8193 at sun.net.www.protocol.http.HttpURLConnection.getInputStream(HttpURLConnection.java:1305) at java.net.URL.openStream(URL.java:1009) at org.apache.hadoop.hive.ql.exec.errors.TaskLogProcessor.getErrors(TaskLogProcessor.java:120) .. 3 more FAILED: Execution Error, return code 2 from org.apache.hadoop.hive.ql.exec.MapRedTask MapReduce Jobs Launched: Job 0: Map: 1 HDFS Read: 0 HDFS Write: 0 FAIL Total MapReduce CPU Time Spent: 0 msec -- ∞ Shashwat Shriparv
Re: How to execute query with timestamp type (Hbase/Hive integeration)
Actually I can execute the first sql and it works well, all the libs you specified is under the hive lib folder, I doubt if the issue is caused by the timestamp mapping between hbase with hive.. Regards, Peyton From: shashwat shriparv Sent: Wednesday, May 30, 2012 5:26 PM To: user@hive.apache.org Subject: Re: How to execute query with timestamp type (Hbase/Hive integeration) Add these file to hive lib folder >>> hadoop-0.20-core.jar >>> hive/lib/hive-exec-0.7.1.jar >>> hive/lib/hive-jdbc-0.7.1.jar >>> hive/lib/hive-metastore-0.7.1.jar >>> hive/lib/hive-service-0.7.1.jar >>> hive/lib/libfb303.jar >>> lib/commons-logging-1.0.4.jar >>> slf4j-api-1.6.1.jar >>> slf4j-log4j12-1.6.1.jar and then try On Wed, May 30, 2012 at 2:23 PM, Peyton Peng wrote: Hi, I build the hive table mapped with hbase table, CREATE TABLE http_access(key string, client_ip string, client_port int, request_method string, event_time timestamp) STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler' WITH SERDEPROPERTIES ( "hbase.columns.mapping" = ":key,client:ip,client:port,request:method,request:event_time" ); The data is store with hbase client. I get an issue while query with hive for (hbase/hive integration), while I execute sql: select ip, port, request_method from http_access, it works well with no problem, but while I execute below sql: select ip, port, event_time from http_access, I got below exception. The only difference between two sqls is: the event_time is timestamp type, I can scan the corresponding hbase table and see the value of event_time is: 1338365792142 column=request:event_time, timestamp=1338365739818, value=Wed May 30 16:15:06 CST 2012 Anyone who know what the issue is? (Not sure if I made a wrong mapping or should I just store the timestamp value as long in hbase? currently I store the value as java.util.Date) Thank you very much Regards, Peyton Exception tracking: Total MapReduce jobs = 1 Launching Job 1 out of 1 Number of reduce tasks is set to 0 since there's no reduce operator hdfs://Server:9000/user/hive/warehouse/http_access Starting Job = job_201205291421_0008, Tracking URL = http://Server:50030/jobdetails.jsp?jobid=job_201205291421_0008 Kill Command = //libexec/../bin/hadoop job -Dmapred.job.tracker=Server:9001 �Ckill job_201205291421_0008 Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 0 2012-05-30 16:28:01,572 Stage-1 map = 0%, reduce = 0% 2012-05-30 16:28:34,707 Stage-1 map = 100%, reduce = 100% Ended Job = job_201205291421_0008 with errors Error during job, obtaining debugging information... Examining task ID: task_201205291421_0008_m_02 (and more) from job job_201205291421_0008 Exception in thread "Thread-211" java.lang.RuntimeException: Error while reading from task log url at org.apache.hadoop.hive.ql.exec.errors.TaskLogProcessor.getErrors(TaskLogProcessor.java:130) at org.apache.hadoop.hive.ql.exec.JobDebugger.showJobFailDebugInfo(JobDebuggerjava:211) at org.apache.hadoop.hive.ql.exec.JobDebugger.run(JobDebugger.java:81) at java.lang.Thread.run(Thread.java:619) Caused by: java.io.IOException: Server returned HTTP response code: 400 for URL: http://Server:50060/tasklog?taskid=attempt_201205291421_0008_m_00_1&start=-8193 at sun.net.www.protocol.http.HttpURLConnection.getInputStream(HttpURLConnection.java:1305) at java.net.URL.openStream(URL.java:1009) at org.apache.hadoop.hive.ql.exec.errors.TaskLogProcessor.getErrors(TaskLogProcessor.java:120) ... 3 more FAILED: Execution Error, return code 2 from org.apache.hadoop.hive.ql.exec.MapRedTask MapReduce Jobs Launched: Job 0: Map: 1 HDFS Read: 0 HDFS Write: 0 FAIL Total MapReduce CPU Time Spent: 0 msec -- ∞ Shashwat Shriparv
How to execute query with timestamp type (Hbase/Hive integeration)
Hi, I build the hive table mapped with hbase table, CREATE TABLE http_access(key string, client_ip string, client_port int, request_method string, event_time timestamp) STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler' WITH SERDEPROPERTIES ( "hbase.columns.mapping" = ":key,client:ip,client:port,request:method,request:event_time" ); The data is store with hbase client. I get an issue while query with hive for (hbase/hive integration), while I execute sql: select ip, port, request_method from http_access, it works well with no problem, but while I execute below sql: select ip, port, event_time from http_access, I got below exception. The only difference between two sqls is: the event_time is timestamp type, I can scan the corresponding hbase table and see the value of event_time is: 1338365792142 column=request:event_time, timestamp=1338365739818, value=Wed May 30 16:15:06 CST 2012 Anyone who know what the issue is? (Not sure if I made a wrong mapping or should I just store the timestamp value as long in hbase? currently I store the value as java.util.Date) Thank you very much Regards, Peyton Exception tracking: Total MapReduce jobs = 1 Launching Job 1 out of 1 Number of reduce tasks is set to 0 since there's no reduce operator hdfs://Server:9000/user/hive/warehouse/http_access Starting Job = job_201205291421_0008, Tracking URL = http://Server:50030/jobdetails.jsp?jobid=job_201205291421_0008 Kill Command = //libexec/../bin/hadoop job -Dmapred.job.tracker=Server:9001 �Ckill job_201205291421_0008 Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 0 2012-05-30 16:28:01,572 Stage-1 map = 0%, reduce = 0% 2012-05-30 16:28:34,707 Stage-1 map = 100%, reduce = 100% Ended Job = job_201205291421_0008 with errors Error during job, obtaining debugging information... Examining task ID: task_201205291421_0008_m_02 (and more) from job job_201205291421_0008 Exception in thread "Thread-211" java.lang.RuntimeException: Error while reading from task log url at org.apache.hadoop.hive.ql.exec.errors.TaskLogProcessor.getErrors(TaskLogProcessor.java:130) at org.apache.hadoop.hive.ql.exec.JobDebugger.showJobFailDebugInfo(JobDebugger.java:211) at org.apache.hadoop.hive.ql.exec.JobDebugger.run(JobDebugger.java:81) at java.lang.Thread.run(Thread.java:619) Caused by: java.io.IOException: Server returned HTTP response code: 400 for URL: http://Server:50060/tasklog?taskid=attempt_201205291421_0008_m_00_1&start=-8193 at sun.net.www.protocol.http.HttpURLConnection.getInputStream(HttpURLConnection.java:1305) at java.net.URL.openStream(URL.java:1009) at org.apache.hadoop.hive.ql.exec.errors.TaskLogProcessor.getErrors(TaskLogProcessor.java:120) ... 3 more FAILED: Execution Error, return code 2 from org.apache.hadoop.hive.ql.exec.MapRedTask MapReduce Jobs Launched: Job 0: Map: 1 HDFS Read: 0 HDFS Write: 0 FAIL Total MapReduce CPU Time Spent: 0 msec
Re: Error when integrate hive with hbase
Ok, I got the solution from web, need to put the /hbase-0.92.1.jar to /lib, put /conf/hbase-site.xml to /conf on all nodes, But not sure why... Anyone who know more detail about this? Thanks.. Regards, Peyton From: mailto:-pengp...@yunyou.tv Sent: Thursday, April 26, 2012 3:42 PM To: user@hive.apache.org Subject: Error when integrate hive with hbase Hello all, I’m a fresher with hadoop/hbase/hive, there is a requirement with our biz, I need to build the env with hive and hbase for audit log data store and analytics. I just tried to integrate hive with hbase follow the doc: https://cwiki.apache.org/confluence/display/Hive/HBaseIntegration But I got below exceptions, I deployed hbase 1 master with 3 regionservers(connected hadoop hdfs), and try hive connect hbase to create table. Any hints? Thanks in advance. Regards, Peyton hive> ^[[Aroot@swhs-128:/opt/yunyou/hive-0.8.1-bin# bin/hive --auxpath lib/hive-ler-0.8.1.jar,lib/hbase-0.89.0-SHAPSHOT.jar,lib/zookeeper-3.4.3.jar -hiveconf hbase.zookeeper.quarum=swhs-128.yunyou.com: Logging initialized using configuration in jar:file:/opt/yunyou/hive-0.8.1-bin/lib/hive-common-0.8.1.jar!/hive-log4jproperties Hive history file=/tmp/root/hive_job_log_root_201204260026_1127091623.txt hive> CREATE TABLE hbase_table_1(key int, value string) > STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler' > WITH SERDEPROPERTIES ("hbase.columns.mapping" = ":key,cf1:val") > ; FAILED: Error in metadata: MetaException(message:org.apache.hadoop.hbase.ZooKeeperConnectionException: org.apache.hadoop.hbase.ZooKeeperConnectionException: org.apache.zookeeper.KeeperException$ConnectionLossException: KeeperErrorCode = ConnectionLoss for /hbase at org.apache.hadoop.hbase.client.HConnectionManager$HConnectionImplementation.getZooKeeperWatcher(HConnectionManager.java:985) at org.apache.hadoop.hbase.client.HConnectionManager$HConnectionImplementation.setupZookeeperTrackers(HConnectionManager.java:301) at org.apache.hadoop.hbase.client.HConnectionManager$HConnectionImplementation.(HConnectionManager.java:292) at org.apache.hadoop.hbase.client.HConnectionManager.getConnection(HConnectionManager.java:155) at org.apache.hadoop.hbase.client.HBaseAdmin.(HBaseAdmin.java:79) at org.apache.hadoop.hive.hbase.HBaseStorageHandler.getHBaseAdmin(HBaseStorageHandler.java:74) at org.apache.hadoop.hive.hbase.HBaseStorageHandler.preCreateTable(HBaseStorageHandler.java:158) at org.apache.hadoop.hive.metastore.HiveMetaStoreClient.createTable(HiveMetaStoreClient.java:396) at org.apache.hadoop.hive.ql.metadata.Hive.createTable(Hive.java:540) at org.apache.hadoop.hive.ql.exec.DDLTask.createTable(DDLTask.java:3479) at org.apache.hadoop.hive.ql.exec.DDLTask.execute(DDLTask.java:225) at org.apache.hadoop.hive.ql.exec.Task.executeTask(Task.java:133) at org.apache.hadoop.hive.ql.exec.TaskRunner.runSequential(TaskRunner.java:57) at org.apache.hadoop.hive.ql.Driver.launchTask(Driver.java:1332) at org.apache.hadoop.hive.ql.Driver.execute(Driver.java:1123) at org.apache.hadoop.hive.ql.Driver.run(Driver.java:931) at org.apache.hadoop.hive.cli.CliDriver.processLocalCmd(CliDriver.java:255) at org.apache.hadoop.hive.cli.CliDriver.processCmd(CliDriver.java:212) at org.apache.hadoop.hive.cli.CliDriver.processLine(CliDriver.java:403) at org.apache.hadoop.hive.cli.CliDriver.run(CliDriver.java:671) at org.apache.hadoop.hive.cli.CliDriver.main(CliDriver.java:554) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:597) at org.apache.hadoop.util.RunJar.main(RunJar.java:156) Caused by: org.apache.hadoop.hbase.ZooKeeperConnectionException: org.apache.zookeeper.KeeperException$ConnectionLossException: KeeperErrorCode = ConnectionLoss for /hbase at org.apache.hadoop.hbase.zookeeper.ZooKeeperWatcher.(ZooKeeperWatcher.java:147) at org.apache.hadoop.hbase.client.HConnectionManager$HConnectionImplementation.getZooKeeperWatcher(HConnectionManager.java:983) ... 25 more Caused by: org.apache.zookeeper.KeeperException$ConnectionLossException: KeeperErrorCode = ConnectionLoss for /hbase at org.apache.zookeeper.KeeperException.create(KeeperException.java:90) at org.apache.zookeeper.KeeperException.create(KeeperException.java:42) at org.apache.zookeeper.ZooKeeper.create(ZooKeeper.java:637) at org.apache.hadoop.hbase.zookeeper.ZKUtil.createAndFailSilent(ZKUtil.java:886) at org.apache.hadoop.hbase.zookeeper.ZooKeeperWatcher.(ZooKeeperWatcher.java:133) ... 26 more ) FAILED: Execution Error
Error when integrate hive with hbase
Hello all, I’m a fresher with hadoop/hbase/hive, there is a requirement with our biz, I need to build the env with hive and hbase for audit log data store and analytics. I just tried to integrate hive with hbase follow the doc: https://cwiki.apache.org/confluence/display/Hive/HBaseIntegration But I got below exceptions, I deployed hbase 1 master with 3 regionservers(connected hadoop hdfs), and try hive connect hbase to create table. Any hints? Thanks in advance. Regards, Peyton hive> ^[[Aroot@swhs-128:/opt/yunyou/hive-0.8.1-bin# bin/hive --auxpath lib/hive-ler-0.8.1.jar,lib/hbase-0.89.0-SHAPSHOT.jar,lib/zookeeper-3.4.3.jar -hiveconf hbase.zookeeper.quarum=swhs-128.yunyou.com: Logging initialized using configuration in jar:file:/opt/yunyou/hive-0.8.1-bin/lib/hive-common-0.8.1.jar!/hive-log4j.properties Hive history file=/tmp/root/hive_job_log_root_201204260026_1127091623.txt hive> CREATE TABLE hbase_table_1(key int, value string) > STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler' > WITH SERDEPROPERTIES ("hbase.columns.mapping" = ":key,cf1:val") > ; FAILED: Error in metadata: MetaException(message:org.apache.hadoop.hbase.ZooKeeperConnectionException: org.apache.hadoop.hbase.ZooKeeperConnectionException: org.apache.zookeeper.KeeperException$ConnectionLossException: KeeperErrorCode = ConnectionLoss for /hbase at org.apache.hadoop.hbase.client.HConnectionManager$HConnectionImplementation.getZooKeeperWatcher(HConnectionManager.java:985) at org.apache.hadoop.hbase.client.HConnectionManager$HConnectionImplementation.setupZookeeperTrackers(HConnectionManager.java:301) at org.apache.hadoop.hbase.client.HConnectionManager$HConnectionImplementation.(HConnectionManager.java:292) at org.apache.hadoop.hbase.client.HConnectionManager.getConnection(HConnectionManager.java:155) at org.apache.hadoop.hbase.client.HBaseAdmin.(HBaseAdmin.java:79) at org.apache.hadoop.hive.hbase.HBaseStorageHandler.getHBaseAdmin(HBaseStorageHandler.java:74) at org.apache.hadoop.hive.hbase.HBaseStorageHandler.preCreateTable(HBaseStorageHandler.java:158) at org.apache.hadoop.hive.metastore.HiveMetaStoreClient.createTable(HiveMetaStoreClient.java:396) at org.apache.hadoop.hive.ql.metadata.Hive.createTable(Hive.java:540) at org.apache.hadoop.hive.ql.exec.DDLTask.createTable(DDLTask.java:3479) at org.apache.hadoop.hive.ql.exec.DDLTask.execute(DDLTask.java:225) at org.apache.hadoop.hive.ql.exec.Task.executeTask(Task.java:133) at org.apache.hadoop.hive.ql.exec.TaskRunner.runSequential(TaskRunner.java:57) at org.apache.hadoop.hive.ql.Driver.launchTask(Driver.java:1332) at org.apache.hadoop.hive.ql.Driver.execute(Driver.java:1123) at org.apache.hadoop.hive.ql.Driver.run(Driver.java:931) at org.apache.hadoop.hive.cli.CliDriver.processLocalCmd(CliDriver.java:255) at org.apache.hadoop.hive.cli.CliDriver.processCmd(CliDriver.java:212) at org.apache.hadoop.hive.cli.CliDriver.processLine(CliDriver.java:403) at org.apache.hadoop.hive.cli.CliDriver.run(CliDriver.java:671) at org.apache.hadoop.hive.cli.CliDriver.main(CliDriver.java:554) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:597) at org.apache.hadoop.util.RunJar.main(RunJar.java:156) Caused by: org.apache.hadoop.hbase.ZooKeeperConnectionException: org.apache.zookeeper.KeeperException$ConnectionLossException: KeeperErrorCode = ConnectionLoss for /hbase at org.apache.hadoop.hbase.zookeeper.ZooKeeperWatcher.(ZooKeeperWatcher.java:147) at org.apache.hadoop.hbase.client.HConnectionManager$HConnectionImplementation.getZooKeeperWatcher(HConnectionManager.java:983) ... 25 more Caused by: org.apache.zookeeper.KeeperException$ConnectionLossException: KeeperErrorCode = ConnectionLoss for /hbase at org.apache.zookeeper.KeeperException.create(KeeperException.java:90) at org.apache.zookeeper.KeeperException.create(KeeperException.java:42) at org.apache.zookeeper.ZooKeeper.create(ZooKeeper.java:637) at org.apache.hadoop.hbase.zookeeper.ZKUtil.createAndFailSilent(ZKUtil.java:886) at org.apache.hadoop.hbase.zookeeper.ZooKeeperWatcher.(ZooKeeperWatcher.java:133) ... 26 more ) FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask