BigQuery | wrapper around the Google api ruby gem | GCP library

I have a table that looks like this

bigquery tells me that created_date is stored a string. So I need to transform created_date from a unix timestamp into a date.

• First I tried PARSE_TIMESTAMP("%s", created_date) but got error: Failed to parse input string "1617753600000"

• Then I tried TIMESTAMP_MICROS(CAST(created_date as int64)) as submitted_at which displays the date correctly Wed Apr 07 2021

I'm curious why does PARSE_TIMESTAMP not work in this case? Isn't this how it should be used?

I'm trying to join array elements in BigQuery but I am getting the following error message:

Correlated subqueries that reference other tables are not supported unless they can be de-correlated, such as by transforming them into an efficient JOIN.

In my first table I have something like:

field1 | field2 | some_list

Elements in some_list have ids and other data and I'd like to enrich each element from some_list with some fields from a different table (they may exist or not).
I've tried to unnest some_list and left join with a different table on id but it seems it's not allowed.

Any ideas how I can do it? Thanks!

First table:

I have data in BigQuery with the following schema:

I am looking to replace a table in a single transaction. I am using the bigqueryoperator with write_disposition='WRITE_TRUNCATE' and my sql is just select * from my_table. I am wondering if this will happen in a single transaction or two seperate transactions? If it is 2 transactions, is there anyway I can replace my bigquery table with select * from my_table in a single transaction?

I have such data structure in BigQuery:

I am planning to use Google Cloud Workflows to perform SQL queries on a BigQuery data lake. I have 7 consecutive queries to perform (the query n is using the result of n-1) to update some tables. However, I am quite sure is there a way to directly call BigQuery from a GCP Workflows .yaml?

exampe.yaml

I am trying to use bigquery to search text in TABLE A by using keywords in TABLE B. I must register, per each row found in TABLE A, which keywords it matched in TABLE B. I would be most grateful for the standardSQL bigqury code that uses the input below to generate the expected result below

INPUT:

I am trying to extract data from Google public data set - chicago_taxi_trips, since the dataset is huge, I would like to only extract data from 2012 & 2018

I am using the query below to extract the columns I need but I can't extract it with the WHERE Year = 2012 OR Year = 2018 I have created the columns Year with a sql function

I have an array of structs in BigQuery that looks like: