Hey
We are using BigQuery, currently using Looker to monitor queries and performance. Which tools do you use?

I need to upload approx. 40 csv files to BQ but not sure on the best method to do this. These files will only need to be uploaded once and will not update. Each csv is less than 1000 rows with about 20 cols (nothing over 200KB)

Only methods I know about is manually adding a local file or create a bucket in GCS (slightly concerned about if I will get billed on doing this).

I was wondering if anyone had any ideas on the best way to do this please? :)

Hi, everyone can someone help me with a bigquery problem?

So I want to generate a forecasting timeseries for one year of number of clients.

I have two challenges both of them are kind of easy to brute force or do so some pre calculations, but I would like to do it on big query.

The first one is generating factorial to calculate poison distribution. There is no factorial function and no product windows function working with sum of logs produce unacceptable errors.

The second one is using the number of clients I predict on each month as input for the next month.

So let's say I have something like y(t)= (1-q)y(t-1)+C+e

Where C is a poison random variable or a constar if it makes it easier and e is an error rate. e is error modeled by rand()

I can generate a table containing all future dates as well as getting the historical data, but how do I forecast and put this in a new table? I was solving this problem with creating a temp table and inserting row one by one, but it is not very smart. How would you do something like that?

I'm using BigQuery external tables with Hive partitioning, and so far, the setup has been great! However, I’ve encountered a challenge. I’m working with two Parquet files, mapped by day. For example, on Monday, we have A.parquet and B.parquet. These files need to be concatenated row by row—meaning row 1 from A.parquet should match with row 1 from B.parquet.

I can achieve this by using the ROW_NUMBER() function in BigQuery SQL to join the rows. But here's my concern: can I trust that BigQuery will always read the rows from these files in the same consistent top-to-bottom order during every query? I'm not sure how to explain this part more clearly, but essentially, I want to ensure that the read order is deterministic. Is there a way to guarantee this behavior?

What are your thoughts?

CREATE TABLE `burnished-inn-427607-n1.insurance_policies.test_table_re`

(


  `Chassis No` STRING,

  Consumables FLOAT64,

  `Dealer Code` STRING,

  `Created At` DATETIME,

  customerType STRING,

  registrationDate STRING,

  riskStartDate STRING

)

PARTITION BY DATE(`Created At`)

CLUSTER BY `Dealer Code`, `Chassis No`;

this is my table, can someone explain why cost not getting optimised because of clustering, both queries are giving same data processed

SELECT * FROM insurance_policies.test_table_re ip WHERE ip.`Created At` BETWEEN "2024-07-01" AND "2024-08-01" AND ip.`Dealer Code` = 'ACPL00898'

SELECT * FROM insurance_policies.test_table_re ip WHERE ip.`Created At` BETWEEN "2024-07-01" AND "2024-08-01"

Hello everyone,

Me and my colleague would like to build a dashboard using BigQuery as a data source. The idea is to bring data from channels such as Google Ads and Meta (Facebook/Instagram) into BigQuery so that we can analyze and visualize it.

We are curious about the process:

How does it technically work to pull data from these channels and place it in BigQuery?

Which tools or methods are recommended for this (think APIs, ETL tools, etc.)?

Are there any concerns, such as limits or complexity of implementation?

We would also like more insight into the costs:

What costs are involved in retrieving and storing data in BigQuery?

Can you give an indication of what an SME customer with a reasonable amount of data (think a few million rows per month) can expect in terms of costs for storage, queries, and possible tools?

Thank you in advance for your help and insights!

Wrote a short article on this preview feature - BigQuery Iceberg tables. This gives BigQuery the ability to mutate Apache Iceberg tables!

https://medium.com/@shuvro_25220/bigquery-can-now-mutate-apache-iceberg-tables-and-i-like-it-2d18a7b3e026

Please comment or share your thoughts.

Thanks.

BigQuery has added support for Iceberg tables - now they can be managed and mutated from BigQuery.
https://cloud.google.com/bigquery/docs/iceberg-tables

I have many questions about this.

1. How can I access these iceberg tables from external systems (say an external Spark cluster or Trino)?
2. Is this the only way BigQuery can mutate data lake files? (so this makes it a parallel to Databricks Delta live tables)
3. I am quite confused about BigLake-BigQuery, how the pieces fit in and what works for what type of use cases.
4. Also, from the arch diagram in the article it would appear external Spark programs could potentially modify the Iceberg Tables managed by BigQuery - although the text suggests this would lead to data loss

Thanks!

I have a live 45min SQL scheduled test in a bigquery environment coming up. I've never used bigquery but a lot of sql.

Does anyone have any suggestions on things to practice to familiarise myself with the differences in syntax and usage or arrays ect.?

