Hologres:Connect and query with HoloWeb

Procedure

1. Log on to the Hologres management console.

2. In the top navigation bar, select a region.

3. Click Go to HoloWeb to open the HoloWeb developer page.

4. Connect to a Hologres instance.

   1. On the Metadata Management tab, click Instances.

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

      Parameter	Description	Required
      Network type	Public Network: Supported in regions such as China (Shanghai), China (Shenzhen), China (Beijing), China (Hangzhou), China (Zhangjiakou), Singapore, China (Hong Kong), Malaysia (Kuala Lumpur), Indonesia (Jakarta), and US (Silicon Valley). Instances with the icon are public network instances. VPC: You can only select the region where you are logged on to HoloWeb. Instances with the icon are VPC instances. You cannot edit the details of or delete these instances.	No
      Instance name	Select an instance that is created under the current account. Note After you purchase an instance, HoloWeb needs to load the instance information. The instance name may be displayed with a delay. In this case, you can manually configure parameters such as Domain Name to connect to the target instance.	No
      Name	After you select an Instance Name, the Name field is automatically populated with the name of the selected instance. You can also specify a custom connection name.	Yes
      Description	The description of the connection.	No
      Domain Name	The network domain name of the Hologres instance. You can go to the instance details page in the Hologres management console to obtain the domain name from the Network Information section. If you specify an Instance Name, the system automatically fills in the Domain Name of the instance. You can also manually enter a domain name.	Yes
      Port	The network port of the Hologres instance. You can go to the instance details page in the Hologres management console to obtain the port from the Network Information section. If you specify an Instance Name, the system automatically fills in the Port of the instance. You can also manually enter a port number.	Yes
      Logon method	Passwordless logon with current account: Log on directly with the current account without entering an account or password. Password Logon: Log on using an account's username and password.	Yes
      Account	This parameter is required when Logon method is set to Logon with account and password. The AccessKey ID of the account. You can click AccessKey Management to obtain the AccessKey ID.	No
      Password	This parameter is required when Logon method is set to Logon with account and password. The AccessKey secret of the account.	No
      Test connectivity	Tests whether the connection is successful: A Test passed message indicates that the connection is successful. A Test failed message indicates that the connection failed.	No
      Log on after connection	Select whether to log on to the instance. Yes: The instance is logged on and displayed in the Logged-on Instances list on the left. No: The instance is displayed in the Not Logged-on Instances list on the left.	Yes

5. (Optional) Create a database.

   Each Hologres instance includes a default postgres database with limited resources, intended for management only.

   Note

   If you have already created a database, skip this step and create an SQL query.

   1. Click Metadata Management > Database.

      Alternatively, on the Metadata Management page, right-click your instance in the Instances Connected list and select Create Database.

   2. In the Create Database dialog box, configure the parameters and click OK.

      Parameter	Description
      instance name	Displays the name of the connected instance by default.
      database name	Enter a name for the database. Note The database name must be unique within the instance.
      permission policy	Select a permission policy for the database based on your business requirements. For more information about permission policies, see the following topics: SPM SLPM standard PostgreSQL authorization model
      Log on	Yes: You are automatically logged on to the database and can use it immediately. No: You must manually log on to the database before you can use it.

6. Create an SQL query

   Use the SQL Editor to write and run standard PostgreSQL statements.

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

      Alternatively, in the left-side navigation pane, right-click My SQL query and select Create SQL Query. SQL window.

   2. On the new 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 statements in the SQL editor and 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

      SQL queries are case-insensitive for table names and field names. To perform a case-sensitive search for a table, enclose the table name in double quotes (").

   3. The Result [1] section at the bottom of the SQL Editor page displays the execution result.

      The query returns 10 records from the nation table, including the n_nationkey, n_name, n_regionkey, and n_comment columns, which verifies that the table creation and data insertion operations are successful.

7. Create a foreign table to accelerate queries on MaxCompute data.

   Hologres integrates with MaxCompute at the resource layer, enabling query acceleration through foreign tables. Follow these steps to create a foreign table in HoloWeb.

   Note

   • Ensure that you have created a MaxCompute project, created the source table, and populated it with data. You can do this in one of the following ways:

     1. In DataWorks, bind a MaxCompute data source, and then create a table and populate it with data.

     2. Use the MaxCompute client to create a table and write data to it. Use the MaxCompute client.

   • You must log in to a database in HoloWeb before you can use the acceleration features for MaxCompute and OSS data lakes.

   1. Click Metadata Management > Import from MaxCompute > Create Foreign Table.

   2. Configure the parameters in the Create Foreign Table dialog box, select an acceleration method, and then click Submit.

      Entire project

      Category	Parameter	Description
      Acceleration Method	Select an acceleration method.	Hologres supports three acceleration methods: 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 MaxCompute schema. If your MaxCompute project has schemas enabled, specify any schema for which you have permissions. This parameter is not required for projects without schemas. Schema operations.
      Destination Table Position	Holo schema	The name of the schema. You can select the default public schema or a custom schema that you created.
      Advanced Settings	Processing Rule for Table Name Conflicts	Specifies how to handle a table name conflict. Valid values: Ignore, and continue creating other tables Update, and modify the existing table Report an error, and do not create the table
      	Processing Rule for Unsupported Data Types	Specifies how to handle an unsupported data type. Valid values: Report an error and fail the import Ignore and Skip Table of Unsupported Fields

      Selected tables

      Category	Parameter	Description
      Acceleration Method	Select an acceleration method.	Hologres supports three acceleration methods: 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 MaxCompute schema. If your MaxCompute project has schemas enabled, specify any schema for which you have permissions. This parameter is not required for projects without schemas. Schema operations.
      Destination Table Position	Holo schema	The name of the schema. You can select the default public schema or a custom schema that you created.
      Advanced Settings	Processing Rule for Table Name Conflicts	Specifies how to handle a table name conflict. Valid values: Ignore, and continue creating other tables Update, and modify the existing table Report an error, and do not create the table
      	Processing Rule for Unsupported Data Types	Specifies how to handle an unsupported data type. Valid values: Report an error and fail the import Ignore and Skip Table of Unsupported Fields
      	Search	You can perform a fuzzy search by table name. The search returns a maximum of 200 tables.

      Single table

      Category	Parameter	Description
      Acceleration Method	Select an acceleration method.	Hologres supports three acceleration methods: 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 MaxCompute schema. If your MaxCompute project has schemas enabled, specify any schema for which you have permissions. This parameter is not required for projects without schemas. Schema operations.
      	Table Name	Specifies the source table in the specified MaxCompute schema. You can use a table name prefix to perform a fuzzy search.
      Destination Hologres Table	Schema	The name of the schema. You can select the default public schema or a custom schema that you created.
      	Table Name	Specifies the name of the Hologres table used for query acceleration.
      	Destination Table Description	Specifies the description of the Hologres table used for query acceleration.

   3. After creating the foreign table, go to Metadata Management > Instances Connected in the left-side navigation pane. Find and double-click the foreign table. In the editor tab that opens, click Data Preview to verify that you can query the MaxCompute data.