Spark's JDBC Development (Connection Database test)

Spark's JDBC Development (Connection Database test)

the following actions belong to local mode operations:1, establish the project RDDTOJDBC in Eclipse4.5, and create a folder lib for placing third-party driver packages

[Email protected] software]$ cd/project/rddtojdbc/
[Email protected] rddtojdbc]$ mkdir-p Lib
[[email protected] rddtojdbc]$ ls
Bin Lib Src

2, add the necessary environment

2.1. Copy the MySQL jar package to the Lib directory under the project directory RDDTOJDBC
[Email protected] software]$ cp-a/software/hive-1.2.2/lib/mysql-connector-java-3.0.17-ga-bin.jar/project/ rddtojdbc/lib/
2.1. Add the Spark's development library Spark2.1.1-all to the classpath path of the RDDTOJDBC project (which can be resolved by adding a user library); What packages are included in Spark2.1.1-all, please click here

3. Prepare the source data for Spark:

[Email protected] spark]$ cd/home/hadoop/test/jdbc/[[email protected] jdbc]$ lsmyuser  123456 165 1998-9-9187 2009-10-19177 1990-8-3

4, the development of source code:

 Packagecom.mmzs.bigdata.spark.core.local;ImportJava.io.File;Importjava.sql.Connection;Importjava.sql.Date;ImportJava.sql.DriverManager;Importjava.sql.PreparedStatement;Importjava.sql.SQLException;Importorg.apache.spark.SparkConf;ImportOrg.apache.spark.api.java.JavaRDD;ImportOrg.apache.spark.api.java.JavaSparkContext;Importorg.apache.spark.api.java.function.VoidFunction;ImportScala. Tuple4; Public classTestmain {/*** Global Counter*/    Private Static intcount; /*** Database Connection*/    Private StaticConnection Conn; /*** Pre-compiled statements*/    Private StaticPreparedStatement PStat; Private Static FinalFile out_path=NewFile ("/home/hadoop/test/jdbc/output"); Static{deldir (Out_path); Try{String SQL= "INSERT into T_user (username,password,height,birthday) VALUES (?,?,?,?)"; String URL= "Jdbc:mysql://127.0.0.1:3306/test?useunicode=true&characterencoding=utf8"; Class.forName ("Com.mysql.jdbc.Driver"); Conn=drivermanager.getconnection (URL, "root", "123456"); PStat=conn.preparestatement (SQL); } Catch(ClassNotFoundException e) {e.printstacktrace (); } Catch(SQLException e) {e.printstacktrace (); }    }    /*** Delete any directory or file *@paramF*/    Private Static voidDeldir (File f) {if(!f.exists ())return; if(F.isfile () | | (F.isdirectory () &&f.listfiles (). length==0) ) {f.delete (); return; } file[] Files=F.listfiles ();  for(File fp:files) deldir (FP);    F.delete (); }        Private Static voidBatchSave (tuple4<string,string,double,date> Line,Booleanisover) {        Try{pstat.setstring (1, Line._1 ()); Pstat.setstring (2, Line._2 ()); Pstat.setdouble (3, Line._3 ()); Pstat.setdate (4, Line._4 ()); if(Isover) {//write the disk directly if the loop is closedPstat.addbatch ();                Pstat.executebatch ();                Pstat.clearbatch ();            Pstat.clearparameters (); }Else{//add the SQL statement to the batch if it does not endPstat.addbatch (); Count++; if(count%100==0) {//commit a batch operation if one batch is fullPstat.executebatch ();                    Pstat.clearbatch ();                Pstat.clearparameters (); }            }        }Catch(SQLException e) {e.printstacktrace (); }    }        /*** Store data in the Rdd collection into the relational database MySQL *@paramStatresrdd*/    Private Static voidSavetodb (javardd<string>Statresrdd) {        Final Longrddnum=Statresrdd.count (); Statresrdd.foreach (NewVoidfunction<string>(){            Private LongCount=0; @Override Public voidCall (String line)throwsException {string[] fields=line.split (""); String UserName=fields[1]; String PassWord=fields[2]; Double Height=double.parsedouble (fields[3]); Date Birthday=date.valueof (fields[4]); Tuple4<String,String,Double,Date> fieldtuple=NewTuple4<string,string,double,date>(Username,password,height,birthday); if(++count<rddnum) {BatchSave (fieldtuple,false); }Else{batchsave (fieldtuple,true);                }            }        }); Try{            if(NULL!=pstat) pstat.close (); if(NULL!=conn) conn.close (); }Catch(SQLException e) {e.printstacktrace (); }    }         Public Static voidMain (string[] args) {sparkconf conf=Newsparkconf (); Conf.setappname ("Java Spark Local"); Conf.setmaster ("Local"); //generate spark context based on spark configurationJavasparkcontext jsc=Newjavasparkcontext (conf); //reads a local text file into an in-memory Rdd collection ObjectJavardd<string> linerdd=jsc.textfile ("/home/hadoop/test/jdbc/myuser"); //........... Other conversion or statistical operations .........//Store statistics after the results to the disk file//linerdd.saveastextfile ("/home/hadoop/test/jdbc/output");Savetodb (LINERDD); //Close Spark ContextJsc.close (); }}

5. Initializing MySQL database Service (node on 192.168.154.134)

A. Start MySQL database service

[Email protected] ~]# cd/software/mysql-5.5.32/multi-data/3306/[[email protected] 3306]# lsdata my.cnf My.cnf.bak Mysqld[[email protected] 3306]#./mysqld startstarting MySQL ...

B. Build Test Library

[Email protected] 3306]# Cd/software/mysql-5.5.32/bin/[[email protected] bin]#./mysql-h192.168.154.134-p3306-uroot -p123456-e "Show databases;" +--------------------+| Database           |+--------------------+| information_schema | | mysql              | | performance_schema |+--------------------+ [[email protected] bin]#./MYSQL-H192.168.154.134-P3306-UROOT-P123456-E "CREATE database test character set UTF8;" [[email protected] bin]#/mysql-h192.168.154.134-p3306-uroot-p123456-e "show databases;" +--------------------+| Database           |+--------------------+| information_schema | | mysql              | | performance_schema | | Test               |+---------- ----------+

C, the establishment of the MyUser table:

[[email protected] bin]#./mysql-h192.168.154.134-p3306-uroot-p123456-e "CREATE table if not exists Test.myuser (Usern Ame varchar (+), password varchar (+), height double (10,1), birthday date) Engine=myisam Charset=utf8;

[email protected] bin]#./mysql-h192.168.154.134-p3306-uroot-p123456-e "use test;show tables;" +-------------------+| Tables_in_test |+-------------------+| MyUser |+-------------------+[email protected] bin]#./mysql-h192.168.154.134-p3306-uroot-p123456-e "Use Test;desc test.myuser;" +-------------+--------------+-------+----+---------+-------+| Field | Type | Null | key| Default | Extra |+-------------+--------------+-------+----+---------+-------+| Username | varchar (30) |    YES | |       NULL | || password | varchar (30) |    YES | |       NULL | || Height |Double(10,1) |    YES | | NULL | NULL | | Birthday | Date |    YES | |       NULL | |+-------------+--------------+-------+----+---------+-------+#目前数据库表中还没有数据 [[email protected] bin]#./mysql-h192.168.154.134-p3306-uroot-p123456-e "SELECT * from Test.myuser;"

6. Run and view the results in the database 6.1, run the spark code directly in Eclipse4.5, observe the Eclipse console output 6.2, check whether the data already exists in the relational database MySQL

Spark's JDBC Development (Connection Database test)