OceanBase 知識加油站(工具篇一)：準備好您的資料了嗎？

玉璁，OceanBase 工具團隊技術專家

孫夕恩，花名玉璁，長期從事基礎架構與中介軟體領域研發工作。現任職於 OceanBase 工具團隊，負責基礎元件&工具的研發工作，致力於為 OceanBase 建設一套完善的生態工具體系。在Java、分散式、基礎架構、中介軟體、“去O”等領域有著較為豐富的研發實戰經驗。

 

引言

在企業級的應用系統中，不是所有的業務都是使用線上遷移/同步工具處理資料。 有些業務使用匯出工具將資料庫中的資料匯出，再匯入到其它庫中供業務分析；有些業務使用匯出工具將資料庫中的資料匯出，檔案傳遞給其它系統進行處理....包括定期邏輯備份恢復，日常的離線遷移等操作，我們都需要將資料庫中的模式或者資料匯出到檔案中。我們只有準確地理解不同的資料格式的規範，熟練地使用匯入匯出工具，才能在日常的工作中更好地運用這些資料。

本文主要是結合 OceanBase 開源的匯入匯出工具，講述常用的資料格式、最佳實踐、常見案例和一些注意事項。

正文

OBLOADER 是一款使用 Java 開發的客戶端匯入工具，提供向 OceanBase 資料庫匯入結構和資料的能力。該工具提供了非常靈活的命令列選項，您可以在多種複雜的場景下，將結構和資料匯入到 OceanBase 資料庫中。OBLOADER 主要與 OBDUMPER 搭配使用。但是在外部業務中，OBLOADER 也支援如 Navicat、Mydumper 和 SQLDeveloper 等工具匯出的 SQL 或 CSV 格式的檔案匯入。OBLOADER 充分利用 OceanBase 分散式系統的特性，重點最佳化了匯入的效能。 

 

檔案大小限制和建議

強烈建議您不要直接使用太大的檔案，例如 100 GB 或更大的資料檔案。如果您使用的導數工具（如 mysqldump 和 SQLDeveloper 等）無法按照檔案塊匯出時，我們可以尋找第三方的切分工具對大檔案進行切分。下述介紹了在不同系統下如何進行資料切分。

 

Linux or macOSX

使用系統內建的切分命令  split  就可以把大的 CSV 檔案切分成若干小的檔案。

split [-a suffix_length] [-bbyte_count[k|m]] [-l line_count] [-p pattern] [file [name]]

示例語句如下所示：

split -l 100000 pagecounts-20210723.csvpages

上述示例是將資料檔案  pagecounts-20210723.csv 根據行數進行切分。假設當前檔案大小是 8 GB，且包含 1000 萬行資料，以 100,000 行作為條件，將大檔案切分成 100 份子檔案，每一份子檔案的名稱以 pages作為字尾且大小將是80MB（1,000,000/100,000=100）。

 

注意：

split  命令是根據行數切分檔案，資料存在換行時可能會出現切分錯誤。

 

Windows

Windows 平臺沒有系統內建的切分工具。但是在網上您可以搜尋許多第三方的切分工具和指令碼用來切分大檔案。

說明：

1.使用 OBDUMPER 匯出資料時，為了避免將一張表中所有的資料寫入單個檔案從而導致在磁碟中生成超大檔案的情況，程式預設會限制檔案的大小是 1 GB。如果匯出的資料體積超出 1 GB，程式會生成一份新的檔案並繼續寫入。

2.使用 OBLOADER 匯入資料時，為了提升匯入效能，程式內部預設會對檔案進行邏輯拆分。需要注意的是，這裡只是邏輯拆分，過程中不會產生任何臨時檔案。即使遇到大檔案（100 GB 以上），程式也可以在很短的時間內完成檔案拆分，從而並行解析每一份子檔案，然後併發匯入到資料庫中。因此，您無需啟動多個 OBLOADER 程式進行併發匯入的操作。

準確地辨識資料格式

在離線遷移和邏輯備份等場景中 OBDUMPER 支援三種通用的資料格式，包括 CSV、Insert 和 Delimited Text。檔案格式與檔案字尾名不是同一個概念。檔案格式，通常是指檔案中的內容的組織形式。例如 123.csv 檔案通常存放的是 CSV 格式的資料，檔案使用 .csv 作為字尾名是為了增加辨識度。您也可以在 123.txt 檔案中存放 CSV 格式的資料，它並不影響軟體程式解析該資料檔案，只是您可能會難以透過檔名辨識檔案中的內容。為了準確地確認資料格式，最快速的辦法就是預覽部分資料內容。這一節，我們主要教大家如何辨識這些資料格式，避免日常操作中頻犯錯誤。

 

CSV 格式

CSV 格式是業界最常見的、最通用的資料儲存格式，也是我們推薦使用的資料格式。除了基本的格式規範（參照： RFC4180 <點選閱讀原文檢視> ）以外，需要特別注意以下 2 個容易犯錯的地方。

1、資料中含有某種特殊字元。CSV 格式的基本符號有定界符、列分隔符和行分隔符。如果資料中存在上述符號或者 NULL 值，匯出的命令中必須要設定轉義符引數，否則生成的 CSV 檔案是無法被正確地解析。

