SecretFlow之SCQL部署（P2P方案）避雷纯享版

前言
一、搭建alice节点
- 1.1、创建工作区
- 1.2、准备状态数据、源数据
- 1.3、配置 SCQLBroker
- 1.4、配置 SCQLEngine
- 1.5、创建 docker-compose 文件
- 1.6、准备身份验证文件
- 1.7、启动服务
二、搭建bob节点
- 2.1、创建工作区
- 2.2、准备状态数据、源数据
- 2.3、配置 SCQLBroker
- 2.4、配置 SCQLEngine
- 2.5、创建 docker-compose 文件
- 2.6、准备身份验证文件
- 2.7、启动服务
三、测试SCQL
- 3.1、构建 brokerctl
- 3.2、创建项目并邀请参与方加入
- 3.3、创建数据表
- 3.4、授权CCL
- 3.5、执行查询
四、Q&A
- 4.1、docker-compose版本
- 4.2、SCQLBroker连不上数据库
- 4.3、找不到私钥
- 4.4、执行查询建立会话失败

前言

本文将针对蚂蚁的开源隐私计算平台隐语secretflow，实战部署SCQL系统（基于0.6.0b1版本的P2P非中心化方案），达到两个参与方联合数据分析的目标。

参考文档：

https://www.secretflow.org.cn/zh-CN/docs/scql/0.6.0b1/topics/deployment/how-to-deploy-p2p-cluster
https://www.secretflow.org.cn/zh-CN/docs/scql/0.6.0b1/intro/p2p-tutorial

名词解释：

SCQL ：安全协作查询语言（Secure Collaborative Query Language）是一个允许多个互不信任参与方在不泄露各自隐私数据的条件下进行联合数据分析的系统。

部署环境：

参与方alice：192.168.3.20
参与方bob：192.168.3.58

一、搭建alice节点

1.1、创建工作区

mkdir scql-p2p
cd scql-p2p

1.2、准备状态数据、源数据

# For Bob, please use command: wget raw.githubusercontent.com/secretflow/scql/main/examples/p2p-tutorial/mysql/initdb/bob_init.sql
wget raw.githubusercontent.com/secretflow/scql/main/examples/p2p-tutorial/mysql/initdb/alice_init.sql
# For Bob, please use command: wget raw.githubusercontent.com/secretflow/scql/main/examples/p2p-tutorial/mysql/initdb/broker_init_bob.sql
wget raw.githubusercontent.com/secretflow/scql/main/examples/p2p-tutorial/mysql/initdb/broker_init_alice.sql

1.3、配置 SCQLBroker

在工作区中创建一个名为 config.yml 的文件，并粘贴如下代码：

intra_server:
  host: 0.0.0.0
  port: 8080
inter_server:
  port: 8081
log_level: debug
party_code: alice
session_expire_time: 24h
session_expire_check_time: 1m
party_info_file: "/home/admin/configs/party_info.json"
private_key_path: "/home/admin/configs/ed25519key.pem"
intra_host: broker:8080
engine:
  timeout: 120s
  protocol: http
  content_type: application/json
  uris:
    - for_peer: http://192.168.3.20:8003
      for_self: 192.168.3.20:8003
storage:
  type: mysql
  conn_str: "root:password123@tcp(mysql:3306)/brokeralice?charset=utf8mb4&parseTime=True&loc=Local&interpolateParams=true"
  max_idle_conns: 10
  max_open_conns: 100
  conn_max_idle_time: 2m
  conn_max_lifetime: 5m

mysql数据库连接为broker元数据的db配置（这里和业务源数据库alice连接同一个mysql，只是库有所区分）。

1.4、配置 SCQLEngine

在工作区中创建一个名为 gflags.conf 的文件，并粘贴如下代码：

