PolarDB:Use OSS foreign tables to access OSS data

How it works

You can use OSS foreign tables to store CSV-formatted cold data in an OSS bucket for query and analysis. Cold data refers to data that is infrequently accessed. The following figure shows the process.

CSV-formatted data can contain numeric, date and time, and string values, as well as NULL values. The following tables describe the supported data types.

• Numeric types

  Data type	Size	Data range (signed)	Data range (unsigned)	Description
  TINYINT	1 Byte	-128~127	0~255	A small integer value
  SMALLINT	2 bytes	-32768~32767	0~65535	An integer value
  MEDIUMINT	3 bytes	-8388608~8388607	0~16777215	An integer value
  INT or INTEGER	4 bytes	-2147483648~2147483647	0~4294967295	An integer value
  BIGINT	8 bytes	-9,223,372,036,854,775,808~9223372036854775807	0~18446744073709551615	A big integer value
  FLOAT	4 bytes	-3.402823466 E+38~-1.175494351E-38; 0; 1.175494351E-38~3.402823466351E+38	0; 1.175494351E-38~3.402823466E+38	A single-precision floating-point value
  DOUBLE	8 bytes	-2.2250738585072014E-308~-1.7976931348623157E+308; 0; 1.7976931348623157E+308~2.2250738585072014E-308	0; 1.7976931348623157E+308~2.2250738585072014E-308	A double-precision floating-point value
  DECIMAL	For DECIMAL(M,D), it is M+2 if M>D. Otherwise, it is D+2.	Depends on the values of M and D.	Depends on the values of M and D.	A decimal value

• Date and time types

  Data type	Size	Data range	Format	Description
  DATE	3 bytes	1000-01-01~9999-12-31	YYYY-MM-DD	A date value
  TIME	3 bytes	-838:59:59~838:59:59	HH:MM:SS	A time value or duration
  YEAR	1 Byte	1901~2155	YYYY	A year value
  DATETIME	8 bytes	1000-01-01 00:00:00~9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	A combined date and time value Note The month and date of this type must have two digits. For example, January 1, 2020 must be written as 2020-01-01, instead of 2020-1-1. The query cannot be executed as expected if 2020-1-1 is pushed down to OSS.
  TIMESTAMP	4 bytes	1970-01-01 00:00:00~2038-01-19 03:14:07	YYYY-MM-DD HH:MM:SS	A timestamp (combined date and time) value Note The month and date of this type must have two digits. For example, January 1, 2020 must be written as 2020-01-01, instead of 2020-1-1. The query cannot be executed as expected if 2020-1-1 is pushed down to OSS.

• String types

  Data type	Size	Description
  CHAR	0~255 bytes	A fixed-length string
  VARCHAR	0~65535 bytes	A variable-length string
  TINYBLOB	0~255 bytes	A small binary large object up to 255 characters
  TINYTEXT	0~255 bytes	A short string
  BLOB	0~65535 bytes	A standard binary large object
  TEXT	0~65535 bytes	A standard string
  MEDIUMBLOB	0~16777215 bytes	A medium binary large object
  MEDIUMTEXT	0~16777215 bytes	A medium string
  LONGBLOB	0~4294967295 bytes	A long binary large object
  LONGTEXT	0~4294967295 bytes	A long string

