实现一个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 文件。

环境变量 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 方式启动:

以debug方式启动

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

正常debug

项目特性总结

这个完整的 SQL 执行器 Operator 项目提供了:

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