--listen_port=8003
--datasource_router=embed
--enable_driver_authorization=false
--server_enable_ssl=false
--driver_enable_ssl_as_client=false
--peer_engine_enable_ssl_as_client=false
--embed_router_conf={"datasources":[{"id":"ds001","name":"mysql db","kind":"MYSQL","connection_str":"db=alice;user=root;password=password123;host=mysql;auto-reconnect=true"}],"rules":[{"db":"*","table":"*","datasource_id":"ds001"}]}
# party authentication flags
--enable_self_auth=false
--enable_peer_auth=false

mysql数据库连接为业务源数据库alice的db配置（这里和broker连接同一个mysql，只是库有所区分）。

1.5、创建 docker-compose 文件

在您的工作区中创建一个名为 docker-compose.yaml 的文件，并粘贴以下代码：

version: '3.8'
services:
  broker:
    image: secretflow/scql:latest
    command:
      - /home/admin/bin/broker
      - -config=/home/admin/configs/config.yml
    restart: always
    ports:
      - 8080:8080
      - 8081:8081
    volumes:
      - ./config.yml:/home/admin/configs/config.yml
      - ./party_info.json:/home/admin/configs/party_info.json
      - ./ed25519key.pem:/home/admin/configs/ed25519key.pem
  engine:
    cap_add:
      - NET_ADMIN
    command:
      - /home/admin/bin/scqlengine
      - --flagfile=/home/admin/engine/conf/gflags.conf
    image: secretflow/scql:latest
    ports:
      - 8003:8003
    volumes:
      - ./gflags.conf:/home/admin/engine/conf/gflags.conf
  mysql:
    image: mysql:latest
    environment:
      - MYSQL_ROOT_PASSWORD=password123
      - TZ=Asia/Shanghai
    healthcheck:
      retries: 10
      test:
        - CMD
        - mysqladmin
        - ping
        - -h
        - mysql
      timeout: 20s
    expose:
      - "3306"
    restart: always
    volumes:
      - ./alice_init.sql:/docker-entrypoint-initdb.d/alice_init.sql
      - ./broker_init_alice.sql:/docker-entrypoint-initdb.d/broker_init_alice.sql

1.6、准备身份验证文件

参与方身份通过私钥-公钥对进行识别，因此我们需要生成这些文件。

创建192.168.3.20的参与方alice的密钥：

# generate private key
openssl genpkey -algorithm ed25519 -out ed25519key.pem

并获取自己的公钥，以便给party_info.json中使用：

# get public key corresponding to the private key, the output can be used to replace the __ALICE_PUBLIC_KEY__ in party_info.json
# for engine Bob,  the output can be used to replace the __BOB_PUBLIC_KEY__ in party_info.json
openssl pkey -in ed25519key.pem  -pubout -outform DER | base64

在您的工作区中创建一个名为 party_info.json 的文件，并粘贴以下代码：

{
  "participants": [
    {
      "party_code": "alice",
      "endpoint": "http://192.168.3.20:8081",
      "pubkey": "MCowBQYDK2VwAyEA3Ijkj1iaGBtpTukw78vBr8j+IuuW3dohTMm9rO3wLOg="
    },
    {
      "party_code": "bob",
      "endpoint": "http://192.168.3.58:8081",
      "pubkey": "MCowBQYDK2VwAyEAnRDPlvULRuuC9oIcQjBs6uHuonkdp1e+kP29cElocdo="
    }
  ]
}

1.7、启动服务

您的工作区文件应如下所示：

└── scql-p2p
  ├── alice_init.sql
  ├── broker_init_alice.sql
  ├── config.yml
  ├── docker-compose.yaml
  ├── ed25519key.pem
  ├── gflags.conf
  └── party_info.json

然后您可以运行docker-compose up来启动引擎服务：

# If you install docker with Compose V1, please use `docker-compose` instead of `docker compose`
docker-compose -f docker-compose.yaml up -d

Creating network "scql-p2p_default" with the default driver
Creating scql-p2p_broker_1 ... done
Creating scql-p2p_engine_1 ... done
Creating scql-p2p_mysql_1  ... done

查看进程：

docker-compose ps               
      Name                     Command                  State                                             Ports                                       