• NULL values

  • Insert a NULL value.

    • Insert a NULL value into an OSS foreign table.

      To insert a NULL value into an OSS foreign table, you must specify NULL_MARKER when you create the OSS foreign table. The default value of NULL_MARKER is NULL for an OSS foreign table. You can execute the show create table statement to check the value of NULL_MARKER.

      show create table t1;

      Result:

      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)

    • Insert a NULL value into a CSV file.

      If you insert a value of NULL_MARKER for a field in a CSV file without enclosing the value in double quotation marks ("), PolarDB identifies the value as NULL.

      Note

      • If you enclose the value of NULL_MARKER in double quotation marks ("), PolarDB identifies the value as a string. This means that the is_null statement cannot identify the value as NULL. If the data type of a field to which a NULL value is assigned in the CSV file does not match that of the corresponding field in the OSS foreign table, an error is reported.

      • The value of cannot consist solely of digits or be left empty. In addition, it cannot contain the following characters:

        ", \n, \r, and ,

      Example: Execute the following statement to create an OSS foreign table:

      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';

      In this example, the data file contains the following data:

      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

      The following OSS data is obtained if you query the OSS foreign table:

      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)

  • Read a NULL value.

    • When data is read from a CSV file, if the value of a field in the CSV file is NULL and the corresponding value in the OSS foreign table can be set to NULL, the field is directly set to NULL.

    • When data is read from a CSV file, if the value of a field in the CSV file is NULL but the corresponding value in the OSS foreign table is set to NOT NULL, the data in the CSV file conflicts with the data specified in the OSS foreign table. In this case, different results are returned depending on the specified syntax verification rule.

      • If you set the sql_mode parameter to STRICT_TRANS_TABLES, an error is reported.

      • If you set the sql_mode parameter to a value other than STRICT_TRANS_TABLES and the field has a default value, the default value of the field is used. If the field does not have a default value, a MySQL default value is assigned to the field based on the field type. For more information, see Data Type Default Values. If a warning message appears, you can execute the show warnings; statement to view the details of the warning message.

      Note

      You can execute the show variables like "sql_mode"; statement to view the current syntax verification rule. You can also change the syntax verification rule by modifying the value of the sql_mode parameter on the Parameters page in the PolarDB console. For more information, see Configure cluster and node parameters.

      Example: Create an OSS foreign table named t and set the id field to NOT NULL. Do not specify a default value for the field.

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

      In this example, the t.CSV file contains the following data:

      NULL
      2

      When you use an OSS foreign table to read data from the CSV file, one of the following scenarios occurs:

      • If you set the sql_mode parameter to STRICT_TRANS_TABLES and execute the following statement to query data in the CSV file:

        select * from t;

        The following error message is reported:

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

      • If you set the sql_mode parameter to a value other than STRICT_TRANS_TABLES and execute the following statement to query data in the CSV file:

        select * from t;

        The following result is returned:

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

        0 is the default value of MySQL.

        Execute the following statement to view the details of the warning message:

        show warnings;

        Result:

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

Limits

• You can use OSS foreign tables to query only CSV-formatted data.

• You can perform only the following operations on OSS foreign tables: CREATE, SELECT, and DROP.

  Note

  The DROP operation deletes only the table information in PolarDB, without affecting the data files stored in OSS.

• OSS foreign tables do not support indexing, partitioning, or transactions.

Parameters

You can view or modify the following parameters on the Parameters page of the console.

You can calculate the total memory usage of OSS by using the following formula:Total memory usage of OSS = Value of the loose_csv_max_oss_threads parameter × Value of the loose_csv_oss_buff_size parameter.

Procedure

1. Upload a CSV file to OSS.

   You can use the ossutil tool to upload a local CSV file to a remote OSS bucket.

   Note

   • The OSS directory to which the CSV file is uploaded must be the same as the directory specified by the my_database_name or oss_prefix parameter on the OSS server.

   • The name of the uploaded CSV file must be in the Foreign table name.CSV format, and the .CSV extension must be in uppercase. For example, if the name of the created OSS foreign table is t1, the name of the uploaded CSV file must be t1.CSV.

   • The data fields in the CSV file must match the fields of the OSS foreign table. For example, if the created OSS foreign table t1 contains only one field id and the field is of the INT type, the uploaded CSV file must contain only one field of the INT type.

   • We recommend that you directly upload the local MySQL data file and create an OSS foreign table based on the table definitions.

2. Add OSS connection information.

   You can add OSS connection information by creating an OSS server.

   Note

   Other methods to connect to OSS have been disabled due to security risks. You can only create an OSS server to add OSS connection information and connect to OSS.

   • If you use a PolarDB for MySQL 8.0.1 cluster whose revision version is 8.0.1.1.28 or later, or a PolarDB for MySQL 8.0.2 cluster whose revision version is 8.0.2.2.5 or later, you can create an OSS server by using the following syntax:

     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>"}'
     );

     Note

     • On a PolarDB for MySQL 8.0.1 cluster whose revision version is 8.0.1.1.29 or later or a PolarDB for MySQL 8.0.2 cluster whose revision version is 8.0.2.2.6 or later, you can use the my_oss_sts_token parameter.

     • The my_database_name parameter is supported in the syntax. If both the my_database_name and my_oss_prefix parameters exist on the OSS server that you create, the final path of the file is my_oss_prefix/my_database_name. For more information about how to specify the my_database_name parameter, see the following text.

     The following table describes the parameters in the syntax.

     Parameter	Type	Required	Description
     server_name	String	Yes	The OSS server name. Note The name must be globally unique. The name can be up to 64 characters in length and is not case-sensitive. A name that contains more than 64 characters is automatically truncated. You can specify the OSS server name as a quoted string.
     my_oss_endpoint	String	Yes	The endpoint of the OSS server. Note If you access your database from an Alibaba Cloud server, use an internal endpoint to prevent incurring Internet traffic. An internal endpoint contains the keyword "internal". For example, the internal endpoint of an OSS node in the China (Hangzhou) region is oss-cn-xxx-internal.aliyuncs.com.
     my_oss_bucket	String	Yes	The bucket where the data file is stored. You must create the bucket on OSS before you import data. Note We recommend that you deploy the bucket in the same zone as the PolarDB cluster to reduce network latency.
     my_oss_access_key_id	String	Yes	The AccessKey ID of the Resource Access Management (RAM) user or Alibaba Cloud account. For more information about how to create an AccessKey pair, see Create an AccessKey pair.
     my_oss_access_key_secret	String	Yes	The AccessKey secret of the RAM user or Alibaba Cloud account. For more information about how to create an AccessKey pair, see Create an AccessKey pair.
     my_oss_prefix	String	No	The OSS directory for the CSV file.
     my_oss_sts_token	String	No	The temporary access credentials provided by Security Token Service (STS) Note This parameter is required if you use a temporary access credential provided by STS to access OSS. The value of the my_oss_sts_token parameter has a default expiration time. If the value of the my_oss_sts_token parameter expires, execute the following statement to reset all parameters in 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>"}');

     Note

     • The SERVERS_ADMIN permissions are required when you create an OSS server. You can execute the SHOW GRANTS FOR the current user statement to check whether the current user has the SERVERS_ADMIN permissions. A privileged account has the SERVERS_ADMIN permissions by default, and can grant the SERVERS_ADMIN permissions to standard accounts.

     • If you do not have the SERVERS_ADMIN permissions, the error message Access denied; you need (at least one of) the SERVERS_ADMIN OR SUPER privilege(s) for this operation appears.

     • If you use a standard account that does not have the SERVERS_ADMIN permissions, you can use a privileged account to execute the following statement: GRANT SERVERS_ADMIN ON *.* TO `users`@`%` WITH GRANT OPTION. If you use a privileged account that does not have the SERVERS_ADMIN permissions, you can reset the permissions of the account. To do so, find the cluster in the console and click the cluster ID or name to go to the cluster details page. In the left-side navigation pane, choose Settings and Management > Accounts. On the User Account tab, find the privileged account that you want to manage and click Reset Permissions in the Actions column. Wait until the permissions are reset. Then, the privileged account has the SERVERS_ADMIN permissions.

     • If you use a privileged account, you can execute the SELECT Server_name, Extra_server_info FROM mysql.servers; statement to view the information about the OSS server that you create. The values of the oss_access_key_id and oss_access_key_secret parameters are encrypted for security reasons.

   • If you use a PolarDB for MySQL 8.0.1 cluster whose revision version is from 8.0.1.1.25.4 to 8.0.1.1.28, or a PolarDB for MySQL 8.0.2 cluster whose revision version is from 8.0.2.2.1 to 8.0.2.2.5, you can create an OSS server by using the following syntax:

     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>"}'
     );                  

     Note

     The oss_prefix parameter is not supported in the syntax.

     The following table describes the parameters in the syntax.

     Parameter	Type	Required	Description
     server_name	String	Yes	The OSS server name. Note The name must be globally unique. The name can be up to 64 characters in length and is not case-sensitive. A name that contains more than 64 characters is automatically truncated. You can specify the OSS server name as a quoted string.
     my_database_name	String	No	The name of the OSS directory for the CSV file.
     my_oss_endpoint	String	Yes	The endpoint of the OSS server. Note If you access your database from an Alibaba Cloud server, use an internal endpoint to prevent incurring Internet traffic. An internal endpoint contains the keyword "internal". Example: oss-cn-xxx-internal.aliyuncs.com.
     my_oss_bucket	String	Yes	The bucket where the data file is stored. You must create the bucket on OSS before you import data.
     my_oss_access_key_id	String	Yes	The AccessKey ID of the RAM user or Alibaba Cloud account. For more information about how to create an AccessKey pair, see Create an AccessKey pair.
     my_oss_access_key_secret	String	Yes	The AccessKey secret of the RAM user or Alibaba Cloud account. For more information about how to create an AccessKey pair, see Create an AccessKey pair.

