Teradata SQL Assistant

What is Teradata SQL Assistant?

Teradata SQL Assistant is a query and information discovery tool for data on the Teradata database. Teradata SQL Assistant is referred to as a Teradata Tools and Utility product. It stores, retrieves, and manipulates data from the Teradata database, or any database that is ODBC compliant. 

We can combine data retrieved from the databases with desktop applications such as Excel to create consolidated reports. The Teradata SQL Assistant can record the SQL activities like timings, row counts, source identification, etc. The Teradata SQL Assistant comes in two editions.

We have the perfect professional Teradata Course for you. Enroll now!

Teradata SQL Assistant for Microsoft Windows - It is a data discovery tool designed for Windows XP and Windows 2000. We can connect to the database through ODBC connection, retrieve the data, analyze it, and store it locally on a PC.

Teradata SQL Assistant/ Web Edition - It is a web-based version of SQL Assistant. We can connect to the database, query the data, and view the results on the web browser.

Features of Teradata SQL Assistant for Microsoft Windows

Here are the key features of Teradata SQL Assistant for Microsoft Windows.

• Construct queries on an ODBC compliant database.
• Execute the same query on different databases.
• Export the required data from a database to a file on PC.
• We can generate reports for the data present in the database.
• We can import a file to create similar reports.
• It logs all the transactions of the SQL events with timings and status information.
• We can explore the database objects using Database Explorer Tree.
• While building stored procedures, we can use a procedure builder that helps in building the logic by providing a list of valid statements.
• We can limit the data that will be returned to queries.

Features of Teradata SQL Assistant/ Web Edition

The following are the key features of Teradata SQL Assistant/Web Edition.

• Construct and send queries to any connected database.
• We can view and sort the results from the browser. 
• We can save the queries to a file so we can use it in the future to run them automatically.
• Load queries from a file, edit them, and submit them to a database.
• We can update the history of the logs with some notes.
• We can use the find feature on the browser to search for a string in either result or queries.
• We can stop a running query if it is taking too long.
• The browser connection will end after 30 minutes of inactivity.

Supported Teradata Database Versions

The following are the Teradata Database versions that the SQL Assistant supports.

• Teradata Database 16.20
• Teradata Database 16.10
• Teradata Database 15.10
• Teradata Database 15.0
• Teradata Database 14.10

Pre-requisites and downloading the software

Before we install the Teradata SQL assistant, we have to make sure that the below dependent software is installed on the system.

• Microsoft .NET Framework 3.5 SP1 or later
• Java 2 Runtime Environment 1.4.2_06 or later
• Teradata Database

Go to https://downloads.teradata.com/download/tools/teradata-tools-and-utilities-windows-installation-package and click on 'TTU 17.00.15.00 Windows - Base'. A pop-up appears and prompts you to accept to terms and conditions, click on 'I Agree'.The package will be downloaded to your PC.

Teradata SQL Assistant installation

Unzip the downloaded folder and run the setup file to open the installation wizard. It will ask you to choose the language. By default, English will be selected click on 'Next'. Click on 'Next' to start the installation. Then you will get the license agreement. Choose the 'I accept' option and click on 'Next'. It will then show the default installation folder. We can change it if we want, click on 'Next' to proceed. 

Click here to learn Tetadata Tutorial

We have to choose the features that we want to install. Select ODBC driver for Teradata, BTEQ, FastExport, FastLoad, MultiLoad, TPump, Named Pipes Access Module, Teradata SQL Assistant, and click on 'Install'. It will then start installing all the selected modules. We can see the status of the installation through the progress bar. Once the installation is complete, we can see all these modules in our apps. 

Defining a Data Source

Open the Teradata SQL Assistant on your system. Click on the ODBC driver on the top left corner. Click on ‘Tools’ and then select ‘Define Data Source’. Switch to the 'System DSN' tab, click on ‘Add’, and then click on ‘Ok’. It will then ask to select a data source. Select 'User Data Source' and click on 'Next'. Then we have to select a driver. Scroll down and select the 'Teradata Database ODBC Driver'. Click on 'Next' and click on 'Finish'. 

You will get a screen to fill in the details of the database. Give a name and description of the connection. Give the IP address or the name of the Teradata server. Give the username, password, and click on 'Test'. If you get a pop-up that says 'successfully connected to data source', your connection is successful. Click on 'Ok', it will ask about storing your password. Click on 'Yes' and click on 'Ok'.  You can see your newly created connection in the list of data sources.

Writing a query in Teradata SQL Assistant

You can find the data source on the left side panel. Click on it, and you will get a query window in the middle. Write the below query in the query window.

select * from dbo.tables where DatabaseName = ''

will be the name of your database. Select the entire query and click on 'Run'. You can see the list of tables that you have in your database in the ‘Answerset’ window.You can also see a list of all the queries run in the history window below.

Import data into Teradata SQL Assistant

We can import a CSV file into the Teradata SQL Assistant with the help of the import function. First, create a table in your database to which you want to import the data. The query syntax should be based on the data of your file. Write the below SQL statement in the query window and execute it.

Create table (column1 not null, ..); 

Go to the File menu and click on the 'import' option. Enter the below SQL statement in the query window. 

insert into values(?,?,?,?....);

will be the name of the table that you have created recently. Execute the above query by clicking on the run button. You will get an import file dialog box. Browse the file in your local file system and select your file. Click on 'Open' to start importing the file.

Export results to an Excel file from Teradata SQL Assistant

When you execute a SQL statement in the Query window, you can view the results in the 'Answerset' window. If you want to export these results into an excel file, we need to redirect the answer set before executing the query.

Select the File menu and click on the 'Export Results' option. Enter the SQL statement in the Query window and execute it. You will get an export file dialog box.Enter the file name, select the .xlsx file type from the 'Save As Type' menu. When the export is complete, you can see a confirmation message on the export bar.

Using Teradata.Net instead of ODBC

Microsoft's .Net Data Provider for ODBC is a temporary solution for connecting to a database. It will have limited functionality like it supports only a few data types. But it does have additional features as follows.

• The data retrieval is 8 times faster.
• The data import is 70 times faster.
• We can edit the data in a table with the help of an editable grid.
• It will show the syntax errors if a query fails.
• Macros or parallel query execution will work perfectly.
• We can see the data in timestamp columns fully.
• We can return CLOBs with the character set.
• We can see all the User Defined Functions in the database tree.

Conclusion:

The Teradata SQL Assistant will be hugely advantageous to the database developers. It is a one-stop destination for data retrieval and data manipulation. The rows and columns of a table are not stored on the Teradata SQL Assistant. Instead, they are derived from the database. If there is a network issue and the connection is lost, it will automatically connect to the data source again after the connection is restored.

Other Related Article:

• Teradata Interview questions