How can i run multiple queries in dataflow template?

Question

I am performing Mysql to bigquery data migration using jdbc to bigquery template in dataflow.

But while performing "select * from teable1" command on mysql, i also want to insert the selected data to another table in same database for some reason.

How can i perform both select and insert queries in dataflow template? I got error when used semicolon between two queries.

Yes. i got "You have an error in your SQL syntax;" error. I think we cant use select and insert together for union all — Joseph N, Nov 11 '20 at 05:45
The issue should be in your query. The JDBC IO connector simply execute the query, simple or complex, and get the result — guillaume blaquiere, Nov 11 '20 at 08:04

score 0 · Answer 1 · answered Nov 10 '20 at 17:03

0

The Jdbc to Bigquery template will write all data you read to the table specified under "Bigquery output table" (<my-project>:<my-dataset>.<my-table>), so there is no need to write the insert statement.

(The parameter is "outputTable" for gcloud/REST)

answered Nov 10 '20 at 17:03

Peter Kim

1,929
14
22

@Dhairyaa The Jdbc "to Bigquery" template is not for your use-case. The mysql query is only used to extract the data. https://cloud.google.com/dataflow/docs/guides/templates/provided-batch?hl=en_US#java-database-connectivity-jdbc-to-bigquery – Peter Kim Nov 10 '20 at 17:23
Actually, I want to perform incremental load. So for keeping track of migrated data , I am saving there primary key in different table. That is why I want to perform insert in source database – Joseph N Nov 10 '20 at 17:27

score 0 · Answer 2 · answered Nov 25 '20 at 22:36

As @PeterKim mentioned the JDBC to BigQuery termplate could be not the best approach for your use case.

You could try to use that template as reference and modify it to write into MySQL, in this post you will find an implementation about how to make an insert into MYSQL database.

After modifying the pipeline source code you can create a custom template.

How can i run multiple queries in dataflow template?

2 Answers2