TOPIC_ID: 61 TEAM_ID:1269929257 TEAM_NAME: 紫金山实验室-北京邮电大学小分队
模拟公司考勤打卡场景,利用华为云人脸识别服务(FRS)和openLooKeng用户自定义SQL函数(UDF)实现。
根据题目描述设计AddFaceSet, AddFace, DelFace, DelFaceSet等基础函数构建人脸库,据此设计SearchFace以及赛题要求FaceScan函数进行人脸识别,并以插件形式集成在openLooKeng服务器上,可以通过CLI进行查询,完成赛题要求。
基于VMware虚拟机的项目展示视频(MySQL用户表构造+UDF插件实现+UDF函数使用)
项目基于openEuler操作系统,以UDF插件形式集成于openLooKeng服务器,借此可连接MySQL数据库,统一处理和查询操作,为开发者用户提供简单但强大的SQL接口。
- 人脸录入流程:管理员上传人脸图片,录入MySQL人脸数据库,经过华为云AI处理得到华为云人脸库;
- 人脸检测流程:通过外部设备获取人脸图像,经华为云AI提取人脸特征,与华为云人脸库比对,若匹配则打卡成功,反之失败,流程结束。
访问openEuler社区网站下载对应镜像文件即可安装,详细步骤参考官方安装指南。
本项目首先在VMware虚拟机上进行安装,后又迁移到鹏城实验室(❤)的云主机(openEuler-20.03-LTS),用于最终demo展示。
openEuler提供了多种repo源供用户在线使用,各repo源含义可参考系统安装,详见开发环境准备
# pengchenglab's vm as an example
vi /etc/yum.repos.d/openEuler_<arch>.repo
# fill in below
[base]
name=base
baseurl=https://repo.openeuler.org/openEuler-20.03-LTS/everything/aarch64/
enabled=1
gpgcheck=0
# end
# query version and arch of <baseurl> above
cat /etc/os-release
uname -a
# update repo
yum update
原始的openEuler操作系统缺乏一些实用工具, 需要先行安装.
yum install -y wget # openLooKeng自动部署需要下载工具
yum install -y tar # openLooKeng自动部署需要解压工具
yum install -y python # openLooKeng的启动需要Python环境
访问openLooKeng社区安装文档,选择自动部署方式 bash <(wget -qO- https://download.openlookeng.io/install.sh)
或 bash <(wget -qO- https://download.openlookeng.io/install.sh)
即可快速部署。自动部署失败可以选择手动部署
安装过程略微复杂, 可参考社区MySQL安装指南.
yum install -y mysql
# config mysql
mkdir -p /data/mysql && cd /data/mysql && mkdir data tmp run log
chown -R mysql:mysql /data
vi /etc/my.cnf
# fill in below
[mysqld_safe]
log-error=/data/mysql/log/mysql.log
pid-file=/data/mysql/run/mysqld.pid
[mysqldump]
quick
[mysql]
no-auto-rehash
[client]
default-character-set=utf8
[mysqld]
basedir=/usr/local/mysql
socket=/data/mysql/run/mysql.sock
tmpdir=/data/mysql/tmp
datadir=/data/mysql/data
default_authentication_plugin=mysql_native_password
port=3306
user=mysql
# end
chown mysql:mysql /etc/my.cnf
echo 'alias mysql="mysql -S /data/mysql/run/mysql.sock"' >> /etc/profile
source /etc/profile
# initialize mysql
mysqld --defaults-file=/etc/my.cnf --initialize # attention to a temp passwd
# init with system
chmod 777 /usr/local/mysql/support-files/mysql.server
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql
su - mysql && service mysql start
# grant privilege
mysql -uroot -p <the temp passwd>
mysql> create user 'root'@'%' identified by 'root';
mysql> grant all privileges on *.* to 'root'@'%';flush privileges;
mysql> flush privileges;
配置Maven工具, 可以根据源码打包, 便于开发维护.
# install maven
wget https://mirrors.tuna.tsinghua.edu.cn/apache/maven/maven-3/3.6.3/binaries/apache-maven-3.6.3-bin.tar.gz -P /tmp
sudo tar xzf /tmp/*maven*.tar.gz -C /opt
sudo ln -s /opt/apache-maven-3.6.3 /opt/maven
# config maven
echo "export MAVEN_HOME=/opt/maven" >> /etc/profile
echo "export PATH=$MAVEN_HOME/bin:$PATH" >> /etc/profile
source /etc/profile
sudo chown -R openlkadmin:openlkadmin /opt/
mvn -version
本项目采用编译模块后拷贝jar包到hetu-server/plugin目录下的方式实现UDF.
su - openlkadmin
# config mysql connector
vim /opt/openlookeng/data/etc/catalog/mysql.properties
# fill in below
connector.name=mysql
connection-url=jdbc:mysql://localhost:3306
connection-user=root
connection-password=root
# end
# install local huaweicloud-sdk-java-frs
cd /home/openlkadmin && git clone https://github.com/huaweicloud/huaweicloud-sdk-java-frs.git
mvn install:install-file \
-Dfile=/home/openlkadmin/huaweicloud-sdk-java-frs/frs-sdk/target/frs-sdk-1.0.jar \
-DgroupId=com.huaweicloud.frs \
-DartifactId=frs-sdk \
-Dversion=1.0 \
-Dpackaging=jar \
-DgeneratePom=true
mvn install:install-file \
-Dfile=/home/openlkadmin/huaweicloud-sdk-java-frs/frs-sdk/lib/java-sdk-core-3.0.9.jar \
-DgroupId=com.huawei \
-DartifactId=java-sdk-core \
-Dversion=3.0.9 \
-Dpackaging=jar \
-DgeneratePom=true
# download sources
git clone https://gitee.com/openlookeng/hetu-core.git && git checkout 1.0.1
git clone https://gitee.com/openeuler2020/team-1269929257.git
cp -r /home/openlkadmin/team-1269929257/hetu-cloudface/ /home/openlkadmin/hetu-core
# package from sources
cd /home/openlkadmin/hetu-core/hetu-cloudface && mvn clean && mvn package -e
ln -s /home/openlkadmin/hetu-core/hetu-cloudface/target/hetu-cloudface-1.0.1 /opt/openlookeng/hetu-server-1.0.1/plugin/cloudface
### ----------- OR ---------- ###
# use targer jar of repo
ln -s /home/openlkadmin/team-1269929257/hetu-cloudface/target/hetu-cloudface-1.0.1 /opt/openlookeng/hetu-server-1.0.1/plugin/cloudface
# restart hetu-server and open cli
bash /opt/openlookeng/bin/auxiliary_tools/launcher.sh restart
bash /opt/openlookeng/bin/openlk-cli
为提供可运行的demo, 本项目迁移到鹏城实验室的云主机上用于展示(ssh [email protected] -p 3652 # usr:pwd = openlkadmin:olk || root:root
).
云主机上已完成前述所有安装步骤, 可以直接使用. 使用步骤同介绍章节的展示视频.
通过如下MySQL语句可以构建赛题要求的用户表, 代码仓库下的scripts/creat_user_table.sql
也可以直接运行进行构建.
DROP DATABASE IF EXISTS pml;
create database pml charset utf8;
use pml;
create table user(`Id` int not null AUTO_INCREMENT primary key, `Name` varchar(20) NOT NULL, `FacePath` varchar(4096) NOT NULL);
insert into user (Id,Name,FacePath) values (1001,'张三','/home/openlkadmin/team-1269929257/data/luoxiang.jpg');
insert into user (Id,Name,FacePath) values (1002,'李四','/home/openlkadmin/team-1269929257/data/luoyonghao.jpg');
select * from user;
云主机上已经完成用户表的构建.
UDF函数定义在hetu-cloudface/src/main/java/io/hetu/core/plugin/cloudface/
,分布在三个函数类(人脸库函数类、人脸函数类、人脸识别函数类)里面, 有如下函数签名.
// 测试函数, 返回字符串长度
@ScalarFunction("Test")
@Description("Return length of input string")
@SqlType(StandardTypes.INTEGER)
public static long Test(@SqlType(StandardTypes.VARCHAR) Slice slice)
// 新建人脸库, 输入人脸库名字, 成功返回0, 失败返回-1
@ScalarFunction("NewFaceSet")
@Description("New a <para1: face_set>")
@SqlType(StandardTypes.INTEGER)
public static long NewFaceSet(@SqlType(StandardTypes.VARCHAR) Slice faceset)
// 向人脸库添加人脸, 输入人脸库名字,人脸存储路径,人脸对应数据库id, 成功返回0, 失败返回-1
@ScalarFunction("AddFace")
@Description("Add face in <para1: face_set> by <para2: face_path> and <para3: who_id>")
@SqlType(StandardTypes.INTEGER)
public static long AddFace(@SqlType(StandardTypes.VARCHAR) Slice faceset,
@SqlType(StandardTypes.VARCHAR) Slice face, @SqlType(StandardTypes.INTEGER) long who)
// 在人脸库中搜索人脸, 输入人脸库名字,人脸存储路径, 找到返回人脸对应id, 未找到返回0, 失败返回-1
@ScalarFunction("SearchFace")
@Description("Search face in <para1: face_set> by <para2: face_url>, return face_external_id or 0 for none")
@SqlType(StandardTypes.INTEGER)
public static long SearchFace(@SqlType(StandardTypes.VARCHAR) Slice faceset, @SqlType(StandardTypes.VARCHAR) Slice face)
// 在人脸库删除人脸, 输入人脸库名字,人脸对应数据库id, 成功返回0, 失败返回-1
@ScalarFunction("DelFace")
@Description("Delete face in <para1: face_set> by <para2: who_id>")
@SqlType(StandardTypes.INTEGER)
public static long DelFace(@SqlType(StandardTypes.VARCHAR) Slice faceset, @SqlType(StandardTypes.INTEGER) long who)
// 删除人脸库, 输入人脸库名字, 成功返回0, 失败返回-1
@ScalarFunction("DelFaceSet")
@Description("Delete a <para1: face_set>")
@SqlType(StandardTypes.INTEGER)
public static long DelFaceSet(@SqlType(StandardTypes.VARCHAR) Slice faceset)
// 在账户所属第一个人脸库中搜索人脸, 输入人脸网络URL, 找到返回人脸对应id, 未找到返回0, 失败返回-1
@ScalarFunction("FaceScan")
@Description("Scan <para1: face_url> in <implicit_para2: first_face_set>")
@SqlType(StandardTypes.INTEGER)
public static long FaceScan(@SqlType(StandardTypes.VARCHAR) Slice face)
可以通过cloudface.<FunctionName>
在openLooKeng CLI中调用对应函数, 每个函数的使用测试见scripts/test_face_udf.sql
-- # test cloudface
select cloudface.Test('return length of this string');
-- # new face set
select cloudface.NewFaceSet('pml-test-faceset');
-- # add faces
-- ## add zhangsan's face
select id, facepath FROM mysql.pml.user where name = '张三';
-- filling MANUALLY
select cloudface.AddFace('pml-test-faceset', '/home/openlkadmin/team-1269929257/data/luoxiang.jpg', 1001);
-- ## add zhangsan's face
select id, facepath FROM mysql.pml.user where name = '李四';
-- filling MANUALLY
select cloudface.AddFace('pml-test-faceset', '/home/openlkadmin/team-1269929257/data/luoyonghao.jpg', 1002);
-- # search who
select name from mysql.pml.user where id = cloudface.SearchFace('pml-test-faceset', 'https://pic3.zhimg.com/v2-c3b1ad8057c14fc8cf6520702a307798_xl.jpg'); -- luoxiang -> zhangsan
select name from mysql.pml.user where id = cloudface.SearchFace('pml-test-faceset', 'https://pic4.zhimg.com/80/v2-6d6224abdb7134a78e40430bdc690c0e_400x224.jpg'); -- luoyonghao -> lisi
select name from mysql.pml.user where id = cloudface.SearchFace('pml-test-faceset', 'https://pic3.zhimg.com/80/v2-36c2f0f917e0a47440593a2c51dc5769_qhd.jpg'); -- Trump -> none
-- # scan who
select name from mysql.pml.user where id = cloudface.FaceScan('https://pic3.zhimg.com/v2-c3b1ad8057c14fc8cf6520702a307798_xl.jpg'); -- luoxiang -> zhangsan
-- # delete face
select cloudface.DelFace('pml-test-faceset', 1001);
--# delete face set
select cloudface.DelFaceSet('pml-test-faceset');
云主机上保留有人脸库, 可以直接进行人脸识别, 会有如下结果.
- 完整的人脸识别接口
- AI + SQL
- 云主机展示