------------------------------------------------------------------------------------------------------------------------------------------------------
scql-p2p_broker_1   /home/admin/bin/broker -co ...   Up             0.0.0.0:8080->8080/tcp,:::8080->8080/tcp, 0.0.0.0:8081->8081/tcp,:::8081->8081/tcp
scql-p2p_engine_1   /home/admin/bin/scqlengine ...   Up             0.0.0.0:8003->8003/tcp,:::8003->8003/tcp                                          
scql-p2p_mysql_1    docker-entrypoint.sh mysqld      Up (healthy)   3306/tcp, 33060/tcp

您可以使用docker logs检查engine和broker是否正常工作：

docker logs -f scql-p2p_engine_1

[info] [main.cc:main:453] [sciengine] Started engine rpc server success, listen on: 0.0.0.0:8003

docker logs -f scql-p2p_broker_1

INFO main.go:190 Starting to serve request on :8081 with http...
INFO main.go:190 Starting to serve request on 0.0.0.0:8080 with http...

二、搭建bob节点

2.1、创建工作区

mkdir scql-p2p
cd scql-p2p

2.2、准备状态数据、源数据

# For Bob, please use command: wget raw.githubusercontent.com/secretflow/scql/main/examples/p2p-tutorial/mysql/initdb/bob_init.sql
wget raw.githubusercontent.com/secretflow/scql/main/examples/p2p-tutorial/mysql/initdb/bob_init.sql
# For Bob, please use command: wget raw.githubusercontent.com/secretflow/scql/main/examples/p2p-tutorial/mysql/initdb/broker_init_bob.sql
wget raw.githubusercontent.com/secretflow/scql/main/examples/p2p-tutorial/mysql/initdb/broker_init_bob.sql

2.3、配置 SCQLBroker

在工作区中创建一个名为 config.yml 的文件，并粘贴如下代码：

intra_server:
  host: 0.0.0.0
  port: 8080
inter_server:
  port: 8081
log_level: debug
party_code: bob
session_expire_time: 24h
session_expire_check_time: 1m
party_info_file: "/home/admin/configs/party_info.json"
private_key_path: "/home/admin/configs/ed25519key.pem"
intra_host: broker:8080
engine:
  timeout: 120s
  protocol: http
  content_type: application/json
  uris:
    - for_peer: http://192.168.3.58:8003
      for_self: 192.168.3.58:8003
storage:
  type: mysql
  conn_str: "root:password123@tcp(mysql:3306)/brokerbob?charset=utf8mb4&parseTime=True&loc=Local&interpolateParams=true"
  max_idle_conns: 10
  max_open_conns: 100
  conn_max_idle_time: 2m
  conn_max_lifetime: 5m

mysql数据库连接为broker元数据的db配置（这里和业务源数据库bob连接同一个mysql，只是库有所区分）。

2.4、配置 SCQLEngine

在工作区中创建一个名为 gflags.conf 的文件，并粘贴如下代码：

--listen_port=8003
--datasource_router=embed
--enable_driver_authorization=false
--server_enable_ssl=false
--driver_enable_ssl_as_client=false
--peer_engine_enable_ssl_as_client=false
--embed_router_conf={"datasources":[{"id":"ds001","name":"mysql db","kind":"MYSQL","connection_str":"db=bob;user=root;password=password123;host=mysql;auto-reconnect=true"}],"rules":[{"db":"*","table":"*","datasource_id":"ds001"}]}
# party authentication flags
--enable_self_auth=false
--enable_peer_auth=false

mysql数据库连接为业务源数据库bob的db配置（这里和broker连接同一个mysql，只是库有所区分）。

2.5、创建 docker-compose 文件

在您的工作区中创建一个名为 docker-compose.yaml 的文件，并粘贴以下代码：

