bigdata2024: 河南师范大学软件学院2024年大数据实训

开启hbase二级索引

<property> 
  <name>hbase.regionserver.wal.codec</name> 
  <value>org.apache.hadoop.hbase.regionserver.wal.IndexedWALEditCodec</value> 
</property>

表设计

表1. 员工表emp的数据表格示例

其中 role_id为1是管理员角色 role_id为2是售货员角色

deleted 为逻辑删除字段 0代表未删除 1代表已删除

rowkey
	auth	info
	password	role_id	role_name	name	id_card	home_address	deleted
1531148....	lihaozhe	1	管理员	李昊哲	220422...	吉林省...	0
1851569....	lishenglong	2	售货员	李胜龙	220422...	吉林省...	0

表2. 角色表role的数据表格示例

rowkey
	resource
resource_id

表3. 资源表resource的数据表格示例

rowkey

info

manage

parent_id

title

icon

href

target

sort

status

remark

create_time

modified_time

create_account_id

modified_account_id

deleted

表4. 角色资源关系表角色资源关系表role_resource的数据表格示例

rowkey
	resource
	role_id	resource_id

表5. 商品表goods的数据表格示例

rowkey
	info						stock
	goods_name	category_id	price	listing_time	delisting_time	change_time	balance

表6. 商品类别表category的数据表格示例

rowkey
	info
	category_name

表7. 顾客表consumer的数据表格示例

rowkey
	info
	mobile	consumer_name	id_card	home_address

表8. 订单order_info的数据表格示例

rowkey
	info
	consumer_id	total_amount	create_time

表9. 订单详情order_detail的数据表格示例

rowkey
	info
	order_id	consumer_id	goods_id	goods_name	price	goods_num	sub_amount	create_time

初始化数据

员工表

create table emp(
 mobile varchar primary key,
 auth.password varchar,
 info.role_id unsigned_int,   
 info.role_name varchar, 
 info.name varchar,
 info.id_card varchar, 
 info.home_address varchar,
 info.deleted unsigned_int default 0
) COLUMN_ENCODED_BYTES = 0;

create index emp_index on emp(role_id) include(id_card);

upsert into emp values ('15311484568','lihaozhe',1,'管理员','李昊哲','220422198310182012','吉林省东辽县',0);

角色表

create table role (
  role_id unsigned_int primary key, 
  info.role_name varchar   
) COLUMN_ENCODED_BYTES = 0;

upsert into role values (1,'管理员');
upsert into role values (2,'售货员');

资源表

create table resource (
  resource_id unsigned_int primary key,
  info.parent_id unsigned_int,
  info.title varchar,
  info.icon varchar,
  info.href varchar,
  info.target varchar,
  info.sort unsigned_int,
  info.status unsigned_int,
  info.remark varchar,
  manage.create_time date,
  manage.modified_time date,
  manage.create_account_id unsigned_int,
  manage.modified_account_id unsigned_int,
  manage.deleted unsigned_int default 0
) COLUMN_ENCODED_BYTES = 0;

create index resource_index on resource(parent_id);

upsert into resource values (200,0,'系统管理','layui-icon layui-icon-set',NULL,'_self',1,1,NULL,'2021-09-08 18:58:25','2021-09-08 18:58:25','15311484568','15311484568',0);

create table resource (
  resource_id unsigned_int primary key,
  info.parent_id unsigned_int,
  info.title varchar,
  info.icon varchar,
  info.href varchar,
  info.target varchar,
  info.sort unsigned_int,
  info.status unsigned_int,
  info.remark varchar,
  manage.create_time date,
  manage.modified_time date,
  manage.create_account_id unsigned_int,
  manage.modified_account_id unsigned_int,
  manage.deleted unsigned_int default 0
) COLUMN_ENCODED_BYTES = 0;

