Introduction to Teradata Aster Database Administration

This a note for Teradata Aster Basics 6.10 Exam a.k.a TACP(Teradata Aster Certified Professional).

Recommended courses are followings and this note is for the 2nd course.

nc_system schema holds system information.

3 categories of DD views

Replication Factor

Ganglia is a open source, web-based, scalable distributed system monitoring tool.

AMC Status

Aster Database only supports B-tree indexes, cannot enforce referential integrity.
There is no data sharing among Aster databases.

/*Change database*/
beehive=> \connect retails_sales;
retails_sales=>database beehive;


/*List database*/
beehive=> \l

/*Exit database*/
beehive=> \q

/*List schemas*/
beehive=> \dn

/*View tables in the PROD schemas*/
beehive=> \dt prod.*

/*View columns/data types*/
beehive=> \d prod.sales_fact

/*Show current schema*/
show search_path;
ALTER USER beehive SET SEARCH_PATH = 'public', 'mkt';

Select table by walking the path until finding the name

CONNECT privilege must be given to access the database. USAGE privilege must be given to access the schema.

Two Serial types: Global and Local.

PARTITION BY RANGE: START include the value but END exclude the value
partition sales_june (START'2017-06-01'::date END'2017-07-01'::date)

PARTITION BY LIST: If an incoming row doesn not fit into any partition, that row will not be loaded into the table

1. Data Modeling Quiz

Aster column name rules

Constraint Options

create table stuff
dept varchar DEFAULT 'none',
age smallint CHECK(age >= 18 and age <= 70),
name varchar
distribute by replication

Data Types

Supported data types of distribution

For large tables (> 1million rows, usually Fact table)
For small tables (<= 1million rows, usually Dimension table)

If ASH key and JOIN columns doesn’t match, SHUFFLE will occure.

TRUNCATE: Quickly remove all rows and it reclaims disk space immediately
VACUUM: Converts dead space into usable free space
VACUUM FULL ANALYZE: Physically rearrange the data on disk
NC_RELATIONSTATS: Generate various reports

2: Creating Tables Quiz
Tables in a Teradata Aster Database can be of which four variations? (Choose four.)

What data type is commonly used for “payload” columns? Click on the correct data type in the image.

In Teradata Aster, table data may be partitioned in which two ways? (Choose two.)
Logically Partitioned tables (Logical)
Fact tables(Physical)

How do these two partitioning types improve performance? Match the partitioning type to how it improves performance.
Physical: More v-Workers equal more parallelism
Logical: Reduced disk I/O by only reading needed partitions

Scenario: You join 2 FACT tables where the Hash column matches the JOIN column. Will a shuffling of data occur?
No, the JOIN will commence immediately since JOIN column values are guaranteed to be on the same v-Worker.

nCluster loader arguments

Default delimited format is TSV
-B and -E specify script name to execute it

Parallelizing the Load tier

Error logging is turned off by default. This means the load job will abort and rollback the data on encoutering the first error.

ncluster_export example

ncluster_export -h -d beehive -U beehive -w beehive
\"aaf\".\"accesslog\" myfile.txt

3: Data Loading Quiz
What is the name of the Teradata Aster Database bulk loading tool? ncluster_loader

Which two node types can handle Teradata Aster data loading ? (Choose two.)
Loader nodes and Queen node (if there are no loader nodes)

Which task do Loader nodes perform during loading?
Hashing the Distribution Key for v-Worker placement

The loading tier can be scaled in which three ways? (Choose three.)
Add more nCluster Loaders, Loader Nodes, Staging Machines

In addition to the nCluster Loader Tool, which four other types of tools are used to load a Teradata Aster Database? (Choose four.)
ETL Tools, SQL Statements, Connectors, Teradata QueryGrid (Aster-to-Hadoop, Aster-to-Teradata)

Final Exam