What is Teradata?
Teradata is a scalable relational database management system developed by the Teradata Corp. It is an open processing system that supports parallelism. It used for the development of data warehousing applications.
Some of its popular features are:
- Parallelism
- Scalability
- Supports SQL
- Good connectivity
- Data loading and unloading capabilities
What are some important components of Teradata?
Important components of Teradata are:
- Parsing engine
- Node
- Access Module Processor
- Message parsing layer
How Teradata works?
In Teradata, the parsing engine reads the queries and sends the client requests to the Message passing layer. This layer gathers the execution plan from the Parsing engine and sends it to the Access Module Processor.
Upon receiving the execution plan and data to work on, AMP performs sorting, aggregation, type conversion etc. and stores the data in the disks.
How is Teradata different from other databases?
Teradata is different from other databases in the following ways:
- It is developed using open architecture; enhanced devices can be incorporated into the architecture for better results.
- While handling larger databases, it increases the number of nodes to enhance performance.
- Large database operations can be executed in parallel.
- It has enhanced functionalities like MultiLoad, TPT, FastExport, and FastLoad.
- Data can be distributed to the disks automatically.
Why is Teradata stock dropping?
The stocks of Teradata are dropping due to their new offerings to the customers that have not been received well.
The company is looking forward to shifting its entire architecture to the cloud and is providing new subscriptions to its customers. But this huge investment has not been beneficial and hence the downfall in the stock prices.
What is Spool Space in Teradata?
During query processing in Teradata, the temporary results of the query are stored in the spool space. It is used to decide the maximum amount of space that a user can utilize for query execution.
Note: Without spool space, the queries cannot be executed.
How to use Teradata?
Teradata is an open processing system that supports parallelism. It is used for the development of data warehousing applications. It can work as a single data point from where many client operations can be operated.
Large database operations and tasks can be executed in parallel, making their execution time shorter.
What is DBQL in Teradata?
DBQL (Database Query Log) is used to store the records of queries along with their duration, activity and performance information.
What is a product join in Teradata?
In Teradata, product join compares all of the rows from two tables based on certain criteria. Here one row of a table can be joined to many rows of another table.
This type of join takes place due to the following conditions:
- More than one join conditions in the SQL statement
- Incorrect table aliases
- Equality condition is not based on the join condition
What is the identity column in Teradata?
A column can be set as the Identity column in a Teradata table that cannot be altered. Using a unique value generated by the system, these columns are used to ensure that the rows are unique.
They are very useful when several tables are combined and avoids over-processing of the data.
Why is Teradata not used for OLTP?
Teradata is not used with OLTP due to the locking behaviour of the Teradata Database. It does not support row-level locking like OLTP.
Explain various table types supported by Teradata?
The different table types supported by Teradata are:
- Permanent table – These are used for permanently storing data in the tables.
- Volatile table – These are used for storing the data during a session. After the session is completed, the table is deleted or dropped along with the data.
- Derived table – These tables are used for storing the results of the queries that are being executed or are already executed.
- Global temporary table – These are used for storing the values that are used global values used throughout the application.
Name some ETL tools used by Teradata?
Some common ETL tools are Informatica ETL tool, BTQMain, FastLoad and MLoad that can be used with Teradata.
Explain UPSERT and TENACITY command in Teradata?
- UPSERT – This command is used to update and insert data while database operations. If the row that needs to be updated is missing, then it is inserted.
- TENACITY – This command is a feature of the FastLoad that specifies the amount of time that FastLoad used to log in. It is the total waiting period that Teradata needs to create a new connection.
What is PDE in Teradata?
PDE (Parallel Database Extension) is a layer residing on top of the operating system and the Teradata database that enables parallel execution of the tasks.
Some of the most popular PDE tools are:
- ampload
- cnstrerm
- awtmon
- csppeek
- tsklist
- puma
- vconfig.
PDE also allows Teradata to debug the database and the Kernel of the OS.
What is the difference between Oracle and Teradata?
The differences between Oracle and Teradata are given below:
| Teradata | Oracle |
| It is based on the Shared Nothing architecture where the disk storage and memory storage are not shared across the processors. | It is based on the Shared Everything architecture where the data is distributed across all servers. |
| It is linearly scalable and fresh nodes can be included in the infrastructure. | It is not as scalable as Teradata. |
| It supports unconditional parallelism, the tasks are executed faster as multiple processors are working together to achieve a result. | It supports conditional parallelism and is slower than Teradata. |
| It runs on UNIX, LINUX, and Windows and does not support Mac OS X | It runs on all major operating system like UNIX, LINUX, Windows and Mac OS X. |
| It does use many indexes. | It relies on the indexes due to OLTP. |
What is an AMP in Teradata?
AMP (Access Module Processor) handles the file tasks and database operations. It receives the execution plan and data from the Parsing engine, and performs the data manipulation tasks like aggregation, filtering, sorting and data type conversion.
How to check stats on a table in Teradata?
In Teradata, the statistics on a table can be viewed using the following command:
HELP STATISTICS tableneme.
Explain Teradata Dispatcher?
The dispatcher is a component of Teradata that is used during query execution. When the query goes through the Parser, Session handler and Optimizer, the dispatcher forwards it to the AMP. It uses the BYNET to forward the query.
What is the Primary Index in Teradata?
Each data table in Teradata consists of one primary index in at least one column. It specifies where the data resides and which AMP will get the row. During the table creation, the primary index is created and it cannot be altered. To make any modifications to the primary index, the table has to be deleted or dropped and recreated.
What is the difference between the primary index and primary key in Teradata?
The differences between Primary index and Primary key in Teradata are given below:
| Primary index | Primary key |
| The primary index is always unique. | This can be unique or nonunique. |
| It can be optional for a table. | It is not optional. |
| It specifies where the data resides in a table. | It is used to uniquely identify a table. |
| No duplicates are allowed. | Duplicates are allowed. |
| It can be altered by dropping the table and recreating it. | It cannot be changed. |