Count how many rows are in a BigQuery table using Python

Question

For a short summary - I want to set a variable equal to the amount of rows present or amount of data entries present in a table.

I am running a query in my Python script that counts the amount of devices that have been used in my app through the past week. I do this by counting the distinct serial numbers. Currently I am using this query.

#standardsql
SELECT count(distinct serial)
FROM `dataworks-356fa.FirebaseArchive.test2` 
Where (PeripheralType = 1 or PeripheralType = 2 or PeripheralType = 12)
AND EXTRACT(WEEK FROM createdAt) = EXTRACT(WEEK FROM CURRENT_TIMESTAMP()) - 1
AND serial != 'null'

I am then sending this result to slack at a later point in my python script with this code.

with open('Count_BB_Serial_weekly.json', 'r') as lowfile:
  low = json.load(lowfile)

low1 = low["f0_"]

f0_ = the amount of distinct serial numbers counted

However, now I want to add a function that I can also see the serial numbers which have been used along with the amount of different serial numbers used. The query to list the serial numbers is similar to the above just without the count(distinct) part. Is there a way that I can set low1 equal to the amount of rows in the table because I can not run a count function that also lists the serial numbers. Hopefully you can follow along with this. If there is any confusion I will address it in the comments.

Thank you!

I just realized that this is actually pulling the value from a JSON not the bigquery table. So is it possible to count the amount of entries present in a JSON? — W. Stephens
– W. Stephens, Commented Jul 19, 2017 at 5:21
You can count entries in a JSON in python by using the len operator. Still, from what I understood from your question, this json is the result of a query ran in BQ. This being the case I wonder if it would be better to bring another field in the results (such as f1_) containing the total rows processed already (let BQ do the work, not python). — Willian Fuks
– Willian Fuks, Commented Jul 19, 2017 at 5:29
You can count entries in a JSON in python by using the len operator. Still, from what I understood from your question, this JSON is the result of a query ran in BQ. This being the case, I wonder if it would be better to bring another field in the results (such as f1_) containing the total rows processed already (let BQ do the work, not python). — Willian Fuks
– Willian Fuks, Commented Jul 19, 2017 at 5:30
So you're saying that I can add a line to my query that would add a column to the table that has the value of rows processed? — W. Stephens
– W. Stephens, Commented Jul 19, 2017 at 5:33
Just answered with an example. Not sure though why you tagged the question with "mysql" and "bigquery". I suppose by the question title that you are working with BigQuery and not MySQL — Willian Fuks
– Willian Fuks, Commented Jul 19, 2017 at 6:05

Willian Fuks · Accepted Answer · 2017-07-19 06:14:24Z

3

You probably can already bring this result in BigQuery. For instance:

#standardSQL
WITH data AS(
  SELECT '1' AS serial UNION ALL
  SELECT '2' as serial UNION ALL
  SELECT '3' AS serial UNION ALL
  SELECT '1' AS serial
)

SELECT
  ARRAY_AGG(DISTINCT serial) distinct_serials,
  ARRAY_LENGTH(ARRAY_AGG(DISTINCT serial)) total_serials
FROM
  data

Results in:

[
  {
    "distinct_serials": [
      "2",
      "1",
      "3"
    ],
    "total_serials": "3"
  }
]

Or in your query, it would be something like:

#standardsql
SELECT
    ARRAY_AGG(serial) serials,
    ARRAY_LENGTH(ARRAY_AGG(serial)) total_serials
FROM(
SELECT distinct serial AS serial
FROM `dataworks-356fa.FirebaseArchive.test2` 
Where (PeripheralType = 1 or PeripheralType = 2 or PeripheralType = 12)
AND EXTRACT(WEEK FROM createdAt) = EXTRACT(WEEK FROM CURRENT_TIMESTAMP()) - 1
AND serial != 'null')

Results should be something like:

[{"serials": ["serial_1", "serial_2", (...)], "total_serials": 10}]

You would have an ARRAY with the serials and another field with its total entries.

edited Jul 19, 2017 at 6:14

answered Jul 19, 2017 at 5:55

Willian Fuks

11.9k10 gold badges55 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

W. Stephens Over a year ago

Im trying to run this but Bigquery is giving me an error that serial is an unrecognized name in ARRAY_AGG(). How is that possible?

Collectives™ on Stack Overflow

Count how many rows are in a BigQuery table using Python

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related