Hologres:Automatically create foreign tables for MaxCompute tables

How it works

Limits

• The feature of automatically creating foreign tables for MaxCompute tables is supported only by Hologres V1.1.43 and later. If the version of your Hologres instance is earlier than V1.1.43, manually upgrade your Hologres instance in the Hologres console or join a Hologres DingTalk group to apply for an instance upgrade. For more information about how to manually upgrade a Hologres instance, see Instance upgrades. For more information about how to join a Hologres DingTalk group, see Obtain online support for Hologres .

• Make sure that the name of a MaxCompute project does not start with hg_ or holo_. These prefixes are reserved keywords in Hologres.

• If a MaxCompute table contains data whose types are not supported by Hologres foreign tables, Hologres cannot create a foreign table for the MaxCompute table. You can only manually create a foreign table for the MaxCompute table by selecting supported fields.

• When you use the feature of automatically creating foreign tables for queried MaxCompute tables, take note of the following items:

  • When you query a MaxCompute table, Hologres automatically creates a foreign table if the foreign table does not exist in the schema with the same name as the MaxCompute project. The foreign table is named the same as the MaxCompute table and is created in the schema with the same name as the MaxCompute project.

  • If an internal table with the same name as the MaxCompute table exists in the schema with the same name as the MaxCompute project, data is queried from the internal table. In this case, the feature of automatically creating foreign tables for MaxCompute tables is not triggered.

  • To allow your queries to trigger the creation of foreign tables after the feature is enabled, make sure that your account that is used to initiate the queries has the permissions to create and delete tables and schemas in the specified database. If you have used the feature to create a foreign table, you need to only make sure that the account has the permissions to query the foreign table.

  • Up to six foreign tables can be automatically created for a single query. Therefore, you can execute an SQL statement to query a maximum of six tables. If the number of tables exceeds six in a query statement, foreign tables fail to be created. You need to modify the query statement to automatically create foreign tables.

• When you use the feature of automatically creating foreign tables for all MaxCompute tables, take note of the following items:

  • We recommend that you set the interval at which foreign tables are created to at least 5 minutes.

  • We recommend that you do not specify a MaxCompute project that contains more than 1,000 tables as the default MaxCompute project. Foreign tables are automatically created for all tables in the default MaxCompute project.

  • If the schema of a MaxCompute table is modified and a foreign table has been automatically created for the MaxCompute table, the schema of the foreign table is not updated when Hologres automatically creates foreign tables for all tables in the related MaxCompute project. You need to run the import command to manually update the foreign table.

• In Hologres V1.3.28 and later, the value of the hg_experimental_load_all_foreign_table_interval_time parameter is changed from 5 minutes to 30 minutes for the feature of automatically creating foreign tables for all tables in a specific MaxCompute project. This reduces the consumption of system resources due to automatic inspection tasks.

• Hologres does not automatically create foreign tables for tables in MaxCompute projects that are configured with three-layer models.

Automatically create foreign tables for queried MaxCompute tables

• Enable the feature of automatically creating foreign tables for queried MaxCompute tables.

  To enable the feature of automatically creating foreign tables, execute the following statement to set hg_experimental_enable_auto_load_foreign_table to on.

  ALTER DATABASE <database name> SET hg_experimental_enable_auto_load_foreign_table = on;

  Replace database name with the name of the Hologres database for which you want to enable the feature of automatically creating foreign tables for queried MaxCompute tables. The default value of the hg_experimental_enable_auto_load_foreign_table parameter is off. This indicates that the feature of automatically creating foreign tables for MaxCompute tables is disabled by default.

  After you enable this feature, you can execute the select schema_name.table_name statement to query data from tables in MaxCompute. In this statement, set schema_name to the name of a MaxCompute project and set table_name to the name of a table in the MaxCompute project. If you use JDBC to connect to Hologres, you can set the currentSchema parameter in the JDBC URL to associate the schema in Hologres with the project in MaxCompute. For more information, see JDBC.

  Note

  If you do not specify schema_name, the system uses the current schema name as the name of the MaxCompute project and queries the metadata of the related table in the MaxCompute project. The default schema name is public. You must make sure that a project with the same name as the schema exists in MaxCompute.

• Disable the feature of automatically creating foreign tables for queried MaxCompute tables.

  Execute the following SQL statement to disable this feature. After this feature is disabled, MaxCompute metadata is not automatically synchronized and Hologres does not automatically create foreign tables for MaxCompute tables. In Hologres databases, you can view only the created foreign tables and query the data in these foreign tables. If you want to create foreign tables for MaxCompute tables, run the import command. For more information, see IMPORT FOREIGN SCHEMA.

  ALTER DATABASE <database name> SET hg_experimental_enable_auto_load_foreign_table = off;

  Replace database name with the name of the Hologres database for which you want to disable the feature of automatically creating foreign tables for MaxCompute tables.

• Delete a created foreign table.

  If you do not need to accelerate queries of data in a foreign table, execute the DROP statement to delete the foreign table. For more information, see DROP TABLE.

