Using Aster Express: ACT 1

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

Using Aster Express: ACT 1

Once you have an Aster cluster running (see Getting Started with Aster Express), it's time to start doing some work.  We'll start with the Aster database basics in this first part of a 3 part overview.

ACT on Aster

The foundation of the Aster platform is a parallel database, so a good starting point is to show some simple database examples.  To demonstrate this, we'll use Aster's command line query tool, ACT, to connect to the cluster through the Queen node, and then submit SQL queries.  The Queen node is the entry point for connecting to the Aster cluster.

We have a few options for where we can run this tool.  The easiest is to work directly on the Queen node and run ACT there in a terminal shell.  ACT is included with the Aster software already installed on the cluster nodes and so no additional installation is required.  The Aster tools, including ACT, can also be installed on a remote client workstation and run from there with connection back to the cluster using the Queen's address, 192.168.100.100.  This is a very common configuration for Aster customers connecting to their production cluster.  These client tools will be the topic of a future Developer Exchange article, along with a download package of the tools for Windows client workstations.

The other common option used is to connect to the Queen node (192.168.100.100) from a remote terminal shell using SSH (If you are using a Windows PC, do a web search for PuTTY, a great and free SSH utility).   This is usually my preferred method of working on the Queen, its seems a bit more light-weight than using the Linux desktop.  Generally, I start up my Queen and Worker image and then leave them running in the background without ever logging onto them directly.  This more closely resembles a production environment where all connections are remote; SSH sessions for direct access and management using a web browser and the Aster Management Console (AMC).

When using the remote options, we'll need to specify the database connection parameters because Aster is no longer running locally and ACT defaults to 'localhost'.

In this example, I'm passing the host, dbname, user and password parameters to ACT:

         act --host 192.168.100.100 --dbname beehive --username beehive --password beehive

(Tip: starting ACT with the "--help" option will list all the startup parameter options.)  

Again, the remote options are closer to what you would do in an actual Aster cluster in a datacenter, but for simplicity, using the Aster Express Queen node directly is a reasonable choice for Aster Express and is how we'll connect in our examples.

Working on the Queen

To work directly on the Queen node itself.  Simply log into the Queen's desktop using the user/password "aster/aster". 

Double-click on the 'GNOME terminal' icon to open a shell window, and then stretch it a bit to give us some room to type and read the results.

We start by running the ACT program.  At the prompt, type "act" and the ENTER key. 

You'll be prompted for a password, use "beehive".  (Queens, Workers, beehive....starting to see a theme here).

As simple as that, we're connected to the Aster database and can start submitting commands and SQL queries.  In this example, most of the connection details were hidden. 

By default, ACT will try to connect to the localhost address, with the user "beehive".  All we needed to add was the password, which is also "beehive".

For a quick list of the ACT command options, type "\h" at the prompt, as shown here:

Next I'll demo some very basic SQL with Aster.

Hello Aster

OK, we're ready, let's issued our very first Aster query!

That was easy, but we can do better. Here’s a better SQL example that includes creating our first Aster table.

Here are the SQL statements we'll use:

CREATE TABLE hello
(
id INT NOT NULL,
message VARCHAR(1000)
)DISTRIBUTE BY REPLICATION;

INSERT INTO hello(id, message) VALUES(1,'Hello Aster');

SELECT * FROM hello;

With these, we create our first Aster table, insert a row and run a query.  Later on in our tutorials we'll dig more into the 'DISTRIBUTE BY REPLICATION' syntax. (It's how Aster knows how to distribute the data among the cluster's Worker nodes).

So there it is - Hello Aster!  We've reached the end of ACT I.  Hopefully you've seen how easy it is to use Aster just as you would any other database.  In the next article, Using Aster Express: ACT 2, Loading Data, I'll show you how to load data and run some more complex SQL queries and start leveraging Aster's SQL-MapReduce capabilities.

Tags (2)
6 REPLIES

Re: Using Aster Express: ACT 1

Hey Mike ... I have been playing around with the express tool and I tried to load some data from teradata, but I am getting the error:

ERROR: column "queryid" specified in the distribution key does not exist in the SELECT list

I know for sure the queryid exist in my select list from the teradata table that I am trying to load ... any idea? Feel free to email me at admin.dataxpert@gmail.com

Thanks!

Re: Using Aster Express: ACT 1

Hi, I got an error:
ERROR: There are currently not enough vworkers available to execute queries. The cluster may need to be activated through the AMC. Contact your database administrator for further assistance.

while trying to run DDL query.
Can you tell me please what do I have to do?
Teradata Employee

Re: Using Aster Express: ACT 1

The "not enough workers" message usually means that the cluster is in an inactive state (this may happen when you return from a hibernate state with the virtual images). The quick remedy is to log into the web management console (AMC). Under the 'Admin/Cluster Mgmt' tab simply click the 'Activate' button.

Re: Using Aster Express: ACT 1

Hii Mike 

I am following  your tutorial to hands on ASter and learn its feature. But i have a problem with Queen virtual machine step 

When i entered ACT command 

and came  to password promt, i corruectly provided the  password you have given. 

However WM gave contact terra error and held process

could you please inform me on what the problem is 

Teradata Employee

Re: Using Aster Express: ACT 1

Hi Mike

Can I use mutiple SQL querie using ACT command in the shell program(.sh)?

For Example, ' This is question area' section below sample.sh

****sample.sh***********

#!/usr/bin/bash 

Data_Dir=/tmp/test/sh_test

Data_File=${Data_Dir}/sample.dat1

SData_File=${Data_Dir}/sample.dat

FData_File=${Data_Dir}/sample_f.dat

Log_Dir=/tmp/test/sh_test

Log_File=${Data_Dir}/sample.log

#============================================================

Export_Job()

{

act -d secsrv -U user1 -w aster -t -o ${Data_File}  --redirect-session-log ${Log_File} -c "SELECT

  CENTER_CODE,CENTER_NAME,BRANCH_CODE,BRANCH_NAME FROM    SRC.DMTD002 Limit 10;" -F  ' '

#This is OK

sed -e '$d' ${Data_File} > ${SData_File}

}

Export_Job

while read A B C D

do

#************* This is question area**************** 

act -d secsrv -U user1 -w aster -t -o ${FData_File}${A} --redirect-session-log ${Log_File}  -F  ' ' -c

"

BEGIN;

Insert into test.test_ab as

SELECT

  CENTER_CODE,

 CENTER_NAME,

 BRANCH_CODE,

 BRANCH_NAME 

FROM    SRC.DMTD002 

Where CENTER_CODE = '${A}'";

Delete from test.test_cd

where CENTER_CODE = '${A}'";

done <  ${SData_File}

N/A

Re: Using Aster Express: ACT 1

HI ALL 

I'm zaky , 

in here i want to question for query in Aster , the question is , 

how to the statement query LIKE in aster ? 

thanks for advice