Tag: google-bigquery

How to deal with semantic version data type in BigQuery

google-bigquery google-cloud-platform sql

I know that there is no data type in BigQuery. What would you prefer to deal with semantic versions in BigQuery? I have the following schema: software_version column is string but the data I store there is in semver format: `MAJOR.MINOR.PATCH-prerelease I especially want to perform operators < > =. This returns true, but according to the semver definition it’s

BigQuery cli bq how to use EXECUTE IMMEDIATE script

bash bq google-bigquery sql

I would like to use two SQL scripts to create and update a table in BigQuery using bq query command. Here are the two scripts : 1- Creating the table 2- updating the table When using these two scripts as queries with the bq query command, I have got an error : Is there any other way to do that?

Need to find string using bigquery

google-bigquery python-re sql

We have below string column and having below data and I want to find Null count present in string columns means how many times null value(”) present in front of id column present in select statement using big query. Don’t use string position. Expected output: Answer Below is for BigQuery Standard SQL if to apply to sample data from your

SQL – Find most recent value from other fields (per user, between occurrences)

google-bigquery sql

I have actions and results mixed together in an “event” field. But each of these actions essentially “causes” the result (and the subsequent outcome) (let’s assume). I want to tie each future outcome back to the actions that caused it, for each user. I want the value from the “outcome” field to be copied into a new field for each

Filter country using a JOIN in BigQuery

google-bigquery join sql

In a scheduled query in BQ, I am trying to filter countries using an external Google Sheet (already made a table). The query already has a join from two other tables. I would like to enter a SELECT clause in the WHERE statement. I keep getting errors. Any tips as to where I am going wrong? Expected output would be

Compare two arrays and count number of the same strings

google-bigquery sql

I would like to compare two arrays in two columns and in the third column return the number of the same strings within those two arrays. Answer You can use unnest(). Assuming that the individual arrays have no duplicates:

How to unnest multilevel structs in BigQuery?

google-bigquery sql

I have many tables with different table suffixes in BigQuery where each table has four level structs in each row. I made query to unnest it into flattening table. It works correct but not very well because it is seems like too long. How can I make it simpler and more effective? Any suggestions. Thank you. Answer Did you try

How to escape a percent sign in BigQuery queries?

google-bigquery sql

We are attempting to select rows from a field in BigQuery that contains only a percent sign “%”. We have tried the answers to various other questions for escaping like this: SELECT COUNT(1) FROM Table WHERE field_name LIKE “%” This gives an invalid escape character error with single or double quotes. We also tried other variation escape characters shown for

SQL COUNT items and GROUP over multiple categories with 0 totals for missing entries

google-bigquery sql

I have a table in BigQuery I would like to aggregate over a number of different categories (columns in the table) to produce a summary of the data, which is a simple COUNT and GROUP BY statement. However, for this project I would like to produce a “complete” count of every permutation of the categories (the possible values of the