小贝子编程

如何从Google DataFlow连接到Cloud SQL



我正试图使用beam java SDK和Google Dataflow创建一个管道任务，将数据从Cloud SQL移动到Elastic搜索

我创建了以下类的主方法：

public static void main(String[] args) throws Exception{
DataflowPipelineOptions options = PipelineOptionsFactory.as(DataflowPipelineOptions.class);
options.setProject("staging");      
options.setTempLocation("gs://csv_to_sql_staging/temp"); 
options.setRunner(DataflowRunner.class);        
options.setGcpTempLocation("gs://csv_to_sql_staging/temp");
options.setUsePublicIps(false);         
options.setJobName("tamer-new");
options.setSubnetwork("regions/us-central1/subnetworks/new-network");
final List<String> SCOPES = Arrays.asList(
"https://www.googleapis.com/auth/cloud-platform",
"https://www.googleapis.com/auth/devstorage.full_control",
"https://www.googleapis.com/auth/userinfo.email",
"https://www.googleapis.com/auth/datastore",
"https://www.googleapis.com/auth/sqlservice.admin",
"https://www.googleapis.com/auth/pubsub");
options.setGcpCredential(ServiceAccountCredentials.fromStream(new ElasticSearchIO().getClass().getResourceAsStream("/staging-b648da5d2b9b.json")).createScoped(SCOPES));            options.setServiceAccount("data-flow@staging.iam.gserviceaccount.com");

Pipeline p = Pipeline.create(options);

p.begin();

PCollection < List < String >> rows = p.apply(JdbcIO. < List < String >> read().withQuery("select u.id, u.name from user_table").withDataSourceConfiguration(JdbcIO.DataSourceConfiguration.create("com.mysql.jdbc.Driver", "jdbc:mysql://google/nameDB_new?cloudSqlInstance=staging:europe-west1:sql-staging-instance&socketFactory=com.google.cloud.sql.mysql.SocketFactory&useUnicode=true&characterEncoding=UTF-8&user=user&password=password&useSSL=false")).withRowMapper(new RowMapper < List < String >> () {
@Override public List < String > mapRow(ResultSet resultSet) throws Exception {
List < String > addRow = new ArrayList < String > ();

for (int i = 1; i <= resultSet.getMetaData().getColumnCount(); i++) {
addRow.add(i - 1, String.valueOf(resultSet.getObject(i)));
}
//LOG.info(String.join(",", addRow));
return addRow;
}
})
.withCoder(ListCoder.of(StringUtf8Coder. < Object > of ()))
);
Write w = ElasticsearchIO.write().withConnectionConfiguration(
ElasticsearchIO.ConnectionConfiguration.create(new String[] {
"https://host:9243"
}, "user-temp", "String").withUsername("elastic").withPassword("password")
);
rows.apply(w.compose(new SerializableFunction() {
@Override public Object apply(Object input) {
// TODO Auto-generated method stub
return input;
}
}));

p.run().waitUntilFinish();
}

下面是pom.xml文件：

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.harmonica.dataflow</groupId>
<artifactId>com-harmonica-dataflow</artifactId>
<version>0.0.1-SNAPSHOT</version>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<maven-compiler-plugin.version>3.7.0</maven-compiler-plugin.version>
<exec-maven-plugin.version>1.6.0</exec-maven-plugin.version>
<slf4j.version>1.7.25</slf4j.version>
<beam.version>2.19.0</beam.version>
</properties>
<repositories>
<repository>
<id>ossrh.snapshots</id>
<name>Sonatype OSS Repository Hosting</name>
<url>https://oss.sonatype.org/content/repositories/snapshots/</url>
<releases>
<enabled>false</enabled>
</releases>
<snapshots>
<enabled>true</enabled>
</snapshots>
</repository>
</repositories>
<build>
<plugins>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-compiler-plugin</artifactId>
<version>${maven-compiler-plugin.version}</version>
<configuration>
<source>1.8</source>
<target>1.8</target>
</configuration>
</plugin>
</plugins>
<pluginManagement>
<plugins>
<plugin>
<groupId>org.codehaus.mojo</groupId>
<artifactId>exec-maven-plugin</artifactId>
<version>${exec-maven-plugin.version}</version>
<configuration>
<cleanupDaemonThreads>false</cleanupDaemonThreads>
</configuration>
</plugin>
</plugins>
</pluginManagement>
</build>
<dependencies>
<!--  Beam Lib -->
<dependency>
<groupId>org.apache.beam</groupId>
<artifactId>beam-sdks-java-core</artifactId>
<version>${beam.version}</version>
</dependency>