upsert into resource values (1,0,'系统管理','layui-icon layui-icon-set',NULL,'_self',1,1,NULL,'2021-09-08 18:58:25','2021-09-08 18:58:25','15311484568','15311484568',0);
upsert into resource values (2,0,'商品管理','layui-icon layui-icon-chart',NULL,'_self',2,1,NULL,'2021-09-08 18:59:54','2021-09-08 18:59:54','15311484568','15311484568',0);
upsert into resource values (3,0,'订单管理','layui-icon layui-icon-rmb',NULL,'_self',3,1,NULL,'2021-09-08 19:00:38','2021-09-08 19:00:38','15311484568','15311484568',0);
upsert into resource values (4,0,'报表管理','layui-icon layui-icon-cart',NULL,'_self',4,1,NULL,'2021-09-08 19:01:46','2021-09-08 19:01:46','15311484568','15311484568',0);
upsert into resource values (5,1,'角色管理','layui-icon layui-icon-user',NULL,'_self',1,1,NULL,'2021-09-08 19:04:00','2021-09-08 19:04:00','15311484568','15311484568',0);
upsert into resource values (6,1,'用户管理','layui-icon layui-icon-user','','_self',2,1,NULL,'2021-09-08 19:04:47','2021-09-08 19:04:47','15311484568','15311484568',0);
upsert into resource values (7,5,'添加角色','layui-icon layui-icon-user','addRolePage.html','_self',1,1,NULL,'2021-09-08 19:11:10','2021-09-08 19:11:10','15311484568','15311484568',0);
upsert into resource values (8,5,'角色列表','layui-icon layui-icon-user','roleListPage.html','_self',2,1,NULL,'2021-09-08 19:11:20','2021-09-08 19:11:20','15311484568','15311484568',0);
upsert into resource values (9,6,'员工列表','layui-icon layui-icon-user','empListPage.html','_self',2,1,NULL,'2021-09-08 19:11:20','2021-09-08 19:11:20','15311484568','15311484568',0);
upsert into resource values (10,6,'顾客列表','layui-icon layui-icon-user','consumerListPage.html','_self',4,1,NULL,'2021-09-08 19:11:20','2021-09-08 19:11:20','15311484568','15311484568',0);
upsert into resource values (11,2,'商品列表','layui-icon layui-icon-table','commodityListPage.html','_self',1,1,NULL,'2021-09-08 19:11:56','2021-09-08 19:11:56','15311484568','15311484568',0);
upsert into resource values (12,3,'添加购物车','layui-icon layui-icon-table','shoppingCarPage.html','_self',1,1,NULL,'2021-09-08 19:17:56','2021-09-08 19:17:56','15311484568','15311484568',0);
upsert into resource values (13,3,'订单列表','layui-icon layui-icon-table','ordersListPage.html','_self',2,1,NULL,'2021-09-08 19:17:56','2021-09-08 19:17:56','15311484568','15311484568',0);
upsert into resource values (14,4,'报表1','layui-icon layui-icon-table','reportPage01.html','_self',1,1,NULL,'2021-09-08 19:18:14','2021-09-08 19:18:14','15311484568','15311484568',0);
upsert into resource values (15,4,'报表2','layui-icon layui-icon-table','reportPage02.html','_self',2,1,NULL,'2021-09-08 19:19:14','2021-09-08 19:19:14','15311484568','15311484568',0);

角色资源关系表

create table role_resource (
  create_time date primary key,  
  resource.role_id unsigned_int,
  resource.resource_id unsigned_int
) COLUMN_ENCODED_BYTES = 0;

create index role_resource_index on role_resource(role_id,resource_id);

upsert into role_resource values ('2021-09-08 18:58:01',1,1);
upsert into role_resource values ('2021-09-08 18:58:02',1,2);
upsert into role_resource values ('2021-09-08 18:58:03',1,3);
upsert into role_resource values ('2021-09-08 18:58:04',1,4);
upsert into role_resource values ('2021-09-08 18:58:05',1,5);
upsert into role_resource values ('2021-09-08 18:58:06',1,6);
upsert into role_resource values ('2021-09-08 18:58:07',1,7);
upsert into role_resource values ('2021-09-08 18:58:08',1,8);
upsert into role_resource values ('2021-09-08 18:58:09',1,9);
upsert into role_resource values ('2021-09-08 18:58:10',1,10);
upsert into role_resource values ('2021-09-08 18:58:11',1,11);
upsert into role_resource values ('2021-09-08 18:58:13',1,13);
upsert into role_resource values ('2021-09-08 18:58:14',1,14);
upsert into role_resource values ('2021-09-08 18:58:15',1,15);
upsert into role_resource values ('2021-09-08 18:58:17',2,2);
upsert into role_resource values ('2021-09-08 18:58:18',2,3);
upsert into role_resource values ('2021-09-08 18:58:19',2,6);
upsert into role_resource values ('2021-09-08 18:58:20',2,11);
upsert into role_resource values ('2021-09-08 18:58:21',2,12);
upsert into role_resource values ('2021-09-08 18:58:22',2,13);

商品表

create table goods (
  goods_id    unsigned_int primary key,
  info.goods_name  varchar,
  info.category_id unsigned_int,
  info.price       decimal(16, 2),
  info.listing_time   date,
  info.delisting_time   date default '2099-12-31',
  info.change_time   date,
  stock.balance unsigned_int
) COLUMN_ENCODED_BYTES = 0;

create index goods_index on goods(category_id);

