Gregory Bethardy

Loading Data from A CSV File using Aster AppCenter

Blog Post created by Gregory Bethardy Champion on Feb 2, 2016

Introduction:

 

In this blog, we will demonstrate how to use AppCenter java code to load data from a CVS file on the AppCenter server into an Aster table.

 

Aster AppCenter can be used for automating the analytic work flow of your business use-cases. It is applicable to all types of multi-genre analytic work flows.  This involves creating one, or a series of apps that include step for data loading, exploration, modeling, and visualization. These can be run manually on-demand, or scheduled in AppCenter.

This is great way to give AppCenter users, such as business analysts and data scientists, the ability to manually run, or schedule, data loads in environments where they don’t have shell access or some other method available.

 

 

 

 

 

 

 

Demonstration Set-up:

 

The logic for reading, parsing and inserting data from a CSV file must be implemented in Java.  A number of useful methods are exposed that can be overridden with the logic you need for your data load application. In this simple demo only the code in onLaunch method has been modified.  This code reads the CSV file, parses the data, inserts it into an Aster table, and presents a table visualization of the loaded data.  For convenience, the sample app is attached to this blog and a code listing appear in the appendix at the end

 

 

Table 1 : AppCenter methods


Method Name


When invoked

Usage

onLaunch

called by the AppCenter framework when a new job is requested by the user

method is where main App logic resides

onInstall

called by the AppCenter framework when initially installed

can be used to create necessary schemas, etc

onUninstall

called by the AppCenter framework when the App is uninstalled

can be used to cleanup data

onError

called by the framework in two cases:

  • onLaunch fails
  • job is cancelled when in onLaunch
can be used for cleaning up all the tables or views if error has occurred in onLaunch




The set-up for this demonstration involves the following steps:


1. Create a CSV file on the AppCenter Server and set the permissions.  Here Aster Express is used and AppCenter is running on the queen.




2. Create a table in Aster with the same fields as the CSV file.


drop table if exists public.test;





create table public.test ( 

  f1 numeric 

  ,f2 character varying(10)

  ,f3 character varying(10)           

              )

Distribute by hash (f1);








3. Create an AppCenter application

  • On the App Info tab, select the check box for Portable




 







  • Implemented the logic in java





  • On the Input Tables tab add the table from the step above and it’s columns





  • Configure the application and select (or create) the Database Connection.





  • Save and run the application.



  • Verify that the data has load by viewing the table visualization

 

 

 

 

Conclusion:

 

This concludes the demonstration of how to use AppCenter java code to load data from a CVS file on the AppCenter server into an Aster table.

 

The sample code presented here can be modified to parse through many types of files and formats.

 

Remember, this is applicable to all types of multi-genre analytic work flows.  It is great way to give AppCenter users, such as business analysts and data scientists, the ability to manually run, or schedule, data loads in environments where they don’t have shell access or some other method available.

 

Let us know how you have used AppCenter in automating your business analytics work flows!

 

 

 

 

Appendix 1: Java Code Listing For AppCenter Load Demonstation


package com.teradata.asa.apps;


import com.teradata.aster.app.sdk.*;

import java.io.*;

 

public class MyApp implements AppAPI {

  private static final String QUOTE_STRING = "\"";

 

  /**

  * This method is where main App logic resides. It is called by the

  * frame work when a new analytic job is requested by the user.

  * @param ctx

  *    Context of Job currently running. Provides access to AppCenter utilities

  * @throws AppException

  */

  @Override

      public void onLaunch(AppContext ctx) throws AppException {

      String inFile = "/home/aster/test.txt";

      BufferedReader buffer_read = null;

      String line = "";

      String cvsSplitBy = ",";

      //System.setSecurityManager(null);

         

      //Get input table

      TableSchema schema_input = ctx.getTable("test");

      String inputTable        = schema_input.getActualTableName();

      String f1                = schema_input.getActualColumnName("f1");

      String f2                = schema_input.getActualColumnName("f2");

      String f3                = schema_input.getActualColumnName("f3");

 

      // Read the lines of the file, parse, and run SQL insert

      try {

          buffer_read = new BufferedReader(new FileReader(inFile));

          while ((line = buffer_read.readLine()) != null) {

                // use comma as separator

                String[] field = line.split(cvsSplitBy);

 

                //insert query

              String insertQuery = "insert into "+ inputTable +" values ("+ field[0]+", '"+ field[1]+"', '"+ field[2]+"');";

              ctx.runSqlStatement(insertQuery);

          }

 

      } catch (FileNotFoundException e) {

          e.printStackTrace();

      } catch (IOException e) {

            e.printStackTrace();

      } finally {

            if (buffer_read != null) {

                try {

                    buffer_read.close();

                } catch (IOException e) {

                    e.printStackTrace();

                }

            }

      }

      // end try

   

      //Visualize result as a table

      ctx.visualizeTable(inputTable, "Table viz");


  } 

 

 

  /**

  * Called by the framework when initially installed. May be used to

  * create necessary schemas, etc.   

  * @param ctx

  * Context of a Job that is currently running. Provides access to AppCenter utilities and services.

  */

  @Override

      public void onInstall(AppContext ctx) throws AppException {

         

  }

 

 

  /**

  * Called by the framework when the App is uninstalled.

  * May be used to cleanup data, depending on license terms

  * @param ctx

  *    Context of Job currently running. Provides access to AppCenter utilities

  */

  @Override

      public void onUninstall(AppContext ctx) throws AppException {

         

  }

 

 

  /**

  * May be used for cleaning up tables/views if error has occurred in onLaunch.

  * Called by the framework in two cases:

  * 1. If onLaunch fails.

  * 2. Job is cancelled when in onLaunch.

  * @param ctx

  *    Context of Job currently running. Provides access to AppCenter utilities

  * @param e Exception that occurred in onLaunch

  */

  @Override

      public void onError(AppContext ctx, Exception e) throws AppException {

 

  }

 


  /**

  * This method is used to add double quotes it is used for the below reasons

  * Preserve case of object names in sql statements and ctx.visualiza api's

  * @param text the string that requires quotes

  */

      private static String addQuotes(String text){

        if (text==null || text.equals(""))return text;

        String [] splits = text.split("\\.");

        if(splits.length > 2){ //schema.tableName

              throw new IllegalArgumentException("Invalid table name: " + text);

        }

        StringBuilder finalName = new StringBuilder();

        boolean first = true;

        for(String split: splits){

              if(!first){

                    finalName.append(".");

              }

              if (split.startsWith("\"") && split.endsWith("\"")){

                    finalName.append(split);

              }

              else{

                    finalName.append(QUOTE_STRING+split+QUOTE_STRING);

              }

              first = false;

        }

        return finalName.toString();

  }

}

Outcomes