<dependency>
<groupId>org.apache.beam</groupId>
<artifactId>beam-runners-google-cloud-dataflow-java</artifactId>
<version>${beam.version}</version>        
</dependency>

<dependency>
<groupId>org.apache.beam</groupId>
<artifactId>beam-sdks-java-io-elasticsearch</artifactId>
<version>${beam.version}</version>  
</dependency>
<dependency>
<groupId>org.apache.beam</groupId>
<artifactId>beam-sdks-java-io-jdbc</artifactId>
<version>${beam.version}</version>
</dependency>



<dependency>
<groupId>org.apache.beam</groupId>
<artifactId>beam-sdks-java-io-google-cloud-platform</artifactId>
<version>${beam.version}</version>

</dependency>

<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.19</version>           
</dependency>
<dependency>
<groupId>com.google.cloud.sql</groupId>
<artifactId>mysql-socket-factory-connector-j-8</artifactId>
<version>1.0.15</version>
</dependency>

<!-- slf4j API frontend binding with JUL backend -->
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-api</artifactId>
<version>${slf4j.version}</version>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-jdk14</artifactId>
<version>${slf4j.version}</version>
</dependency>
</dependencies>
</project>

当我执行这个命令时：

man exec mvn compile exec:java -Dexec.mainClass=com.dataflow.ElasticSearchIO

工作进程已成功启动，但无法连接到云SQL:甚至认为我已经完成了流动：

  • 我创建了一个拥有项目所有者访问权限的服务帐户，并将其传递给runner选项
  • 我创建了一个VPC网络，名称为new network，IP范围为190.10.0.0/16，并将其分配给管道选项，然后在云SQL中缩减该范围

但是我仍然得到这个错误：

