使用clickhouse即席查询

controller：面向外部请求，提供数据查询，返回对应格式的数据

service：组合和数据层，提供数据服务（接口+实现类）

mapper：单表操作（接口 + xml操作(sql) ）

 1. com.atguigu.gmall0105.publisher.controller.PublisherController

package com.atguigu.gmall0105.publisher.controller;

import com.alibaba.fastjson.JSON;
import com.atguigu.gmall0105.publisher.service.ClickHouseService;
import com.atguigu.gmall0105.publisher.service.EsService;
import org.apache.commons.lang3.time.DateUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import java.math.BigDecimal;
import java.math.RoundingMode;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

@RestController
public class PublisherController {

    @Autowired
    EsService esService;

    @Autowired
    ClickHouseService clickHouseService;

    @RequestMapping(value = "realtime-total", method = RequestMethod.GET)
    public String realtimeTotal(@RequestParam("date") String dt) {
        List<Map<String, Object>> rsList = new ArrayList<>();

//        Map<String, Object> dauMap = new HashMap();
//        dauMap.put("id", "dau");
//        dauMap.put("name", "新增日活");
//        Long dauTotal = 0L;
//        try {
//            dauTotal = esService.getDauTotal(dt);
//        } catch (Exception e) {
//            e.printStackTrace();
//        }
//        if (dauTotal != null) {
//            dauMap.put("value", dauTotal);
//        } else {
//            dauMap.put("value", 0L);
//        }
//        rsList.add(dauMap);

//        Map<String, Object> newMidMap = new HashMap();
//        newMidMap.put("id", "new_mid");
//        newMidMap.put("name", "新增设备");
//        newMidMap.put("value", 233);
//        rsList.add(newMidMap);

        Map<String, Object> orderAmountMap = new HashMap();
        orderAmountMap.put("id","order_amount");
        orderAmountMap.put("name","新增交易额");
        BigDecimal orderAmount = clickHouseService.getOrderAmount(dt);
        orderAmountMap.put("value",orderAmount);
        rsList.add(orderAmountMap);

        return JSON.toJSONString(rsList);
    }

    @GetMapping("realtime-hour")
    public String realtimeHour(@RequestParam("id") String id, @RequestParam("date") String dt) {
        if (id.equals("dau")) {
            Map dauHourMapTD = esService.getDauHour(dt);
            String yd = getYd(dt);
            Map dauHourMapYD = esService.getDauHour(yd);

            Map<String, Map<String, Long>> rsMap = new HashMap<>();
            rsMap.put("yesterday", dauHourMapYD);
            rsMap.put("today", dauHourMapTD);
            return JSON.toJSONString(rsMap);
        } else if (id.equals("order_amount")) {
            Map orderAmountHourMapTD = clickHouseService.getOrderAmountHour(dt);
            String yd = getYd(dt);
            Map orderAmountHourMapYD = clickHouseService.getOrderAmountHour(yd);

            Map<String, Map<String, BigDecimal>> rsMap = new HashMap<>();
            rsMap.put("yesterday", orderAmountHourMapYD);
            rsMap.put("today", orderAmountHourMapTD);
            return JSON.toJSONString(rsMap);
        } else {
            return null;
        }

    }

    private String getYd(String today) {
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
        try {
            Date todayDate = dateFormat.parse(today);
            Date ydDate = DateUtils.addDays(todayDate, -1);
            return dateFormat.format(ydDate);

        } catch (ParseException e) {
            e.printStackTrace();
            throw new RuntimeException("日期格式不正确");
        }

    }

}

2. com.atguigu.gmall0105.publisher.service.ClickHouseService

package com.atguigu.gmall0105.publisher.service;

import java.math.BigDecimal;
import java.util.List;
import java.util.Map;

public interface ClickHouseService {

    public BigDecimal getOrderAmount(String date);

    public Map getOrderAmountHour(String date);

}

3. com.atguigu.gmall0105.publisher.service.impl.ClickHouseServiceImpl

package com.atguigu.gmall0105.publisher.service.impl;

import com.atguigu.gmall0105.publisher.mapper.OrderWideMapper;
import com.atguigu.gmall0105.publisher.service.ClickHouseService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.math.BigDecimal;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Service
public class ClickHouseServiceImpl implements ClickHouseService {

    @Autowired
    OrderWideMapper orderWideMapper;

    @Override
    public BigDecimal getOrderAmount(String date) {
        return orderWideMapper.selectOrderAmount(date);
    }

    @Override
    public Map getOrderAmountHour(String date) {
        //进行转换
//        List<Map>     [{"hr":10,"order_amount":1000.00} ,{"hr":11,"order_amount":2000.00}...... ]
//        Map    {"10":1000.00,"11":2000.00,...... }
        List<Map> mapList = orderWideMapper.selectOrderAmountHour(date);
        Map<String, BigDecimal> hourMap = new HashMap<>();
        for (Map map : mapList) {
            hourMap.put(String.valueOf(map.get("hr")), (BigDecimal) map.get("order_amount"));
        }
        return hourMap;
    }
}

 4. com.atguigu.gmall0105.publisher.mapper.OrderWideMapper

package com.atguigu.gmall0105.publisher.mapper;

import java.math.BigDecimal;
import java.util.List;
import java.util.Map;

public interface OrderWideMapper {
    //查询当日总额
    public BigDecimal selectOrderAmount(String date);

    //查询当日交易额
    public List<Map> selectOrderAmountHour(String date);

}

5. mapper/OrderWideMapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper SYSTEM "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.atguigu.gmall0105.publisher.mapper.OrderWideMapper">
    <select id="selectOrderAmount" resultType="java.math.BigDecimal">
            select  sum(final_detail_amount) order_amount
            from order_wide_0105
            where dt=#{date}
    </select>
    <select id="selectOrderAmountHour" resultMap="orderAmountHrMap">
            select toHour(create_time) hr, sum(final_detail_amount) order_amount
            from order_wide_0105
            where dt='2021-05-19'
            group by hr
    </select>

    <resultMap
            id="orderAmountHrMap" type="java.util.Map" autoMapping="true">
    </resultMap>
</mapper>

5. application.properties

server.port=8070
#Es
spring.elasticsearch.jest.uris=http://hadoop102:9200,http://hadoop103:9200

#ClickHouse
spring.datasource.driver-class-name=ru.yandex.clickhouse.ClickHouseDriver
spring.datasource.url=jdbc:clickhouse://hadoop102:8123/test0105

mybatis.mapperLocations=classpath:mapper/*.xml
mybatis.configuration.map-underscore-to-camel-case=true

#Mysql
#spring.datasource.driver-class-name=com.mysql.jdbc.Driver
#spring.datasource.url=jdbc:mysql://hadoop102/gmall0105_rs?characterEncoding=utf-#8&useSSL=false
#spring.datasource.username=root
#spring.datasource.password=000000