Cannot connect to cloud sql from cloud dataflow transform

0
votes

I am unable to connect to cloud SQL from inside a custom DoFn while running in cloud dataflow. The errors that show up in the log are:

  • Connecting to Cloud SQL instance [] via ssl socket.
  • [Docbuilder-worker-exception]: com.zaxxer.hikari.pool.HikariPool$PoolInitializationException: Failed to initialize pool: Could not create connection to database server.

The same code and config work fine when connecting to cloud sql from the appenginer handle.

I have explicitly given the compute engine service account - [email protected] - the Cloud SQL client, Cloud SQL viewer and Editor roles.

Any help to troubleshoot this is greatly appreciated!

2
javagoogle-cloud-dataflowgoogle-cloud-sql
It looks like you are using the Cloud SQL JDBC Socket Factory. Can you provide more of the error that you encountered and the snippet of code used to create your connection pool? - kurtisvg

2 Answers

0
votes

To connect to Cloud SQL from external applications there are some methods that could follow in the document How to connect to Cloud SQL from external applications[1] you can find the alternatives and the steps to achieve your goal.

[1]https://cloud.google.com/sql/docs/postgres/connect-external-app

0
votes

I've also run into a lot of issues when trying to use connection pooling with cloud dataflow to cloud sql with custom DoFn. Now I do not remember if my error was the same as yours, but my solution was to create an @Setup method in the DoFn class like this:

static class ProcessDatabaseEvent extends DoFn<String, String> {
@Setup
public void createConnectionPool() throws IOException {
  final Properties properties = new Properties();
  properties.load(Thread.currentThread().getContextClassLoader().getResourceAsStream("config.properties"));
  final String JDBC_URL = properties.getProperty("jdbc.url");
  final String JDBC_USER = properties.getProperty("jdbc.username");
  final String JDBC_PASS = properties.getProperty("jdbc.password");

  final HikariConfig config = new HikariConfig();
  config.setMinimumIdle(5);
  config.setMaximumPoolSize(50);
  config.setConnectionTimeout(10000);
  config.setIdleTimeout(600000);
  config.setMaxLifetime(1800000);
  config.setJdbcUrl(JDBC_URL);
  config.setUsername(JDBC_USER);
  config.setPassword(JDBC_PASS);

  pool = new HikariDataSource(config);
}

@ProcessElement
public void processElement(final ProcessContext context) throws IOException, SQLException {
//Your DoFn code here...
}