This topic describes how to accelerate queries of MaxCompute data by creating a foreign table in Hologres.

Background

MaxCompute is a fast and fully-managed computing platform for large-scale data warehousing. MaxCompute can process exabytes of data. It provides solutions for storing and computing mass structured data in data warehouses and provides analytics and modeling services.

Hologres is a real-time interactive analytics data warehouse compatible with PostgreSQL. Hologres integrates seamlessly with MaxCompute at the underlying layer. Hologres allows you to create foreign tables to accelerate queries of MaxCompute data, without the need to store data in Hologres. This eliminates data import and export operations and accelerates data queries. In addition, you can import data to Hologres for analytics. Compared with other similar services not oriented for the big data ecosystem, Hologres offers faster data import and export and higher performance.

We recommend that you select a query method based on different business features and scenarios:

  • Directly analyze MaxCompute data by using Hologres: This is a preferred method, which is suitable for the scenario where the size of the data to be queried is less than 100 GB. This is a threshold for the size of the hit partition after partition filtering, regardless of the size of the queried fields.
  • Import MaxCompute data to Hologres for queries: This method is suitable for the following scenarios: the size of the data to be queried in a single table being greater than 100 GB, complex queries, index-based queries, and data updates and insertions involved.

Example 1: query data in a MaxCompute non-partitioned table

  1. Create a MaxCompute non-partitioned table.
    For more information about how to create a table and import data to the table, see Create and view a table. You can also use an existing MaxCompute table. This example uses an existing MaxCompute table. The sample DDL statements are as follows:
    CREATE TABLE weather (
        city            STRING ,
        temp_lo         int,           -- The lowest temperature.
        temp_hi         int         -- The highest temperature.
    );
    INSERT INTO weather VALUES 
    ('beijing',40,50),
    ('hangzhou',46,55);
  2. Create a foreign table in Hologres.
    Create a foreign table in Hologres for mapping the source table in MaxCompute. You can select some or all fields in the MaxCompute source table. The sample statement is as follows:
    CREATE FOREIGN TABLE weather1 (
     city text,
     temp_lo int8,
     temp_hi int8
    )
    SERVER odps_server
    OPTIONS (project_name '<odpsprojectname>',table_name 'weather');
    Note :
    • SERVER: specifies the server on which the foreign table is stored. The value is odps_server, which is implemented at the underlying layer of Hologres. You can directly call this server without the need to create a server. For more information, see Postgres FDW.
    • project_name: the name of the project where the MaxCompute table to be queried resides.
    • table_name: the name of the MaxCompute table to be queried.
    • The field types of the foreign table must be consistent with those of the MaxCompute table. For more information about the data type mapping, see Data types.
    • When the schema of the source table in the MaxCompute project changes, Hologres does not automatically update the table schema. Instead, you must update the table schema by yourself.
    • For more information about how to create multiple foreign tables at a time, see IMPORT FOREIGN SCHEMA.
  3. Query the foreign table.
    After the foreign table is created, you can query MaxCompute data by querying the foreign table.
    SELECT * FROM weather1;

Example 2: query data in a MaxCompute partitioned table

  1. Create a MaxCompute partitione table.
    For more information about how to create a MaxCompute partitioned table, see Partition and column operations. This example uses an existing partitioned table in Data Map. The sample DDL statements are as follows:
    CREATE TABLE odps_test
    (
        shop_name     string,
        customer_id   string,
        total_price   INT 
    )
    PARTITIONED BY  (sale_date string);
    INSERT  overwrite table odps_test partition (sale_date='2013')values ('shop', '1234', 12);
    INSERT  overwrite table odps_test partition (sale_date='2014')values ('rest', '1111', 13);
    INSERT  overwrite table odps_test partition (sale_date='2015')values ('texy', '2222', 14);
  2. Create a foreign table in Hologres.
    Create a foreign table in Hologres for mapping the MaxCompute source table. The foreign table in Hologres does not store data. One partition in the MaxCompute source table maps one field in the foreign table. The sample DDL statement is as follows:
    CREATE FOREIGN TABLE table_odps (
     shop_name text,
     customer_id text,
     total_price int8,
     sale_date text
    )
    SERVER odps_server
    OPTIONS (project_name '<odpsprojectname>', table_name 'odps_test');
  3. Query the partitioned table.
    Query the entire table. The sample SQL statement is as follows:
    SELECT * FROM table_odps;
    Query data of a specified partition. The sample SQL statement is as follows:
    SELECT * FROM table_odps 
    WHERE sale_date = '2013';