Hologres:Hologres SQL

Procedure

1. Create a workflow.

   1. Log on to the DataWorks console by using your Alibaba Cloud account, select the region in which the Hologres instance resides, and then click Workspaces in the left-side navigation pane.

   2. On the Workspaces page, find the desired workspace and choose Shortcuts > Data Development in the Actions column to go to the DataStudio page.

   3. In the left-side navigation pane, click the icon.

   4. Move the pointer over Create and select Create Workflow.

   5. In the Create Workflow dialog box, configure the parameters.

      

      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 Create and choose Create Node > Hologres > Hologres SQL.

      Note

      You can create a Hologres SQL node only after you associate a Hologres data source with the DataWorks workspace. For more information, see Associate a Hologres instance with a workspace.

   2. In the Create Node dialog box, configure the Engine Instance, Name, and Path parameters.

   3. Click Confirm.

3. Perform data development by using the Hologres SQL node.

   Click the Hologres SQL node that you created, enter the following PostgreSQL statements in the code editor, and then click the icon.

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

   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;

Common errors and troubleshooting

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

  • Problem description

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

  • 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 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 instances. To view the information about the Hologres instances, click the icon to go to the Workspace Management page and click the Hologres tab in the Compute Engine Information section.