来自worker的错误消息：java.lang.RuntimeException：org.apache.beam.sdk.util.UserCodeException：java.sql.sql异常：无法创建PoolableConnectionFactory(通信链路故障最后一个成功发送到服务器的数据包是0毫秒以前驱动程序未从服务器接收到任何数据包。)org.apache.beam.runners.dataflow.worker.IntrinsicMapTaskExecutiorFactory$1.typedApply(IntrinsicMappingTaskExecutiorFactory.java:194)org.apache.beam.runners.dataflow.worker.IntrinsicMapTaskExecutiorFactory$1.typedApply(IntrinsicMappingTaskExecutiorFactory.java:165)org.apache.beam.runners.dataflow.worker.graph.Networks$TypeSafeNodeFunction.apply(Networks.java:63)org.apache.beam.runners.dataflow.worker.graph.Networks$TypeSafeNodeFunction.apply(Networks.java:50)org.apache.beam.runners.dataflow.worker.graphic.Networks.replacedDirectedNetworkNodes(Networks.java:87)org.apache.beam.runners.dataflow.worker.IntrinsicMapTaskExecutorFactory.create(IntrinsicMappingTaskExecutiorFactory.java:125)org.apache.beam.runners.dataflow.worker.BatchDataflowWorker.doWork(BatchDataflowWorker.java:352)org.apache.beam.runners.dataflow.worker.BatchDataflowWorker.getAndPerformWork(BatchDataflowWorker.java:305)org.apache.beam.runners.dataflow.worker.DataflowBatchWorkerHarness$WorkerThread.doWork(DataflowBatchWorkerHarness.java:140)org.apache.beam.runners.dataflow.worker.DataflowBatchWorkerHarness$WorkerThread.call(DataflowBatchWorkerHarness.java:120)org.apache.beam.runners.dataflow.worker.DataflowBatchWorkerHarness$WorkerThread.call(DataflowBatchWorkerHarness.java:107)java.util.concurrent.FFutureTask.run(FutureTask.java:266)java.util.concurrent.ThreadPoolExecutiator.runWorker(ThreadPoolExecutiator.java:1149)java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)java.lang.Thread.run(Thread.java:748)原因：org.apache.beam.sdk.util.UserCodeException：java.sql.sql异常：无法创建PoolableConnectionFactory(通信链路故障最后一个成功发送到服务器的数据包是0毫秒以前驱动程序未从服务器接收到任何数据包。)org.apache.beam.sdk.util.UserCodeException.wrapp(UserCodeException.java:34)org.apache.beam.sdk.io.JdbcIO$ReadFn$DoFnInvoker.invokeSetup(未知来源)org.apache.beam.runners.dataflow.worker.DoFnInstanceManagers$CurrentQueueInstanceManager.destrializeCopy(DoFnInstanceManagers.java:80)org.apache.beam.runners.dataflow.worker.DoFnInstanceManagers$CurrentQueueInstanceManager.peek(DoFnInstanceManagers.java:62)org.apache.beam.runners.dataflow.worker.UserParDoFnFactory.create(UserParDofFactory.java:95)org.apache.beam.runners.dataflow.worker.DefaultParDoFnFactory.create(DefaultParDofFactory.java:75)org.apache.beam.runners.dataflow.worker.IntrinsicMapTaskExecutorFactory.createParDoOperation(IntrinsicMappingTaskExecutiorFactory.java:264)org.apache.beam.runners.dataflow.worker.IntrinsicMapTaskExecutorFactory.access$000(IntrinsicMappingTaskExecutiorFactory.java:86)org.apache.beam.runners.dataflow.worker.IntrinsicMapTaskExecutiorFactory$1.typedApply(IntrinsicMappingTaskExecutiorFactory.java:183)…还有14个原因：java.sql.sql异常：无法创建PoolableConnectionFactory(通信链路故障最后一个数据包成功发送到服务器的时间是0毫秒前。驾驶员未从服务器接收到任何数据包。)org.apache.commons.dbcp2.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:735)org.apache.commons.dbcp2.BasicDataSource.createDataSource(BasicDataSource.java:605)org.apache.commons.dbcp2.BasicDataSource.getConnection(BasicDataSource.java:809)org.apache.beam.sdk.io.jdbc.JdbcIO$ReadFn.setup(JdbcIO.java:881)原因：com.mysql.cj.jdbc.exceptions.CommunicationsException：通信链路故障最后一个数据包成功发送到服务器在0毫秒前。驱动程序未接收到任何数据包从服务器。com.mysql.cj.jdbc.exceptions.SQLError.createCommunicationsException(SQLError.java:174)com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLException_Mapping.java:64)com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:836)com.mysql.cj.jdbc.ConnectionImpl.(ConnectionImpl.java:456)com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:246)com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java：197)org.apache.commons.dbcp2.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:53)org.apache.commons.dbcp2.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:355)org.apache.commons.dbcp2.BasicDataSource.validateConnectionFactory(BasicDataSource.java:116)org.apache.commons.dbcp2.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:731)org.apache.commons.dbcp2.BasicDataSource.createDataSource(BasicDataSource.java:605)org.apache.commons.dbcp2.BasicDataSource.getConnection(BasicDataSource.java:809)org.apache.beam.sdk.io.jdbc.JdbcIO$ReadFn.setup(JdbcIO.java:881)org.apache.beam.sdk.io.JdbcIO$ReadFn$DoFnInvoker.invokeSetup(未知来源)org.apache.beam.runners.dataflow.worker.DoFnInstanceManagers$CurrentQueueInstanceManager.destrializeCopy(DoFnInstanceManagers.java:80)org.apache.beam.runners.dataflow.worker.DoFnInstanceManagers$CurrentQueueInstanceManager.peek(DoFnInstanceManagers.java:62)org.apache.beam.runners.dataflow.worker.UserParDoFnFactory.create(UserParDofFactory.java:95)org.apache.beam.runners.dataflow.worker.DefaultParDoFnFactory.create(DefaultParDofFactory.java:75)org.apache.beam.runners.dataflow.worker.IntrinsicMapTaskExecutorFactory.createParDoOperation(IntrinsicMappingTaskExecutiorFactory.java:264)org.apache.beam.runners.dataflow.worker.IntrinsicMapTaskExecutorFactory.access$000(IntrinsicMappingTaskExecutiorFactory.java:86)org.apache.beam.runners.dataflow.worker.IntrinsicMapTaskExecutiorFactory$1.typedApply(IntrinsicMappingTaskExecutiorFactory.java:183)org.apache.beam.runners.dataflow.worker.IntrinsicMapTaskExecutiorFactory$1.typedApply(IntrinsicMappingTaskExecutiorFactory.java:165)org.apache.beam.runners.dataflow.worker.graph.Networks$TypeSafeNodeFunction.apply(Networks.java:63)org.apache.beam.runners.dataflow.worker.graph.Networks$TypeSafeNodeFunction.apply(Networks.java:50)org.apache.beam.runners.dataflow.worker.graphic.Networks.replacedDirectedNetworkNodes(Networks.java:87)org.apache.beam.runners.dataflow.worker.IntrinsicMapTaskExecutorFactory.create(IntrinsicMappingTaskExecutiorFactory.java:125)org.apache.beam.runners.dataflow.worker.BatchDataflowWorker.doWork(BatchDataflowWorker.java:352)org.apache.beam.runners.dataflow.worker.BatchDataflowWorker.getAndPerformWork(BatchDataflowWorker.java:305)org.apache.beam.runners.dataflow.worker.DataflowBatchWorkerHarness$WorkerThread.doWork(DataflowBatchWorkerHarness.java:140)org.apache.beam.runners.dataflow.worker.DataflowBatchWorkerHarness$WorkerThread.call(DataflowBatchWorkerHarness.java:120)org.apache.beam.runners.dataflow.worker.DataflowBatchWorkerHarness$WorkerThread.call(DataflowBatchWorkerHarness.java:107)java.util.concurrent.FFutureTask.run(FutureTask.java:266)java.util.concurrent.ThreadPoolExecutiator.runWorker(ThreadPoolExecutiator.java:1149)java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)java.lang.Thread.run(Thread.java:748)原因：com.mysql.cj.exceptions.CJCommunicationsException：通信链接failure最后一个成功发送到服务器的数据包是0毫秒前。驱动程序未从服务器sun.reflect.NativeConstructorAccessorImpl.newInstance0(本机方法)sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessor Impl.java:62)sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessor Impl.java:45)java.lang.reflect.CConstructor.newInstance(Constructor.java:423)com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:61)com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:105)com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:151)com.mysql.cj.exceptions.ExceptionFactory.createCommunicationsException(ExceptionFactory.java:167)com.mysql.cj.protocol.a.NativeSocketConnection.connect(NativeSocketConnection.java:91)com.mysql.cj.NativeSession.connect(NativeSession.java:144)com.mysql.cj.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:956)com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:826)…还有32个原因：java.net.ConnectException：连接超时(连接超时)java.net.PlainSocketImpl.socketConnect(本机方法)java.net.AbstractPlainSocketImpl.doConnect(AbstractPlasinSocketimpl.java:350)java.net.AbstractPlainSocketImpl.connectToAddress(AbstractPlasinSocketimpl.java:206)java.net.AbstractPlainSocketImpl.connect(AbstractPlasinSocketimpl.java:188)java.net.SocksSocketImpl.connect(SocksSocketImpl.java:392)java.net.Socket.connect(Socket.java:589)sun.security.ssl.SSLSocketImpl.connect(SSLSocketImpl.java:673)sun.security.ssl.BaseSSLSocketImpl.connect(BaseSSLSocketImpl.java:173)com.google.cloud.sql.core.CoreSocketFactory.createSslSocket(CoreSocketFactory.java:233)com.google.cloud.sql.core.CoreSocketFactory.connect(CoreSocketFactory.java:185)com.google.cloud.sql.mysql.SocketFactory.connect(SocketFactory.java:48)com.google.cloud.sql.mysql.SocketFactory.connect(SocketFactory.java:38)com.mysql.cj.protocol.a.NativeSocketConnection.connect(NativeSocketConnection.java:65)…再增加35个

