RegexSerDe Issue

[Sasanka Vemavarapu <sa...@hotmail.com>]

Hi,

I have a HIVE table with ROW FORMAT SERDE, loaded data into it, could successfully query all columns, but am unable to query specific columns. Could you please help me understand this issue about why I am unable to query individual columns? If possible provide me a solution.

Steps to Replicate the Issue are as follows:

1. Create a text file 'confirmation_sample' with the following data

xxx$40052568xxx/99900000002
xxx$40052568xxx/99900000004
xxx$40052568xxx/99900000006
xxx$40052568xxx/99900000008
xxx$40052568xxx/99900000010
xxx$40052568xxx/99900000012

2. Create a hive table using the following script

create table confirmation (account1 STRING, account2 STRING,account3 STRING, account4 STRING, account5 STRING) 
ROW FORMAT SERDE 'org.apache.hadoop.hive.contrib.serde2.RegexSerDe'
WITH SERDEPROPERTIES ( "input.regex" = "((xxx[\$])([0-9]{8})(xxx/)(.*))","output.format.string" = "account1:%1$s account2:%2$s account3:%3$s account4:%4$s account5:%5$s")
STORED AS TEXTFILE;

3. Load data from 'confirmation_sample' into the confirmation table using the following hive command

load data local inpath '/home/sasanka/usecases/data/confirmation_sample' overwrite into table confirmation;

4. Query confirmation table using select * .. ( This command should work and return output as given below)
     
     hive>
    > select * from confirmation limit 6;
    OK
    xxx$40052568xxx/99900000002    xxx$    40052568    xxx/    99900000002
    xxx$40052568xxx/99900000004    xxx$    40052568    xxx/    99900000004
    xxx$40052568xxx/99900000006    xxx$    40052568    xxx/    99900000006
    xxx$40052568xxx/99900000008    xxx$    40052568    xxx/    99900000008
    xxx$40052568xxx/99900000010    xxx$    40052568    xxx/    99900000010
    xxx$40052568xxx/99900000012    xxx$    40052568    xxx/    99900000012
    Time taken: 0.135 seconds

5. query confirmation table using select account1 from ... ( Failing)

sasanka@sasanka:~$ select account1 from confirmation;
bash: syntax error near unexpected token `from'
sasanka@sasanka:~$ hive 
Logging initialized using configuration in file:/etc/hive/conf.dist/hive-log4j.properties
Hive history file=/tmp/sasanka/hive_job_log_e11c1bd0-a200-4879-b15f-fdde82a1717f_1920840164.txt
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/usr/lib/zookeeper/lib/slf4j-log4j12-1.6.1.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/usr/lib/hive/lib/slf4j-log4j12-1.6.1.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
hive> select account1 from confirmation;
Total MapReduce jobs = 1
Launching Job 1 out of 1
Number of reduce tasks is set to 0 since there's no reduce operator
Starting Job = job_201312061236_0020, Tracking URL = http://localhost:50030/jobdetails.jsp?jobid=job_201312061236_0020
Kill Command = /usr/lib/hadoop/bin/hadoop job  -kill job_201312061236_0020
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 0
2013-12-06 17:27:30,247 Stage-1 map = 0%,  reduce = 0%
2013-12-06 17:28:01,497 Stage-1 map = 100%,  reduce = 100%
Ended Job = job_201312061236_0020 with errors
Error during job, obtaining debugging information...
Job Tracking URL: http://localhost:50030/jobdetails.jsp?jobid=job_201312061236_0020
Examining task ID: task_201312061236_0020_m_000002 (and more) from job job_201312061236_0020

Task with the most failures(4): 
-----
Task ID:
  task_201312061236_0020_m_000000

URL:
  http://localhost:50030/taskdetails.jsp?jobid=job_201312061236_0020&tipid=task_201312061236_0020_m_000000
-----
Diagnostic Messages for this Task:
java.lang.RuntimeException: Error in configuring object
    at org.apache.hadoop.util.ReflectionUtils.setJobConf(ReflectionUtils.java:109)
    at org.apache.hadoop.util.ReflectionUtils.setConf(ReflectionUtils.java:75)
    at org.apache.hadoop.util.ReflectionUtils.newInstance(ReflectionUtils.java:133)
    at org.apache.hadoop.mapred.MapTask.runOldMapper(MapTask.java:413)
    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.reflect.InvocationTargetException
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.ja

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



Thanks,
Sasanka.