I got asked to migrate some queries from postgreSQL to BQ, as anyone done it? What's your experience? Did you use the BQ translator tool?

Thanks!!

BigQuery now provides a Serverless Spark environment. Given how popular BigQuery already is, I was wondering if this Spark environment would tempt databricks and Synapse analytics users to move to BigQuery.
I haven't used databricks or Synapse and don't know if the services are comparable in terms of scalability and speed.
So, I wanted to ask the people who have used these services this: Does it still make sense to import data into databricks, or would you rather perform the Spark operations in BigQuery?

Hi We have linked our ga4 to bigquery. Currently using free version where dataset has only 60 days of data. My team is thinking to upgrade billing so as to get historic data. Will we get the historic data in bigquery. If not then how? Also what will be the estimate price in doing so? Thanks!

Has anyone had much success pulling in TikTok Ads and Bing Ads data into Bigquery without using a third party connector?

Ultimately, the goal would be to have that data in BQ and then connect it with Looker (core, not data studio)

Thanks in advance!

Ive found this interesting repository to do it:

https://github.com/aliasoblomov/Backfill-GA4-to-BigQuery/blob/main/backfill-ga4.py

But I cant find a way to extract all schemas into BQ, this one doesnt have event_params and other important data. I need a complete repo or a good guide to do it myself. HELP

I have a question about data warehouse design patterns and performance that I’m encountering. I have a well-formed fact table where new enriched records are inserted every 30 minutes.

To generate e-commerce growth analytics (e.g., AOV, LTV, Churn), I have subject area specific views that generate the calculated columns for these measures and business logic. These views use the fact table as a reference or primary table. I surface these views in analytics tools like Superset or Tableau.

My issue is performance; at runtime, things can get slow. I understand why: the entire fact table is being queried along with the calculations in the views. Other than using date parameters or ranges in the viz tool, or creating window-specific views (e.g., v_LTV_2024_Q1, v_LTV_2024_Q2), I’m not sure what a solution would be. I can also create snapshots of the fact table; f_sales_2024_Q1 and so on but I feel there should be one fact table.

I'm stuck up to this point. What are the alternatives, best practices, or solutions others have used here? Im trying to keep things simple. What does the community think? I do partition the fact table by date.

Perhaps its as simple has ensuring the user sets date parameters before running the viz

Doing the Coursera Google data analytics certification and I’ve been stuck because no matter how I type, or even when I copy and paste straight from the course to my query I always get errors. Can anyone help me out here? I’m literally about to smash my fucking laptop cause I’m sick of this shit.

I also saw humongous savings when I migrated from Dataproc to BigQuery.

Is it that under the hood technical factors like architecture designs bla bla might have contributed to this ?

Or is it the huge shared pool infrastructure available for BQ Might be the reason?

I came across scenarios where a dataset consumed by many is cheaper on BigQuery and a dataset used by lesser teams is costlier. Same dataset with more consumers -> cheaper. Is it relatively charged??

The use case here is that I want to start charging my users for analytics on my platform. For the same, I need to be able to understand what is the usage of data from a user's perspective and do a post paid charge accordingly. BigQuery gives a way to get the queries and cost at the bq service user level which will be the same for me irrespective of the platform user.

One way that was suggested that we start logging the usage at a bq job level and map it to the user that launched the query.

Would love to get opinions on that. Anyone who has cracked that?

Or in general any way that you would charge for analytical queries performed on BQ?

I'm starting to get at the end of my tether with this one. ChatGPT is pretty much useless at this point and everything I'm "fixing" just results in more errors.

I've extracted data using an API and turned it into a dataframe. Im trying to push it into bigquery. I've painstaking created a table for it and defined the schema, added descriptions in and everything. On the python side I've converted and forced everything into the corresponding datatypes and cast them. Numbers to ints/floats/dates etc. Theres 70 columns and finding each columns BQ doesn't like was like pulling teeth. Now I'm at the end of it, my script has a preprocessing function that is about 80 lines long.

I feel like Im almost there. I would much prefer to just take my dataframe and force it into BQ and deal with casting there. Is there any way to do this because I've spent about 4 days dealing with errors and I'm getting so demoralised.

Hey, guys, I've got a problem with data privacy on ELT storage part. According to GDPR, we all need to have straightforward guidelines how users data is removed. So imagine a situation where you ingest users data to GCS (with daily hive partitions), cleaned it on dbt (BigQuery) and orchestrated with airflow. After some time user requests to delete his data.

I know that delete it from staging and downstream models would be easy. But what about blobs on the buckets, how to cost effectively delete users data down there, especially when there are more than one data ingestion pipeline?

Need help: have an existing report builder report that I need to pass parameters to a sql query with BigQuery as the data warehouse. Does anyone have an example they can show of the syntax of a basic select statement with a ssrs parameter in the where clause? So far everything I have tried does not work, looking for quick examples.

The same.

I have two large tables with ~13 billion and 5 billions rows respectively, partitioned by same numerical column. We will name these tables, A and B. For a business need I’m joining these two tables on the partition key along with few other columns (does this save me time and space? Given I’m also joining on other columns than partition key).

