关于SCQL run in kuscia中使用kusciadatamesh的一些疑问 #436

Handikang · 2025-01-10T03:31:14Z

Issue Type

Others

Have you searched for existing issues?

Yes

Link to Relevant Documentation

No response

Question Details

kuscua版本：0.8.0b0
scql版本：0.9.2b1

按照官方提供的https://www.secretflow.org.cn/zh-CN/docs/kuscia/v0.9.0b0/tutorial/run_scql_on_kuscia_cn
和https://www.secretflow.org.cn/zh-CN/docs/scql/0.9.2b1/topics/deployment/run-scql-on-kuscia
两个连接我将scql部署在了kuscia中，目前仅是单节点自查单表的sql
下面是对应的操作以及结果：
# 在容器内执行示例
export CTR_CERTS_ROOT=/home/kuscia/var/certs
curl -k -X POST 'https://localhost:8082/api/v1/domaindatasource/create' \
 --header "Token: $(cat ${CTR_CERTS_ROOT}/token)" \
 --header 'Content-Type: application/json' \
 --cert ${CTR_CERTS_ROOT}/kusciaapi-server.crt \
 --key ${CTR_CERTS_ROOT}/kusciaapi-server.key \
 --cacert ${CTR_CERTS_ROOT}/ca.crt \
 -d '{
  "domain_id": "com2023011620072311738",
  "datasource_id":"scql-demo-local-datasource",
  "type":"mysql",
  "name": "DemoDataSource",
  "info": {
      "database": {
          "endpoint": "10x.28.5.09:3306",
          "user": "root",
          "password": "123456",
          "database":"alice"
      }
  },
  "access_directly": true
}'


export CTR_CERTS_ROOT=/home/kuscia/var/certs
curl -k -X POST 'https://localhost:8082/api/v1/domaindata/create' \
 --header "Token: $(cat ${CTR_CERTS_ROOT}/token)" \
 --header 'Content-Type: application/json' \
 --cert ${CTR_CERTS_ROOT}/kusciaapi-server.crt \
 --key ${CTR_CERTS_ROOT}/kusciaapi-server.key \
 --cacert ${CTR_CERTS_ROOT}/ca.crt \
 -d '{
  "domain_id": "com2023011620072311738",
  "domaindata_id": "scqltable2",
  "datasource_id": "scql-demo-local-datasource",
  "name": "alice001",
  "type": "table",
  "relative_uri": "alice.user_credit",
  "columns": [
    {
      "name": "ID",
      "type": "str"
    },
    {
      "name": "credit_rank",
      "type": "int"
    },
    {
      "name": "income",
      "type": "int"
    },
    {
      "name": "age",
      "type": "int"
    }
  ]
}'


curl -X POST http://127.0.0.1:80/intra/project/create \
--header "host: scql-broker-intra.com2023011620072311738.svc" \
--header "kuscia-source: com2023011620072311738" \
-d '{
    "project_id":"demo2",
    "name":"demo2",
    "conf":{
        "spu_runtime_cfg":{
        "protocol":"SEMI2K",
        "field":"FM64"
        }
    },
   "description":"this is a project"
}'


curl -X POST http://127.0.0.1:80/intra/table/create \
--header "host: scql-broker-intra.com2023011620072311738.svc" \
--header "kuscia-source: com2023011620072311738" \
-H "Content-Type: application/json" \
-d '{
    "project_id": "demo2",
    "table_name": "ta",
    "ref_table": "scqltable2",
    "db_type": "mysql",
    "columns": [
        {"name":"ID","dtype":"string"},
        {"name":"credit_rank","dtype":"int"},
        {"name":"income","dtype":"int"},
        {"name":"age","dtype":"int"}
    ]
}'


curl -X POST http://127.0.0.1:80/intra/ccl/grant \
--header "host: scql-broker-intra.com2023011620072311738.svc" \
--header "kuscia-source: com2023011620072311738" \
-H "Content-Type: application/json" \
-d '{
    "project_id": "demo2",
    "column_control_list":[
    {"col":{"column_name":"ID","table_name":"ta"},"party_code":"com2023011620072311738","constraint":1},
    {"col":{"column_name":"age","table_name":"ta"},"party_code":"com2023011620072311738","constraint":1},
    {"col":{"column_name":"income","table_name":"ta"},"party_code":"com2023011620072311738","constraint":1},
    {"col":{"column_name":"credit_rank","table_name":"ta"},"party_code":"com2023011620072311738","constraint":1}
    ]
}'


