使用go備份StarRocks建表語句方法實例

更新時間：2023年12月08日 09:07:04 作者：CDH煎炒烹炸

這篇文章主要為大家介紹了使用go備份StarRocks建表語句方法實例詳解,有需要的朋友可以借鑒參考下,希望能夠有所幫助,祝大家多多進步,早日升職加薪

Golang從StarRocks集群中導出所有數(shù)據(jù)庫的表結(jié)構(gòu)

使用StarRocks的非穩(wěn)定版的最新版本，為了防止因為未知BUG造成集群無法恢復，或者為了定期備份生產(chǎn)集群的元數(shù)據(jù)信息。都需要一個能夠從集群導出表結(jié)構(gòu)的腳本或者是程序。這里選擇使用Golang來從StarRocks集群中導出所有數(shù)據(jù)庫的表結(jié)構(gòu)。

步驟

獲取所有的Database
遍歷每一個Database獲取所有的表
遍歷每一張表獲取每一張表的建表語句
因為View的輸出結(jié)果和Table的數(shù)據(jù)結(jié)果不同，需要進行分類判斷。
將數(shù)據(jù)寫入文件，文件能直接使用source命令執(zhí)行。

可執(zhí)行的詳細代碼

如下：

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)
 }
 return 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
}

結(jié)果樣例

執(zhí)行完成后，結(jié)果樣例：

-- Database: 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 "數(shù)據(jù)插入時間"
) 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 [("2023-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_partition.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' ;

未實現(xiàn)：