123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500 |
- package services
- import (
- "fmt"
- "io"
- "io/ioutil"
- "log"
- "os"
- "path/filepath"
- "sort"
- "sync"
- "time"
- "xg_fetl/internal/db_executor"
- "xg_fetl/internal/models"
- "xg_fetl/module/csv/csvwriter"
- )
- func WriteMain(
- executor db_executor.DBExecutor,
- tableNames map[string]models.TableInfo,
- baseFilePath string,
- delimiter rune,
- maxFileSize int64,
- pageSize int) error {
- {
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
- err := exportTableConcurrently(tableNames, baseFilePath, pageSize, delimiter, &executor, maxFileSize)
- if err != nil {
- fmt.Println("导出表数据失败:", err)
- return err
- }
- fmt.Println("Table exported successfully with size limit and pagination")
-
- }
- return nil
- }
- type TableResult struct {
- TableName string
- Success bool
- Error error
- GeneratedFiles int
- ErrorCount int
- GoroutineCount int
- TotalRows int
- AverageRowSize int64
- ExportDuration time.Duration
- EstimatedDuration time.Duration
- TableSize int64
- Logs []string
- }
- func currentTime() string {
- return time.Now().Format("2006-01-02 15:04:05")
- }
- func sortedTableNames(tableInfos map[string]models.TableInfo) []string {
- tableNames := make([]string, 0, len(tableInfos))
- for name := range tableInfos {
- tableNames = append(tableNames, name)
- }
- sort.Strings(tableNames)
- return tableNames
- }
- func exportTableConcurrently(
- tableInfos map[string]models.TableInfo,
- baseFilePath string,
- pageSize int,
- delimiter rune,
- executor *db_executor.DBExecutor,
- maxFileSizeMB int64,
- ) error {
- var (
- wg sync.WaitGroup
- resultsMap = make(map[string]TableResult)
- resultsMutex sync.Mutex
- maxConcurrentTableExports = 3
- tableSemaphore = make(chan struct{}, maxConcurrentTableExports)
- maxConcurrentFileExports = 100
- )
-
- logFilePath := filepath.Join(baseFilePath, "export.log")
- logFile, err := os.OpenFile(logFilePath, os.O_CREATE|os.O_WRONLY|os.O_TRUNC, 0644)
- if err != nil {
- return fmt.Errorf("无法打开日志文件: %v", err)
- }
- defer logFile.Close()
-
- logger := log.New(logFile, "", 0)
-
- dataTransferRate, err := estimateDataTransferRate()
- if err != nil {
- msg := fmt.Sprintf("%s 无法估算数据传输速率,默认使用 50 MB/s。错误信息:%v\n", currentTime(), err)
- fmt.Print(msg)
- logger.Print(msg)
- dataTransferRate = 50 * 1024 * 1024
- } else {
- msg := fmt.Sprintf("%s 估算的数据传输速率为:%.2f MB/s\n", currentTime(), dataTransferRate/(1024*1024))
- fmt.Print(msg)
- logger.Print(msg)
- }
-
- for _, tableName := range sortedTableNames(tableInfos) {
- tableInfo := tableInfos[tableName]
- wg.Add(1)
- go func(tableInfo models.TableInfo) {
- tableSemaphore <- struct{}{}
- defer func() {
- <-tableSemaphore
- wg.Done()
- }()
- tableName := tableInfo.Name
- totalRecords := tableInfo.Count
-
- result := TableResult{
- TableName: tableName,
- TotalRows: totalRecords,
- GoroutineCount: 1,
- }
-
- tableDir := filepath.Join(baseFilePath, tableName)
- if err := os.MkdirAll(tableDir, 0755); err != nil {
- msg := fmt.Sprintf("%s 创建表 %s 文件夹失败: %v\n", currentTime(), tableName, err)
- logMessage(&result, msg, logger)
- result.Success = false
- result.Error = err
- result.ErrorCount = 1
- resultsMutex.Lock()
- resultsMap[tableName] = result
- resultsMutex.Unlock()
- return
- }
-
- averageRecordSize, err := estimateAverageRecordSize(*executor, tableName)
- if err != nil {
- msg := fmt.Sprintf("%s 无法估算表 %s 平均记录大小: %v\n", currentTime(), tableName, err)
- logMessage(&result, msg, logger)
- result.Success = false
- result.Error = err
- result.ErrorCount = 1
- resultsMutex.Lock()
- resultsMap[tableName] = result
- resultsMutex.Unlock()
- return
- }
- result.AverageRowSize = averageRecordSize
- msg := fmt.Sprintf("%s 表 %s 的平均记录大小: %d 字节\n", currentTime(), tableName, averageRecordSize)
- logMessage(&result, msg, logger)
-
- tableSize := averageRecordSize * int64(totalRecords)
- result.TableSize = tableSize
- tableSizeMB := float64(tableSize) / (1024.0 * 1024.0)
- msg = fmt.Sprintf("%s 表 %s 的容量约为:%.2f MB\n", currentTime(), tableName, tableSizeMB)
- logMessage(&result, msg, logger)
-
- estimatedDurationSeconds := float64(tableSize) / dataTransferRate
- estimatedDuration := time.Duration(estimatedDurationSeconds * float64(time.Second))
- result.EstimatedDuration = estimatedDuration
- msg = fmt.Sprintf("%s 表 %s 估算导出时间:%.2f 秒\n", currentTime(), tableName, estimatedDurationSeconds)
- logMessage(&result, msg, logger)
-
- maxFileSizeBytes := maxFileSizeMB * 1024 * 1024
- recordsPerFile := int(maxFileSizeBytes / averageRecordSize)
- if recordsPerFile <= 0 {
- recordsPerFile = 1000
- }
- msg = fmt.Sprintf("%s 表 %s 的每个文件应包含的记录数: %d\n", currentTime(), tableName, recordsPerFile)
- logMessage(&result, msg, logger)
-
- totalFiles := (totalRecords + recordsPerFile - 1) / recordsPerFile
- msg = fmt.Sprintf("%s 表 %s 需要的 CSV 文件数量: %d\n", currentTime(), tableName, totalFiles)
- logMessage(&result, msg, logger)
-
- startTime := time.Now()
- var fileWg sync.WaitGroup
- fileSemaphore := make(chan struct{}, maxConcurrentFileExports)
- maxRetries := 3
-
- var errorCount int32
-
- for fileIndex := 0; fileIndex < totalFiles; fileIndex++ {
- fileWg.Add(1)
- result.GoroutineCount++
- go func(fileIndex int) {
- fileSemaphore <- struct{}{}
- defer func() {
- <-fileSemaphore
- fileWg.Done()
- }()
- offset := fileIndex * recordsPerFile
- limit := recordsPerFile
- if offset+limit > totalRecords {
- limit = totalRecords - offset
- }
- csvFilePath := filepath.Join(tableDir, fmt.Sprintf("%s_%d.csv", tableName, fileIndex+1))
- attempt := 0
- for {
- attempt++
- err := exportDataRangeToCSV(*executor, tableName, csvFilePath, delimiter, offset, limit, pageSize)
- if err != nil {
- if attempt < maxRetries {
- msg := fmt.Sprintf("%s 表 %s 导出到文件 %s 时发生错误 (第 %d 次尝试): %v,重试...\n", currentTime(), tableName, csvFilePath, attempt, err)
- logMessage(&result, msg, logger)
- time.Sleep(2 * time.Second)
- continue
- } else {
- msg := fmt.Sprintf("%s 表 %s 导出到文件 %s 时发生错误 (第 %d 次尝试): %v,已达最大重试次数。\n", currentTime(), tableName, csvFilePath, attempt, err)
- logMessage(&result, msg, logger)
-
- errorCount++
- break
- }
- } else {
- msg := fmt.Sprintf("%s 表 %s 的文件 %s 导出完成。\n", currentTime(), tableName, filepath.Base(csvFilePath))
- logMessage(&result, msg, logger)
- break
- }
- }
- }(fileIndex)
- }
-
- fileWg.Wait()
-
- exportDuration := time.Since(startTime)
- result.ExportDuration = exportDuration
- msg = fmt.Sprintf("%s 表 %s 导出完成,共生成 %d 个文件,使用了 %d 个协程。\n", currentTime(), tableName, totalFiles, result.GoroutineCount)
- logMessage(&result, msg, logger)
- success := errorCount == 0
- var finalError error
- if !success {
- finalError = fmt.Errorf("表 %s 导出过程中发生 %d 个错误", tableName, errorCount)
- }
- result.Success = success
- result.Error = finalError
- result.ErrorCount = int(errorCount)
- result.GeneratedFiles = totalFiles
- resultsMutex.Lock()
- resultsMap[tableName] = result
- resultsMutex.Unlock()
- }(tableInfo)
- }
-
- wg.Wait()
-
- err = WriteExportReportToLogFile(logFilePath, resultsMap, tableInfos)
- if err != nil {
- return fmt.Errorf("无法写入日志文件: %v", err)
- }
- return nil
- }
- func estimateAverageRecordSize(executor db_executor.DBExecutor, tableName string) (int64, error) {
-
- sampleSize := 100
- offset := 0
-
- _, records, err := executor.ReadTableFromDBWithPagination(tableName, offset, sampleSize)
- if err != nil {
- return 0, fmt.Errorf("failed to query table: %v", err)
- }
-
- if records == nil || len(records) == 0 {
- return 0, fmt.Errorf("没有获取到抽样记录,表数量可能为 0")
- }
- var totalSize int64 = 0
- var count int64 = 0
-
- for _, record := range records {
- var recordSize int64 = 0
- for _, val := range record {
- if val != "" {
- recordSize += int64(len(val))
- }
- }
- totalSize += recordSize
- count++
- }
- if count == 0 {
- return 0, fmt.Errorf("没有获取到抽样记录,表数量可能为 0")
- }
- averageSize := totalSize / count
- return averageSize, nil
- }
- func exportDataRangeToCSV(
- executor db_executor.DBExecutor,
- tableName string,
- csvFilePath string,
- delimiter rune,
- offset int,
- limit int,
- pageSize int,
- ) error {
-
- var headers []string
- var headersWritten bool = false
-
- totalPages := (limit + pageSize - 1) / pageSize
- for page := 0; page < totalPages; page++ {
- pageOffset := offset + page*pageSize
- pageLimit := pageSize
- if pageOffset+pageLimit > offset+limit {
- pageLimit = (offset + limit) - pageOffset
- }
-
- currentHeaders, records, err := executor.ReadTableFromDBWithPagination(tableName, pageOffset, pageLimit)
- if err != nil {
- return fmt.Errorf("failed to read data from table %s: %v", tableName, err)
- }
-
- if !headersWritten && currentHeaders != nil {
- headers = currentHeaders
- headersWritten = true
- }
-
- appendToFile := page > 0
-
- err = csvwriter.WriteRecordsToCSV(csvFilePath, headers, records, delimiter, appendToFile)
- if err != nil {
- return fmt.Errorf("failed to write data to CSV: %v", err)
- }
- fmt.Printf("表 %s 文件 %s 已写入 %d/%d 页。\n", tableName, filepath.Base(csvFilePath), page+1, totalPages)
- }
- return nil
- }
- func estimateDataTransferRate() (float64, error) {
-
- tmpFile, err := ioutil.TempFile("", "data_transfer_test")
- if err != nil {
- return 0, err
- }
- defer os.Remove(tmpFile.Name())
- defer tmpFile.Close()
-
- testDataSize := 50 * 1024 * 1024
- testData := make([]byte, testDataSize)
-
- startTime := time.Now()
- _, err = tmpFile.Write(testData)
- if err != nil {
- return 0, err
- }
- writeDuration := time.Since(startTime)
-
- writeRate := float64(testDataSize) / writeDuration.Seconds()
-
- tmpFile.Seek(0, io.SeekStart)
- readBuffer := make([]byte, testDataSize)
- startTime = time.Now()
- _, err = io.ReadFull(tmpFile, readBuffer)
- if err != nil && err != io.EOF {
- return 0, err
- }
- readDuration := time.Since(startTime)
-
- readRate := float64(testDataSize) / readDuration.Seconds()
-
- dataTransferRate := (writeRate + readRate) / 2
- return dataTransferRate, nil
- }
|