Eclipse — The other Aster Data SQL client

By Mark Ott, Teradata Aster

When learning Aster Data, the first thing we did was logon to Aster Command Terminal (ACT) and start submitting queries.  This is typically done through a SSH client (like Putty).  To customize the ACT interface, I used the Putty options to change the background and foreground colors and change the font and font size.  Later on in the training, we were introduced to Eclipse, which provided additional functionalities that I found compelling.

This presentation will cover how to setup Eclipse to be an SQL client for Aster data and introduce a number of features it provides.   

Eclipse is advertised as a Development platform.  And sure enough, after installing the Aster plugin, you can get started writing JAVA code to create and install your own functions.  It works great and provides things like Debugging tools to sift through your code looking for those elusive bugs.  But it can also be used as an Aster SQL client.  Here’s how.

After installing Eclipse, the first step is to ensure you have the Data Source Explorer tab opened.  If you do not see it, go to Window\Show View\Data Source Explorer.  You’ll see the new tab in the upper left-hand corner.


Now it’s time to add our database.  All we have to do is point to Aster Data server and provide proper credentials.  Right click on Database Connections and from the dialog box, select: New\Generic JDBC.  In the Name: field supply a User friendly name (ie: Aster Beehive db), then click Next.  You will then see the following below window.   Here’s where it gets tricky since the URL field is not necessarily intuitive to fill out.  Use syntax similar to the follow and you should be OK:
After providing the Database name and the Username and Password credentials, you'll almost done.


But to ensure connectivity. click the Test Connection button.  If all is well, you’ll get back a successful PING and you should be in business.  Click the Finish button and you should see your DB connection in the Data Source Explorer tab.


At this point, you should be automatically Connected.  However if not, simply right-click on the database name and select Connect frm the Dialog box.

As you expand the hierarchical folders you see a listing of the Schemas for the beehive database.  Let’s spend some time here to get a feel for the SQL tables.

First expand out NC_SYSTEM.  There are a number of interesting objects here.


Notice you have 2 system tables: NC_ERRORTABLE_PART and NC_ERRORTABLE_REPL.  These tables are hard-coded at installation and provide a table to log errors when using theNCLUSTER_LOADER tool.  Why do we have 2 tables?  One is for loading FACT tables, the other for loading REPLICATION tables. Since each have a slightly different structure, we need distinct tables for each of them. 

Under the VIEWS object, you see a listing of the System Tables.  Also created during the installation process, these tables hold (for lack of a better word) some of the metadata information about the system.
Suppose you want to see all the Users on the system.  To do so it’s probably a good idea to first go into Database Development mode.  In the right hand pane click the icon highlighted by Red circle, then select Other, then click on Database Development, then click OK.  You should see a depressed button of the same name in the right-hand pane.  


Now to list your Users, in the left-pane right click on nc_all_users and from the Dialog box, select Data\Sample contents.  You’ll see your answer set in the bottom right-hand pane of Database Development.  You will sprout a new tab named SQL Results which keeps history of your queries and the output of your current query.  As you can see, I have a number of User accounts for the beehive database.


Navigating the tree structure, I next visit my PUBLIC schema where I have nested most of my User-created tables.  From here, I can re-constitute the DDL statement if needed (note it may not be a perfect clone but is suitable for most needs).  To do so, right-click on the SQL table, then select Generate DDL for the Dialog box.  Follow the Wizard prompts and you’ll eventually wind up with something like this. Pretty cool considering you cannot view the DDL from ACT (Note it's not a perfect clone of the original DDL as the 'DISTRIBUTE BY' clause won't show up) but it can still can come in handy if need to create a new table and want to use this code as Template).  And 'Yes', you can copy and paste the below DDL as well.


Of course you can quickly see the entire contents of a table by right-clicking too.  I use the EDIT option via the Dialog box.  Also note if you expand the table in the left-pane , Eclipse displays a list of all the Columns in that table and their respective data types.  Note this can be done in ACT using the \d <schema.tablename>.


But suppose you only want to view a partial listing of Rows or Columns.  In other words, you want to execute a typical query.  To do so, go to the button bar and select Scrapbook icon.


From here, type in your custom SQL code (no, you cannot drag and drop columns and table names from the left-pane window.  However,  most SQL keywords are automatically bolded). Then right-click on the code you wish to execute and select Executed Selected Text.


 A new tab name SQL Results sprouts in bottom right-hand pane with your answer set. 


Here’s another great feature.  I can be logged into 2 databases simultaneously and submit queries.  I just connected to another database (in this case, RETAIL_SALES) and then opened up a new Scrapbook and pointed to that database.  From there I can start writing queries.


Eclipse provides for both Export (Extract in Eclipse terminology) and Load of a table.  This is just a matter of right-clicking on the table of choice in the Data Source Explorer tab and from Dialog box choosing Load or Extract.  Both allow you to select a delimiter of your choice and worked flawlessly.  ACT does provide an Export capability (using \o command to export).  To do a bulk load, NCLUSTER_LOADER would probably be a much better performer, but note this utility is executed from a UNIX prompt and not from within ACT.


In conclusion

ACT is a great tool for executing your day-to-day queries.  It reminds me of Teradata's BTEQ client; it's quick and to the point.  However, one tool does not a carpenter make. 

Eclipse is a powerful addition to your toolbox to supplement the ACT client and provides additional capability you may find useful.  Let's recap.