2、CSV 格式與 Excel 格式的區別。CSV 是文字格式，可以用任何編輯器開啟預覽。Excel 格式是二進位制格式，只能使用 Microsoft Excel 軟體開啟。由於 Excel 軟體可以解析 CSV 格式的資料，所以不少使用者習慣用 Excel 軟體預覽 CSV 檔案的內容，但是這要求您熟知 CSV 格式的靈活性以及對於 Excel 軟體有較高的熟練度。例如 Excel 軟體解析 CSV 檔案時，其預設定界符是雙引號，列分隔符是逗號，轉義符是雙引號。假設開啟的 CSV 檔案中沒有使用 Excel 軟體預設的符號，結果預覽的就是錯誤的內容。另外，Windows 版本的 Excel 軟體支援配置 CSV 格式，而 MacOS 版本的 Excel 軟體不支援該配置。

下述為 CSV 的格式樣例：

CSV Header 是指 CSV 檔案中第一行存放的是表中的列名，使用者可以選擇不插入列頭。（可選）CSV Record 是指 CSV 檔案中存放的是表中的資料。資料中含有的定界符、分隔符、換行符和 NULL 等需要轉義。

Insert 格式

Insert 格式就是我們最熟悉的 Insert SQL 語句，為了確保 SQL 語法的正確性，您必須設定正確的轉義符號。

Insert SQL 語句中一些容易造成語法錯誤的原因：

1.物件名或者列名是 SQL 關鍵字。該問題會導致 SQL 語句執行失敗，解決辦法是生成 Insert SQL 語句時，程式邏輯中給物件名或者列名加上轉義符號。例如，Oracle 語法使用雙引號轉義（"），MySQL 語法使用反引號（`）轉義。

2.業務資料中存在單引號。該問題會造成 Insert SQL 解析錯誤，解決辦法是生成 Insert SQL 語句時，程式邏輯中給資料做特殊字元的轉義替換。使用 OBDUMPER 工具匯出資料時，預設會對資料做轉義處理，所以最終可以保證生成的是語法正確的 Insert SQL 語句。

下述為 Insert 格式樣例：

Delimited Tex t 格式

Delimited Text 格式是最簡單的文字格式，也是最容易與 CSV 格式混淆的格式。CSV 格式的分隔符只能是單個字元，預設是逗號。Delimited Text 格式的分隔符可以是單個字元，也可以是多個字元。Delimited Text 格式的基本符號是列分隔符和換行符，沒有定界符。資料中不能出現與基本符號相沖突的字元，否則程式無法正確地解析。例如，Delimited Text 格式要求一行資料在檔案中獨佔一行，假設資料中存在換行，最終解析出來的就是兩行資料。

下述為 Delimited Text 格式樣例：

 

半結構化資料的處理

通常我們所遇見的半結構化資料格式是 JSON 或 XML 資料型別。這兩種資料型別自身也是一種複合的資料定義格式。比如，在 JSON 或者 XML 資料節點中可以存放 CSV 或者 Insert SQL 語句等任意格式的資料。所以您在準備資料的時候，一定要做好資料的定界和轉義。目前檔案中存在該型別的資料是無法被正確切分的，所以使用 OBLOADER 匯入檔案中包含 JSON 或者 XML 型別的資料時，可以調整檔案拆分的閾值引數，讓程式跳過對檔案的自動拆分。

 

二進位制大物件的處理

像RAW、BINARY 和LOB（Large ObjectBinary）等二進位制大物件型別在企業級的業務中的應用並不少見，有些資料庫匯入匯出工具並不支援處理這些資料型別。 強烈要求您將二進位制型別的資料編碼成 16 進位制的字串才能進行檔案儲存和解析， OBDUMPER 和 MySQLDump 等工具預設也是這麼處理的。極端場景下，LOB 型別可能儲存數百 MB 甚至若干 GB 的資料，這為匯入匯出工具的效能和儲存空間兩個方面帶來巨大的挑戰。您在日常的操作中，可以儘量將存在二進位制大物件資料型別的表單獨進行處理。

 

日期時間型別的處理

在 Oracle 或者 OceanBaseOracle 模式下，日期時間相關的資料型別是比較複雜的，而且在使用的過程中也是極其容易犯錯。比如精度、格式和時區錯誤等。

源庫匯出資料前，要求設定符合預期的日期時間格式，目標庫匯入前，也要設定符合預期的日期時間格式。假設源庫和目標庫的日期時間格式不一致，日期時間型別的資料是無法被正確地匯入。在日常操作中，這種問題是很常見的。其次，不同資料庫的日期時間型別存在差異。例如，DATE 型別在 MySQL、DB2、Oracle 和 OceanBase 等各個不同的資料庫中所表示的日期時間資訊是有差異的。而這些差異，您只能自行查閱資料庫文件來解決相容性的問題，否則可能會出現精度丟失或者寫入失敗等問題。

您可以透過下述引數設定日期時間資料的格式：

NLS_DATE_FORMAT              YYYY-MM-DD

NLS_TIMESTAMP_FORMAT         YYYY-MM-DD HH24:MI:SSXFF AM

NLS_TIMESTAMP_TZ_FORMAT      YYYY-MM-DD HH24:MI:SSXFF AM TZR

 

寫在最後

從資料庫中匯出資料時，只要您仔細根據上述的指導建議，並進行檢查和設定。那麼無論使用哪一種導數工具，它都能夠產生符合要求的資料檔案。同樣，您也能夠輕鬆地運用好這些資料檔案以便於後續的匯入工作。切記不要圖省事兒！