警务大数据项目实战
- 专题一 大数据数据存储
- 1-1 警务大数据之表创建
- 第1关：Phoenix在Springboot应用
- 第2关：通过POI事件读取Excel内容
- 第3关：创建警务大数据表信息
- 1-2 警务大数据之交换配置
- 第1关：通过JDBC存储字段信息
- 第2关：表关系信息存储
- 第3关：自动交换应用
- 1-3 警务大数据之离线存储
- 第1关：Excel数据存储HBase
- 第2关：文本文件数据存储HBase
- 1-4 警务大数据之实时存储
- 第一关：Kafka实时存储HBase
- 第二关：从HTTP读取数据存入HBase
- 1-5警务大数据之关系数据库存储
- 第一关：MySQL支持
- 第二关：MySQL离线存储
- 专题二 大数据批处理
- 2-1 警务大数据之上网相关指标批处理
- 第一关：频繁更换上网场所
- 第二关：多次同上网
- 专题三 实时流计算
- 3-1 警务大数据之大屏幕实时流数据监控
- 第一关：车辆相关信息实时计算
- 第二关：实时数据可视化
- 专题四 数据可视化
- 4-1 警务大数据之慧搜
- 4-2 警务大数据之精准布控
- 第一关：布控人员查询
- 第二关：布控人员日志查询
专题一 大数据数据存储
1-1 警务大数据之表创建
第1关：Phoenix在Springboot应用
Phoenix是构建在HBase上的一个SQL层，能让我们用标准的JDBC API 来对数据进行操作。Phoenix主要原理是通过查询引擎将SQL查询转换为一个或多个HBase扫描，并发执行来生成标准的JDBC结果集。
在做警务大数据项目之前，我们先来学会使用Springboot 集成Phoenix，完成HBase表的创建。
package com.educodeer.police1.case1;
import java.sql.Connection;
import java.sql.SQLException;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
@Service
public class Case1 {
//获取hikariCP连接池
@Autowired
DataSource dataSource;
/**
* HBase 表创建
* @throws SQLException
*/
public void createTable() throws SQLException {
/********** Begin *********/
Connection connection=dataSource.getConnection();
connection.createStatement().execute("CREATE TABLE TEST (ID INTEGER not null primary key, NAME VARCHAR)");
/********** end *********/
}
}第2关：通过POI事件读取Excel内容
Excel是个人计算机数据处理软件，可以存储大量的数据，当警务需要的大量数据存储在Excel中时应该如何读取呢？本关任务就是编写一个通过 POI事件读取Excel内容的小程序。
package com.educodeer.police1.case1;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.hssf.eventusermodel.FormatTrackingHSSFListener;
import org.apache.poi.hssf.eventusermodel.HSSFEventFactory;
import org.apache.poi.hssf.eventusermodel.HSSFListener;
import org.apache.poi.hssf.eventusermodel.HSSFRequest;
import org.apache.poi.hssf.eventusermodel.MissingRecordAwareHSSFListener;
import org.apache.poi.hssf.record.LabelSSTRecord;
import org.apache.poi.hssf.record.Record;
import org.apache.poi.hssf.record.SSTRecord;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
/**
* POI的事件模式处理
*
*/
public class Case2 implements HSSFListener {
// Records we pick up as we process
private SSTRecord sstRecord;
// 存储行记录的容器
private List<String> rowlist = new ArrayList<String>();
public void process(InputStream stream) throws IOException {
POIFSFileSystem fs = new POIFSFileSystem(stream);
// 添加监听记录的事件
MissingRecordAwareHSSFListener listener = new MissingRecordAwareHSSFListener(this);
// 监听代理
FormatTrackingHSSFListener formatListener = new FormatTrackingHSSFListener(listener);
// 创建事件工厂
HSSFEventFactory factory = new HSSFEventFactory();
// 注册监听器
HSSFRequest request = new HSSFRequest();
request.addListenerForAllRecords(formatListener);
// 处理基于时间文档流(循环获取每一条Record进行处理)
factory.processWorkbookEvents(request, fs);
}
//记录处理
@Override
public void processRecord(Record record) {
/********** Begin *********/
int thisColumn = -1;
switch (record.getSid()) {
//所有sheet文本单元格的文本内容
case SSTRecord.sid:
sstRecord = (SSTRecord) record;
break;
//单元格为字符串类型
case LabelSSTRecord.sid:
LabelSSTRecord labelSSTRecord = (LabelSSTRecord) record;
thisColumn = labelSSTRecord.getColumn();
if (labelSSTRecord != null) {
String value = sstRecord.getString(labelSSTRecord.getSSTIndex()).toString().trim();
value = value.equals("") ? " " : value;
rowlist.add(thisColumn, value);
} else {
rowlist.add(thisColumn, " ");
}
break;
default:
break;
}
if (record.getSid() == -1) {
System.out.println(rowlist);
rowlist.clear();
}
/********** end *********/
}
}第3关：创建警务大数据表信息
上一关已经介绍了POI事件模式的使用，本关任务是通过POI事件模式来创建警务大数据的相关表。
package com.educodeer.police1.case1;
import com.educodeer.police1.util.Excel2003Reader;
import com.educodeer.police1.util.IRowReader;
import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
/**
* @Description Case3
* @DateTime 2023/4/18 10:26
* 创建HBase表
*/
public class Case3 {
static StringBuffer buffer = new StringBuffer();
/**
* 获取excel的表信息，来创建HBase表
*
* @param connection HBase连接
* @return
* @throws IOException
*/
public List<String> createHBaseTable(Connection connection) throws IOException, SQLException {
/********** Begin *********/
List<String> tableNames = new ArrayList<>();
List<String> stringList = new ArrayList<>();
List<String> primaryKeys = Arrays.asList(new String[]{"DJRSFZH", "ID", "ID", "GMSFHM", "ZJHM", "RZSJ,ZJHM", "RZSJ,ZJHM"});
Excel2003Reader reader = new Excel2003Reader(new IRowReader() {
boolean isStart = false;
boolean flag = false;
String startName = "名称";
String startName1 = "数据类型";
String endName = "表名：";
@Override
public void getRows(int sheetIndex, int curRow, List<String> rowlist) {
if (endName.equals(rowlist.get(0))) {
if (flag) {
stringList.add(buffer.toString());
buffer = new StringBuffer();
} else flag = true;
tableNames.add(rowlist.get(1));
isStart = false;
} else if (rowlist.get(0).equals(startName) && rowlist.get(1).equals(startName1)) {
isStart = true;
} else if (isStart) {
buffer.append(rowlist.get(0)).append(" VARCHAR,");
}
}
});
reader.process("table1.xls");
stringList.add(buffer.toString());
for (int i = 0; i < 7; i++) {
String str = "CREATE TABLE " + tableNames.get(i) + "(" + stringList.get(i) + "CONSTRAINT pk PRIMARY KEY ("
+ primaryKeys.get(i) + ") )DATA_BLOCK_ENCODING='DIFF',VERSIONS=3,BLOCKSIZE='32000',MAX_FILESIZE=10000000";
connection.createStatement().execute(str);
}
return tableNames;
/********** End *********/
}
}1-2 警务大数据之交换配置
第1关：通过JDBC存储字段信息
当外接数据和HBase本身字段名不匹配，则需要配置映射关系，来让程序知道如何进行转换，这就是自动交换。在进行自动交换之前我们需要知道外接数据的字段信息，并把外接数据的字段信息进行存储。前面我们通过读取 Excel获取过HBase表的字段信息，接下来我们要通过JDBC的方式获取表字段信息。
本关任务：编写一个程序，通过JDBC的方式获取表字段信息，并存储到 HBase里。
package com.educodeer.police3.case1;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
public class Case1 {
/**
* 初始化数据
*
* @param hbaseConnection HBase连接
* @param mysqlConnection mysql连接
* @param sqlTableName sql表名
* @param hbaseTableName HBase表名
* @throws SQLException
*/
public void init(Connection hbaseConnection, Connection mysqlConnection, String sqlTableName, String hbaseTableName)
throws SQLException {
/********** Begin *********/
ResultSet hbaseSet = hbaseConnection.createStatement().executeQuery("select * from " + hbaseTableName + " limit 1");
ResultSetMetaData hbaseSetMetaData = hbaseSet.getMetaData();
ResultSet sqlSet = mysqlConnection.createStatement().executeQuery("select * from " + sqlTableName + " limit 1");
ResultSetMetaData sqlSetMetaData = sqlSet.getMetaData();
for (int i = 1; i <= hbaseSetMetaData.getColumnCount(); i++) {
String str = "UPSERT INTO table_fields(id,name,type,table_name,table_type)" +
"VALUES (NEXT VALUE FOR table_fields_sequence," +
"'" + hbaseSetMetaData.getColumnName(i) + "'," +
"'" + hbaseSetMetaData.getColumnTypeName(i) + "'," +
"'" + hbaseSetMetaData.getTableName(i) + "'," +
"'1')";
hbaseConnection.createStatement().execute(str);
}
for (int i = 1; i <= sqlSetMetaData.getColumnCount(); i++) {
String str = "UPSERT INTO table_fields(id,name,type,table_name,table_type)" +
"VALUES (NEXT VALUE FOR table_fields_sequence," +
"'" + sqlSetMetaData.getColumnName(i) + "'," +
"'" + sqlSetMetaData.getColumnTypeName(i) + "'," +
"'" + sqlSetMetaData.getTableName(i) + "'," +
"'2')";
hbaseConnection.createStatement().execute(str);
}
/********** end *********/
}
}第2关：表关系信息存储
当外接数据和HBase本身字段名不匹配，则需要配置映射关系，来让程序知道如何进行转换，这就是自动交换。上一关我们已经学习了通过JDBC的方式获取表字段信息，本关来编写一个存储映射关系的接口，来说明HBase 表和外部数据名称的对应关系。
package com.educodeer.police3.case1;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
public class Case2 {
/**
* 保存关联关系
*
* @param connection HBase 连接
* @param map 外接数据名称 和 HBase字段名称映射关系
* @throws SQLException
*/
public void saveRelationData(Connection connection, Map<String, String> map) throws SQLException {
/********** Begin *********/
ResultSet resultSet = connection.createStatement().executeQuery("select * from table_fields");
Map<String, Info> sqlMap = new HashMap<>();
Map<String, Info> hbaseMap = new HashMap<>();
while (resultSet.next()) {
Integer id = resultSet.getInt(1);
String name = resultSet.getString(2);
String type = resultSet.getString(3);
String table_name = resultSet.getString(4);
String table_type = resultSet.getString(5);
Info info = new Info(id, name, type, table_name, table_type);
if (table_type.equals("1")){
hbaseMap.put(name, info);
}
else sqlMap.put(name, info);
}
for (String key : map.keySet()) {
String value = map.get(key);
Info sqlInfo = sqlMap.get(key);
Info hbaseInfo = hbaseMap.get(value);
connection.createStatement().execute("UPSERT INTO relate_fields values(" +
hbaseInfo.id + "," +
sqlInfo.id + "," +
"'" + sqlInfo.table_name + "'," +
"'" + hbaseInfo.table_name + "')"
);
}
/********** end *********/
}
class Info {
Integer id;
String name;
String type;
String table_name;
String table_type;
public Info() {
}
public Info(Integer id, String name, String type, String table_name, String table_type) {
this.id = id;
this.name = name;
this.type = type;
this.table_name = table_name;
this.table_type = table_type;
}
}
}第3关：自动交换应用
当外接数据和HBase本身字段名不匹配，则需要配置映射关系，来让程序知道如何进行转换，这就是自动交换。上一关我们已经存储了映射关系，本关我们来编写一个自动交换应用的小程序。
package com.educodeer.police3.case1;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
public class Case3 {
/**
* @param hbaseConnection HBaseConnection连接
* @param sqlTableName 外接表表名
* @param data 数据
* @throws SQLException
*/
public void dataStore(Connection hbaseConnection, String sqlTableName, Map<String, String> data) throws SQLException {
/********** Begin *********/
//sql列名——>sqlID——>hbaseID>——>hbase列名——>类型转换
ResultSet tableFieldsResultSet = hbaseConnection.createStatement().executeQuery("select * from table_fields");
Map<Integer, TableFields> hbaseMap = new HashMap<>();
Map<String, TableFields> sqlMap = new HashMap<>();
while (tableFieldsResultSet.next()) {
TableFields tableFields = new TableFields();
tableFields.id = tableFieldsResultSet.getInt(1);
tableFields.name = tableFieldsResultSet.getString(2);
tableFields.type = tableFieldsResultSet.getString(3);
tableFields.table_name = tableFieldsResultSet.getString(4);
tableFields.table_type = tableFieldsResultSet.getString(5);
if (tableFields.table_type.equals("1")) {
hbaseMap.put(tableFields.id, tableFields);
} else sqlMap.put(tableFields.name, tableFields);
}
ResultSet relateFieldsResultSet = hbaseConnection.createStatement().executeQuery("select * from relate_fields");
Map<Integer, RelateFields> relateFieldsMap = new HashMap<>();
while (relateFieldsResultSet.next()) {
RelateFields relateFields = new RelateFields();
relateFields.hbaseID = relateFieldsResultSet.getInt(1);
relateFields.sqlID = relateFieldsResultSet.getInt(2);
relateFields.sqlTableName = relateFieldsResultSet.getString(3);
relateFields.hbaseTableName = relateFieldsResultSet.getString(4);
relateFieldsMap.put(relateFields.sqlID, relateFields);
}
Salary salary = new Salary();
for (String key : data.keySet()) {
TableFields sqlTableFields = sqlMap.get(key);
RelateFields relateFields = relateFieldsMap.get(sqlTableFields.id);
TableFields hbaseTableFields = hbaseMap.get(relateFields.hbaseID);
switch (hbaseTableFields.name) {
case "ID":
salary.id = Integer.parseInt(data.get(key));
break;
case "NAME":
salary.name = data.get(key);
break;
case "AGE":
salary.age = Integer.parseInt(data.get(key));
break;
case "CITY":
salary.city = data.get(key);
break;
case "SALARY":
salary.salary = Double.parseDouble(data.get(key));
break;
}
}
hbaseConnection.createStatement().execute(salary.generateStr());
/********** end *********/
}
class RelateFields {
Integer hbaseID;
Integer sqlID;
String sqlTableName;
String hbaseTableName;
public RelateFields() {
}
public RelateFields(Integer hbaseID, Integer sqlID, String sqlTableName, String hbaseTableName) {
this.hbaseID = hbaseID;
this.sqlID = sqlID;
this.sqlTableName = sqlTableName;
this.hbaseTableName = hbaseTableName;
}
}
class TableFields {
Integer id;
String name;
String type;
String table_name;
String table_type;
public TableFields() {
}
public TableFields(Integer id, String name, String type, String table_name, String table_type) {
this.id = id;
this.name = name;
this.type = type;
this.table_name = table_name;
this.table_type = table_type;
}
}
class Salary {
Integer id;
String name;
Integer age;
String city;
Double salary;
public Salary() {
}
public Salary(Integer id, String name, Integer age, String city, Double salary) {
this.id = id;
this.name = name;
this.age = age;
this.city = city;
this.salary = salary;
}
public String generateStr() {
return "UPSERT INTO salary values(" + id + ",'" + name + "'," + age + ",'" + city + "'," + salary + ")";
}
}
}1-3 警务大数据之离线存储
第1关：Excel数据存储HBase
Excel是个人计算机数据处理软件，可以存储大量的数据，当警务大量数据存储在Excel中如何读取呢？
本关任务：读取Excel的警务数据，通过Phoenix插件，采用JDBC方式将其存入HBase。
package com.educoder.policeCalculate;
import com.educoder.policeCalculate.util.Excel2003Reader;
import com.educoder.policeCalculate.util.IRowReader;
import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class Cs {
// 请在此处编写代码，读取Excel表中数据，并通过Phoenix插件将其存入HBase
/********** Begin **********/
private static Connection connection = null;
private static List<String> hbaseCol = new ArrayList<String>();
private static List<String> col = new ArrayList<String>();
private static List<Integer> index = new ArrayList<Integer>();
private static List<List> values = new ArrayList<List>();
static {
try {
//连接Phoenxi的服务驱动
Class.forName("org.apache.phoenix.queryserver.client.Driver");
connection = DriverManager.getConnection("jdbc:phoenix:thin:url=http://127.0.0.1:8765;serialization=PROTOBUF");
//获取HBase表T_BKYJ_N_JBXX的所有列
ResultSet resultSet = connection.createStatement().executeQuery("select * from T_BKYJ_N_JBXX limit 1");
ResultSetMetaData metaData = resultSet.getMetaData();
for (int i = 1; i <= metaData.getColumnCount(); i++)
hbaseCol.add(metaData.getColumnName(i));
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void main(String[] args) throws IOException, SQLException {
Excel2003Reader excel2003Reader = new Excel2003Reader(new IRowReader() {
public void getRows(int sheetIndex, int curRow, List<String> rowlist) {
if (curRow == 0) {
for (String str : rowlist)
col.add(str.toUpperCase());
col.retainAll(hbaseCol);
for (int i = 0; i < col.size(); i++)
index.add(rowlist.indexOf(col.get(i).toLowerCase()));
} else {
List<String> value = new ArrayList<String>();
for (int i = 0; i < index.size(); i++)
value.add(rowlist.get(index.get(i)));
values.add(value);
}
}
});
excel2003Reader.process("/root/files/t_bkyj_n_jbxx.xls");
upsertHBase();
}
private static void upsertHBase() {
StringBuffer buffer = new StringBuffer("UPSERT INTO T_BKYJ_N_JBXX(");
for (String str : col)
buffer.append(str).append(",");
buffer.deleteCharAt(buffer.length() - 1).append(") VALUES(");
for (String str : col)
buffer.append("?,");
buffer.deleteCharAt(buffer.length() - 1).append(")");
String sql = buffer.toString();
PreparedStatement preparedStatement = null;
try {
preparedStatement = connection.prepareStatement(sql);
for (List value : values) {
for (int i = 0; i < value.size(); i++) {
preparedStatement.setString(i + 1, value.get(i).toString());
}
preparedStatement.execute();
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
preparedStatement.close();
connection.commit();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
/********** End **********/
}第2关：文本文件数据存储HBase
CSV是一种通用的、相对简单的文件格式，被用户、商业和科学广泛应用，当警务大量数据存储在CSV中如何读取呢?
本关任务：编写一个Spark程序，读取csv数据，通过Phoenix插件将其存入HBase。
package com.educoder.policeCalculate;
import org.apache.log4j.Level;
import org.apache.log4j.Logger;
import org.apache.spark.sql.Dataset;
import org.apache.spark.sql.Row;
import org.apache.spark.sql.SaveMode;
import org.apache.spark.sql.SparkSession;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
public class CsvToHbase {
public static void Csv(SparkSession spark) {
Logger.getLogger("org").setLevel(Level.ERROR);
// 请在此处编写Spark程序，读取csv文件的数据，并通过Phoenix插件将其存入HBase
/********** Begin **********/
//获取hbase表T_BKYJ_N_JBXX的所有列
List<String> hbaseCol = Arrays.asList(spark.read()
.option("table", "T_BKYJ_N_JBXX")
.option("zkUrl", "127.0.0.1:2181")
.format("org.apache.phoenix.spark")
.load()
.columns());
//获取csv文件所有数据并摘出列名
Dataset<Row> rowDataset = spark.read()
.option("header", true)
.option("encoding", "utf-8")
.csv("/root/files1/a.csv");
List<String> csvCol = Arrays.asList(rowDataset.columns());
//转化为大写并找出共同的列
List<String> col = new ArrayList<String>();
for (String str : csvCol)
col.add(str.toUpperCase());
col.retainAll(hbaseCol);
//将csv的数据做成临时视图csvData并写入hbase
rowDataset.createOrReplaceTempView("csvData");
spark.sql("select " + col.toString().substring(1, col.toString().length() - 1) + " from csvData")
.write()
.mode(SaveMode.Overwrite)
.format("org.apache.phoenix.spark")
.option("zkUrl", "127.0.0.1:2181")
.option("table", "T_BKYJ_N_JBXX")
.save();
/********** End **********/
}
}1-4 警务大数据之实时存储
第一关：Kafka实时存储HBase
Kafka是由Apache软件基金会开发的一个开源流处理平台，当实时数据存储到 Kafka时，如何进行读取并存储呢？
本关任务：编写一个读取 Kafka 中的名为 test3 的 topic，通过Phoenix插件将其存入HBase的SparkStreaming程序。
package com.educoder.policeCalculate;
import org.apache.kafka.clients.consumer.ConsumerConfig;
import org.apache.kafka.clients.consumer.ConsumerRecord;
import org.apache.kafka.common.TopicPartition;
import org.apache.kafka.common.serialization.StringDeserializer;
import org.apache.spark.Accumulator;
import org.apache.spark.SparkConf;
import org.apache.spark.api.java.JavaSparkContext;
import org.apache.spark.api.java.function.Function;
import org.apache.spark.api.java.function.VoidFunction;
import org.apache.spark.streaming.Durations;
import org.apache.spark.streaming.api.java.JavaDStream;
import org.apache.spark.streaming.api.java.JavaInputDStream;
import org.apache.spark.streaming.api.java.JavaStreamingContext;
import org.apache.spark.streaming.kafka010.ConsumerStrategies;
import org.apache.spark.streaming.kafka010.KafkaUtils;
import org.apache.spark.streaming.kafka010.LocationStrategies;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.*;
public class KafkaToHBase {
public static void main(String[] args) throws InterruptedException {
SparkConf conf = new SparkConf().setMaster("local[*]").setAppName("Step2");
conf.set("spark.streaming.stopGracefullyOnShutdown", "true");
JavaSparkContext sc = new JavaSparkContext(conf);
/********* Begin *********/
//kafka参数
Map<String, Object> kafkaParams = new HashMap<String, Object>();
kafkaParams.put(ConsumerConfig.BOOTSTRAP_SERVERS_CONFIG, "127.0.0.1:9092");
kafkaParams.put(ConsumerConfig.KEY_DESERIALIZER_CLASS_CONFIG, StringDeserializer.class);
kafkaParams.put(ConsumerConfig.VALUE_DESERIALIZER_CLASS_CONFIG, StringDeserializer.class);
kafkaParams.put(ConsumerConfig.GROUP_ID_CONFIG, "sparkStreaming1");
kafkaParams.put(ConsumerConfig.ENABLE_AUTO_COMMIT_CONFIG, "false");
//Topic以及分区
TopicPartition topicPartition = new TopicPartition("test3", 0);
List<TopicPartition> topicPartitionList = new ArrayList<>();
topicPartitionList.add(topicPartition);
//偏移量
Map<TopicPartition, Long> offsets = new HashMap<TopicPartition, Long>();
offsets.put(topicPartition, 0l);
//初始化JavaStreamingContext并设置3秒处理一个批次
JavaStreamingContext javaStreamingContext = new JavaStreamingContext(sc, Durations.seconds(3));
//使用KafkaUtils对象创建流，使用Assign订阅主题
JavaInputDStream<ConsumerRecord<String, String>> message = KafkaUtils.createDirectStream(
javaStreamingContext,
LocationStrategies.PreferConsistent(),
ConsumerStrategies.Assign(topicPartitionList, kafkaParams, offsets));
JavaDStream<String> line = message.map(new Function<ConsumerRecord<String, String>, String>() {
@Override
public String call(ConsumerRecord<String, String> stringStringConsumerRecord) throws Exception {
return stringStringConsumerRecord.value();
}
});
//对数据进行处理
List<List<String>> values = new ArrayList<List<String>>();
JavaDStream<List<List<String>>> map = line.map(new Function<String, List<List<String>>>() {
@Override
public List<List<String>> call(String s) throws Exception {
List<String> value = new ArrayList<String>();
String[] split = s.split(",");
value.add(split[0]);//id
value.add(split[1]);//bkid
value.add(split[2]);//tzlx
value.add(split[3]);//dxlx
value.add(split[4]);//tzxx
value.add(split[5]);//lylx
value.add(split[6]);//lyb
value.add(split[7]);//lybid
value.add(split[8]);//gxsj
value.add(split[9]);//gldx
value.add(split[10]);//px
value.add(split[11]);//sfbd
values.add(value);
return values;
}
});
//判断rdd是否累计3次为空
Accumulator<Integer> a = sc.accumulator(0, "Error Accumulator");
map.foreachRDD(rdd -> {
if (rdd.isEmpty()) {
if (a.value() == 2) {
javaStreamingContext.stop(false, false);
System.exit(0);
} else {
a.add(1);
}
} else {
rdd.foreachPartition(new VoidFunction<Iterator<List<List<String>>>>() {
@Override
public void call(Iterator<List<List<String>>> listIterator) throws Exception {
while (listIterator.hasNext()) {
upsertTable(listIterator.next());
}
}
});
}
});
javaStreamingContext.start();
javaStreamingContext.awaitTermination();
/********* End *********/
}
public static void upsertTable(List<List<String>> values) {
Connection connection = null;
PreparedStatement ps = null;
try {
connection = DriverManager.getConnection("jdbc:phoenix:127.0.0.1:2181");
connection.setAutoCommit(false);
String sql = "UPSERT INTO T_BKYJ_N_TZ(ID,BKID,TZLX,DXLX,TZXX,LYLX,LYB,LYBID,GXSJ,GLDX,PX,SFBD) VALUES(?,?,?,?,?,?,?,?,?,?,?,?)";
ps = connection.prepareStatement(sql);
for (List<String> value : values) {
for (int i = 0; i < value.size(); i++)
ps.setString(i + 1, value.get(i));
ps.addBatch();
}
ps.executeBatch();
ps.clearBatch();
connection.commit();
} catch (Exception e) {
e.printStackTrace();
} finally {
if (ps != null) {
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}第二关：从HTTP读取数据存入HBase
上一关卡，我们学习到了如何实时读取Kafka的警务数据，那使用HTTP协议的警务数据如何读取呢？
本关任务：采用MyBatis框架，实时读取HTTP数据，用注解的方式，通过Phoenix插件将其存入HBase。
package com.educoder.policeBd.mapper;
import com.educoder.policeBd.bean.TBkyjNTz;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import java.util.List;
@Mapper
public interface TBkyjNTzMapper {
/********** Begin **********/
//插入HBase
@Insert("UPSERT INTO T_BKYJ_N_TZ(ID,BKID,TZLX,DXLX,TZXX,LYLX,LYB,LYBID,GXSJ,GLDX,PX,SFBD) VALUES(#{tBkyjNTz.ID},#{tBkyjNTz.BKID},#{tBkyjNTz.TZLX},#{tBkyjNTz.DXLX},#{tBkyjNTz.TZXX},#{tBkyjNTz.LYLX},#{tBkyjNTz.LYB},#{tBkyjNTz.LYBID},#{tBkyjNTz.GXSJ},#{tBkyjNTz.GLDX},#{tBkyjNTz.PX},#{tBkyjNTz.SFBD})")
void insert(@Param("tBkyjNTz") TBkyjNTz tBkyjNTz);
/********** End **********/
}package com.educoder.policeBd.cotroller;
import com.educoder.policeBd.bean.TBkyjNTz;
import com.educoder.policeBd.mapper.TBkyjNTzMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import java.util.List;
@RestController
@RequestMapping("TBkyjNTz")
public class TBkyjNTzCotroller {
@Autowired
private TBkyjNTzMapper tBkyjNTzMapper;
@RequestMapping("insert")
//调用mapper层方法将其插入HBase
public void upsertHbase(List<List<String>> lists) {
/********** Begin **********/
for(List<String> list:lists){
TBkyjNTz tBkyjNTz=new TBkyjNTz();
tBkyjNTz.setID(list.get(0));
tBkyjNTz.setBKID(list.get(1));
tBkyjNTz.setTZLX(list.get(2));
tBkyjNTz.setDXLX(list.get(3));
tBkyjNTz.setTZXX(list.get(4));
tBkyjNTz.setLYLX(list.get(5));
tBkyjNTz.setLYB(list.get(6));
tBkyjNTz.setLYBID(list.get(7));
tBkyjNTz.setGXSJ(list.get(8));
tBkyjNTz.setGLDX(list.get(9));
tBkyjNTz.setPX(list.get(10));
tBkyjNTz.setSFBD(list.get(11));
tBkyjNTzMapper.insert(tBkyjNTz);
}
/********** End **********/
}
}1-5警务大数据之关系数据库存储
第一关：MySQL支持
当外界数据为关系型数据库时，我们首先需要把关系型数据库表自动转换成 HBase表。本关任务：把MySQL表转换成HBase表。
package com.educodeer.police2.case1;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
public class Case1 {
/**
* 把mysql表转换成HBase表
*
* @param tableName Mysql表名
* @param hbaseConnection HBase连接
* @param mysqlConnection Mysql连接
* @throws SQLException
*/
public void mysqlTableCovent(String tableName, Connection hbaseConnection, Connection mysqlConnection) throws SQLException {
// 请在此处添加代码
/********** Begin *********/
ResultSet resultSet = mysqlConnection.createStatement().executeQuery("show full fields from " + tableName);
List<String> colNames = new ArrayList<String>();
List<String> colTypes=new ArrayList<String>();
while (resultSet.next()) {
String colName = resultSet.getString("Field");
String colType = resultSet.getString("Type");
if (colType.contains("int"))
colType = "INTEGER";
else colType = "VARCHAR";
colNames.add(colName);
colTypes.add(colType);
}
StringBuffer sqlBuffer = new StringBuffer("CREATE TABLE ").append(tableName).append("(");
for (int i = 0; i < colNames.size(); i++) {
String colName = colNames.get(i);
String colType = colTypes.get(i);
if (i == 0)
sqlBuffer.append(colName).append(" ").append(colType).append(" primary key,");
else
sqlBuffer.append(colName).append(" ").append(colType).append(",");
}
sqlBuffer.deleteCharAt(sqlBuffer.length() - 1).append(")");
String sql = sqlBuffer.toString();
hbaseConnection.createStatement().execute(sql);
/********** end *********/
}
}第二关：MySQL离线存储
当MySQL大量数据需要存储到HBase，如何保证高效稳定的进行传输呢，答案是使用MapReduce，本关任务：编写一个MapReduce把MySQL的内容存储到HBase。
package com.educodeer.police2.case1;
import org.apache.hadoop.conf.Configuration;
import org.apache.hadoop.conf.Configured;
import org.apache.hadoop.hbase.HBaseConfiguration;
import org.apache.hadoop.io.NullWritable;
import org.apache.hadoop.io.Writable;
import org.apache.hadoop.mapreduce.Job;
import org.apache.hadoop.mapreduce.Mapper;
import org.apache.hadoop.mapreduce.lib.db.DBConfiguration;
import org.apache.hadoop.mapreduce.lib.db.DBInputFormat;
import org.apache.hadoop.mapreduce.lib.db.DBWritable;
import org.apache.hadoop.mapreduce.lib.output.NullOutputFormat;
import org.apache.hadoop.util.Tool;
import java.io.DataInput;
import java.io.DataOutput;
import java.io.IOException;
import java.sql.*;
/**
* * 离线存储mysql数据
**/
public class Case2 extends Configured implements Tool {
private static class MyMapper extends Mapper<Object, SqlRecord, NullWritable, NullWritable> {
@Override
public void map(Object object, SqlRecord value, Context context) {
// 请在此处添加代码
/********** Begin *********/
String sql = "UPSERT INTO " + value.tableName + "(" + value.columnName.toString() + ") VALUES(" + value.value.toString() + ")";
Connection connection = null;
try {
connection = DriverManager.getConnection("jdbc:phoenix:127.0.0.1:2181");
connection.createStatement().execute(sql);
connection.commit();
} catch (SQLException e) {
} finally {
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
}
}
}
/********** end *********/
}
}
/**
* 在调用时,为单例模式,需考虑赋值方式
*/
public static class SqlRecord implements Writable, DBWritable {
//对结果值进行组装，例如 1,2,3,4
private StringBuffer value = null;
//对结果名称进行组装，例如id,'name',age,'content'
private StringBuffer columnName = null;
private String tableName = null;
public StringBuffer getValue() {
return value;
}
public StringBuffer getColumnName() {
return columnName;
}
public String getTableName() {
return tableName;
}
@Override
public void write(PreparedStatement statement) throws SQLException {
}
/**
* * 从 ResultSet 对象中读取表信息
**/
@Override
public void readFields(ResultSet resultSet) throws SQLException {
// 请在此处添加代码
/********** Begin *********/
columnName = new StringBuffer();
value = new StringBuffer();
ResultSetMetaData metaData = resultSet.getMetaData();
tableName = metaData.getTableName(1);
int count = metaData.getColumnCount();
for (int i = 0; i < count; i++) {
columnName.append(metaData.getColumnName(i + 1)).append(",");
String colType = metaData.getColumnTypeName(i + 1);
if (colType.equalsIgnoreCase("VARCHAR"))
value.append("'").append(resultSet.getString(i + 1)).append("',");
else value.append(resultSet.getString(i + 1)).append(",");
}
columnName.deleteCharAt(columnName.length() - 1);
value.deleteCharAt(value.length() - 1);
/********** end *********/
}
@Override
public void write(DataOutput out) throws IOException {
}
@Override
public void readFields(DataInput in) throws IOException {
}
}
@Override
public int run(String[] args) throws Exception {
//配置Job
String tableName = args[0];
Configuration conf = HBaseConfiguration.create(getConf());
//配置mysql信息
conf.set(DBConfiguration.DRIVER_CLASS_PROPERTY, "com.mysql.jdbc.Driver");
conf.set(DBConfiguration.URL_PROPERTY, "jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true");
conf.set(DBConfiguration.USERNAME_PROPERTY, "root");
conf.set(DBConfiguration.PASSWORD_PROPERTY, "123123");
Job job = Job.getInstance(conf);
//不需要reduce 设置为0线程
job.setNumReduceTasks(0);
//设置mapper节点的执行类
job.setMapperClass(MyMapper.class);
//通过查找给定类的来源来设置Jar
job.setJarByClass(Case2.class);
//设置输出到null文件
job.setOutputFormatClass(NullOutputFormat.class);
//SqlRecord 为mysql输出内容接受类，
//select * from " + tableName 为 输出内容
//select count(1) from " + tableName 为输出总数
DBInputFormat.setInput(job, SqlRecord.class, "select * from " + tableName, "select count(1) from " + tableName);
return job.waitForCompletion(true) ? 0 : 1;
}
}专题二 大数据批处理
2-1 警务大数据之上网相关指标批处理
第一关：频繁更换上网场所
本关卡属于警务大数据中的一个业务指标统计，从网吧的上网人员日志记录中进行批量计算，对上网地点异常的人员进行统计，对有犯罪动机的人员进行监控、预警。
本关任务：统计出触及频繁更换上网场所指标的人员。
package com.yy;
import org.apache.spark.sql.Dataset;
import org.apache.spark.sql.Row;
import org.apache.spark.sql.SaveMode;
import org.apache.spark.sql.SparkSession;
public class Police {
public static void main(String[] args) {
//请在以下提示框内填写你的代码
/**********Begin**********/
//zookeeper地址
String zk = "127.0.0.1:2181";
//查询sql
String sql = "SELECT ZJHM AS zjhm, SUBSTR(RZSJ,1,8) AS theday, COUNT(*) AS num " +
"FROM temp GROUP BY SUBSTR(RZSJ,1,8), ZJHM having COUNT(*)>10";
//输出结果表表名
String outputTable = "pfghswd";
//数据源表名
String sourceTable = "dw.ZNB_04_121800005";
//创建SparkSession
SparkSession sparkSession = SparkSession.builder().appName("police").master("local").getOrCreate();
Dataset<Row> dwDataset = sparkSession.read()
.format("org.apache.phoenix.spark")
.option("zkUrl", zk)
.option("table", sourceTable)
.load();
dwDataset.createOrReplaceTempView("temp");
//执行查询
Dataset<Row> sqlDataSet = sparkSession.sql(sql);
//查询结果导入HBase
sqlDataSet
.write()
.mode(SaveMode.Overwrite)
.format("org.apache.phoenix.spark")
.option("zkUrl", zk)
.option("table", outputTable)
.save();
//停止
sparkSession.stop();
/**********End**********/
}
}第二关：多次同上网
本关卡属于警务大数据中的一个业务指标统计，从网吧的上网人员日志记录中进行批量计算，对上网时间异常的人员进行统计，对有犯罪动机的人员进行监控、预警。
本关任务：统计出触及多次同上网指标的人员。
指标定义：一个月内一同上网超过十次，两个人同一天在同一家网吧上过网即作为一次。
package com.yy;
import org.apache.spark.sql.Dataset;
import org.apache.spark.sql.Row;
import org.apache.spark.sql.SaveMode;
import org.apache.spark.sql.SparkSession;
public class Police {
public static void main(String[] args) {
//请在以下提示框内填写你的代码
/**********Begin**********/
//zookeeper地址
String zk = "127.0.0.1:2181";
//查询sql
String sql = "SELECT zjhm1, zjhm2, COUNT(*) AS swcs " +
"FROM " +
"(SELECT temp1.ZJHM AS zjhm1, temp2.ZJHM AS zjhm2 " +
"FROM " +
"(SELECT CONCAT(SUBSTR(RZSJ, 1, 8), LGMC) AS key1, ZJHM " +
"FROM temp " +
"GROUP BY CONCAT(SUBSTR(RZSJ, 1, 8), LGMC), ZJHM) temp1 " +
"JOIN " +
"(SELECT CONCAT(SUBSTR(RZSJ, 1, 8), LGMC) AS key2, ZJHM " +
"FROM temp " +
"GROUP BY CONCAT(SUBSTR(RZSJ, 1, 8), LGMC), ZJHM) temp2 " +
"ON temp1.key1 = temp2.key2 " +
"WHERE temp1.ZJHM != temp2.ZJHM) " +
"GROUP BY zjhm1, zjhm2 " +
"HAVING cast(zjhm1 AS STRING) > cast(zjhm2 AS STRING) AND COUNT(*)>20";
//输出结果表表名
String outputTable = "dctsw";
//数据源表名
String sourceTable = "dw.ZNB_04_121800005";
//创建SparkSession，master模式设为local
SparkSession sparkSession = SparkSession.builder().appName("police").master("local").getOrCreate();
//读取数据，创建临时表
Dataset<Row> sourceData = sparkSession.read()
.format("org.apache.phoenix.spark")
.option("zkUrl", zk)
.option("table", sourceTable)
.load();
sourceData.createOrReplaceTempView("temp");
//执行查询
Dataset<Row> outputData = sparkSession.sql(sql);
//查询结果导入HBase
outputData
.write()
.mode(SaveMode.Overwrite)
.format("org.apache.phoenix.spark")
.option("zkUrl", zk)
.option("table", outputTable)
.save();
//停止
sparkSession.stop();
/**********End**********/
}
}专题三 实时流计算
3-1 警务大数据之大屏幕实时流数据监控
第一关：车辆相关信息实时计算
本关卡属于警务大数据中的实时计算模块，对车辆相关信息进行实时计算，实时计算出车辆在线数、车辆活跃数、致命故障数量、严重故障数量、一般故障数量、轻微故障数量、房车报警数量、旅行车报警数量、桥跑车报警数量、跑车报警数量、敞篷车报警数量等，为下一关的实时数据可视化提供数据基础。
本关任务：编写一个 SparkStructuredStreaming 程序完成车辆相关信息实时统计。
package net.educoder.spark
import org.apache.spark.sql.SparkSession
object KafkaSparkStreaming {
def main(args: Array[String]): Unit = {
val spark = SparkSession.builder().master("local").appName("demo").getOrCreate()
spark.sparkContext.setLogLevel("error")
/** ******begin ******** */
val stream = spark
.readStream
.format("kafka")
.option("kafka.bootstrap.servers", "127.0.0.1:9092")
.option("subscribe", "demo")
.load()
val frame = stream.selectExpr("CAST(value AS STRING)")
import spark.implicits._
val query = frame.as[String].map(x => {
val arr = x.split("\t")
val carType = arr(1).toInt
val warning = arr(3).toInt
val fault = arr(4).toInt
val speed = arr(5).toInt
val online = 1
val activeCount = if (speed > 0) 1 else 0
val fault_4 = if (fault == 4) 1 else 0
val fault_3 = if (fault == 3) 1 else 0
val fault_2 = if (fault == 2) 1 else 0
val fault_1 = if (fault == 1) 1 else 0
val warning_5 = if (warning == 1 || carType == 5) 1 else 0
val warning_4 = if (warning == 1 || carType == 4) 1 else 0
val warning_3 = if (warning == 1 || carType == 3) 1 else 0
val warning_2 = if (warning == 1 || carType == 2) 1 else 0
val warning_1 = if (warning == 1 || carType == 1) 1 else 0
event(online, activeCount, fault_4, fault_3, fault_2, fault_1, warning_5, warning_4, warning_3, warning_2, warning_1, 0)
})
query
.groupBy("flag")
.sum("onlineCount", "activeCount", "fault_4", "fault_3", "fault_2", "fault_1", "warning_4", "warning_4", "warning_3", "warning_2", "warning_1")
.map(x => {
var result = ""
for (i <- 1 to 11) {
result = result + x.get(i) + ","
}
result.substring(0, result.length - 1)
})
.writeStream
.outputMode("complete")
.format("kafka")
.option("kafka.bootstrap.servers", "127.0.0.1:9092")
.option("checkpointLocation", "/root/sparkStreaming")
.option("topic", "demo2")
.start().awaitTermination()
/** ******end ******** */
}
//在线数、活跃数、致命故障数量、严重故障数量、一般故障数量、轻微故障数量、房车报警数量、旅行车报警数量、桥跑车报警数量、跑车报警数量、敞篷车报警数量
case class event(onlineCount: Int, activeCount: Int, fault_4: Int, fault_3: Int, fault_2: Int, fault_1: Int, warning_5: Int, warning_4: Int, warning_3: Int, warning_2: Int, warning_1: Int, flag: Int)
}第二关：实时数据可视化
本关卡属于警务大数据中的实时可视化模块，在上一关的数据基础上进行实时数据展示，让车辆监控人员更容易地制定出监控策略、更可靠地保障交通安全、发挥交通基础设施效能、提升交通系统运行效率和管理水平，为通畅的公众出行和可持续的经济发展服务。
本关任务：使用WebSocket完成Kafka数据推送及数据可视化
- WebSocketServer
package net.educoder.app.service;
import net.educoder.app.utils.kafkaClient;
import org.springframework.stereotype.Component;
import javax.websocket.OnOpen;
import javax.websocket.Session;
import javax.websocket.server.ServerEndpoint;
import java.io.IOException;
@ServerEndpoint("/websocket")
@Component
public class WebSocketServer {
/**
* 需求如下：
* 客户端与Websocket服务端建立连接之后，启动 kafkaClient 线程，将当前 session 作为参数传入
*/
/********** begin **********/
@OnOpen
public void onOpen(Session session){
new Thread(new kafkaClient(session)).start();
}
/********** end **********/
}- kafkaClient
package net.educoder.app.utils;
import com.alibaba.fastjson.JSON;
import net.educoder.app.entity.Event;
import net.educoder.app.service.WebSocketServer;
import org.apache.kafka.clients.consumer.ConsumerRecord;
import org.apache.kafka.clients.consumer.ConsumerRecords;
import org.apache.kafka.clients.consumer.KafkaConsumer;
import javax.websocket.Session;
import java.io.IOException;
import java.util.Arrays;
import java.util.Properties;
public class kafkaClient implements Runnable {
private Session session;
public kafkaClient(Session session) {
this.session = session;
}
/********** begin **********/
@Override
public void run() {
// 1. 创建 Properties 对象
Properties props = new Properties();
// 2. 配置连接 kafka 的参数
/**
* bootstrap.servers:127.0.0.1:9092
* group.id:my_group
* enable.auto.commit:true
* auto.commit.interval.ms:1000
* key.deserializer:org.apache.kafka.common.serialization.StringDeserializer
* value.deserializer:org.apache.kafka.common.serialization.StringDeserializer
*/
props.put("bootstrap.servers", "127.0.0.1:9092");
props.put("group.id", "my_group");
props.put("enable.auto.commit", "true");
props.put("auto.commit.interval.ms", "1000");
props.put("key.deserializer", "org.apache.kafka.common.serialization.StringDeserializer");
props.put("value.deserializer", "org.apache.kafka.common.serialization.StringDeserializer");
//3. 创建 KafkaConsumer 对象
KafkaConsumer<String, String> consumer = new KafkaConsumer<>(props);
//4. 订阅名为 demo2 的 topic
consumer.subscribe(Arrays.asList("demo2"));
//5. 死循环，不断消费订阅的数据
while (true) {
//6. 使用 KafkaConsumer 拉取数据
ConsumerRecords<String, String> records = consumer.poll(100);
//7. 遍历数据，将数据封装到 Event 对象中，使用 fastjson 将 Event 对象转换成 JSON 字符串，最后调用 session.getBasicRemote().sendText(String msg); 将数据推送到前端页面
/**
*
* kafka消费的数据如下：
* 在线数,活跃数,致命故障数量,严重故障数量,一般故障数量,轻微故障数量,房车报警数量,旅行车报警数量,桥跑车报警数量,跑车报警数量,敞篷车报警数量
* 3608335,1802435,25809,63260,15879,38612,77507,29697,10542,67913,42963
* 1745818,1365579,29449,46912,58208,29464,46830,55611,90398,94499,89332
* 3768443,2243235,32830,12980,26930,61768,44310,20354,11672,91021,52017
*
*
* Event对象属性如下：
* private String onlineCount; 在线数
* private String activeCount; 活跃数
* private String fault4Count; 致命故障数量
* private String fault3Count; 严重故障数量
* private String fault2Count; 一般故障数量
* private String fault1Count; 轻微故障数量
* private String warning5Count; 房车报警数量
* private String warning4Count; 旅行车报警数量
* private String warning3Count; 桥跑车报警数量
* private String warning2Count; 跑车报警数量
* private String warning1Count; 敞篷车报警数量
*
* Event 对象有参构造如下：
* public Event(String onlineCount, String activeCount, String fault4Count, String fault3Count, String fault2Count, String fault1Count, String warning5Count, String warning4Count, String warning3Count, String warning2Count, String warning1Count){....}
*
*/
for (ConsumerRecord<String, String> record : records) {
String value = record.value();
String[] arr = value.split(",");
if (arr.length == 11) {
//在线数、活跃数、致命故障数量、严重故障数量、一般故障数量、轻微故障数量、房车报警数量、旅行车报警数量、桥跑车报警数量、跑车报警数量、敞篷车报警数量
Event event = new Event(arr[0], arr[1], arr[2], arr[3], arr[4], arr[5], arr[6], arr[7], arr[8], arr[9], arr[10]);
String s = JSON.toJSONString(event);
try {
session.getBasicRemote().sendText(s);
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
}
/********** end **********/
}- index.html
<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.thymeleaf.org">
<head>
<meta charset="UTF-8">
<title>智慧警务-交通大数据监控平台</title>
<link rel="stylesheet" th:href="@{css/common.css}">
<link rel="stylesheet" th:href="@{css/map.css}">
</head>
<body>
<div class="data">
<div class="data-title">
<div class="title-left fl"></div>
<div class="title-center fl"></div>
<div class="title-right fr"></div>
</div>
<div class="data-content">
<div class="con-left fl">
<div class="left-top">
<div class="info">
<div class="info-title">实时统计</div>
<img th:src="@{img/bj-1.png}" alt="" class="bj-1">
<img th:src="@{img/bj-2.png}" alt="" class="bj-2">
<img th:src="@{img/bj-3.png}" alt="" class="bj-3">
<img th:src="@{img/bj-4.png}" alt="" class="bj-4">
<div class="info-main">
<div class="info-1">
<div class="info-img fl">
<img th:src="@{img/info-img-1.png}" alt="">
</div>
<div class="info-text fl">
<p>车辆总数(辆)</p>
<p id="CountNum">12,457</p>
</div>
</div>
<div class="info-2">
<div class="info-img fl">
<img th:src="@{img/info-img-2.png}" alt="">
</div>
<div class="info-text fl">
<p>当前在线数(辆)</p>
<p id="OnlineNum">12,457</p>
</div>
</div>
<div class="info-3">
<div class="info-img fl">
<img th:src="@{img/info-img-3.png}" alt="">
</div>
<div class="info-text fl">
<p>当前活跃数(辆)</p>
<p id="activeNum">12,457</p>
</div>
</div>
<div class="info-4">
<div class="info-img fl">
<img th:src="@{img/info-img-4.png}" alt="">
</div>
<div class="info-text fl">
<p>当前活跃率(%)</p>
<p id="liveness">83</p>
</div>
</div>
</div>
</div>
<div class="top-bottom">
<div class="title"></div>
<img th:src="@{img/bj-1.png}" alt="" class="bj-1">
<img th:src="@{img/bj-2.png}" alt="" class="bj-2">
<img th:src="@{img/bj-3.png}" alt="" class="bj-3">
<img th:src="@{img/bj-4.png}" alt="" class="bj-4">
<div id="echarts_1" class="charts"></div>
</div>
</div>
<div class="left-bottom">
<div class="title"></div>
<img th:src="@{img/bj-1.png}" alt="" class="bj-1">
<img th:src="@{img/bj-2.png}" alt="" class="bj-2">
<img th:src="@{img/bj-3.png}" alt="" class="bj-3">
<img th:src="@{img/bj-4.png}" alt="" class="bj-4">
<div id="echarts_2" class="charts"></div>
</div>
</div>
<div class="con-center fl">
<div class="map-num">
<p>实时行驶车辆(辆)</p>
<div class="num" id="runningNum">
<span>1</span>
<span>,</span>
<span>2</span>
<span>3</span>
<span>4</span>
<span>,</span>
<span>5</span>
<span>6</span>
<span>7</span>
</div>
</div>
<div class="cen-top" id="map"></div>
<div class="cen-bottom">
<div class="title"></div>
<img th:src="@{img/bj-1.png}" alt="" class="bj-1">
<img th:src="@{img/bj-2.png}" alt="" class="bj-2">
<img th:src="@{img/bj-3.png}" alt="" class="bj-3">
<img th:src="@{img/bj-4.png}" alt="" class="bj-4">
<div id="echarts_3" class="charts"></div>
</div>
</div>
<div class="con-right fr">
<div class="right-top">
<div class="title">汽车故障统计</div>
<img th:src="@{img/bj-1.png}" alt="" class="bj-1">
<img th:src="@{img/bj-2.png}" alt="" class="bj-2">
<img th:src="@{img/bj-3.png}" alt="" class="bj-3">
<img th:src="@{img/bj-4.png}" alt="" class="bj-4">
<div id="echarts_5" class="charts"></div>
</div>
<div class="right-center">
<div class="title">电池报警车辆统计</div>
<img th:src="@{img/bj-1.png}" alt="" class="bj-1">
<img th:src="@{img/bj-2.png}" alt="" class="bj-2">
<img th:src="@{img/bj-3.png}" alt="" class="bj-3">
<img th:src="@{img/bj-4.png}" alt="" class="bj-4">
<div id="echarts_6" class="charts"></div>
</div>
<div class="right-bottom">
<div class="title"></div>
<img th:src="@{img/bj-1.png}" alt="" class="bj-1">
<img th:src="@{img/bj-2.png}" alt="" class="bj-2">
<img th:src="@{img/bj-3.png}" alt="" class="bj-3">
<img th:src="@{img/bj-4.png}" alt="" class="bj-4">
</div>
</div>
</div>
</div>
</body>
<script th:src="@{js/jquery-2.1.1.min.js}"></script>
<script th:src="@{js/echarts.min.js}"></script>
<script th:src="@{js/china.js}"></script>
<script th:src="@{js/echarts.js}"></script>
<script type="text/javascript" th:inline="javascript">
var socket;
if (typeof(WebSocket) == "undefined") {
console.log("您的浏览器不支持WebSocket");
} else {
console.log("您的浏览器支持WebSocket");
//实现化WebSocket对象，指定要连接的服务器地址与端口 建立连接
var href = window.location.href;
var arr = href.split(":");
var ip = arr[1];
var port = arr[2];
socket = new WebSocket("ws://" + ip + ":" + port + "/websocket");
//打开事件
socket.onopen = function () {
console.log("Socket 已打开");
};
//获得消息事件
socket.onmessage = function (msg) {
/********** begin **********/
//1.获取 WebSocket 服务端推送过来的数据并将其转换成 JSON 对象并命名为 d
var d = JSON.parse(msg.data);
//2.从 JSON 对象 d 中获取carCount、onlineCount、activeCount、active 并将其分别替换 id 为 CountNum、OnlineNum、activeNum 的文本内容
$("#CountNum").text(d.carCount);
$("#OnlineNum").text(d.onlineCount);
$("#activeNum").text(d.activeCount)
$("#liveness").text(d.active)
var runningNum = numberHandle(d.activeCount);
$("#runningNum").html(runningNum)
//3.汽车故障图表生成，创建一个存储4种故障类型数量的数组（fault4Count、fault3Count、fault2Count、fault1Count），调用 fault(Array arr) 函数生成图表
var faultList = new Array(d.fault4Count, d.fault3Count, d.fault2Count, d.fault1Count)
fault(faultList)
//4.汽车电池警告图表生成，创建一个存储5种电池警告类型数量的数组（warning5Count、warning4Count、warning3Count、warning2Count，warning1Count），调用 warning(Array arr) 函数生成图表
var warningList = new Array(d.warning5Count, d.warning4Count, d.warning3Count, d.warning2Count, d.warning1Count)
warning(warningList)
/********** end **********/
};
//关闭事件
socket.onclose = function () {
console.log("Socket已关闭");
};
//发生了错误事件
socket.onerror = function () {
alert("Socket发生了错误");
}
}
function numberHandle(str) {
var arr = str.split("");
var result = "";
for (var x = 0; x < arr.length; x++) {
result += "<span>" + arr[x] + "</span>"
}
return result;
}
function fault(dataList) {
var myChart = echarts.init(document.getElementById('echarts_5'));
var xData = function () {
var data = ['致命故障', '严重故障', '一般故障', '轻微故障'];
return data;
}();
option = {
tooltip: {
show: "true",
trigger: 'item',
backgroundColor: 'rgba(0,0,0,0.4)', // 背景
padding: [8, 10], //内边距
// extraCssText: 'box-shadow: 0 0 3px rgba(255, 255, 255, 0.4);', //添加阴影
formatter: function (params) {
if (params.seriesName != "") {
return params.name + ' ： ' + params.value + ' 辆';
}
},
},
grid: {
borderWidth: 0,
top: 20,
bottom: 35,
left: 55,
right: 30,
textStyle: {
color: "#fff"
}
},
xAxis: [{
type: 'category',
axisTick: {
show: false
},
axisLine: {
show: true,
lineStyle: {
color: '#363e83',
}
},
axisLabel: {
inside: false,
textStyle: {
color: '#bac0c0',
fontWeight: 'normal',
fontSize: '12',
},
},
data: xData,
}, {
type: 'category',
axisLine: {
show: false
},
axisTick: {
show: false
},
axisLabel: {
show: false
},
splitArea: {
show: false
},
splitLine: {
show: false
},
data: xData,
}],
yAxis: {
type: 'value',
axisTick: {
show: false
},
axisLine: {
show: true,
lineStyle: {
color: '#32346c',
}
},
splitLine: {
show: true,
lineStyle: {
color: '#32346c ',
}
},
axisLabel: {
textStyle: {
color: '#bac0c0',
fontWeight: 'normal',
fontSize: '12',
},
formatter: '{value}',
},
},
series: [{
type: 'bar',
itemStyle: {
normal: {
show: true,
color: new echarts.graphic.LinearGradient(0, 0, 0, 1, [{
offset: 0,
color: '#00c0e9'
}, {
offset: 1,
color: '#3b73cf'
}]),
barBorderRadius: 50,
borderWidth: 0,
},
emphasis: {
shadowBlur: 15,
shadowColor: 'rgba(105,123, 214, 0.7)'
}
},
zlevel: 2,
barWidth: '20%',
data: dataList
},
{
name: '',
type: 'bar',
xAxisIndex: 1,
zlevel: 1,
itemStyle: {
normal: {
color: '#121847',
borderWidth: 0,
shadowBlur: {
shadowColor: 'rgba(255,255,255,0.31)',
shadowBlur: 10,
shadowOffsetX: 0,
shadowOffsetY: 2,
},
}
},
barWidth: '20%'
}
]
};
myChart.setOption(option);
window.addEventListener("resize", function () {
myChart.resize();
});
}
function warning(dataList) {
var myChart = echarts.init(document.getElementById('echarts_6'));
var xData = function () {
var data = ['房车', '旅行轿车', '轿跑车', '跑车', '敞篷车'];
return data;
}();
option = {
// backgroundColor: "#141f56",
tooltip: {
show: "true",
trigger: 'item',
backgroundColor: 'rgba(0,0,0,0.4)', // 背景
padding: [8, 10], //内边距
// extraCssText: 'box-shadow: 0 0 3px rgba(255, 255, 255, 0.4);', //添加阴影
formatter: function (params) {
if (params.seriesName != "") {
return params.name + ' ： ' + params.value + ' 辆';
}
},
},
grid: {
borderWidth: 0,
top: 20,
bottom: 35,
left: 55,
right: 30,
textStyle: {
color: "#fff"
}
},
xAxis: [{
type: 'category',
axisTick: {
show: false
},
axisLine: {
show: true,
lineStyle: {
color: '#363e83',
}
},
axisLabel: {
inside: false,
textStyle: {
color: '#bac0c0',
fontWeight: 'normal',
fontSize: '12',
},
},
data: xData,
}, {
type: 'category',
axisLine: {
show: false
},
axisTick: {
show: false
},
axisLabel: {
show: false
},
splitArea: {
show: false
},
splitLine: {
show: false
},
data: xData,
}],
yAxis: {
type: 'value',
axisTick: {
show: false
},
axisLine: {
show: true,
lineStyle: {
color: '#32346c',
}
},
splitLine: {
show: true,
lineStyle: {
color: '#32346c ',
}
},
axisLabel: {
textStyle: {
color: '#bac0c0',
fontWeight: 'normal',
fontSize: '12',
},
formatter: '{value}',
},
},
series: [{
type: 'bar',
itemStyle: {
normal: {
show: true,
color: new echarts.graphic.LinearGradient(0, 0, 0, 1, [{
offset: 0,
color: '#00c0e9'
}, {
offset: 1,
color: '#3b73cf'
}]),
barBorderRadius: 50,
borderWidth: 0,
},
emphasis: {
shadowBlur: 15,
shadowColor: 'rgba(105,123, 214, 0.7)'
}
},
zlevel: 2,
barWidth: '20%',
data: dataList,
},
{
name: '',
type: 'bar',
xAxisIndex: 1,
zlevel: 1,
itemStyle: {
normal: {
color: '#121847',
borderWidth: 0,
shadowBlur: {
shadowColor: 'rgba(255,255,255,0.31)',
shadowBlur: 10,
shadowOffsetX: 0,
shadowOffsetY: 2,
},
}
},
barWidth: '20%'
}
]
};
// 使用刚指定的配置项和数据显示图表。
myChart.setOption(option);
window.addEventListener("resize", function () {
myChart.resize();
});
}
</script>
</html>专题四 数据可视化
4-1 警务大数据之慧搜
4-2 警务大数据之精准布控
第一关：布控人员查询
本关卡是对前端框架layUI的数据表格模块的熟悉、掌握并在此基础上完成布控人员数据渲染，对下一关的人员行为数据展示做铺垫。
本关任务：使用layUI中的数据表格模块完成数据渲染功能
- Step1Mapper
package net.educoder.mapper;
import net.educoder.entity.User;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import java.util.List;
@Mapper
public interface Step1Mapper {
/********** Begin **********/
@Select("select * from monitoring")
List<User> findAllUser();
/********** End **********/
@Select("select * from monitoring where 1=0")
void init();
}- Step1ServiceImpl
package net.educoder.service.impl;
import net.educoder.entity.User;
import net.educoder.mapper.Step1Mapper;
import net.educoder.service.Step1Service;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
@Service
public class Step1ServiceImpl implements Step1Service {
/********** Begin **********/
@Autowired
private Step1Mapper mapper;
@Override
public List<User> findMonitoringUser() {
return mapper.findAllUser();
}
/********** End **********/
}- Step1Controller
package net.educoder.controller;
import net.educoder.entity.User;
import net.educoder.service.Step1Service;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
@Controller
public class Step1Controller {
/********** Begin **********/
@Autowired
private Step1Service service;
@RequestMapping("/monitoringUser")
@ResponseBody
public Map<String, Object> monitoringUser() {
HashMap<String, Object> map = new HashMap<>();
List<User> list = service.findMonitoringUser();
map.put("msg", "");
map.put("code", 0);
map.put("data", list);
map.put("count", list.size());
return map;
}
/********** End **********/
}- demo.html（其实没有写html也能通过）
<html xmlns:th="http://www.thymeleaf.org">
<head>
<title>精准布控</title>
<link rel="stylesheet" type="text/css" th:href="@{/jw/css/index.css}">
<link rel="stylesheet" type="text/css" th:href="@{/jw/css/magic-check.css}">
<script type="text/javascript" th:src="@{/jw/js/jquery-3.3.1.min.js}"></script>
<script type="text/javascript" th:src="@{/jw/js/index.js}"></script>
<link rel="stylesheet" type="text/css" th:href="@{/layui/css/layui.css}">
</head>
<body>
<div class="policeHead">
<img th:src="@{/jw/images/logo.png}"/>
<a href="/" class="returnToIndex">返回</a>
<table id="demo" lay-filter="test"></table>
<div id="jsmind_container"></div>
</div>
<script type="text/html" id="barDemo">
<a class="layui-btn layui-btn-xs layui-btn-normal layui-btn-fluid" lay-event="cat">查看</a>
</script>
<script type="text/javascript" th:src="@{/layui/layui.all.js}"></script>
<script th:inline="javaScript">
layui.use('table', function () {
var table = layui.table;
/********** Begin **********/
table.render({
elem: '#demo',
height: 312,
url: '/monitoringUser',
page: true,
cols: [[
{field: 'id', title: '身份证号码', fixed: 'left'},
{field: 'trueName', title: '真实姓名'},
{field: 'monitoringTime', title: '布控时间'},
{field: 'monitoringRange', title: '布控范围'}
]]
})
/********** End **********/
});
</script>
</body>
</html>第二关：布控人员日志查询
本关卡是警务大数据可视化层的精准布控模块，对人员行为日志数据进行可视化，让警务人员更好地从数据可视化中得出结论并作出相应的决策。
本关任务：完成布控人员日志查询功能。
- Step2Mapper
package net.educoder.mapper;
import net.educoder.entity.Event;
import net.educoder.entity.User;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import java.util.List;
@Mapper
public interface Step2Mapper {
/********** Begin **********/
@Select("select id, log from eventLog where code = #{code}")
List<Event> findEventLogByCode(String code);
@Select("select * from monitoring where id = #{id}")
User findUserById(String id);
/********** End **********/
}- Step2ServiceImpl
package net.educoder.service.impl;
import net.educoder.entity.Event;
import net.educoder.entity.JsMind;
import net.educoder.entity.User;
import net.educoder.mapper.Step2Mapper;
import net.educoder.service.Step2Service;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.ArrayList;
import java.util.List;
@Service
public class Step2ServiceImpl implements Step2Service {
/********** Begin **********/
@Autowired
private Step2Mapper mapper;
@Override
public List<JsMind> findEventLogByCode(String id) {
List<JsMind> jsMinds = new ArrayList<>();
User user = mapper.findUserById(id);
List<Event> events = mapper.findEventLogByCode(id);//id作code
JsMind root = new JsMind("root", "true", "", user.getTrueName());
jsMinds.add(root);
for (int i = 0; i < events.size(); i++) {
JsMind jsMind = new JsMind(String.valueOf(i), "false", "root", events.get(i).getLog());
jsMinds.add(jsMind);
}
return jsMinds;
}
/********** End **********/
}- Step2Controller
package net.educoder.controller;
import net.educoder.entity.JsMind;
import net.educoder.service.Step2Service;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
@Controller
public class Step2Controller {
/********** Begin **********/
@Autowired
private Step2Service service;
@RequestMapping("/jsMindInfo")
@ResponseBody
public Map<String, Object> jsMindInfo(String id) {
Map<String, Object> map = new HashMap<>();
List<JsMind> jsMinds = service.findEventLogByCode(id);
map.put("list", jsMinds);
return map;
}
/********** End **********/
}- accurate_control.htm（其实没有写html也能通过）
<html xmlns:th="http://www.thymeleaf.org">
<head>
<title>精准布控</title>
<link rel="stylesheet" type="text/css" th:href="@{/jw/css/index.css}">
<link rel="stylesheet" type="text/css" th:href="@{/jw/css/magic-check.css}">
<script type="text/javascript" th:src="@{/jw/js/jquery-3.3.1.min.js}"></script>
<script type="text/javascript" th:src="@{/jw/js/index.js}"></script>
<link rel="stylesheet" type="text/css" th:href="@{/layui/css/layui.css}">
<link rel="stylesheet" type="text/css" th:href="@{/jsmind/jsmind.css}">
<script type="text/javascript" th:src="@{/jsmind/jsmind.draggable.js}"></script>
<script type="text/javascript" th:src="@{/jsmind/jsmind.js}"></script>
<style type="text/css">
#jsmind_container {
height: 600px;
border: solid 1px #ccc;
/*background:#f4f4f4;*/
background: #f4f4f4;
}
</style>
</head>
<body>
<div class="policeHead">
<img th:src="@{/jw/images/logo.png}"/>
<a href="/" class="returnToIndex">返回</a>
<table id="demo" lay-filter="test"></table>
<div id="jsmind_container"></div>
</div>
<script type="text/html" id="barDemo">
<a class="layui-btn layui-btn-xs layui-btn-normal layui-btn-fluid" lay-event="cat">查看</a>
</script>
<script type="text/javascript" th:src="@{/layui/layui.all.js}"></script>
<script th:inline="javaScript">
layui.use('table', function () {
var table = layui.table;
//第一个实例
table.render({
elem: '#demo'
, height: 312
, url: '/monitoringUser'
, page: true //开启分页
, cols: [[ //表头
{field: 'id', title: '身份证号码', fixed: 'left'}
, {field: 'trueName', title: '真实姓名'}
, {field: 'monitoringTime', title: '布控时间'}
, {field: 'monitoringRange', title: '布控范围'}
, {fixed: 'right', title: '操作', toolbar: '#barDemo'}
]]
});
table.on('tool(test)', function (obj) {
var data = obj.data;
if (obj.event === 'cat') {
load_jsmind(data.id)
}
});
});
var options = {
container: 'jsmind_container',
editable: true,
theme: 'primary'
}
var jm = new jsMind(options);
function load_jsmind(id) {
/********** Begin **********/
$.ajax({
url: "/jsMindInfo",
data: {"id": id},
success: function (data) {
var mind = {
"meta": {
"name": "example",
"author": "hizzgdev@163.com",
"version": "0.2"
},
"format": "node_array",
"data": data.list
};
jm.show(mind)
},
dataType: "json",
type: "post"
});
/********** End **********/
}
</script>
</body>
</html>
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
