Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

impala split udf

Solved Go to solution

impala split udf

Contributor

I am on CDH 5.2 using Impala 2.0

Does anybody know when the UDTF split function be available?  

 

I have an array that is delimited by Pipe that has a max of 6 entries; min of 4

split[0], split[3] would have worked perfectly

 

Ideas are welcome on how to work around my challenge 

example 

aaaaaaaa|bbb|ccc|ddddddddddd|eeeeeeee|ffffff

 

a is variable length but always there

b may not be present

c may not be present

d, e and f is always there fixed length

 

I am think a really convoluted usage of substr

 

 

1 ACCEPTED SOLUTION

Accepted Solutions

Re: impala split udf

 

> select regexp_extract('aaaaaaaa|bbb|ccc|ddddddddddd|eeeeeeee|ffffff', '([a-z]+)\\|([a-z]+)\\|([a-z]+)\\|([a-z]+)\\|?([a-z]*)\\|?([a-z]*)', 1);
Query: select regexp_extract('aaaaaaaa|bbb|ccc|ddddddddddd|eeeeeeee|ffffff', '([a-z]+)\\|([a-z]+)\\|([a-z]+)\\|([a-z]+)\\|?([a-z]*)\\|?([a-z]*)', 1)
aaaaaaaa
Fetched 1 row(s) in 0.01s
 
> select regexp_extract('aaaaaaaa|bbb|ccc|ddddddddddd|eeeeeeee|ffffff', '([a-z]+)\\|([a-z]+)\\|([a-z]+)\\|([a-z]+)\\|?([a-z]*)\\|?([a-z]*)', 3);
Query: select regexp_extract('aaaaaaaa|bbb|ccc|ddddddddddd|eeeeeeee|ffffff', '([a-z]+)\\|([a-z]+)\\|([a-z]+)\\|([a-z]+)\\|?([a-z]*)\\|?([a-z]*)', 3)
ccc
Fetched 1 row(s) in 0.01s
 
> select regexp_extract('aaaaaaaa|bbb|ccc|ddddddddddd|eeeeeeee|ffffff', '([a-z]+)\\|([a-z]+)\\|([a-z]+)\\|([a-z]+)\\|?([a-z]*)\\|?([a-z]*)', 6);
Query: select regexp_extract('aaaaaaaa|bbb|ccc|ddddddddddd|eeeeeeee|ffffff', '([a-z]+)\\|([a-z]+)\\|([a-z]+)\\|([a-z]+)\\|?([a-z]*)\\|?([a-z]*)', 6)
ffffff
Fetched 1 row(s) in 0.01s
 
> select regexp_extract('aaaaaaaa|ddddddddddd|eeeeeeee|ffffff', '([a-z]+)\\|([a-z]+)\\|([a-z]+)\\|([a-z]+)\\|?([a-z]*)\\|?([a-z]*)', 1);
Query: select regexp_extract('aaaaaaaa|ddddddddddd|eeeeeeee|ffffff', '([a-z]+)\\|([a-z]+)\\|([a-z]+)\\|([a-z]+)\\|?([a-z]*)\\|?([a-z]*)', 1)
aaaaaaaa
Fetched 1 row(s) in 0.01s
 
> select regexp_extract('aaaaaaaa|ddddddddddd|eeeeeeee|ffffff', '([a-z]+)\\|([a-z]+)\\|([a-z]+)\\|([a-z]+)\\|?([a-z]*)\\|?([a-z]*)', 3);
Query: select regexp_extract('aaaaaaaa|ddddddddddd|eeeeeeee|ffffff', '([a-z]+)\\|([a-z]+)\\|([a-z]+)\\|([a-z]+)\\|?([a-z]*)\\|?([a-z]*)', 3)
eeeeeeee
Fetched 1 row(s) in 0.01s
 
> select regexp_extract('aaaaaaaa|ddddddddddd|eeeeeeee|ffffff', '([a-z]+)\\|([a-z]+)\\|([a-z]+)\\|([a-z]+)\\|?([a-z]*)\\|?([a-z]*)', 6);
Query: select regexp_extract('aaaaaaaa|ddddddddddd|eeeeeeee|ffffff', '([a-z]+)\\|([a-z]+)\\|([a-z]+)\\|([a-z]+)\\|?([a-z]*)\\|?([a-z]*)', 6)
""

 