curl -X POST http://127.0.0.1:80/intra/query \
--header "host: scql-broker-intra.com2023011620072311738.svc" \
--header "kuscia-source: com2023011620072311738" \
-H "Content-Type: application/json" \
-d '{
    "project_id": "demo2",
    "query":"SELECT * from ta;"
}'


下面是对应结果
curl -X POST http://127.0.0.1:80/intra/query \
--header "host: scql-broker-intra.com2023011620072311738.svc" \
--header "kuscia-source: com2023011620072311738" \
-H "Content-Type: application/json" \
-d '{
    "project_id": "demo2",
    "query":"SELECT * from ta;"
}'
{"status":{"code":320, "message":"RunExecutionPlan run jobs(fb626e58-cf02-11ef-a085-d2a244c564e6) failed, catch std::exception=[engine/datasource/odbc_adaptor.cc:44] catch unexpected Poco::Data::DataException: MySQL: [MySQL]: [Comment]: mysql_stmt_prepare error\t[mysql_stmt_error]: Table 'alice.scqltable2' doesn't exist\t[mysql_stmt_errno]: 1146\t[mysql_stmt_sqlstate]: 42S02\t[statemnt]: select scqltable2.ID,scqltable2.age,scqltable2.credit_rank,scqltable2.income from scqltable2;", "details":[]}, "result":null}



我的疑问是，通过kusciadatamesh路由到的数据源为什么没能找到ta对应的表alice.user_credit，而是通过kuscia中的domaindata去查

zimu-yuxi · 2025-01-10T06:18:30Z

mysql数据源ref_table使用alice.user_credit尝试下

tyrone-yu · 2025-01-10T06:34:31Z

curl -X POST http://127.0.0.1:80/intra/table/create \
--header "host: scql-broker-intra.com2023011620072311738.svc" \
--header "kuscia-source: com2023011620072311738" \
-H "Content-Type: application/json" \
-d '{
    "project_id": "demo2",
    "table_name": "ta",
    "ref_table": "scqltable2",
    "db_type": "mysql",
    "columns": [
        {"name":"ID","dtype":"string"},
        {"name":"credit_rank","dtype":"int"},
        {"name":"income","dtype":"int"},
        {"name":"age","dtype":"int"}
    ]
}'

这里 ref_table 必须是物理表名，所以用 kuscia 的时候，kuscia 里面注册的表名要和原始的表名一致。目前只能这么使用，可能文档说明不是很清楚。

Handikang · 2025-01-10T06:46:14Z

感谢回复，我这边重试一下

Handikang · 2025-01-10T07:09:46Z

我这边再创建表阶段ref_table引用的是alice.user_credit就报kuscia那边没有这个domaindata了，所以这边就很奇怪

tyrone-yu · 2025-01-10T08:11:44Z

我这边再创建表阶段ref_table引用的是alice.user_credit就报kuscia那边没有这个domaindata了，所以这边就很奇怪

注册 domaindata 的时候的 domaindata_id 也要改下，改成 alice.user_credit
SCQL 的逻辑是根据你提供的 query 找到对应 create table 的时候的 ref table，如果你用的是 kuscia 则用这个 ref table 去查询对应的 domaindata，所以 domaindata 要和 ref table 一致，也要和实际数据库里的表名一致

Handikang · 2025-01-10T08:19:41Z

这个我也试过了，由于创建domaindata这种命名方式（alice.usercredit）不被允许，所以这种我也没跑通。

…

---原始邮件--- 发件人: ***@***.***> 发送时间: 2025年1月10日(周五) 下午4:12 收件人: ***@***.***>; 抄送: "Han ***@***.******@***.***>; 主题: Re: [secretflow/scql] 关于SCQL run in kuscia中使用kusciadatamesh的一些疑问 (Issue #436) 我这边再创建表阶段ref_table引用的是alice.user_credit就报kuscia那边没有这个domaindata了，所以这边就很奇怪注册 domaindata 的时候的 domaindata_id 也要改下，改成 alice.user_credit SCQL 的逻辑是根据你提供的 query 找到对应 create table 的时候的 ref table，如果你用的是 kuscia 则用这个 ref table 去查询对应的 domaindata，所以 domaindata 要和 ref table 一致，也要和实际数据库里的表名一致 — Reply to this email directly, view it on GitHub, or unsubscribe. You are receiving this because you authored the thread.Message ID: ***@***.***>