3. Create an OSS foreign table.

   After you define an OSS server, you can create an OSS foreign table on PolarDB to connect to OSS. Example:

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

   The value of connection_string consists of the following items that are separated by forward slashes (/):

   • The OSS server name.

   • Optional. The path of the data file in OSS.

     Note

     If you use a PolarDB for MySQL 8.0.1 cluster whose revision version is 8.0.1.1.28 or later, or a PolarDB for MySQL 8.0.2 cluster whose revision version is 8.0.2.2.5 or later, you can configure the path of the data file in OSS.

   • Optional. The data file name.

     Note

     The data file name cannot contain the .CSV extension.

   Example:

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

   In this example, the path of the data file in OSS is oss_prefix/a/b/c/d/, and the name of the data file is t1.CSV.

   Note

   • You can use only the data file name to specify the data file that corresponds to the OSS foreign table. For example, if you execute the create table t1 (id int) engine=csv connection="server_name/t2" statement, PolarDB searches for the t2.CSV file in the directory specified by the oss_prefix parameter in OSS.

   • If you add the path of the data file in OSS to connection_string, you must add the name of the data file. Otherwise, the last segment of the path is considered as the file name when PolarDB searches for the data file.

   • If you do not specify a data file name, the OSS file corresponding to the current table is Name of the current table.CSV. If you specify a data file name, the OSS file corresponding to the current table is Specified data file name.CSV.

   After an OSS foreign table is created, you can execute the show create table statement to view the table. Check whether the engine of the created table is CSV. If not, the version of your current PolarDB cluster is outdated and does not support OSS.

