How to do streaming insert data from one bigquery table to another table after flattening the nested fields?

Question

I have an intermediate table that holds send_timestamp and JSON data in its columns. I'm using dataflow to insert data from pubsub to this intermediate table.

Now my use case is to authenticate the data and flatten the nested JSON in the intermediate table into the relevant columns in my final Bigquery table between a particular timestamp and insert it. I used cloud function but the total messages are huge so it is not working out.

Can someone please recommend a solution and that requires less amount of time to make it work?

Thanks!

guillaume blaquiere · Accepted Answer · 2020-05-07T19:03:57.997

2

Simply use DML. Perform a query like this

insert into DATASET.TARGET_TABLE
select * from DATASET.tmp_table where ....

Your cloud function can run this query and exit. Or wait the end to be sure that the job is over and successful.

EDIT

You can read (and only read) the data into Cloud SQL thanks to federated table.

Like this, you can perform your where close with the data into BigQuery and select only this one which match with the CloudSQL ones!

edited May 07 '20 at 19:03

answered May 02 '20 at 12:17

guillaume blaquiere

66,369
2
47
76

I can't do that. I need to authenticate the data before flattening and inserting to the final table. – Mohsin Mumtaz May 02 '20 at 15:41
What do you mean by "authenticate the data"? – guillaume blaquiere May 03 '20 at 11:45
If the id of the application payload does not match the id in the DB then I want to filter out those – Mohsin Mumtaz May 04 '20 at 04:57
Is a simple `where` condition isn't enough to exclude this kind of unwanted entry? – guillaume blaquiere May 04 '20 at 14:39
unfortunately no, I have to compare it with the key in db – Mohsin Mumtaz May 06 '20 at 13:56
In which db? Cloud SQL db? other db? – guillaume blaquiere May 06 '20 at 19:39
I'm using cloud sql db – Mohsin Mumtaz May 07 '20 at 16:33
1

@MohsinMumtaz, I edited my answer. Is it solve your issue? – guillaume blaquiere May 07 '20 at 19:04

How to do streaming insert data from one bigquery table to another table after flattening the nested fields?

1 Answers1