Hologres is compatible with PostgreSQL and can be connected by using most development tools or business intelligence (BI) tools that are supported by PostgreSQL. You can use a tool with which you are familiar to build a real-time data warehouse for your enterprise. This topic describes how to use the PostgreSQL client to connect to a Hologres instance and use standard PostgreSQL statements for data development.

Install the PostgreSQL client

Before you use the PostgreSQL client, you must download the client from the official website of PostgreSQL. If you have installed the PostgreSQL client, skip the steps described in this section. To install the PostgreSQL client, perform the following steps:

  1. Download the PostgreSQL client.
    Visit the official website of PostgreSQL, download the installation package of version 11 or later based on your operating system, and then install the client as prompted.
  2. Set environment variables.
    • If you use the Windows operating system, perform the following operations:
      1. Open the System Properties dialog box. On the Advanced tab, click Environment Variables in the lower-right corner.
      2. Set the Path variable to the bin subdirectory of the installation directory.
      3. Click OK.
    • If you use the macOS operating system, you do not need to set environment variables in most cases. If you need to set environment variables, see Setting Up Your Environment.

Connect to a Hologres instance for data development

After you download and install the PostgreSQL client, you can use the client to connect to a Hologres instance for data development.

  1. Connect to Hologres.
    Open the PostgreSQL client and execute the required SQL statements based on your operating system to connect to Hologres. The syntax of the SQL statements is the same as that used to connect to a PostgreSQL database.
    • Execute the following SQL statement if you use the Linux operating system:
      psql -h <Endpoint> -p <Port> -U <AccessKey ID> -d <Database>

      Enter the AccessKey secret of your Alibaba Cloud account as prompted.

      LIN
    • Execute the following SQL statement if you use the macOS operating system:
      PGUSER=<AccessKey ID> PGPASSWORD=<AccessKey Secret> psql -p <Port> -h <Endpoint> -d <Database>
      linux
    • Execute the following SQL statements if you use the Windows operating system:
      Server [localhost]: Endpoint
      Database [postgres]: Database
      Port [5432]: Port
      Username [postgres]: <AccessKey ID>
      Password of the <AccessKey ID> user: <AccessKey Secret>
    ParameterDescription
    AccessKey ID
    • Alibaba Cloud account: the AccessKey ID of your Alibaba Cloud account. You can obtain the AccessKey ID from the Security Management page.
    • Custom account: the username of the custom account. Example: BASIC$abc.
    AccessKey Secret
    • Alibaba Cloud account: the AccessKey secret of your Alibaba Cloud account. You can obtain the AccessKey secret from the Security Management page.
    • Custom account: the password of the custom account.
    PortThe public port number of the Hologres instance.

    Example: 80.

    EndpointThe public endpoint of the Hologres instance.

    Example: xxx-cn-hangzhou.hologres.aliyuncs.com.

    DatabaseThe name of the Hologres database.

    After you purchase a Hologres instance, a database named postgres is automatically created.

    You can connect to the postgres database. However, limited resources are allocated to this database. We recommend that you go to the Hologres console and create a database for business purposes. For more information, see Create a database.

    Example: mydb.

    Examples

    • If you log on to the Hologres console with an Alibaba Cloud account, execute the following statement to use the PostgreSQL client to connect to a Hologres instance:
      PGUSER="xxx" PGPASSWORD="xxx" psql -h hgpostcn-cn-xxx-cn-hangzhou.hologres.aliyuncs.com -p 80 -d demo
      Logon with an Alibaba Cloud account
    • Log on to the Hologres console with a custom account
      • Assume that the username of the custom account is abc, as shown in the following figure. Logon with a custom account
      • Execute the following statement to use the PostgreSQL client to connect to a Hologres instance:
        PGUSER="BASIC\$abc" PGPASSWORD="xxx" psql -h hgpostcn-cn-xxx-cn-hangzhou.hologres.aliyuncs.com -p 80 -d demo
        Logon with a custom account
    Note You can also use a development tool with which you are familiar, such as DataWorks or HoloWeb, to connect to Hologres for data development. For more information, see Get started with DataWorks or HoloWeb quick start.
  2. (Optional) Create a database.
    After you purchase a Hologres instance, a database named postgres is automatically created. However, limited resources are allocated to this database. We recommend that you use this database for O&M management and create another database for business purposes.
    Note If you have created a database for business data in the Hologres instance, skip this step.
    • Syntax:
      CREATE Database <DatabaseName>;
      Replace DatabaseName with the name of the database that you want to create.
    • Example:
      -- Create a database named test. 
      CREATE Database test; 
  3. Develop data.
    You can execute standard PostgreSQL statements to develop data by using the PostgreSQL client.
    For example, you can execute the following SQL statements to create a table in the database and write data to the table:
    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;
    You can develop data in different ways based on your business requirements. Examples: