Building Your First SQL-GR Function (HelloWorld) - Aster Execution Engine

Teradata Aster® Developer Guide

Product
Aster Execution Engine
Release Number
7.00.02
Published
July 2017
Language
English (United States)
Last Update
2018-04-13
dita:mapPath
xnl1494366523182.ditamap
dita:ditavalPath
Generic_no_ie_no_tempfilter.ditaval
dita:id
ffu1489104705746
lifecycle
previous
Product Category
Software
This section explains how to use ADE to build and deploy the SQL-GR function HelloWorld. This function simply prints "HelloWorld!" in the SQL-MapReduce logs. In subsequent sections, you expand this function so that it outputs, if found, the path from the source vertex to the destination vertex, as specified in the calling SQL query. In the process, you learn many of the aspects of SQL-GR function implementation.

The HelloWorld function takes as input:

  • Two tables (a vertices table and an edges table)
  • Two arguments (source and destination)
  1. Launch Eclipse.
  2. Create a new project:
    1. Choose File > New > Aster Analytics Project.

    2. In the Project name field, enter the name of the project.

    3. Under Project Aster Database, click Use a specific Aster Database and, from the drop-down menu, choose the Aster Database where you plan on deploying your graph function.

      If no Aster Database is configured, click Configure Aster Database and add the Aster Database to use for running your graph function.

    4. Click Finish.

    The project appears in the Project Explorer panel.

  3. Add a SQL-GR function by selecting the new project and choosing File > New > Aster SQL-Graph Function.

  4. When the New Aster Data Function wizard appears:
    1. In the Introduction page, click Next.
    2. In the Name field, enter "HelloWorld" as the name of the function. Click Next.

    3. In the Argument clauses page, add these two arguments:
      • source (integer)
      • destination (integer)

    4. Click Next.
    5. In the Input Schema for SQL-GRAPH function page, add two input tables. In the Input Name field, specify the alias names of the two input tables (vtable and etable). These aliases are used in the SQL-GR query.
    6. Add an Integer vertex message schema definition.

    7. Add a character varying vertex message schema definition.
    8. Click Next.
      If you check the Enable distinct Vertex Message check box, SQL-GR sends only distinct messages to vertices. For example, if the message consists of one integer value, if multiple messages have the same value, only one message is passed; the remaining messages are discarded.
    9. In the Output Columns page, add one column: path (character varying).

    10. Click Finish.

  5. Add a println() statement under the first TODO marker inside the initializeVertex() function:
    public void initializeVertex(GraphGlobals globals, VertexState vertexState,
    ...
    	// TODO: do something with this row iterator, i.e. fetch certain columns value.
    	System.out.println("HelloWorld!");
  6. Build the project (Project > Build All).

    The HelloWorld.ZIP archive is added to the deployment folder.

  7. Deploy the function on your database:
    1. Choose Project > Properties.
    2. In the left panel, select Aster Development Environment > Aster Database.

    3. Click Configure Aster Database.
    4. Select the database on which to deploy the function.

    5. Click Info.

    6. Click Upload File.
    7. In the top field, enter the first letter of the function’s name (in this case "H") to display the available resources that start with "H".

    8. Select HelloWorld.zip.
    9. To start the deployment process, click OK.
  8. Run the function from a SQL-GR query:
    1. Open an SSH connection to your database.
    2. Start ACT and use the \dF command to make sure the HelloWorld.zip file is installed.
      beehive=> \dF
      List of installed files
      schemaname |    filename     | fileowner |         uploadtime
      ------------+-----------------+-----------+----------------------------
      ...
      public     | HelloWorld.jar  | beehive   | 2013-08-06 07:47:43.933803
      ...
    3. Create the input tables:
      drop table if exists edges;
      drop table if exists vertices;
      create table edges (eid int, src int, destn int) DISTRIBUTE BY HASH (eid);
      create table vertices (vid int, name varchar) DISTRIBUTE BY HASH (vid);
      insert into vertices values (1,'San Francisco'),(2,'San Carlos'),(3,'Sunnyvale'),(4,'Santa Clara'),(5,'Cupertino'),(6,'Mountain View'),(7,'Palo Alto');
      insert into edges values (1,1,2),(2,2,7),(3,7,6),(4,6,3),(5,3,5),(6,3,4),(7,5,4);
    4. To run the HelloWorld SQL-GR function, enter this query:
      select * from HelloWorld(
      on vertices as vtable partition by vid
      on edges as etable partition by src
      source(1) destination(4)
      ) order by 1;

      The output table in this case is empty.

      path
      ------
      (0 rows)
    5. Open AMC.
    6. In the Dashboard, in the Processes section, click SQL-MapReduce .
    7. Click the ID of the SQL-MapReduce statement you just executed.
    8. Click View Logs.

    9. In the log file, verify that you see "HelloWorld!" repeated 7 times (once per vertex).