Hologres:Hologres SQL

Procedure

1. Create a workflow.

   1. Log on to the DataWorks console with your Alibaba Cloud account. Select the region where your Hologres instance is located, and then click Workspace in the navigation pane on the left.

   2. On the Workspaces page, click Enter > Data Development in the Actions column of the target workspace to go to the DataStudio page.

   3. In the left navigation bar, click the icon to go to the Data Development page.

   4. Hover over Create in the top menu bar and click Create workflow.

   5. In the Create workflow dialog box, configure the following parameters.

      Parameter	Description
      Workflow name	Enter a name for the workflow.
      Description	Enter a description for the workflow.

   6. Click Create.

2. Create a Hologres SQL node

   1. On the Data Development page, hover over Create in the top menu bar and choose Create node > Hologres > Hologres SQL.

      Note

      To create a Hologres SQL node, you must first bind a Hologres data source to the current DataWorks workspace. For more information, see Bind a Hologres instance.

   2. In the Create node dialog box, select an engine instance, enter a Name, and select a Path.

   3. Click OK.

3. Develop with Hologres

   Open the new Hologres SQL node, enter the following standard PostgreSQL statements, and then click the icon.

   The following code creates a table named supplier_holo, inserts data into it, and then queries the table. This demonstrates a simple development workflow in Hologres.

   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

• Engine information not displayed in the SQL editor

  • Symptom

    When you select an engine instance for Hologres development, the SQL editor does not display engine instance information.

  • Cause

    The engine instance information is hidden.

  • Solution

    In the upper-left corner of the Data Development page, click the icon and deselect Hide engine instances.

• Which specific Hologres instance does the Hologres engine instance in the SQL editor window refer to?

  Hologres engine instance is the display name of the Hologres instance. Click the icon in the upper-right corner to go to the Workspace Management page and view the corresponding Hologres instance information in the Hologres binding list.

• When you right-click a Hologres > Table node and select Import Table on the Data Development page in DataWorks, the names of created Hologres tables are not displayed.

  You must collect metadata from Hologres by using Data Map before you can import the table. For more information about metadata collection, see Data Map.