26,231
questions
-1
votes
0
answers
15
views
Error "updates.map is not a function" when modifying cells of tables retrieved from BigQuery via an API
Here's the revised version of your question with the HTML structure and server details included:
Title:
Error "updates.map is not a function" when modifying cells of tables retrieved from ...
0
votes
0
answers
8
views
Issues when saving excel to CSV and then transferring to BigQuery
My name is Ian and I'm having an issue with while trying to upload a CSV file into BigQuery. All of my files have the same custom date format as seen below
(https://i.sstatic.net/Ba4kMjzu.png)
When I ...
- 1
-1
votes
2
answers
49
views
EXCEPT showing additional fields
Let's say I have two tables (from uploaded csv files) and I want to do a diff based on an id+territory in the new file that wasn't in the old file. The easiest way to do this is something like:
SELECT ...
- 109k
0
votes
0
answers
10
views
BigQuery won't recognize paired double quotes in CSV
I have a .CSV file I am pulling data from in Google Cloud Storage into a BigQuery external table with the following text: ASR-184,05/15/2023,""Occurence"" is spelled incorrectly on ...
- 13
0
votes
1
answer
25
views
How can I add a calendar as a column/field to a view table?
I need to add another calendar to the current view table. The table already includes a column for month and year, but some months have no transactions, so they don't show up in the table. I want to ...
- 5
0
votes
0
answers
16
views
BigQuery system variables - values for current statement in a multi-statement job
My BigQuery procedural SQL script contains a loop, where each iteration deletes some records from a table. I want to save some stats for each iteration of the loop by inserting system-variable values ...
- 13
1
vote
1
answer
29
views
Bigquery: Load Dynamically Named CSV File from GCS
In Bigquery, I'm trying to schedule a daily query (which has LOAD DATA statement in it) job to load daily .csv file from GCS bucket. File name starts with a random hash and ends with current date, ...
- 767
0
votes
1
answer
28
views
Resources exceeded during query execution while querying big query with order by
I have a big query table with billions of data and I need to query results based on day level. In a day there will be millions of records flowing into the table. And I need to retrieve data on a daily ...
- 495
0
votes
0
answers
31
views
How to create a dynamic "que" of calculations in a query
Assume I have this table
event
amount
time
earned
100
1:00
spent
50
2:00
earned
50
3:00
spent
50
4:00
spent
50
5:00
I want to calculate when the transaction is break even (spent >= earned)
...
- 19
0
votes
1
answer
17
views
Replace Null in Big Query
“How to replace zero values with null in BigQuery for weather data analysis?”
Missing values were incorrectly entered as zeroes, and I need to change them to null values. The code I used was Update (...
0
votes
0
answers
11
views
Pipe delimiter "|" within a string is read with escaping backslash as "\|" in Google Big Query
I have the issue when I write salesforce sales cloud data with Tableau Prep (on Tableau Cloud) into the Big Query, a string that contains a pipe delimiter is stored with an additional escaping ...
1
vote
1
answer
28
views
Spark on Dataproc: Slow Data Insertion into BigQuery for Large Datasets (~30M Records)
I have a Scala Spark job running on Google Cloud Dataproc that sources and writes data to Google BigQuery (BQ) tables. The code works fine for smaller datasets, but when processing larger volumes (e.g....
- 453
1
vote
1
answer
28
views
Big query table partitioning based on time
I’m new to big query and was working on creating python function that fetches table partitioning of a table .
If bqtable.time_partioning:
Print(table.time_partioning.field)
Elif bqtable.partioning-...
-1
votes
2
answers
11
views
what is the best way to test that a table is not empty in dataform bigquery assertion?
I have a dataform assertion and I want to test that a step returns any data before allowing the next more expensive step to initiate.
What is the most efficient way to write the assertion that must ...
- 349
1
vote
3
answers
33
views
Import CSV Bigquery with only 1 header row
I'm importing CSV files from Google Storage into BigQuery using this code:
LOAD DATA OVERWRITE [DBNAME].[TABLENAME]
FROM FILES (
format = 'CSV',
uris = ['gs://xxxx/yyyy/file.csv'],
...
- 59
-1
votes
0
answers
37
views
How can I extract JSON key-value pairs from documents where each document has different key-value pairs using BigQuery?
So, I am trying to convert those key-value pairs into columns and rows, where if a key exists in multiple documents, its values will be stored in that column, corresponding to their respective rows. ...
0
votes
1
answer
24
views
How do I troubleshoot when a schema has the same name as a query?
How do I troubleshoot when a schema has the same name as a query?
For example:
SELECT
date -- This keeps wanting to become the DATE function, even though there is a column called "date"
...
0
votes
1
answer
189
views
Is there any way In Apache beam Java to upsert (Update + Insert) the rows in BigQuery tables
Is there any way in apache beam java to update the rows in BigQuery table ? My use case is I run my dataflow job once in a day and it takes data from one BQ table and after transforming, it writes to ...
- 19
0
votes
0
answers
20
views
Error on Liquibase Authentication Attempt to BigQuery via GitHub Actions
Along with a small change log file, I have the following workflow YAML file in a GitHub repo:
name: Liquibase Update
on:
push:
branches:
- deploy-to-bq-dev
jobs:
test-liquibase-action:
...
0
votes
1
answer
39
views
Pyspark performance problems when writing to table in Bigquery
I am new to the world of PySpark, I am experiencing serious performance problems when writing data from a dataframe to a table in Bigquery. I have tried everything I have read, recommendations, using ...
- 43
1
vote
1
answer
89
views
Get a min of maxes over a moving window in SQ
I'm trying to compute when a charge event will next occur in an event table at any given point in time. Specifically, when a new charge date is entered for a given group, I want to ignore all prior ...
- 11
0
votes
0
answers
35
views
Seeking Advice on Migrating from RDS Postgres to a Scalable Data Warehouse Solution (Redshift, BigQuery, Snowflake) for Real-Time Analytics [closed]
We are currently using RDS Postgres as a datawarehouse (have been using it for a good couple of years), but recently we started noticing performance spikes and queries are running much slower.
Our use ...
- 41
-4
votes
1
answer
30
views
How can I replace numerical values with NULL in SQL? [closed]
i am struggling with getting the correct output from your query
You’re a junior data analyst working for a local news station and your team uses BigQuery to analyze weather trends. The station’s ...
0
votes
2
answers
50
views
Using window functions to filter by a date range
I have a table with only two columns: Date, Customer.
The table records sales, so for the same day there may be duplicate customers (they can make more than one purchase).
I want to calculate a table ...
1
vote
1
answer
54
views
Erroneous pandas rolling results with time window in grouped by dataframe imported from BigQuery
I would like to preface this by apologizing for the lack of reproducibility of my question because if I convert my dataframe to a dictionary and turn that into a dataframe again I am not getting the ...
- 423
0
votes
1
answer
17
views
A BigQuery Python notebook runs fine manually but fails when scheduled
Problem
I've written a Python notebook in BigQuery, that I've run many times manually without errors, but I've tried for days to run it on a schedule and each run gets a Failed status with this ...
- 126
1
vote
1
answer
21
views
How to backup a complete dataset in JSON format on Bigquery, using a scheduled query?
I would like to do a backup of some of my datasets in JSON format with a single row per table. I managed to get to the part where I convert each table into a single row, with all the data stored as a ...
- 7,223
0
votes
1
answer
48
views
Transfer data from GCP to Azure SQL
I am having a huge dataset and I am looking to transfer data from Big query table to Azure SQL.
I designed a java code to fetch data from Big query and persist it in SQL but I am facing out of memory ...
- 495
-1
votes
0
answers
28
views
The query is showing an error even though it's exactly the same as being taught in the Google Data Analytics Course [closed]
Here is the query I wrote :
SELECT
usertype,
Concat (start_station_name, "to", end_station_name) As route,
Count(*) As num_trips
Round (AVG(Cast(tripduration As INT64)/60), 2) AS duration
...
-1
votes
0
answers
33
views
Efficiently mapping big query TableResult to Java object
I have millions of data residing in my BigQuery table and I need to retrieve those data and map them to Java Object.
Right now I am using the below approach
TableResult result = queryJob....
- 495
0
votes
0
answers
22
views
Data migration from Google BigQuery to Yandex Cloud | FileNotFoundError: [WinError 2] | gsutil_path | gsutil
I'm working on the data migrattion from Google BigQuery to Yandex Cloud.
I found the next page with a guide:
https://yandex.cloud/ru/docs/managed-clickhouse/tutorials/bigquery-to-clickhouse#migrate-...
- 17
0
votes
1
answer
22
views
Use the "as" keyword in nested fields in Bigquery
i am querying BigQuery and some of my tables have records aka nested fields.
Now, since i am using Dataform to perform some transformations, i want to rename some of those fields, for instance i want ...
- 3
0
votes
0
answers
41
views
Syntax Error on ORDER BY command in Bigquery Dataset [closed]
In my BigQuery sandbox while practicing sorting on public dataset movie_data I am getting syntax error on this code:
SELECT *
FROM `avid-phoenix-428716-n1.movie_data.movies`
ORDER BY Release Date;
...
0
votes
0
answers
36
views
Is it possible to call a cloud function from google bigquery user defined function?
I have a simple HTTP trigger gen2 cloud function to return a string value when triggered, like for instance "Hello <name>". It works when I tested it from google cloud SDK shell. When ...
- 1
0
votes
0
answers
47
views
How can I measure slot contention severity in BigQuery?
We have a high volume of BQ queries running in many GCP projects using slots across 8 reservations. I want a metric to understand how impactful slot contention is/how much it's slowing down queries ...
0
votes
0
answers
24
views
Match a nested JSON field value in Big query GCP
I have a nested JSON payload which contains multiple field value pairs and I would like to put a condition to match and unmatch some values.
For example below is my sample payload -
responseContent: &...
- 335
0
votes
1
answer
50
views
Extract a string value from a JSON field in GCP log analytics
I am working on building some stats using log analytics in GCP where I am writing a SQL query to extract a value from a JSON field.
I tried few queries but is always returns null. I think I am doing ...
- 335
0
votes
0
answers
11
views
Is there a way to use Logs Explorer to retrieve the configuration settings of a deleted data transfer?
About a week ago, I accidentally deleted the wrong data transfer and I'm trying to see how to recreate it. I realize that deleting a data transfer can't be undone, but I was hoping I could maybe use ...
0
votes
2
answers
41
views
How to retrieve a fixed length string from json array
I have a json array that I need to pull a string from. We're storing this data in Bigquery and for the most part it's been pretty easy, except for extracting the id from the user_id field from the ...
0
votes
0
answers
18
views
Does BiqQuery and Cloud Endpoints (ESP) on GCP work with Workload Identity?
I have an authenticated workload running on my GKE cluster that is using workload identity.
I can confirm that workload identity engages correctly using the service account, because the secrets-store ...
0
votes
0
answers
21
views
Dataplex is recreating the Default bigquery table
I have deleted the default bigquery table that is created with the dataplex zone from the entities tab. But dataplex is recreating the same table again with the same name .
How to stop table ...
0
votes
1
answer
28
views
Langchain SQLDatabaseChain error due to SQL formatting
import os
from dotenv import load_dotenv
load_dotenv()
import google.generativeai as genai
from langchain_google_genai import GoogleGenerativeAI
# Ensure your GOOGLE_API_KEY is set in the .env file
...
0
votes
0
answers
27
views
Read data from PubSub Topic and write those data into BigQuery through the Dataflow by using Python
I successfully created the Dataflow job, but it didn't complete as expected. It seems there might be an issue with reading data from Pub/Sub, which could be preventing the job from finishing correctly....
0
votes
0
answers
34
views
Is it possible to use Dataflow for streaming row edits into BigQuery?
I have an ETL flow where I need to combine two Pub/Sub messages on a key and write these into BigQuery. One of the message types is the parent; I am working on payment processing, and this is an order ...
- 3,315
-1
votes
1
answer
20
views
Bigquery - Get latest close notes
number Created_Date Closed_Date Close_Notes
T1 8/10/2024 null null
T1 8/10/2024 8/11/2024 Completed Successfully
T2 8/...
- 11.9k
0
votes
1
answer
43
views
Selecting rows with matching multiple columns (order doesn't matter)
I am wanting to determine the number of distinct user_ids that have the same 3 animals as any user (where order doesn't matter).
For example below, user 11 and 13 both have dog, cat, bird so they ...
- 902
0
votes
0
answers
33
views
Dataform cross project dependencies
Currently in dataform , the dependencies can be specified either intra-repository or cross-repository. I'd like to specify some dependencies that rely on a dataform repository in a different project. ...
- 1,837
0
votes
0
answers
17
views
Log rows affected for every model execution
I'm unable to log the rows affected, after every model run. I'm doing this to know how many rows got affected after every model run for observability purposes. I used call statement but it looks like ...
- 121
1
vote
1
answer
36
views
Cannot query even though using partition field in Google BigQuery
I have the following query:
select count(1)
from `some_catalogue.some_table_with_partitions`
WHERE date_trunc(period,week(MONDAY)) >= (select max(date_trunc(period,week(MONDAY))) from `...
- 1,079
0
votes
1
answer
41
views
Pre hook - Macro - Truncate statement
I'm firing a macro using prehook in the pub_table model. The role of the macro is to truncate the pub_table but it doesn't truncate the table. So i checked the firing of the truncate statement using 2 ...
- 121