3

How would I query to select length(int) which is within the array 'details' which is within the 'packets' column? Hopefully, the image attached will explain better than me!

I've tried SELECT packets.details.length FROM test.ssh_data which doesn't work.

This gives me the following error:

illegal column/field reference 'packets.details.length' with intermediate collection 'details' of type 'ARRAY<STRUCT<datestamp:STRING,length:INT>>

Thank you in advance!

description of test.test_data

Improve this question
2
  • Table structure , sample data and expected result would be more helpful. Commented Aug 11, 2020 at 12:41
  • have you tried size(arr_col) ? Commented Aug 12, 2020 at 7:36

1 Answer 1

Reset to default
5

In the Impala nested types support, arrays and maps are treated as nested tables. You need to reference them in the FROM clause to unnest them. In that case you can add the array to the from clause, taking care to refer to it via sd, the alias of the table it's inside. E.g.

SELECT d.length FROM test.ssh_data sd, sd.packets.details d
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Hi thanks for the comment. I gave this a go but gives no results? I also tried to add another column to this just to see if that column indeed does not have any values i.e. SELECT d.length sd.datestamp FROM test.ssh_data sd, sd.packets.details d but that also produces no results even though I know datestamp does indeed have values :/
Ah, you might want to change it to "FROM test.ssh_data sd, LEFT JOIN sd.packets.details d". The first version only returns rows if there are elements in the nested collection, so could be filtering out some of the top-level rows.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.