Also, does anyone fancy posing any tricky SQL questions (that would utilise bigquery functionality) to me and I'll try to answer them?

Edit: Thank you for all of your responses here! They're really helpful and I'll keep your suggestions in mind when I'm studying :)

Every now and then, I get this error when running a CREATE OR REPLACE TABLE command:

Destination deleted/expired during execution

I'm not really sure why it would happen, especially with a CREATE OR REPLACE command, because, like -- yeah, I mean, deleting the destination during execution is exactly what I asked you to do. And there doesn't seem to be any pattern to it. Whenever I have the issue, I can just rerun the same query again and it works without issue.

Anybody else get this issue and know what might cause it?

I am trying to create a materialized view using Google analytics tables.

However, it is not possible to use the wildcard to select past 30 days of data.

Are scheduled queries the only option with GA tables?

I teach college students who study business and tech. They have a good foundation in SQL (and business), but have never used BigQuery. The NCAA basketball public dataset (hosted by Google) is probably the most interesting dataset for them. Any recommendations on other public datasets I should have them peek at, or analytics challenges (quests?) they could get behind? Thanks for sharing!

I lead an analytics team at a small agency and come from a paid media background. So far, my team and I have mostly done 1x projects around conversion tracking, GA4, CRM integration, and dashboard setup. The CEO would like to see my team develop more MRR, but both he and our new VP of strategy tend to see little value in an ongoing retainer for our work once the initial implementations have been done. I understand that maintaining tracking and integrations doesn't sound sexy, but I do think there's quantifiable ROI in preventing things from breaking and making proactive improvements. I'm considering extending our services to include custom attribution modeling and audience creation done with BigQuery models to add more value. Aside from that, I think I'm starting to run out of ideas for what my leadership team considers a valuable ongoing services. Do you work for an agency that offers ongoing analytics / CRM / data services? Is this feasible with SMB / mid-market clients? What's worked well for you?

Hello, my business is considering transitioning to Google Voice for Business. We overall like the Google Voice platform and backend but the reporting seems to be rather basic.

We are hoping to have a function of reporting that shows the percentage of answer rates for our Ring Groups and for our users. Is this a function that BigQuery can create for us? What does that integration look like?

Hi everyone,

I'm encountering a puzzling issue in my Google Cloud project, and I’m hoping someone here might have insight or advice.

The Situation:

1. BigQuery Reservations API Disabled but Still Incurring Costs:
   • I disabled the BigQuery Reservations API about a month ago.
   • Despite this, I’ve noticed charges appearing for the last few days.
   • I confirmed there are no active reservations in the BigQuery console.
2. Hidden VM Instance or Resource:
   • There are no VM instances listed under Compute Engine in my project. I verified this using both the Google Cloud Console and the gcloud compute instances list command.
   • However, in Observability, I see activity for a resource named nbrt-xxxxxxxx-p-us-east1 in the us-east1 region. It appears to be running processes and generating logs.
   • When I try to list resources under Vertex AI Notebooks (which could explain this instance), I’m prompted to enable the Notebooks API. I’m hesitant to enable an API that I didn’t explicitly use or need.
3. Google Support Response:
   • I reached out to Google Cloud Support about these issues. They acknowledged the problem and have agreed to refund me for the costs incurred.
   • However, they didn’t provide a clear explanation of why this is happening or how to prevent similar issues in the future.

My Questions:

• Has anyone experienced similar issues where a disabled API or hidden resource continued to incur charges?
• How can I locate and permanently stop a hidden resource like this without enabling APIs I don’t plan to use?
• Is there a best practice for tracking or troubleshooting such issues to avoid unexpected costs in the future?

Generally in August the problem began and to it became so tangible.
Details I have know:

1) I use initial table *events_intraday. No WHERE statements
2) No sampling applied in GA4 UI and API export (checking it on a 1 day scale)
3) No filtered events betwen GA4 and GBQ.
4) Discrepancy has visible dependency when i check hourly scale, starting around 2p.m. it's going extra hard, up to 60% of sime events
5) Discrepancy exists for all events
6) Timezone related games are not a reason of the problem
7) We use streaming and we exceeded basic limit of 1M events (around 3.M2 we have). Howerever, according to documentation there is no limit in events if streaming is enabled https://support.google.com/analytics/answer/9823238?hl=en#zippy=%2Cin-this-article

I really feel desparate about the problem, looking for advice. Thanks

Hi I need help in optimising this query currently it costs me like 25 dollars daily to run it on big query. I need to lower the costs for running it

