cloud - 无法从 Data Fusion 连接 Cloud SQL PostgreSQL
问题描述
尝试从 Cloud Data Fusion（同一 VPC 中的两个私有实例，不共享）连接 Cloud SQL PotgreSQL 数据库，如此处所述逐步说明：
https://cloud.google.com/data-fusion/docs/how-to/reading-from-postgresql https://cloud.google.com/data-fusion/docs/how-to/create-private-ip
以此postgres-socket-factory-1.3.0-jar-with-driver-and-dependencies.jar为驱动程序，下载自：
https ://github.com/GoogleCloudPlatform/cloud-sql-jdbc-socket-factory/releases/tag/v1.3.0
测试连接，牧马人像这样被破坏：
但是 Data Fusion Wranger 日志也描述了这个错误：
2021-06-29 21:28:02,226 - WARN [service-http-executor-1147:i.c.w.s.c.AbstractWranglerHandler@210] - Error processing POST /v3/namespaces/system/apps/dataprep/services/service/methods/contexts/default/connections/databases, resulting in a 500 response.
java.lang.ExceptionInInitializerError: null
at com.google.api.services.sqladmin.SQLAdmin$Builder.build(SQLAdmin.java:7339) ~[na:na]
at com.google.cloud.sql.core.CoreSocketFactory.createAdminApiClient(CoreSocketFactory.java:365) ~[na:na]
at com.google.cloud.sql.core.CoreSocketFactory.getInstance(CoreSocketFactory.java:141) ~[na:na]
at com.google.cloud.sql.core.CoreSocketFactory.connect(CoreSocketFactory.java:252) ~[na:na]
at com.google.cloud.sql.postgres.SocketFactory.createSocket(SocketFactory.java:76) ~[na:na]
at org.postgresql.core.PGStream.createSocket(PGStream.java:223) ~[na:na]
at org.postgresql.core.PGStream.<init>(PGStream.java:95) ~[na:na]
at org.postgresql.core.v3.ConnectionFactoryImpl.tryConnect(ConnectionFactoryImpl.java:98) ~[na:na]
at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:213) ~[na:na]
at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:51) ~[na:na]
at org.postgresql.jdbc.PgConnection.<init>(PgConnection.java:223) ~[na:na]
at org.postgresql.Driver.makeConnection(Driver.java:465) ~[na:na]
at org.postgresql.Driver.connect(Driver.java:264) ~[na:na]
at io.cdap.wrangler.service.database.JDBCDriverShim.connect(JDBCDriverShim.java:44) ~[expanded.16249867015644.4.1.86000cc8-2950-4df3-8ded-ef6513642958.jar/:na]
at java.sql.DriverManager.getConnection(DriverManager.java:664) ~[na:1.8.0_292]
at java.sql.DriverManager.getConnection(DriverManager.java:247) ~[na:1.8.0_292]
at io.cdap.wrangler.service.database.DatabaseHandler.loadAndExecute(DatabaseHandler.java:558) [expanded.16249867015644.4.1.86000cc8-2950-4df3-8ded-ef6513642958.jar/:na]
at io.cdap.wrangler.service.database.DatabaseHandler.lambda$listDatabases$4(DatabaseHandler.java:322) [expanded.16249867015644.4.1.86000cc8-2950-4df3-8ded-ef6513642958.jar/:na]
at io.cdap.wrangler.service.common.AbstractWranglerHandler.respond(AbstractWranglerHandler.java:200) ~[expanded.16249867015644.4.1.86000cc8-2950-4df3-8ded-ef6513642958.jar/:na]
at io.cdap.wrangler.service.database.DatabaseHandler.listDatabases(DatabaseHandler.java:316) [expanded.16249867015644.4.1.86000cc8-2950-4df3-8ded-ef6513642958.jar/:na]
at io.cdap.wrangler.service.database.DatabaseHandlercdb75ebbd71058e7f79bb5e06b7f47ea.lambda$listDatabases_Lio_cdap_cdap_api_service_http_HttpServiceRequest_Lio_cdap_cdap_api_service_http_HttpServiceResponder_Ljava_lang_String__V(Unknown Source) [na:na]
at io.cdap.cdap.internal.app.runtime.AbstractContext.execute(AbstractContext.java:560) ~[na:na]
at io.cdap.cdap.internal.app.services.ServiceHttpServer$HandlerDelegatorContext$1.execute(ServiceHttpServer.java:230) ~[na:na]
at io.cdap.cdap.internal.app.runtime.service.http.AbstractHttpHandlerDelegator.execute(AbstractHttpHandlerDelegator.java:66) ~[na:na]
at io.cdap.wrangler.service.database.DatabaseHandlercdb75ebbd71058e7f79bb5e06b7f47ea.listDatabases(Unknown Source) [na:na]
at sun.reflect.GeneratedMethodAccessor128.invoke(Unknown Source) ~[na:na]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_292]
at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_292]
at io.cdap.http.internal.HttpMethodInfo.invoke(HttpMethodInfo.java:87) ~[io.cdap.http.netty-http-1.5.0.jar:na]
at io.cdap.http.internal.HttpDispatcher.channelRead(HttpDispatcher.java:45) ~[io.cdap.http.netty-http-1.5.0.jar:na]
at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:362) ~[io.netty.netty-all-4.1.16.Final.jar:4.1.16.Final]
at io.netty.channel.AbstractChannelHandlerContext.access$600(AbstractChannelHandlerContext.java:38) ~[io.netty.netty-all-4.1.16.Final.jar:4.1.16.Final]
at io.netty.channel.AbstractChannelHandlerContext$7.run(AbstractChannelHandlerContext.java:353) ~[io.netty.netty-all-4.1.16.Final.jar:4.1.16.Final]
at io.netty.util.concurrent.AbstractEventExecutor.safeExecute(AbstractEventExecutor.java:163) ~[io.netty.netty-all-4.1.16.Final.jar:4.1.16.Final]
at io.cdap.http.internal.NonStickyEventExecutorGroup$NonStickyOrderedEventExecutor.run(NonStickyEventExecutorGroup.java:254) ~[io.cdap.http.netty-http-1.5.0.jar:na]
at io.netty.util.concurrent.UnorderedThreadPoolEventExecutor$NonNotifyRunnable.run(UnorderedThreadPoolEventExecutor.java:277) ~[io.netty.netty-all-4.1.16.Final.jar:4.1.16.Final]
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) ~[na:1.8.0_292]
at java.util.concurrent.FutureTask.run(FutureTask.java:266) ~[na:1.8.0_292]
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$201(ScheduledThreadPoolExecutor.java:180) ~[na:1.8.0_292]
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293) ~[na:1.8.0_292]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) ~[na:1.8.0_292]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) ~[na:1.8.0_292]
at java.lang.Thread.run(Thread.java:748) ~[na:1.8.0_292]
Caused by: java.lang.IllegalStateException: You are currently running with version 1.30.9 of google-api-client. You need at least version 1.31.1 of google-api-client to run version 1.31.0 of the Cloud SQL Admin API library.
at com.google.common.base.Preconditions.checkState(Preconditions.java:537) ~[guava-20.0.jar:na]
at com.google.api.client.util.Preconditions.checkState(Preconditions.java:113) ~[google-http-client-1.34.2.jar:1.34.2]
at com.google.api.services.sqladmin.SQLAdmin.<clinit>(SQLAdmin.java:44) ~[na:na]
... 43 common frames omitted
注意Caused by: java.lang.IllegalStateException: You are currently running with version 1.30.9 of google-api-client. You need at least version 1.31.1 of google-api-client to run version 1.31.0 of the Cloud SQL Admin API library.
更新：还尝试使用从具有相同结果的来源生成的 fat-jar。
使用管道工作室中的相同驱动程序测试连接我收到此错误：
2021-06-30 08:39:25,091 - ERROR [studio-http-executor-49:i.c.p.d.b.s.AbstractDBSource@140] - Exception while performing getSchema
org.postgresql.util.PSQLException: The connection attempt failed.
at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:315) ~[na:na]
at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:51) ~[na:na]
at org.postgresql.jdbc.PgConnection.<init>(PgConnection.java:223) ~[na:na]
at org.postgresql.Driver.makeConnection(Driver.java:465) ~[na:na]
at org.postgresql.Driver.connect(Driver.java:264) ~[na:na]
at io.cdap.plugin.db.JDBCDriverShim.connect(JDBCDriverShim.java:60) ~[database-commons-1.5.3.jar:na]
at java.sql.DriverManager.getConnection(DriverManager.java:664) ~[na:1.8.0_292]
at java.sql.DriverManager.getConnection(DriverManager.java:208) ~[na:1.8.0_292]
at io.cdap.plugin.db.batch.source.AbstractDBSource.getConnection(AbstractDBSource.java:219) [database-commons-1.5.3.jar:na]
at io.cdap.plugin.db.batch.source.AbstractDBSource.getSchema(AbstractDBSource.java:132) [database-commons-1.5.3.jar:na]
at io.cdap.plugin.db.batch.source.AbstractDBSource.configurePipeline(AbstractDBSource.java:113) [database-commons-1.5.3.jar:na]
at io.cdap.plugin.cloudsql.postgres.CloudSQLPostgreSQLSource.configurePipeline(CloudSQLPostgreSQLSource.java:57) [1625042353080-0/:na]
at io.cdap.cdap.etl.spec.PipelineSpecGenerator.configureStage(PipelineSpecGenerator.java:306) [cdap-etl-core-6.4.1.jar:na]
at io.cdap.cdap.datapipeline.service.ValidationHandler.validateStage(ValidationHandler.java:161) [expanded.16249866968976.4.1.73eaa383-b419-44a0-be67-36b2647bbaa0.jar/:na]
at io.cdap.cdap.datapipeline.service.ValidationHandler4b0e8282e7c248900dbfd7c39c0c3cc8.lambda$validateStage_Lio_cdap_cdap_api_service_http_HttpServiceRequest_Lio_cdap_cdap_api_service_http_HttpServiceResponder_Ljava_lang_String__V(Unknown Source) [na:na]
at io.cdap.cdap.internal.app.services.ServiceHttpServer$HandlerDelegatorContext$1.lambda$execute$0(ServiceHttpServer.java:228) ~[na:na]
at io.cdap.cdap.internal.app.runtime.AbstractContext.lambda$execute$3(AbstractContext.java:515) ~[na:na]
at io.cdap.cdap.data2.transaction.Transactions$CacheBasedTransactional.finishExecute(Transactions.java:224) ~[na:na]
at io.cdap.cdap.data2.transaction.Transactions$CacheBasedTransactional.execute(Transactions.java:211) ~[na:na]
at io.cdap.cdap.internal.app.runtime.AbstractContext.execute(AbstractContext.java:512) ~[na:na]
at io.cdap.cdap.internal.app.runtime.AbstractContext.execute(AbstractContext.java:500) ~[na:na]
at io.cdap.cdap.internal.app.services.ServiceHttpServer$HandlerDelegatorContext$1.execute(ServiceHttpServer.java:228) ~[na:na]
at io.cdap.cdap.internal.app.runtime.service.http.AbstractHttpHandlerDelegator.execute(AbstractHttpHandlerDelegator.java:66) ~[na:na]
at io.cdap.cdap.datapipeline.service.ValidationHandler4b0e8282e7c248900dbfd7c39c0c3cc8.validateStage(Unknown Source) [na:na]
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_292]
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_292]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_292]
at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_292]
at io.cdap.http.internal.HttpMethodInfo.invoke(HttpMethodInfo.java:87) ~[io.cdap.http.netty-http-1.5.0.jar:na]
at io.cdap.http.internal.HttpDispatcher.channelRead(HttpDispatcher.java:45) ~[io.cdap.http.netty-http-1.5.0.jar:na]
at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:362) ~[io.netty.netty-all-4.1.16.Final.jar:4.1.16.Final]
at io.netty.channel.AbstractChannelHandlerContext.access$600(AbstractChannelHandlerContext.java:38) ~[io.netty.netty-all-4.1.16.Final.jar:4.1.16.Final]
at io.netty.channel.AbstractChannelHandlerContext$7.run(AbstractChannelHandlerContext.java:353) ~[io.netty.netty-all-4.1.16.Final.jar:4.1.16.Final]
at io.netty.util.concurrent.AbstractEventExecutor.safeExecute(AbstractEventExecutor.java:163) ~[io.netty.netty-all-4.1.16.Final.jar:4.1.16.Final]
at io.cdap.http.internal.NonStickyEventExecutorGroup$NonStickyOrderedEventExecutor.run(NonStickyEventExecutorGroup.java:254) ~[io.cdap.http.netty-http-1.5.0.jar:na]
at io.netty.util.concurrent.UnorderedThreadPoolEventExecutor$NonNotifyRunnable.run(UnorderedThreadPoolEventExecutor.java:277) ~[io.netty.netty-all-4.1.16.Final.jar:4.1.16.Final]
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) ~[na:1.8.0_292]
at java.util.concurrent.FutureTask.run(FutureTask.java:266) ~[na:1.8.0_292]
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$201(ScheduledThreadPoolExecutor.java:180) ~[na:1.8.0_292]
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293) ~[na:1.8.0_292]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) ~[na:1.8.0_292]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) ~[na:1.8.0_292]
at java.lang.Thread.run(Thread.java:748) ~[na:1.8.0_292]
Caused by: java.net.SocketTimeoutException: connect timed out
at java.net.PlainSocketImpl.socketConnect(Native Method) ~[na:1.8.0_292]
at java.net.AbstractPlainSocketImpl.doConnect(AbstractPlainSocketImpl.java:350) ~[na:1.8.0_292]
at java.net.AbstractPlainSocketImpl.connectToAddress(AbstractPlainSocketImpl.java:206) ~[na:1.8.0_292]
at java.net.AbstractPlainSocketImpl.connect(AbstractPlainSocketImpl.java:188) ~[na:1.8.0_292]
at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:392) ~[na:1.8.0_292]
at java.net.Socket.connect(Socket.java:607) ~[na:1.8.0_292]
at org.postgresql.core.PGStream.createSocket(PGStream.java:231) ~[na:na]
at org.postgresql.core.PGStream.<init>(PGStream.java:95) ~[na:na]
at org.postgresql.core.v3.ConnectionFactoryImpl.tryConnect(ConnectionFactoryImpl.java:98) ~[na:na]
at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:213) ~[na:na]
... 42 common frames omitted
解决方案
使用 1.2 及以下版本的 postgres-socket-factory 将修复由@Anthanh Pham Trinh 确认的 Google API 库版本问题。
问题中提到的牧马人测试连接问题是一个已知问题，可以在https://cdap.atlassian.net/browse/CDAP-17973中跟踪对此的修复。
编辑：20210729
要使用不同的驱动程序，而不是 Data Fusion 让您下载的默认 v1.0.16。您可以在此 github 链接中下载不同版本的 cloud-sql-jdbc 驱动程序（包括 1.2 版）。
下载 1.2 版驱动程序时：
开放云数据融合实例
点击“集线器”
点击“驱动程序”
搜索“CloudSQL PostgreSQL JDBC Driver”选择它
点击“部署”
上传您在cloud-sql-jdbc 驱动程序 github 链接中下载的驱动程序。在这个例子中，我下载了 postgres-socket-factory-1.2.0-jar-with-driver-and-dependencies.jar
单击“下一步”，驱动程序配置将自动填充，因为它将使用已上传驱动程序的详细信息。
点击“完成”
上传完成后，会显示上传的驱动版本。
您也可以通过转到“牧马人”来检查这一点
在“牧马人”中时，单击“+ 添加连接”
点击“数据库”
这将显示所有可用的数据库连接，并且 PostgreSQL 版本 1.2 应该可用。
推荐阅读
- django - urls.py 中的 Django2 AttributeError
- wordpress - Wordpress 动态元框输入
- python - Python 和 Matplotlib：如何绘制一组只知道极值的线段？
- android - 发布 MainThread Runnables 内存泄漏安全 Android
- c++ - 弃用 C++11 中的类和函数
- javascript - javascript数组循环到表
- hadoop - Windows 上的 Hadoop 安装：无法为您的平台加载 native-hadoop 库...在适用的情况下使用内置 java 类
- node.js - 使用 nodejs 在 RHEL linux 上安装连接 Web 服务器
- javascript - 有没有办法改变我在 find() 函数中比较的字段的值？
- docker - 在 docker build 期间重新加载 Nginx