Technical Architecture

The solution utilizes HBase for big data storage, MySQL for structured querying, and ECharts for frontend visualization. The primary tools involved are:

1. HBase & MapReduce: For distributed storage and data processing.
2. MySQL: Serves as a relational interface for aggregated data.
3. ECharts: Provides interactive charting capabilities.
4. Java Servlet/JSP: Handles backend logic and frontend rendering.

Implementation Strategy

The implementation follows a pipeline approach:

1. Data Cleaning: Raw data is processde and validated. For testing purposes, structured datasets with predictable patterns are used.
2. Statistical Aggregation: MapReduce jobs or direct HBase scans calculate statistics based on specific criteria (e.g., IP frequency, traffic volume).
3. Data Transfer: Processed data is synchronized from HBase to MySQL to facilitate easier querying for the web layer.
4. Visualization Layer: A Java Servlet retrieves data from MySQL, converts it to JSON, and forwards it to a JSP page where ECharts renders the charts.

Data Migration: HBase to MySQL

To bridge the gap between HBase's column-oriented storage and the web application's need for structured data, a migration utility is implemented. This utility connects to HBase, retrieves specific records, and inserts them into MySQL.

package com.data.pipeline;

import org.apache.hadoop.conf.Configuration;
import org.apache.hadoop.hbase.HBaseConfiguration;
import org.apache.hadoop.hbase.TableName;
import org.apache.hadoop.hbase.client.*;
import org.apache.hadoop.hbase.util.Bytes;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class DataMigrationTool {

    private static Connection sqlConnection;
    private static org.apache.hadoop.hbase.client.Connection hbaseConnection;
    private static Admin hbaseAdmin;

    public static void main(String[] args) {
        initHBase();
        initMySQL();

        try {
            migrateRecords(1000, 2217);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeConnections();
        }
    }

    private static void initHBase() {
        try {
            Configuration config = HBaseConfiguration.create();
            config.set("hbase.zookeeper.quorum", "cluster-node-1,cluster-node-2,cluster-node-3");
            hbaseConnection = ConnectionFactory.createConnection(config);
            hbaseAdmin = hbaseConnection.getAdmin();
            System.out.println("HBase connection established.");
        } catch (IOException e) {
            System.err.println("Failed to connect to HBase.");
            e.printStackTrace();
        }
    }

    private static void initMySQL() {
        String url = "jdbc:mysql://localhost:3306/data_warehouse?useSSL=false&serverTimezone=UTC";
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            sqlConnection = DriverManager.getConnection(url, "admin", "password");
            System.out.println("MySQL connection established.");
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
    }

    private static void migrateRecords(int startId, int endId) throws IOException, SQLException {
        String insertSQL = "INSERT INTO traffic_stats (ip, access_date, day_of_week, traffic, content_type, record_id) VALUES (?, ?, ?, ?, ?, ?)";
        PreparedStatement stmt = sqlConnection.prepareStatement(insertSQL);
        Table hbaseTable = hbaseConnection.getTable(TableName.valueOf("raw_data"));

        for (int i = startId; i < endId; i++) {
            String rowKey = String.valueOf(i);
            Get get = new Get(Bytes.toBytes(rowKey));
            get.addFamily(Bytes.toBytes("details"));
            Result result = hbaseTable.get(get);

            if (!result.isEmpty()) {
                String ip = getValue(result, "details", "ip");
                String date = getValue(result, "details", "date");
                String day = getValue(result, "details", "day");
                String traffic = getValue(result, "details", "traffic");
                String type = getValue(result, "details", "type");

                stmt.setString(1, ip);
                stmt.setString(2, date);
                stmt.setString(3, day);
                stmt.setString(4, traffic);
                stmt.setString(5, type);
                stmt.setString(6, rowKey);
                stmt.addBatch();

                if ((i - startId) % 100 == 0) {
                    stmt.executeBatch();
                }
            }
        }
        stmt.executeBatch();
        System.out.println("Data migration completed.");
    }

    private static String getValue(Result result, String family, String qualifier) {
        byte[] value = result.getValue(Bytes.toBytes(family), Bytes.toBytes(qualifier));
        return value == null ? "" : Bytes.toString(value);
    }

    private static void closeConnections() {
        try {
            if (hbaseAdmin != null) hbaseAdmin.close();
            if (hbaseConnection != null) hbaseConnection.close();
            if (sqlConnection != null) sqlConnection.close();
        } catch (IOException | SQLException e) {
            e.printStackTrace();
        }
    }
}