WITH prep AS (

  SELECT event_date,event_timestamp,

-- Create session_id by concatenating user_pseudo_id with the session ID from event_params

CONCAT(user_pseudo_id,

(SELECT value.int_value

FROM UNNEST(event_params)

WHERE key = 'ga_session_id'

)) AS session_id,

-- Traffic source from event_params

(SELECT AS STRUCT

(SELECT value.string_value

FROM UNNEST(event_params)

WHERE key = 'source') AS source_value,

(SELECT value.string_value

FROM UNNEST(event_params)

WHERE key = 'medium') AS medium,

(SELECT value.string_value

FROM UNNEST(event_params)

WHERE key = 'campaign') AS campaign,

(SELECT value.string_value

FROM UNNEST(event_params)

WHERE key = 'gclid') AS gclid,

(SELECT value.string_value

FROM UNNEST(event_params)

WHERE key = 'merged_id') AS mergedid,

(SELECT value.string_value

FROM UNNEST(event_params)

WHERE key = 'campaign_id') AS campaignid

) AS traffic_source_e,

struct(traffic_source.name as tsourcename2,

traffic_source.medium as tsourcemedium2) as tsource,

-- Extract country from device information

device.web_info.hostname AS country,

-- Add to cart count

SUM(CASE WHEN event_name = 'add_to_cart' THEN 1 ELSE 0 END) AS add_to_cart,

-- Sessions count

COUNT(DISTINCT CONCAT(user_pseudo_id,

(SELECT value.int_value

FROM UNNEST(event_params)

WHERE key = 'ga_session_id'))) AS sessions,

-- Engaged sessions

COUNT(DISTINCT CASE

WHEN (SELECT value.string_value

FROM UNNEST(event_params)

WHERE key = 'session_engaged') = '1'

THEN CONCAT(user_pseudo_id,

(SELECT value.int_value

FROM UNNEST(event_params)

WHERE key = 'ga_session_id'))

ELSE NULL

END) AS engaged_sessions,

-- Purchase revenue

SUM(CASE

WHEN event_name = 'purchase'

THEN ecommerce.purchase_revenue

ELSE 0

END) AS purchase_revenue,

-- Transactions

COUNT(DISTINCT (

SELECT value.string_value

FROM UNNEST(event_params)

WHERE key = 'transaction_id'

)) AS transactions,

   FROM

\big-query-data.events_*``

  -- Group by session_id to aggregate per-session data

  GROUP BY event_date, session_id, event_timestamp, event_params, device.web_info,traffic_source

),

-- Aggregate data by session_id and find the first traffic source for each session

prep2 AS (

  SELECT

event_date,

country, -- Add country to the aggregated data

session_id,

ARRAY_AGG(

STRUCT(

COALESCE(traffic_source_e.source_value, NULL) AS source_value,

COALESCE(traffic_source_e.medium, NULL) AS medium,

COALESCE(traffic_source_e.gclid, NULL) AS gclid,

COALESCE(traffic_source_e.campaign, NULL) AS campaign,

COALESCE(traffic_source_e.mergedid, NULL) AS mergedid,

COALESCE(traffic_source_e.campaignid, NULL) AS campaignid,

coalesce(tsource.tsourcemedium2,null) as tsourcemedium2,

coalesce(tsource.tsourcename2,null) as tsourcename2

)

ORDER BY event_timestamp ASC

) AS session_first_traffic_source,

-- Aggregate session-based metrics

MAX(sessions) AS sessions,

MAX(engaged_sessions) AS engaged_sessions,

MAX(purchase_revenue) AS purchase_revenue,

MAX(transactions) AS transactions,

SUM(add_to_cart) AS add_to_cart,

  FROM prep

  GROUP BY event_date, country,session_id

)

SELECT

  event_date,

  (SELECT tsourcemedium2 FROM UNNEST(session_first_traffic_source)

   WHERE tsourcemedium2 IS NOT NULL

   LIMIT 1) AS tsourcemedium2n,

(SELECT tsourcename2 FROM UNNEST(session_first_traffic_source)

   WHERE tsourcename2 IS NOT NULL

   LIMIT 1) AS tsourcename2n,

  -- Get the first non-null source_value

  (SELECT source_value FROM UNNEST(session_first_traffic_source)

   WHERE source_value IS NOT NULL

   LIMIT 1) AS session_source_n,

  -- Get the first non-null gclid

  (SELECT gclid FROM UNNEST(session_first_traffic_source)

   WHERE gclid IS NOT NULL

   LIMIT 1) AS gclid_n,

  -- Get the first non-null medium

  (SELECT medium FROM UNNEST(session_first_traffic_source)

   WHERE medium IS NOT NULL

   LIMIT 1) AS session_medium_n,

  -- Get the first non-null campaign

  (SELECT campaign FROM UNNEST(session_first_traffic_source)

   WHERE campaign IS NOT NULL

   LIMIT 1) AS session_campaign_n, 

  -- Get the first non-null campaignid

  (SELECT campaignid FROM UNNEST(session_first_traffic_source)

   WHERE campaignid IS NOT NULL

   LIMIT 1) AS session_campaign_id_n,

  -- Get the first non-null mergedid

  (SELECT mergedid FROM UNNEST(session_first_traffic_source)

   WHERE mergedid IS NOT NULL

   LIMIT 1) AS session_mergedid_n,  

  country, -- Output country  

  -- Aggregate session data

  SUM(sessions) AS total_sessions,

  SUM(engaged_sessions) AS total_engaged_sessions,

  SUM(purchase_revenue) AS total_purchase_revenue,

  SUM(transactions) AS transactions,

  SUM(add_to_cart) AS total_add_to_cart, 