upsert into goods values (1,'xiaomi10',1,2000.00,'2020-01-01','2099-12-31','2020-01-01',1000);
upsert into goods values (2,'手机壳',1,20.00,'2020-02-01','2099-12-31','2020-02-01',1000);
upsert into goods values (3,'apple12',1,5000.00,'2020-03-01','2099-12-31','2020-03-01',1000);
upsert into goods values (4,'xiaomi13',1,6000.00,'2020-04-01','2099-12-31','2020-04-01',1000);
upsert into goods values (5,'破壁机',2,500.00,'2020-01-01','2099-12-31','2020-01-01',1000);
upsert into goods values (6,'洗碗机',2,1500.00,'2020-02-01','2099-12-31','2020-02-01',1000);
upsert into goods values (7,'热水壶',2,100.00,'2020-03-01','2099-12-31','2020-03-01',1000);
upsert into goods values (8,'微波炉',2,600.00,'2020-04-01','2099-12-31','2020-04-01',1000);
upsert into goods values (9,'自行车',3,1000.00,'2020-01-01','2099-12-31','2020-01-01',1000);
upsert into goods values (10,'帐篷',3,300.00,'2020-02-01','2099-12-31','2020-02-01',1000);
upsert into goods values (11,'烧烤架',3,150.00,'2020-03-01','2099-12-31','2020-03-01',1000);
upsert into goods values (12,'遮阳伞',3,30.00,'2020-04-01','2099-12-31','2020-04-01',1000);

商品变更详情

create table goods_modify_detail (
  change_time date primary key,
  goods_id     unsigned_int,
  new_price   decimal(16, 2)
) COLUMN_ENCODED_BYTES = 0;

create index goods_modify_detail_index on goods_modify_detail(goods_id);

分类表

create table category (
  category_id   unsigned_int primary key,
  info.category_name varchar
) COLUMN_ENCODED_BYTES = 0;

create index category_index on category(category_name);

upsert into category values (1,'数码');
upsert into category values (2,'厨卫');
upsert into category values (3,'户外');

顾客表

create table consumer (
  consumer_id  unsigned_int primary key,
  info.mobile  varchar, 
  info.consumer_name  varchar,
  info.id_card  varchar,
  info.home_address  varchar
) COLUMN_ENCODED_BYTES = 0;

create index consumer_index on consumer(mobile,id_card);

upsert into consumer values (100,'15223958643','廖欣','230155197009261756','内蒙古自治区兴安盟泥辫靛路3617号谅纺傣小区9单元1595室');
upsert into consumer values (101,'17028262891','轩辕联','513757199409063673','湖北省荆州市能钨耿路1660号刑局入小区3单元1291室');
upsert into consumer values (102,'13919640645','轩辕友','232207197804066438','辽宁省大连市秦祟路3651号瑚溯讶小区2单元2274室');
upsert into consumer values (103,'15084061963','赵幻','511570197802267778','辽宁省鞍山市凰里禄路7438号悲嫡小区17单元521室');
upsert into consumer values (104,'15368800063','皇甫园半','235483198206044719','甘肃省定西市冈扎路3478号迄帐匀小区16单元2070室');
upsert into consumer values (105,'13435248661','郑梆','82603819920721198X','山东省青岛市题渴路7138号黍楔冰小区12单元275室');
upsert into consumer values (106,'13397080151','曹至','325709198203270969','浙江省宁波市憨傍拒路82号税然嚎小区10单元125室');
upsert into consumer values (107,'15337740208','韩史','225388198611219505','海南省海口市嘎鲤亲路7291号停者小区2单元1181室');
upsert into consumer values (108,'13061674567','蒋放','137720198607100485','四川省甘孜藏族自治州告严路5166号冗蹦小区10单元1093室');
upsert into consumer values (109,'13574105921','史伍','541848198503050375','湖南省常德市项竖阵路5761号哪彬另小区7单元1499室');

订单表

create table order_info (
  order_id  unsigned_int primary key,
  info.consumer_id  unsigned_int,
  info.total_amount decimal(16, 2),
  info.create_time  date
) COLUMN_ENCODED_BYTES = 0;

create index order_info_index on order_info(consumer_id);

订单详情表

create table order_detail (
  detail_id  varchar primary key,
  info.order_id  unsigned_int,
  info.consumer_id  unsigned_int,
  info.goods_id     unsigned_int,
  info.goods_name 	varchar,
  info.price        decimal(16, 2),
  info.goods_num    unsigned_int,
  info.sub_amount decimal(16, 2),
  info.create_time  date
) COLUMN_ENCODED_BYTES = 0;

create index order_detail_index on order_detail(order_id,consumer_id,goods_id);

执行以下代码生成订单数据

