Creating cloud sql tables from Dataflow job

user_12992584 0
Viewed 216

There is a pipeline that will copy table data from BigQuery to CloudSql.

Cloud SQL table creation was happening outside dataflow.

Now we need to have the table creation in Dataflow.

I have the tables to be created in GCS bucket as .sql file.

Below is the code snippet which copies table from BQ to sql.

        p.apply(BigQueryIO.readTableRows()
                    .from(source_table)
                    .withTemplateCompatibility()
                    .withoutValidation())
           
            .apply(JdbcIO.<TableRow>write()
                    .withDataSourceConfiguration(
                            JdbcIO.DataSourceConfiguration.create(
                                    "org.postgresql.Driver",
                                    base_url
                            )
                    )
                    .withStatement("INSERT INTO " + target_table.split("\\.")[1] + " VALUES " + insert_query)
                    .withPreparedStatementSetter(new StatementSetter(some_map)));
    p.run();

Is there a way I can execute the .sql file using JDBCIO?

google-cloud-sql apache-beam google-cloud-dataflow java
1 Answers

JdbcIO in Apache Beam can only be used to read from and write to JDBC datasources. Unfortunately, a .sql file cannot be executed using JdbcIO. Because, JdbcIO requires the databases and tables to be created before writing data into them. The same applies to DDL commands like CREATE.

A workaround to automate the table creation would be to use Cloud Build with Dataflow. The end-to-end process could be created as a Cloud Build job that creates the Table by using the cloud client, and then the Dataflow job could be triggered. Please refer to this Medium article for a sample Cloud Build-Dataflow setup.

If this feature is an important element in your pipeline, please raise a feature request with the Apache Beam team on JIRA under the Beam project. To do so, JIRA requires a sign-in.

user_157458840
Related