执行 SQL 查询¶
使用 AI Studio SDK 在指定数据库中提交 SQL 语句,按查询记录中的语句 ID 读取结果列与行数据,并可按需将 SQL 保存为工作簿及版本。
任务流程¶
工作簿、查询请求与语句结果分别使用各自的资源标识。提交 SQL 返回查询 ID,再从查询详情提取语句 ID 读取最终结果。
数据库名称 + SQL 文本
│
▼
提交 SQL(返回查询 ID)
│
▼
读取查询详情(取得语句 ID)
│
▼
按语句 ID 读取列与结果行
准备¶
已完成 使用 SDK 中的服务地址、个人访问令牌与工作区配置。
数据库名称(
DATABASE_NAME):通过工作区的 SQL 元数据句柄列出有权访问的数据库,或使用在 管理目录、数据库和表 中准备好的数据库名称。SQL 语句与工作簿名称:下方示例使用
SELECT 1和sdk_query_demo;接入实际业务时替换为目标只读查询语句与工作簿名称。
在终端设置目标数据库名称:
export DATABASE_NAME='<database-name>'
提交 SQL 并读取结果¶
以下示例先核对目标数据库的表元数据并保存一个工作簿版本,再提交 SQL 查询。提交调用返回查询 ID;随后读取查询详情以提取语句 ID,并用该语句 ID 读取分页结果行。
import os
import moi_product_sdk as sdk
client = sdk.new_with_personal_access_token(
os.environ["PRODUCT_API_BASE_URL"], os.environ["PRODUCT_API_KEY"]
)
workspace = client.workspace(os.environ["WORKSPACE_ID"])
def run_sql(workspace, database_name, workbook_name, sql_text):
metadata = workspace.sql_metadata()
database = metadata.database(database_name)
database.tables()
workbook, created_workbook = workspace.create_sql_workbook(workbook_name)
if workbook.id != created_workbook.workbook_id:
raise RuntimeError("workbook handle and result do not match")
version, created_version = workbook.create_version(sql_text)
if version.id != created_version.id:
raise RuntimeError("workbook version handle and result do not match")
version.save()
query, started = workspace.execute_sql(
sql_text,
sdk.with_sql_query_db_name(database_name),
sdk.with_sql_query_limit(100),
)
if query.id != started.query_id:
raise RuntimeError("query handle and result do not match")
described = query.describe()
if not described.statement_id:
raise RuntimeError(f"query {query.id} has no statement id")
statement = workspace.sql_statement(described.statement_id)
return statement.result(sdk.with_sql_result_limit(100))
result = run_sql(workspace, os.environ["DATABASE_NAME"], "sdk_query_demo", "SELECT 1")
print("Statement ID:", result.statement_id, "State:", result.status, "Error:", result.err_msg)
print("Columns:", [column.name for column in result.columns])
for row in result.result:
print(list(row.values))
package main
import (
"context"
"fmt"
"os"
sdk "github.com/matrixorigin/matrixflow/sdk/go-sdk"
)
func runSQL(ctx context.Context, workspace *sdk.WorkspaceHandle, databaseName, workbookName, sqlText string) error {
metadata := workspace.SQLMetadata()
database, err := metadata.Database(databaseName)
if err != nil {
return err
}
if _, err := database.Tables(ctx); err != nil {
return err
}
workbook, createdWorkbook, err := workspace.CreateSQLWorkbook(ctx, workbookName)
if err != nil {
return err
}
if workbook.ID() != createdWorkbook.GetWorkbookId() {
return fmt.Errorf("workbook handle and result do not match")
}
version, createdVersion, err := workbook.CreateVersion(ctx, sqlText)
if err != nil {
return err
}
if version.ID() != createdVersion.GetId() {
return fmt.Errorf("workbook version handle and result do not match")
}
if err := version.Save(ctx); err != nil {
return err
}
query, started, err := workspace.ExecuteSQL(
ctx, sqlText, sdk.WithSQLQueryDBName(databaseName), sdk.WithSQLQueryLimit(100),
)
if err != nil {
return err
}
if query.ID() != started.GetQueryId() {
return fmt.Errorf("query handle and result do not match")
}
described, err := query.Describe(ctx)
if err != nil {
return err
}
if described.GetStatementId() == "" {
return fmt.Errorf("query %q has no statement id", query.ID())
}
statement, err := workspace.SQLStatement(described.GetStatementId())
if err != nil {
return err
}
result, err := statement.Result(ctx, sdk.WithSQLResultLimit(100))
if err != nil {
return err
}
fmt.Println("Statement ID:", result.GetStatementId(), "State:", result.GetStatus(), "Error:", result.GetErrMsg())
for _, column := range result.GetColumns() {
fmt.Print(column.GetName(), "\t")
}
fmt.Println()
for _, row := range result.GetResult() {
fmt.Println(row.GetValues())
}
return nil
}
func main() {
ctx := context.Background()
client, err := sdk.NewWithPersonalAccessToken(
os.Getenv("PRODUCT_API_BASE_URL"),
os.Getenv("PRODUCT_API_KEY"),
)
if err != nil {
panic(err)
}
workspace, err := client.Workspace(os.Getenv("WORKSPACE_ID"))
if err != nil {
panic(err)
}
if err := runSQL(ctx, workspace, os.Getenv("DATABASE_NAME"), "sdk_query_demo", "SELECT 1"); err != nil {
panic(err)
}
fmt.Println("Query result retrieved")
}
返回的结果对象包含本次读取的分页信息、列名定义与数据行。只有当查询详情中已生成非空的语句 ID 时,才能继续读取语句结果或查询执行画像。
下一步¶
最后更新于