35,99 €
Mehr erfahren.
- Herausgeber: Packt Publishing
- Kategorie: Wissenschaft und neue Technologien
- Sprache: Englisch
Data management and analytics simplified with Teradata
Key Features
- Take your understanding of Teradata to the next level and build efficient data warehousing applications for your organization
- Covers recipes on data handling, warehousing, advanced querying and the administrative tasks in Teradata.
- Contains practical solutions to tackle common (and not-so-common) problems you might encounter in your day to day activities
Book Description
Teradata is an enterprise software company that develops and sells its eponymous relational database management system (RDBMS), which is considered to be a leading data warehousing solutions and provides data management solutions for analytics. This book will help you get all the practical information you need for the creation and implementation of your data warehousing solution using Teradata.
The book begins with recipes on quickly setting up a development environment so you can work with different types of data structuring and manipulation function. You will tackle all problems related to efficient querying, stored procedure searching, and navigation techniques. Additionally, you’ll master various administrative tasks such as user and security management, workload management, high availability, performance tuning, and monitoring.
This book is designed to take you through the best practices of performing the real daily tasks of a Teradata DBA, and will help you tackle any problem you might encounter in the process.
What you will learn
- Understand Teradata's competitive advantage over other RDBMSs.
- Use SQL to process data stored in Teradata tables.
- Leverage Teradata’s available application utilities and parallelism to play with large datasets
- Apply various performance tuning techniques to optimize the queries.
- Acquire deeper knowledge and understanding of the Teradata Architecture.
- Easy steps to load, archive, restore data and implement Teradata protection features
- Gain confidence in running a wide variety of Data analytics and develop applications for the Teradata environment
Who this book is for
This book is for Database administrator's and Teradata users who are looking for a practical, one-stop resource to solve all their problems while handling their Teradata solution. If you are looking to learn the basic as well as the advanced tasks involved in Teradata querying or administration, this book will be handy. Some knowledge of relational database concepts will be helpful to get the best out of this book.
Abhinav Khandelwal has 11 years of industry experience in BI, analytics, and data warehousing. He gained a BE from GIT Sitapura, Jaipur. He is pursuing his MBA at Welingkar, Mumbai and is currently based in Melbourne. He acts as a senior consultant on BI DWH projects. He has also been in the news for his award-winning projects. For more information, you can refer to his blogs called as anonymously rave and three of kind. Contact him on Twitter at @abhi_khandu. Rajsekhar Bhamidipati has 13 years' industry experience in Teradata and DWH and gained his BTech from BPUT Rourkela. He is working in Pune with Teradata Corp as a Senior Teradata Specialist on Teradata DWH projects. He has been associated with Teradata for more than 8 years and has successfully led multiple projects in various roles, with notable success in service delivery. He leverages Teradata and related technologies in the areas of database administration, workload, performance and cloud.Sie lesen das E-Book in den Legimi-Apps auf:
Seitenzahl: 377
Veröffentlichungsjahr: 2018
Ähnliche
Teradata Cookbook
Copyright © 2018 Packt Publishing
All rights reserved. No part of this book may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, without the prior written permission of the publisher, except in the case of brief quotations embedded in critical articles or reviews.
Every effort has been made in the preparation of this book to ensure the accuracy of the information presented. However, the information contained in this book is sold without warranty, either express or implied. Neither the authors, nor Packt Publishing or its dealers and distributors, will be held liable for any damages caused or alleged to have been caused directly or indirectly by this book.
Packt Publishing has endeavored to provide trademark information about all of the companies and products mentioned in this book by the appropriate use of capitals. However, Packt Publishing cannot guarantee the accuracy of this information.
Commissioning Editor:Amey VarangaonkarAcquisition Editor: Varsha ShettyContent Development Editor: Aaryaman SinghTechnical Editor: Dinesh ChaudharyCopy Editor: Safis EditingProject Coordinator: Manthan PatelProofreader: Safis EditingIndexer: Aishwarya GangawaneGraphics:Tania DuttaProduction Coordinator:Shantanu Zagade
First published: February 2018
Production reference: 1140218
Published by Packt Publishing Ltd. Livery Place 35 Livery Street Birmingham B3 2PB, UK.
ISBN 978-1-78728-078-6
www.packtpub.com
Mapt is an online digital library that gives you full access to over 5,000 books and videos, as well as industry leading tools to help you plan your personal development and advance your career. For more information, please visit our website.
Why subscribe?
Spend less time learning and more time coding with practical eBooks and Videos from over 4,000 industry professionals
Improve your learning with Skill Plans built especially for you
Get a free eBook or video every month
Mapt is fully searchable
Copy and paste, print, and bookmark content
PacktPub.com
Did you know that Packt offers eBook versions of every book published, with PDF and ePub files available? You can upgrade to the eBook version at www.PacktPub.com and as a print book customer, you are entitled to a discount on the eBook copy. Get in touch with us at [email protected] for more details.
At www.PacktPub.com, you can also read a collection of free technical articles, sign up for a range of free newsletters, and receive exclusive discounts and offers on Packt books and eBooks.
Contributors
About the authors
Abhinav Khandelwal has 11 years of industry experience in BI, analytics, and data warehousing. He gained a BE from GIT Sitapura, Jaipur. He is pursuing his MBA at Welingkar, Mumbai and is currently based in Melbourne. He acts as a senior consultant on BI DWH projects. He has also been in the news for his award-winning projects. For more information, you can refer to his blogs called as anonymously rave and three of kind. Contact him on Twitter at @abhi_khandu.
Rajsekhar Bhamidipati has 13 years' industry experience in Teradata and DWH and gained his BTech from BPUT Rourkela. He is working in Pune with Teradata Corp as a Senior Teradata Specialist on Teradata DWH projects. He has been associated with Teradata for more than 8 years and has successfully led multiple projects in various roles, with notable success in service delivery. He leverages Teradata and related technologies in the areas of database administration, workload, performance and cloud.
About the reviewer
Viswanath Kasi is a decisive and results-driven professional with more than 12 years of experience in technology and management, with Teradata DBA expertise of 7 years. His experience with esteemed clients makes him unique and his passion for technology makes him technically stronger.
Packt is searching for authors like you
If you're interested in becoming an author for Packt, please visit authors.packtpub.com and apply today. We have worked with thousands of developers and tech professionals, just like you, to help them share their insight with the global tech community. You can make a general application, apply for a specific hot topic that we are recruiting an author for, or submit your own idea.
Table of Contents
Preface
Who this book is for
What this book covers
To get the most out of this book
Download the example code files
Download the color images
Conventions used
Get in touch
Reviews
Installation
Setting up Teradata 15.10
How to do it...
Setting up Teradata Studio Express
Getting ready
How to do it...
Teradata on Azure
Getting ready
How to do it...
How it works...
There's more...
Defining a connection
How to do it...
Connecting to the Teradata system
How to do it...
There's more...
Using Studio tool options
Setting up Teradata SQLA
Getting ready
How to do it...
How it works...
There's more...
Configuring SQLA
Getting ready
How to do it...
How it works...
Building a query builder
How to do it...
Query builder in SQLA
Query builder in Express
Importing data
Getting started
How to do it...
In Express
Exporting data
How to do it
How it works...
There's more...
SQLs
Introduction
Writing queries
Getting ready
How to do it...
Using SQLA
Using BTEQ in Windows
How it works...
There's more...
Querying efficiently
Getting ready
How to do it...
How it works...
Explain before executing queries
Getting ready
How to do it...
How it works...
There's more...
Decoding explain
How to do it...
How it works...
There's more...
Resolving skewing data
Getting ready
How to do it...
How it works...
There's more...
See also
Resolving skew in database
Getting ready
How to do it...
How it works...
There's more...
Solving insert performance
Getting ready
How to do it...
How it works...
There's more...
Improving delete performance
Getting ready
How to do it...
How it works...
There's more...
Improving update performance
Getting ready
How to do it...
How it works...
There's more...
See also
Performing MERGE INTO
Getting ready
How to do it...
How it works...
Advanced SQL with Backup and Restore
Introduction
Backup and recovery
Exploring ordered analytic functions
Getting ready
How to do it...
How it works...
There's more...
Using CASE statements
Getting ready
How to do it...
How it works...
There's more...
Working with correlated subqueries
Getting ready
How to do it...
How it works...
There's more...
Experimenting with JSON
Getting ready
How to do it...
How it works...
There's more...
Partitioning tables column wise
Getting ready
How to do it...
How it works...
There's more...
Archiving data dictionary
Getting ready
How to do it...
How it works...
There's more...
Archiving databases
Getting ready
How to do it...
How it works...
There's more...
Archiving PPI tables
Getting ready
How to do it...
How it works...
There's more...
Restoring a table
Getting ready
How to do it...
How it works...
There's more...
Generating a unique row number
Getting ready
How to do it...
How it works...
There's more...
All about Indexes
Introduction
Creating a partitioned primary index to improve performance
Getting ready
How to do it...
How it works...
There's more...
Creating a join index to improve performance
Getting ready
How to do it...
How it works...
There's more...
Creating a secondary index to improve performance
Getting ready
How to do it...
How it works...
There's more...
Creating a hash index to improve performance
Getting ready
How to do it...
How it works...
There's more...
Mixing Strategies – Joining of Tables
Introduction
Identifying skewness in joins
Getting ready
How to do it...
How it works...
There's more...
Identify the right columns for joins
Getting ready
How to do it...
How it works...
There's more...
Eliminating product joins
Getting ready
How to do it...
How it works...
There's more...
See also
Improving left join
Getting ready
How to do it...
How it works...
Improving Teradata joins
Getting ready
How to do it...
How it works...
Building Loading Utility – Replication and Loading
Introduction
Loading data from flat to Teradata
Getting ready
How to do it...
How it works...
There's more...
Resolving FastLoad error 2652
Getting ready
How to do it...
How it works...
There's more...
Using MultiLoad to delete
Getting ready
How to do it...
How it works...
There's more...
Resolving MLOAD error 2571
Getting ready
How to do it...
How it works...
There's more...
Resolving failure 7547
Getting ready
How to do it...
How it works...
There's more...
Upserting data using MLOAD
Getting ready
How to do it...
How it works...
There's more...
Loading data from a one Teradata Database to another
Getting ready
How to do it...
How it works...
Monitoring the better way
Introduction
Configuring the viewpoint portlet
Getting ready
How to do it...
How it works...
Identifying killer queries in viewpoint
Getting ready
How to do it...
How it works...
There's more...
Setting up viewpoint alerts
Getting ready
How to do it...
How it works...
There's more...
Changing and managing filters
Getting ready
How to do it...
How it works...
There's more...
Changing and managing throttle
Getting ready
How to do it...
How it works...
There's more...
Defining a ruleset
Getting ready
How to do it...
How it works...
Creating a TASM exception
Getting ready
How to do it...
How it works...
Collect Statistics the Better Way
Introduction
Cost-based optimizer
Evolution of statistics
How to collect statistics
Getting ready
How to do it...
How it works...
Identifying stats for a table
Getting ready
How to do it...
How it works...
There's more...
Identifying multi-column stats
Getting ready
How to do it...
How it works...
There's more...
How to collect expression stats
Getting ready
How to do it...
How it works...
There's more...
How to copy statistics
Getting ready
How to do it...
How it works...
There's more...
Using help and show to resolve query issues
Getting ready
How to do it...
How it works...
There's more...
Recommendations
Application and OPS DBA Insight
Introduction
Creating, copying, and dropping tables
Getting ready
How to do it...
How it works...
There's more...
Working with views
Getting ready
How to do it...
How it works...
There's more...
Defining workloads based on application
Getting ready
How to do it...
How it works...
There's more...
Securing passwords using Teradata Wallet
Getting ready
How to do it...
How it works...
There's more...
Shrinking your data size
Getting ready
How to do it...
How it works...
There's more...
Using query band
Getting ready
How to do it...
How it works...
DBA Insight
Introduction
Creating a Teradata database
Getting ready
How to do it...
How it works...
There's more...
Creating a Teradata database user
Getting ready
How to do it...
How it works...
There's more...
Creating profiles and assigning them to users
Getting ready
How to do it...
How it works...
There's more...
Creating a Secure Zone
Getting ready
How to do it...
How it works...
There's more...
Creating user accounts
Getting ready
How to do it...
How it works...
Investigating phantom and leftover spool issues
Getting ready
How to do it...
How it works...
There's more...
Unlocking archive HUT locks
Getting ready
How to do it...
How it works...
There's more...
Performance Tuning
Introduction
Resolving a slow or hung system
Getting ready
How to do it...
How it works...
Monitoring slow queries
Getting ready
How to do it...
How it works...
There's more...
Aborting a session from the supervisor window
Getting ready
How to do it...
How it works...
There's more...
Resolving AWT saturation
Getting ready
How to do it...
How it works...
There's more...
Identifying suspect queries
Getting ready
How to do it...
How it works...
Managing DBC space
Getting ready
How to do it...
How it works...
There's more...
See also
Optimizing queries
Getting ready
How to do it...
How it works...
See also
Troubleshooting
Introduction
Restarting the TD system
Getting ready
How to do it...
How it works...
There's more...
Insufficient memory to parse this request
Getting ready
How to do it...
How it works...
There's more...
Recovering AMP down
Getting ready
How to do it...
How it works...
There's more...
Performing scandisk on the system
Getting ready
How to do it...
How it works...
There's more...
Unlocking the DBC
Getting ready
How to do it...
How it works...
There's more...
Managing the FSG cache
Getting ready
How to do it...
How it works...
There's more...
Other Books You May Enjoy
Leave a review - let other readers know what you think
Preface
Teradata is the world's leading provider of business analytics solutions, data and analytics solutions, and hybrid cloud products and services. Its eponymous relational database management system (RDBMS), which is considered to be a leading data warehousing solution. It provides data management solutions for analytics. This book will help you get all the practical information you need to create and implement your data warehousing solution using Teradata.
The book begins with recipes on quickly setting up a development environment so that you can work with different types of data structuring and manipulation functions. You will tackle all the problems related to efficient querying, stored procedure searching, and navigation techniques. Additionally, you'll master administrative tasks such as user and security management, workload management, high availability, performance tuning, and monitoring.
This book is designed to take you through the best practices of performing real daily tasks no matter what role are you performing when dealing with Teradata. It will help you solve your problem and implement the best solution.
Who this book is for
This book is for database administrators and Teradata users who are looking for a practical, one-stop resource to solve all their problems while handling their Teradata solutions. If you are looking to learn the basics as well as advanced tasks involved in Teradata querying or administration, this book will be handy. Some knowledge of relational database concepts will be helpful to get the best out of this book.
What this book covers
Chapter 1, Installation, is about setting up the Teradata database and client utility to access the database.
Chapter 2, SQLs, teaches the basic SQL used to manage and process your data in a database.
Chapter 3, Advanced SQL with Backup and Restore, shows new and advanced SQL and explains how to back up your database.
Chapter 4, All about Indexes, resolves and improves our query performance using indexes.
Chapter 5, Mixing Strategies – Joining of Tables, shows how to improve join performance and explains how they work.
Chapter 6, Building Loading Utility – Replication and Loading, resolves utility issues and makes it work efficiently.
Chapter 7, Monitoring the better way, examines the best practices to catch and resolve performance issues on the database.
Chapter 8, Collect Statistics the Better Way, resolves query performance by collecting and managing statistics.
Chapter 9, Applications and OPS DBA Insight, identifies and resolves daily issues faced by application admins.
Chapter 10, DBA Insight, identifies and resolves daily issues faced by system admins.
Chapter 11, Performance Tuning, helps us identify, resolve, and improve query performance issues faced by database users.
Chapter 12, Troubleshooting, shows how to troubleshoot system-wide issues using database utilities.
To get the most out of this book
Readers of this book should have working knowledge of the Teradata database and SQL writing.
In order to install and connect to Teradata, r
eaders
have to download the software mentioned by creating a free account on the Teradata developer website.
Download the example code files
You can download the example code files for this book from your account at www.packtpub.com. If you purchased this book elsewhere, you can visit www.packtpub.com/support and register to have the files emailed directly to you.
You can download the code files by following these steps:
Log in or register at
www.packtpub.com
.
Select the
SUPPORT
tab.
Click on
Code Downloads & Errata
.
Enter the name of the book in the
Search
box and follow the onscreen instructions.
Once the file is downloaded, please make sure that you unzip or extract the folder using the latest version of:
WinRAR/7-Zip for Windows
Zipeg/iZip/UnRarX for Mac
7-Zip/PeaZip for Linux
The code bundle for the book is also hosted on GitHub athttps://github.com/PacktPublishing/Teradata-Cookbook. We also have other code bundles from our rich catalog of books and videos available athttps://github.com/PacktPublishing/. Check them out!
Download the color images
We also provide a PDF file that has color images of the screenshots/diagrams used in this book. You can download it here: http://www.packtpub.com/sites/default/files/downloads/TeradataCookbook_ColorImages.pdf.
Conventions used
There are a number of text conventions used throughout this book.
CodeInText: Indicates code words in text, database table names, folder names, filenames, file extensions, pathnames, dummy URLs, user input, and Twitter handles. Here is an example: "It went to the default CASE statement."
A block of code is set as follows:
/*Correlated query*/SELECT
Column1, Column2
FROM
Table1 Tb1
WHERE
Column1
IN
(
When we wish to draw your attention to a particular part of a code block, the relevant lines or items are set in bold:
/*Aggregate CASE*/SELECT
SUM
(CASE WHEN department='IT' THEN) AS SAL_IT
SUM
(
Any command-line input or output is written as follows:
tpareset -f testing the restart command
Bold: Indicates a new term, an important word, or words that you see onscreen. For example, words in menus or dialog boxes appear in the text like this. Here is an example: "JavaScript Object Notation (JSON) is a lightweight programming independent data interchange format."
Get in touch
Feedback from our readers is always welcome.
General feedback: Email [email protected] and mention the book title in the subject of your message. If you have questions about any aspect of this book, please email us at [email protected].
Errata: Although we have taken every care to ensure the accuracy of our content, mistakes do happen. If you have found a mistake in this book, we would be grateful if you would report this to us. Please visit www.packtpub.com/submit-errata, selecting your book, clicking on the Errata Submission Form link, and entering the details.
Piracy: If you come across any illegal copies of our works in any form on the Internet, we would be grateful if you would provide us with the location address or website name. Please contact us at [email protected] with a link to the material.
If you are interested in becoming an author: If there is a topic that you have expertise in and you are interested in either writing or contributing to a book, please visit authors.packtpub.com.
Reviews
Please leave a review. Once you have read and used this book, why not leave a review on the site that you purchased it from? Potential readers can then see and use your unbiased opinion to make purchase decisions, we at Packt can understand what you think about our products, and our authors can see your feedback on their book. Thank you!
For more information about Packt, please visit packtpub.com.
Installation
Teradata installation is easy and straightforward. This chapter will help you in the installation of Teradata 15.10.0.07 SLES 11 for VMware (40 GB), including the monitor tool Viewpoint on 64-bit Windows 7 with 8 GB RAM.
The following recipes will be covered in this chapter:
Setting up Teradata 15.10
Setting up Teradata Studio Express
Teradata on Azure
Defining a connection
Connecting to the Teradata system
Using studio tool options
Setting up Teradata SQLA
Configuring SQLA
Building a query builder
Importing data
Exporting data
Go to http://downloads.teradata.com/download/database.
Setting up Teradata 15.10
Before we begin the installation process of our Teradata Database on a local machine, you need to install VMware workstation 12.x for Windows.
How to do it...
Fire up the installed VMware software:
Click on
Open
a Virtual Machine
and select the
TDExpress15.10.0.7_Sles11_40GB_vp.vmx
file from the open window prompt:
You are now ready to play the installed Teradata Database.
Click on the
Play Virtual machine
link. The setup will run its course and take you to the login screen:
Login
with
root
as the username and password. Wait for the Teradata Lab log wallpaper to show up on the desktop in the bottom-right corner:
Voila! You have successfully installed the Teradata Database on your machine. The world's best analytical and data warehouse database is now up and running on your machine as the VM image:
Our next step will be to set up a client tool that connects to the existing database instance.
Setting up Teradata Studio Express
In the current version of VMware, we are already blessed with Teradata Studio Express. You will see an icon, Teradata Studio Express; all you need to do is click on this and it will fire up the client tool.
Getting ready
What if you want to install on your local machine/desktop without VMware? It's easy, all you need to do is create a login account at http://developer.teradata.com/ and then go to the download link. Under tools, search for Teradata Studio Express.
How to do it...
For a Windows machine:
Unzip the client tool package to your local directory.
Run
setup.exe
to launch the Teradata Studio Express installation.
Teradata Studio Express is installed in the Program Files directory.
For example,
C:\Program Files (x86)\Teradata\Client\16.00\Teradata Studio Express
.
Now, as the VMware machine is Linux OS-based, the installation file will be located in the
/opt/teradata
directory:
Teradata Studio Express requires a JDBC connection type; it does not support ODBC like the Teradata SQL assistant. You can connect to the following databases other than Teradata using studio. To add other databases to your preferred list of databases to connect to, go to the
Preference
tab and select from the list:
Aster database
Hadoop database
DB2
SQL
Oracle
Here are some highlights of Studio and Express:
Connection to variety of data databases
Studio has an administrative window
Copy and compare in Studio
Hadoop data transfer in Studio
Using load utilities in both
Data lab feature in both
Teradata on Azure
With growing demand to increase its footprint, Teradata is now available on the cloud. This means that customers now don't have to worry about data centers and setting up the hardware.
Teradata has availability on the cloud as follows:
Private cloud
Public cloud
Mixed cloud
With a proper subscription account, you can create your own Teradata Database on the cloud with all the capability and features you require.
With the current setup of Azure you can only deploy a 32 node system; if you require more nodes get in touch with Teradata sales personnel.
The thing you need to note is, many things in this recipe might change over time and some of them might not be valid. As the cloud platform is under continuous change, please check your subscription type before deploying a Teradata solution. Deploying Teradata on Azure is similar; you buy Teradata hardware on premises, you need to purchase the software with various available purchase methods.
You can also deploy Teradata with all the additional products such as Viewpoint, datamover, and many others. Or you can buy these products individually; this gives you added advantages when you require flexibility.
There are many factors and parameters when you deploy the solution to the cloud, but in this recipe we will only cover how to create a Teradata Database and Viewpoint instance in Azure and make it available publicly.
Getting ready
You need to have an Azure subscription. Log in to the Azure marketplace and create a free account. It is recommended to have a subscription-based account to have enough cores available to you. Following are some prerequisites:
Pay-as-you-go subscription on your Azure account
Sufficiently high quota limits (recommended: 128 cores) in your Azure account
How to do it...
Once you are logged in to the Azure marketplace, search for Teradata, and as shown in the screenshot, select
Teradata Database
from the list:
On next window, read the terms and conditions. And after that, click on
Create
, as shown in the screenshot:
Next will be the window with the list of steps. In the first step, you need to provide a
User name
, a
Password
, and select a
Location
based on your requirements. Not all the
Locations
have products that you require. Click
OK
to move to step 2. Check the screenshot for details:
In step 2, select the name of your
database
, and provide the
DBC password
(keep it safe in a notepad). Also, select the flavor or
Database Tier
and
Version
of the database you want. Azure hosts the latest version Td 16.10. Click OK to move to the next step. Check the following screenshot:
The next step will be configuring your
Viewpoint
. To keep it simple, select only a single monitoring Viewpoint system. All the fields are self-explanatory. Password here will be used to log in to Viewpoint via an admin account. Check the following screenshot:
Options 4 to 8 are optional. You can either enable or disabled them as per your requirements.
In step 8, once all the configuration is done, a final validation is executed, which checks for all the parameters and cores available at your location. If all went fine, your deployment will start after you make the required purchase. Once finished with the configuration, a template will be prepared for future use. You can download the template from your database, which can be used afterwards if you want to create a same configuration system or want to use template based deployment. Check the following screenshot:
Your deployment will start, as shown in the following screenshot. It will take approximately 30 mins or so to get the system up and running. You can check the progress from the
Resource group
option from the left-hand side of the toolbar, and under the resource group you created while deploying the database. Check the following screenshot:
Now to connect our system on cloud with our local machine, we need to make system available publicly. To do this, once your system is up and running, go to
Resource groups
and select
search for database
. From the list, we will select network interface. In this option, we will enable the option to access this database publicly. Check the following screenshot:
In the settings, click on IP configurations; next, click on the IP address in the bar. For the next option displayed, select
Enabled
under
Public IP address
and click
Save
, as shown in the screenshot:
Once you have enabled the public IP, you can now give a DNS name to the IP for easy access. To enable this, search for public IP under the
type
column in your resource group. Under the
Configuration
tab, enter the
DNS name
as seems fit and save the settings. This can be seen in the following screenshot:
Repeat the same step for enabling a public IP and assigning a DNS name for accessing Viewpoint from your local machine.
Open your SQLA and enter the DNS name you grabbed from the settings, and enter the server name to create a DSN connection:
You can do the same for Viewpoint; grab the DNS as shown in the screenshot and enter it locally in your web browser. Log in using an admin account and enable the system to monitor your existing Teradata Database:
Welcome to Teradata on Azure.
How it works...
Teradata on Azure gives you the same performance as on premises. You can get same power of analytic database as you get from an on-premises database. Once on the cloud you can configure your system and can take advantage of the additional Azure applications, as well, to monitor and create system alerts.
If you are stuck with any of the issues when performing deployment, it is advisable to contact the Azure help desk via your dashboard. A ticket will be created and the issue will be resolved based on your subscription type:
There's more...
Once you deploy your Teradata Database, from time to time you need to upgrade or apply patches to the database. You need to download these from the Teradata access portal and, based on your license tiers, Teradata engineering will help you.
There are four license tiers available as of now:
Developer
: With low performance
Base
: With simple and middle-level performance
Advance
: With high-performance workloads and better performance
Enterprise
: With full capability and enterprise-level performance
Defining a connection
Connect better! In this case, we will connect to the Teradata Express VMware machine, which is on the desktop PC, using Teradata Studio Express. However, in the real world, Teradata System(s) are located in safe data centers and you would need the IP address(es) of the system.
How to do it...
We will be establishing a JDBC data connection to a local database:
Click on
Database Connection
,
New...
:
Select
Teradata Database
from the connection profile screen and provide a name for the connection:
Enter
localhost
in the
Database Server Name
. When doing a connection to a real-world database, provide an IP or a hostname here. The
User Name
and
Password
will be
dbc
:
If your site/client is using an LDAP to log on, you need to choose an LDAP mechanism and enter your LDAP credentials.
Click on
Test Connection
to ensure connectivity.
You are now connected to the Teradata Database; start firing up your queries using SLQ Editor.
Connecting to the Teradata system
Now, before starting up with our very first query, you need to know that express works in the ANSI TMODE. You need to change to TERA mode manually.
Additionally, you can have a multiple connections to the same database connection or a different one.
How to do it...
Let's execute our very first query:
/*Sample query*/sel * from dbc.dbcinfo;
The output of the preceding query will give us the current version of the Teradata Database system. Yes, we are on
15.10.03.07
:
This is a heavy tool, so it will consume more resources from your PC compared to the handy and breezy SQL Assistant. We'll be exploring SQL Assistant in the later part of this chapter.
There's more...
Humans and software—we judge them on the basis of their characteristics. Let's explore some of the important options available with Teradata Studio Express.
Using Studio tool options
Here are some of the tool options that we will cover:
SQL compare
: Comparing two text documents is the basic necessity of the coding community. There are many tools for this job. However, when you have a built-in feature for it, you don't need to go anywhere else. You ask how to use it? Click on the
Project Explorer
tab (next to the
Data Explorer
tab). Right-click on the SQL tab and create SQL text files that you need to compare. Select both files and select the
Compare With
|
Each Other
option. The text that is different will be highlighted and shown:
Code assist and syntax checking:
Forgot the names of the tables in the database, a column name, or the syntax of your statement? Express will prompt as you type. Type
EXEC
for macro execution with the code assist
ON
and you will get a series of options that will go with the main command. Now, when you write a database name followed by a dot, it will give you a list of tables to choose from that database:
Object Viewer:
This works in a similar to the traditional tool SQL Assistant; however, it can help
to get more details for your object of interest. If you have Studio, it will have even more options for admin use. Right-click on any object from the Explorer. You will see all the options affiliated with the object you need:
There are many other options at your disposal that you can explore.
Setting up Teradata SQLA
We will now turn our focus to the traditional but easy to use tool, SQL Assistant (SQLA). We now have a SQLA Java version just like Teradata Studio or Teradata Studio Express.
Getting ready
For SQLA to work, you need to download it from the developer.teradata.com website and download Teradata Tools and Utilities, also known as TTU. Once downloaded and installed, start making the connection using ODBC, .NET, or JDBC.
Here, we will use SQLA 16.0. It is highly recommended that you use the TTU version that matches or is higher than the major release version number of the Teradata Database that you intend to connect with. So, if you are on Teradata Database 15.10, use TTU 15.10 or 16. TTU 13.10 is not recommended. Now that we have this covered, let's move on to the connection.
How to do it...
Use the following steps to establish a connection:
Click on the plug icon in the top-left corner; now you can either select file data source or machine data source.
Click on
New...
if you don't have a connection file. Select user or system data source:
Then select the option of
Teradata Database ODBC driver 16.00
; after this, you will see the screen for providing the name of your data connection and source IP.
Enter your credentials; you have the option to save the password if you don't want to enter it every time you connect to the database:
Wouldn't it be great to have an option to run SQL using the command-line interface with SQLA?
How it works...
Teradata SQLA allows you to connect to a data source using the ODBC and .NET framework. When you install SQLA from TTU toolkit, make sure to check all the options. You will have two executables:
TTU_BASE
has the following:
ODBC driver
BTEQ
All the loading tools, such as fastexport, fastload, and many others
Named pipe modules
Websphere access modules
.NET data provider
C preprocessor 2
Tearadata adminstrator
SQLA
Teradata Wallet and many more
TTU_DBM
has the following:
Index wizard
BTEQ
ODBC driver
Query scheduler admin, client, server
TSET
Workload analyzer
Visual explain
Teradata Wallet
There's more...
Here are some shortcuts to make your daily job easier:
F2
: It will open query builder, with syntax for all SQL queries
F5
: Execute SQL query
F6
: Explain plan for SQL query
F9
: Execute SQL queries in parallel
F10
: Abort SQL query
F11
: Display last error encountered
Ctrl
+
N
: New SQL query window
Ctrl
+
Q
: Format SQL query
Ctrl
+
U
: Convert to UPPERCASE
Ctrl
+
H
: Find and replace
Configuring SQLA
Once you have the SQLA all fired up, the next thing we want is to configure the settings. SQLA comes with some default settings that you might want to change. For example, every time you execute the query, you may want to preserve the previous ruleset. The default setting has a habit of closing the old ruleset.
In this recipe we will change the SQLA settings to better ones.
Getting ready
You need to open the SQLA.
How to do it...
Open SQLA.
To change the behavior of the
resultset
, click on
Tools
|
Options ...
, highlighted in the following screenshot:
From the pop-up window, click on the
Query
tab, as highlighted in the following screenshot:
Next, uncheck the following boxes as shown in step 1; option 1 will make sure that your old answer set is not closed when you are submitting your query. Tick the 7; option will help you execute highlighted queries when you have multiple queries in your window:
There are many other options that, if you want, you can check and uncheck.
How it works...
SQLA is a simple and efficient tool that helps users with their daily work. With better configuration, this tool can be used effectively.
SQLA also provides a way for you to automate your queries from a Windows machine. This recipe won't be covering the full process, but here are some commands that you can use to automate the scripts/jobs.
To execute SQLA from the command prompt, use the following:
Enter the following command on Run:
Sqla -c cookbook -f "c:\workbook\dbc.sql” -e "c:\workbook\resultset\dbc.log"
The
-c
cookbook is used to establish a connection to the cookbook server
-f "c:\workbook\dbc.sql"
opens a file in the given path directly to SQL Assistant
-e "c:\workbook\resultset\dbc.log"
is used to export the
resultset
to the given file
This will open SQLA, execute the queries, and close it afterwards. This is useful to schedule your jobs/queries on Windows machines.
Building a query builder
Query builder is your quick reference guide to SQL syntax. It not only lets you use its own SQLs, but also provides you with the option to add your own along with editing the existing ones.
How to do it...
Query builder is available both in SQLA and Teradata Studio Express. Express query builder is more interactive and has drag-and-drop features. Let's first cover SQLA, followed by Studio Express.
Query builder in SQLA
Here is how you can proceed with query builder in SQLA:
Query builder in SQLA is under the
Help
tab, or you can press
F2
. Here, you can click on any existing command or browse for the one you need.
You can also add frequently used queries in query builder. Click on
+
as shown in the following screenshot and add the queries that you need most, so that you don't have to search for them:
Query builder in Express
Here is how to proceed with query builder in Express:
In express, you can edit an existing query using the query builder. Selecting the query in the SQL Editor, right-click and select the
Edit in Query Builder
option:
Once you click on the
Edit
option, your query will be opened in a new window with many other options. Here, you can add new tables to the existing query, creating a relationship between them. There is also a tab that gives you options to put conditions (
where
,
or
,
and
) in queries. You can also add the group by clause; refer to the screenshot for details:
There is an option to change the statement type when you click on the statement box.
Importing data
Let's get the ball rolling now.
We have had Teradata Studio Express and SQLA up and running for a while now. It is time to load data into the Teradata Database.
Now, there are many ways to do this, but, for simplicity, I will be importing data via Excel using the GUI option in Teradata Studio Express. You can copy the table data from Teradata to a data lab just by dragging the source table from the data lab view. The datalab copy wizard helps you through step by step.
You can get the dataset from various websites; there are tons of websites giving free datasets for analysis.
Getting started
The following are the prerequisites to import the data:
Create a new user, for example,
Mike
.
Create a new database, for example,
TestDb
.
Allocate space to the new database.
Grant appropriate access rights to the new database for the new user.
Create a table DDL under this database with the same column layout as the import file:
How to do it...
Let's begin with importing data using Studio first and then we will move on to SQLA. The following DDL was used to create the table in the new database. Now load the data from the external file. The following steps allow you to do so:
Go to data explorer.
Search for the database under which the required table is located:
Explore the database.
Right-click on the table,
Name
|
Data
|
Load Data
:
The Wizard will come up; select the source as
External File
:
