topic Re: null object while running a query against parquet in Warehousing & Analytics

null object while running a query against parquet

Shaimaa — Mon, 24 Jun 2024 17:39:38 GMT

I am running this query against parquet:

SELECT SUM(CASE WHEN match_result.ecommerce.has_online_payments THEN 1 ELSE 0 END) FROM parquet.`s3://folder_path/*`

when all the values of the object `match_result.ecommerce` are null, I get the following error:

[INVALID_EXTRACT_BASE_FIELD_TYPE] Can't extract a value from "match_result.ecommerce". Need a complex type [STRUCT, ARRAY, MAP] but got "INT". SQLSTATE: 42000; line 2, pos 14

How can I fix this issue?

Re: null object while running a query against parquet

giuseppegrieco — Mon, 24 Jun 2024 20:08:12 GMT

Without additional context, my assumption is that since all the values are null, it's impossible to determine the column's type. Do you have a default value? You could consider coalescing with that value. For example, if the default value is false:

SELECT SUM( CASE WHEN COALESCE( CAST(match_result.ecommerce.has_online_payments AS BOOLEAN), FALSE ) THEN 1 ELSE 0 END ) FROM parquet.`s3://folder_path/*`

Otherwise you can handle the null values as @Retired_mod suggested.

Re: null object while running a query against parquet

Shaimaa — Mon, 24 Jun 2024 20:09:52 GMT

@Anonymous Thank you but the issue persists even if I add a null check:

SELECT SUM(CASE WHEN match_result IS NOT NULL AND match_result.ecommerce IS NOT NULL AND match_result.ecommerce.has_online_payments THEN 1 ELSE 0 END) FROM parquet.`s3://folder_path/*`

the values in this particular file for "match_result.ecommerce" are null but they are not always null for all files that I run the query against. How can I tell specify that the condition should evaluate to 0 if the value of "match_result.ecommerce" is null instead of erroring out?

Re: null object while running a query against parquet

Shaimaa — Tue, 25 Jun 2024 11:44:22 GMT

None of these solutions with coalesce work because it's "match_result.ecommerce" that is null not "match_result.ecommerce.has_online_payments". So it's still trying to extract a value from a null. Help me modify the query accordingly please.