Find how much bytes processed by a certain table on specific date ?

[Akash101]

I'm running some queries on a table through out the day.
Now I want to find how much bytes processed by that table on specific date ? 
How much bill it will cost me ? 

[ms4446]

BigQuery's billing model is predicated on the volume of data processed by your queries. To accurately monitor the data consumption of a specific table on a particular date, you can follow these steps:

Find Relevant Job IDs:

1. Utilize Cloud Logging: Identify the relevant job IDs for the specified date with this query:

resource.type="bigquery_resource" 
protoPayload.methodName="jobservice.jobcompleted" 
timestamp >= "YYYY-MM-DDT00:00:00Z" AND timestamp < "YYYY-MM-DDT23:59:59Z" 

Note: Direct filtering by table name in Cloud Logging may face limitations. For comprehensive tracking, proceed with the subsequent step.

Query Job Details:

2. Leverage INFORMATION_SCHEMA: Obtain detailed byte-level data through the JOBS_BY_PROJECT or JOBS_BY_USER views:

   SELECT 
       job_id, 
       user_email, 
       total_bytes_processed, 
       total_bytes_billed, 
       creation_time
   FROM `your_project_id.region.INFORMATION_SCHEMA.JOBS_BY_PROJECT`
   WHERE job_id IN ('jobId1', 'jobId2', ...) 
   AND creation_time BETWEEN 'YYYY-MM-DDT00:00:00' AND 'YYYY-MM-DDT23:59:59'
   

• Ensure to replace the placeholders with your actual project details and the job IDs identified in the first step.

  Example Output:

job_id	user_email	total_bytes_processed	total_bytes_billed	creation_time
job123	user@example.com	2500000000	2500000000	2023-11-23 14:35:00 UTC

Cost Management and Analysis Tools

• Google Cloud's BigQuery Cost Management: Implement cost controls, utilize the BigQuery Reservation API, and review billing reports for effective cost management.

• Data Studio: Develop custom dashboards for a visual representation of your costs.

• Looker: Offers comprehensive analytics and business intelligence capabilities.

  Optimization for Cost Savings

  • Query Optimization: Enhance your queries by filtering data early and employing efficient joins to minimize data processed.

  • Schema Optimization: Implement partitioning and clustering for more efficient data retrieval, leading to potential cost savings.

  • Data Management: Regularly evaluate your data storage strategy. Archiving old data or reorganizing tables according to usage patterns can further optimize costs.

  Key Points

  • Pricing Models: BigQuery's on-demand pricing model is typically more suitable for variable workloads, while flat-rate pricing may be more cost-effective for consistent, high-volume usage.

  • Proactive Optimization: Continually refining your queries and data structures is crucial for minimizing BigQuery costs and maximizing the platform's value.

[Akash101]

How to filter the job ids of specific 'X' table from all job ids we will get from 1st step ?
is it by opening all job ids and checking if it has queried on that 'X' table ? 

[ms4446]

It can be tricky to determine which BigQuery jobs interacted with a specific table ('X') using Cloud Logging data directly. Below are the most effective approaches:

1. Filtering Job Information with High Accuracy

• Step 1: Collect Initial Job IDs

  • Use Cloud Logging with date-based filtering to broadly collect job IDs active within your target time frame.

• Step 2: Refine with INFORMATION_SCHEMA

  • Use INFORMATION_SCHEMA.JOBS_BY_* views to pinpoint relevant jobs. Filter based on the presence of table 'X' in the query text:

  SELECT job_id, query 
  FROM `your_project_id.region.INFORMATION_SCHEMA.JOBS_BY_PROJECT` 
  WHERE job_id IN ('job123', 'job456', ...) 
  AND query LIKE '% your_table_X %' 
  

  • Important:
    • Query References: Account for how 'X' is referenced (aliases, fully qualified names, etc.). Adjust your LIKE pattern accordingly.
    • Performance: Filtering many jobs against INFORMATION_SCHEMA can be taxing. Narrow down your initial job ID list as much as possible.

2. Advanced Log Parsing for General Monitoring

• Custom log parsing can extract table names from query text within logs, but it's complex and less reliable, especially with intricate SQL.

• This is better for general table usage monitoring, not precise byte-level analysis or cost estimation.

  • Key Considerations:
    • Direct Filtering Limitations: Cloud Logging isn't set up for filtering by table name.
    • Accuracy vs. Scope: Method 1 is accurate for jobs directly related to 'X' (cost tracking, optimization). Method 2 is a broader overview, but with less precision.

Additional Tips:

• Regular Expressions: For complex table naming, use REGEXP_CONTAINS for more flexible pattern matching in INFORMATION_SCHEMA queries.
• Optimizing Queries: Explore BigQuery's optimization features (materialized views, cached results) when dealing with large datasets, to improve performance and reduce costs.

[kolban]

I'll throw in a few cents.  My gut is saying that the question of "how much bytes processed by that table" is likely not going to be found.  Here is my thinking.  Google charges by the execution of queries.  A query is (to all intents) a SQL statement.  Within a SQL statement you can reference one or MORE tables.  This means that there isn't a one-to-one relationship between executing a query and how much data was processed in a SINGLE table.  With BigQuery on-demand billing, you are charged by how much data was scanned for a query ... but I haven't seen any reporting that said "For a given query, the contribution to the total data scanned from a given table was XXX".

For example ... if a query referenced just one table, then the data scanned by that query could be attributed to exactly that table.  However, if the query referenced multiple tables (subqueries or joins), then we wouldn't be able to determine the contribution of any of the individual tables.

If you have queries that ARE only referencing one table, then you could apply labels to those queries.  By applying the labels, you can then retrieve data filtered just for those queries.  This might give you an approximation to the information you are looking for but it will under report ... if you miss adding labels to queries referencing the table or you have queries that reference your table as well as other tables.

[Alex_at_gcp]

For several optimisation analyses, such as partitioning, clustering of a table, this information would be really helpful. It would also be helpful to understand which tables should be considered for performance optimisation, especially in complex queries with more than 10 tables, it is really difficult to find out such information. I submitted an enhancement request for more granular logging information 4 years ago. Unfortunately, it is still not implemented.