PolarDB:Use OSS foreign tables to access OSS data

During the query process, the query engine can push queries with specific conditions down to a remote OSS bucket to enhance query efficiency. This optimization is called engine condition pushdown. The engine condition pushdown feature is 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 file 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 a WHERE clause. Each statement supports up to 100 aggregation operations.

Queries that meet the preceding conditions are pushed down to a remote OSS bucket. You can use the execution plan of an OSS foreign table to view the queries that are pushed down to a 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)

  Queries with the conditions after With pushed engine condition can be pushed down to a remote OSS bucket. Queries with the conditions `name` LIKE "%1%%%%%" and GROUP BY `id` ORDER BY `id` DESC can be performed only 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)

  Queries with the conditions after engine conditions: can be pushed down to a remote OSS bucket. Queries with the conditions `name` LIKE "%1%%%%%" and GROUP BY `id` ORDER BY `id` DESC can be performed only on the local OSS server.

  Note

  You must use a PolarDB for MySQL 8.0.2 cluster to query data. You can query the version number to check the cluster 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)

  Queries with the conditions after engine conditions: can be pushed down to a remote OSS bucket. Queries with the conditions `name` LIKE "%1%%%%%" and GROUP BY `id` ORDER BY `id` DESC can be performed only on the local OSS server.

If the following error occurs, some characters in the current OSS data file do not meet the requirements for engine condition 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

  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

  Use the optimizer_switch variable to disable the engine condition pushdown feature for all queries in the current session.

  SET SESSION optimizer_switch='engine_condition_pushdown=off'; # Set the engine_condition_pushdown parameter to off. In this case, the engine condition pushdown feature is disabled for all queries in the current session.

  You can execute the following statement to determine whether the engine condition pushdown feature is enabled for all queries in the current session based on the value of the optimizer_switch variable:

  select @@optimizer_switch;