使用go备份StarRocks建表语句方法实例

目录

• golang从StarRocks集群中导出所有数据库的表结构
• 步骤
• 可执行的详细代码
• 结果样例

Golang从StarRocks集群中导出所有数据库的表结构

使用StarRocks的非稳定版的最新版本，为了防止因为未知BUG造成集群无法恢复，或者为了定期备份生产集群的元数据信息。都需要一个能够从集群导出表结构的脚本或者是程序。这里选择使用Golang来从StarRocks集群中导出所有数据库的表结构。

步骤

• 获取所有的Database

• 遍历每一个Database获取所有的表

• 遍历每一张表获取每一张表的建表语句

• 因为View的输出结果和Table的数据结果不同，需要进行分类判断。

• 将数据写入文件，文件能直接使用source命令执行。

可执行的详细代码

如下：

package main
import (
 "database/sql"
 "errors"
 "fmt"
 "log"
 "os"
 _ "github.com/go-sql-driver/mysql"
 "github.com/spf13/pflag"
 "github.com/spf13/viper"
)
type Databases struct {
 Database string
}
type Tables struct {
 Tables string
}
type CreateTable struct {
 Table                string
 CreateString         string
 Character_set_client string
 Collation_connection string
}
const (
 defaultStarRocks  = "127.0.0.1"
 defaultPort       = 9030
 defaultUserName   = "root"
 defaultOutputFile = "ddl.sql"
)
func main() {
 var starRocks, userName, password, outputFile string
 var port int
 var includedSys bool
 pflag.StringVarP(&starRocks, "starrocks", "s", defaultStarRocks, "The address of starrocks without a database name.")
 pflag.IntVarP(&port, "port", "p", defaultPort, "The port of starrocks")
 pflag.StringVarP(&userName, "userName", "u", defaultUserName, "The use name of starrocks")
 pflag.StringVarP(&password, "password", "w", "", "The password of starrocks")
 pflag.StringVarP(&outputFile, "outputFile", "o", defaultOutputFile, "The output file name.")
 pflag.BoolVarP(&includedSys, "includedSys", "i", false, "Included the system database or not, default is not.")
 pflag.Parse()
 viper.BindPFlag("starrocks", pflag.Lookup("starrocks"))
 viper.BindPFlag("port", pflag.Lookup("port"))
 viper.BindPFlag("userName", pflag.Lookup("userName"))
 viper.BindPFlag("password", pflag.Lookup("password"))
 viper.BindPFlag("outputFile", pflag.Lookup("outputFile"))
 viper.BindPFlag("includedSys", pflag.Lookup("includedSys"))
 jdbcURL := fmt.Sprintf("%s:%s@tcp(%s:%d)/", userName, password, starRocks, port)
 sysDB := []string{"_statistics_", "information_schema", "sys"}
 databases := getDatabases(jdbcURL)
 if _, err := os.Stat(outputFile); err == nil {
  // File exists, remove it
  errRemove := os.Remove(outputFile)
  if errRemove != nil {
   log.Println("Error removing file:", errRemove)
   return
  }
  log.Println("File removed successfully.")
 }
 for _, db := range databases {
  if !includedSys && containsElement(sysDB, db) {
   continue
  }
  writeToFile(outputFile, fmt.Sprintf("-- Database: %s\n", db))
  writeToFile(outputFile, fmt.Sprintf("use %s;\n", db))
  tables := getTables(jdbcURL, db)
  for _, table := range tables {
   if table != "" {
    createTable := getCreateTable(jdbcURL, db, table)
    for k, v := range createTable {
     var objectType string
     if v == "VIEW" {
      objectType = "View"
     } else if v == "TABLE" {
      objectType = "Table"
     }
     writeToFile(outputFile, fmt.Sprintf("-- %s: %s\n", objectType, table))
     writeToFile(outputFile, fmt.Sprintf("-- Create %s: \n", objectType))
     writeToFile(outputFile, fmt.Sprintf("%s\n", k))
    }
   }
  }
 }
}
func writeToFile(outputFile string, s string) {
 file, err := os.OpenFile(outputFile, os.O_WRONLY|os.O_CREATE|os.O_APPEND, 0644)
 if err != nil {
  fmt.Println("Error opening file:", err)
  return
 }
 defer file.Close()
 _, err = file.Write([]byte(s))
 if err != nil {
  fmt.Println("Error writing to file:", err)
  return
 }
}
func containsElement(list []string, target string) bool {
 for _, element := range list {
  if element == target {
   return true
  }
 }
 return false
}
func getDatabases(jdbcURL string) []string {
 db, err := sql.Open("mysql", jdbcURL)
 if err != nil {
  log.Fatal(err)
 }
 defer db.Close()
 res, err := db.Query("show databases")
 if err != nil {
  log.Fatal(err)
 }
 var databases []string
 for res.Next() {
  var ds Databases
  err := res.Scan(&ds.Database)
  if err != nil {
   log.Fatal(err)
  }
  databases = append(databases, ds.Database)
 }
 return databases
}
func getTables(jdbcURL, dbName string) []string {
 db, err := sql.Open("mysql", jdbcURL+dbName)
 if err != nil {
  log.Fatal(err)
 }
 defer db.Close()
 res, err := db.Query("show tables")
 if err != nil {
  log.Fatal(err)
 }
 var tables []string
 for res.Next() {
  var ts Tables
  err := res.Scan(&ts.Tables)
  if err != nil {
   log.Fatal(err)
  }
  tables = append(tables, ts.Tables)
 }
 jsreturn tables
}
func getCreateTable(jdbcURL, dbName, tableName string) map[string]string {
 db, err := sql.Open("mysql", jdbcURL+dbName)
 if err != nil {
  log.Fatal(err)
 }
 defer db.Close()
 res, err := db.Query("show create table " + tableName)
 if err != nil {
  log.Fatal(err)
 }
 createTables := make(map[string]string)
 for res.Next() {
  var ct CreateTable
  cs, _ := res.Columns()
  err := errors.New("")
  if len(cs) == 4 {
   // for view
   err = res.Scan(&ct.Table, &ct.CreateString, &ct.Character_set_client, &ct.Collation_connection)
   if err != nil {
    log.Fatal(err)
   }
   createTables[ct.CreateString] = "VIEW"
  } else if len(cs) == 2 {
   // for table
   err = res.Scan(&ct.Table, &ct.CreateString)
   if err != nil {
    log.Fatal(err)
   }
   createTables[ct.CreateString] = "TABLE"
  }
 }
 return createTables
}