version: '3.8'
services:
  broker:
    image: secretflow/scql:latest
    command:
      - /home/admin/bin/broker
      - -config=/home/admin/configs/config.yml
    restart: always
    ports:
      - 8080:8080
      - 8081:8081
    volumes:
      - ./config.yml:/home/admin/configs/config.yml
      - ./party_info.json:/home/admin/configs/party_info.json
      - ./ed25519key.pem:/home/admin/configs/ed25519key.pem
  engine:
    cap_add:
      - NET_ADMIN
    command:
      - /home/admin/bin/scqlengine
      - --flagfile=/home/admin/engine/conf/gflags.conf
    image: secretflow/scql:latest
    ports:
      - 8003:8003
    volumes:
      - ./gflags.conf:/home/admin/engine/conf/gflags.conf
  mysql:
    image: mysql:latest
    environment:
      - MYSQL_ROOT_PASSWORD=password123
      - TZ=Asia/Shanghai
    healthcheck:
      retries: 10
      test:
        - CMD
        - mysqladmin
        - ping
        - -h
        - mysql
      timeout: 20s
    expose:
      - "3306"
    restart: always
    volumes:
      - ./bob_init.sql:/docker-entrypoint-initdb.d/bob_init.sql
      - ./broker_init_bob.sql:/docker-entrypoint-initdb.d/broker_init_bob.sql

2.6、准备身份验证文件

参与方身份通过私钥-公钥对进行识别，因此我们需要生成这些文件。

~~创建192.168.3.58的参与方bob的密钥~~（已在1.6中生成）：

# generate private key
openssl genpkey -algorithm ed25519 -out ed25519key.pem

并获取自己的公钥，以便给party_info.json中使用：

# get public key corresponding to the private key, the output can be used to replace the __ALICE_PUBLIC_KEY__ in party_info.json
# for engine Bob,  the output can be used to replace the __BOB_PUBLIC_KEY__ in party_info.json
openssl pkey -in ed25519key.pem  -pubout -outform DER | base64

在您的工作区中创建一个名为 party_info.json 的文件，并粘贴以下代码：

{
  "participants": [
    {
      "party_code": "alice",
      "endpoint": "http://192.168.3.20:8081",
      "pubkey": "MCowBQYDK2VwAyEA3Ijkj1iaGBtpTukw78vBr8j+IuuW3dohTMm9rO3wLOg="
    },
    {
      "party_code": "bob",
      "endpoint": "http://192.168.3.58:8081",
      "pubkey": "MCowBQYDK2VwAyEAnRDPlvULRuuC9oIcQjBs6uHuonkdp1e+kP29cElocdo="
    }
  ]
}

2.7、启动服务

您的工作区文件应如下所示：

└── scql-p2p
  ├── bob_init.sql
  ├── broker_init_bob.sql
  ├── config.yml
  ├── docker-compose.yaml
  ├── ed25519key.pem
  ├── gflags.conf
  └── party_info.json

然后您可以运行docker-compose up来启动引擎服务：

# If you install docker with Compose V1, please use `docker-compose` instead of `docker compose`
docker-compose -f docker-compose.yaml up -d

Creating network "scql-p2p_default" with the default driver
Creating scql-p2p_mysql_1  ... done
Creating scql-p2p_engine_1 ... done
Creating scql-p2p_broker_1 ... done

查看进程：

docker-compose ps               
      Name                     Command                       State                                                 Ports                                       
---------------------------------------------------------------------------------------------------------------------------------------------------------------
scql-p2p_broker_1   /home/admin/bin/broker -co ...   Up                      0.0.0.0:8080->8080/tcp,:::8080->8080/tcp, 0.0.0.0:8081->8081/tcp,:::8081->8081/tcp
scql-p2p_engine_1   /home/admin/bin/scqlengine ...   Up                      0.0.0.0:8003->8003/tcp,:::8003->8003/tcp                                          
scql-p2p_mysql_1    docker-entrypoint.sh mysqld      Up (healthy)            3306/tcp, 33060/tcp

您可以使用docker logs检查engine和broker是否正常工作：

docker logs -f scql-p2p_engine_1

[info] [main.cc:main:453] [sciengine] Started engine rpc server success, listen on: 0.0.0.0:8003

