Building a Table and Loading Data in Aster Express (bank_web_clicks)

Document created by John Thuma on Nov 27, 2014Last modified by John Thuma on Mar 31, 2015
Version 8Show Document
  • View in full screen mode

This article will take you through building a table using the Aster Command Tool (ACT) or Terdata Studio and then loading data using nCluster_Loader tool.  It is recommended that you take a look at the following articles prior to reading this document:

 

Using Aster Command Tool (ACT) with Aster Express

Using Teradata Studio with Aster Express

 

If you are familiar with DDL or Data Definition Language then some of this material will seem simple, but there are definitely some Aster specific concepts that will be covered.

 

The great news is that if you are already familiar with ANSI SQL you are 95% familiar with how to build a data object in Aster.  The rest of the statement contains Aster specific predicates that help you define the object and how it lands data into the Aster system.

 

The script below is what we are going to use to develop our table: public.bank_web_clicks.  The statements will be decomposed and articulated for your understanding.  Colors have been applied to the Aster specifics to understand.  The rest of the command is pure ANSI SQL.

 

If you already understand ANSI SQL and ASTER SQL then open up a PuTTy session and run the following commands.

 

-----------------------------------------------------------------------------------

-- DDL: public.bank_web_clicks

-----------------------------------------------------------------------------------

CREATE TABLE public.bank_web_clicks  (

customer_id INTEGER NULL,

session_id  INTEGER NULL,

page        VARCHAR(100) NULL,

datestamp   TIMESTAMP WITHOUT TIME ZONE NULL

)

DISTRIBUTE BY HASH (customer_id)

STORAGE ROW

COMPRESS LOW;


*NOTE:  The author prefers to use UPPER case for all ANSI SQL and ASTER SQL reserved words.  Lower case words are used for specific object names and references.  This is a matter of style and readability.

 

DROP TABLE will delete all the data and the definition of the table from Aster.  This is a very powerful command and should be thought through before executing.  If the table is not backed up the table and its content are gone forever.

 

--DROP TABLE IF EXISTS  public.bank_web_clicks;

 

The CREATE TABLE statement builds a table in Aster that can contain data.  It is made up of fields such as: customer_id, session_id, page, and datestamp.  Fields have data types:  customer_id is type integer and allows NULL values.  The page field is type VARCHAR, or alpha numeric values.

 

The unique predicates in the statement below are DISTRIBUTE BY HASH, STORAGE ROW, and COMPRESS LOW.  See below for details of how these statements work and change a tables behavior and characteristics.

 

-----------------------------------------------------------------------------------

-- DDL: public.bank_web_clicks

-----------------------------------------------------------------------------------

CREATE TABLE public.bank_web_clicks  (

customer_id INTEGER NULL,

session_id  INTEGER NULL,

page        VARCHAR(100) NULL,

datestamp   TIMESTAMP WITHOUT TIME ZONE NULL

)

DISTRIBUTE BY HASH (customer_id)

STORAGE ROW

COMPRESS LOW;

 

 

DISTRIBUTE BY HASH (customer_id)

 

The required* DISTRIBUTE BY clause specifies either hash distribution (with a distribution key constraint) or replication.   In our example above we are using HASH distribution.  To use HASH distribution you are telling Aster to spread data out across the collection of computers by one of the field/columns in the table.  That field/column in our example is: customer_id.

 

Another option for this would be DISTRIBUTE BY REPLICATION.  This tells Aster to build the table thats data will be located on each node of the cluster.  This can get expensive as we do not want to have a very large table located on every node.  So a good rule of thumb is for very large tables, say more than 1 million records, we use a DISTRIBUTE BY HASH, otherwise we use a DISTRIBUTE BY REPLICATION.

 

*NOTE:  If you do not specify a DISTRIBUTE BY clause it will default to REPLICATION.

 

STORAGE ROW

 

CREATE TABLE statement accepts the STORAGE ROW or STORAGE COLUMN flag to specify

  • STORAGE ROW indicates the table will be a traditional row-wise table. This is the default.
  • STORAGE COLUMN indicates the table will use a column-oriented storage layout.

 

COMPRESS LOW;

 

Aster Database allows you to compress tables to four degrees: HIGH; MEDIUM; LOW; and none. You can compress using the COMPRESS keyword when you create it with CREATE TABLE, or later using the COMPRESS action of ALTER TABLE. Compression is not supported for temporary tables.

 

 

Building the Table using Teradata Studio:

 

Open Teradata Studio and cut and paste the following CREATE TABLE statement in tthe SQL Editor and run the query: (Your screen should look like the one below):

 

ddlbuild.PNG

 

If you would rather build the table using Aster Command Tool (ACT) do the following:

 

Open PuTTY and start a session with the queen:  Host Name: 192.168.100.100  /  Port:22   Click: Open

 

putty.PNG

 

Login to the queen using (Login as: aster  / Password: aster)

 

loginssh.PNG

 

At the Linux command prompt enter the following to log into Aster Command Tool: (ACT)

act -U db_superuser -w db_superuser -d beehive

 

actlogin.PNG

 

Cut and paste the following CREATE TABLE Statement into ACT

 

actddl.PNG

 

Your table is created.  Close your PuTTY session window.

Attachments

    Outcomes