package cn.lhz.phoenix.mock;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import cn.lhz.phoenix.mapper.ConsumerMapper;
import cn.lhz.phoenix.mapper.GoodsMapper;
import cn.lhz.phoenix.mapper.OrderDetailMapper;
import cn.lhz.phoenix.mapper.OrderInfoMapper;
import cn.lhz.phoenix.dto.Consumer;
import cn.lhz.phoenix.dto.Goods;
import cn.lhz.phoenix.dto.OrderDetail;
import cn.lhz.phoenix.dto.OrderInfo;
import cn.lhz.util.date.DateUtil;
import jakarta.annotation.Resource;
import lombok.RequiredArgsConstructor;
import org.junit.jupiter.api.Test;
import org.springframework.boot.test.context.SpringBootTest;

import java.math.BigDecimal;
import java.text.ParseException;
import java.util.*;
import java.util.concurrent.ThreadLocalRandom;
/**
 * @author 李昊哲
 * @version 1.0.0
 */
@SpringBootTest
@RequiredArgsConstructor
public class MockTest {
  @Resource
  private ConsumerMapper consumerMapper;
  @Resource
  private GoodsMapper goodsMapper;
  @Resource
  private OrderInfoMapper orderInfoMapper;
  @Resource
  private OrderDetailMapper orderDetailMapper;

  @Test
  public void mockOrder() throws ParseException {
    QueryWrapper<Consumer> consumerQueryWrapper = new QueryWrapper<>();
    List<Consumer> consumerList = consumerMapper.selectList(consumerQueryWrapper);
    QueryWrapper<Goods> goodsQueryWrapper = new QueryWrapper<>();
    List<Goods> goodsList = goodsMapper.selectList(goodsQueryWrapper);
    int count = 10000;
    List<Date> dateList = DateUtil.random("2024-01-01 00:00:00", "2024-06-30 23:59:59", count);
    long orderId = 1;
    for (int i = 0; i < count; i++) {
      int orderIdRef = 1;
      int goodsIndex = ThreadLocalRandom.current().nextInt(goodsList.size());
      int goodsNum = goodsIndex == 0 ? goodsIndex + 1 : goodsIndex;
      List<Goods> goodsListCopy = new ArrayList<>(goodsList);
      Date createTime = dateList.getFirst();
      dateList.removeFirst();
      List<OrderDetail> orderDetails = new ArrayList<>();
      for (int j = 0; j < goodsNum; j++) {
        long consumer_id = consumerList.get(ThreadLocalRandom.current().nextInt(consumerList.size())).getConsumerId();
        int goodsIdx = ThreadLocalRandom.current().nextInt(goodsListCopy.size());
        Goods goods = goodsListCopy.get(goodsIdx);
        goodsListCopy.remove(goodsIdx);
        OrderDetail orderDetail = new OrderDetail();
        orderDetail.setDetailId(String.valueOf(orderId) + "-" +  orderIdRef++);
        orderDetail.setOrderId(orderId);
        orderDetail.setConsumerId(consumer_id);
        orderDetail.setGoodsId(goods.getGoodsId());
        orderDetail.setGoodsName(goods.getGoodsName());
        orderDetail.setPrice(goods.getPrice());
        orderDetail.setGoodsNum(1L);
        orderDetail.setSub_amount(goods.getPrice());
        orderDetail.setCreateTime(createTime);
        orderDetails.add(orderDetail);
        orderDetailMapper.insert(orderDetail);
      }
      OrderInfo orderInfo = new OrderInfo();
      orderInfo.setOrderId(orderId++);
      orderInfo.setConsumerId(orderDetails.getFirst().getConsumerId());
      orderInfo.setCreateTime(createTime);
      BigDecimal amount = orderDetails.stream().reduce((a, b) -> {
        BigDecimal total_amount = a.getSub_amount().add(b.getSub_amount());
        OrderDetail orderDetail = new OrderDetail();
        orderDetail.setSub_amount(total_amount);
        return orderDetail;
      }).get().getSub_amount();
      orderInfo.setTotalAmount(amount);
      orderInfoMapper.insert(orderInfo);
    }
  }
}

或者使用脚本

psql.py supermarket.sql

报错

jdk版本高于1.8会遇到如下报错：

Unable to make static boolean java.nio.Bits.unaligned() accessible: module java.base does not "opens java.nio" to unnamed module @247bddad

QQ_1720583736260

解决方法：

方法一、配置系统缓解变量

方法二、开发工具中增加jvm参数 --add-opens java.base/java.nio=ALL-UNNAMED

本案例才有方法二，开发工具中增加jvm参数

QQ_1720583971320

QQ_1720584011230

QQ_1720584082894

QQ_1720584205931

htu/bigdata2024

开启hbase二级索引

表设计

初始化数据

报错

About

Releases

Contributors (1)

Language(Optional)

Activities

htu/bigdata2024 .gitee-modal { width: 500px !important; }

开启hbase二级索引

表设计

初始化数据

报错

About

Releases

Contributors (1)

Language(Optional)

Activities

Search

htu/bigdata2024