PolarDB:Use OSS foreign tables to access OSS data

You can use OSS foreign tables to store cold data (the data that is queried at low frequency) in the CSV format on OSS and then query and analyze cold data. The following figure shows the process.

CSV-formatted data can be of the numeric, date and time, and string types and set to NULL. The following table describes the supported data types.

• Data types

  Data type	Size	Range (signed)	Range (unsigned)	Description
  TINYINT	1 Byte	-128~127	0~255	A tiny integer value
  SMALLINT	2 Bytes	-32768~32767	0~65535	An integer value
  MEDIUMINT	3 Bytes	-8388608~8388 607	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	Range	Format	Description
  DATE	3 Bytes	1000-01-01~9999-12-31	YYYY-MM-DD	Date values
  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 correctly 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 correctly if 2020-1-1 is pushed down to OSS.

• String types

  Data type	Size	Description
  CHAR	0~255 Bytes	A string value of fixed length
  VARCHAR	0~65535 Bytes	A string value of variable length
  TINYBLOB	0~255 Bytes	A binary string of no more than 255 characters
  TINYTEXT	0~255 Bytes	A short string value
  BLOB	0~65535 Bytes	A string value in the binary form
  TEXT	0~65535 Bytes	A string value
  MEDIUMBLOB	0~16777215 Bytes	A medium string value in the binary form
  MEDIUMTEXT	0~16777215 Bytes	A medium string value
  LONGBLOB	0~4294967295 Bytes	A long string value in the binary form
  LONGTEXT	0~4294967295 Bytes	A long string value

• NULL value

  • Insert the NULL value.

    • Insert the NULL value into an OSS foreign table.

      To insert the NULL value into an OSS foreign table, you must set NULL_MARKER when you create the table. By default, the NULL_MARKER value of an OSS foreign table is NULL. You can execute the SHOW CREATE TABLE statement to view the value:

      show create table t1;

      Sample 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)

    • Inserts the NULL value into a CSV file.

      If you insert a NULL_MARKER in a field in a CSV file without enclosing the NULL_MARKER in double quotation marks, PolarDB for MySQL recognizes it as NULL.

      Note

      • If you enclose the NULL_MARKER in double quotation marks, PolarDB for MySQL recognizes it as a string and the NULL value cannot be discovered by executing the is_null statement. If the data type of the parameter to which the NULL value is assigned in the CSV file does not match that in the OSS foreign table, an error is reported.

      • The NULL_MARKER value cannot contain only digits or be left empty. In addition, it cannot contain the following four characters:

        ", \n, \r, or ,

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

      If 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 the NULL value.

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

    • When data is from a CSV file and if the value in the CSV file is NULL but the 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 sql_mode values that you set for syntax verification.

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

      • If you set sql_mode to a value other than STRICT_TRANS_TABLES and the current field has a default value, the value of the current field is set to the default value. If the current field does not have a default value, the current field is assigned to the default value of MySQL based on the field type. For more information, see Data Type Default Values. If a warning message is prompted, 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 modify the sql_mode parameter on the Parameters page of the console to modify the current syntax verification rule. For more information, see Modify parameters.

      Example: Create an OSS foreign table named t, and then set the id field to NOT NULL and no default value.

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

      If the t.CSV file contains the following data:

      NULL
      2

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

      • If sql_mode is set to STRICT_TRANS_TABLES and when you execute the following statement to query the 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 the sql_mode is set to a value other than STRICT_TRANS_TABLES and when you execute the following statement to query the data in the CSV file:

        select * from t;

        A similar 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;

        A similar result is returned:

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

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

The maximum memory for OSS is loose_csv_max_oss_threads * loose_csv_oss_buff_size.

Some query conditions can be push down to the remote OSS bucket for better query efficiency. This optimization process is called engine condition pushdown. Engine condition pushdown are subject to the following limits:

• Only UTF-8 encoded CSV files are supported.

• Only the following types of operators are supported in SQL statements:

  • Comparison operators: >, <, >=, <=, and ==

  • Logical operators: LIKE, IN, AND, and OR

  • Arithmetic operators: +,-, *, and /.

• Only a single object can be queried when you use an SQL statement. The following clauses are not supported: JOIN, ORDER BY, GROUP BY, and HAVING.

• The WHERE clause cannot contain aggregation conditions. For example, WHERE max(age) > 100 is not allowed.

• A maximum of 1,000 columns can be specified for an SQL statement. The column name in an SQL statement can be a maximum of 1,024 bytes in length.

• A maximum of five wildcards (%) are supported in a LIKE clause.

• A maximum of 1,024 constants are supported in an IN clause.

• The maximum column size and row size for a CSV object are 256 KB.

• The maximum size of an SQL statement is 16 KB. A maximum of 20 expressions can be added after WHERE. Each statement supports up to 100 aggregation operations.

