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.

Enroll in our Hyperion EPM Training program today and elevate your skills!

Subscribe to our YouTube channel to get new updates..!

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: