Spark-Streaming与Spark-Sql整合实现实时股票排行---通过kafka列队数据

Spark-Streaming与Spark-Sql整合实现实时股票排行---通过kafka列队数据，前端数据通过 kafka队列传递，外层还有flume的实时收集。

1、mvn构建工程，指定好依赖的库，这里用的是spark1.4.1

[html]  view plain  copy

1. <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"  
2.     xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">  
3.     <modelVersion>4.0.0</modelVersion>  
4.   
5.     <groupId>com.hexun</groupId>  
6.     <artifactId>spark-streaming-java</artifactId>  
7.     <version>0.0.1-SNAPSHOT</version>  
8.     <packaging>jar</packaging>  
9.   
10.     <name>spark-study-java</name>  
11.     <url>http://maven.apache.org</url>  
12.   
13.     <properties>  
14.         <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>  
15.     </properties>  
16.   
17.     <dependencies>  
18.         <dependency>  
19.             <groupId>junit</groupId>  
20.             <artifactId>junit</artifactId>  
21.             <version>3.8.1</version>  
22.             <scope>test</scope>  
23.         </dependency>  
24.         <dependency>  
25.             <groupId>org.apache.spark</groupId>  
26.             <artifactId>spark-core_2.10</artifactId>  
27.             <version>1.4.1</version>  
28.         </dependency>  
29.         <dependency>  
30.             <groupId>org.apache.spark</groupId>  
31.             <artifactId>spark-sql_2.10</artifactId>  
32.             <version>1.4.1</version>  
33.         </dependency>  
34.         <dependency>  
35.             <groupId>org.apache.spark</groupId>  
36.             <artifactId>spark-hive_2.10</artifactId>  
37.             <version>1.4.1</version>  
38.         </dependency>  
39.         <dependency>  
40.             <groupId>org.apache.spark</groupId>  
41.             <artifactId>spark-streaming_2.10</artifactId>  
42.             <version>1.4.1</version>  
43.         </dependency>  
44.         <dependency>  
45.             <groupId>org.apache.hadoop</groupId>  
46.             <artifactId>hadoop-client</artifactId>  
47.             <version>2.6.0</version>  
48.         </dependency>  
49.         <dependency>  
50.             <groupId>org.apache.spark</groupId>  
51.             <artifactId>spark-streaming-kafka_2.10</artifactId>  
52.             <version>1.4.1</version>  
53.         </dependency>  
54.         <dependency>  
55.             <groupId>mysql</groupId>  
56.             <artifactId>mysql-connector-java</artifactId>  
57.             <version>5.1.6</version>  
58.         </dependency>  
59.     </dependencies>  
60.   
61.     <build>  
62.         <sourceDirectory>src/main/java</sourceDirectory>  
63.         <testSourceDirectory>src/main/test</testSourceDirectory>  
64.   
65.         <plugins>  
66.             <plugin>  
67.                 <artifactId>maven-assembly-plugin</artifactId>  
68.                 <configuration>  
69.                     <descriptorRefs>  
70.                         <descriptorRef>jar-with-dependencies</descriptorRef>  
71.                     </descriptorRefs>  
72.                     <archive>  
73.                         <manifest>  
74.                             <mainClass></mainClass>  
75.                         </manifest>  
76.                     </archive>  
77.                 </configuration>  
78.                 <executions>  
79.                     <execution>  
80.                         <id>make-assembly</id>  
81.                         <phase>package</phase>  
82.                         <goals>  
83.                             <goal>single</goal>  
84.                         </goals>  
85.                     </execution>  
86.                 </executions>  
87.             </plugin>  
88.   
89.             <plugin>  
90.                 <groupId>org.codehaus.mojo</groupId>  
91.                 <artifactId>exec-maven-plugin</artifactId>  
92.                 <version>1.2.1</version>  
93.                 <executions>  
94.                     <execution>  
95.                         <goals>  
96.                             <goal>exec</goal>  
97.                         </goals>  
98.                     </execution>  
99.                 </executions>  
100.                 <configuration>  
101.                     <executable>java</executable>  
102.                     <includeProjectDependencies>true</includeProjectDependencies>  
103.                     <includePluginDependencies>false</includePluginDependencies>  
104.                     <classpathScope>compile</classpathScope>  
105.                 </configuration>  
106.             </plugin>  
107.   
108.             <plugin>  
109.                 <groupId>org.apache.maven.plugins</groupId>  
110.                 <artifactId>maven-compiler-plugin</artifactId>  
111.                 <configuration>  
112.                     <source>1.6</source>  
113.                     <target>1.6</target>  
114.                 </configuration>  
115.             </plugin>  
116.   
117.         </plugins>  
118.     </build>  
119. </project>  

