PolarDB：基於OSS外表的單表多檔案查詢

操作步驟

1. 拆分CSV檔案。

   1. 您可以將CSV檔案按行拆分為多個小的CSV檔案，且單個CSV檔案大小建議為128 MB，最大限制為1 GB。

      說明

      拆分CSV格式的檔案時，必須按照完整的一行資料進行拆分，不能從一行資料中間進行拆分，需要保證每一個OSS資料檔案的完整性。

      檔案命名規則如下：

      • 使用OSS外表的建表語句CONNECTION參數中配置的檔案名稱。如果該參數中沒有設定檔名稱，則資料檔案名稱為當前OSS外表的表名.CSV。樣本如下：

        • CONNECTION參數中已設定檔名稱。

          CREATE TABLE t1 (id int) engine=csv CONNECTION="server_name/a/b/c/d/t1";

          通過樣本可以看出：OSS上的資料檔案路徑為oss_prefix/a/b/c/d/，資料檔案名稱為t1.CSV。

        • CONNECTION參數中沒有設定檔名稱。

          CREATE TABLE t1 (id int) engine=csv CONNECTION="server_name";

          則資料檔案名稱為t1.CSV。

      • 需要拆分的資料檔案名稱 - 任一數字.CSV。樣本如下：

        假設需要拆分的資料檔案名稱為t1.CSV，則拆分後的檔案名稱為 t1.CSV、t1-1.CSV和t1-2.CSV等。

   2. 上傳資料檔案。

      檔案拆分後，您需要手動將所有的CSV檔案上傳到OSS上的同一路徑下。此處以使用ossutil命令列工具批量上傳CSV檔案為例，關於ossutil命令列工具更多內容請參見ossutil。

      ./ossutil64 cp localfolder/ oss://examplebucket/desfolder/ --include "*.CSV" -r

      其中，localfolder為待上傳的CSV檔案的檔案夾名稱，oss://examplebucket/desfolder/為OSS上的CSV檔案路徑。使用過程中請根據實際使用情境進行替換。

2. 添加OSS串連資訊。

   您可以通過建立OSS server來添加OSS串連資訊。文法如下：

   CREATE SERVER <server_name> 
   FOREIGN DATA WRAPPER oss OPTIONS 
   (   
   EXTRA_SERVER_INFO '{"oss_endpoint": "<my_oss_endpoint>","oss_bucket": "<my_oss_bucket>","oss_access_key_id": "<my_oss_access_key_id>","oss_access_key_secret": "<my_oss_access_key_secret>","oss_prefix":"<my_oss_prefix>","oss_sts_token":"<my_oss_sts_token>"}'
   );

   說明

   • PolarDB MySQL版為8.0.1版本且修訂版本為8.0.1.1.29及以上，或PolarDB MySQL版為8.0.2版本且修訂版本為8.0.2.2.6及以上時，支援使用my_oss_sts_token參數。

   • 該文法支援DATABASE參數，若您建立的OSS server中既存在DATABASE參數，又存在my_oss_prefix參數，則最終尋找檔案的路徑為my_oss_prefix/DATABASE。請保證在該文法中配置的資料檔案路徑與拆分檔案中上傳到OSS上的資料檔案路徑一致。

   參數說明如下表所示：

   參數名稱	參數類型	參數說明
   server_name	字串	OSS server名稱。 說明 該參數為全域參數，且全域唯一。該參數不區分大小寫，最大長度不超過64個字元，超過64個字元的名稱會被自動截斷。您可以將OSS server名稱指定為帶引號的字串。
   my_oss_endpoint	字串	OSS對應地區的網域名稱。 說明 如果是從阿里雲的主機訪問資料庫，應該使用內網網域名稱（即帶有“internal”的網域名稱），避免產生公網流量。 例如：oss-cn-xxx-internal.aliyuncs.com
   my_oss_bucket	字串	資料檔案所在OSS的bucket，需要通過OSS預先建立。 說明 OSS的bucket和PolarDB最好在同一個可用性區域內，以減少兩者之間的網路延遲。
   my_oss_access_key_id	字串	OSS帳號的AccessKey ID。
   my_oss_access_key_secret	字串	OSS帳號的AccessKey Secret。
   my_oss_prefix	字串	當前CSV格式的資料檔案在OSS中的路徑。
   my_oss_sts_token	字串	OSS臨時訪問憑證。擷取OSS臨時訪問憑證詳情請參見擷取臨時訪問憑證。 說明 my_oss_sts_token參數值有預設的到期時間。如果my_oss_sts_token已到期，您需要通過以下命令重設EXTRA_SERVER_INFO中的全部參數值。 ALTER SERVER server_name OPTIONS(EXTRA_SERVER_INFO '{"oss_endpoint": "<my_oss_endpoint>", "oss_bucket": "<my_oss_bucket>", "oss_access_key_id": "<my_oss_access_key_id>", "oss_access_key_secret": "<my_oss_access_key_secret>", "oss_prefix":"<my_oss_prefix>", "oss_sts_token": "<my_oss_sts_token>"}');

   說明

   • 建立OSS server時需要SERVERS_ADMIN許可權，您可以通過show grants for 目前使用者命令查看目前使用者是否具有SERVERS_ADMIN許可權。目前，高許可權賬戶預設具有該許可權，並且高許可權賬戶可以給低許可權賬戶賦予該許可權。

   • 如果您是高許可權使用者，可以通過SELECT Server_name, Extra_server_info FROM mysql.servers;命令查看您建立的OSS Server資訊，且oss_access_key_id和oss_access_key_secret參數資訊因為涉及安全資訊會被加密處理，無法查看其詳細資料。

