Simple Example to get the total number of records in BigQuery with Python

The easiest way to get the number of records from a BigQuery query result directly is to use the next() function to retrieve the first row of the result set. Here’s a streamlined approach:

from google.cloud import bigquery

# Initialize the BigQuery client
client = bigquery.Client()

# Your query
US_TODAY = "2024-07-10"
query = f"""
    SELECT count(*) as cnt
    FROM
        `lucid-bebop-474.player_event.kafka_raw`
    WHERE DATE(dt) = '{US_TODAY}'
"""

# Execute the query
query_results = client.query(query).result()

# Get the number of records directly
count_value = next(query_results).cnt

print(count_value)

Explanation:

1. Initialize the BigQuery Client: Connect to your BigQuery project.
2. Define Your Query: Write the SQL query to count the records.
3. Execute the Query: Run the query using client.query(query).result().
4. Retrieve the Count: Use next(query_results).cnt to directly access the count value from the first row of the results.

This approach is simple and avoids the need for additional data structures like Pandas DataFrame, making it efficient and easy to use for fetching single values from the query result.