Plz如有任何帮助，我们将不胜感激！提前感谢

您可以使用以下代码来建立连接：

管道p=管道.create(选项)；

//Increase pool size based on your records
ComboPooledDataSource dataSource = new ComboPooledDataSource();
dataSource.setDriverClass("com.mysql.jdbc.Driver");
dataSource.setJdbcUrl(
"jdbc:mysql://google/test?cloudSqlInstance=dataflowtest-:us-central1:sql-test&socketFactory=com.google.cloud.sql.mysql.SocketFactory");
dataSource.setUser("root");
dataSource.setPassword("root");
dataSource.setMaxPoolSize(10);
dataSource.setInitialPoolSize(6);
JdbcIO.DataSourceConfiguration config = JdbcIO.DataSourceConfiguration.create(dataSource);
// ADD rewriteBatchedStatements=true to improve write speed"
PCollection<KV<String, String>> sqlResult = p.apply(JdbcIO.<KV<String, String>>read()
.withDataSourceConfiguration(config)
.withQuery("select * from test_table").withCoder(KvCoder.of(StringUtf8Coder.of(), StringUtf8Coder.of()))
.withRowMapper(new JdbcIO.RowMapper<KV<String, String>>() {
private static final long serialVersionUID = 1L;
public KV<String, String> mapRow(ResultSet resultSet) throws Exception {
return KV.of(resultSet.getString(1), resultSet.getString(2));
}
}));

