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 > Create 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 Name drop-down list and a database from the Database drop-down list, enter the following sample statements in the SQL editor, and then click Running.

      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 to accelerate queries on MaxCompute data.

   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:

   Note

   • Make sure that a MaxCompute project is created, a table is created in the project, and data is written to the table. For more information, see Create a MaxCompute project. You can use one of the following methods to create a table and write data:

     1. Add a MaxCompute data source in the DataWorks console. Then, create a table and write data to the table.

     2. Use the MaxCompute client to create a table and write data to the table. For more information, see Use the MaxCompute client.

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

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

   2. On the Create MaxCompute External Table tab, select an acceleration method, configure other parameters, and then click Submit.

      Entire Project

      Category	Parameter	Description
      Acceleration Method	Select an acceleration method.	Valid values: Entire Project Selected Tables Single Table Select Entire Project.
      MaxCompute Data Source	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 specify an authorized schema for this parameter. For information about the schema feature, see Schema-related operations.
      Destination Table Position	Holo Schema	The name of the schema. You can select the default schema named public or a custom schema.
      Advanced Settings	Processing Rule for Table Name Conflicts	Valid values: Ignore Conflicts and Continue Creating Tables Change Table Names Report Error and Create No Table
      	Processing Rule for Unsupported Data Types	Valid values: Report Error and Stop Import Ignore and Skip Table of Unsupported Fields

      Selected Tables

      Category	Parameter	Description
      Acceleration Method	Select an acceleration method.	Valid values: Entire Project Selected Tables Single Table Select Selected Tables.
      MaxCompute Data Source	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 specify an authorized schema for this parameter. For information about the schema feature, see Schema-related operations.
      Destination Table Position	Holo Schema	The name of the schema. You can select the default schema named public or a custom schema.
      Advanced Settings	Processing Rule for Table Name Conflicts	Valid values: Ignore Conflicts and Continue Creating Tables Change Table Names Report Error and Create No Table
      	Processing Rule for Unsupported Data Types	Valid values: Report Error and Stop Import Ignore and Skip Table of Unsupported Fields
      	Search	A maximum of 200 tables can be displayed when you search for source tables. Excess tables are not displayed.

      Single Table

      Category	Parameter	Description
      Acceleration Method	Select an acceleration method.	Valid values: Entire Project Selected Tables Single Table Select Single Table.
      MaxCompute Data Source	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 specify an authorized schema for this parameter. For information about the schema feature, see Schema-related operations.
      	Table Name	The name of the table in the schema of MaxCompute. You can enter a table name prefix to perform a fuzzy match.
      Destination Hologres Table	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 table used for query acceleration.
      	Destination Table Description	The description of the Hologres table used for query acceleration.

   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.