Hologres:Hologres Shared Cluster (BI-based MaxCompute Acceleration Edition)

Limits

Use Hologres Shared Cluster (BI-based MaxCompute Acceleration Edition)

1. Purchase a Hologres Shared Cluster (BI-based MaxCompute Acceleration Edition) instance.

   1. Log on to the Alibaba Cloud official website by using your Alibaba Cloud account.

   2. Go to the Hologres product page.

   3. Click Buy Now to go to the buy page.

   4. Set the Product Type parameter to Shared Cluster (BI-based MaxCompute Acceleration Edition), select a region, specify the Instance Name parameter, and then click Buy Now.

2. Create a database in the Hologres Shared Cluster (BI-based MaxCompute Acceleration Edition) instance.

   After the instance is purchased, go to the Hologres console to view the status of the instance.

   After the instance is created, the system automatically creates a default database named postgres. The default database is used to monitor and manage the instance that you have created. If you need to create other databases to process business data, perform the following steps:

   1. On the Instances page, find the instance for which you want to create a database and click the instance name.

      You can also click Manage in the Actions column of the instance to go to the Instance Details page.

   2. In the left-side navigation pane of the Instance Details page, click Database Management.

   3. On the Database Authorization page, click Create Database in the upper-right corner.

   4. In the Create Database dialog box, configure the Instance Name, Database Name, and SPM parameters based on your business requirements.

      In Hologres, you can select Expert or SPM for SPM.

      Expert: This model is exactly the same as the permission model in PostgreSQL. This model is also called the standard PostgreSQL authorization model. For more information, see Standard PostgreSQL authorization model.

      SPM: This model is exclusively provided by Hologres and helps you simplify permission management based on your business requirements. For more information, see Overview.

      To facilitate permission management, we recommend that you select SPM when you create a database.

   5. Click OK.

      You can view the created database on the Database Authorization page.

3. Connect a development tool to the Hologres Shared Cluster (BI-based MaxCompute Acceleration Edition) instance.

   Hologres Shared Cluster (BI-based MaxCompute Acceleration Edition) is compatible with PostgreSQL and provides the Java Database Connectivity (JDBC) driver and the Open Database Connectivity (ODBC) driver. After you create a database, you can connect a development tool to the Hologres Shared Cluster (BI-based MaxCompute Acceleration Edition) instance for data development. You can select an appropriate development tool based on your business requirements. For more information, see Overview.

   In this example, HoloWeb is used. HoloWeb is a tool that is developed by the Hologres development team. To connect HoloWeb to the Hologres Shared Cluster (BI-based MaxCompute Acceleration Edition) instance, perform the following steps:

   1. Log on to the Hologres console. In the left-side navigation pane, click Go to HoloWeb to go to the HoloWeb console.

   2. In the top navigation bar, click Metadata Management > Connect to an instance.

   3. In the Connect to an instance dialog box, configure the parameters.

      Parameter	Description	Required
      Network type	public network: Supported regions include China (Shanghai), China (Shenzhen), China (Beijing), China (Hangzhou), Singapore, China (Hong Kong), Malaysia (Kuala Lumpur), and US (Silicon Valley). The icon indicates that the network type of the instance is public network. VPC: You can select only the region from which you log on to the HoloWeb console. The icon indicates that the network type of the instance is VPC. You cannot edit information about this instance or delete this instance.	No
      Instance name	The Hologres instance that is created by using the current Alibaba Cloud account.	No
      Name	After you set the Instance name parameter, the name of the specified instance is automatically displayed in the Name field. You can also enter a custom instance name.	Yes
      Description	The description of the connection.	No
      Domain Name	The endpoint of the Hologres instance. You can view the endpoint of the Hologres instance in the Network Information section of the instance details page in the Hologres console. After you set the Instance name parameter, the endpoint of the specified instance is automatically displayed in the Domain Name field. You can also enter the endpoint of the Hologres instance.	Yes
      Port	The port number of the Hologres instance. You can view the port number of the Hologres instance in the Network Information section of the instance details page in the Hologres console. After you set the Instance name parameter, the port number of the specified instance is automatically displayed in the Port field. You can also enter the port number of the Hologres instance.	Yes
      Logon Method	Password-free Logon: You can directly connect to the instance by using the current Alibaba Cloud account, without the need to enter the AccessKey ID and AccessKey secret. Password Logon: You can enter the AccessKey ID and AccessKey secret of the current or another Alibaba Cloud account to connect to the instance.	Yes
      Username	This parameter is displayed only when you set the Logon Method parameter to Password Logon. The AccessKey ID of your Alibaba Cloud account. You can obtain the AccessKey ID from the Security Management page.	No
      Password	This parameter is displayed only when you set the Logon Method parameter to Password Logon. The AccessKey secret of your Alibaba Cloud account. You can obtain the AccessKey secret from the Security Management page.	No
      Test Connectivity	Checks whether the instance connection is successful. If the instance connection is successful, the message Test passed is displayed. If the instance connection fails, the message Test failed is displayed.	No
      Login after connection	Specifies whether to connect to the instance. Yes: connects to the instance. The connected instance is displayed in the left-side Instances Connected list. No: does not connect to the instance. The disconnected instance is displayed in the left-side Instances Disconnected list.	Yes

   4. Click OK.

