Stack Overflow
Stack Overflow

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
113
votes
8 answers

Is there a way to export a BigQuery table's schema as JSON?

A BigQuery table has schema which can be viewed in the web UI, updated, or used to load data with the bq tool as a JSON file. However, I can't find a way to dump this schema from an existing table to a JSON file (preferably from the command-line).…
Daniel Waechter
  • 2,574
  • 2
  • 21
  • 21
107
votes
3 answers

What's the difference between BigQuery and Bigtable?

Is there any reason why someone would use Bigtable instead of BigQuery? Both seem to support Read and Write operations with the latter offering also advanced 'Query' operations. I need to develop an affiliate network (thus I need to track clicks and…
The user with no hat
  • 10,166
  • 20
  • 57
  • 80
92
votes
1 answer

Cannot access field in Big Query with type ARRAY>

I'm trying to run a query using Standard SQL Dialect (ie not Legacy SQL) on BigQuery. My query is: SELECT date, hits.referer FROM `refresh.ga_sessions_xxxxxx*` LIMIT 1000 But keep getting the error Error: Cannot access field referer on a value…
Feynman27
  • 3,049
  • 6
  • 30
  • 39
87
votes
6 answers

Random Sampling in Google BigQuery

I just discovered that the RAND() function, while undocumented, works in BigQuery. I was able to generate a (seemingly) random sample of 10 words from the Shakespeare dataset using: SELECT word FROM (SELECT rand() as random,word FROM…
David M Smith
  • 2,212
  • 4
  • 21
  • 27
73
votes
17 answers

Setting GOOGLE_APPLICATION_CREDENTIALS for BigQuery Python CLI

I'm trying to connect to Google BigQuery through the BigQuery API, using Python. I'm following this page here: https://cloud.google.com/bigquery/bigquery-api-quickstart My code is as follows: import os import argparse from apiclient.discovery…
Colin Ricardo
  • 16,488
  • 11
  • 47
  • 80
72
votes
9 answers

Delete duplicate rows from a BigQuery table

I have a table with >1M rows of data and 20+ columns. Within my table (tableX) I have identified duplicate records (~80k) in one particular column (troubleColumn). If possible I would like to retain the original table name and remove the duplicate…
TheGoat
  • 2,587
  • 3
  • 25
  • 58
71
votes
8 answers

How do I identify the Google Cloud Storage URI from my Google Developers Console?

When I attempt load data into BigQuery from Google Cloud Storage it asks for the Google Cloud Storage URI (gs://). I have reviewed all of your online support as well as stackoverflow and cannot find a way to identify the URL for my uploaded data…
Kelly
  • 853
  • 2
  • 7
  • 6
69
votes
2 answers

Update or Delete tables with streaming buffer in BigQuery?

I'm getting this following error when trying to delete records from a table created through GCP Console and updated with GCP BigQuery Node.js table insert function. UPDATE or DELETE DML statements are not supported over table…
Diego
  • 1,678
  • 1
  • 16
  • 20
66
votes
3 answers

Select All Columns Except Some in Google BigQuery?

Is there a way to Select * except [x,y,z column names] in BigQuery? I see some solutions for MySQL but not sure if it applies to BQ. Thank you.
wubr2000
  • 855
  • 2
  • 8
  • 10
60
votes
1 answer

BigQuery - Datetime vs Timestamp

I looked on the documentation for google big query data types, checking the differences between TimeStamp to Datetime data types. As I understand the main difference is: Unlike Timestamps, a DATETIME object does not refer to an absolute instance in…
NirKa
  • 697
  • 1
  • 7
  • 10
58
votes
11 answers

How to create temporary table in Google BigQuery

Is there any way to create a temporary table in Google BigQuery through: SELECT * INTO FROM same as we can create in SQL? For complex queries, I need to create temporary tables to store my data.
Vasundhara
  • 645
  • 1
  • 6
  • 9
52
votes
1 answer

SQL array flattening: Why doesn't CROSS JOIN UNNEST join every nested value with every row?

This question isn't about solving a particular problem, it's about understanding what's actually happening behind the scenes in a common SQL idiom used to flatten arrays. There's some magic behind the scenes and I want to peek behind the curtain of…
conradlee
  • 12,985
  • 17
  • 57
  • 93
51
votes
4 answers

STRING to DATE in BIGQUERY

I am struggling to try to do this with Google BigQuery: I do have a column with dates in the following STRING format: 6/9/2017 (M/D/YYYY) I am wondering how can I deal with this, trying to use the DATE clause in order to get the this format:…
Lucasaudati11
  • 577
  • 1
  • 4
  • 8
51
votes
4 answers

Google BigQuery There are no primary key or unique constraints, how do you prevent duplicated records being inserted?

Google BigQuery has no primary key or unique constraints. We cannot use traditional SQL options such as insert ignore or insert on duplicate key update so how do you prevent duplicate records being inserted into Google BigQuery? If I have to call…
searain
  • 3,143
  • 6
  • 28
  • 60
49
votes
6 answers

Setting Big Query variables like mysql

what is the bigquery equivalent to mysql variables like? SET @fromdate = '2014-01-01 00:00:00', -- dates for after 2013 @todate='2015-01-01 00:00:00', @bfromdate = '2005-01-01 00:00:00', -- dates for before 2013 @btodate = '2005-01-01…
Chris Hansen
  • 7,813
  • 15
  • 81
  • 165
1
2 3
…
99 100