CODEWITHSUNDEEP
google-bigquery
Canovice
Canovice

Reputation: 10163

BigQuery check entire table for null values

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!

Upvotes: 4

Views: 12470

Answers (2)

Jon
Jon

Reputation: 11

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

Upvotes: 1

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172974

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

Upvotes: 18

Related Questions