hadoop - Extract individual column from a HIVE table -

-

below select query hive table:

select *  test_aviation limit 5;                                                    ok 2015    1   1   1   4   2015-01-01  aa  19805   aa  n787aa  1   jfk new york     ny ny  36  new york    22  lax los angeles  ca ca  06  california  91  0900    0855    -5.00   0.00    0.00    -1  0900-0959   17.00   0912    1230    7.00    1230    1237    7.00    7.00    0.00    0   1200-1259   0.00        0.00    390.00  402.00  378.00  1.00    2475.00 10           2015    1   1   2   5   2015-01-02  aa  19805   aa  n795aa  1   jfk new york     ny ny  36  new york    22  lax los angeles  ca ca  06  california  91  0900    0850    -10.00  0.00    0.00    -1  0900-0959   15.00   0905    1202    9.00    1230    1211    -19.00  0.00    0.00    -2  1200-1259   0.00        0.00    390.00  381.00  357.00  1.00    2475.00 10           2015    1   1   3   6   2015-01-03  aa  19805   aa  n788aa  1   jfk new york     ny ny  36  new york    22  lax los angeles  ca ca  06  california  91  0900    0853    -7.00   0.00    0.00    -1  0900-0959   15.00   0908    1138    13.00   1230    1151    -39.00  0.00    0.00    -2  1200-1259   0.00        0.00    390.00  358.00  330.00  1.00    2475.00 10           2015    1   1   4   7   2015-01-04  aa  19805   aa  n791aa  1   jfk new york     ny ny  36  new york    22  lax los angeles  ca ca  06  california  91  0900    0853    -7.00   0.00    0.00    -1  0900-0959   14.00   0907    1159    19.00   1230    1218    -12.00  0.00    0.00    -1  1200-1259   0.00        0.00    390.00  385.00  352.00  1.00    2475.00 10           2015    1   1   5   1   2015-01-05  aa  19805   aa  n783aa  1   jfk new york     ny ny  36  new york    22  lax los angeles  ca ca  06  california  91  0900    0853    -7.00   0.00    0.00    -1  0900-0959   27.00   0920    1158    24.00   1230    1222    -8.00   0.00    0.00    -1  1200-1259   0.00        0.00    390.00  389.00  338.00  1.00    2475.00 10           time taken: 0.067 seconds, fetched: 5 row(s)

structure of hive table 

hive> describe test_aviation; ok col_value               string                                       time taken: 0.221 seconds, fetched: 1 row(s)

i want segregate entire table in different columns.i have written query below extract 12th column:

select regexp_extract(col_value, '^(?:([^,]*)\,?){1}', 12)  test_aviation;

output:

