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 Instances.

   2. In the Instances 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), China (Zhangjiakou), Singapore, China (Hong Kong), Malaysia (Kuala Lumpur), Indonesia (Jakarta) 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.	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. Optional. 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.

   Note

   If you have created a database, skip this step and proceed to the next step.

   1. On the Metadata Management > New Database.

      You can also click Instances Connected in the left-side navigation pane of the Metadata Management tab. Right-click your Hologres instance and select New Database.

   2. In the Create 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.

   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 Create 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 of the statements in the Operational 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 Query Acceleration > Create Foreign Table.

      Note

      You can accelerate queries on data in MaxCompute and OSS data lakes only after you log on to a database.

   2. On the Create MaxCompute External Table tab, configure the parameters and click Submit.

      Section	Parameter	Description
      Source MaxCompute Table	Project Name	The name of the MaxCompute project.
      	Schema Name	The name of the schema in which the MaxCompute table resides. If your MaxCompute project uses the two-layer model, this parameter is not displayed by default. If your MaxCompute project uses the three-layer model, you can select an authorized schema from the drop-down list.
      	Table Name	The name of the MaxCompute table. Prefix-based fuzzy search is supported.
      Destination Hologres Table	Database Name	The name of the Hologres database to which the foreign table belongs.
      	Schema Name	Schema Name You can select the default schema named public or a custom schema.
      	Table Name	The name of the Hologres foreign table. The name of the source MaxCompute table is automatically specified for this parameter. You can also manually rename the table.
      	Destination Table Description	The description of the Hologres foreign table that you create, which is user-defined.

      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.