4. Accelerate queries on data in MaxCompute tables.

   After the instance is connected to HoloWeb, you can create foreign tables to accelerate queries on data in MaxCompute tables.

   In this example, a foreign table is created in HoloWeb to accelerate queries on table data of the MaxCompute project named public_data in the public dataset. This section describes the procedure.

   1. Create a foreign table.

      1. Choose Metadata Management > MaxCompute Acceleration > Create Foreign Table to create a foreign table in a visualized manner.

      2. On the New foreign table tab, configure the parameters that are described in the following table.

         Parameter	Description
         Schema	The name of the schema. You can select the default schema named public or a custom schema.
         Table name	The name of the Hologres foreign table. After you enter the name of the source MaxCompute table, the name of the foreign table must be the same as that of the source MaxCompute table. In this case, you cannot modify the name of the foreign table. To rename the foreign table, you must create the foreign table, find the foreign table in the left-side Instances Connected list, and then right-click the foreign table.
         Description	The description of the Hologres foreign table.
         Type	The service type of the source table. The default value is MaxCompute, which cannot be changed.
         Server List	You can use the odps_server server that is created at the underlying layer of Hologres. For more information, see postgres_fdw.
         Table	The name of the source MaxCompute table to be mapped, including the name of the MaxCompute project. Format: project.table_name. Note You cannot query data in a source table that is stored in a region different from that of the selected Hologres Shared Cluster (BI-based MaxCompute Acceleration Edition) instance. After you enter the name of the source MaxCompute table, all the fields of the source table are displayed. By default, the created foreign table contains all the fields of the source table. If you need to create a foreign table that contains only specific fields of the source table, use SQL statements to create the foreign table. For more information, see CREATE FOREIGN TABLE.

      3. Enter the name of the MaxCompute table that you want to query. Then, fields in the table appear. Select the fields that you want to synchronize and click Submit.

      You can also create an SQL query window and create multiple foreign tables at a time by executing SQL statements in the SQL query window. For example, you can execute the following statement. For more information about how to create an SQL query window, see Manage an SQL query task.

      IMPORT FOREIGN SCHEMA public_data LIMIT to(
        customer,
        customer_address,
        customer_demographics,
        inventory,item,
        date_dim,
        warehouse) 
        FROM server odps_server INTO PUBLIC options(if_table_exist 'update');

   2. Preview data in the foreign table.

      After you create a foreign table, go to the Metadata Management page. In the left-side Instance Management section, double-click the created foreign table. In the table configuration pane, click the Data preview tab to view the data in the MaxCompute table.

      You can also create an SQL query window and create multiple foreign tables at a time by executing SQL statements in the SQL query window. For more information about how to create an SQL query window, see Manage an SQL query task. Sample statement:

      IMPORT FOREIGN SCHEMA public_data LIMIT to(
        customer,
        customer_address,
        customer_demographics,
        inventory,item,
        date_dim,
        warehouse) 
        FROM server odps_server INTO PUBLIC options(if_table_exist 'update');

   3. Accelerate queries on data in source tables by using foreign tables.

      In this example, the following SQL statements are executed to accelerate queries on data in source tables by using foreign tables.

      # SQL 1: Query the number of customers with the preferred flag specified, and sort the query results in descending order based on the number of customers. 
      SELECT c_preferred_cust_flag,
             count(*) AS cnt
      FROM customer
      WHERE c_preferred_cust_flag IS NOT NULL
      GROUP BY c_preferred_cust_flag
      ORDER BY cnt DESC LIMIT 10;
      
      # SQL 2: Query the number of customers who were born in each year, and display the years in which more than 1,000 customers were born in descending order based on the number of customers. 
      SELECT c_birth_year,
             count(*) AS cnt
      FROM customer
      WHERE c_birth_year IS NOT NULL
      GROUP BY c_birth_year HAVING count(*) > 1000
      ORDER BY cnt DESC LIMIT 10;
      
      # SQL 3: Query the number of customers in each city, and display the cities where more than 10 customers reside in descending order based on the number of customers. 
      SELECT ca_city,
             count(*) AS cnt
      FROM customer ,
           customer_address
      WHERE c_current_addr_sk = ca_address_sk
        AND ca_city IS NOT NULL
      GROUP BY ca_city HAVING count(*) > 10
      ORDER BY cnt DESC LIMIT 10;
      
      # SQL 4: Query the number of customers who were born between 1980 and 1990 in each city, and display the cities where more than 10 customers born between 1980 and 1990 reside in descending order based on the number of customers. 
      SELECT ca_city,
             count(*) AS cnt
      FROM customer ,
           customer_address
      WHERE c_current_addr_sk = ca_address_sk
        AND c_birth_year >= 1980
        AND c_birth_year < 1990
        AND c_preferred_cust_flag = 'Y'
        AND ca_city IS NOT NULL
      GROUP BY ca_city HAVING count(*) > 10
      ORDER BY cnt DESC LIMIT 10;

5. Connect a business intelligence (BI) tool to the instance to perform visualized data analytics.

   Hologres Shared Cluster (BI-based MaxCompute Acceleration Edition) is compatible with PostgreSQL and can be directly connected to BI tools. After you create a foreign table to accelerate queries on data in a MaxCompute table, you can select an appropriate BI tool based on your business requirements and perform visualized data analytics. For more information about the BI tools that are supported by Hologres, see Overview.

   In this example, Quick BI is used to connect to Hologres Shared Cluster (BI-based MaxCompute Acceleration Edition). To perform visualized data analytics, perform the following steps:

   1. Log on to the Quick BI console.

   2. Add a data source.

      For more information about how to add a Hologres data source, see Add a Hologres data source.

   3. Analyze data in a visualized manner.

      After the data source is connected, you can analyze data in a visualized manner by executing SQL statements for ad hoc queries.

Data type mappings

For more information about data type mappings between Hologres and MaxCompute, see Data types.