Special characters support in column names
Hi, Currently hive doesn't support special characters(i.e,%,$..etc) in table names. Is there any request for adding this feature? Please let me know, what do you feel about this. -- Thanks, Pandeeswaran
SerDe for CSV files
Hi all, I wrote a simple SerDe to deserialize CSV files defined as external table. The CSV files contain a header with columns names. I would like to skip the header while executing select * from my table. Example: CSV file content: a,b,c 1,2,3 4,5,6 select * from my_table; should return: 1,2,3 4,5,6 Is there a hook to make the framework to skip a specific line ? I'd appreciate your advise. Using Hive 0.11.0. Thanks, Lior
RE: Hive 0.11 with Cloudera CHD4.3 MR v1
Hello All, Sorry for not replying to the thread, but I never received any of your responses.. Hopefully this info will help: @kulkarni.swarnim Here is the error I'm getting on task tracker for the mapper: 2013-08-21 15:34:32,491 INFO org.apache.hadoop.mapred.TaskLogsTruncater: Initializing logs' truncater with mapRetainSize=-1 and reduceRetainSize=-1 2013-08-21 15:34:32,494 WARN org.apache.hadoop.mapred.Child: Error running child java.lang.RuntimeException: java.lang.NullPointerException at org.apache.hadoop.hive.ql.exec.Utilities.getMapRedWork(Utilities.java:230) at org.apache.hadoop.hive.ql.io.HiveInputFormat.init(HiveInputFormat.java:255) at org.apache.hadoop.hive.ql.io.HiveInputFormat.pushProjectionsAndFilters(HiveInputFormat.java:381) at org.apache.hadoop.hive.ql.io.HiveInputFormat.pushProjectionsAndFilters(HiveInputFormat.java:374) at org.apache.hadoop.hive.ql.io.CombineHiveInputFormat.getRecordReader(CombineHiveInputFormat.java:540) at org.apache.hadoop.mapred.MapTask.runOldMapper(MapTask.java:394) at org.apache.hadoop.mapred.MapTask.run(MapTask.java:332) at org.apache.hadoop.mapred.Child$4.run(Child.java:268) at java.security.AccessController.doPrivileged(Native Method) at javax.security.auth.Subject.doAs(Subject.java:396) at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1408) at org.apache.hadoop.mapred.Child.main(Child.java:262) Caused by: java.lang.NullPointerException at org.apache.hadoop.hive.ql.exec.Utilities.getMapRedWork(Utilities.java:215) ... 11 more 2013-08-21 15:34:32,498 INFO org.apache.hadoop.mapred.Task: Runnning cleanup for the task @Srinivas Surasani Hopefully the above helps.. @Lars Francke Thanks, I'll take a peek at those 2 patches.. But I've just recently pulled down fresh code and recompiled everything and I'm still getting the above error. And for reference, below is my original email to the list.. Thanks all for you help! JC Hello All, Has anyone been successful at running hive 0.11 with Cloudera CDH 4.3? I've been able to get hive to connect to my metadb (which is in Postgres). Verified by doing a show tables.. I can run explain and describes on tables, but when I try to run anything that fires off an M/R job, I get the following error: hiveselect count(*) from tableA; Total MapReduce jobs = 1 Launching Job 1 out of 1 Number of reduce tasks determined at compile time: 1 In order to change the average load for a reducer (in bytes): set hive.exec.reducers.bytes.per.reducer=number In order to limit the maximum number of reducers: set hive.exec.reducers.max=number In order to set a constant number of reducers: set mapred.reduce.tasks=number Starting Job = job_201307112247_13816, Tracking URL = http://master:50030/jobdetails.jsp?jobid=job_201307112247_13816 Kill Command = /usr/lib/hadoop/bin/hadoop job -kill job_201307112247_13816 Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 1 2013-07-12 02:11:42,829 Stage-1 map = 0%, reduce = 0% 2013-07-12 02:12:08,173 Stage-1 map = 100%, reduce = 100% Ended Job = job_201307112247_13816 with errors Error during job, obtaining debugging information... Job Tracking URL: http://master:50030/jobdetails.jsp?jobid=job_201307112247_13816 Examining task ID: task_201307112247_13816_m_02 (and more) from job job_201307112247_13816 Exception in thread Thread-19 java.lang.NoClassDefFoundError: org/apache/hadoop/mapreduce/util/HostUtil at org.apache.hadoop.hive.shims.Hadoop23Shims.getTaskAttemptLogUrl(Hadoop23Shims.java:61) at org.apache.hadoop.hive.ql.exec.JobDebugger$TaskInfoGrabber.getTaskInfos(JobDebugger.java:186) at org.apache.hadoop.hive.ql.exec.JobDebugger$TaskInfoGrabber.run(JobDebugger.java:142) at java.lang.Thread.run(Thread.java:619) Caused by: java.lang.ClassNotFoundException: org.apache.hadoop.mapreduce.util.HostUtil at java.net.URLClassLoader$1.run(URLClassLoader.java:202) at java.security.AccessController.doPrivileged(Native Method) at java.net.URLClassLoader.findClass(URLClassLoader.java:190) at java.lang.ClassLoader.loadClass(ClassLoader.java:307) at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:301) at java.lang.ClassLoader.loadClass(ClassLoader.java:248) ... 4 more FAILED: Execution Error, return code 2 from org.apache.hadoop.hive.ql.exec.MapRedTask MapReduce Jobs Launched: Job 0: Map: 1 Reduce: 1 HDFS Read: 0 HDFS Write: 0 FAIL Total MapReduce CPU Time Spent: 0 msec I'm using my configs from hive 0.10, which works with no issues and this was pretty much a drop in replacement on the machine that hadoop 0.10 was running on.. I've done a bunch of googling around and have found a bunch of other folks that have have had the same issue, but no solid answers.. Thanks in advance for any help.. JC
Re: Hive 0.11 with Cloudera CHD4.3 MR v1
I do have this working on CHD4.3.0 with MR1 and Kerberos. I am finishing up a blog today explaining what was needed to get it working. I will reply with the link later today. Justin Sent from my iPhone On Aug 22, 2013, at 6:17 AM, Jim Colestock j...@ramblingredneck.com wrote: Hello All, Sorry for not replying to the thread, but I never received any of your responses.. Hopefully this info will help: @kulkarni.swarnimhttp://search.gmane.org/?author=kulkarni.swarnim-Re5JQEeQqe8AvxtiuMwx3w%40public.gmane.orgsort=date Here is the error I'm getting on task tracker for the mapper: 2013-08-21 15:34:32,491 INFO org.apache.hadoop.mapred.TaskLogsTruncater: Initializing logs' truncater with mapRetainSize=-1 and reduceRetainSize=-1 2013-08-21 15:34:32,494 WARN org.apache.hadoop.mapred.Child: Error running child java.lang.RuntimeException: java.lang.NullPointerException at org.apache.hadoop.hive.ql.exec.Utilities.getMapRedWork(Utilities.java:230) at org.apache.hadoop.hive.ql.io.HiveInputFormat.init(HiveInputFormat.java:255) at org.apache.hadoop.hive.ql.io.HiveInputFormat.pushProjectionsAndFilters(HiveInputFormat.java:381) at org.apache.hadoop.hive.ql.io.HiveInputFormat.pushProjectionsAndFilters(HiveInputFormat.java:374) at org.apache.hadoop.hive.ql.io.CombineHiveInputFormat.getRecordReader(CombineHiveInputFormat.java:540) at org.apache.hadoop.mapred.MapTask.runOldMapper(MapTask.java:394) at org.apache.hadoop.mapred.MapTask.run(MapTask.java:332) at org.apache.hadoop.mapred.Child$4.run(Child.java:268) at java.security.AccessController.doPrivileged(Native Method) at javax.security.auth.Subject.doAs(Subject.java:396) at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1408) at org.apache.hadoop.mapred.Child.main(Child.java:262) Caused by: java.lang.NullPointerException at org.apache.hadoop.hive.ql.exec.Utilities.getMapRedWork(Utilities.java:215) ... 11 more 2013-08-21 15:34:32,498 INFO org.apache.hadoop.mapred.Task: Runnning cleanup for the task @Srinivas Surasanihttp://search.gmane.org/?author=Srinivas+Surasanisort=date Hopefully the above helps.. @Lars Francke http://search.gmane.org/?author=Lars+Franckesort=date Thanks, I'll take a peek at those 2 patches.. But I've just recently pulled down fresh code and recompiled everything and I'm still getting the above error. And for reference, below is my original email to the list.. Thanks all for you help! JC Hello All, Has anyone been successful at running hive 0.11 with Cloudera CDH 4.3? I've been able to get hive to connect to my metadb (which is in Postgres). Verified by doing a show tables.. I can run explain and describes on tables, but when I try to run anything that fires off an M/R job, I get the following error: hiveselect count(*) from tableA; Total MapReduce jobs = 1 Launching Job 1 out of 1 Number of reduce tasks determined at compile time: 1 In order to change the average load for a reducer (in bytes): set hive.exec.reducers.bytes.per.reducer=number In order to limit the maximum number of reducers: set hive.exec.reducers.max=number In order to set a constant number of reducers: set mapred.reduce.tasks=number Starting Job = job_201307112247_13816, Tracking URL = http://master:50030/jobdetails.jsp?jobid=job_201307112247_13816 Kill Command = /usr/lib/hadoop/bin/hadoop job -kill job_201307112247_13816 Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 1 2013-07-12 02:11:42,829 Stage-1 map = 0%, reduce = 0% 2013-07-12 02:12:08,173 Stage-1 map = 100%, reduce = 100% Ended Job = job_201307112247_13816 with errors Error during job, obtaining debugging information... Job Tracking URL: http://master:50030/jobdetails.jsp?jobid=job_201307112247_13816 Examining task ID: task_201307112247_13816_m_02 (and more) from job job_201307112247_13816 Exception in thread Thread-19 java.lang.NoClassDefFoundError: org/apache/hadoop/mapreduce/util/HostUtil at org.apache.hadoop.hive.shims.Hadoop23Shims.getTaskAttemptLogUrl(Hadoop23Shims.java:61) at org.apache.hadoop.hive.ql.exec.JobDebugger$TaskInfoGrabber.getTaskInfos(JobDebugger.java:186) at org.apache.hadoop.hive.ql.exec.JobDebugger$TaskInfoGrabber.run(JobDebugger.java:142) at java.lang.Thread.run(Thread.java:619) Caused by: java.lang.ClassNotFoundException: org.apache.hadoop.mapreduce.util.HostUtil at java.net.URLClassLoader$1.run(URLClassLoader.java:202) at java.security.AccessController.doPrivileged(Native Method) at java.net.URLClassLoader.findClass(URLClassLoader.java:190) at java.lang.ClassLoader.loadClass(ClassLoader.java:307) at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:301) at java.lang.ClassLoader.loadClass(ClassLoader.java:248) ... 4 more FAILED: Execution Error, return code 2 from org.apache.hadoop.hive.ql.exec.MapRedTask MapReduce Jobs Launched: Job 0: Map: 1 Reduce: 1 HDFS Read: 0 HDFS Write: 0
How to perform arithmetic operations in hive
Hi all , Can we perform arithmetic operator on *select* command. I have a table emp_table with columns emp_name and emp_no i want to multiply the data in a emp_no with 10 . i tryed select * from emp_table where emp_no=emp_no*10; But it did not work . Help me out Thanks in advance . --Regards Sandeep Nemuri
Re: How to perform arithmetic operations in hive
Try select emp_name, (emp_no * 10) from emp_table; Sent from my iPhone On Aug 22, 2013, at 8:14 AM, Sandeep Nemuri nhsande...@gmail.com wrote: Hi all , Can we perform arithmetic operator on *select* command. I have a table emp_table with columns emp_name and emp_no i want to multiply the data in a emp_no with 10 . i tryed select * from emp_table where emp_no=emp_no*10; But it did not work . Help me out Thanks in advance . --Regards Sandeep Nemuri
Re: Hive 0.11 with Cloudera CHD4.3 MR v1
Justin, That would be much appreciated! Thanks Again, JC On Aug 22, 2013, at 9:18 AM, Justin Workman justinjwork...@gmail.com wrote: I do have this working on CHD4.3.0 with MR1 and Kerberos. I am finishing up a blog today explaining what was needed to get it working. I will reply with the link later today. Justin Sent from my iPhone On Aug 22, 2013, at 6:17 AM, Jim Colestock j...@ramblingredneck.com wrote: Hello All, Sorry for not replying to the thread, but I never received any of your responses.. Hopefully this info will help: @kulkarni.swarnim Here is the error I'm getting on task tracker for the mapper: 2013-08-21 15:34:32,491 INFO org.apache.hadoop.mapred.TaskLogsTruncater: Initializing logs' truncater with mapRetainSize=-1 and reduceRetainSize=-1 2013-08-21 15:34:32,494 WARN org.apache.hadoop.mapred.Child: Error running child java.lang.RuntimeException: java.lang.NullPointerException at org.apache.hadoop.hive.ql.exec.Utilities.getMapRedWork(Utilities.java:230) at org.apache.hadoop.hive.ql.io.HiveInputFormat.init(HiveInputFormat.java:255) at org.apache.hadoop.hive.ql.io.HiveInputFormat.pushProjectionsAndFilters(HiveInputFormat.java:381) at org.apache.hadoop.hive.ql.io.HiveInputFormat.pushProjectionsAndFilters(HiveInputFormat.java:374) at org.apache.hadoop.hive.ql.io.CombineHiveInputFormat.getRecordReader(CombineHiveInputFormat.java:540) at org.apache.hadoop.mapred.MapTask.runOldMapper(MapTask.java:394) at org.apache.hadoop.mapred.MapTask.run(MapTask.java:332) at org.apache.hadoop.mapred.Child$4.run(Child.java:268) at java.security.AccessController.doPrivileged(Native Method) at javax.security.auth.Subject.doAs(Subject.java:396) at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1408) at org.apache.hadoop.mapred.Child.main(Child.java:262) Caused by: java.lang.NullPointerException at org.apache.hadoop.hive.ql.exec.Utilities.getMapRedWork(Utilities.java:215) ... 11 more 2013-08-21 15:34:32,498 INFO org.apache.hadoop.mapred.Task: Runnning cleanup for the task @Srinivas Surasani Hopefully the above helps.. @Lars Francke Thanks, I'll take a peek at those 2 patches.. But I've just recently pulled down fresh code and recompiled everything and I'm still getting the above error. And for reference, below is my original email to the list.. Thanks all for you help! JC Hello All, Has anyone been successful at running hive 0.11 with Cloudera CDH 4.3? I've been able to get hive to connect to my metadb (which is in Postgres). Verified by doing a show tables.. I can run explain and describes on tables, but when I try to run anything that fires off an M/R job, I get the following error: hiveselect count(*) from tableA; Total MapReduce jobs = 1 Launching Job 1 out of 1 Number of reduce tasks determined at compile time: 1 In order to change the average load for a reducer (in bytes): set hive.exec.reducers.bytes.per.reducer=number In order to limit the maximum number of reducers: set hive.exec.reducers.max=number In order to set a constant number of reducers: set mapred.reduce.tasks=number Starting Job = job_201307112247_13816, Tracking URL = http://master:50030/jobdetails.jsp?jobid=job_201307112247_13816 Kill Command = /usr/lib/hadoop/bin/hadoop job -kill job_201307112247_13816 Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 1 2013-07-12 02:11:42,829 Stage-1 map = 0%, reduce = 0% 2013-07-12 02:12:08,173 Stage-1 map = 100%, reduce = 100% Ended Job = job_201307112247_13816 with errors Error during job, obtaining debugging information... Job Tracking URL: http://master:50030/jobdetails.jsp?jobid=job_201307112247_13816 Examining task ID: task_201307112247_13816_m_02 (and more) from job job_201307112247_13816 Exception in thread Thread-19 java.lang.NoClassDefFoundError: org/apache/hadoop/mapreduce/util/HostUtil at org.apache.hadoop.hive.shims.Hadoop23Shims.getTaskAttemptLogUrl(Hadoop23Shims.java:61) at org.apache.hadoop.hive.ql.exec.JobDebugger$TaskInfoGrabber.getTaskInfos(JobDebugger.java:186) at org.apache.hadoop.hive.ql.exec.JobDebugger$TaskInfoGrabber.run(JobDebugger.java:142) at java.lang.Thread.run(Thread.java:619) Caused by: java.lang.ClassNotFoundException: org.apache.hadoop.mapreduce.util.HostUtil at java.net.URLClassLoader$1.run(URLClassLoader.java:202) at java.security.AccessController.doPrivileged(Native Method) at java.net.URLClassLoader.findClass(URLClassLoader.java:190) at java.lang.ClassLoader.loadClass(ClassLoader.java:307) at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:301) at java.lang.ClassLoader.loadClass(ClassLoader.java:248) ... 4 more FAILED: Execution Error, return code 2 from org.apache.hadoop.hive.ql.exec.MapRedTask MapReduce Jobs Launched: Job
[HIVE] Cannot perform a queries
Hi everyone, I have a CDH4 cluster running In 4 nodes. I can perform queries in Beeswax UI but If I try to perform the same query with the hive console it is not possible. I launched the console in this way: hive -hiveconf hive.root.logger=DEBUG,console and this is the output. = 13/08/22 13:44:21 INFO zookeeper.ZooKeeper: Client environment:java.library.path=/opt/cloudera/parcels/CDH-4.3.0-1.cdh4.3.0.p0.22/lib/hadoop/lib/native 13/08/22 13:44:21 INFO zookeeper.ZooKeeper: Client environment:java.io.tmpdir=/tmp 13/08/22 13:44:21 INFO zookeeper.ZooKeeper: Client environment:java.compiler=NA 13/08/22 13:44:21 INFO zookeeper.ZooKeeper: Client environment:os.name=Linux 13/08/22 13:44:21 INFO zookeeper.ZooKeeper: Client environment:os.arch=amd64 13/08/22 13:44:21 INFO zookeeper.ZooKeeper: Client environment:os.version=2.6.32-279.el6.x86_64 13/08/22 13:44:21 INFO zookeeper.ZooKeeper: Client environment:user.name =root 13/08/22 13:44:21 INFO zookeeper.ZooKeeper: Client environment:user.home=/root 13/08/22 13:44:21 INFO zookeeper.ZooKeeper: Client environment:user.dir=/ 13/08/22 13:44:21 INFO zookeeper.ZooKeeper: Initiating client connection, connectString=datlas-cloudera-m01:2181 sessionTimeout=60 watcher=org.apache.hadoop.hive.ql.lockmgr.zookeeper.ZooKeeperHiveLockManager$DummyWatcher@357c7988 13/08/22 13:44:21 DEBUG zookeeper.ClientCnxn: zookeeper.disableAutoWatchReset is false 13/08/22 13:44:21 INFO zookeeper.ClientCnxn: Opening socket connection to server datlas-cloudera-m01/10.5.96.195:2181. Will not attempt to authenticate using SASL (No se puede localizar una configuración de inicio de sesión) 13/08/22 13:44:21 INFO zookeeper.ClientCnxn: Socket connection established to datlas-cloudera-m01/10.5.96.195:2181, initiating session 13/08/22 13:44:21 DEBUG zookeeper.ClientCnxn: Session establishment request sent on datlas-cloudera-m01/10.5.96.195:2181 13/08/22 13:44:21 INFO zookeeper.ClientCnxn: Unable to read additional data from server sessionid 0x0, likely server has closed socket, closing socket connection and attempting reconnect Interrupting... Be patient, this might take some time. 13/08/22 13:44:21 INFO CliDriver: Interrupting... Be patient, this might take some time. 13/08/22 13:44:21 INFO ql.Driver: PERFLOG method=acquireReadWriteLocks 13/08/22 13:44:22 INFO zookeeper.ClientCnxn: Opening socket connection to server datlas-cloudera-m01/10.5.96.195:2181. Will not attempt to authenticate using SASL (No se puede localizar una configuración de inicio de sesión) 13/08/22 13:44:22 INFO zookeeper.ClientCnxn: Socket connection established to datlas-cloudera-m01/10.5.96.195:2181, initiating session 13/08/22 13:44:22 DEBUG zookeeper.ClientCnxn: Session establishment request sent on datlas-cloudera-m01/10.5.96.195:2181 13/08/22 13:44:22 INFO zookeeper.ClientCnxn: Unable to read additional data from server sessionid 0x0, likely server has closed socket, closing socket connection and attempting reconnect == Also, when I see the status of the services with the cloudera manager UI I see a Bad Health in Zookeper and HBase. I restarted a lot of times Zookeper but it is in the same status. Why I can perform queries with Beeswax UI and not with Hive console? And how can I solve this problem? BTW, if I type SHOW TABLES, I get the result, but a SELECT does not work I will apreciate very much your help, Thanks in advance.
Case When ... Else... Type Restriction
Hello: Can someone clarify this for me? In CASE a WHEN b THEN c [ELSE f] END, are the following rules necessary? 1. a and b should have the same TypeInfo, or an exception will be thrown. 2. c and f should have the same TypeInfo, or an exception will be thrown. Right now, even if c is int, f is smallint, it throws exception. Please let me know whether such restriction is there by design. Thank you very much! Xiu
[HIVE2] Error on insert into table dejavu2 partition (ds) select from jdbc driver
Hi Guys
I am getting the next error when I run an insert query from hive2 jdbc
driver
java.sql.SQLException: Error running query: null
at org.apache.hive.jdbc.Utils.verifySuccess(Utils.java:162)
at org.apache.hive.jdbc.Utils.verifySuccessWithInfo(Utils.java:150)
at org.apache.hive.jdbc.HiveStatement.execute(HiveStatement.java:196)
at org.apache.hive.jdbc.HiveStatement.executeUpdate(HiveStatement.java:275)
at
com.ml.dejavu.hadoop.historymigrator.MigrationTask$2.call(MigrationTask.java:143)
My code before run the query set the next parameters
add jar hdfs://namenode.melidoop.com:8020/libs/MapUDFS-0.0.1-SNAPSHOT.jar
create temporary function void_map as com.ml.VoidMap
create temporary function rename_keys as com.ml.RenameKeys
create temporary function filter_map as com.ml.FilterMap
create temporary function keys_to_lower as com.ml.KeysToLower
create temporary function my_nvl_str_to_map as com.ml.NvlStrToMap
set hive.exec.dynamic.partition=true
set hive.exec.dynamic.partition.mode=nonstrict
set hive.exec.max.dynamic.partitions=1
set hive.exec.max.dynamic.partitions.pernode=1
set mapred.output.compress=true
set hive.exec.compress.output=true
set mapred.output.compression.codec=org.apache.hadoop.io.compress.GzipCodec
set io.compression.codecs=org.apache.hadoop.io.compress.GzipCodec
set io.seqfile.compression.type=BLOCK
set mapred.job.name=##JOB_NAME##
Could you help me with this error? I can't found any similar.
The snipet that I using to run the query it's
for(String subQuery : configureQuery.split(\n)) {
log.debug(Config query [+subQuery+]);
stmt.execute(subQuery);
}
log.debug(Running Query [+query+]);
stmt = con.createStatement();
stmt.executeUpdate(query);
I can find that everything runs ok before run the executeUpdate.
I am using hive-jdbc-0.10.0-cdh4.3.0.jar
Thanks,
Gabriel.
ODBC driver for Excel
Trying to find an ODBC driver to use with Excel against my cluster that I don't have to shell out money for. Are there options? I'm using the cloudera driver for tableau and it works great! Just need a similar option for excel Sent from my iPhone
Re: ODBC driver for Excel
You can use the hortonworks odbc driver for free, it is available as an add-on - http://hortonworks.com/download/download-archives/ -Thejas On Thu, Aug 22, 2013 at 4:30 PM, Chad Dotzenrod cdotzen...@gmail.comwrote: Trying to find an ODBC driver to use with Excel against my cluster that I don't have to shell out money for. Are there options? I'm using the cloudera driver for tableau and it works great! Just need a similar option for excel Sent from my iPhone -- CONFIDENTIALITY NOTICE NOTICE: This message is intended for the use of the individual or entity to which it is addressed and may contain information that is confidential, privileged and exempt from disclosure under applicable law. If the reader of this message is not the intended recipient, you are hereby notified that any printing, copying, dissemination, distribution, disclosure or forwarding of this communication is strictly prohibited. If you have received this communication in error, please contact the sender immediately and delete it from your system. Thank You.
Alter or Query a table with field name 'date' always get error
Hi: I have a table transaction3 with a field name 'date'. That table is the target of importing table from MYSQL using Sqoop. The table in MYSQL has a field with the field name 'date' and SQOOP does not allow column name mapping. Therefore, the field name 'date' is kept in the transaction3 table in Hive. 'date' field is the partition field name of transaction3. I tried to change column name (existing code is is using created_date field) ALTER TABLE transaction3 CHANGE date created_date STRING; I got error. FAILED: ParseException line 1:32 extraneous input 'date' expecting Identifier near 'EOF' I cannot get around it. I query the count SELECT COUNT(*) FROM transaction3 WHERE date = '2013-08-15'; I got another error. FAILED: ParseException line 1:40 cannot recognize input near 'date' '=' ''2013-08-15'' in expression specification I query another table with field name 'created_date' instead and it works fine and I didn't get any error. I like to know if date is a reserved word. Is there any way to get around either change field name or query? Thanks.
Re: Alter or Query a table with field name 'date' always get error
Yes date is a reserved word. My recommendations If your table is Hive managed (I.e u created the table without using EXTERNAL ) === - Then copy the data for this hive table that is on HDFS to another location - Drop the table - CREATE a EXTERNAL TABLE with filename - replace date with some field name like date_ - ALTER TABLE ADD IF NOT EXISTS PARTITION (date_='your_partition') LOCATION '/path/to/your/HDFS/where/you/copied/the/data' If your table is EXTERNAL === - Drop the table - CREATE a EXTERNAL TABLE with filename - replace date with some field name like date_ - ALTER TABLE ADD IF NOT EXISTS PARTITION (date_='your_partition') LOCATION '/path/to/your/HDFS/where/you/copied/the/data' Hope this helps Good luck sanjay From: Sonya Ling sonya_ling1...@yahoo.commailto:sonya_ling1...@yahoo.com Reply-To: user@hive.apache.orgmailto:user@hive.apache.org user@hive.apache.orgmailto:user@hive.apache.org, Sonya Ling sonya_ling1...@yahoo.commailto:sonya_ling1...@yahoo.com Date: Thursday, August 22, 2013 6:45 PM To: user@hive.apache.orgmailto:user@hive.apache.org user@hive.apache.orgmailto:user@hive.apache.org Subject: Alter or Query a table with field name 'date' always get error Hi: I have a table transaction3 with a field name 'date'. That table is the target of importing table from MYSQL using Sqoop. The table in MYSQL has a field with the field name 'date' and SQOOP does not allow column name mapping. Therefore, the field name 'date' is kept in the transaction3 table in Hive. 'date' field is the partition field name of transaction3. I tried to change column name (existing code is is using created_date field) ALTER TABLE transaction3 CHANGE date created_date STRING; I got error. FAILED: ParseException line 1:32 extraneous input 'date' expecting Identifier near 'EOF' I cannot get around it. I query the count SELECT COUNT(*) FROM transaction3 WHERE date = '2013-08-15'; I got another error. FAILED: ParseException line 1:40 cannot recognize input near 'date' '=' ''2013-08-15'' in expression specification I query another table with field name 'created_date' instead and it works fine and I didn't get any error. I like to know if date is a reserved word. Is there any way to get around either change field name or query? Thanks. CONFIDENTIALITY NOTICE == This email message and any attachments are for the exclusive use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message along with any attachments, from your computer system. If you are the intended recipient, please be advised that the content of this message is subject to access, review and disclosure by the sender's Email System Administrator.
Re: How to perform arithmetic operations in hive
Yes this will work Also arithmetic operations will work in a WHERE clause Example select channel_id from keyword_impressions_log where header_date_partition='2013-08-21' and channel_id*10=290640 limit 10 From: Justin Workman justinjwork...@gmail.commailto:justinjwork...@gmail.com Reply-To: user@hive.apache.orgmailto:user@hive.apache.org user@hive.apache.orgmailto:user@hive.apache.org Date: Thursday, August 22, 2013 7:17 AM To: user@hive.apache.orgmailto:user@hive.apache.org user@hive.apache.orgmailto:user@hive.apache.org Subject: Re: How to perform arithmetic operations in hive Try select emp_name, (emp_no * 10) from emp_table; Sent from my iPhone On Aug 22, 2013, at 8:14 AM, Sandeep Nemuri nhsande...@gmail.commailto:nhsande...@gmail.com wrote: Hi all , Can we perform arithmetic operator on select command. I have a table emp_table with columns emp_name and emp_no i want to multiply the data in a emp_no with 10 . i tryed select * from emp_table where emp_no=emp_no*10; But it did not work . Help me out Thanks in advance . --Regards Sandeep Nemuri CONFIDENTIALITY NOTICE == This email message and any attachments are for the exclusive use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message along with any attachments, from your computer system. If you are the intended recipient, please be advised that the content of this message is subject to access, review and disclosure by the sender's Email System Administrator.
Re: Hive 0.11 with Cloudera CHD4.3 MR v1
For anyone interested, I have finished my blog on getting Hive-0.11.0 working with CDH4.3.0. It is a hack and can be made better but works like a charm. http://www.justinjworkman.com/big-data/hive-0-11-0-on-cloudera/ On Thu, Aug 22, 2013 at 8:29 AM, Jim Colestock j...@ramblingredneck.comwrote: Justin, That would be much appreciated! Thanks Again, JC On Aug 22, 2013, at 9:18 AM, Justin Workman justinjwork...@gmail.com wrote: I do have this working on CHD4.3.0 with MR1 and Kerberos. I am finishing up a blog today explaining what was needed to get it working. I will reply with the link later today. Justin Sent from my iPhone On Aug 22, 2013, at 6:17 AM, Jim Colestock j...@ramblingredneck.com wrote: Hello All, Sorry for not replying to the thread, but I never received any of your responses.. Hopefully this info will help: @kulkarni.swarnimhttp://search.gmane.org/?author=kulkarni.swarnim-Re5JQEeQqe8AvxtiuMwx3w%40public.gmane.orgsort=date Here is the error I'm getting on task tracker for the mapper: 2013-08-21 15:34:32,491 INFO org.apache.hadoop.mapred.TaskLogsTruncater: Initializing logs' truncater with mapRetainSize=-1 and reduceRetainSize=-1 2013-08-21 15:34:32,494 WARN org.apache.hadoop.mapred.Child: Error running child java.lang.RuntimeException: java.lang.NullPointerException at org.apache.hadoop.hive.ql.exec.Utilities.getMapRedWork(Utilities.java:230) at org.apache.hadoop.hive.ql.io.HiveInputFormat.init(HiveInputFormat.java:255) at org.apache.hadoop.hive.ql.io.HiveInputFormat.pushProjectionsAndFilters(HiveInputFormat.java:381) at org.apache.hadoop.hive.ql.io.HiveInputFormat.pushProjectionsAndFilters(HiveInputFormat.java:374) at org.apache.hadoop.hive.ql.io.CombineHiveInputFormat.getRecordReader(CombineHiveInputFormat.java:540) at org.apache.hadoop.mapred.MapTask.runOldMapper(MapTask.java:394) at org.apache.hadoop.mapred.MapTask.run(MapTask.java:332) at org.apache.hadoop.mapred.Child$4.run(Child.java:268) at java.security.AccessController.doPrivileged(Native Method) at javax.security.auth.Subject.doAs(Subject.java:396) at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1408) at org.apache.hadoop.mapred.Child.main(Child.java:262) Caused by: java.lang.NullPointerException at org.apache.hadoop.hive.ql.exec.Utilities.getMapRedWork(Utilities.java:215) ... 11 more 2013-08-21 15:34:32,498 INFO org.apache.hadoop.mapred.Task: Runnning cleanup for the task @Srinivas Surasanihttp://search.gmane.org/?author=Srinivas+Surasanisort=date Hopefully the above helps.. @Lars Francke http://search.gmane.org/?author=Lars+Franckesort=date Thanks, I'll take a peek at those 2 patches.. But I've just recently pulled down fresh code and recompiled everything and I'm still getting the above error. And for reference, below is my original email to the list.. Thanks all for you help! JC Hello All, Has anyone been successful at running hive 0.11 with Cloudera CDH 4.3? I've been able to get hive to connect to my metadb (which is in Postgres). Verified by doing a show tables.. I can run explain and describes on tables, but when I try to run anything that fires off an M/R job, I get the following error: hiveselect count(*) from tableA; Total MapReduce jobs = 1 Launching Job 1 out of 1 Number of reduce tasks determined at compile time: 1 In order to change the average load for a reducer (in bytes): set hive.exec.reducers.bytes.per.reducer=number In order to limit the maximum number of reducers: set hive.exec.reducers.max=number In order to set a constant number of reducers: set mapred.reduce.tasks=number Starting Job = job_201307112247_13816, Tracking URL = http://master:50030/jobdetails.jsp?jobid=job_201307112247_13816 Kill Command = /usr/lib/hadoop/bin/hadoop job -kill job_201307112247_13816 Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 1 2013-07-12 02:11:42,829 Stage-1 map = 0%, reduce = 0% 2013-07-12 02:12:08,173 Stage-1 map = 100%, reduce = 100% Ended Job = job_201307112247_13816 with errors Error during job, obtaining debugging information... Job Tracking URL: http://master:50030/jobdetails.jsp?jobid=job_201307112247_13816 Examining task ID: task_201307112247_13816_m_02 (and more) from job job_201307112247_13816 Exception in thread Thread-19 java.lang.NoClassDefFoundError: org/apache/hadoop/mapreduce/util/HostUtil at org.apache.hadoop.hive.shims.Hadoop23Shims.getTaskAttemptLogUrl(Hadoop23Shims.java:61) at org.apache.hadoop.hive.ql.exec.JobDebugger$TaskInfoGrabber.getTaskInfos(JobDebugger.java:186) at org.apache.hadoop.hive.ql.exec.JobDebugger$TaskInfoGrabber.run(JobDebugger.java:142) at java.lang.Thread.run(Thread.java:619) Caused by: java.lang.ClassNotFoundException: org.apache.hadoop.mapreduce.util.HostUtil at
Interpreting explain plan in hive
Hi, What are the key areas we need to check in the explain plan generated in Hive? I have checked the documentation, it's not detailed about the above question. https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Explain I have similar kind of question asked in our forum, which is unanswered. http://mail-archives.apache.org/mod_mbox/hive-user/201107.mbox/%3CCAAG3+BGHadR65FnR5udmGP9=QcriHuubnR8WR-VbxczdOhA=e...@mail.gmail.com%3E In summary, how we can distinguish a good/bad plan ? Thanks for your help. -- Thanks, Pandeeswaran
