CODEWITHSUNDEEP
google-bigquery
Ali SAID OMAR
Ali SAID OMAR

Reputation: 6792

Bigquery Find which table an entry belongs to

I'm cleaning my production dataset, to do, i'm looking for some useless entries. 

SELECT count(pid_like) FROM TABLE_DATE_RANGE(DATASET.TABLE_PRODUCTION_, DATE_ADD(CURRENT_TIMESTAMP(), -30, 'DAY'),CURRENT_TIMESTAMP()) where c1 is null and c2 is null and c3 is null and c4 is null

So I want to delete entries where c1, c2, c3 and c4 are null.

Before I made on the last 30 tables the cleaning as:

# done for each last 30 tables
DELETE FROM DATASET.TABLE_PRODUCTION_YYYYMMDD where c1 is null and c2 is null and c2 is null and c4 is null.

But after the cleaning, some useless entries remain but I can't find on what table they are.

Upvotes: 0

Views: 61

Answers (1)

Elliott Brossard
Elliott Brossard

Reputation: 33765

Try this to find which tables have rows with nulls:

#standardSQL
SELECT
  _TABLE_SUFFIX AS suffix,
  COUNT(*) AS null_count
FROM `DATASET.TABLE_PRODUCTION_*`
WHERE _TABLE_SUFFIX BETWEEN
    FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)) AND
    FORMAT_DATE('%Y%m%d', CURRENT_DATE()) AND
  c1 IS NULL AND
  c2 IS NULL AND
  c3 IS NULL AND
  c4 IS NULL
GROUP BY suffix
HAVING null_count > 0
ORDER BY null_count DESC;

It will return the suffixes (dates) with null rows and the counts.

Upvotes: 2

Related Questions