3. 建立OSS外表，具體請參見建立OSS外表。外表建立成功後，PolarDB會根據您設定的路徑，尋找對應的檔案。

4. 資料查詢。

   以上述步驟樣本中的t1表為例進行說明。

   #查詢t1表內的資料數量
   SELECT count(*) FROM t1;
   
   #範圍查詢
   SELECT id FROM t1 WHERE id < 10 AND id > 1;
   
   #點查
   SELECT id FROM t1 where id = 3;
   
   #多表join
   SELECT id FROM t1 left join t2 on t1.id = t2.id WHERE t2.name like "%er%";

   查詢資料的過程中，常見的報錯資訊及報錯原因請參見下表：

   說明

   如果在查詢資料的過程中，沒有報錯資訊但有警告資訊時，您需要通過SHOW WARNINGS;命令查看報錯資訊。

   報錯資訊	報錯原因	解決方案
   OSS error: No corresponding data file on the OSS engine.	OSS上沒有找到對應的資料檔案。	您需要根據上述規則檢查OSS上對應的路徑下是否存在資料檔案。 若存在，確認資料檔案格式是否符合命名規則。即符合外表名.CSV，且檔案名稱尾碼CSV必須為大寫格式。 若不存在，則需要將資料檔案上傳至目標路徑。
   There is not enough memory space for OSS transmission. Currently requested memory %d.	沒有足夠的空間進行OSS查詢。	您可以通過以下兩種方式中的任意一種來修複該錯誤： 在控制台的參數配置中通過修改loose_csv_max_oss_threads參數值來運行更多的OSS線程。 通過flush table關閉某些OSS表的線程。
   ERROR 8054 (HY000): OSS error: error message : Couldn't connect to server.Failed connect to aliyun-mysql-oss.oss-cn-hangzhou-internal.aliyuncs.com:80;	當前的資料庫執行個體無法串連OSS伺服器。	檢查當前的資料庫執行個體與OSS bucket是否在同一個可用性區域。 如果不在同一個可用性區域，則需要將當前的資料庫執行個體與OSS bucket放在同一個可用性區域。 如果在同一個可用性區域，您可以將endpoint修改為公網的endpoint。如果endpoint修改後仍然報錯，請聯絡阿里雲支援人員解決。

5. （可選）增加新的資料檔案。

   如果您需要上傳新的資料檔案，並在t1表中讀取該檔案中的資料，您可以執行步驟1上傳目標檔案，上傳完成後，對t1表執行以下操作，即可使用查詢命令查詢新上傳檔案中的資料。

   FLUSH TABLE t1;

查詢最佳化

建立了多檔案的OSS外表之後，PolarDB會預先掃描OSS上當前表的資料檔案，得到總行數的估計值，基於代價啟動一定數量的worker，將多個OSS資料檔案分給不同的worker進行並行掃描，以此來加快掃描速度。其原理圖如下所示：

開啟OSS多檔案並行掃描

您可以通過將參數loose_csv_max_oss_threads的值設定為大於1的整數，來開啟OSS多檔案並行掃描功能。關於參數loose_csv_max_oss_threads的更多詳情請參見參數說明，設定參數的操作步驟請參見設定叢集參數和節點參數。