4. Query data.

   In this example, the t1 table mentioned in the previous steps is used to show how to query data.

   # Query the number of data records in the t1 table.
   SELECT count(*) FROM t1;
   
   # Query data records in the specified range.
   SELECT id FROM t1 WHERE id < 10 AND id > 1;
   
   # Query a specific record.
   SELECT id FROM t1 where id = 3;
   
   # Query records by joining multiple tables.
   SELECT id FROM t1 left join t2 on t1.id = t2.id WHERE t2.name like "%er%";

   The following table describes the common error messages and causes when you query data.

   Note

   If no error message is reported but a warning message appears when you query data, you must execute the SHOW WARNINGS; statement to view the message.

   Error message	Cause	Solution
   OSS error: No corresponding data file on the OSS engine.	The specified data file is not found in OSS.	Check whether the data file exists in the specified path in OSS based on the preceding rules. If yes, check whether the data file complies with the following naming rules: The data file name is in the Foreign table name.CSV format, and the .CSV extension is in uppercase. If not, you must upload the data file to the specified path.
   There is not enough memory space for OSS transmission. Currently requested memory %d.	Insufficient memory for OSS queries.	You can use one of the following methods to fix this error: On the Parameters page in the console, modify the value of the loose_csv_max_oss_threads parameter to run more OSS threads. Execute the flush table statement to close threads for some OSS tables.
   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;	The current cluster cannot connect to the OSS server.	Check whether the current cluster is in the same zone as the OSS bucket. If not, you must move the current cluster and the OSS bucket to the same zone. If yes, you can change the endpoint of the cluster to a public endpoint. If the error persists after the endpoint is modified, contact Alibaba Cloud technical support.

Query optimization

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.

Synchronize OSS server information between multiple nodes

The primary and read-only nodes of a PolarDB cluster share the same OSS server. This ensures that these nodes can access OSS data. The synchronization of OSS server information between these nodes is lock-free to ensure that operations on these nodes are independent.

After you modify the OSS server information, the modifications are synchronized to read-only nodes in a lock-free manner. If a thread on a read-only node holds the lock for the OSS server, the synchronization of OSS server information may be delayed. In this case, you can execute the /*force_node='pi-bpxxxxxxxx'*/ flush privileges; or /*force_node='pi-bpxxxxxxxx'*/flush table oss_foreign_table; statement to manually update the OSS server information of read-only nodes.