total jobs = 1 launching job 1 out of 1 number of reduce tasks set 0 since there's no reduce operator starting job = job_1437067221195_0008, tracking url = http://localhost:8088/proxy/application_1437067221195_0008/ kill command = /usr/local/hadoop/bin/hadoop job  -kill job_1437067221195_0008 hadoop job information stage-1: number of mappers: 1; number of reducers: 0 2015-07-17 02:46:56,215 stage-1 map = 0%,  reduce = 0% 2015-07-17 02:47:27,650 stage-1 map = 100%,  reduce = 0% ended job = job_1437067221195_0008 errors error during job, obtaining debugging information... job tracking url: http://localhost:8088/proxy/application_1437067221195_0008/ examining task id: task_1437067221195_0008_m_000000 (and more) job job_1437067221195_0008  task failures(4):  ----- task id:   task_1437067221195_0008_m_000000  url:   http://localhost:8088/taskdetails.jsp?jobid=job_1437067221195_0008&tipid=task_1437067221195_0008_m_000000 ----- diagnostic messages task: error: java.lang.runtimeexception: org.apache.hadoop.hive.ql.metadata.hiveexception: hive runtime error while processing row {"col_value":"2015\t1\t1\t1\t4\t2015-01-01\taa\t19805\taa\tn787aa\t1\tjfk\tnew york\t ny\tny\t36\tnew york\t22\tlax\tlos angeles\t ca\tca\t06\tcalifornia\t91\t0900\t0855\t-5.00\t0.00\t0.00\t-1\t0900-0959\t17.00\t0912\t1230\t7.00\t1230\t1237\t7.00\t7.00\t0.00\t0\t1200-1259\t0.00\t\t0.00\t390.00\t402.00\t378.00\t1.00\t2475.00\t10\t\t\t"}     @ org.apache.hadoop.hive.ql.exec.mr.execmapper.map(execmapper.java:195)     @ org.apache.hadoop.mapred.maprunner.run(maprunner.java:54)     @ org.apache.hadoop.mapred.maptask.runoldmapper(maptask.java:450)     @ org.apache.hadoop.mapred.maptask.run(maptask.java:343)     @ org.apache.hadoop.mapred.yarnchild$2.run(yarnchild.java:163)     @ java.security.accesscontroller.doprivileged(native method)     @ javax.security.auth.subject.doas(subject.java:415)     @ org.apache.hadoop.security.usergroupinformation.doas(usergroupinformation.java:1628)     @ org.apache.hadoop.mapred.yarnchild.main(yarnchild.java:158) caused by: org.apache.hadoop.hive.ql.metadata.hiveexception: hive runtime error while processing row {"col_value":"2015\t1\t1\t1\t4\t2015-01-01\taa\t19805\taa\tn787aa\t1\tjfk\tnew york\t ny\tny\t36\tnew york\t22\tlax\tlos angeles\t ca\tca\t06\tcalifornia\t91\t0900\t0855\t-5.00\t0.00\t0.00\t-1\t0900-0959\t17.00\t0912\t1230\t7.00\t1230\t1237\t7.00\t7.00\t0.00\t0\t1200-1259\t0.00\t\t0.00\t390.00\t402.00\t378.00\t1.00\t2475.00\t10\t\t\t"}     @ org.apache.hadoop.hive.ql.exec.mapoperator.process(mapoperator.java:550)     @ org.apache.hadoop.hive.ql.exec.mr.execmapper.map(execmapper.java:177)     ... 8 more caused by: org.apache.hadoop.hive.ql.metadata.hiveexception: unable execute method public java.lang.string org.apache.hadoop.hive.ql.udf.udfregexpextract.evaluate(java.lang.string,java.lang.string,java.lang.integer)  on object org.apache.hadoop.hive.ql.udf.udfregexpextract@4def4616 of class org.apache.hadoop.hive.ql.udf.udfregexpextract arguments {2015  1   1   1   4   2015-01-01  aa  19805   aa  n787aa  1   jfk new york     ny ny  36  new york    22  lax los angeles  ca ca  06  california  91  0900    0855    -5.00   0.00    0.00    -1  0900-0959   17.00   0912    1230    7.00    1230    1237    7.00    7.00    0.00    0   1200-1259   0.00        0.00    390.00  402.00  378.00  1.00    2475.00 10          :java.lang.string, ^(?:([^,]*),?){1}:java.lang.string, 12:java.lang.integer} of size 3     @ org.apache.hadoop.hive.ql.exec.functionregistry.invoke(functionregistry.java:1243)     @ org.apache.hadoop.hive.ql.udf.generic.genericudfbridge.evaluate(genericudfbridge.java:182)     @ org.apache.hadoop.hive.ql.exec.exprnodegenericfuncevaluator._evaluate(exprnodegenericfuncevaluator.java:166)     @ org.apache.hadoop.hive.ql.exec.exprnodeevaluator.evaluate(exprnodeevaluator.java:77)     @ org.apache.hadoop.hive.ql.exec.exprnodeevaluator.evaluate(exprnodeevaluator.java:65)     @ org.apache.hadoop.hive.ql.exec.selectoperator.processop(selectoperator.java:79)     @ org.apache.hadoop.hive.ql.exec.operator.forward(operator.java:793)     @ org.apache.hadoop.hive.ql.exec.tablescanoperator.processop(tablescanoperator.java:92)     @ org.apache.hadoop.hive.ql.exec.operator.forward(operator.java:793)     @ org.apache.hadoop.hive.ql.exec.mapoperator.process(mapoperator.java:540)     ... 9 more caused by: java.lang.reflect.invocationtargetexception     @ sun.reflect.nativemethodaccessorimpl.invoke0(native method)     @ sun.reflect.nativemethodaccessorimpl.invoke(nativemethodaccessorimpl.java:57)     @ sun.reflect.delegatingmethodaccessorimpl.invoke(delegatingmethodaccessorimpl.java:43)     @ java.lang.reflect.method.invoke(method.java:606)     @ org.apache.hadoop.hive.ql.exec.functionregistry.invoke(functionregistry.java:1219)     ... 18 more caused by: java.lang.indexoutofboundsexception: no group 12     @ java.util.regex.matcher.group(matcher.java:487)     @ org.apache.hadoop.hive.ql.udf.udfregexpextract.evaluate(udfregexpextract.java:56)     ... 23 more

