We are generating a dynamic query using the script below : We want to create a view out of this is there any way in BQ by which we can create a view from this, so as user we can directly query our view. Answer Should be as simple as just adding create or replace view project.dataset.myview as to original
Tag: google-bigquery
Flatten the Data in BigQuery
I have dimensions.key_value of RECORD type i run the following query with following output. To unnest the data i have created the following view: My Output: There are two thing i need to verify is my query correct ? How i can make the query generic i.e if i don’t know all the key, there some other keys may also
How to extract everything after parentheses in BigQuery?
How can I extract all the characters after the first word? For example, I would like to have (084M) in its own column with the parentheses included. I’ve tried to SPLIT and REGEXP_EXTRACT but I have ran into issues. Table: Name Elizabeth (084M) Elizabeth (084M) Elizabeth (084M) Pittston (14KN) Pittston (14KN) Pittston (14KN) Cheektowaga (14ON) Image of Table: Answer use
Fix duplicate rows while selecting nested fields from BigQuery using JOIN and UNNEST
I am trying to copy data from one BQ table to another and having trouble with un-nesting and JOINs. I have two fields of REPEATED RECORD type in my schema and when I try to JOIN those, I get a lot of duplicate rows. Here´s my query: What I want to achieve is: But when I JOIN Address and Work,
Filtering ‘Where In’ Queries by ‘Is Not Null’ in Big Query
I am attempting to query a table in BigQuery programmatically, and have a WHERE-IN clause that I would like to support having NULL as the value. Current Query looks like: I am wondering if there is a way so that I can query everything from the table for a specific date in the case that the LIST_OF_IDS input is NULL
Google BigQuery – Subtract SUMs of a column basing on values in another column
Hi I need 1 query to get top 10 country which has largest [total(import) – total(export)] for goods_type medicines between 2019 – 2020. The data sample is as below: The returned data should include country, goods_type, and the value of [total(imports) – total(export)]. I have come up with the query below, but I don’t know if it’s right or wrong,
How to subtract the cumulative values of confirmed, recovered and deseased?
I would like to subtract the cumulative values of confirmed, recovered and deceased. However, it does not work, Google BigQuery keeps giving me this error message (look at the pic). All attributes are INTEGER. Answer The error message is pretty clear. You have mixed types in the data — suggesting that something is wrong. Presumably, the offending value is cumulative_recovered
Numbering duplicate rows in bigquery
I’m trying to number rows in which column a and column b is a match and when they occur again regardless of the date. The dataset looks like this and I want the row_number column: rider driver date row_number jim joe 2020-10-20 1 jim joe 2020-10-28 2 jim jack 2020-10-29 1 jim joe 2020-10-31 3 john jane 2020-10-29 1 john
Google BigQuery Dimensions SQL Multiple Grids
how would you select multiple grids in the example below instead of just one under “WHERE”: What needs to be changed: It currently only runs for 1 organisation (GRID), I would like it to run for 11 organisations. The org is identified with an ID called a “GRID”, it looks like this: “grid.5170.3” I want my new code to take
Why select invalid field in subquery could run in BigQuery?
For the following sql Since there is no a in temp.a1 and there should be an error here, However, the output of Bigquery is Why the result happened? On the other side, when run select distinct a from temp.a1; there is one error Unrecognized name: a comes up. Answer Your query is: You think this should be: And hence generate