实现一个SQL执行Operator
项目初始化
使用 Kubebuilder 创建项目:
# [可选]创建一个新的给 operator 使用的 workspace,并进入
mkdir operator && cd operator
# 创建并进入项目目录
mkdir -p sql-executor-operator && cd sql-executor-operator
# 初始化项目
kubebuilder init --domain hua-ri.cn --repo github.com/hua-ri/sql-executor-operator
# 创建 API
kubebuilder create api --group batch --version v1 --kind SqlJob --resource --controller
然后就可以着手编写第一个 operator 了。我们这里使用 sql-executor-operator 来进行演示。
常用命令
make manifests
make install
export ENABLE_WEBHOOKS=false
make run
make manifests
.PHONY: manifests
manifests: controller-gen ## Generate WebhookConfiguration, ClusterRole and CustomResourceDefinition objects.
$(CONTROLLER_GEN) rbac:roleName=manager-role crd webhook paths="./..." output:crd:artifacts:config=config/crd/bases
执行 make manifests 主要是为了生成 WebhookConfiguration、ClusterRole 和 CustomResourceDefinition 这三个对象。
执行成功后,可以在 config/crd/bases 目录下看到生成的产物,但该文件不能编辑!!!
因为每次执行 make install 和 make run 都会执行 manifests 依赖,该配置会被覆盖。
make install
.PHONY: install
install: manifests kustomize ## Install CRDs into the K8s cluster specified in ~/.kube/config.
$(KUSTOMIZE) build config/crd | $(KUBECTL) apply -f -
先运行 manifests 和 kustomize 目标,然后使用 kustomize 构建 config/crd 目录中的资源,并使用 kubectl 将其应用到集群。
这里 manifests 目标会生成 WebhookConfiguration、ClusterRole 和 CustomResourceDefinition 对象,并保存在 config/crd/bases 目录下。
而 kustomize 目标是进行 kustomize 工具的下载,如果二进制文件不存在则下载,然后生成二进制文件的软链,二进制文件只在首次下载,后续都是仅更新软链。
下面的命令是 install 实际执行的动作:
$(KUSTOMIZE) build config/crd | $(KUBECTL) apply -f -
make run
.PHONY: run
run: manifests generate fmt vet ## Run a controller from your host.
go run ./cmd/main.go
先运行 manifests、generate、fmt 和 vet 目标,然后使用 go run 命令运行 cmd/main.go 文件。
manifests目标会生成WebhookConfiguration、ClusterRole和CustomResourceDefinition对象generate目标使用controller-gen工具生成包含DeepCopy、DeepCopyInto和DeepCopyObject方法实现的代码fmt和vet目标就是执行go fmt ./...和go vet ./...
环境变量 ENABLE_WEBHOOKS
截至目前还没有开启 webhook 能力,但这块可以了解下。
环境变量 ENABLE_WEBHOOKS 控制着 webhook 的启用与否。
这段代码是 kubebuilder 生成的 operator 项目的 main 函数中启用 webhook 逻辑的部分:
if os.Getenv("ENABLE_WEBHOOKS") != "false" {
if err = (&appsv1.Myapp{}).SetupWebhookWithManager(mgr); err != nil {
setupLog.Error(err, "unable to create webhook", "webhook", "Myapp")
os.Exit(1)
}
}
os.Getenv("ENABLE_WEBHOOKS") 返回的值,可能是空字符串,也可能是 bool 类型值的字符串,但是只要值不是 false,就会开启 webhook。
至于这里为什么要临时关闭 webhook,因为启用 webhook 是需要证书的,也就是我们需要在本地安装 cert-manager,并且还需要配置,在本实验项目就不搞太复杂了。
项目实现
API 类型定义 (api/v1/sqljob_types.go)
package v1
import (
corev1 "k8s.io/api/core/v1"
metav1 "k8s.io/apimachinery/pkg/apis/meta/v1"
)
// SqlJobSpec定义了SqlJob的期望状态
type SqlJobSpec struct {
// 全局数据库配置,可以被阶段级别的配置覆盖
// +optional
Database *DatabaseConfig `json:"database,omitempty"`
// 执行阶段定义
Stages []Stage `json:"stages"`
// 全局重试策略
// +optional
RetryPolicy *RetryPolicy `json:"retryPolicy,omitempty"`
// 全局变量,可以在阶段间共享
// +optional
Variables map[string]string `json:"variables,omitempty"`
}
// Stage定义了单个执行阶段
type Stage struct {
// 阶段名称,必须唯一
Name string `json:"name"`
// 阶段描述
// +optional
Description string `json:"description,omitempty"`
// 此阶段的数据库配置(覆盖全局配置)
// +optional
Database *DatabaseConfig `json:"database,omitempty"`
// 操作类型
Type OperationType `json:"type"`
// 要执行的SQL语句
Queries []string `json:"queries"`
// 输出配置(用于查询操作)
// +optional
Output *OutputConfig `json:"output,omitempty"`
// 执行此阶段的条件
// +optional
Conditions []Condition `json:"conditions,omitempty"`
// 期望结果验证
// +optional
Expectations *Expectation `json:"expectations,omitempty"`
// 失败时的回滚查询
// +optional
RollbackQueries []string `json:"rollbackQueries,omitempty"`
// 此阶段的超时时间(秒)
// +optional
Timeout *int32 `json:"timeout,omitempty"`
}
// OperationType表示SQL操作的类型
// +kubebuilder:validation:Enum=Query;Execute;DDL;DML
type OperationType string
const (
// Query - SELECT语句,导出结果
OperationQuery OperationType = "Query"
// Execute - DML操作(INSERT, UPDATE, DELETE)
OperationExecute OperationType = "Execute"
// DDL - 模式变更(CREATE, ALTER, DROP)
OperationDDL OperationType = "DDL"
// DML - 支持事务的数据操作
OperationDML OperationType = "DML"
)
// Condition定义了阶段的执行条件
type Condition struct {
// 条件类型
Type ConditionType `json:"type"`
// 要检查条件的阶段名称
// +optional
Stage string `json:"stage,omitempty"`
// 要评估的表达式
// +optional
Expression string `json:"expression,omitempty"`
// 用于比较的期望值
// +optional
ExpectedValue *string `json:"expectedValue,omitempty"`
// 自定义条件脚本
// +optional
Script *string `json:"script,omitempty"`
}
// ConditionType表示条件的类型
// +kubebuilder:validation:Enum=Always;OnSuccess;OnFailure;OnRowsAffected;Custom
type ConditionType string
const (
ConditionAlways ConditionType = "Always"
ConditionOnSuccess ConditionType = "OnSuccess"
ConditionOnFailure ConditionType = "OnFailure"
ConditionOnRowsAffected ConditionType = "OnRowsAffected"
ConditionCustom ConditionType = "Custom"
)
// Expectation定义了验证的期望结果
type Expectation struct {
// 最小影响行数
// +optional
MinRowsAffected *int64 `json:"minRowsAffected,omitempty"`
// 最大影响行数
// +optional
MaxRowsAffected *int64 `json:"maxRowsAffected,omitempty"`
// 查询的期望行数
// +optional
ExpectedRowCount *int64 `json:"expectedRowCount,omitempty"`
// 自定义验证脚本
// +optional
ValidationScript *string `json:"validationScript,omitempty"`
}
// DatabaseConfig定义了数据库连接参数
type DatabaseConfig struct {
// 数据库类型:mysql或clickhouse
Type DatabaseType `json:"type"`
// 数据库版本(例如:"mysql5.7", "mysql8.0", "clickhouse22.8")
// +optional
Version string `json:"version,omitempty"`
// 连接详情
Host string `json:"host"`
Port int32 `json:"port"`
Database string `json:"database"`
Username string `json:"username"`
// 密码Secret引用
PasswordSecret SecretRef `json:"passwordSecret"`
// 额外的连接参数
// +optional
Parameters map[string]string `json:"parameters,omitempty"`
// SSL/TLS配置
// +optional
TLS *TLSConfig `json:"tls,omitempty"`
// 连接超时时间(秒)
// +optional
Timeout *int32 `json:"timeout,omitempty"`
}
// DatabaseType表示数据库类型
// +kubebuilder:validation:Enum=mysql;clickhouse
type DatabaseType string
const (
MySQL DatabaseType = "mysql"
ClickHouse DatabaseType = "clickhouse"
)
// TLSConfig定义了SSL/TLS配置
type TLSConfig struct {
// 启用TLS
Enabled bool `json:"enabled"`
// CA证书Secret引用
// +optional
CASecret *SecretRef `json:"caSecret,omitempty"`
// 客户端证书Secret引用
// +optional
ClientCertSecret *SecretRef `json:"clientCertSecret,omitempty"`
// 跳过证书验证
// +optional
SkipVerify bool `json:"skipVerify,omitempty"`
}
// SecretRef引用Kubernetes Secret
type SecretRef struct {
Name string `json:"name"`
Key string `json:"key,omitempty"`
}
// OutputConfig定义了输出目的地
type OutputConfig struct {
// CSV配置
CSV CSVConfig `json:"csv"`
// 存储目的地
// +optional
Destinations []Destination `json:"destinations,omitempty"`
}
// CSVConfig定义了CSV输出格式
type CSVConfig struct {
// 文件名前缀
FilenamePrefix string `json:"filenamePrefix"`
// 包含标题行
// +optional
IncludeHeader *bool `json:"includeHeader,omitempty"`
// 字段分隔符
// +optional
Delimiter string `json:"delimiter,omitempty"`
// 自定义日期格式
// +optional
DateFormat string `json:"dateFormat,omitempty"`
}
// Destination定义了输出存储目的地
type Destination struct {
// 目的地类型
Type DestinationType `json:"type"`
// 阿里云OSS配置
// +optional
AliOSS *AliOSSConfig `json:"aliOSS,omitempty"`
// 腾讯云COS配置
// +optional
TencentCOS *TencentCOSConfig `json:"tencentCOS,omitempty"`
// 飞书配置
// +optional
Feishu *FeishuConfig `json:"feishu,omitempty"`
}
// DestinationType表示存储目的地类型
// +kubebuilder:validation:Enum=alioss;tencentcos;feishu
type DestinationType string
const (
AliOSS DestinationType = "alioss"
TencentCOS DestinationType = "tencentcos"
Feishu DestinationType = "feishu"
)
// AliOSSConfig定义了阿里云OSS配置
type AliOSSConfig struct {
Bucket string `json:"bucket"`
Endpoint string `json:"endpoint"`
Region string `json:"region"`
// 包含访问密钥和秘密密钥的Secret
SecretRef SecretRef `json:"secretRef"`
// 对象路径前缀
// +optional
PathPrefix string `json:"pathPrefix,omitempty"`
}
// TencentCOSConfig定义了腾讯云COS配置
type TencentCOSConfig struct {
Bucket string `json:"bucket"`
Region string `json:"region"`
// 包含Secret ID和Secret Key的Secret
SecretRef SecretRef `json:"secretRef"`
// 对象路径前缀
// +optional
PathPrefix string `json:"pathPrefix,omitempty"`
}
// FeishuConfig定义了飞书配置
type FeishuConfig struct {
// 飞书云文档文件夹token
FolderToken string `json:"folderToken"`
// 包含App ID和App Secret的Secret
SecretRef SecretRef `json:"secretRef"`
// 飞书中的文件标题
// +optional
Title string `json:"title,omitempty"`
}
// RetryPolicy定义了作业重试行为
type RetryPolicy struct {
// 最大重试次数
// +optional
MaxRetries *int32 `json:"maxRetries,omitempty"`
// 退避策略
// +optional
Backoff *BackoffPolicy `json:"backoff,omitempty"`
}
// BackoffPolicy定义了重试退避行为
type BackoffPolicy struct {
// 初始退避持续时间(秒)
// +optional
InitialInterval *int32 `json:"initialInterval,omitempty"`
// 最大退避持续时间(秒)
// +optional
MaxInterval *int32 `json:"maxInterval,omitempty"`
// 退避乘数
// +optional
Multiplier *float64 `json:"multiplier,omitempty"`
}
// SqlJobStatus定义了SqlJob的观察状态
type SqlJobStatus struct {
// 作业的当前阶段
Phase JobPhase `json:"phase,omitempty"`
// 当前正在执行的阶段
// +optional
CurrentStage string `json:"currentStage,omitempty"`
// 阶段执行历史
// +optional
StageHistory []StageResult `json:"stageHistory,omitempty"`
// 计算后的全局变量
// +optional
ComputedVariables map[string]string `json:"computedVariables,omitempty"`
// 总体消息
// +optional
Message string `json:"message,omitempty"`
// 作业开始时间
// +optional
StartTime *metav1.Time `json:"startTime,omitempty"`
// 作业完成时间
// +optional
CompletionTime *metav1.Time `json:"completionTime,omitempty"`
// 当前阶段的重试次数
// +optional
RetryCount int32 `json:"retryCount,omitempty"`
}
// JobPhase表示作业的阶段
// +kubebuilder:validation:Enum=Pending;Running;Succeeded;Failed;Retrying
type JobPhase string
const (
JobPending JobPhase = "Pending"
JobRunning JobPhase = "Running"
JobSucceeded JobPhase = "Succeeded"
JobFailed JobPhase = "Failed"
JobRetrying JobPhase = "Retrying"
)
// StageResult包含阶段的执行结果
type StageResult struct {
// 阶段名称
Name string `json:"name"`
// 执行阶段
Phase StagePhase `json:"phase"`
// 详细消息
// +optional
Message string `json:"message,omitempty"`
// 阶段开始时间
// +optional
StartTime *metav1.Time `json:"startTime,omitempty"`
// 阶段完成时间
// +optional
CompletionTime *metav1.Time `json:"completionTime,omitempty"`
// 对于查询操作:导出结果
// +optional
ExportResults []ExportResult `json:"exportResults,omitempty"`
// 对于执行操作:执行统计
// +optional
ExecutionStats *ExecutionStats `json:"executionStats,omitempty"`
// 从此阶段提取的变量
// +optional
Variables map[string]string `json:"variables,omitempty"`
// 条件评估结果
// +optional
ConditionResults []ConditionResult `json:"conditionResults,omitempty"`
}
// StagePhase表示阶段的阶段
// +kubebuilder:validation:Enum=Pending;Running;Succeeded;Failed;Skipped;RolledBack
type StagePhase string
const (
StagePending StagePhase = "Pending"
StageRunning StagePhase = "Running"
StageSucceeded StagePhase = "Succeeded"
StageFailed StagePhase = "Failed"
StageSkipped StagePhase = "Skipped"
StageRolledBack StagePhase = "RolledBack"
)
// ExecutionStats包含执行操作的统计信息
type ExecutionStats struct {
// 所有查询影响的总行数
RowsAffected int64 `json:"rowsAffected"`
// 执行时间(毫秒)
ExecutionTime int64 `json:"executionTime"`
// 单个查询结果
// +optional
QueryResults []QueryResult `json:"queryResults,omitempty"`
}
// QueryResult包含单个查询的结果
type QueryResult struct {
// 查询索引
Index int32 `json:"index"`
// 影响的行数
RowsAffected int64 `json:"rowsAffected"`
// 最后插入的ID(如果适用)
// +optional
LastInsertID *int64 `json:"lastInsertId,omitempty"`
// 如果查询失败,错误消息
// +optional
Error *string `json:"error,omitempty"`
}
// ExportResult包含查询操作的导出信息
type ExportResult struct {
// 目的地类型
DestinationType DestinationType `json:"destinationType"`
// 文件位置或标识符
Location string `json:"location"`
// 导出的行数
RowsExported int64 `json:"rowsExported"`
// 文件大小(字节)
FileSize int64 `json:"fileSize"`
// 上传时间戳
UploadTime metav1.Time `json:"uploadTime"`
}
// ConditionResult包含条件评估结果
type ConditionResult struct {
// 条件描述
Description string `json:"description"`
// 条件是否满足
Met bool `json:"met"`
// 详细消息
// +optional
Message string `json:"message,omitempty"`
}
// +kubebuilder:object:root=true
// +kubebuilder:subresource:status
// +kubebuilder:printcolumn:name="Phase",type=string,JSONPath=`.status.phase`
// +kubebuilder:printcolumn:name="Current Stage",type=string,JSONPath=`.status.currentStage`
// +kubebuilder:printcolumn:name="Age",type=date,JSONPath=`.metadata.creationTimestamp`
// +kubebuilder:printcolumn:name="Message",type=string,JSONPath=`.status.message`
// SqlJob是SqlJob API的Schema
type SqlJob struct {
metav1.TypeMeta `json:",inline"`
metav1.ObjectMeta `json:"metadata,omitempty"`
Spec SqlJobSpec `json:"spec,omitempty"`
Status SqlJobStatus `json:"status,omitempty"`
}
// +kubebuilder:object:root=true
// SqlJobList包含SqlJob列表
type SqlJobList struct {
metav1.TypeMeta `json:",inline"`
metav1.ListMeta `json:"metadata,omitempty"`
Items []SqlJob `json:"items"`
}
func init() {
SchemeBuilder.Register(&SqlJob{}, &SqlJobList{})
}
数据库管理器 (internal/database/manager.go)
package database
import (
"database/sql"
"fmt"
"strings"
"time"
// 导入数据库驱动
_ "github.com/go-sql-driver/mysql"
_ "github.com/ClickHouse/clickhouse-go/v2"
"github.com/yourusername/sql-executor-operator/api/v1"
)
// DatabaseManager处理数据库连接和查询
type DatabaseManager struct{}
// NewDatabaseManager创建新的数据库管理器
func NewDatabaseManager() *DatabaseManager {
return &DatabaseManager{}
}
// Connect根据配置建立数据库连接
func (dm *DatabaseManager) Connect(config v1.DatabaseConfig, password string) (*sql.DB, error) {
var dsn string
var driver string
switch config.Type {
case v1.MySQL:
driver = "mysql"
dsn = dm.buildMySQLDSN(config, password)
case v1.ClickHouse:
driver = "clickhouse"
dsn = dm.buildClickHouseDSN(config, password)
default:
return nil, fmt.Errorf("不支持的数据库类型: %s", config.Type)
}
db, err := sql.Open(driver, dsn)
if err != nil {
return nil, fmt.Errorf("打开数据库连接失败: %v", err)
}
// 设置连接池设置
db.SetMaxOpenConns(10)
db.SetMaxIdleConns(5)
db.SetConnMaxLifetime(time.Hour)
// 测试连接
if err := db.Ping(); err != nil {
return nil, fmt.Errorf("数据库连接测试失败: %v", err)
}
return db, nil
}
// buildMySQLDSN构建MySQL DSN,考虑版本差异
func (dm *DatabaseManager) buildMySQLDSN(config v1.DatabaseConfig, password string) string {
// 基础DSN
dsn := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s", config.Username, password, config.Host, config.Port, config.Database)
// 根据版本添加参数
params := "charset=utf8mb4&parseTime=True&loc=Local"
// MySQL 8.0+ 特定参数
if config.Version >= "8.0" {
params += "&allowNativePasswords=true"
}
// MySQL 5.7 特定参数
if config.Version >= "5.7" && config.Version < "8.0" {
params += "&checkConnLiveness=true"
}
// 如果启用了TLS,添加TLS配置
if config.TLS != nil && config.TLS.Enabled {
if config.TLS.SkipVerify {
params += "&tls=skip-verify"
} else {
params += "&tls=true"
}
}
// 如果指定了超时时间,添加超时参数
if config.Timeout != nil {
params += fmt.Sprintf("&timeout=%ds", *config.Timeout)
}
// 添加自定义参数
for key, value := range config.Parameters {
params += fmt.Sprintf("&%s=%s", key, value)
}
return dsn + "?" + params
}
// buildClickHouseDSN构建ClickHouse DSN,考虑版本差异
func (dm *DatabaseManager) buildClickHouseDSN(config v1.DatabaseConfig, password string) string {
// 基础DSN
dsn := fmt.Sprintf("tcp://%s:%d?username=%s&password=%s&database=%s",
config.Host, config.Port, config.Username, password, config.Database)
// ClickHouse版本特定参数
if config.Version >= "21.8" {
dsn += "&enable_http_compression=1"
}
if config.Version >= "20.1" {
dsn += "&connection_open_timeout=10"
}
// 如果启用了TLS,添加TLS配置
if config.TLS != nil && config.TLS.Enabled {
dsn += "&secure=true"
if config.TLS.SkipVerify {
dsn += "&skip_verify=true"
}
}
// 如果指定了超时时间,添加超时参数
if config.Timeout != nil {
dsn += fmt.Sprintf("&read_timeout=%d", *config.Timeout)
}
// 添加自定义参数
for key, value := range config.Parameters {
dsn += fmt.Sprintf("&%s=%s", key, value)
}
return dsn
}
// ExecuteQuery执行SQL查询并返回结果
func (dm *DatabaseManager) ExecuteQuery(db *sql.DB, query string) (*sql.Rows, error) {
return db.Query(query)
}
// ExecuteStatement执行SQL语句(INSERT, UPDATE, DELETE等)
func (dm *DatabaseManager) ExecuteStatement(db *sql.DB, query string) (sql.Result, error) {
return db.Exec(query)
}
// BeginTransaction开始数据库事务
func (dm *DatabaseManager) BeginTransaction(db *sql.DB) (*sql.Tx, error) {
return db.Begin()
}
// Close关闭数据库连接
func (dm *DatabaseManager) Close(db *sql.DB) error {
return db.Close()
}
// ValidateConnection验证数据库连接是否有效
func (dm *DatabaseManager) ValidateConnection(db *sql.DB) error {
return db.Ping()
}
// GetDatabaseVersion获取数据库版本信息
func (dm *DatabaseManager) GetDatabaseVersion(db *sql.DB, dbType v1.DatabaseType) (string, error) {
var version string
var err error
switch dbType {
case v1.MySQL:
err = db.QueryRow("SELECT VERSION()").Scan(&version)
case v1.ClickHouse:
err = db.QueryRow("SELECT version()").Scan(&version)
default:
return "", fmt.Errorf("不支持的数据库类型: %s", dbType)
}
if err != nil {
return "", fmt.Errorf("获取数据库版本失败: %v", err)
}
return version, nil
}
// IsQueryReadOnly检查查询是否为只读(SELECT语句)
func (dm *DatabaseManager) IsQueryReadOnly(query string) bool {
query = strings.TrimSpace(query)
query = strings.ToUpper(query)
return strings.HasPrefix(query, "SELECT") ||
strings.HasPrefix(query, "SHOW") ||
strings.HasPrefix(query, "DESCRIBE") ||
strings.HasPrefix(query, "EXPLAIN")
}
CSV 处理器 (internal/utils/csv.go)
package utils
import (
"bytes"
"encoding/csv"
"database/sql"
"fmt"
"io"
"strconv"
"time"
"github.com/yourusername/sql-executor-operator/api/v1"
)
// CSVProcessor处理从SQL结果到CSV的转换
type CSVProcessor struct {
}
// NewCSVProcessor创建新的CSV处理器
func NewCSVProcessor() *CSVProcessor {
return &CSVProcessor{}
}
// ConvertToCSV将SQL行转换为CSV格式
func (cp *CSVProcessor) ConvertToCSV(rows *sql.Rows, config v1.CSVConfig) ([]byte, int64, error) {
var buffer bytes.Buffer
writer := csv.NewWriter(&buffer)
// 如果指定了自定义分隔符,设置分隔符
if config.Delimiter != "" {
writer.Comma = rune(config.Delimiter[0])
}
columns, err := rows.Columns()
if err != nil {
return nil, 0, fmt.Errorf("获取列失败: %v", err)
}
// 如果启用了标题,写入标题
includeHeader := true
if config.IncludeHeader != nil {
includeHeader = *config.IncludeHeader
}
if includeHeader {
if err := writer.Write(columns); err != nil {
return nil, 0, fmt.Errorf("写入标题失败: %v", err)
}
}
// 准备值指针
values := make([]interface{}, len(columns))
valuePtrs := make([]interface{}, len(columns))
for i := range values {
valuePtrs[i] = &values[i]
}
var rowCount int64
// 处理每一行
for rows.Next() {
if err := rows.Scan(valuePtrs...); err != nil {
return nil, 0, fmt.Errorf("扫描行失败: %v", err)
}
record := make([]string, len(columns))
for i, val := range values {
record[i] = cp.formatValue(val, config.DateFormat)
}
if err := writer.Write(record); err != nil {
return nil, 0, fmt.Errorf("写入记录失败: %v", err)
}
rowCount++
}
writer.Flush()
if err := rows.Err(); err != nil {
return nil, 0, fmt.Errorf("迭代行时出错: %v", err)
}
return buffer.Bytes(), rowCount, nil
}
// formatValue为CSV输出格式化值
func (cp *CSVProcessor) formatValue(value interface{}, dateFormat string) string {
if value == nil {
return ""
}
switch v := value.(type) {
case time.Time:
if dateFormat != "" {
return v.Format(dateFormat)
}
return v.Format(time.RFC3339)
case []byte:
return string(v)
case int64:
return strconv.FormatInt(v, 10)
case float64:
return strconv.FormatFloat(v, 'f', -1, 64)
case bool:
return strconv.FormatBool(v)
default:
return fmt.Sprintf("%v", v)
}
}
// ValidateCSVConfig验证CSV配置
func (cp *CSVProcessor) ValidateCSVConfig(config v1.CSVConfig) error {
if config.FilenamePrefix == "" {
return fmt.Errorf("filenamePrefix是必需的")
}
if config.Delimiter != "" && len(config.Delimiter) != 1 {
return fmt.Errorf("分隔符必须是单个字符")
}
return nil
}
// GenerateFilename生成带时间戳的文件名
func (cp *CSVProcessor) GenerateFilename(prefix string) string {
timestamp := time.Now().Format("20060102-150405")
return fmt.Sprintf("%s-%s.csv", prefix, timestamp)
}
// GenerateFilenameWithPart生成带部分编号的文件名
func (cp *CSVProcessor) GenerateFilenameWithPart(prefix string, part int) string {
timestamp := time.Now().Format("20060102-150405")
return fmt.Sprintf("%s-%s-part%d.csv", prefix, timestamp, part)
}
条件引擎 (internal/condition/engine.go)
package condition
import (
"fmt"
"strconv"
"strings"
"github.com/yourusername/sql-executor-operator/api/v1"
)
// Engine评估执行条件
type Engine struct {
variables map[string]string
stageHistory map[string]*v1.StageResult
}
// NewEngine创建新的条件引擎
func NewEngine(variables map[string]string, stageHistory map[string]*v1.StageResult) *Engine {
return &Engine{
variables: variables,
stageHistory: stageHistory,
}
}
// EvaluateConditions评估阶段的所有条件
func (e *Engine) EvaluateConditions(conditions []v1.Condition, currentStage string) ([]v1.ConditionResult, bool, error) {
var results []v1.ConditionResult
allMet := true
for _, condition := range conditions {
result, err := e.evaluateCondition(condition, currentStage)
if err != nil {
return nil, false, err
}
results = append(results, result)
if !result.Met {
allMet = false
}
}
return results, allMet, nil
}
// evaluateCondition评估单个条件
func (e *Engine) evaluateCondition(condition v1.Condition, currentStage string) (v1.ConditionResult, error) {
result := v1.ConditionResult{
Description: string(condition.Type),
}
switch condition.Type {
case v1.ConditionAlways:
result.Met = true
result.Message = "条件始终满足"
case v1.ConditionOnSuccess:
if stage, exists := e.stageHistory[condition.Stage]; exists {
result.Met = (stage.Phase == v1.StageSucceeded)
result.Message = fmt.Sprintf("阶段 %s: %s", condition.Stage, stage.Phase)
} else {
result.Met = false
result.Message = fmt.Sprintf("阶段 %s 在历史记录中未找到", condition.Stage)
}
case v1.ConditionOnFailure:
if stage, exists := e.stageHistory[condition.Stage]; exists {
result.Met = (stage.Phase == v1.StageFailed)
result.Message = fmt.Sprintf("阶段 %s: %s", condition.Stage, stage.Phase)
} else {
result.Met = false
result.Message = fmt.Sprintf("阶段 %s 在历史记录中未找到", condition.Stage)
}
case v1.ConditionOnRowsAffected:
if stage, exists := e.stageHistory[condition.Stage]; exists {
if stage.ExecutionStats != nil {
rowsAffected := stage.ExecutionStats.RowsAffected
if condition.ExpectedValue != nil {
expected, err := strconv.ParseInt(*condition.ExpectedValue, 10, 64)
if err != nil {
return result, fmt.Errorf("影响行数的期望值无效: %v", err)
}
result.Met = (rowsAffected == expected)
result.Message = fmt.Sprintf("影响行数: %d, 期望: %d", rowsAffected, expected)
} else if condition.Expression != "" {
met, err := e.evaluateRowsExpression(rowsAffected, condition.Expression)
if err != nil {
return result, err
}
result.Met = met
result.Message = fmt.Sprintf("影响行数: %d, 表达式: %s", rowsAffected, condition.Expression)
}
} else {
result.Met = false
result.Message = "无执行统计信息可用"
}
} else {
result.Met = false
result.Message = fmt.Sprintf("阶段 %s 在历史记录中未找到", condition.Stage)
}
case v1.ConditionCustom:
if condition.Script != nil {
met, err := e.evaluateCustomScript(*condition.Script)
if err != nil {
return result, err
}
result.Met = met
result.Message = "自定义脚本已评估"
} else {
result.Met = false
result.Message = "未提供自定义脚本"
}
default:
return result, fmt.Errorf("不支持的条件类型: %s", condition.Type)
}
return result, nil
}
// evaluateRowsExpression评估行数表达式
func (e *Engine) evaluateRowsExpression(rows int64, expression string) (bool, error) {
expression = strings.TrimSpace(expression)
switch {
case strings.HasPrefix(expression, ">="):
value, err := strconv.ParseInt(strings.TrimSpace(expression[2:]), 10, 64)
if err != nil {
return false, err
}
return rows >= value, nil
case strings.HasPrefix(expression, "<="):
value, err := strconv.ParseInt(strings.TrimSpace(expression[2:]), 10, 64)
if err != nil {
return false, err
}
return rows <= value, nil
case strings.HasPrefix(expression, ">"):
value, err := strconv.ParseInt(strings.TrimSpace(expression[1:]), 10, 64)
if err != nil {
return false, err
}
return rows > value, nil
case strings.HasPrefix(expression, "<"):
value, err := strconv.ParseInt(strings.TrimSpace(expression[1:]), 10, 64)
if err != nil {
return false, err
}
return rows < value, nil
case strings.HasPrefix(expression, "=="):
value, err := strconv.ParseInt(strings.TrimSpace(expression[2:]), 10, 64)
if err != nil {
return false, err
}
return rows == value, nil
case strings.HasPrefix(expression, "!="):
value, err := strconv.ParseInt(strings.TrimSpace(expression[2:]), 10, 64)
if err != nil {
return false, err
}
return rows != value, nil
default:
return false, fmt.Errorf("不支持的表达式格式: %s", expression)
}
}
// evaluateCustomScript评估自定义条件脚本
func (e *Engine) evaluateCustomScript(script string) (bool, error) {
evaluatedScript := script
for key, value := range e.variables {
placeholder := fmt.Sprintf("${%s}", key)
evaluatedScript = strings.ReplaceAll(evaluatedScript, placeholder, value)
}
return e.evaluateSimpleExpression(evaluatedScript)
}
// evaluateSimpleExpression评估简单的布尔表达式
func (e *Engine) evaluateSimpleExpression(expr string) (bool, error) {
expr = strings.ReplaceAll(expr, " ", "")
switch strings.ToLower(expr) {
case "true", "1", "yes":
return true, nil
case "false", "0", "no":
return false, nil
default:
if strings.Contains(expr, "==") {
parts := strings.Split(expr, "==")
if len(parts) == 2 {
return parts[0] == parts[1], nil
}
} else if strings.Contains(expr, "!=") {
parts := strings.Split(expr, "!=")
if len(parts) == 2 {
return parts[0] != parts[1], nil
}
}
return false, fmt.Errorf("无法评估表达式: %s", expr)
}
}
// ExtractVariables从阶段结果中提取变量
func (e *Engine) ExtractVariables(stageResult v1.StageResult) map[string]string {
variables := make(map[string]string)
if stageResult.ExecutionStats != nil {
variables[stageResult.Name+"_rows_affected"] = fmt.Sprintf("%d", stageResult.ExecutionStats.RowsAffected)
variables[stageResult.Name+"_execution_time_ms"] = fmt.Sprintf("%d", stageResult.ExecutionStats.ExecutionTime)
}
if stageResult.ExportResults != nil && len(stageResult.ExportResults) > 0 {
totalRows := int64(0)
for _, result := range stageResult.ExportResults {
totalRows += result.RowsExported
}
variables[stageResult.Name+"_rows_exported"] = fmt.Sprintf("%d", totalRows)
}
if stageResult.Variables != nil {
for k, v := range stageResult.Variables {
variables[stageResult.Name+"_"+k] = v
}
}
return variables
}
存储管理器 (internal/storage/manager.go)
package storage
import (
"bytes"
"context"
"fmt"
"time"
"github.com/yourusername/sql-executor-operator/api/v1"
corev1 "k8s.io/api/core/v1"
"k8s.io/apimachinery/pkg/types"
"sigs.k8s.io/controller-runtime/pkg/client"
)
// StorageManager处理文件上传到各种存储目的地
type StorageManager struct {
client client.Client
}
// NewStorageManager创建新的存储管理器
func NewStorageManager(client client.Client) *StorageManager {
return &StorageManager{
client: client,
}
}
// UploadToDestinations将数据上传到所有配置的目的地
func (sm *StorageManager) UploadToDestinations(
ctx context.Context,
namespace string,
data []byte,
filename string,
rowCount int64,
destinations []v1.Destination,
) ([]v1.ExportResult, error) {
var results []v1.ExportResult
for _, dest := range destinations {
var result v1.ExportResult
var err error
switch dest.Type {
case v1.AliOSS:
result, err = sm.uploadToAliOSS(ctx, namespace, data, filename, rowCount, *dest.AliOSS)
case v1.TencentCOS:
result, err = sm.uploadToTencentCOS(ctx, namespace, data, filename, rowCount, *dest.TencentCOS)
case v1.Feishu:
result, err = sm.uploadToFeishu(ctx, namespace, data, filename, rowCount, *dest.Feishu)
default:
err = fmt.Errorf("不支持的目的地类型: %s", dest.Type)
}
if err != nil {
return nil, fmt.Errorf("上传到 %s 失败: %v", dest.Type, err)
}
result.DestinationType = dest.Type
results = append(results, result)
}
return results, nil
}
存储管理器还包含阿里云 OSS 客户端(
internal/storage/alioss.go)、腾讯云 COS 客户端(internal/storage/tencentcos.go)和飞书客户端(internal/storage/feishu.go)的实现,分别负责对接三种存储目的地的文件上传操作。
主控制器 (controllers/sqljob_controller.go)
package controllers
import (
"context"
"database/sql"
"fmt"
"time"
"github.com/go-logr/logr"
corev1 "k8s.io/api/core/v1"
"k8s.io/apimachinery/pkg/api/errors"
metav1 "k8s.io/apimachinery/pkg/apis/meta/v1"
"k8s.io/apimachinery/pkg/runtime"
"k8s.io/apimachinery/pkg/types"
ctrl "sigs.k8s.io/controller-runtime"
"sigs.k8s.io/controller-runtime/pkg/client"
"sigs.k8s.io/controller-runtime/pkg/controller/controllerutil"
batchv1 "github.com/yourusername/sql-executor-operator/api/v1"
"github.com/yourusername/sql-executor-operator/internal/condition"
"github.com/yourusername/sql-executor-operator/internal/database"
"github.com/yourusername/sql-executor-operator/internal/storage"
"github.com/yourusername/sql-executor-operator/internal/utils"
)
const (
sqlJobFinalizer = "batch.yourcompany.com/finalizer"
)
// SqlJobReconciler协调SqlJob对象
type SqlJobReconciler struct {
client.Client
Log logr.Logger
Scheme *runtime.Scheme
}
// +kubebuilder:rbac:groups=batch.yourcompany.com,resources=sqljobs,verbs=get;list;watch;create;update;patch;delete
// +kubebuilder:rbac:groups=batch.yourcompany.com,resources=sqljobs/status,verbs=get;update;patch
// +kubebuilder:rbac:groups=batch.yourcompany.com,resources=sqljobs/finalizers,verbs=update
// +kubebuilder:rbac:groups=core,resources=secrets,verbs=get;list;watch
// Reconcile是主要的协调循环
func (r *SqlJobReconciler) Reconcile(ctx context.Context, req ctrl.Request) (ctrl.Result, error) {
log := r.Log.WithValues("sqljob", req.NamespacedName)
// 获取SqlJob实例
var sqlJob batchv1.SqlJob
if err := r.Get(ctx, req.NamespacedName, &sqlJob); err != nil {
if errors.IsNotFound(err) {
log.Info("SqlJob资源未找到,可能已被删除")
return ctrl.Result{}, nil
}
log.Error(err, "获取SqlJob失败")
return ctrl.Result{}, err
}
// 检查对象是否正在被删除
if !sqlJob.ObjectMeta.DeletionTimestamp.IsZero() {
return r.finalizeSqlJob(ctx, &sqlJob, log)
}
// 如果不存在,添加finalizer
if !controllerutil.ContainsFinalizer(&sqlJob, sqlJobFinalizer) {
controllerutil.AddFinalizer(&sqlJob, sqlJobFinalizer)
if err := r.Update(ctx, &sqlJob); err != nil {
log.Error(err, "添加finalizer失败")
return ctrl.Result{}, err
}
}
// 如果作业已完成,跳过
if sqlJob.Status.Phase == batchv1.JobSucceeded || sqlJob.Status.Phase == batchv1.JobFailed {
log.Info("作业已完成", "phase", sqlJob.Status.Phase)
return ctrl.Result{}, nil
}
// 初始化状态(如果需要)
if sqlJob.Status.StageHistory == nil {
sqlJob.Status.StageHistory = []batchv1.StageResult{}
}
if sqlJob.Status.ComputedVariables == nil {
sqlJob.Status.ComputedVariables = make(map[string]string)
}
// 更新全局变量
r.updateGlobalVariables(&sqlJob)
// 执行阶段
result, err := r.executeStages(ctx, &sqlJob, log)
if err != nil {
log.Error(err, "执行阶段失败")
return r.handleExecutionError(ctx, &sqlJob, err)
}
return result, nil
}
// executeStages按顺序执行所有阶段
func (r *SqlJobReconciler) executeStages(ctx context.Context, sqlJob *batchv1.SqlJob, log logr.Logger) (ctrl.Result, error) {
for i, stage := range sqlJob.Spec.Stages {
// 检查阶段是否已执行
if r.isStageExecuted(sqlJob, stage.Name) {
continue
}
// 更新当前阶段
sqlJob.Status.CurrentStage = stage.Name
if err := r.Status().Update(ctx, sqlJob); err != nil {
return ctrl.Result{}, err
}
// 评估此阶段的条件
shouldExecute, conditionResults, err := r.evaluateStageConditions(sqlJob, stage)
if err != nil {
return ctrl.Result{}, fmt.Errorf("评估阶段 %s 的条件失败: %v", stage.Name, err)
}
var stageResult batchv1.StageResult
if !shouldExecute {
stageResult = batchv1.StageResult{
Name: stage.Name,
Phase: batchv1.StageSkipped,
Message: "由于条件未满足,跳过阶段",
StartTime: &metav1.Time{Time: time.Now()},
CompletionTime: &metav1.Time{Time: time.Now()},
ConditionResults: conditionResults,
}
} else {
stageResult, err = r.executeStage(ctx, sqlJob, stage, log)
if err != nil {
rollbackErr := r.executeRollback(ctx, sqlJob, stage, log)
if rollbackErr == nil {
stageResult.Phase = batchv1.StageRolledBack
}
stageResult.Message = fmt.Sprintf("阶段执行失败: %v", err)
}
stageResult.ConditionResults = conditionResults
}
// 更新阶段历史
sqlJob.Status.StageHistory = append(sqlJob.Status.StageHistory, stageResult)
// 从此阶段更新计算变量
if stageResult.Variables != nil {
for k, v := range stageResult.Variables {
sqlJob.Status.ComputedVariables[k] = v
}
}
// 更新作业状态
if stageResult.Phase == batchv1.StageFailed || stageResult.Phase == batchv1.StageRolledBack {
sqlJob.Status.Phase = batchv1.JobFailed
sqlJob.Status.Message = fmt.Sprintf("阶段 %s 失败", stage.Name)
sqlJob.Status.CompletionTime = &metav1.Time{Time: time.Now()}
} else if i == len(sqlJob.Spec.Stages)-1 && stageResult.Phase == batchv1.StageSucceeded {
sqlJob.Status.Phase = batchv1.JobSucceeded
sqlJob.Status.Message = "所有阶段成功完成"
sqlJob.Status.CompletionTime = &metav1.Time{Time: time.Now()}
} else if stageResult.Phase == batchv1.StageSucceeded {
sqlJob.Status.Phase = batchv1.JobRunning
sqlJob.Status.Message = fmt.Sprintf("阶段 %s 成功完成", stage.Name)
}
if err := r.Status().Update(ctx, sqlJob); err != nil {
return ctrl.Result{}, err
}
if stageResult.Phase == batchv1.StageFailed {
return ctrl.Result{}, nil
}
}
return ctrl.Result{}, nil
}
// SetupWithManager设置控制器与Manager
func (r *SqlJobReconciler) SetupWithManager(mgr ctrl.Manager) error {
return ctrl.NewControllerManagedBy(mgr).
For(&batchv1.SqlJob{}).
Complete(r)
}
控制器还包含
executeStage、executeQueryStage、executeChangeStage(带事务支持)、executeRollback、validateExpectations、getPasswordFromSecret等辅助方法,完整实现了多阶段 SQL 执行、条件判断、期望验证和回滚机制。
程序入口 (cmd/main.go)
package main
import (
"flag"
"os"
_ "k8s.io/client-go/plugin/pkg/client/auth"
"k8s.io/apimachinery/pkg/runtime"
utilruntime "k8s.io/apimachinery/pkg/util/runtime"
clientgoscheme "k8s.io/client-go/kubernetes/scheme"
ctrl "sigs.k8s.io/controller-runtime"
"sigs.k8s.io/controller-runtime/pkg/healthz"
"sigs.k8s.io/controller-runtime/pkg/log/zap"
batchv1 "github.com/yourusername/sql-executor-operator/api/v1"
"github.com/yourusername/sql-executor-operator/controllers"
)
var (
scheme = runtime.NewScheme()
setupLog = ctrl.Log.WithName("setup")
)
func init() {
utilruntime.Must(clientgoscheme.AddToScheme(scheme))
utilruntime.Must(batchv1.AddToScheme(scheme))
}
func main() {
var metricsAddr string
var enableLeaderElection bool
var probeAddr string
flag.StringVar(&metricsAddr, "metrics-bind-address", ":8080", "指标绑定的地址。")
flag.StringVar(&probeAddr, "health-probe-bind-address", ":8081", "健康检查和就绪检查绑定的地址。")
flag.BoolVar(&enableLeaderElection, "leader-elect", false,
"启用领导者选举。确保多个副本运行时,只有一个副本处于活动状态。")
opts := zap.Options{Development: true}
opts.BindFlags(flag.CommandLine)
flag.Parse()
ctrl.SetLogger(zap.New(zap.UseFlagOptions(&opts)))
mgr, err := ctrl.NewManager(ctrl.GetConfigOrDie(), ctrl.Options{
Scheme: scheme,
MetricsBindAddress: metricsAddr,
Port: 9443,
HealthProbeBindAddress: probeAddr,
LeaderElection: enableLeaderElection,
LeaderElectionID: "a1b2c3d4.sql-executor-operator.yourcompany.com",
LeaderElectionReleaseOnCancel: true,
})
if err != nil {
setupLog.Error(err, "无法启动管理器")
os.Exit(1)
}
if err = (&controllers.SqlJobReconciler{
Client: mgr.GetClient(),
Scheme: mgr.GetScheme(),
Log: ctrl.Log.WithName("controllers").WithName("SqlJob"),
}).SetupWithManager(mgr); err != nil {
setupLog.Error(err, "无法创建控制器", "controller", "SqlJob")
os.Exit(1)
}
if err := mgr.AddHealthzCheck("healthz", healthz.Ping); err != nil {
setupLog.Error(err, "无法设置健康检查")
os.Exit(1)
}
if err := mgr.AddReadyzCheck("readyz", healthz.Ping); err != nil {
setupLog.Error(err, "无法设置就绪检查")
os.Exit(1)
}
setupLog.Info("启动管理器")
if err := mgr.Start(ctrl.SetupSignalHandler()); err != nil {
setupLog.Error(err, "管理器运行失败")
os.Exit(1)
}
}
Dockerfile
# 构建阶段
FROM golang:1.19 as builder
WORKDIR /workspace
COPY go.mod go.sum ./
RUN go mod download
COPY main.go main.go
COPY api/ api/
COPY controllers/ controllers/
COPY internal/ internal/
RUN CGO_ENABLED=0 GOOS=linux GOARCH=amd64 go build -a -o manager main.go
# 使用Distroless作为最小基础镜像
FROM gcr.io/distroless/static:nonroot
WORKDIR /
COPY --from=builder /workspace/manager .
USER 65532:65532
ENTRYPOINT ["/manager"]
RBAC 配置
更新 config/rbac/role.yaml:
apiVersion: rbac.authorization.k8s.io/v1
kind: ClusterRole
metadata:
name: manager-role
rules:
- apiGroups:
- batch.yourcompany.com
resources:
- sqljobs
verbs:
- get
- list
- watch
- create
- update
- patch
- delete
- apiGroups:
- batch.yourcompany.com
resources:
- sqljobs/status
verbs:
- get
- update
- patch
- apiGroups:
- batch.yourcompany.com
resources:
- sqljobs/finalizers
verbs:
- update
- apiGroups:
- ""
resources:
- secrets
verbs:
- get
- list
- watch
构建和部署
生成 CRD manifests 和 Go 代码
make manifests
make generate
构建和推送镜像
# 构建镜像
make docker-build docker-push IMG=your-registry/sql-executor-operator:v1.0.0
# 或者使用自定义镜像名称
docker build -t your-registry/sql-executor-operator:v1.0.0 .
docker push your-registry/sql-executor-operator:v1.0.0
部署到集群
# 部署CRD和控制器
make deploy IMG=your-registry/sql-executor-operator:v1.0.0
# 或者手动部署
kubectl apply -f config/crd/bases/
kubectl apply -f config/rbac/
kubectl apply -f config/manager/
创建必要的 Secrets
# MySQL密码Secret
kubectl create secret generic mysql-secret \
--from-literal=password=your-mysql-password
# 阿里云OSS Secret
kubectl create secret generic alioss-secret \
--from-literal=accessKeyId=your-access-key-id \
--from-literal=accessKeySecret=your-access-key-secret
# 飞书Secret
kubectl create secret generic feishu-secret \
--from-literal=appId=your-app-id \
--from-literal=appSecret=your-app-secret
测试环境搭建
docker-compose 创建数据库测试环境
新建 docker-compose.yaml 文件:
version: '3.8'
services:
# MySQL 5.7
mysql57:
image: m.daocloud.io/docker.io/library/mysql:5.7
platform: linux/amd64
container_name: mysql57-test
environment:
MYSQL_ROOT_PASSWORD: "123456"
MYSQL_DATABASE: "testdb"
ports:
- "3306:3306"
volumes:
- mysql57_data:/var/lib/mysql
- ./config/mysql57/conf.d:/etc/mysql/conf.d
command:
- --character-set-server=utf8mb4
- --collation-server=utf8mb4_unicode_ci
# MySQL 8.0
mysql80:
image: m.daocloud.io/docker.io/library/mysql:8.0
container_name: mysql80-test
environment:
MYSQL_ROOT_PASSWORD: "123456"
MYSQL_DATABASE: "testdb"
ports:
- "3307:3306"
volumes:
- mysql80_data:/var/lib/mysql
- ./config/mysql80/conf.d:/etc/mysql/conf.d
# ClickHouse 20.1
clickhouse20:
image: m.daocloud.io/docker.io/yandex/clickhouse-server:20.1
container_name: clickhouse20-test
platform: linux/amd64
ports:
- "8123:8123" # HTTP接口
- "9000:9000" # 原生TCP接口
ulimits:
nofile:
soft: 262144
hard: 262144
volumes:
- clickhouse20_data:/var/lib/clickhouse
- clickhouse20_log:/var/log/clickhouse-server
# ClickHouse 21.8
clickhouse21:
image: m.daocloud.io/docker.io/yandex/clickhouse-server:21.8
container_name: clickhouse21-test
platform: linux/amd64
ports:
- "8124:8123" # HTTP接口
- "9001:9000" # 原生TCP接口
ulimits:
nofile:
soft: 262144
hard: 262144
volumes:
- clickhouse21_data:/var/lib/clickhouse
- clickhouse21_log:/var/log/clickhouse-server
volumes:
mysql57_data:
mysql80_data:
clickhouse20_data:
clickhouse20_log:
clickhouse21_data:
clickhouse21_log:
执行命令进行环境搭建和销毁:
docker-compose up -d # 启动环境
docker-compose down # 销毁环境
会创建两个 MySQL 容器(5.7 和 8.0)以及两个 ClickHouse 容器(20.1 和 21.8)。
创建测试资源
创建密码 Secret:
apiVersion: v1
kind: Secret
metadata:
name: mysql-root-password
namespace: default
type: Opaque
data:
password: MTIzNDU2 # echo -n '123456' | base64
创建测试用 SqlJob CR(sqljob-mysql-test.yaml):
apiVersion: batch.hua-ri.cn/v1
kind: SqlJob
metadata:
name: mysql80-test-job
namespace: default
spec:
database:
type: mysql
version: "8.0"
host: "127.0.0.1"
port: 3307
database: "testdb"
username: "root"
passwordSecret:
name: "mysql-root-password"
key: "password"
parameters:
charset: "utf8mb4"
parseTime: "true"
retryPolicy:
maxRetries: 3
backoff:
initialInterval: 5
maxInterval: 60
multiplier: 2
variables:
table_prefix: "test_"
batch_size: "100"
stages:
# 阶段1: 创建测试表
- name: "create-tables"
description: "创建测试用的数据表"
type: DDL
queries:
- |
CREATE TABLE IF NOT EXISTS {{.table_prefix}}users (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(100) NOT NULL UNIQUE,
email VARCHAR(255) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
- |
CREATE TABLE IF NOT EXISTS {{.table_prefix}}orders (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
user_id BIGINT NOT NULL,
order_number VARCHAR(50) NOT NULL UNIQUE,
amount DECIMAL(10,2) NOT NULL,
status ENUM('pending', 'completed', 'cancelled') DEFAULT 'pending',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES {{.table_prefix}}users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
timeout: 30
rollbackQueries:
- "DROP TABLE IF EXISTS {{.table_prefix}}orders"
- "DROP TABLE IF EXISTS {{.table_prefix}}users"
# 阶段2: 插入测试数据
- name: "insert-test-data"
description: "插入测试用户和订单数据"
type: DML
queries:
- |
INSERT INTO {{.table_prefix}}users (username, email) VALUES
('user1', 'user1@example.com'),
('user2', 'user2@example.com'),
('user3', 'user3@example.com'),
('user4', 'user4@example.com'),
('user5', 'user5@example.com')
- |
INSERT INTO {{.table_prefix}}orders (user_id, order_number, amount, status) VALUES
(1, 'ORDER001', 99.99, 'completed'),
(1, 'ORDER002', 149.50, 'pending'),
(2, 'ORDER003', 199.99, 'completed'),
(3, 'ORDER004', 79.99, 'completed'),
(4, 'ORDER005', 299.99, 'pending')
conditions:
- type: OnSuccess
stage: "create-tables"
expectations:
minRowsAffected: 5
maxRowsAffected: 10
timeout: 60
# 阶段3: 查询验证数据
- name: "verify-data"
description: "验证插入的数据并导出统计信息"
type: Query
queries:
- "SELECT COUNT(*) as user_count FROM {{.table_prefix}}users"
- "SELECT COUNT(*) as order_count FROM {{.table_prefix}}orders"
- "SELECT status, COUNT(*) as count, SUM(amount) as total_amount FROM {{.table_prefix}}orders GROUP BY status"
output:
csv:
filenamePrefix: "mysql-test-results"
includeHeader: true
delimiter: ","
destinations:
- type: alioss
aliOSS:
bucket: "your-bucket"
endpoint: "oss-cn-hangzhou.aliyuncs.com"
region: "cn-hangzhou"
secretRef:
name: "alioss-secret"
pathPrefix: "sqljob-exports/"
conditions:
- type: OnSuccess
stage: "insert-test-data"
timeout: 30
# 阶段4: 数据更新操作
- name: "update-data"
description: "更新订单状态"
type: DML
queries:
- "UPDATE {{.table_prefix}}orders SET status = 'completed' WHERE status = 'pending' AND amount > 100"
conditions:
- type: OnSuccess
stage: "verify-data"
expectations:
minRowsAffected: 1
timeout: 30
# 阶段5: 最终验证
- name: "final-verification"
description: "最终数据验证"
type: Query
queries:
- "SELECT COUNT(*) as pending_orders FROM {{.table_prefix}}orders WHERE status = 'pending'"
- "SELECT COUNT(*) as completed_orders FROM {{.table_prefix}}orders WHERE status = 'completed'"
conditions:
- type: OnSuccess
stage: "update-data"
timeout: 30
CRD 调试
make manifests
% make manifests
/Users/king/workspace/operator/sql-executor-operator/bin/controller-gen rbac:roleName=manager-role crd webhook paths="./..." output:crd:artifacts:config=config/crd/bases
% ls -lh config/crd/bases/
total 48
-rw-r--r--@ 1 king staff 24K Nov 19 23:53 batch.hua-ri.cn_sqljobs.yaml
make install
% make install
/Users/king/workspace/operator/sql-executor-operator/bin/controller-gen rbac:roleName=manager-role crd webhook paths="./..." output:crd:artifacts:config=config/crd/bases
Warning: unrecognized format "int32"
Warning: unrecognized format "int64"
customresourcedefinition.apiextensions.k8s.io/sqljobs.batch.hua-ri.cn created
查看安装结果:
% kubectl get crd
NAME CREATED AT
sqljobs.batch.hua-ri.cn 2025-11-19T16:07:01Z
make run
export ENABLE_WEBHOOKS=false
make run
执行结果:
% make run
/Users/king/workspace/operator/sql-executor-operator/bin/controller-gen rbac:roleName=manager-role crd webhook paths="./..." output:crd:artifacts:config=config/crd/bases
/Users/king/workspace/operator/sql-executor-operator/bin/controller-gen object:headerFile="hack/boilerplate.go.txt" paths="./..."
go fmt ./...
go vet ./...
go run ./cmd/main.go
2025-11-20T00:10:15+08:00 INFO setup 启动管理器
2025-11-20T00:10:15+08:00 INFO starting server {"name": "health probe", "addr": "[::]:8081"}
2025-11-20T00:10:15+08:00 INFO controller-runtime.metrics Starting metrics server
2025-11-20T00:10:15+08:00 INFO controller-runtime.metrics Serving metrics server {"bindAddress": ":8080", "secure": false}
2025-11-20T00:10:15+08:00 INFO Starting EventSource {"controller": "sqljob", "controllerGroup": "batch.hua-ri.cn", "controllerKind": "SqlJob", "source": "kind source: *v1.SqlJob"}
2025-11-20T00:10:15+08:00 INFO Starting Controller {"controller": "sqljob", "controllerGroup": "batch.hua-ri.cn", "controllerKind": "SqlJob"}
2025-11-20T00:10:15+08:00 INFO Starting workers {"controller": "sqljob", "controllerGroup": "batch.hua-ri.cn", "controllerKind": "SqlJob", "worker count": 1}
已经成功启动!!!
IDE Debug 调试
在上面执行了以下步骤后:
make manifests
make install
export ENABLE_WEBHOOKS=false
make run
我们先将 make run 运行的 controller 停止,然后打开 operator 项目,在项目的调谐函数内打上断点,然后直接以 debug 模式启动。

以 debug 方式启动:

接下来 apply 一个资源,测试一下,已经可以正常 debug:

项目特性总结
这个完整的 SQL 执行器 Operator 项目提供了:
- 多阶段执行:将复杂的数据处理流程分解为多个阶段
- 条件判断:基于前一阶段结果决定是否继续执行
- 变更操作支持:完整的 DDL/DML 操作支持
- 事务支持:对 DML 操作提供事务保证
- 回滚机制:每个阶段可以配置回滚操作
- 期望验证:验证操作结果是否符合预期
- 变量传递:在阶段间传递执行结果和数据
- 多存储支持:支持阿里云 OSS、腾讯云 COS 和飞书云文档
- 多数据库支持:支持 MySQL 和 ClickHouse,考虑版本差异
- 完整的错误处理:包含重试机制和指数退避
- 生产就绪:包含监控、RBAC、资源限制等