PolarDB：通過OSS外表訪問OSS資料

技術原理

通過OSS外表，您可以將CSV格式的查詢頻度低的資料（稱為冷資料）儲存到OSS引擎上，並對冷資料進行查詢和分析。具體原理如下：

CSV格式的資料支援的資料類型包括數實值型別、日期和時間類型、字串類型以及NULL值。具體如下：

• 數實值型別

  類型	大小	資料範圍（有符號）	資料範圍（無符號）	說明
  TINYINT	1 Byte	-128~127	0~255	小整數值
  SMALLINT	2 Bytes	-32768~32767	0~65535	大整數值
  MEDIUMINT	3 Bytes	-8388608~8388607	0~16777215	大整數值
  INT或INTEGER	4 Bytes	-2147483648~2147483647	0~4294967295	大整數值
  BIGINT	8 Bytes	-9,223,372,036,854,775,808~9223372036854775807	0~18446744073709551615	極大整數值
  FLOAT	4 Bytes	-3.402823466 E+38~-1.175494351E-38；0；1.175494351E-38~3.402823466351E+38	0；1.175494351E-38~3.402823466E+38	單精確度浮點數值
  DOUBLE	8 Bytes	-2.2250738585072014E-308~-1.7976931348623157E+308；0；1.7976931348623157E+308~2.2250738585072014E-308	0；1.7976931348623157E+308~2.2250738585072014E-308	雙精確度浮點數值
  DECIMAL	對於DECIMAL(M,D) ，如果M>D，為M+2；否則為D+2	依賴於M和D的值	依賴於M和D的值	小數值

• 日期和時間類型

  類型	大小	資料範圍	資料格式	說明
  DATE	3 Bytes	1000-01-01~9999-12-31	YYYY-MM-DD	日期值
  TIME	3 Bytes	-838:59:59~838:59:59	HH:MM:SS	時間值或期間
  YEAR	1 Byte	1901~2155	YYYY	年份值
  DATETIME	8 Bytes	1000-01-01 00:00:00~9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和時間值 說明 該類型中的月份和日期必須是兩位元。例如，2020年1月1日要寫成2020-01-01 ，而不能寫成2020-1-1，否則該查詢下推到OSS後無法被正確執行。
  TIMESTAMP	4 Bytes	1970-01-01 00:00:00~2038-01-19 03:14:07	YYYY-MM-DD HH:MM:SS	時間戳記（混合日期和時間值） 說明 該類型中的月份和日期必須是兩位元。例如，2020年1月1日要寫成2020-01-01 ，而不能寫成2020-1-1，否則該查詢下推到OSS後無法被正確執行。

• 字串類型

  類型	大小	說明
  CHAR	0~255 Bytes	定長字串
  VARCHAR	0~65535 Bytes	變長字串
  TINYBLOB	0~255 Bytes	不超過255個字元的二進位字串
  TINYTEXT	0~255 Bytes	短文本字串
  BLOB	0~65535 Bytes	二進位形式的長文本資料
  TEXT	0~65535 Bytes	長文本資料
  MEDIUMBLOB	0~16777215 Bytes	二進位形式的中等長度文本資料
  MEDIUMTEXT	0~16777215 Bytes	中等長度文本資料
  LONGBLOB	0~4294967295 Bytes	二進位形式的極大文本資料
  LONGTEXT	0~4294967295 Bytes	極大文本資料

