FT实现逆地理编码&报错处理

最近遇到一个逆地理编码的需求（有经纬度数据需要转化成省市县数据），确定采用高德的坐标系（还有百度坐标系、大地坐标系等其他坐标系），因此借用高德的逆地理编码接口进行转换（个人日限制30万次），因采用FT来做，遇到一点问题特此记录。

一、编码任务

为求稳定，这次采用逐次调用，参考文档地理/逆地理编码。完成编码需申请成为高德个人开发者(免费），在高德控制台管理Key页面创建新应用并生成专属key。

1. FT流程

表输入----HTTP----JSON----表输出

表输出：

SELECT * FROM
(
select
re_id,
ROW_NUMBER() over(ORDER BY re_id) AS NUM,
re_time,
vin_id,
substring (longitude,1,10) longitude,
substring (latitude,1,10) latitude,
concat('https://restapi.amap.com/v3/geocode/regeo?output=json&location=',substring (longitude,1,10),',',substring (latitude,1,10),'&key=************************&radius=1000&extensions=base') as url,
upload_time
 from dw.car_gps
WHERE province is null
) car
WHERE NUM > 70000

**********************替换成自己的key
HTTP：



JSON:

字段值路径：

$.regeocode.addressComponent.province
$.regeocode.addressComponent.city
$.regeocode.addressComponent.district

2. 加速调取

利用表输入的sql：sql ROW_NUMBER() over(ORDER BY re_id) AS NUM把数据分组，在多个任务中并行调取。
如需并行，FT需设置：

二、问题解决

1. 后台报错连接超时

java.net.ConnectException: 连接超时 (Connection timed out)

1.1 错误详情

错误详情：com.fr.finetube.base.exp.work.step.EventHandleFailureException: Step HTTP Client request url https://restapi.amap.com/v3/geocode/regeo?output=json&location=121.210542,31.2670332&key=***********************&radius=1000&extensions=base failed.
        at com.fr.finetube.core.work.step.component.query.HttpClientStep.doWork(HttpClientStep.java:209)
        at com.fr.finetube.core.work.step.component.AbstractNamedStep.onEvent(AbstractNamedStep.java:176)
        at com.fr.finetube.core.work.task.thread.runner.impl.SimpleProcessRunner.processStep(SimpleProcessRunner.java:132)
        at com.fr.finetube.core.work.task.thread.runner.impl.SimpleProcessRunner.processStep(SimpleProcessRunner.java:136)
        at com.fr.finetube.core.work.task.thread.runner.impl.SimpleProcessRunner.process(SimpleProcessRunner.java:68)
        at com.fr.finetube.core.work.task.thread.CronRepeatThread.run(CronRepeatThread.java:50)
Caused by: org.apache.http.conn.HttpHostConnectException: Connect to restapi.amap.com:443 [restapi.amap.com/59.82.14.114] failed: 连接超时 (Connection timed out)
        at org.apache.http.impl.conn.DefaultHttpClientConnectionOperator.connect(DefaultHttpClientConnectionOperator.java:159)
        at org.apache.http.impl.conn.PoolingHttpClientConnectionManager.connect(PoolingHttpClientConnectionManager.java:373)
        at org.apache.http.impl.execchain.MainClientExec.establishRoute(MainClientExec.java:394)
        at org.apache.http.impl.execchain.MainClientExec.execute(MainClientExec.java:237)
        at org.apache.http.impl.execchain.ProtocolExec.execute(ProtocolExec.java:185)
        at org.apache.http.impl.execchain.RetryExec.execute(RetryExec.java:89)
        at org.apache.http.impl.execchain.RedirectExec.execute(RedirectExec.java:110)
        at org.apache.http.impl.client.InternalHttpClient.doExecute(InternalHttpClient.java:185)
        at org.apache.http.impl.client.CloseableHttpClient.execute(CloseableHttpClient.java:83)
        at com.fr.finetube.base.utils.HttpClientUtils.httpResponseGet(HttpClientUtils.java:208)
        at com.fr.finetube.core.work.step.component.query.HttpClientStep.lambda$getHttpClientMethod$4(HttpClientStep.java:374)
        at com.fr.finetube.core.work.step.component.query.HttpClientStep.doWork(HttpClientStep.java:173)
        ... 5 more
Caused by: java.net.ConnectException: 连接超时 (Connection timed out)
        at java.net.PlainSocketImpl.socketConnect(Native Method)
        at java.net.AbstractPlainSocketImpl.doConnect(AbstractPlainSocketImpl.java:350)
        at java.net.AbstractPlainSocketImpl.connectToAddress(AbstractPlainSocketImpl.java:206)
        at java.net.AbstractPlainSocketImpl.connect(AbstractPlainSocketImpl.java:188)
        at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:392)
        at java.net.Socket.connect(Socket.java:589)
        at org.apache.http.conn.ssl.SSLConnectionSocketFactory.connectSocket(SSLConnectionSocketFactory.java:339)
        at org.apache.http.impl.conn.DefaultHttpClientConnectionOperator.connect(DefaultHttpClientConnectionOperator.java:142)
        ... 16 more

