This topic describes how to use SQL statements to import data from MaxCompute to Hologres.
Background information
In specific scenarios, you may require quick responses to complex queries in seconds from a large amount of data greater than 100 GB in MaxCompute. To meet this requirement, you can use Hologres to import data from MaxCompute to Hologres for queries.
Import data from a MaxCompute non-partitioned table to Hologres for queries
- Create a non-partitioned table in MaxCompute.Create a non-partitioned source table in MaxCompute, or use an existing non-partitioned table in MaxCompute.In this example, an existing non-partitioned table named odps_test1 in Data Map is used. You can use the following sample SQL statements to create a table and insert data:
CREATE TABLE odps_test1( id int, name STRING , class STRING ); INSERT INTO odps_test1 VALUES (1,'Tom','class3'), (2,'Neal','class4'), (3,'Tony','class4'), (4,'David','class5');
- Create a foreign table in Hologres.Create a foreign table in Hologres to map the source table in MaxCompute. For example, you can use the following SQL statement to create a foreign table:
CREATE FOREIGN TABLE holo_test1 ( id int8, name text, class text ) SERVER odps_server OPTIONS (project_name '<odpsprojectname>', table_name 'odps_test1');
Parameter Description Server The foreign server that stores the connection information for accessing the MaxCompute table to be queried. You can directly call the odps_server server created at the underlying layer of Hologres. 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 map those of the MaxCompute table. For more information about data type mappings, see Data type mappings between MaxCompute and Hologres when you create a foreign MaxCompute table. - Create a table in Hologres to store the received data.Create a table in Hologres to receive data from the MaxCompute source table.The field types of this table must map those of the MaxCompute source table.For example, you can use the following SQL statement to create a table to store the received data:
CREATE TABLE holo_test2 ( id int8, name text, class text );
- Import data to Hologres.Use the
INSERT
statement to import data from MaxCompute to Hologres. You can execute an SQL statement similar to the following example:INSERT INTO holo_test2 SELECT id as id, name as name, class as class FROM holo_test1;
- Query the imported data.Use the SELECT statement to query data imported from the MaxCompute source table to Hologres. For example, you can use the following SQL statement to query the imported data:
SELECT * FROM holo_test2;
Import data from a MaxCompute partitioned data to Hologres for queries
- Create a partitioned table in MaxCompute.For more information about how to create a partitioned table in MaxCompute, see Partition and column operations. Alternatively, use an existing partitioned table in MaxCompute.In this example, an existing partitioned table named odps_test2 in Data Map is used. You can use the following sample SQL statements to create a table and insert data:
CREATE TABLE odps_test2 ( shop_name string, customer_id string, total_price INT ) PARTITIONED BY (sale_date string); INSERT overwrite table odps_test2 partition (sale_date='2013')values ('shop', '1234', 12); INSERT overwrite table odps_test2 partition (sale_date='2014')values ('rest', '1111', 13); INSERT overwrite table odps_test2 partition (sale_date='2015')values ('texy', '2222', 14);
- Create a foreign table in Hologres.Create a foreign table in Hologres to map the source table in MaxCompute. For example, you can use the following SQL statement to create a foreign table:
CREATE FOREIGN TABLE table1_odps ( shop_name text, customer_id text, total_price int8, sale_date text ) SERVER odps_server OPTIONS (project_name '<odpsprojectname>', table_name 'odps_test2');
- Create a table in Hologres to store the received data.Create a table in Hologres to receive data from the MaxCompute source table.Data import from MaxCompute to Hologres succeeds regardless of whether data in Hologres is partitioned. You can import data from the MaxCompute partitioned table to a partitioned or non-partitioned table in Hologres.In this example, data is imported to a partitioned table in Hologres. For example, you can use the following SQL statement to create a partitioned table:
CREATE TABLE table1_holo ( shop_name text, customer_id text, total_price int8, sale_date text ) PARTITION BY LIST (sale_date);
Note Hologres does not support multi-level partitioning. Therefore, you can import data in a multi-level MaxCompute partitioned table to a non-partitioned table or a partitioned table with only one partition level in Hologres. - Create a child partitioned table.Create a child partitioned table in Hologres to receive data from the specified partition. For example, you can use the following SQL statement to create a child partitioned table:
CREATE TABLE table1_holo_0001 PARTITION of table1_holo for VALUES in ('2015');
- Import data to Hologres.Use the
INSERT
statement to import data from the MaxCompute partitioned table to the specified child partitioned table in Hologres. You can execute an SQL statement similar to the following example:INSERT INTO table1_holo_0001 SELECT shop_name as shop_name, customer_id as customer_id, total_price as total_price, sale_date as sale_date from table1_odps WHERE sale_date = '2015';
- Query the imported data.Use the SELECT statement to query data imported from the MaxCompute source table to Hologres. For example, you can use the following SQL statement to query the imported table:
SELECT * FROM table1_holo;