2 REPLIES 2

Re: impala split udf

 

> select regexp_extract('aaaaaaaa|bbb|ccc|ddddddddddd|eeeeeeee|ffffff', '([a-z]+)\\|([a-z]+)\\|([a-z]+)\\|([a-z]+)\\|?([a-z]*)\\|?([a-z]*)', 1);
Query: select regexp_extract('aaaaaaaa|bbb|ccc|ddddddddddd|eeeeeeee|ffffff', '([a-z]+)\\|([a-z]+)\\|([a-z]+)\\|([a-z]+)\\|?([a-z]*)\\|?([a-z]*)', 1)
aaaaaaaa
Fetched 1 row(s) in 0.01s
 
> select regexp_extract('aaaaaaaa|bbb|ccc|ddddddddddd|eeeeeeee|ffffff', '([a-z]+)\\|([a-z]+)\\|([a-z]+)\\|([a-z]+)\\|?([a-z]*)\\|?([a-z]*)', 3);
Query: select regexp_extract('aaaaaaaa|bbb|ccc|ddddddddddd|eeeeeeee|ffffff', '([a-z]+)\\|([a-z]+)\\|([a-z]+)\\|([a-z]+)\\|?([a-z]*)\\|?([a-z]*)', 3)
ccc
Fetched 1 row(s) in 0.01s
 
> select regexp_extract('aaaaaaaa|bbb|ccc|ddddddddddd|eeeeeeee|ffffff', '([a-z]+)\\|([a-z]+)\\|([a-z]+)\\|([a-z]+)\\|?([a-z]*)\\|?([a-z]*)', 6);
Query: select regexp_extract('aaaaaaaa|bbb|ccc|ddddddddddd|eeeeeeee|ffffff', '([a-z]+)\\|([a-z]+)\\|([a-z]+)\\|([a-z]+)\\|?([a-z]*)\\|?([a-z]*)', 6)
ffffff
Fetched 1 row(s) in 0.01s
 
> select regexp_extract('aaaaaaaa|ddddddddddd|eeeeeeee|ffffff', '([a-z]+)\\|([a-z]+)\\|([a-z]+)\\|([a-z]+)\\|?([a-z]*)\\|?([a-z]*)', 1);
Query: select regexp_extract('aaaaaaaa|ddddddddddd|eeeeeeee|ffffff', '([a-z]+)\\|([a-z]+)\\|([a-z]+)\\|([a-z]+)\\|?([a-z]*)\\|?([a-z]*)', 1)
aaaaaaaa
Fetched 1 row(s) in 0.01s
 
> select regexp_extract('aaaaaaaa|ddddddddddd|eeeeeeee|ffffff', '([a-z]+)\\|([a-z]+)\\|([a-z]+)\\|([a-z]+)\\|?([a-z]*)\\|?([a-z]*)', 3);
Query: select regexp_extract('aaaaaaaa|ddddddddddd|eeeeeeee|ffffff', '([a-z]+)\\|([a-z]+)\\|([a-z]+)\\|([a-z]+)\\|?([a-z]*)\\|?([a-z]*)', 3)
eeeeeeee
Fetched 1 row(s) in 0.01s
 
> select regexp_extract('aaaaaaaa|ddddddddddd|eeeeeeee|ffffff', '([a-z]+)\\|([a-z]+)\\|([a-z]+)\\|([a-z]+)\\|?([a-z]*)\\|?([a-z]*)', 6);
Query: select regexp_extract('aaaaaaaa|ddddddddddd|eeeeeeee|ffffff', '([a-z]+)\\|([a-z]+)\\|([a-z]+)\\|([a-z]+)\\|?([a-z]*)\\|?([a-z]*)', 6)
""

 

Re: impala split udf

Contributor

somewhat better than me leveraging nested substr and reverses

 

but still wouldn't mind seeing the split function be brought in

Don't have an account?
Coming from Hortonworks? Activate your account here