docker logs -f scql-p2p_broker_1

INFO main.go:190 Starting to serve request on :8081 with http...
INFO main.go:190 Starting to serve request on 0.0.0.0:8080 with http...

三、测试SCQL

3.1、构建 brokerctl

注意拉取对应版本0.6.0b1的源码，同时注意golang版本需要大于等于1.19。

# Grab a copy of scql:
git clone -b 0.6.0b1 https://github.com/secretflow/scql.git
cd scql

# build scdbclient from source
# requirements:
#   go version >= 1.19
go build -o brokerctl cmd/brokerctl/main.go

# try brokerctl
./brokerctl --help

3.2、创建项目并邀请参与方加入

./brokerctl create project --project-id "demo" --host http://192.168.3.20:8080
./brokerctl get project --host http://192.168.3.20:8080
./brokerctl invite bob --project-id "demo" --host http://192.168.3.20:8080
./brokerctl get invitation --host http://192.168.3.58:8080
./brokerctl process invitation 1 --response "accept" --project-id "demo" --host http://192.168.3.58:8080
./brokerctl get project --host http://192.168.3.20:8080

3.3、创建数据表

./brokerctl create table ta --project-id "demo" --columns "ID string, credit_rank int, income int, age int" --ref-table alice.user_credit --db-type mysql --host http://192.168.3.20:8080
./brokerctl get table ta --host http://192.168.3.20:8080 --project-id "demo"
./brokerctl create table tb --project-id "demo" --columns "ID string, order_amount double, is_active int" --ref-table bob.user_stats --db-type mysql --host http://192.168.3.58:8080
./brokerctl get table tb --host http://192.168.3.58:8080 --project-id "demo"

3.4、授权CCL

./brokerctl grant alice PLAINTEXT --project-id "demo" --table-name ta --column-name ID --host http://192.168.3.20:8080
./brokerctl grant alice PLAINTEXT --project-id "demo" --table-name ta --column-name credit_rank --host http://192.168.3.20:8080
./brokerctl grant alice PLAINTEXT --project-id "demo" --table-name ta --column-name income --host http://192.168.3.20:8080
./brokerctl grant alice PLAINTEXT --project-id "demo" --table-name ta --column-name age --host http://192.168.3.20:8080

./brokerctl grant bob PLAINTEXT_AFTER_JOIN --project-id "demo" --table-name ta --column-name ID --host http://192.168.3.20:8080
./brokerctl grant bob PLAINTEXT_AFTER_GROUP_BY --project-id "demo" --table-name ta --column-name credit_rank --host http://192.168.3.20:8080
./brokerctl grant bob PLAINTEXT_AFTER_AGGREGATE --project-id "demo" --table-name ta --column-name income --host http://192.168.3.20:8080
./brokerctl grant bob PLAINTEXT_AFTER_COMPARE --project-id "demo" --table-name ta --column-name age --host http://192.168.3.20:8080

./brokerctl grant bob PLAINTEXT --project-id "demo" --table-name tb --column-name ID --host http://192.168.3.58:8080
./brokerctl grant bob PLAINTEXT --project-id "demo" --table-name tb --column-name order_amount --host http://192.168.3.58:8080
./brokerctl grant bob PLAINTEXT --project-id "demo" --table-name tb --column-name is_active --host http://192.168.3.58:8080

./brokerctl grant alice PLAINTEXT_AFTER_JOIN --project-id "demo" --table-name tb --column-name ID --host http://192.168.3.58:8080
./brokerctl grant alice PLAINTEXT_AFTER_COMPARE --project-id "demo" --table-name tb --column-name is_active --host http://192.168.3.58:8080
./brokerctl grant alice PLAINTEXT_AFTER_AGGREGATE --project-id "demo" --table-name tb --column-name order_amount --host http://192.168.3.58:8080

./brokerctl get ccl  --project-id "demo" --parties alice --host http://192.168.3.20:8080
./brokerctl get ccl  --project-id "demo" --parties bob --host http://192.168.3.20:8080

