Hologres SQL is an SQL editor in DataStudio. You can write SQL statements to perform operations on Hologres instances and implement interactive queries within seconds by using Hologres SQL. This topic describes how to use Hologres SQL to perform operations on Hologres instances in DataStudio.

Prerequisites

  • An Alibaba Cloud account is created.
  • A Hologres instance is purchased and associated with a DataWorks workspace.
  1. Create a workflow.
    1. Log on to the DataWorks console by using your Alibaba Cloud account, select the region where the Hologres instance resides, and then click Workspaces in the left-side navigation pane.
    2. On the Workspaces page, find the required workspace and click Data Analytics in the Actions column to go to the DataStudio page.
    3. In the left-side navigation pane, click the DataStudio icon.
    4. Move the pointer over the Create icon and click Workflow.
      Workflow
    5. In the Create Workflow dialog box, set the parameters.
      Create Workflow
      Parameter Description
      Workflow Name The name of the workflow.
      Description The description of the workflow.
    6. Click Create.
  2. Create a Hologres SQL node.
    1. In the Scheduled Workflow pane, move the pointer over the Create icon and choose Hologres > Hologres SQL.
      Hologres SQL
    2. In the Create Node dialog box, set the Node Name and Location parameters.
      Create Node
    3. Click Commit.
  3. Perform operations on the associated Hologres instance.
    Click the Hologres SQL node that you create, enter the following PostgreSQL statements in the editor, and then click the Run icon.

    The following statements are used to create a table named supplier_holo, insert data into the table, and query the data:

    BEGIN;
    CREATE TABLE supplier_holo (
     s_suppkey bigint NOT NULL,
     s_name text NOT NULL,
     s_address text NOT NULL,
     s_nationkey bigint NOT NULL,
     s_phone text NOT NULL,
     s_acctbal bigint NOT NULL,
     s_comment text NOT NULL,
    PRIMARY KEY (s_suppkey)
    );
    CALL SET_TABLE_PROPERTY('supplier_holo', 'bitmap_columns', 's_suppkey,s_nationkey,s_acctbal,s_name');
    CALL SET_TABLE_PROPERTY('supplier_holo', 'dictionary_encoding_columns', 's_name,s_address');
    CALL SET_TABLE_PROPERTY('supplier_holo', 'time_to_live_in_seconds', '31536000');
    COMMIT;
    
    INSERT INTO supplier_holo VALUES 
    (1, 'Supplier01', 'New York', 17, '27-918-335-1736',  575594, 'careful'),  
    (6, 'Supplier06', 'London', 14, '24-696-997-4969',  136579, 'final accounts '),
    (10, 'Supplier03',  'Beijing', 24, '34-852-489-8585', 389191, 'ing waters'),  
    (18, 'Supplier04', 'Paris', 16, '26-729-551-1115', 704082, 'accounts snooze'),  
    (39, 'Supplier05', 'Shanghai',  8, '18-851-856-5633 611565', 88990, 'special packages'),  
    (48, 'Supplier06',  'Canada', 14, '24-722-551-9498',563062, 'xpress instructions affix');  
    
    SELECT * FROM supplier_holo;

FAQ

  • Why is information about Hologres engine instances not displayed in the SQL editor?
    • Problem description

      When you use the SQL editor, information about Hologres engine instances is not displayed in the SQL editor.

    • Possible cause

      The system hides the instance information.

    • Solution

      In the upper-right corner of the Scheduled Workflow pane, click the显示方式 icon and clear Hide Engine Instances.

  • How can I view the information about the Hologres engine instances that correspond to the names in the Engine Instance Hologres drop-down list?

    The names in the Engine Instance Hologres drop-down list are the display names of Hologres engine instances. To view the information about the Hologres engine instances, click the Workspace Manage icon icon to go to the Workspace Management page and click the Hologres tab in the Compute Engine Information section.