BigQuery check entire table for null values

1
votes

Not sure if a reproducible example is necessary here. I have a big-ish and wide-ish table in BigQuery (10K rows x 100 cols) and I would like to know if any columns have null values, and how many null values there are. Is there a query that I can run that would return a 1-row table indicating the number of null values in each column, that doesn't require 100 ifnull calls?

Thanks!

2
google-bigquery

2 Answers

6
votes

Below is for BigQuery Standard SQL 

#standardSQL
SELECT col_name, COUNT(1) nulls_count
FROM `project.dataset.table` t,
UNNEST(REGEXP_EXTRACT_ALL(TO_JSON_STRING(t), r'"(\w+)":null')) col_name
GROUP BY col_name

Instead of returning just one row - it returns those column which have NULL in them - each column and count in separate row - like in below example 

Row col_name    nulls_count  
1   col_a       21   
2   col_d       12
0
votes

This will provide the percentage of null values:

SELECT col_name,
       COUNT(1) AS nulls_count,
       round(100*(count(1)/
                      (SELECT count(*)
                       FROM `project.dataset.table`)), 2) AS percent_nulls
FROM `project.dataset.table` t,
     UNNEST(REGEXP_EXTRACT_ALL(TO_JSON_STRING(t), r'"(\w+)":null')) col_name
GROUP BY col_name
ORDER BY nulls_count DESC