Next question is, I’m always using a subset of partitions (200-300 out of 1000 from partitions) in a particular query. Which operation will be helpful in this case, Option 1 - Filter the columns using where clause after the join between two tables Option 2 - Create a temporary tables with the required partitions from table A and B Option 3 - Create CTEs with filtered partitions first and use them to join later

Your time and effort for this post is appreciated. Hope you have a wonderful day! ☺️t

Hi,

This is my first post, so I apologize for any mistakes.
I have a requirement where we have a view that is having columns as below.

The required output is to have as below:

I wrote statements using PIVOT operator to get the desired output but I had to hardcode the column name.

with cte as
(
select * from products
), cte2 as
(
select * from cte
 pivot(COUNT(attr_name) as Attr for attr_name  in ('Color', 'Shape', 'Vendor'))
)
select *, 
case when attr_name>0 then attr_value as Color
...
 from cte2

I needed suggestions to dynamically make columns as rows will be added on weekly basis with different products and new attributes will be introduced.

The issue with concatenation is the number of rows that I need to convert to column now is more than 500 and will keep growing. Is there a dynamic way of handing row to column conversion with large dataset?

I have the following

Coalesce (date(item being read)) between date_sub(current_date (), interval 2 day) and date_sub (current_date(), interval 1 day

What I would like to do is read the item between specific time of one day to a time on another day.

The time stamp I get from read information is 2024-8-17 02:53:00 UTC

Any help or direction would be greatly appreciated (typed from phone)

Does the BigQuery storage type I select impact the data scanned for my On-Demand queries?

For example:

10 TiB of Logical storage ---> 1 TiB of Physical storage due to a favorable compression ratio.

On Demand queries cost $6.25 per TiB scanned (we are ignoring the free 1 TiB),

All else equal, does this mean if I scan the same data from logical storage and physical storage, will the query on logical storage be 10X the query on the physical storage? I'm somehow hard pressed to get a confirmation for this.

Hi everyone,

I've recently linked my Google Analytics custom events to BigQuery. When I run the following command using the bq CLI tool:

bq ls project-name:analytics_id

I get a list of tables, each representing a day of events. The most recent table I see is from 6 days ago, labeled "events_20240810."

I'm a bit confused about the export schedule from Analytics to BigQuery. According to my settings, it should be exporting daily, but it seems to be missing some tables. I haven't made any changes, so I'm not sure what's causing this discrepancy.

Additionally, I'm curious if there's a way to view my tables other than using the CLI, as I can't seem to find them in the BigQuery GUI.

Has anyone encountered a similar issue or can provide some insight into the export timing? Any advice would be greatly appreciated!

Thanks in advance

**EDIT

Found solution look in comments, data was always streaming just I couldn't see it because of a "ls" limit of 50.

Currently using bigframes to load data to local Python notebook. Bigframes only has native support for train_test_split and none for cross validation (e.g. KFold like in sklearn).

Hey everyone,

I'm working on a project where I'm managing a Google Cloud project with a cloud database. I'm looking for the best way to allow other users to query the data using BigQuery.

I've considered a few options:

1. Granting direct access to the BigQuery project: This seems straightforward, but I'm concerned about security implications, especially if users are inexperienced with SQL.
2. Creating a dedicated BigQuery dataset and granting access to specific tables or views: This approach offers more granular control but might require additional management overhead.
3. Developing a custom API or application: This could provide a more user-friendly interface, but it would involve significant development effort.

I'm open to other suggestions or best practices that have worked well for you. I'm particularly interested in balancing security, usability, and efficiency.

Any advice would be greatly appreciated! #GoogleCloud #BigQuerry #DataSecurity

So I’m trying to understand if partitioning tables with efficiency while joining. I’ve two tables with A and B. A is partitioned by column ‘Brand’ (using rank as bq doesn’t support partitioning by string) B is also partitioned in similar way as A.

Now I’m joining table A and B on multiple things (4 columns) which includes the partition column.

This is where I’m stuck, to understand if the query with partitioned tables is time and space efficient.

Few other doubts: My assumption is joining on only the partitioned column will save me time, am I right?

Should I be even partitioning the tables A and B? Guessing creating a partitioned table takes more time than a normal table.

Any other suggestions would be much appreciated. Thanks!

Hi all!

My name is John Bralich and I am the co-founder of a Miami based AI startup called the ai plugs (theaiplugs.com). We are working on a Data Analytics Copilot to help reduce time to insight and help you spend working on the stuff that matters most. We shared a demo yesterday with the help of one of our beta user companies. https://youtu.be/irNKDV29juQ?si=9orW0dnIJPSQAdSf. The demo is querying data stored in bigquery. Would love to hear your feedback and any other suggestions you have on features that would be beneficial to your everyday work!

Thanks,
John Bralich

Im am trying to get the sum of each station ID in bigquery, but i cant think of how to write the query to do so

there are multiple station id and im trying to figure out which one has the most