PolarDB:Access OSS data using OSS foreign tables

The OSS engine improves query performance by pushing down eligible conditions to the remote OSS engine. This optimization is called engine condition pushdown. The following limitations apply to condition pushdown:

• Only UTF-8-encoded CSV text files are supported.

• In SQL statements, only the following types of operators and arithmetic expressions are supported:

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

  • Logical operators: LIKE, IN, AND, OR

  • Arithmetic expressions: + , - , * , /

• Only single-file queries are supported. Queries that use JOIN, ORDER BY, GROUP BY, or HAVING clauses are not supported.

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

• The maximum number of columns is 1,000, and the maximum length of a column name in an SQL statement is 1,024 bytes.

• In a LIKE clause, up to five % wildcards are supported.

• In an IN clause, up to 1,024 constants are supported.

• For CSV files, the maximum size is 256 KB per row and 256 KB per column.

• The maximum length of an SQL statement is 16 KB. The WHERE clause can contain up to 20 expressions, and a query can contain up to 100 aggregation operations.

Queries that meet these conditions are pushed down to the OSS engine. Check the execution plan of an OSS foreign table to see which conditions are pushed down.

• Use EXPLAIN to view the execution plan of an OSS foreign table. 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 following With pushed engine condition are pushed down to the remote OSS engine. The remaining conditions, `name` LIKE "%1%%%%%" and GROUP BY `id` ORDER BY `id` DESC, are not pushed down and are executed only on the local OSS server.

• Use the tree format to view the execution plan of an OSS foreign table. Example:

  EXPLAIN FORMAT=tree  SELECT SELECT count(*) FROM `t1` WHERE `id` > 5 AND `id` < 100 AND `name` LIKE "%1%%%%%" GROUP BY `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 following engine conditions: are pushed down to the remote OSS engine. The remaining conditions, `name` LIKE "%1%%%%%" and GROUP BY `id` ORDER BY `id` DESC, are not pushed down and are executed only on the local OSS server.

  Note

  Your cluster must be running PolarDB for MySQL 8.0.2 or later. You can confirm the cluster version by following the instructions in Query the version number.

• Use the JSON format to view the execution plan of an OSS foreign table. 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)

  Similarly, the conditions in the engine_condition field are pushed down to the remote OSS engine. The remaining conditions, `name` LIKE "%1%%%%%" and GROUP BY `id` ORDER BY `id` DESC, are not pushed down and are executed only on the local OSS server.

If you receive the following error, it indicates that some characters in the OSS data file are incompatible with 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 manually disable engine condition pushdown by using a hint or optimizer_switch.

• Hints

  Use a hint to disable engine condition pushdown for a specific query. For example, to disable engine condition pushdown for the t1 table:

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

• optimizer_switch

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

  SET SESSION optimizer_switch='engine_condition_pushdown=off'; # Disable engine condition pushdown for the current session.

  To check the status of engine condition pushdown for the current session, run the following command:

  select @@optimizer_switch;