在pom.xml 中添加以下依赖项

<dependency>
<groupId>org.apache.beam</groupId>
<artifactId>beam-sdks-java-io-jdbc</artifactId>
<version>2.17.0</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.25</version>
</dependency>
<dependency>
<groupId>com.google.cloud.sql</groupId>
<artifactId>mysql-socket-factory</artifactId>
<version>1.0.0</version>
</dependency>

这应该有效。。

如果可能，请尝试以下sql连接代码：

connection = connectToCloudSql(map.get(LiteralConstant.URL.toString()),
map.get(LiteralConstant.USERNAME.toString()), map.get(LiteralConstant.PASSWORD.toString()));

然后使用以下代码从sql连接中获取结果：

statement = connection.prepareCall("query"); 
statement.execute();
resultSet = statement.getResultSet();
ResultSetMetaData rsmd = resultSet.getMetaData();
int count = rsmd.getColumnCount();
if(!resultSet.next() || count < 1)
throw new ConnectionFailureException("Failed to connect to Cloud SQL");
for (int k = 1; k <= count; k++) {
row.set(rsmd.getColumnName(k), resultSet.getString(k));
}

在PCollection中获得以上结果注意：不要忘记启用云sql api和云sql管理api

Maven依赖项：

<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.25</version>
</dependency>
<dependency>
<groupId>com.google.cloud.sql</groupId>
<artifactId>mysql-socket-factory</artifactId> <!-- mysql-socket-factory-connector-j-6 if using 6.x.x -->
<version>1.0.0</version>
</dependency>

上面的这段代码在我的情况下起了作用。如果这个解决方案对你有效，请告诉我。

相关内容

  • 没有找到相关文章

最新更新


热门标签：

javascript python java c# php android html jquery c++ css ios sql mysql arrays asp.net json python-3.x ruby-on-rails .net sql-server django objective-c excel regex ruby linux ajax iphone xml vba spring asp.net-mvc database wordpress string postgresql wpf windows xcode bash git oracle list vb.net multithreading eclipse algorithm macos powershell visual-studio image forms numpy scala function api selenium