Queries that meet the preceding conditions are pushed down to the remote OSS bucket. You can use the OSS foreign table execution plan to view which query conditions are pushed down to pushed down to the remote OSS bucket.

• View the execution plan of an OSS foreign table by executing the EXPLAIN statement. Example:

  EXPLAIN SELECT count(*) FROM `t1` WHERE `id` > 5 AND `id` < 100 AND `name` LIKE "%1%%%%%" GROUP BY `id` ORDER BY `id` DESC;
  +----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------------------------------------------------------------------------------------------------------------------------+
  | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra                                                                                                                            |
  +----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------------------------------------------------------------------------------------------------------------------------+
  |  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 15000 |     1.23 | Using where; With pushed engine condition ((`test`.`t1`.`id` > 5) and (`test`.`t1`.`id` < 100)); Using temporary; Using filesort |
  +----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------------------------------------------------------------------------------------------------------------------------+
  1 row in set, 1 warning (0.00 sec)

  The conditions behind With pushed engine condition can be pushed down to the remote OSS bucket. The conditions `name` LIKE "%1%%%%%" and GROUP BY `id` ORDER BY `id` DESC cannot be pushed down to the remote OSS bucket. They are only executed on the local OSS server.

• View the execution plan of an OSS foreign table in the tree format. Example:

  EXPLAIN FORMAT=tree  SELECT SELECT count(*) FROM `t1` WHERE `id` > 5 AND `id` < 100 AND `name` LIKE "%1%%%%%" Y `id` ORDER BY `id` DESC;
  +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  | EXPLAIN                                                                                                                                                                                                                                                                                                           |
  +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  | -> Sort: <temporary>.id DESC
      -> Table scan on <temporary>
          -> Aggregate using temporary table
              -> Filter: (t1.`name` like '%1%%%%%')  (cost=1690.00 rows=185)
                  -> Table scan on t1, extra ( engine conditions: ((t1.id > 5) and (t1.id < 100)) )  (cost=1690.00 rows=15000)
   |
  +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  1 row in set (0.00 sec)

  The conditions behind engine conditions: can be pushed down to the remote OSS bucket. The conditions `name` LIKE "%1%%%%%" and GROUP BY `id` ORDER BY `id` DESC cannot be pushed down to the remote OSS bucket. They are only executed on the local OSS server.

  Note

  The cluster version must be PolarDB for MySQL 8.0.2. You can check the cluster version as stated in Query the engine version.

• View the execution plan of an OSS foreign table in the JSON format. Example:

  EXPLAIN FORMAT=json  SELECT count(*) FROM `t1` WHERE `id` > 5 AND `id` < 100 AND `name` LIKE "%1%%%%%" GROUP BY `id` ORDER BY `id` DESC;
  +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  | EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
  +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  | {
    "query_block": {
      "select_id": 1,
      "cost_info": {
        "query_cost": "1875.13"
      },
      "ordering_operation": {
        "using_filesort": false,
        "grouping_operation": {
          "using_temporary_table": true,
          "using_filesort": true,
          "cost_info": {
            "sort_cost": "185.13"
          },
          "table": {
            "table_name": "t1",
            "access_type": "ALL",
            "rows_examined_per_scan": 15000,
            "rows_produced_per_join": 185,
            "filtered": "1.23",
            "engine_condition": "((`test`.`t1`.`id` > 5) and (`test`.`t1`.`id` < 100))",
            "cost_info": {
              "read_cost": "1671.49",
              "eval_cost": "18.51",
              "prefix_cost": "1690.00",
              "data_read_per_join": "146K"
            },
            "used_columns": [
              "id",
              "name"
            ],
            "attached_condition": "(`test`.`t1`.`name` like '%1%%%%%')"
          }
        }
      }
    }
  } |
  +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  1 row in set, 1 warning (0.00 sec)

  The conditions behind engine conditions: can be pushed down to the remote OSS bucket. The conditions `name` LIKE "%1%%%%%" and GROUP BY `id` ORDER BY `id` DESC cannot be pushed down to the remote OSS bucket. They are only executed on the local OSS server.

If the following error occurs, some characters in the current OSS data file do not meet the requirements for OSS pushdown.

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.

You can use hints or the optimizer_switch variable to manually disable the engine condition pushdown feature.

• Hints

  You can use a hint to disable the engine condition pushdown feature for a query. In the following example, the engine condition pushdown feature is disabled for the t1 table:

  SELECT /*+ NO_ECP(t1) */ `j` FROM `t1` WHERE `j` LIKE "%c%" LIMIT 10;

• optimizer_switch

  You can use the optimizer_switch variable to disable the engine condition pushdown feature for all queries on the current session.

  SET SESSION optimizer_switch='engine_condition_pushdown=off'; # Set engine_condition_pushdown to off to disable the condition pushdown feature for all queries on the current session. 

  You can execute the following statement to view the optimizer_switch status and determine whether the engine_condition_pushdown feature is enabled for all queries in the current session:

  select @@optimizer_switch;