• NULL值

  • 插入NULL值。

    • 在OSS外表中插入NULL值。

      如果在OSS外表中插入NULL值，則需要在建表時指明對應的NULL值標記，即NULL_MARKER。OSS外表的NULL_MARKER值預設為NULL，您可以通過show create table語句來查看NULL值標記：

      show create table t1;

      查詢結果如下：

      show create table t1;
      +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Table | Create Table                                                                                                                                                                      |
      +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | t1    | CREATE TABLE `t1` (
        `id` int(11) DEFAULT NULL
      ) ENGINE=CSV DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!99990 800020204 NULL_MARKER='NULL' */ CONNECTION='server_name' |
      +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      1 row in set (0.00 sec)

    • 在CSV格式的檔案中插入NULL值。

      如果在CSV格式的檔案中需要在某一欄位對應的位置插入NULL_MARKER，且NULL_MARKER兩端不添加雙引號，則PolarDB會把該值識別為NULL。

      說明

      • 當您在NULL_MARKER兩端添加雙引號，則PolarDB會識別為字串，通過is_null語句無法查出NULL值，且如果CSV檔案中被賦予NULL值的參數與OSS外表中對應的參數類型不符，則會報錯。

      • NULL_MARKER不能設定為純數字，也不能設定為空白，且不能含有以下四種字元：

        "、\n、\r和,

      樣本：建立OSS外表的建表語句如下：

      CREATE TABLE `t1` (
        `id` int(11) DEFAULT NULL,
        `name` varchar(20) DEFAULT NULL,
        `time` timestamp NULL DEFAULT NULL
      ) ENGINE=CSV NULL_MARKER='NULL' CONNECTION='server_name';

      假設對應的資料檔案內容如下：

      1,"xiaohong","2022-01-01 00:00:00"
      NULL,"xiaoming","2022-02-01 00:00:00"
      3,NULL,"2022-03-01 00:00:00"
      4,"xiaowang",NULL

      則通過OSS外表查詢的OSS資料如下：

      select * from t1;
      +------+----------+---------------------+
      | id   | name     | time                |
      +------+----------+---------------------+
      |    1 | xiaohong | 2022-01-01 00:00:00 |
      | NULL | xiaoming | 2022-02-01 00:00:00 |
      |    3 | NULL     | 2022-03-01 00:00:00 |
      |    4 | xiaowang | NULL                |
      +------+----------+---------------------+
      4 rows in set (0.00 sec)

  • 讀取NULL值。

    • 從CSV格式的資料檔案中讀取資料時，如果CSV中的值為NULL，且OSS外表中對應的值可以為NULL時，則當前欄位直接設定為NULL。

    • 從CSV格式的資料檔案中讀取資料時，如果CSV中的值為NULL，但OSS外表中對應的值設定為NOT NULL時，即CSV中的資料內容與OSS外表中定義的內容衝突。則會根據您設定的文法校正規則返回不同的結果。

      • 當您將文法校正規則sql_mode設定為STRICT_TRANS_TABLES時，則會報錯。

      • 當您將文法校正規則sql_mode設定為除STRICT_TRANS_TABLES之外的其他模式時，如果當前欄位有預設值，則當前欄位的值會設定為預設值。如果沒有預設值，則當前欄位會根據欄位類型被賦予MySQL的預設值，詳情請參見資料類型預設值。且會有warning提示，您可以通過show warnings;命令查看warning提示詳細資料。

      說明

      您可以通過show variables like "sql_mode";命令查看當前的文法校正規則。且可以在控制台的參數配置中通過修改sql_mode參數的值來修改當前的文法校正規則，具體請參見修改參數值。

      樣本：建立一張OSS外表t，將id欄位設定為NOT NULL，並且沒有預設值。

      CREATE TABLE `t` (
        `id` int(11) NOT NULL
      ) ENGINE=CSV 
      CONNECTION="server_name";

      假設CSV格式的資料檔案t.CSV中的內容為：

      NULL
      2

      通過OSS外表讀取CSV格式檔案中的資料會有以下兩種情況：

      • 當sql_mode設定為STRICT_TRANS_TABLES時，執行如下命令，查詢CSV格式檔案中的資料：

        select * from t;

        報錯資訊如下：

        ERROR 1364 (HY000): Field 'id' doesn't have a default value

      • 當sql_mode設定為除STRICT_TRANS_TABLES之外的模式時，執行如下命令，查詢CSV格式檔案中的資料：

        select * from t;

        查詢結果如下：

        +----+
        | id |
        +----+
        |  0 |
        |  2 |
        +----+
        2 rows in set, 1 warning (0.00 sec)

        其中，0為MySQL預設值。

        執行以下命令，查看warning提示資訊：

        show warnings;

        查詢結果如下：

        +---------+------+-----------------------------------------+
        | Level   | Code | Message                                 |
        +---------+------+-----------------------------------------+
        | Warning | 1364 | Field 'id' doesn't have a default value |
        +---------+------+-----------------------------------------+
        1 row in set (0.00 sec)

使用限制

• 目前通過OSS外表僅支援查詢CSV格式的資料。

