数据从https接口到mysql

数据从https接口到mysql

数据流程

HTTPS→Spring Boot→Logstash→Mysql→Spark→Mysql

流程解释

1. 使用spring boot项目请求https接口获取返回数据，并把数据转发到logstash监听的端口

 ​	使用spring boot实现转发的原因有两个

 ​		一、数据源https需要请求才能返回数据

 ​		二、没有ssl证书无法直接访问https接口，需要程序忽略验证

2. 使用logstash的http input插件监测指定接口，编写filter，使用jdbc output插件落入到mysql

 ​	使用logstash的主要原因是通过修改配置文件的方式配置获取多个数据源的数据(不需要修改程序)，

 ​	并使用logstash的filter可以很好的将不同数据格式的数据标准化(可以把数据格式统一)

3. 把标准数据存入mysql以便后续spark处理

4. spark操作数据（增删改）后把最终结果数据存入mysql

主要问题解决办法

1. 忽略ssl问题

 public static String sendPost(String url1, String params) throws IOException, KeyManagementException, NoSuchAlgorithmException {
        BufferedReader in = null;
        String result = "";
        HttpsURLConnection.setDefaultHostnameVerifier(new JavaUtils().new NullHostNameVerifier());
        SSLContext sc = SSLContext.getInstance("TLS");
        sc.init(null, trustAllCerts, new SecureRandom());
        HttpsURLConnection.setDefaultSSLSocketFactory(sc.getSocketFactory());
        URL url = new URL(url1);
        // 打开restful链接
        HttpURLConnection conn = (HttpURLConnection) url.openConnection();
        conn.setRequestMethod("POST");// POST GET PUT DELETE
		// 设置访问提交模式，表单提交
        conn.setRequestProperty("Content-Type", "application/json;charset=utf-8");
        conn.setConnectTimeout(130000);// 连接超时 单位毫秒
        conn.setReadTimeout(130000);// 读取超时 单位毫秒
        // 读取请求返回值
        conn.setDoOutput(true);
        OutputStream out = null;

        out = conn.getOutputStream();
        out.write(params.getBytes());
        out.flush(); //清空缓冲区,发送数据
        out.close();

        in = new BufferedReader(new InputStreamReader(conn.getInputStream()));
        String line;
        while ((line = in.readLine()) != null) {
            result += line;
        }
      /*  InputStream inStream = conn.getInputStream();
        byte bytes[] = new byte[inStream.available()];
        inStream.read(bytes, 0, inStream.available());*/
        // System.out.println();
        return result;
    }
    static TrustManager[] trustAllCerts = new TrustManager[]{new X509TrustManager() {
        @Override
        public void checkClientTrusted(X509Certificate[] chain, String authType) throws CertificateException {
        }

        @Override
        public void checkServerTrusted(X509Certificate[] chain, String authType) throws CertificateException {
        }

        @Override
        public X509Certificate[] getAcceptedIssuers() {
            return null;
        }
    }};

    public class NullHostNameVerifier implements HostnameVerifier {
        /*
         * (non-Javadoc)
         *
         * @see javax.net.ssl.HostnameVerifier#verify(java.lang.String,
         * javax.net.ssl.SSLSession)
         */
        @Override
        public boolean verify(String arg0, SSLSession arg1) {
            return true;
        }

    }

2. 向指定接口发送数据

 public static String post(String strURL, String params) {
        System.out.println(strURL);
        System.out.println(params);
        try {
            URL url = new URL(strURL);// 创建连接
            HttpURLConnection connection = (HttpURLConnection) url
                    .openConnection();
            connection.setDoOutput(true);
            connection.setDoInput(true);
            connection.setUseCaches(false);
            connection.setInstanceFollowRedirects(true);
            connection.setRequestMethod("POST"); // 设置请求方式
            connection.setRequestProperty("Accept", "application/json"); // 设置接收数据的格式
            connection.setRequestProperty("Content-Type", "application/json"); // 设置发送数据的格式
            connection.connect();
            OutputStreamWriter out = new OutputStreamWriter(
                    connection.getOutputStream(), "UTF-8"); // utf-8编码
            out.append(params);
            out.flush();
            out.close();
            // 读取响应
            int length = (int) connection.getContentLength();// 获取长度
            InputStream is = connection.getInputStream();
            if (length != -1) {
                byte[] data = new byte[length];
                byte[] temp = new byte[512];
                int readLen = 0;
                int destPos = 0;
                while ((readLen = is.read(temp)) > 0) {
                    System.arraycopy(temp, 0, data, destPos, readLen);
                    destPos += readLen;
                }
                String result = new String(data, "UTF-8"); // utf-8编码
                //   System.out.println(result);
                return result;
            }
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return "error"; // 自定义错误信息
    }

3. logstash 配置文件(写入或者读取mysql必须依赖：mysql-connector-java-5.1.47.jar)

input {
    http{
            host => "0.0.0.0"
            port => 8081
        }
}

filter {
    json {
       source => "message"
    }
    #删除不想要的字段
    mutate {
      remove_field => ["tags","code","headers","[payload][total]","@timestamp","@version","host","message","status"]
   }
    #解析json数组
    split {
            field => "[payload][datas]"
   }
	#向json中添加字段   
 	mutate {
      add_field =>{
			"[payload][datas][dataSourceName]" => "dataSourceName"
		}
   }

}
output {
	stdout {codec => rubydebug}

 jdbc{
        driver_jar_path => "/opt/lxl/logstash-7.6.1/mysql-connector-java-5.1.47.jar"
        driver_class => "com.mysql.jdbc.Driver"
        #jdbcurl和数据库用户名密码
        connection_string => "jdbc.url"
        #要执行的sql 只能写单句sql
		statement =>["sql"]
	}
 jdbc{
        driver_jar_path => "/opt/lxl/logstash-7.6.1/mysql-connector-java-5.1.47.jar"
        driver_class => "com.mysql.jdbc.Driver"
        connection_string => "jdbn.url"
		statement =>["sql"]
	}
}