Looking for information on Teradata SQL Assistant? Then you are in the right place. It is always hard to execute queries right on the database server from a command prompt. Instead, if we can construct queries from a UI and apply them to the data present in a database, it will be very easier. Teradata SQL assistant helps us with just that on the Teradata database. In this post, you can learn about Teradata SQL Assistant. Having prior knowledge of formulating queries for processing on Teradata will be helpful to work with Teradata SQL Assistant. So, let us get started.
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.
Here are the key features of Teradata SQL Assistant for Microsoft Windows.
The following are the key features of Teradata SQL Assistant/Web Edition.
Supported Teradata Database Versions
The following are the Teradata Database versions that the SQL Assistant supports.
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.
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.
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 = '
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.
Go to the File menu and click on the 'import' option. Enter the below SQL statement in the query window.
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 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: