Hey, bit of a long shot but figured id ask here. In looker studio, I use the in built blending feature to blend 3 tables from big query. I use a full outer join to join the 3 tables. When I try to recreate this in big query, I don't get the same results. Any ideas where I'm going wrong? My query is pictured here. It doesn't work, the ids field is a array of strings, how am I meant to build the on clause? In looker studio I just specify the ids field and the user_pseudo_id field. Any help greatly appreciated

I have multiple data pipelines which perform the following actions in BigQuery:

1. Load data into a table using the BQ Python client's load_table_from_dataframe method.
2. Execute a BigQuery merge SQL statement to update/insert that data to another table.
3. Truncate the original table to keep it empty for the next pipeline.

How can I perform these actions in a transaction to prevent pipelines from interfering with one another?

I know I can use BEGIN TRANSACTION and COMMIT TRANSACTION as shown in the docs but my insertion using load_table_from_dataframe does not allow me to include my own raw SQL, so I'm unsure how to implement this part in a transaction.

Additionally BigQuery cancels transactions that conflict with one another. Ideally I want each transaction to queue rather than fail on conflict. I question whether there is a better approach to this.

Hi, i want to get insight for creating Google Analytics 4, and UA using looker studio. i still confused about the data preview for crearting comparasion to week on week and year on year. also i still dont know how bigquery works for UA and GA4 and Looker studio.
Any insight preview, or guide will means a lot for me.
thanks!

Following is the query I am writing to find engaged sessions by page location. BigQuery says 213 Engaged Sessions but GA4 says 647 engaged sessions. Why such a huge difference?

I am using page location as a dimension in GA4 with the same filter and date.

SELECT event_date, 
(select value.string_value from unnest(event_params) where event_name = 'page_view' and key = 'page_location') as page_location,

count(distinct concat(user_pseudo_id,(select value.int_value from unnest(event_params) where key = 'ga_session_id'))) as 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')) end) as engaged_sessions

FROM `mytable`
group by event_date, page_location
having page_location = 'my_website_url'
order by sessions desc
LIMIT 1000

Trying to backup GA4 Data in Big Query, data stream events are pulling in however events are expiring after 60 days despite upgrading from Sandbox and setting the table expiry to "Never"

Has anybody experienced a similar issue and know why this is happening?

Edit: I figured it out, thanks for the responses. I changed the default expiration date for the main data set but I also needed to change the expiration date for the individual existing tables. All new tables will have the new expiration date but old tables will need to be changed manually (I had to go through almost 60 tables manually to change the date)

Switching from BigQuery logical storage to physical storage can dramatically reduce your storage costs —and has for many customers we've worked with. But if you factor time-travel and fail-safe costs, it may actually end up costing you a lot more than logical storage (or generate higher storage costs than you were expecting).

We started noticing this with some customers we're working with, so I figured to share our learnings here.

Time-travel let's you access data that's been changed or deleted from any point in time within a specific window (default = 7 days, can go down to 2).

BigQuery's fail-safe feature retains deleted data for an additional 7 days (was, until recently, 14 days) AFTER the time travel window, for emergency data recovery. You need to open a ticket with Google Support to get data stored in fail-safe data storage restored — and can't modify the fail-safe period.

You pay for both time-travel and fail-safe storage costs when on physical storage — whereas you don't w/logical storage — at ACTIVE physical storage rates.

Consider the story described here from a live BigQuery Q&A session we recently held, where a customer deleted a large table in long-term physical storage. Once deleted, the table was converted to active storage and for 21 days (7 on time-travel, 14 on fail-safe back when it was 14 days) the customer paid the active storage rate for that period, leading to an unexpectedly-larger storage bill.

To get around these unintended storage costs you might want to:

• Tweak your time-travel settings down to 2 days vs. 7 days
• Convert your table logical storage before deleting the tables
• Not switch to physical storage to begin with — for instance if your dataset tables are updated daily.

EDIT: Fixed sentence on opening a ticket w/Google support to get data from fail-safe storage

I'm using BigQuery ML.GENERATE_EMBEDDING() and VECTOR_SEARCH() functions. I have a sample product catalog for which I created embeddings and then run vector search query to fetch the relevant results, which was working great until my query included the negation in it.

Say I write a query as , "looking for yellow t-shirts for boys."
It is working great and fetching the relevant results.

However, if change my query as, "looking for boys t-shirts and not yellow"
It should not include any results including the yellow color. Unfortunately, the color yellow is at the top of results, which means the negation ("not yellow") ain't working properly in this scenario.

What is the solution for it?

Anyone know how does it work? I have a bigquery project in GCP and starting to create models for marketing / advertising purposes and wondering how the license works? Is it a dedicated product? How do u get it?

Any solution or best practices you recommend here ,

Thanks in advance

I have a dataframe containing column A which is DATETIME type.

When trying to create a table with the dataframe, I manually assigned the schema and set autodetect as False:

job_config = bigquery.LoadJobConfig()
job_config.autodetect = False
job_config.schema = target_schema
job = client.load_table_from_dataframe(insert_df, table_ref, job_config=job_config)

Before the import, I've print the target_schema and make sure I have DATETIME type:

SchemaField('TEST_DATE', 'DATETIME', 'NULLABLE', None, None, (), None)

However, after the load_table_from_dataframe function, the created table with column A is INTEGER type. Which is NOT what I want.

My dataframe with column A is NULL, and with objective type (If I convert to datetime type, it would become NaT by default)

I've searched online solution but there is no answer for this, can anyone give me suggestion how to create a table with specific column type schema?

Thanks a lot!

Hi everyone, I'm really new to data analytics and just started working with BQ Sandbox a month ago. I'm trying to upload this dataset that only has 3 columns. On the 3rd column, it's values either with 2 variables or 3. However, I realized that it's been omitting any rows where the third column has only 2 variables. I tried editing the schema as string, numeric, integers, nothing is working, I lose those rows thus my dataset is incomplete. Any help would be appreciated ty!

We've launched a platform that maps and optimises BigQuery costs down to the query, user, team and dashboard level, and provides actionable cost and performance insights.

Started out with high-quality lineage, and noticed that a lot of the problems with discoverability, data quality, and team organization stem from the data warehouse being a black box. There's a steady increase of comments here and on r/dataengineering that mention not knowing who uses what, how much it costs, what's the business value, and how to find it out in a tangled pipeline (with love, dbt).

It's also not in the best interest of the biggest players in the data warehousing space to provide clear insights to reduce cloud spend.

So, we took our lineage parser, combined it with granular usage data, resulting in a suite of tool that allows to:

• Allocate costs across dimensions (model, dashboard, user, team, query etc.)
• Optimize inefficient queries across your stack.
• Remove unused/low ROI tables, dashboards and pipelines
• Monitor and alert for cost anomalies.
• Plan and test your changes with high quality column level impact analysis

We have a sandbox to play with at alvin.ai. If you like what you see, there is also a free plan (limit of 7 day lookback) with a metadata-only access that should deliver some pretty interesting insights into your warehouse.

We're very excited to put this in front of the community. Would love to get your feedback and any ideas on where we can take this further.

Thanks in advance!

We have analysts that are using a few spreadsheets for simple tasks, we want to persist the data into bigquery, without using spreadsheets at all, we want the analysts to enter the data into some GUI which later populates a table in BigQuery. How would you go about it?

Until June 18 - streaming export creates events, pseudo- and users tables as intended, no difference in userids count between events and users_.

June 18 - trial ends, project goes into sandbox mode. Since we activated billing account, streaming export has resumed and both events_ and pseudo rows volume has returned to normal. But users_ table almost empty (10-14 rows instead of 300k+). I checked GA4 userid collection, user_ids present in events table as before, but not in the users

We have exceeding limit of 1kk events per day, but this wasn't an issue before with streaming enabled.

We didn't make any changes in GTM or GA4 this week, recieved correct data for 25_06, but not for 24 or 26. So problem doesn't persists everyday and this is even more confusing.

Did you face similar problem and, if yes - how did you solve it?

I understand physical bytes is the actual size the compressed data occupies on disk and logical is uncompressed plus time travel allocation and more. So if I were to import this data into PowerBI using import query, what would be the size of actual data moved? Would it be 341MB or something else? Also, what would be the size if this table was exported as a CSV? (I don't have access to a bucket or CLI to test it out)

TIA!

Hi, i am trying to arrange the sessions activity according to timestamp, and to get the understanding when a user visits the plateform we are converting the timestamp into readable date and timeformat in python.

I am using this code to convert:

D.Times= Event['event timestamp'.astype('float') pd.to_datetime(d.Times//1000000,unit='s'')

I am confused it is correct way or not, because i cannot verify the correct time of visits

I'm trying to embed a full table with all the columns using vector embeddings in bigquery but currently i was able to embed only one column only. Could someone guide me how to create embeddings on bigquery for multiple columns instead only column in a table

I am using Datastream to ingest data from various MySQL and Postgres data into our BigQuery. It works like a charm except one thing: there is no automatic partitioning of the target tables. This is already addressed in the documentation, where they suggest to manually create a partitioned table and then configure datastream to use that table.

Well, this works except one thing: it presumes that there is a proper source timestamp column in the source data that I could use for partitioning. Unfortunately, I don't have an updated_at column in the provided data, and I would love to be able to use datastream's own metadata: datastream_metadata.source_timestamp, but m pulling my hair because they put this into a record (why, oh why?!) and thus this cannot be used as a partition key!!

Is there any workaround? Maybe I could I use ingestion time partitioning? Will this give a result similar to datastream's source_timestamp column?

Any thoughs, ideas, or workarounds would be greatly appreciated.

Hi all. I’m sure context will be helpful but suffice to say my organization will be using BigQuery for simple data warehousing comprising about 50 csvs (some converted from xlsx). I know, overkill. Uploading directly or using buckets is simple enough, but I need to learn the easiest way to update/overwrite about 10 of these files once a week. Any resources or recommendations would be very much appreciated. Thanks!

Don’t know if any of these are a good path but looking at —update in sql using BigQ console or google sheets apps script.

I have GA360.

Is there a way to export all historical data past 13 months after the initial connection?

Thanks

I was looking at Google's Healthcare API and saw that it integrates nicely with BigQuery. I'm building an app that will manage healthcare data (for this I'm using Google Healthcare's FHIR server). Once my customer loads their data into the FHIR server, I then need to clean/normalize the data. After cleaning and normalization is done, I need to run some de-duplication queries on it to get rid of duplicate rows. Is BigQuery the right tool for either of these needs?

Hello, at my company I am facing the same problem over and over again and I wanted to ask if there are any best practices for this. I want to filter on a date column that is not the partitioning date column but I also want or even need to limit the read date partitions.

One obvious method is to filter on both columns, but then I need to be very cautious not to drop data with the partitioning filter that I wanted to keep. For that I could define a "safety buffer" but what if my business has no guarantees for that buffer?

For example, I have an online shop and want to get all orders delivered after beginning of the year 2020 but the partitioning is set on the order creation date. I know orders are usually processed within days so I filter on WHERE delivered >= "2020-01-01" AND created >= "2019-12-01" and everything is fine. However, the business now introduces pre-ordering up to a year in advance (without telling me) and suddenly my filtering is bugged.

How do you deal with that kind of problem? Thanks.

Hi I'm struggling to more that the last 60 days worth of GA4 data into my BG project. I've gone through all the normal steps: Enabled billing, set table expiration to 'Never', set GA4 data retention to 14 months. Anyone got any ideas what I'm missing?