PubSub BigQuery Subscription 

Thanks for the feedback Andre. This is the purpose of this channel to share things to give others ideas 💡 glad you found it useful

Hi Raunak, this feature is designed to “ingest” data into BigQuery, not transformations. Typically it’s not a good idea to deal with transforming data at ingestion time, instead, get data into BigQuery first, then handle transformation using another tool such as DBT. This is because any processes / logics added at ingestion time is likely going to introduce a “magic” layer, and if anything goes wrong in that step it’s very difficult to debug because source data may no longer exist to validate what happened. If you want to only keep data ingested for a period of time, add TTL to the BigQuery table at partition level so that data doesn’t get kept longer than it should, see cloud.google.com/bigquery/docs/managing-tables

I am glad you found it useful, Cong. I think this is what you are looking for if I understood you correctly. cloud.google.com/bigquery/docs/reference/standard-sql/json-data#extract_arrays_from_json basically you can use this function to get an array of JSON types and then do UNNEST to unpack it to multiple roles.

@@practicalgcp2780 Thanks for your apply. Sorry, no, I meant to ask, if it is possible to get multiple BQ entries, through BQ Subscriptions as described in the video, out of a single message containing an array of elements.

@@keisou9765 ok I see, it depends on your input, it supports the Record data type, so that is nested so you can use that like this stackoverflow.com/questions/11764287/how-to-nest-records-in-an-avro-schema. Which will be mapped to the BigQuery Record type. I don’t think you want them to be separate rows in BQ, store them as Record and do Unnesting is a better way to do it because BigQuery natively supports nested and repeated data in a single row.

Not that I am aware of, there maybe a bit of a delay when you first set it up but I don’t expect it to be continuously like that unless there are issues with google services (behind the scenes it might be using cloud dataflow). If you notice unexpected delays I would say note the message timestamp down (you can see these if you export all columns into BigQuery) and then raise it to google cloud support with screenshots etc, they can debug it for you.

@@practicalgcp2780 thanks for the advicee

I am glad you found it useful ;P So you can convert it in two ways. 1) convert it to an integer or long as a unix timestamp (which is a integer since epoch) during your ingestion process, in other words, in the system sends the message to PubSub. 2) leave it as a string, and make sure your PubSub schema and the BigQuery schema both have STRING type to match the data type, then, during the data modelling process, convert it using the BigQuery function cloud.google.com/bigquery/docs/reference/standard-sql/timestamp_functions#timestamp. Hope that makes sense?

And I would say I prefer the second way because that is the least amount of transformation applied which means less logic and if something goes wrong you know it’s not this conversion process is causing it. Also you don’t always have the option to convert it at source systems.

From GCS to BigQuery? Is this a question specific to this video? If you want to load files from GCS to BQ, a much easier way is to do BQ load, have a look at the docs here cloud.google.com/bigquery/docs/loading-data-cloud-storage-csv. This video is about getting data from PubSub to BigQuery, where the data must already exist in PubSub. The producer Python file is just to send data to PubSub, GCS is not involved in this design.