结果样例

执行完成后，结果样例：

-- Databpythonase: test
use test;
-- Table: test_table
-- Create Table: 
CREATE TABLE `test_table` (
  `day` date NULL COMMENT "天",
  `id` varchar(65533) NULL COMMENT "id",
  `amount` double NULL COMMENT "值",
  `insert_time` varchar(65533) NULL COMMENT "数据插入时间"
) ENGINE=OLAP 
UNIQUE KEY(`day`, `id`)
PARTITION BY RANGE(`day`)
(PARTITION p20231020 VALUES [("2023-10-20"), ("2023-10-21")),
PARTITION p20231021 VALUES [("2023-10-21"), ("2023-10-22")),
PARTITION p20231022 VALUES [("2023-10-22"), ("2023-10-23")),
PARTITION p20231023 VALUES [("2023-10-23"), ("2023-10-24")),
PARTITION p20231024 VALUES [("2023-10-24"), ("2023-10-25")),
PARTITION p20231025 VALUES [("2023-10-25"), ("2023-10-26")),
PARTITION p20231027 VALUES [("202python3-10-27"), ("2023-10-28")),
PARTITION p20231028 VALUES [("2023-10-28"), ("2023-10-29")),
PARTITION p20231029 VALUES [("2023-10-29"), ("2023-10-30")),
PARTITION p20231030 VALUES [("2023-10-30"), ("2023-10-31")))
DISTRIBUTED BY HASH(`id`)
PROPERTIES (
"replication_num" = "1",
"dynamic_partition.enable" = "false",
"dynamic_partiwww.devze.comtion.time_unit" = "DAY",
"dynamic_partition.time_zone" = "Asia/Shanghai",
"dynamic_partition.start" = "-7",
"dynamic_partition.end" = "3",
"dynamic_partition.prefix" = "p",
"dynamic_partition.buckets" = "1",
"dynamic_partition.history_partition_num" = "0",
"in_memory" = "false",
"enable_persistent_index" = "false",
"replicated_storage" = "true",
"compression" = "LZ4"
);
-- View: test_view
-- Create View: 
CREATE VIEW `test_view` (`day`, `id`, `amount`, `insert_time`) AS SELECT `test`.`test_table`.`day`, `test`.`test_table`.`id`,`test`.`test_table`.`amount`, `test`.`test_table`.`insert_time`
FROM `test`.`test_table`
WHERE `test`.`test_table`.`day` >= '2023-11-07' ;

未实现：

• 备份MV

• 备份UDF

• 备份用户角色和grant信息。

• 备份Catalog的创建语句。

以上代码使用go版本为1.21.4，其他版本可能会有些不同，更多关于gojavascript备份StarRocks建表语句的资料请关注编程客栈(www.devze.com)其它相关文章！