All Questions
Tagged with sql google-bigquery
7,538
questions
99
votes
1
answer
163k
views
Cannot access field in Big Query with type ARRAY<STRUCT<hitNumber INT64, time INT64, hour INT64, ...>>
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
...
- 3,189
56
votes
1
answer
57k
views
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 ...
- 13.3k
42
votes
6
answers
128k
views
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 ...
- 423
38
votes
3
answers
97k
views
How can I extract date from epoch time in BigQuery SQL
I have date stored in Epoch Time and I want to extract
Date from it. I tried the code below and I get null as output.
date_add( (timestamp( Hp.ASSIGN_TIME)), 1970-01-01,"second" ) as ...
- 409
37
votes
3
answers
123k
views
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 ...
- 643
37
votes
3
answers
18k
views
BigQuery Date-Partitioned 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 ...
- 403
34
votes
3
answers
60k
views
efficient way to compare two tables in bigquery
I am interested in comparing, whether two tables contain the same data.
I could do it like this:
#standardSQL
SELECT
key1, key2
FROM
(
SELECT
table1.key1,
table1.key2,
table1....
- 1,606
32
votes
1
answer
23k
views
Google BigQuery, I lost null row when using 'unnest' function
#StandardSQL
WITH tableA AS (
SELECT ["T001", "T002", "T003"] AS T_id, [1, 5] AS L_id
UNION ALL
SELECT ["T008", "T009"] AS T_id, NULL AS L_id
)
SELECT * FROM tableA, UNNEST(L_id) AS unnest
When I ...
- 345
28
votes
3
answers
38k
views
What is the difference between NUMERIC and FLOAT in BigQuery?
I read the docs:
https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types
https://cloud.google.com/bigquery/pricing#data
I know that FLOAT is 8 bytes while NUMERIC is 16 bytes
Is that ...
- 1,405
27
votes
6
answers
55k
views
Support UNION function in BigQuery SQL
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 ...
- 9,330
27
votes
2
answers
86k
views
How do I find elements in an array in BigQuery
I am trying to search for a row that has certain key value pairs in an array. A row in my BigQuery table would look something like this.
{
"ip": "192.168.1.200",
"cookie&...
- 1,051
26
votes
2
answers
102k
views
Difference between two dates in dates using Google bigquery?
How can I get the difference in days between 2 timestamp fields in Google Big Query?
The only function I know is Datediff which only works in Legacy SQL but I'm in Standard SQL.
For example: the ...
- 261
25
votes
3
answers
143k
views
BigQuery - No matching signature for operator = for argument types: INT64, STRING
Im getting a weird error(Maybe im getting this error for the first time) from BQ.
No matching signature for operator = for argument types: INT64, STRING.
Supported signatures: ANY = ANY at [27:1]
...
- 2,972
24
votes
2
answers
86k
views
WITH in BigQuery
Does BigQuery support the WITH clause? I don't like formatting too many subqueries.
For example:
WITH alias_1 AS (SELECT foo1 c FROM bar)
, alias_2 AS (SELECT foo2 c FROM bar a, alias_1 b WHERE b.c =...
- 1,450
24
votes
11
answers
4k
views
Alternative to BigQuery for medium-sized data
This is a follow-up to the question Why doesn't BigQuery perform as well on small data sets.
Let's suppose I have a data-set that is ~1M rows. In the current database that we're using (mysql) ...
- 108k
24
votes
4
answers
60k
views
'Create Table As' in BigQuery
How to create a table on the basis of result of select query in BigQuery?
For example:
create table abc as select x,y,z from mnp;
Is there any way or workaround to achieve the same in BigQuery?
...
- 1,089
23
votes
3
answers
68k
views
Google BigQuery: how to create a new column with SQL
I would like to add an column to an already existing table without using legacy SQL.
The basic SQL syntax for this is:
ALTER TABLE table_name
ADD column_name datatype;
I formatted the query for ...
- 2,604
22
votes
2
answers
56k
views
Bigquery - json_extract all elements from an array
i'm trying to extract two key from every json in an arry of jsons(using sql legacy)
currently i am using json extract function :
json_extract(json_column , '$[1].X') AS X,
json_extract(json_column , '...
- 249
22
votes
2
answers
34k
views
Get the Last Modified date for all BigQuery tables in a BigQuery Project
I have several databases within a BigQuery project which are populated by various jobs engines and applications. I would like to maintain a dashboard of all of the Last Modified dates for every table ...
- 1,400
22
votes
1
answer
69k
views
Transpose rows into columns in BigQuery (Pivot implementation) [duplicate]
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 ...
- 579
22
votes
2
answers
6k
views
BigQuery replaced most of my Spark jobs, am I missing something? [closed]
I've been developing Spark jobs for some years using on-premise clusters and our team recently moved to the Google Cloud Platform allowing us to leverage the power of BigQuery and such.
The thing is, ...
- 697
21
votes
7
answers
39k
views
Scalable Solution to get latest row for each ID in BigQuery
I have a quite large table with a field ID and another field as collection_time. I want to select latest record for each ID. Unfortunately combination of (ID, collection_time) time is not unique ...
- 2,086
20
votes
9
answers
39k
views
Use google bigquery to build histogram graph
How can write a query that makes histogram graph rendering easier?
For example, we have 100 million people with ages, we want to draw the histogram/buckets for age 0-10, 11-20, 21-30 etc... What does ...
- 1,796
19
votes
5
answers
39k
views
How to generate date series to occupy absent dates in google BiqQuery?
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(...
- 1,025
19
votes
5
answers
33k
views
Querying multiple tables in Big Query
As it is not possible to update data within a table in BigQuery, and supports only append mechanism, I have decided to create new tables on monthly basis. So suppose for year 2012 the tables would be (...
- 475
19
votes
2
answers
15k
views
BigQuery select * except nested column
What I want to do should be simple: Given a BigQuery schema, I want to select all tables (including nested ones) apart from a few. The tricky thing is that BigQuery has a nested structure and the few ...
- 435
18
votes
2
answers
18k
views
Stratified random sampling with BigQuery?
How can I do stratified sampling on BigQuery?
For example, we want a 10% proportionate stratified sample using the category_id as the strata. We have up to 11000 category_ids in some of our tables.
- 57.7k
18
votes
3
answers
34k
views
What to try to get BigQuery to CAST BYTES to STRING?
BigQuery Standard SQL documentation suggests that BYTE fields can be coerced into STRINGS.
We have a byte field that is the result of SHA256 hashing a field using BigQuery itself.
We now want to ...
- 5,932
18
votes
2
answers
20k
views
COUNT(*) FILTER (WHERE... In BigQuery
How can I filter in a count in BigQuery?
In Postgres I was doing something like:
COUNT(*) FILTER (WHERE event_date>('{{ report_date }}'::date-INTERVAL '2 WEEK'))
Though this does not work in ...
- 223
17
votes
4
answers
17k
views
How to calculate Session and Session duration in Firebase Analytics raw data?
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 ...
- 204
17
votes
4
answers
25k
views
Default values for columns in Big Query Tables
Is there a way to set default values for columns in tables in big query? I would like to set 'false' as a default value for a column of boolean data type.
- 303
16
votes
4
answers
29k
views
BigQuery SQL for 28-day sliding window aggregate (without writing 28 lines of SQL)
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 ...
- 4,367
16
votes
4
answers
42k
views
How to unpivot in BigQuery?
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:
...
- 324
16
votes
2
answers
93k
views
Google BigQuery CASE function
I'm trying to run a query like what's answered here,
SQL Selecting multiple sums?
SELECT SUM(CASE WHEN order_date >= '01/01/09' THEN quantity ELSE 0 END) AS items_sold_since_date,
SUM(...
- 3,984
16
votes
6
answers
25k
views
BigQuery: Return First Value from Different Groups in a Group By
I am currently having a problem with a Standard SQL query. I have a list of emails where every email can have multiple functions. See the example below on how the table looks like.
Email ...
- 181
16
votes
6
answers
78k
views
Equivalent of string contains in google bigquery
I have a table like as shown below
I would like to create two new binary columns indicating whether the subject had steroids and aspirin. I am looking to implement this in Postgresql and google ...
- 7,409
16
votes
2
answers
60k
views
BigQuery compare Date
In BigQuery table what I using some date stored in DateTime typed columns with value like "2016-01-20T00:00:00".
I want to run query that for example show row which included in some range (lets say ...
- 2,356
16
votes
6
answers
31k
views
Error in Google BigQuery <EOF>
I am new to Google BigQuery. I need help with the query error:
"Encountered " "WITH" "with "" at line 1, column 1. Was expecting:
EOF"
with
t1 as
(
select
date(USEC_TO_TIMESTAMP(...
- 261
15
votes
1
answer
19k
views
BigQuery JOINs between tables in different projects
I have datasets in two distinct projects.
I want to perform join of one table from a dataset in the first project to table in a dataset in the second project.
Can you provide an example of such a ...
- 15.2k
15
votes
3
answers
27k
views
Unnesting structs in BigQuery
What is the correct way to flatten a struct of two arrays in BigQuery? I have a dataset like the one pictured here (the struct.destination and struct.visitors arrays are ordered - i.e. the visitor ...
- 175
14
votes
1
answer
33k
views
google-bigquery format date as mm/dd/yyyy in query results
I am using Bigquery SQL to generate a report. The standard Bigquery date format is yyyy-mm-dd, but I want it to be formatted as mm/dd/yyyy.
Is there a way via Bigquery SQL to convert the date format ...
- 643
14
votes
2
answers
41k
views
BigQuery standard SQL: how to group by an ARRAY field
My table has two columns, id and a. Column id contains a number, column a contains an array of strings. I want to count the number of unique id for a given array, equality between arrays being defined ...
- 1,012
14
votes
1
answer
28k
views
how to set named parameter on bigquery query
I am trying to use named parameters in my query on bigquery.
@val '123';
SELECT *
FROM [project_id:my_dataset.my_table]
where name=@val
I get
Encountered " <MAX_TOKEN_VAL> "R "" at line 1, ...
- 4,171
14
votes
1
answer
17k
views
Resources Exceeded during query execution
I'm trying to run a query joining 2 large sets of data and I'm hitting the resources exceeded during query execution error. I've read that there are work around when using Join Each and Group Each ...
- 143
14
votes
1
answer
690
views
Improving performance for a nested for loop iterating over dates
I am looking to learn how to improve the performance of code over a large dataframe (10 million rows) and my solution loops over multiple dates (2023-01-10, 2023-01-20, 2023-01-30) for different ...
- 1,497
13
votes
2
answers
25k
views
How to sort an array in BigQuery standard SQL?
I am wondering if it is possible to order (apply order by) for individual array values in Google BigQuery?
I am able to achieve this by applying order by on the whole transactonal base table first, ...
- 185
13
votes
3
answers
52k
views
bigQuery throws "division by zero: 0 / 0" error
I am doing the percentage by the frequency of column value using bigquery. However, some of the value might be zero, so the query will return error for sure
(division by zero: 0 / 0)
How to apply ...
- 539
13
votes
1
answer
48k
views
"contains" in Bigquery standard SQL
I wish to migrate from Legacy SQL to Standard SQL
I had the following code in Legacy SQL
SELECT
hits.page.pageTitle
FROM [mytable]
WHERE hits.page.pageTitle contains '%'
And I tried this in ...
- 2,057
13
votes
4
answers
7k
views
How to get the historical weather for any city with BigQuery?
BigQuery has NOAA's gsod data loaded as a public dataset - starting in 1929: https://www.reddit.com/r/bigquery/comments/2ts9wo/noaa_gsod_weather_data_loaded_into_bigquery/
How can I retrieve the ...
- 57.7k
13
votes
1
answer
23k
views
How to create dummy variable columns for thousands of categories in Google BigQuery?
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 ...
- 935