tyrone-yu · 2025-01-10T08:21:37Z

这个我也试过了，由于创建domaindata这种命名方式（alice.usercredit）不被允许，所以这种我也没跑通。
…
---原始邮件--- 发件人: @.> 发送时间: 2025年1月10日(周五) 下午4:12 收件人: @.>; 抄送: "Han @.@.>; 主题: Re: [secretflow/scql] 关于SCQL run in kuscia中使用kusciadatamesh的一些疑问 (Issue #436) 我这边再创建表阶段ref_table引用的是alice.user_credit就报kuscia那边没有这个domaindata了，所以这边就很奇怪注册 domaindata 的时候的 domaindata_id 也要改下，改成 alice.user_credit SCQL 的逻辑是根据你提供的 query 找到对应 create table 的时候的 ref table，如果你用的是 kuscia 则用这个 ref table 去查询对应的 domaindata，所以 domaindata 要和 ref table 一致，也要和实际数据库里的表名一致 — Reply to this email directly, view it on GitHub, or unsubscribe. You are receiving this because you authored the thread.Message ID: @.***>

发一下你创建的时候 curl 命令，理论上不应该出现这种情况

Handikang · 2025-01-10T08:22:22Z

好的，这边是对应的结果，您看下

tyrone-yu · 2025-01-10T08:38:37Z

好的，这边是对应的结果，您看下

记错了 domaindata_id 要从 alice.usercredit 改成 usercredit

Handikang · 2025-01-10T08:43:50Z

好的，这边我之后再试下，辛苦😁，这下应该没问题了

…

---原始邮件--- 发件人: ***@***.***> 发送时间: 2025年1月10日(周五) 下午4:39 收件人: ***@***.***>; 抄送: "Han ***@***.******@***.***>; 主题: Re: [secretflow/scql] 关于SCQL run in kuscia中使用kusciadatamesh的一些疑问 (Issue #436) 好的，这边是对应的结果，您看下记错了 domaindata_id 要从 alice.usercredit 改成 usercredit — Reply to this email directly, view it on GitHub, or unsubscribe. You are receiving this because you authored the thread.Message ID: ***@***.***>

tongke6 assigned tyrone-yu Jan 10, 2025

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

关于SCQL run in kuscia中使用kusciadatamesh的一些疑问 #436

关于SCQL run in kuscia中使用kusciadatamesh的一些疑问 #436

Handikang commented Jan 10, 2025 •

edited

Loading

zimu-yuxi commented Jan 10, 2025

tyrone-yu commented Jan 10, 2025

Handikang commented Jan 10, 2025

Handikang commented Jan 10, 2025

tyrone-yu commented Jan 10, 2025

Handikang commented Jan 10, 2025 via email

tyrone-yu commented Jan 10, 2025

Handikang commented Jan 10, 2025

tyrone-yu commented Jan 10, 2025

Handikang commented Jan 10, 2025 via email

关于SCQL run in kuscia中使用kusciadatamesh的一些疑问 #436

关于SCQL run in kuscia中使用kusciadatamesh的一些疑问 #436

Comments

Handikang commented Jan 10, 2025 • edited Loading

Issue Type

Have you searched for existing issues?

Link to Relevant Documentation

Question Details

zimu-yuxi commented Jan 10, 2025

tyrone-yu commented Jan 10, 2025

Handikang commented Jan 10, 2025

Handikang commented Jan 10, 2025

tyrone-yu commented Jan 10, 2025

Handikang commented Jan 10, 2025 via email

tyrone-yu commented Jan 10, 2025

Handikang commented Jan 10, 2025

tyrone-yu commented Jan 10, 2025

Handikang commented Jan 10, 2025 via email

Handikang commented Jan 10, 2025 •

edited

Loading