Eclipse - The other Aster Data SQL client

Aster
Teradata Aster is an analytic platform that embeds MapReduce analytic processing with data stores. •Embedded MapReduce analytic processing and unique SQL-MapReduce® framework •Massively parallel data store for multistructured data •Intuitive tools and SQL-MapReduce libraries for rapid analytic development
Teradata Employee

Eclipse - The other Aster Data SQL client

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.

Before using Eclipse Data Source Explorer, you should get JDBC drivers to connect to Aster Beehive db. AsterData queen is bundled with JDBC drivers for various systems. These drivers can be found here:


aster@linux-qvsn:~/demo> ls /home/beehive/clients_all/

linux32  linux64  mac  solaris64  solaris-sparc  solaris-x86  third-party-licenses.html  win32  win64

Just open folder and copy jar file to your local machine where Eclipse is installed. I do use win64 so I've opened folder named win64 and copied file noarch-ncluster-jdbc-driver.jar

I've used WinScp to copy jar file. 

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.

Here is a way to find view named Data Source Explorer if you don't see it in a list.

Select Other...

Start to type Data Sou... and list will be shortened. 

Then right-click on Database Connections and select New...

Select Generic JDBC in the list

Now it's time to confgure this Generic JDBC driver.

Open JAR List and specify path to previously downloaded JDBC jar file. It's possible to take this file from queen node.

Then specify Driver class name.

Now it's time to fill other properties. Here is the JDBC url to AsterData DB:

                                                 jdbc:ncluster://192.168.100.100/beehive

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 the NCLUSTER_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.

Functionality

ACT

Eclipse

Schema and underlying tables

Limited. Provides ability to see all tables within 1 schema at a time within database using \d command

Can see all Schemas and underlying tables in multiple databases via Data Source Explorer tab GUI

Table’s columns and data types

Yes.  Using \d <schema.tablename> or      \d <schema.*>

Data Source Explorer tab GUI

Ability to see DDL

No

Yes

Keyword formatting (bold)

No

Yes

Export

Yes using coding as follows: Begin; create temp table Myexport as select * from trans; \o myfilename copy Myexport to stdout with delimiter ‘,’; \o; End

GUI Wizard using EXTRACT

Bulk Load

No, althoughNCLUSTER_LOADER available via UNIX prompt

GUI Wizard using LOAD, but typically only for small loads

Query History with Result set archive

No

Yes

DB connectivity

Limited.  Can connect to different database once login to another database using \connect <dbname user>.  But can only be connected to 1 db at a time

Can connect to multiple databases at a time.  Note cannot join between 2 different databases in either client

14 REPLIES

Re: Eclipse - The other Aster Data SQL client

How to install "Data Source Explorer" in eclipse? Thank you.
Teradata Employee

Re: Eclipse - The other Aster Data SQL client

You can install the Teradata plug-in for eclipse following the directions here: http://downloads.teradata.com/download/tools/teradata-plug-in-for-eclipse
Enthusiast

Re: Eclipse - The other Aster Data SQL client

"Data Source Explorer" is not a plugin, it's a so called "view" in Eclipse terminology. See the updated article. It's explained there how to open "Data Source Explorer".
Supporter

Re: Eclipse - The other Aster Data SQL client

greate! works also for mac :-)

Re: Eclipse - The other Aster Data SQL client

Hello, first, thanks for the great information on how to connect Eclipse. I'm able to connect to the DB but I'm unable to expand the hierarchy. There is not any "plus" sign next to the DB. This is a brand new implementation of Aster but does have a couple of new schemas and tables so I would think that the DB should have some hierarchy under it, especially the NC_System tables. Any tips?
Teradata Employee

Re: Eclipse - The other Aster Data SQL client

Try this: In the URL caption above, let's append the Port # to it.

So it will be: jdbc:ncluster://192.168.100.100/beehive:2406

See if this does the trick..

Re: Eclipse - The other Aster Data SQL client

Re: "I'm able to connect to the DB but I'm unable to expand the hierarchy. There is not any "plus" sign next to the DB."

Did you install Data Tools Platform (DTP)?  You will need to install this after you complete your Eclipse SDK installation

http://www.eclipse.org/datatools/

Re: Eclipse - The other Aster Data SQL client

Thanks! This is a great Article.

1) I initially had downloaded the Eclipse Standard Platform, which does not have the Data Management tool. Relaized I had to instead use the Eclipse IDE for Java EE Developers.

2) I had to upgrade my JRE, probabaly cause I was using a 64 bit version of Eclipse.

3) I had to specify the location of the javaw.exe file in the eclipse.ini file.

Thought I'd be nice to share my experiences

Re: Eclipse - The other Aster Data SQL client

Hi, I need to install ncluster_loader for importing files in asterdata, but I am not able to find ncluster_loader setup anywhere. Can you please help me with this.