Run SQL Queries¶
Use the AI Studio SDK to execute SQL statements in a target database, read result columns and rows by statement ID, and optionally save SQL text as workbooks and workbook versions.
Task flow¶
Workbooks, query submissions, and SQL statements each have distinct identifiers. Submitting SQL returns a query ID; describing that query yields the statement ID used to read result rows.
Database name + SQL text
│
▼
Execute SQL (returns query ID)
│
▼
Describe query (extracts statement ID)
│
▼
Read columns and rows by statement ID
Prerequisites¶
Complete the endpoint, personal access token, and workspace setup in Use the SDK.
Database name (
DATABASE_NAME): list accessible databases through the workspace SQL metadata handle, or use a database prepared in Manage Catalogs, Databases, and Tables.SQL statement and workbook name: the example below uses
SELECT 1andsdk_query_demo; replace them with your own read-only query and workbook name as needed.
Set the target database name in your terminal:
export DATABASE_NAME='<database-name>'
Submit SQL and read results¶
The example below checks table metadata for the target database, saves a workbook version, and submits a SQL query. Query submission returns a query ID; describing that query provides the statement ID used to fetch paginated result rows.
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")
}
The returned result object contains pagination metadata, column definitions, and output rows for the current read. Result rows and execution profiles can be read once the query record populates a non-empty statement_id.