Hologres:Connect to HoloWeb

Procedure

1. Log on to the Hologres console.

2. In the top navigation bar, select a region from the drop-down list.

3. In the left-side navigation pane of the Hologres console, click Go to HoloWeb to go to the HoloWeb console.

4. Connect to a Hologres instance.

   1. On the Metadata Management tab, click Add Instance.

   2. In the Connect to an instance dialog box, configure the parameters as required and click OK.

      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

5. Create a database.

   After you purchase a Hologres instance, a database named postgres is automatically created. This database is allocated a few resources and is used only for management purposes. You can create a database based on your business requirements. If you have created a database, skip this step and proceed to the next step.

   1. On the Metadata Management > Database.

      You can also click the Instances Connected list on the Metadata Management tab. Right-click your Hologres instance and select New Database.

   2. In the New database dialog box, configure the parameters and click OK.

      Parameter	Description
      Instance Name	The name of the instance in which the database is to be created. By default, the name of the current instance is entered.
      Database Name	Enter a database name. Note The database name must be unique.
      Permissions Policy	Select a permission model for the database based on your business requirements. For more information about permission models, see: SPM SLPM Standard PostgreSQL authorization model
      Log On	Yes: logs on to the database. After the database is created, you can directly use the database. No: does not log on to the database. Before you use the created database, you must log on to the database.

6. Create an SQL query.

   After you connect HoloWeb to a Hologres instance, you can use standard PostgreSQL statements to analyze data on the SQL Editor tab. Procedure:

   1. On the SQL Editor tab, click the icon.

      You can also right-click My SQL query in the left-side navigation pane and select New SQL Query. For more information, see Manage an SQL query task.

   2. On the Ad-hoc Query tab, select an instance from the Instance drop-down list and a database from the Database drop-down list, enter the following sample statements in the SQL editor, and then click Run.

      BEGIN;
      CREATE TABLE nation (
       n_nationkey bigint NOT NULL,
       n_name text NOT NULL,
       n_regionkey bigint NOT NULL,
       n_comment text NOT NULL,
      PRIMARY KEY (n_nationkey)
      );
      CALL SET_TABLE_PROPERTY('nation', 'bitmap_columns', 'n_nationkey,n_name,n_regionkey');
      CALL SET_TABLE_PROPERTY('nation', 'dictionary_encoding_columns', 'n_name,n_comment');
      CALL SET_TABLE_PROPERTY('nation', 'time_to_live_in_seconds', '31536000');
      COMMIT;
      
      INSERT INTO nation VALUES
      (11,'zRAQ', 4,'nic deposits boost atop the quickly final requests? quickly regula'),
      (22,'RUSSIA', 3  ,'requests against the platelets use never according to the quickly regular pint'),
      (2,'BRAZIL',  1 ,'y alongside of the pending deposits. carefully special packages are about the ironic forges. slyly special '),
      (5,'ETHIOPIA',  0 ,'ven packages wake quickly. regu'),
      (9,'INDONESIA', 2  ,'slyly express asymptotes. regular deposits haggle slyly. carefully ironic hockey players sleep blithely. carefull'),
      (14,'KENYA',  0  ,'pending excuses haggle furiously deposits. pending, express pinto beans wake fluffily past t'),
      (3,'CANADA',  1 ,'eas hang ironic, silent packages. slyly regular packages are furiously over the tithes. fluffily bold'),
      (4,'EGYPT', 4 ,'y above the carefully unusual theodolites. final dugouts are quickly across the furiously regular d'),
      (7,'GERMANY', 3 ,'l platelets. regular accounts x-ray: unusual, regular acco'),
      (20 ,'SAUDI ARABIA',  4 ,'ts. silent requests haggle. closely express packages sleep across the blithely');
      
      SELECT * FROM nation;

      Note

      The table names and field names in SQL statements are not case-sensitive. If you want to search for a table with an exactly matched name, you need to enclose the name of the table in double quotation marks (") in SQL statements.

   3. View the execution result.

      You can view the execution result of the statements in the Run Logs section.

7. Create a foreign table.

   Hologres is seamlessly integrated with MaxCompute at the underlying layer. You can create foreign tables in Hologres to accelerate queries of MaxCompute data. To create a foreign table in the HoloWeb console, perform the following steps:

   1. On the Metadata Management > MaxCompute Acceleration > Create Foreign Table.

   2. On the New foreign table tab, configure the parameters and click Submit.

      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.

      Note

      When you create a foreign table to synchronize data from a MaxCompute table, comments of both field names and columns of the MaxCompute table are synchronized to the foreign table.

   3. After you create the foreign table, find the instance where the foreign table resides from the Instances Connected list in the left-side navigation pane on the Metadata Management Double-click the foreign table. On the tab that appears, click Data preview to preview the data in the MaxCompute table for which queries are accelerated.