please me extract different columns hive table.

try this:

select split(col_value,' ')[11] column_12 test_aviation;

assuming have space delimiters.

'\\t' if tab  '\\|' pipe...  ':'

and on


Comments

Post a Comment

Popular posts from this blog

c# - Better 64-bit byte array hash -

-
i need hash algorithm produces 64-bit hash code ( long ) fewer collisions string.gethashcode() , fast (no expensive calls cryptographic functions). here's implementation of fnv still shows 3% of collisions after testing 2 million random strings. need number way lower. void main() { const string chars = "abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz!#@$%^&*()_+}{\":?><,./;'[]0123456789\\"; const int n = 2000000; var random = new random(); var hashes = new hashset<long>(); int collisions = 0; for(int = 0; < n; i++) { var len = random.next(chars.length); var str = new char[len]; (int j = 0; j < len; j++) { str[j] = chars[random.next(chars.length)]; } var s = new string(str); if(!hashes.add(get64bithash( s ))) collisions++; } console.writeline("collision percentage after " + n + " random strings: " + ((doubl
Read more

webrtc - Which ICE candidate am I using and why? -

-
[questions in bold below] i have setup kurento media server 5.1.3 in datacenter behind firewall running os ubuntu 14.04. has 2 network cards: 222.222.222.222 (eth0 - private ip) 111.111.111.111 (eth1 - public ip) attached below sdp (setremotedescription) when browser connected kurento media server type: answer, sdp: v=0 o=- 5487318114793304426 0 in ip4 0.0.0.0 s=kurento media server c=in ip4 0.0.0.0 t=0 0 a=group:bundle audio video m=audio 59068 rtp/savpf 111 0 c=in ip4 111.111.111.111 a=rtpmap:111 opus/48000/2 a=rtpmap:0 pcmu/8000 a=sendrecv a=rtcp:59068 in ip4 111.111.111.111 a=rtcp-mux a=ssrc:669011897 cname:user39019747@host-6e83e4c2 a=extmap:3 http://www.webrtc.org/experiments/rtp-hdrext/abs-send-time a=mid:audio b=as:20 a=ice-ufrag:ymdk a=ice-pwd:lylifk5ueqzpwm91ddj37e a=fingerprint:sha-256 ff:0f:81:8c:41:4e:b4:b6:c6:d8:36:f3:d6:5f:09:fd:5f:af:13:b3:9d:fc:12:66:ac:f3:56:d6:5b:0a:73:5d a=candidate:1 1 udp 2013266431 111.111.111.111 55239 typ host a=candidate:2 1 udp
Read more

php - Zend Framework / Skeleton-Application / Composer install issue -

-
i trying create zend framework application using skeleton in netbeans. when run composer install, following error message: "c:\php\php.exe" "c:\composer\composer.phar" "--ansi" "--no-interaction" "update" "--dev" using deprecated option "dev". dev packages installed default now. loading composer repositories package information updating dependencies (including require-dev) requirements not resolved installable set of packages. problem 1 - package requires php >=5.5 php version (5.4.42) not satisfy requirement. problem 2 - installation request zendframework/zendframework 2.5.1 -> satisfiable zendframework/zendframework[2.5.1]. - zendframework/zendframework 2.5.1 requires php >=5.5 -> php version (5.4.42) not satisfy requirement. done. apparently, zend framework only needs php 5.3+ contradicts above. need use php 5.4. my composer.json following: { "name": "
Read more