How to query ambiguous data types in Athena?

Question

I have a data set stored in Parquet files crawled from S3 and registered in Glue Data Catalog. Some of the columns are of ambiguous type.

For example column col is typed as struct<long:bigint,string:string>.

If I select from that table tbl, then values of col are displayed for example like this:

{long=16, string=null}
{long=null, string=15.2}

What I would like to do now is query specifically those rows where col was classified as a string.

How would I do that?

(What would a query have to look like for filtering rows from tbl whose value in the col column is classified as long and > 10?)

Did you try `... WHERE col.long > 10`? How does this differ from your expected results? Also, if you want to filter on `col.string` you can do this: `... WHERE try(CAST(col.string AS bigint)) > 10`. — Piotr Findeisen, Oct 04 '19 at 14:00

score 5 · Accepted Answer · answered Oct 04 '19 at 17:51

5

You can filter numeric values like this:

... WHERE col.long > 10

You can filter string values that are actually numbers using Presto try function, like this:

... WHERE try(CAST(col.string AS bigint)) > 10

answered Oct 04 '19 at 17:51

Piotr Findeisen

1 Answers1