2、实现过程：

1）通过kafka队列获取数据，createDirectStream实现

2）映射pojo与数据的关系，注册成sparksql的表

3）实现sql中的函数，这里大部分的函数都要自己实现udf，甚至length简单的函数

4)  编写sql语实现

5）保存入MySQL数据，供前端展示

具体代码如下（Scala版本）：

[java]  view plain  copy

1. package com.hexun.streaming  
2.   
3. import java.sql.{DriverManager, Connection}  
4. import java.util.Date  
5. import java.util.regex.Pattern  
6.   
7. import kafka.serializer.StringDecoder  
8. import org.apache.commons.lang.time.DateFormatUtils  
9. import org.apache.spark.streaming.kafka.KafkaUtils  
10. import org.apache.spark.streaming.{Seconds, StreamingContext}  
11. import org.apache.spark.{SparkContext, SparkConf}  
12.   
13. import scala.collection.mutable  
14.   
15. import scala.collection.immutable.ListMap  
16.   
17.   
18. /** 
19.  * Created by Administrator on 2015/11/26. 
20.  */  
21. object StockCntSumKafkaLPcnt {  
22.   
23.   
24.   case class Tracklog(dateday: String, datetime: String, ip: String, cookieid: String, userid: String, logserverip: String, referer: String, requesturl: String, remark1: String,  
25.                       remark2: String, alexaflag: String, ua: String)  
26.   
27.   
28.   def main(args: Array[String]) {  
29.     val smap = new mutable.HashMap[String, Integer]()  
30.   
31.     val url = "jdbc:mysql://10.130.3.211:3306/charts"  
32.     val user = "dbcharts"  
33.     val password = "Abcd1234"  
34.   
35.     val conf = new SparkConf().setAppName("stocker") //.setMaster("local[2]")  
36.     val sc = new SparkContext(conf)  
37.   
38.     val ssc = new StreamingContext(sc, Seconds(15))  
39.   
40.     // Kafka configurations  
41.   
42.     val topics = Set("teststreaming")  
43.   
44.     val brokers = "bdc46.hexun.com:9092,bdc53.hexun.com:9092,bdc54.hexun.com:9092"  
45.   
46.     val kafkaParams = Map[String, String]("metadata.broker.list" -> brokers, "serializer.class" -> "kafka.serializer.StringEncoder")  
47.   
48.     // Create a direct stream  
49.     val kafkaStream = KafkaUtils.createDirectStream[String, String, StringDecoder, StringDecoder](ssc, kafkaParams, topics)  
50.   
51.     val events = kafkaStream.flatMap(line => {  
52.       Some(line.toString())  
53.     })  
54.   
55.     try {  
56.       val tmpdf = events.map(_.split(" ")).filter(_.length >= 11).map(x => Tracklog(x(0), x(1), x(2), x(3), x(4), x(5), x(6), x(7), x(8), x(9), x(10), x(11)))  
57.   
58.       tmpdf.foreachRDD { rdd =>  
59.         val sqlContext = new org.apache.spark.sql.SQLContext(sc)  
60.   
61.         import sqlContext.implicits._  
62.         val df = rdd.toDF().registerTempTable("tracklog")  
63.   
64.         sqlContext.udf.register("strLen", (str: String) => str.length())  
65.   
66.         sqlContext.udf.register("concat", (str1: String, str2: String, str3: String) => str1 + str2 + str3)  
67.   
68.         sqlContext.udf.register("regexp_extract", (str: String, pattern: String) => {  
69.           val matcher = Pattern.compile(pattern, 1).matcher(str)  
70.           var res = ""  
71.           while (matcher.find()) {  
72.             res = matcher.group()  
73.           }  
74.           res  
75.         })  
76.   
77.         val rcount = sqlContext.sql("SELECT  substring(t.requesturl,strLen(regexp_extract(t.requesturl,'(.*?[^0-9][0|3|6][0][0-9][0-9][0-9][0-9]).*?'))-5,6) stock_code," +  
78.           "concat('http://stockdata.stock.hexun.com/', substring(t.requesturl,strLen(regexp_extract(t.requesturl,'(.*?[^0-9][0|3|6][0][0-9][0-9][0-9][0-9]).*?'))-5,6),'.shtml') url," +  
79.           "count(*) clickcnt " +  
80.           "FROM " +  
81.           "(select distinct dateday,datetime,ip,cookieid,userid,logserverip,referer,requesturl,remark1,remark2,alexaflag,ua from  tracklog where strLen(datetime)=12) t " +  
82.           "WHERE  " +  
83.           "regexp_extract(t.requesturl,'(.*?[^0-9][0|3|6][0][0-9][0-9][0-9][0-9]).*?') <>'' " +  
84.           "and t.requesturl like 'http://stockdata.stock.hexun.com/%shtml' " +  
85.           "and t.requesturl not like '%index%' " +  
86.           "and t.requesturl not like '%fund%' " +  
87.           "group by substring(t.requesturl,strLen(regexp_extract(t.requesturl,'(.*?[^0-9][0|3|6][0][0-9][0-9][0-9][0-9]).*?'))-5,6)  " +  
88.           "order by clickcnt desc " +  
89.           "limit 150")  
90.   
91.         var flag:Int = 0  
92.   
93.   
94.         rcount.collect().foreach(data => {  
95.           flag = 1;  
96.           val stockerId = data.get(0).toString;  
97.           val cnt = smap.get(stockerId)  
98.   
99.           println("stockerId: " + stockerId + ", cnt:" + cnt)  
100.   
101.           if (cnt == null || cnt.toString.equals("None")) {  
102.             smap += (stockerId -> Integer.parseInt(data.get(2).toString))  
103.           } else if (cnt != null && !cnt.toString.equals("None")) {  
104.             val cntI = smap(stockerId)  
105.             val sum: Integer = Integer.parseInt(data.get(2).toString) + cntI  
106.             smap += (stockerId -> sum)  
107.           }  
108.         })  
109.   
110.   
111.         if(flag == 1){  
112.           // sort by value  
113.           var idx: Int = 1  
114.   
115.           val sortMap = ListMap(smap.toSeq.sortWith(_._2 > _._2): _*)  
116.   
117.           val stattime = DateFormatUtils.format(new Date, "yyyy-MM-dd HH:mm:ss")  
118.   
119.           val conn: Connection = DriverManager.getConnection(url, user, password)  
120.           val pstat = conn.prepareStatement("INSERT INTO  stock_realtime_analysis_spark (stockId,url,clickcnt,type,recordtime) VALUES (?,?,?,?,?)")  
121.   
122.           sortMap foreach {  
123.             case (key, value) =>  
124.               if (idx <= 150) {  
125.                 println(key + ",http://stockdata.stock.hexun.com/" + key + ".shtml," + value + "," + stattime)  
126.   
127.                 pstat.setString(1, key)  
128.                 pstat.setString(2, "http://stockdata.stock.hexun.com/" + key + ".shtml")  
129.                 pstat.setInt(3, value)  
130.                 pstat.setString(4, "01")  
131.                 pstat.setString(5, stattime)  
132.   
133.                 pstat.executeUpdate()  
134.               }  
135.               idx = idx + 1  
136.           }  
137.   
138.           pstat.close  
139.           conn.close  
140.   
141.           flag == 0  
142.         }  
143.   
144.   
145.       }  
146.     } catch {  
147.       case e: Exception =>  
148.     }  
149.   
150.     ssc.start()  
151.     ssc.awaitTermination()  
152.   }  
153.   
154. }  

3、任务提交执行脚本如下：

[plain]  view plain  copy

1. #!/bin/bash  
2. source /etc/profile  
3.   
4. stocker=`ps -ef | grep spark |grep SparkStreaming.jar | awk '{print $2}'`  
5. echo $stocker  
6.   
7. kill -9 $stocker  
8.   
9. nohup /opt/modules/spark/bin/spark-submit \  
10. --master spark://10.130.2.20:7077 \  
11. --driver-memory 3g \  
12. --executor-memory 3g \  
13. --total-executor-cores 24 \  
14. --conf spark.ui.port=56689  \  
15. --jars /opt/bin/sparkJars/kafka_2.10-0.8.2.1.jar,/opt/bin/sparkJars/spark-streaming-kafka_2.10-1.4.1.jar,/opt/bin/sparkJars/metrics-core-2.2.0.jar,/opt/bin/sparkJars/mysql-connector-java-5.1.26-bin.jar,/opt/bin/sparkJars/spark-streaming-  
16. kafka_2.10-1.4.1.jar \  
17. --class com.hexun.streaming.StockCntSumKafkaLPcnt \  
18. /opt/bin/UDF/SparkStreaming.jar \  
19.  >/opt/bin/initservice/stock.log 2>&1 & \