CODEWITHSUNDEEP
sqlpresto
Sujal
Sujal

Reputation: 13

Selecting Columns which are not null in Athena(Metabase)

I have a table of 1000+ columns in Athena(Metabase), and I want to know how can I extract only those columns which are not null for a certain group of ID.

Upvotes: 1

Views: 3791

Answers (1)

Chuma
Chuma

Reputation: 739

Typically, this would need an UNPIVOTING of your columns to rows and then check where not null and then back to PIVOT. From the documentation, Athena may do it simpler. As documented here

SELECT filter(ARRAY [-1, NULL, 10, NULL], q -> q IS NOT NULL)

Which returns:

[-1,10]

Unfortunately, since there is no ability to be dynamic until we get to an array, this looks like:

WITH dataset AS (
  SELECT
    ID,
    ARRAY[field1, field2, field3, .....] AS fields
  FROM
    Your1000ColumnTable
)
SELECT ID, SELECT filter(fields, q -> q IS NOT NULL)
FROM dataset

If you need to access the column names from the array, use a mapping to field names when creating the array as seen here

Upvotes: 1

Related Questions