All Products
Search
Document Center

Hologres:Connect to Hologres and query data

Last Updated:Jul 19, 2024

HoloWeb is a comprehensive platform that is built based on Hologres. In the HoloWeb console, you can manage databases and develop data in a visualized manner. This topic describes the basic operations that you can perform in the HoloWeb console.

Prerequisites

A Hologres instance is purchased. For more information, see Purchase a Hologres instance.

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 Public network icon 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 VPC icon 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. 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:

      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 新建sql查询 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.

      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.