• 目前針對OSS外表的語句只支援CREATE、SELECT、DROP三種。

  說明

  DROP操作不會刪除OSS上的資料檔案，僅刪除PolarDB上的表資訊。

• OSS外表目前不支援索引、分區和事務。

參數說明

您可以在控制台的參數配置頁面查看或修改以下參數：

根據以上兩個參數可以計算出OSS功能佔用的總記憶體最大為：loose_csv_max_oss_threads * loose_csv_oss_buff_size

操作步驟

1. 上傳CSV格式的資料至OSS。

   您可以通過命令列工具ossutil將本地CSV格式的資料上傳到遠程OSS引擎上。

   說明

   • 上傳CSV檔案的OSS目錄需要與OSS server中DATABASE或oss_prefix的目錄保持一致。

   • 上傳的CSV檔案名稱需要設定為外表名.CSV，且檔案名稱尾碼CSV必須是大寫格式。例如，建立的OSS外表為t1，則上傳的CSV檔案名稱需要設定為t1.CSV。

   • CSV檔案中的資料欄位與OSS外表欄位需要匹配。例如：建立的OSS外表 t1表中只有一個欄位id，類型為INT。則上傳的CSV檔案中也只能有一個INT類型的欄位。

   • 建議您直接上傳本地MySQL的資料檔案，並依據表定義建立對應的OSS外表。