HBase API Utility Class

A wraper class simplifies HBase operations such as checking table existence, creating namespaces, and performing CRUD operations.

package com.data.hbase;

import org.apache.hadoop.conf.Configuration;
import org.apache.hadoop.hbase.HBaseConfiguration;
import org.apache.hadoop.hbase.NamespaceDescriptor;
import org.apache.hadoop.hbase.TableName;
import org.apache.hadoop.hbase.client.*;
import org.apache.hadoop.hbase.util.Bytes;

import java.io.IOException;

public class HBaseClient {

    private static Connection connection;
    private static Admin admin;

    static {
        try {
            Configuration conf = HBaseConfiguration.create();
            conf.set("hbase.zookeeper.quorum", "hadoop102,hadoop103,hadoop104");
            connection = ConnectionFactory.createConnection(conf);
            admin = connection.getAdmin();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static boolean tableExists(String tableName) throws IOException {
        return admin.tableExists(TableName.valueOf(tableName));
    }

    public static void createTable(String tableName, String... columnFamilies) throws IOException {
        if (columnFamilies.length == 0) {
            System.out.println("Error: At least one column family is required.");
            return;
        }
        if (tableExists(tableName)) {
            System.out.println("Table " + tableName + " already exists.");
            return;
        }

        TableDescriptorBuilder tableDescriptor = TableDescriptorBuilder.newBuilder(TableName.valueOf(tableName));
        for (String cf : columnFamilies) {
            ColumnFamilyDescriptor family = ColumnFamilyDescriptorBuilder.newBuilder(Bytes.toBytes(cf)).build();
            tableDescriptor.setColumnFamily(family);
        }
        admin.createTable(tableDescriptor.build());
        System.out.println("Table " + tableName + " created successfully.");
    }

    public static void putData(String tableName, String rowKey, String cf, String cn, String value) throws IOException {
        Table table = connection.getTable(TableName.valueOf(tableName));
        Put put = new Put(Bytes.toBytes(rowKey));
        put.addColumn(Bytes.toBytes(cf), Bytes.toBytes(cn), Bytes.toBytes(value));
        table.put(put);
        table.close();
    }

    public static void scanTable(String tableName) throws IOException {
        Table table = connection.getTable(TableName.valueOf(tableName));
        Scan scan = new Scan();
        ResultScanner scanner = table.getScanner(scan);

        for (Result result : scanner) {
            for (var cell : result.rawCells()) {
                System.out.printf("Row: %s, CF: %s, Value: %s%n",
                        Bytes.toString(CellUtil.cloneRow(cell)),
                        Bytes.toString(CellUtil.cloneFamily(cell)),
                        Bytes.toString(CellUtil.cloneValue(cell)));
            }
        }
        table.close();
    }
    
    public static void close() {
        try {
            if (admin != null) admin.close();
            if (connection != null) connection.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

Backend Service and DAO Layer

The web layer retrieves aggregated data from MySQL based on user selection (e.g., by IP, traffic count, or type) and returns it as JSON.

Data Model Bean

package com.web.model;

public class ChartDataPoint {
    private String label;
    private String value;

    // Getters and Setters
    public String getLabel() { return label; }
    public void setLabel(String label) { this.label = label; }
    public String getValue() { return value; }
    public void setValue(String value) { this.value = value; }
}

Data Access Object

package com.web.dao;

import com.web.model.ChartDataPoint;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class AnalyticsDao {
    
    private static final String URL = "jdbc:mysql://localhost:3306/analytics_db";
    private static final String USER = "root";
    private static final String PASS = "securepass";

    public List<ChartDataPoint> getChartData(String dataType) {
        List<ChartDataPoint> dataList = new ArrayList<>();
        String query;

        switch (dataType) {
            case "addr":
                query = "SELECT ip_address as name, count as sum FROM ip_stats ORDER BY sum DESC LIMIT 5";
                break;
            case "traffic":
                query = "SELECT traffic_range as name, volume as sum FROM traffic_stats ORDER BY sum DESC LIMIT 5";
                break;
            default:
                query = "SELECT content_type as name, occurrences as sum FROM type_stats ORDER BY sum DESC LIMIT 5";
        }

        try (Connection conn = DriverManager.getConnection(URL, USER, PASS);
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(query)) {
            
            while (rs.next()) {
                ChartDataPoint point = new ChartDataPoint();
                point.setLabel(rs.getString("name"));
                point.setValue(rs.getString("sum"));
                dataList.add(point);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return dataList;
    }
}

Servlet Controller

package com.web.controller;

import com.google.gson.Gson;
import com.web.dao.AnalyticsDao;
import com.web.model.ChartDataPoint;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.*;
import java.io.IOException;
import java.util.List;

@WebServlet("/chartData")
public class ChartServlet extends HttpServlet {
    
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        String chartType = request.getParameter("chartType");
        String dataType = request.getParameter("dataType");

        AnalyticsDao dao = new AnalyticsDao();
        List<ChartDataPoint> results = dao.getChartData(dataType);

        String json = new Gson().toJson(results);
        
        request.setAttribute("jsonData", json);
        request.setAttribute("dataList", results);
        request.setAttribute("selectedChart", chartType);
        
        request.getRequestDispatcher("dashboard.jsp").forward(request, response);
    }
}

Frontend Visualization

The JSP page renders the chart using ECharts. It dynamically configures the chart type (Line, Bar, Pie) based on user input and implements "chart-table linkage," where clicking a chart element highlights the corresponding table row.

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>

<html>
<head>
    <meta charset="UTF-8">
    <title>Interactive Dashboard</title>
    <script src="js/jquery-1.8.3.min.js"></script>
    <script src="js/echarts.js"></script>
</head>
<body>
    <form action="chartData" method="post">
        <label>Chart Type:</label>
        <select name="chartType" id="chartType">
            <option value="line">Line Chart</option>
            <option value="bar">Bar Chart</option>
            <option value="pie">Pie Chart</option>
        </select>
        
        <label>Data Category:</label>
        <select name="dataType" id="dataType">
            <option value="addr">IP Address</option>
            <option value="traffic">Traffic</option>
            <option value="type">Content Type</option>
        </select>
        <button type="submit">Generate</button>
    </form>

    <div id="chartContainer" style="width: 1000px; height: 400px;"></div>

    | Category | Count |
|---|---|
<foreach items="${dataList}" var="item"> | ${item.label} | ${item.value} |
 </foreach>

    <script type="text/javascript">
        var chartType = '${selectedChart}';
        var jsonSource = '${jsonData}';
        var parsedData = JSON.parse(jsonSource);

        var xLabels = [];
        var yValues = [];
        var pieData = [];

        parsedData.forEach(function(item) {
            xLabels.push(item.label);
            yValues.push(item.value);
            pieData.push({name: item.label, value: item.value});
        });

        var myChart = echarts.init(document.getElementById('chartContainer'));
        var option = {};

        if (chartType === 'pie') {
            option = {
                title: { text: 'Distribution Analysis', left: 'center' },
                tooltip: { trigger: 'item', formatter: '{b}: {c} ({d}%)' },
                legend: { orient: 'vertical', left: 'left', data: xLabels },
                series: [{ name: 'Stats', type: 'pie', radius: '50%', data: pieData }]
            };
        } else if (chartType === 'line') {
            option = {
                title: { text: 'Trend Analysis' },
                xAxis: { type: 'category', data: xLabels },
                yAxis: { type: 'value' },
                series: [{ data: yValues, type: 'line', smooth: true }]
            };
        } else {
            option = {
                title: { text: 'Volume Statistics' },
                xAxis: { type: 'category', data: xLabels },
                yAxis: { type: 'value' },
                series: [{ data: yValues, type: 'bar' }]
            };
        }

        myChart.setOption(option);

        // Chart-Table Interaction Logic
        myChart.on('click', function (params) {
            var selectedName = params.name;
            alert("Selected: " + selectedName);

            $("#dataTable tr").each(function() {
                var rowText = $(this).find("td:first").text();
                if (rowText === selectedName) {
                    $(this).css("background-color", "#ffcccc");
                } else {
                    $(this).css("background-color", "");
                }
            });
        });
    </script>
</body>
</html>

This setup allows users to toggle between different visualization styles and data categories. The interactive link between the chart and the HTML table ensures that clicking a graphical element immediately highlights the corresponding raw data row, enhancing the user's ability to analyze the dataset.