1.2 解决

1.3 解决后现象

采用异常处理后，每次任务失败会显示为暂停，并间隔重启任务进行编码（曲线救国了属于是）。

2. 后台报错SSL信号中断

javax.net.ssl.SSLHandshakeException: Remote host closed connection during handshake

2.1 错误详情

错误详情：com.fr.finetube.base.exp.work.step.EventHandleFailureException: Step HTTP Client request url https://restapi.amap.com/v3/geocode/regeo?output=json&location=120.636936,33.0491199&key=******************************&radius=1000&extensions=base failed.
        at com.fr.finetube.core.work.step.component.query.HttpClientStep.doWork(HttpClientStep.java:209)
        at com.fr.finetube.core.work.step.component.AbstractNamedStep.onEvent(AbstractNamedStep.java:176)
        at com.fr.finetube.core.work.task.thread.runner.impl.SimpleProcessRunner.processStep(SimpleProcessRunner.java:132)
        at com.fr.finetube.core.work.task.thread.runner.impl.SimpleProcessRunner.processStep(SimpleProcessRunner.java:136)
        at com.fr.finetube.core.work.task.thread.runner.impl.SimpleProcessRunner.process(SimpleProcessRunner.java:68)
        at com.fr.finetube.core.work.task.thread.SelfRepeatThread.run(SelfRepeatThread.java:88)
Caused by: javax.net.ssl.SSLHandshakeException: Remote host closed connection during handshake
        at sun.security.ssl.SSLSocketImpl.readRecord(SSLSocketImpl.java:1002)
        at sun.security.ssl.SSLSocketImpl.performInitialHandshake(SSLSocketImpl.java:1385)
        at sun.security.ssl.SSLSocketImpl.startHandshake(SSLSocketImpl.java:1413)
        at sun.security.ssl.SSLSocketImpl.startHandshake(SSLSocketImpl.java:1397)
        at org.apache.http.conn.ssl.SSLConnectionSocketFactory.createLayeredSocket(SSLConnectionSocketFactory.java:396)
        at org.apache.http.conn.ssl.SSLConnectionSocketFactory.connectSocket(SSLConnectionSocketFactory.java:355)
        at org.apache.http.impl.conn.DefaultHttpClientConnectionOperator.connect(DefaultHttpClientConnectionOperator.java:142)
        at org.apache.http.impl.conn.PoolingHttpClientConnectionManager.connect(PoolingHttpClientConnectionManager.java:373)
        at org.apache.http.impl.execchain.MainClientExec.establishRoute(MainClientExec.java:394)
        at org.apache.http.impl.execchain.MainClientExec.execute(MainClientExec.java:237)
        at org.apache.http.impl.execchain.ProtocolExec.execute(ProtocolExec.java:185)
        at org.apache.http.impl.execchain.RetryExec.execute(RetryExec.java:89)
        at org.apache.http.impl.execchain.RedirectExec.execute(RedirectExec.java:110)
        at org.apache.http.impl.client.InternalHttpClient.doExecute(InternalHttpClient.java:185)
        at org.apache.http.impl.client.CloseableHttpClient.execute(CloseableHttpClient.java:83)
        at com.fr.finetube.base.utils.HttpClientUtils.httpResponseGet(HttpClientUtils.java:208)
        at com.fr.finetube.core.work.step.component.query.HttpClientStep.lambda$getHttpClientMethod$4(HttpClientStep.java:374)
        at com.fr.finetube.core.work.step.component.query.HttpClientStep.doWork(HttpClientStep.java:173)
        ... 5 more
Caused by: java.io.EOFException: SSL peer shut down incorrectly
        at sun.security.ssl.InputRecord.read(InputRecord.java:505)
        at sun.security.ssl.SSLSocketImpl.readRecord(SSLSocketImpl.java:983)
        ... 22 more

2.2 现象

即便是使用异常处理，该报错仍然不可避免：消息中心显示三次暂停&一次中断。目前只能手动再次开启任务。

2.3 解决

目前只能重启。