Questions tagged [google-bigquery]

Google BigQuery is a Google Cloud Platform product providing serverless queries of petabyte-scale data sets using SQL. BigQuery provides multiple read-write pipelines, and enables data analytics that transform how businesses analyze data.

Google BigQuery is a web service that lets you do interactive analysis of massive datasets—up to billions of rows. Scalable and easy to use, BigQuery lets developers and businesses tap into powerful data analytics on demand.

Official sites:

Other sites for related topics and discussion:

25130 questions
47
votes
3 answers

Can we cast the type in BigQuery?

Following my query : SELECT SQRT((D_o_latitude - T_s_lat)^2+(D_o_longitude - T_s_long)^2)/0.00001 FROM [datasetName.tableName] I am getting the error as Error: Argument type mismatch in function SUBTRACT:'D_o_latitude' is type string, 'T_s_lat'…
Ravindra
  • 2,091
  • 3
  • 18
  • 21
46
votes
5 answers

How to Auth to Google Cloud using Service Account in Python?

I'm trying to make a project that will upload Google Storage JSON file to BigQuery (just automate something that is done manually now), and I'd like to use a 'service account' for this as my script is going to be run on daily basis. After reading…
45
votes
4 answers

How can I undelete a BigQuery table?

I've accidentally deleted one of my BigQuery tables. Is it possible to get it back? The API doesn't seem to support undelete.
Jordan Tigani
  • 26,089
  • 4
  • 60
  • 63
43
votes
7 answers

Exporting data from Google Cloud Storage to Amazon S3

I would like to transfer data from a table in BigQuery, into another one in Redshift. My planned data flow is as follows: BigQuery -> Google Cloud Storage -> Amazon S3 -> Redshift I know about Google Cloud Storage Transfer Service, but I'm not sure…
Onca
  • 1,113
  • 3
  • 17
  • 31
42
votes
7 answers

BigQuery converting to a different timezone

I am storing data in unixtimestamp on google big query. However, when the user will ask for a report, she will need the filtering and grouping of data by her local timezone. The data is stored in GMT. The user may wish to see the data in EST. The…
akshah123
  • 708
  • 1
  • 5
  • 10
41
votes
3 answers

How to exclude NULLs from ARRAY so query won't fail

ARRAY_AGG aggregate function includes NULLs in the arrays it builds. When such arrays are part of query result, query fails with error: Array cannot have a null element; error in writing field i.e. the following query demonstrates…
Mosha Pasumansky
  • 13,206
  • 5
  • 32
  • 55
39
votes
3 answers

Efficiently write a Pandas dataframe to Google BigQuery

I'm trying to upload a pandas.DataFrame to Google Big Query using the pandas.DataFrame.to_gbq() function documented here. The problem is that to_gbq() takes 2.3 minutes while uploading directly to Google Cloud Storage takes less than a minute. I'm…
39
votes
3 answers

difference in minutes between 2 bigquery timestamp fields

How can I get the difference in minutes between 2 timestamp fields in google bigquery? The only function I know is Datediff which gives the difference in day Thanks
user3569267
  • 1,065
  • 3
  • 14
  • 27
39
votes
6 answers

Bigquery query to find the column names of a table

I need a query to find column names of a table (table metadata) in Bigquery, like the following query in SQL: SELECT column_name,data_type,data_length,data_precision,nullable FROM all_tab_cols where table_name ='EMP';
user1487985
  • 393
  • 1
  • 3
  • 6
38
votes
7 answers

Google BigQuery Delete Rows?

Anyone know of any plans to add support for delete parts of data from a table in Google Bigquery? The issue we have right now is we are using it for analytics of data points we collect over time. We want to run the queries over the last X days of…
Daum
  • 815
  • 1
  • 7
  • 11
37
votes
3 answers

What is Google's Dremel? How is it different from Mapreduce?

Google's Dremel is described here. What's the difference between Dremel and Mapreduce?
Yktula
  • 14,179
  • 14
  • 48
  • 71
37
votes
4 answers

Google BQ - how to upsert existing data in tables?

I'm using Python client library for loading data in BigQuery tables. I need to update some changed rows in those tables. But I couldn't figure out how to correctly update them? I want some similar UPSERT function - insert row only if its not exists,…
Aleks Boev
  • 630
  • 1
  • 7
  • 16
37
votes
7 answers

How to Pivot table in BigQuery

I am using Google Big Query, and I am trying to get a pivoted result out from public sample data set. A simple query to an existing table is: SELECT * FROM publicdata:samples.shakespeare LIMIT 10; This query returns following result set. Now what…
user1401472
  • 2,203
  • 3
  • 23
  • 37
36
votes
3 answers

BigQuery SQL WHERE Date Between Current Date and -15 Days

I am trying to code the following condition in the WHERE clause of SQL in BigQuery, but I am having difficulty with the syntax, specifically date math: WHERE date_column between current_date() and current_date() - 15 days This seems easy in MySQL,…
Eric Hendershott
  • 633
  • 3
  • 8
  • 15
36
votes
5 answers

How to get BigQuery storage size for a single table

I want to calculate table wise cost for Google Big Query Storage, But i don't know how to view size of storage for each table individually.
selva kumar
  • 1,086
  • 2
  • 11
  • 30