2. 添加OSS串連資訊。

   您可以通過建立OSS server來添加OSS串連資訊。

   說明

   通過其他方式串連OSS的功能由於存在安全風險已經被禁用。目前僅支援通過建立OSS server的方式來添加OSS串連資訊，並與OSS建立串連。

   • 若PolarDB MySQL版為8.0.1版本且修訂版本為8.0.1.1.28及以上。或PolarDB MySQL版為8.0.2版本且修訂版本為8.0.2.2.5及以上時，文法如下：

     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。添加DATABASE參數的方法請參見下文中的內容。

     參數說明如下表所示：

     參數名稱	參數類型	是否必填	參數說明
     server_name	字串	是	OSS server名稱。 說明 該參數為全域參數，且全域唯一。該參數不區分大小寫，最大長度不超過64個字元，超過64個字元的名稱會被自動截斷。您可以將OSS server名稱指定為帶引號的字串。
     my_oss_endpoint	字串	是	OSS對應地區的網域名稱。 說明 如果是從阿里雲的主機訪問資料庫，應該使用內網網域名稱（即帶有“internal”的網域名稱），避免產生公網流量。 例如：華東1（杭州）OSS節點的內網網域名稱：oss-cn-xxx-internal.aliyuncs.com
     my_oss_bucket	字串	是	資料檔案所在OSS的bucket，需要通過OSS預先建立。 說明 OSS的bucket和PolarDB最好在同一個可用性區域內，以減少兩者之間的網路延遲。
     my_oss_access_key_id	字串	是	RAM使用者或阿里雲帳號的AccessKey ID。 如何建立AccessKey請參見建立AccessKey。
     my_oss_access_key_secret	字串	是	RAM使用者或阿里雲帳號的AccessKey Secret。 如何建立AccessKey請參見建立AccessKey。
     my_oss_prefix	字串	否	當前CSV資料檔案在OSS中的目錄。
     my_oss_sts_token	字串	否	STS臨時訪問憑證。 說明 當使用STS臨時訪問憑證訪問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許可權。目前，高許可權賬戶預設具有該許可權，並且高許可權賬戶可以給低許可權賬戶賦予該許可權。

     • 如果您當前沒有SERVERS_ADMIN許可權，會提示錯誤資訊：Access denied; you need (at least one of) the SERVERS_ADMIN OR SUPER privilege(s) for this operation。

     • 如果您是普通賬戶沒有SERVERS_ADMIN許可權，可以使用高許可權賬戶執行：GRANT SERVERS_ADMIN ON *.* TO `users`@`%` WITH GRANT OPTION；如果您是高許可權賬戶沒有SERVERS_ADMIN許可權，您可以點擊叢集列表找到所需要設定的叢集ID/名稱>配置與管理>帳號管理>重設許可權以重設許可權，等待一段時間後，再查看高許可權賬戶，此時就有SERVERS_ADMIN許可權了。

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

   • 若PolarDB MySQL版為8.0.1版本且修訂版本在8.0.1.1.25.4至8.0.1.1.28之間，或PolarDB MySQL版為8.0.2版本且修訂版本在8.0.2.2.1至8.0.2.2.5之間時，文法如下：

     CREATE SERVER <server_name>
     FOREIGN DATA WRAPPER oss OPTIONS
     (DATABASE '<my_database_name>',
       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參數。

     參數說明如下表所示：

     參數名稱	參數類型	是否必填	參數說明
     server_name	字串	是	OSS server名稱。 說明 該參數為全域參數，且全域唯一。該參數不區分大小寫，最大長度不超過64個字元，超過64個字元的名稱會被自動截斷。您可以將OSS server名稱指定為帶引號的字串。
     my_database_name	字串	否	當前CSV資料檔案在OSS中的目錄名稱。
     my_oss_endpoint	字串	是	OSS對應地區的網域名稱。 說明 如果是從阿里雲的主機訪問資料庫，應該使用內網網域名稱（即帶有“internal”的網域名稱），避免產生公網流量。 例如：oss-cn-xxx-internal.aliyuncs.com
     my_oss_bucket	字串	是	資料檔案所在OSS的bucket，需要通過OSS預先建立。
     my_oss_access_key_id	字串	是	RAM使用者或阿里雲帳號的AccessKey ID。 如何建立AccessKey請參見建立AccessKey。
     my_oss_access_key_secret	字串	是	RAM使用者或阿里雲帳號的AccessKey Secret。 如何建立AccessKey請參見建立AccessKey。

3. 建立OSS外表。

   定義了OSS Server之後，您需要在PolarDB上建立OSS外表，與OSS建立串連。樣本如下：

   create table t1 (id int) engine=csv connection="connection_string";

   其中，connection_string由以下內容組成，且使用“/”來進行串連：

   • OSS Server名稱。

   • （可選）OSS上的資料檔案路徑。

     說明

     當PolarDB MySQL版為8.0.1版本且修訂版本為8.0.1.1.28及以上。或PolarDB MySQL版為8.0.2版本且修訂版本為8.0.2.2.5及以上時，支援配置OSS上的資料檔案路徑。

   • （可選）資料檔案名稱。

     說明

     資料檔案名稱後面不能有 .CSV尾碼。

   樣本：

   create table t1 (id int) engine=csv connection="server_name/a/b/c/d/t1";

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

   說明

   • 您可以只使用資料檔案名稱來指定OSS外表對應的資料檔案。例如：create table t1 (id int) engine=csv connection="server_name/t2"，則PolarDB會在OSS上的oss_prefix路徑下尋找t2.CSV的檔案。

   • 如果您在connection_string中添加了OSS上的資料檔案路徑，則您必須添加對應的資料檔案名稱。否則，尋找對應的檔案時會將路徑的最後一段識別為檔案名稱。

   • 如果不指定資料檔案名稱，則當前表對應的OSS檔案為當前表名.CSV；如果指定資料檔案名稱，則當前表對應的OSS檔案為指定的資料檔案名稱.CSV。

   OSS外表建立完成後，您可以通過show create table命令查看已建立的表。請檢查已建立的表的引擎是否為CSV，如果不是，可能是您當前的PolarDB版本過低，不支援OSS引擎。

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修改後仍然報錯，請聯絡阿里雲支援人員解決。

查詢最佳化

OSS error: The current query does not support engine condition pushdown. You need to use NO_ECP() hint or set optimizer_switch = 'engine_condition_pushdown=OFF' to turn off the condition push down function.

多節點之間同步OSS server資訊

目前，PolarDB叢集的主節點和唯讀節點共用一個OSS server，以保證在兩個節點上都可以訪問OSS上的資料。且兩個節點間OSS Server資訊同步是無鎖的，以保證在兩個節點上的操作不會互相影響。

當您修改OSS Server資訊後，修改內容會無鎖地同步到唯讀節點，如果唯讀節點上有線程持有OSS Server的鎖，則可能會導致OSS server資訊同步時間延遲。此時，您可以通過執行/*force_node='pi-bpxxxxxxxx'*/ flush privileges; 或/*force_node='pi-bpxxxxxxxx'*/flush table oss_foreign_table;命令來手動更新唯讀節點的OSS server資訊。