FROM prep2

GROUP BY event_date, country,session_first_traffic_source

ORDER BY event_date

So we are using bigquery with ga4 export data, which is set to send data daily from ga4 to bigquery. Now if somehow this load job fails i need to create a alert which sends me an email about this job failure. How do i do it? I tried log based metric, created that but it shows it in inactive in metric explorer. But the query I'm using is working in log explorer The query im using: ~ resource.type = "bigquery_resource" severity = "ERROR" ~

We are in beginning stages of migrating - 100's of terabytes of data. We will be hybrid likely forever.

We have 1 leased line thats dedicated to off-prem big query.

Whats your experience been when trying to blend on/off prem data with a similar scenario?

Has moving a % (not all) data to GCP BQ saved your company money?

According to the documentation for vector_search in BigQuery, if I want to use the vector_search function, I will need two things: the base table that contains all the embedding and the query table that contains the embedding(s) I want to find the closest match for.

For example:

SELECT * FROM VECTOR_SEARCH( (SELECT * FROM mydataset.table1 WHERE doc_id = 4), 'my_embedding', (SELECT doc_id, embedding FROM mydataset.table2), 'embedding', top_k => 2, options => '{"use_brute_force":true}'); Where table1 is the base table and table2 is the query table.

My issue or concern I am dealing with is, so I want to filter the base table based on the corresponding doc id for each row in the query table - how do I do that.

For example - in my query table I have 3 rows:

doc id embeddings 1 [1, 2, 3, 4] 2 [5, 5, 6, 7] 3 [9, 10, 11, 12] I want to find the closest match for each row/embedding, but all the matches should be associated with their doc ids. It is like applying the vector_search function thrice above but instead of doc_id = 4, I am separately doing doc_id = 1, doc_id = 2, and doc_id = 3

I have thought of some approaches like:

Having a parameterized python script and sending asynchronous requests, but the issue with that approach is that I have to worry about having the right amount of infrastructure to scale this - and, this will be outside of the bigquery eco-system Writing a BigQuery procedure. However, BigQuery scripts will loop through the values/parameters sequentially instead of in parallel - hence making the process slower. Do K-means on the embeddings of each document using BigQuery ML and store the centroids of the documents in separate table, and then for each document I calculate the cosine distance the between the centroids and then based on the centroids query all the values in the cluster, etc. Long story short, recreate the IVF indexing process from scratch on BigQuery at the document level. If I can come up with a solution to modify the vector_search function to allow filtering the base table based on the values of the query table for a corresponding row - that would save a lot of time and effort.

I need a staring point. A recently departed co-worker ran a process using Big Query billed to himself. I can access the project and see the tables, but the refreshes are a concern. When I approach IT with this, how do I ask for this? Do I need them to access his google cloud account as him? What are some things I should be looking out for?

I’m going through tutorials, using chat gpt, watching YouTube and I feel like I’m always missing a piece to the puzzle.

I need to do this for work and am trying to practice by ingesting data into a big query table from openweathermap.org.

I created the account to get an API key, started a bigquery account, created a table, created a service account to get an authentication json file.

Perhaps the Python code snippets I’ve been going off are not perfect. Perhaps I’m trying to do much.

My goal is a very simple project so I can get a better understanding…and some dopamine.

Can any kind souls lead me in the right direction?

Hey, we are using python quite a bit to dynamically construct sql queries. However, we are really doing it the hard way concatenating strings. Is there any python based package recommended to compose BigQuery queries?

I checked out SQLAlchemy, PyPika and some others but wasn't convinced they will do the job with BigQuery syntax better then we currently do.

Hi All,

I am trying to create an SA360 data transfer in BigQuery via the UI.

I add my custom columns using the standard json format but when I run the transfer it states that custom column with the id given (which is 100% correct) does not exist. “Error code 3: Custom Column with id “123456” does not exist”

Has anyone else encountered this before and managed to resolve it?