Skip to main content
2,490 questions
Newest Active Bountied Unanswered
Filter by
Sorted by
Tagged with
42 votes
7 answers
156k views

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; ...
user1401472's user avatar
  • 2,331
1 vote
2 answers
17k views

This is giving me an unrecognized name error. Why? SELECT employees.name AS employee_name, employees.role AS employee_role, departments.name AS department_name FROM `strange-calling-...
Steve Sheng's user avatar
  • 23
79 votes
12 answers
158k views

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 ...
TheGoat's user avatar
  • 2,917
39 votes
6 answers
46k views

In June the BQ team announced support for date-partitioned tables. But the guide is missing how to migrate old non-partitioned tables into the new style. I am looking for a way to update several or ...
Pentium10's user avatar
  • 209k
5 votes
1 answer
6k views

I have setup Firebase analytics data to BigQuery. However I received my first export today and it contains all yesterdays user statistics. That is great but I need lifetime statistics history or at ...
PovilasID's user avatar
  • 895
11 votes
2 answers
5k views

Suppose I have a PCollection<Foo> and I want to write it to multiple BigQuery tables, choosing a potentially different table for each Foo. How can I do this using the Apache Beam BigQueryIO API?...
jkff's user avatar
  • 18k
22 votes
1 answer
70k views

I want to generate a new table and place all key value pairs with keys as column names and values as their respective values using BigQuery. Example: **Key** **Value** channel_title ...
Prabhjot's user avatar
  • 599
46 votes
4 answers
51k views

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's user avatar
  • 26.7k
43 votes
8 answers
68k views

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 ...
Onca's user avatar
  • 1,135
26 votes
2 answers
17k views

A couple of questions about the TABLE_QUERY function: The examples show using table_id in the query string, are there other fields available? It seems difficult to debug. I'm getting "error evaluating ...
Jordan Tigani's user avatar
  • 26.7k
46 votes
4 answers
88k views

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 ...
Aleks Boev's user avatar
  • 730
27 votes
6 answers
57k views

BigQuery does not seem to have support for UNION yet: https://developers.google.com/bigquery/docs/query-reference (I don't mean unioning tables together for the source. It has that.) Is it coming ...
mdahlman's user avatar
  • 9,410
25 votes
5 answers
7k views

This is specifically a question relating to server to server authorisation between a python Google AppEngine app and Google's BigQuery, but could be relevant for other cloud services. tldr; Is it ...
danmux's user avatar
  • 3,012
13 votes
1 answer
24k views

I have a simple table with 2 columns: UserID and Category, and each UserID can repeat with a few categories, like so: UserID Category ------ -------- 1 A 1 B 2 C 3 ...
wubr2000's user avatar
  • 975
6 votes
1 answer
8k views

Let's say, I have music video play stats table mydataset.stats for a given day (3B rows, 1M users, 6K artists). Simplified schema is: UserGUID String, ArtistGUID String I need pivot/transpose ...
Mikhail Berlyant's user avatar
  • 174k
3 votes
2 answers
7k views

I have 32 years of data that I want to put into a partitioned table. However BigQuery says that I'm going over the limit (4000 partitions). For a query like: CREATE TABLE `deleting.day_partition` ...
Felipe Hoffa's user avatar
  • 59.8k
88 votes
16 answers
355k views

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 ...
Colin Burke's user avatar
  • 17.3k
10 votes
2 answers
12k views

When trying the access a federated source (Google sheets) from BigQuery API, the following error is thrown: [..] "errorResult" : { "location" : "/gdrive/id/<removed_file_id>", "...
Graham Polley's user avatar
  • 14.8k
30 votes
2 answers
100k views

I found a glitch/bug in bigquery. We got a table based on Bank Statistic data under the starschema.net:clouddb:bank.Banks_token If i run the following query: SELECT count(*) as totalrow, count(...
Balazs Gunics's user avatar
  • 2,077
27 votes
6 answers
44k views

BigQuery has facilities to parse JSON in real-time interactive queries: Just store the JSON encoded object as a string, and query in real time, with functions like JSON_EXTRACT_SCALAR. However, I can'...
Felipe Hoffa's user avatar
  • 59.8k
19 votes
3 answers
20k views

We're using Google BigQuery via the Python API. How would I create a table (new one or overwrite old one) from query results? I reviewed the query documentation, but I didn't find it useful. We want ...
Lukas Šalkauskas's user avatar
  • 14.4k
17 votes
5 answers
52k views

Not sure what functions to call, but transpose is the closest thing I can think of. I have a table in BigQuery that is configured like this: but I want to query a table that is configured like this: ...
Ben Leathers's user avatar
  • 334
16 votes
1 answer
17k views

I would like to trigger a Cloud Function when new data has been imported into a BigQuery table. Ideally, I would like to extract all the rows (one column is ISIN) that have been inserted. Would this ...
WJA's user avatar
  • 7,054
10 votes
6 answers
7k views

I wanted to take advantage of the new BigQuery functionality of time partitioned tables, but am unsure this is currently possible in the 1.6 version of the Dataflow SDK. Looking at the BigQuery JSON ...
ptf's user avatar
  • 115
6 votes
1 answer
6k views

Today BigQuery released a new cool function called PIVOT. Se below how it works: with Produce AS ( SELECT 'Kale' as product, 51 as sales, 'Q1' as quarter UNION ALL SELECT 'Kale', 23, 'Q2' UNION ...
Murta's user avatar
  • 2,265
3 votes
2 answers
3k views

My Schema looks something like this: userid:string timestamp:integer params:nested/repeated field with 2 fields - name:string (possible values: "a", "b","c") - value:string I want my query to ...
David M Smith's user avatar
  • 2,332
94 votes
6 answers
117k views

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 (...
David M Smith's user avatar
  • 2,332
34 votes
7 answers
159k views

Is there any way to get row number for each record in BigQuery? (From the specs, I haven't seen anything about it) There is a NTH() function, but that applies to repeated fields. There are some ...
Leo Stefa's user avatar
  • 451
20 votes
9 answers
41k views

I am trying to select data from the latest partition in a date-partitioned BigQuery table, but the query still reads data from the whole table. I've tried (as far as I know, BigQuery does not support ...
cshin9's user avatar
  • 1,490
17 votes
4 answers
32k views

I'm trying to compute a 28 day moving sum in BigQuery using the LAG function. The top answer to this question Bigquery SQL for sliding window aggregate from Felipe Hoffa indicates that that you can ...
alan's user avatar
  • 4,487
17 votes
4 answers
17k views

How to calculate Session Duration in Firebase analytics raw data which is linked to BigQuery? I have used the following blog to calculate the users by using the flatten command for the events which ...
Joshua Johnson's user avatar
  • 204
9 votes
3 answers
24k views

I have a BigQuery query which unions two tables (daily Google Analytics exports). What I want is to save the query as a view or table but have that final view/table update automatically everyday as ...
Aaron Harris's user avatar
  • 415
3 votes
2 answers
8k views

I have a problem in transposing a large amount of data table in BigQuery (1.5 billion rows) from rows to columns. I could figure out how to do it with small amount of data when hardcoded, but with ...
Jade's user avatar
  • 51
0 votes
1 answer
1k views

I have a dataflow job where I will read from bigquery query first (in standard sql). It works perfectly in direct runner mode. However I tried to run this dataflow in dataflow runner mode and ...
Tomxiao's user avatar
  • 1
80 votes
2 answers
108k views

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 ...
Diego's user avatar
  • 1,850
52 votes
7 answers
219k views

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 ...
Daum's user avatar
  • 975
21 votes
5 answers
42k views

I am trying to get daily sum of sales from a google big-query table. I used following code for that. select Day(InvoiceDate) date, Sum(InvoiceAmount) sales from test_gmail_com.sales where year(...
Manura Omal's user avatar
  • 1,055
21 votes
6 answers
24k views

I am trying to add new column to BigQuery existing table. I have tried bq command tool and API approach. I get following error when making call to Tables.update(). I have tried with providing full ...
archman's user avatar
  • 444
19 votes
9 answers
27k views

I'm wondering if anyone knows of a way to measure string similarity in BigQuery. Seems like would be a neat function to have. My case is i need to compare the similarity of two urls as want to be ...
andrewm4894's user avatar
  • 1,558
11 votes
4 answers
18k views

I'm just learning BigQuery so this might be a dumb question, but we want to get some statistics there and one of those is the total sessions in a given day. To do so, I've queried in BQ: select sum(...
Willian Fuks's user avatar
  • 11.9k
11 votes
3 answers
6k views

I have loaded my application logs in BigQuery and I need to calculate country based on IP address from those logs. I have written a join query between my table and a GeoIP mapping table that I ...
N.N.'s user avatar
  • 3,182
9 votes
2 answers
8k views

I have seen this question answered before on stack overflow (https://stackoverflow.com/questions/29983621/how-to-get-filename-when-using-file-pattern-match-in-google-cloud-dataflow), but not since ...
WIT's user avatar
  • 1,083
8 votes
10 answers
65k views

I am trying to run a query on a 12 GB csv file loaded in Google big query, I cant run any query on the dataset. I am not sure if the dataset is loaded correctly. It shows as a table in the pane, but ...
Surya Santosh's user avatar
  • 81
6 votes
4 answers
19k views

Can somebody provide working example of using the Bigquery API with PHP. I see there are examples for python and java but could not find anything for PHP. Here is the bigquery browser https://...
Nilesh's user avatar
  • 2,155
1 vote
2 answers
4k views

I'm trying to get the number of unique events on a specific date, rolling 90/30/7 days back. I've got this working on a limited number of rows with the query bellow but for large data sets I get ...
Frithiof's user avatar
  • 101
59 votes
5 answers
132k views

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 ...
user912830823's user avatar
  • 1,392
51 votes
4 answers
243k views

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 ...
Lucasaudati11's user avatar
  • 577
38 votes
3 answers
22k views

BigQuery allows you to create date-partitioned tables: https://cloud.google.com/bigquery/docs/creating-partitioned-tables I'd like to be able to create views on top of date-partitioned tables and ...
tomwoodruff's user avatar
  • 413
37 votes
3 answers
43k views

Google's Dremel is described here. What's the difference between Dremel and Mapreduce?
Yktula's user avatar
  • 15k
33 votes
5 answers
216k views

I have a page URL column components of which are delimited by /. I tried to run the SPLIT() function in BigQuery but it only gives the first value. I want all values in specific columns. I don't ...
Saumil Agrawal's user avatar
  • 351

1
2 3 4 5
…
50