idea连接Hadoop以及通过Java将Hadoop中的文件数据写入数据库中

idea连接Hadoop以及通过idea将Hadoop中的文件数据写入数据库中

一、idea连接Hadoop

1.新建maven项目

2.添加hadoop相关jar包

Ctrl+C&&Ctrl+V

        <dependency>
            <groupId>org.apache.hadoop</groupId>
            <artifactId>hadoop-common</artifactId>
            <version>2.10.1</version>
        </dependency>
        <dependency>
            <groupId>org.apache.hadoop</groupId>
            <artifactId>hadoop-hdfs</artifactId>
            <version>2.10.1</version>
        </dependency>
        <dependency>
            <groupId>org.apache.hadoop</groupId>
            <artifactId>hadoop-client</artifactId>
            <version>2.10.1</version>
        </dependency>
        <dependency>
            <groupId>org.apache.hadoop</groupId>
            <artifactId>hadoop-mapreduce-client-core</artifactId>
            <version>2.10.1</version>
        </dependency>
        <dependency>
            <groupId>commons-logging</groupId>
            <artifactId>commons-logging</artifactId>
            <version>1.2</version>
        </dependency>
        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.17</version>
        </dependency>

3.配置log4j.properties文件

然后将下面内容复制进去

hadoop.root.logger=DEBUG, console
log4j.rootLogger = DEBUG, console
log4j.appender.console=org.apache.log4j.ConsoleAppender
log4j.appender.console.target=System.out
log4j.appender.console.layout=org.apache.log4j.PatternLayout
log4j.appender.console.layout.ConversionPattern=%d{yy/MM/dd HH:mm:ss} %p %c{2}: %m%n

然后如下操作

4.编写HDFS工具类

import org.apache.hadoop.fs.FsUrlStreamHandlerFactory;
import org.apache.hadoop.io.IOUtils;

import java.io.IOException;
import java.io.InputStream;
import java.net.MalformedURLException;
import java.net.URL;

public class URLcat {
    static {
        URL.setURLStreamHandlerFactory(new FsUrlStreamHandlerFactory());
    }

    public static void main(String[] args) throws MalformedURLException , IOException {
        InputStream in=null;
        try{
            in=new URL("hdfs://106.13.58.73:9000/in/text1.txt").openStream();
            IOUtils.copyBytes(in,System.out,4096,false);
        }finally {
            IOUtils.closeStream(in);
        }
    }
}

如果出现如下问题

请参考如下文章解决
log4j警告之log4j:WARN No appenders could be found for logger(org.apache.ibatis.logging.LogFactory).

二、idea连接MySQL

IntelliJ IDEA连接Mysql数据库

1.导入mysql-connector-java-5.1.41-bin.jar包

可参考IntelliJ IDEA 导入jar包（含截图）

2.编写MySQL类

import java.sql.*;

public class example1 {
    static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
    static final String DB_URL = "jdbc:mysql://localhost:3306/ceshi";
    static final String USER = "root";
    static final String PASS = "aaaa1234!";

    public static void main(String[] args) {
        // TODO Auto-generated method stub
        Connection conn = null;

        Statement stmt = null;
        try {
            // 注册 JDBC 驱动
            Class.forName(JDBC_DRIVER);
            // 打开链接
            System.out.println("连接数据库...");
            conn = DriverManager.getConnection(DB_URL, USER, PASS);
            // 执行查询
            System.out.println("实例化Statement对象...");
            stmt = conn.createStatement();
            String sql;
            sql = "SELECT * FROM id_pass";//搜索login表，实际须填入数据库的表名
            ResultSet rs = stmt.executeQuery(sql);
            // 展开结果集数据库
            while (rs.next()) {
                // 通过字段检索
                int id = rs.getInt("id");//得到“id”列的值，类型需要与数据库的数值类型相同
                String password = rs.getString("password");//得到“password”列的值，类型需要与数据库的数值类型相同
                //如有其他列可以选择添加
                // 输出数据
                System.out.print("ID: " + id);
                System.out.print(", 密码: " + password);
                System.out.print("\n");
            }
            // 完成后关闭
            rs.close();
            stmt.close();
            conn.close();
        } catch (SQLException se) {
            // 处理 JDBC 错误
            se.printStackTrace();
        } catch (Exception e) {
            // 处理 Class.forName 错误
            e.printStackTrace();
        } finally {
            // 关闭资源
            try {
                if (stmt != null) stmt.close();
            } catch (SQLException se2) {
            }// 什么都不做
            try {
                if (conn != null) conn.close();
            } catch (SQLException se) {
                se.printStackTrace();
            }
        }
        System.out.println("Goodbye!");
    }
}

三、实现idea将Hadoop中的文件数据写入数据库中

这一项必须保证前面两步没什么问题，也就是导包没什么问题

编写HdfsMysql类

import org.apache.hadoop.conf.Configuration;
import org.apache.hadoop.fs.FSDataInputStream;
import org.apache.hadoop.fs.FileSystem;
import org.apache.hadoop.fs.Path;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.net.URI;
import java.net.URISyntaxException;
import java.sql.*;

public class HdfsMysql {
    //数据库：ceshi  Host Address：localhost  端口：3306
    static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
    static final String DB_URL = "jdbc:mysql://localhost:3306/ceshi";
    static final String USER = "root";
    static final String PASS = "aaaa1234!";

    public static void main(String[] args) throws URISyntaxException, IOException, ClassNotFoundException, SQLException {
        /**
         * 创建filesystem对象，连接hdfs
         */
        URI uri = new URI("hdfs://106.13.58.73:9000");
        Configuration conf = new Configuration();
        FileSystem fs = FileSystem.get(uri, conf);
        /**
         * 连接数据库
         */
        Connection conn = null;
        Statement stmt = null;
        Class.forName(JDBC_DRIVER);//注册JDBC驱动
        conn = DriverManager.getConnection(DB_URL, USER, PASS);//打开数据库
        PreparedStatement ps = conn.prepareStatement("insert into photo(photo_id) values (?)");
        /**
         * 从hdfs中读取数据,切分数据，插入MySQL
         */
        FSDataInputStream in = fs.open(new Path("/in/text1.txt"));
        BufferedReader br = new BufferedReader(new InputStreamReader(in, "UTF-8"));
        String line = null;
        while ((line = br.readLine()) != null) {
            String[] split = line.split(",");
            ps.setString(1, split[0]);
            //ps.setString(2, split[1]);
            //ps.setInt(3, Integer.parseInt(split[2]));
            ps.executeUpdate();
        }
        ps.close();
        in.close();
        br.close();
    }
}

要么努力，要么放弃。