3.5、执行查询

./brokerctl run "SELECT ta.credit_rank, COUNT(*) as cnt, AVG(ta.income) as avg_income, AVG(tb.order_amount) as avg_amount FROM ta INNER JOIN tb ON ta.ID = tb.ID WHERE ta.age >= 20 AND ta.age <= 30 AND tb.is_active=1 GROUP BY ta.credit_rank;"  --project-id "demo" --host http://192.168.3.20:8080 --timeout 3

结果：

run query succeeded
Warning : for safety, we filter the results for groups which contain less than 4 items.
[fetch]
2 rows in set: (1.168158633s)
+-------------+-----+--------------------+--------------------+
| credit_rank | cnt |     avg_income     |     avg_amount     |
+-------------+-----+--------------------+--------------------+
|           5 |   6 | 18069.775970458984 | 7743.3486404418945 |
|           6 |   4 |  336016.8591003418 |  5499.404067993164 |
+-------------+-----+--------------------+--------------------+

四、Q&A

4.1、docker-compose版本

docker compose up -d 是 Docker Compose 2.x 版本及以上的写法，它会根据当前目录下的 docker-compose.yml 文件来启动容器。
docker-compose up -d 是 Docker Compose 1.x 版本的写法，同样也是根据当前目录下的 docker-compose.yml 文件来启动容器。

两者的作用完全相同，只是写法不同，取决于使用的 Docker Compose 版本。

4.2、SCQLBroker连不上数据库

$ docker logs -f scql-p2p_broker_1
Failed to create broker db: dial tcp 172.32.137.4:3306: connect: connection refused

等待自动重启即可。

4.3、找不到私钥

$ docker logs -f scql-p2p_broker_1
Failed to create app: private key path and content are both empty, provide at least one

需要检查文档版本是否一致。比如官方文档参考的版本是0.5.0b2，与当前最新0.6.0b1在配置上的区别是，config.yml中关于私钥文件路径的配置项名称：

0.5.0b2 版本中是private_pem_path。
0.6.0b1 版本中是private_key_path。

所以可能会出现找不到私钥的问题。也可以根据报错日志和源码定位到。

4.4、执行查询建立会话失败

./brokerctl run "SELECT ta.credit_rank, COUNT(*) as cnt, AVG(ta.income) as avg_income, AVG(tb.order_amount) as avg_amount FROM ta INNER JOIN tb ON ta.ID = tb.ID WHERE ta.age >= 20 AND ta.age <= 30 AND tb.is_active=1 GROUP BY ta.credit_rank;"  --project-id "demo" --host http://192.168.3.20:8080 --timeout 3
Error: run query: DoQuery response: {
  "status": {
    "code": 320,
    "message": "RunExecutionPlan create session(bd2ac9f7-0139-11ef-b669-0242ac208902) failed, catch std::exception=[engine/link/mux_link_factory.cc:192] send failed: link_id=bd2ac9f7-0139-11ef-b669-0242ac208902 sender_rank=0 send key=connect_0\u0001\u00020, peer failed code=1, message=dispatch error, link_id=bd2ac9f7-0139-11ef-b669-0242ac208902, error=[Enforce fail at engine/link/listener.cc:34] iter != channels_.end(). channel for rank:0 not exist\nStacktrace:\n#0 scql::engine::MuxReceiverServiceImpl::Push()+0x55850066ac88\n#1 brpc::policy::ProcessRpcRequest()+0x558503497d56\n#2 brpc::ProcessInputMessage()+0x55850348e06b\n#3 brpc::InputMessenger::OnNewMessages()+0x55850348f6fc\n#4 brpc::Socket::ProcessEvent()+0x558503591fd2\n#5 bthread::TaskGroup::task_runner()+0x5585035f17cf\n#6 bthread_make_fcontext+0x5585035db781\n. "
  }
}

注意将config.yml里的engine.uris.for_self中的engine改为实际ip。且注意不要在ip之前再加http://，否则最后执行查询会报错提示无效的字符%2F。