Automatically create foreign tables for all MaxCompute tables

• Enable the feature of automatically creating foreign tables for all tables in a specific MaxCompute project

  If queries need to be accelerated for a large number of MaxCompute tables, you can enable the feature of automatically creating foreign tables for all tables in a specific MaxCompute project. When you enable this feature, you must specify a MaxCompute project. You can also set the hg_experimental_load_all_foreign_table_interval_time parameter to specify an interval at which new tables in the MaxCompute project are periodically inspected and foreign tables are automatically created for the new MaxCompute tables when you initiate a query. This way, you can accelerate queries for full and incremental data in the MaxCompute project. To use the feature of automatically creating foreign tables for all tables in a specific MaxCompute project, perform the following steps:

  1. Enable the feature of automatically creating foreign tables for MaxCompute tables.

     Execute the following statement to enable the feature of automatically creating foreign tables for MaxCompute tables before you enable the feature of automatically creating foreign tables for all tables in a specific MaxCompute project:

     ALTER DATABASE <database name> SET hg_experimental_enable_auto_load_foreign_table = on;

     Replace database name with the name of the Hologres database for which you want to enable the feature of automatically creating foreign tables for MaxCompute tables.

  2. Enable the feature of automatically creating foreign tables for all tables in a specific MaxCompute project.

     After the feature of automatically creating foreign tables for MaxCompute tables is enabled, you can set the hg_experimental_default_odps_project_list parameter to enable the feature of automatically creating foreign tables for all tables in a specific MaxCompute project. When you query tables in the MaxCompute project, Hologres creates foreign tables for all tables in the MaxCompute project.

     ALTER DATABASE <database name> 
     SET hg_experimental_default_odps_project_list='<odps_project_name_1>,<odps_project_name_2>...';

     The following table describes the parameters in the preceding syntax.

     Parameter	Description
     database name	The name of the Hologres database for which you enable the feature of automatically creating foreign tables for MaxCompute tables.
     odps_project_name_1	The name of a MaxCompute project.
     odps_project_name_2	The name of another MaxCompute project.

     If you do not specify a MaxCompute project, Hologres does not periodically synchronize metadata from any MaxCompute project.

  3. Enable periodic inspections.

     After you enable the feature of automatically creating foreign tables for all tables in a specific MaxCompute project, you can set the hg_experimental_load_all_foreign_table_interval_time parameter to specify an interval at which new MaxCompute tables are inspected and foreign tables are automatically created for new tables in the MaxCompute project. After the specified interval expires, Hologres automatically creates foreign tables for new tables in the MaxCompute project when you initiate a query. This implements accelerated queries for incremental tables.

     -- View the interval at which foreign tables are automatically created.
     show hg_experimental_load_all_foreign_table_interval_time;
     
     -- Modify the interval at which foreign tables are automatically created.
     ALTER DATABASE <database name> SET hg_experimental_load_all_foreign_table_interval_time = '5min';

     Replace database name with the name of the Hologres database for which you enable the feature of automatically creating foreign tables for MaxCompute tables. The default interval is 5 minutes. If a new MaxCompute table is created within 5 minutes, Hologres automatically creates a foreign table for the new MaxCompute table 5 minutes after a query is initiated.

• View the MaxCompute projects for which you have enabled the feature of automatically creating foreign tables.

  You can execute the following SQL statement to view the MaxCompute projects for which you have enabled the feature of automatically creating foreign tables:

  show hg_experimental_default_odps_project_list;

Examples

• Example 1: Enable the feature of automatically creating foreign tables for queried MaxCompute tables.

  Enable the feature of automatically creating foreign tables for MaxCompute tables for the Hologres database named demo. After you enable the feature, if no foreign table with the same name as the MaxCompute table exists in Hologres when you initiate a query, Hologres automatically creates a foreign table to accelerate the query.

  ALTER DATABASE demo SET hg_experimental_enable_auto_load_foreign_table = on;

• Example 2: Enable the feature of automatically creating foreign tables for all MaxCompute tables.

  Execute the following statements to enable the feature of automatically creating foreign tables for all tables in the MaxCompute project named odps_hologres. In addition, Hologres periodically inspects new tables in the MaxCompute project at the interval specified by the hg_experimental_load_all_foreign_table_interval_time parameter. In this example, the interval is set to 10 minutes. This way, Hologres automatically creates foreign tables for new tables in the odps_hologres project.

  -- Enable the feature of automatically creating foreign tables for MaxCompute tables.
  ALTER DATABASE demo SET hg_experimental_enable_auto_load_foreign_table = on;
  -- Enable the feature of automatically creating foreign tables for all tables in the odps_project project.
  ALTER DATABASE demo SET hg_experimental_default_odps_project_list='odps_hologres';
  -- Set the inspection interval to 10 minutes.
  ALTER DATABASE demo SET hg_experimental_load_all_foreign_table_interval_time = '10min';