Teradata SQL Assistant is the SQL Editor which is most widely used to execute SQL queries on Teradata. The Teradata SQL Assistant has very easy to use GUI and it is far more comfortable to work on this editor than working on any other SQL Editor for Teradata. Teradata SQL Assistant has almost all the functionality which can be achieved via BTEQ.
Teradata SQL Assistant – DEFINE DATA SOURCE
The first step after opening Teradata SQL Assistant must be defining a data source. Teradata SQL Assistant can be used to connect to any RDBMS that supports ODBC connection. To connect to a Teradata RDBMS , lets DEFINE DATA SOURCE first.
Go to TOOLS –> DEFINE DATA SOURCE –> ADD –> scroll down and select TERADATA –> FINISH
NAME: Enter the name with which you want to identify this connection.
Description: Enter Description about the connection
SERVER NAME or IP ADDRESS: Enter Teradata SERVER Name or IP ADDRESS.
USERNAME: Enter USERNAME with which you will connect to TERADATA.
PASSWORD: Enter PASSWORD for the user.
SESSION CHARACTER SET: Select the appropriate Character Set : ASCII , UTF8 etc
Click OK and prompt will come to save password , If you want to save password and want to avoid entering password all the time you connect via this connection , click on YES else NO. So now you have set the connection. To Connect to Teradata via this connection: Go to TOOLS –> CONNECT
Or there is icon below FILE Menu. Select the connection which you have just created. Now you are connected to the Teradata Server and you can run your SQL queries now in query window.
Teradata SQL Assistant Export Data
Exporting Data from Table to FLAT FILE using TERADATA SQL ASSISTANT is very simple. It is very easy to export data from any table in TERADATA using TERADATA SQL Assistant. First, you need to write SQL Query in Query Window. The output of this query you want in flat file. Go to FILE –> Export Results. Now when you run the query, it will display the browse window and will ask you to name the file in which output of the query will be re-directed.
Teradata SQL Assistant – Export Data
SELECT COL1,COL2,COL3,COALESCE(COL4,0),COL5 from TABLENAME;
Points to consider while exporting data from table:
a) Delimiter: Whether you want file to be FIXED WIDTH or DELIMITED. If you want file to be delimited then you need to select the delimiter for the output file. To select the appropriate Delimiter for your output file go to TOOLS–>OPTIONS–> EXPORT/IMPORT –> Set the Delimiter.
b) FIXED WIDTH:If you want FIXED width file, then you have to CAST all columns as CHAR and assign the width to each column. All columns should be concatenated with each other to produce FIXED width file. Point to remember here; if there is any column in SELECT clause which is NULLABLE, then make sure to assign some default value for that column. Because NULL concatenates with anything make complete record NULL. So you may use COALESCE function in SELECT clause for NULLABLE columns.
c) DATE FORMAT:While exporting data from table using TERADATA SQL Assistant, it is very important to give proper format to DATE and TIMESTAMP columns. Since, TERADATA SQL Assistant converts date to its own internal format while displaying the data, it may export date columns in some other format, so make sure you apply CAST for date columns to specify proper format of date you want in exported file.
d) OPTIONS:There are other options available, like exporting SQL query in the exported file or exporting column headers etc. So check these options also while exporting data.
e) SUITABLE UTILITY:EXPORT via TERADATA SQL Assistant is not preferable if data is too huge in terms of records counts or row length. As it operates at row level, exporting huge volume of data may take long time. For such cases, use FASTEXPORT utility.
Teradata SQL Assistant Import Data
Importing Data from FLAT FILE to TABLE using TERADATA SQL ASSISTANT is very simple.It is very easy to import data from any flat file to table in TERADATA using TERADATA SQL Assistant. First, you need to write SQL Query in Query Window. INSERT query should be in accordance with the flat file considering the columns mentioned in FLAT FILE in the same order. So if your flat file contains five columns, mention those five columns in the same order in your INSERT CLAUSE; like
Teradata SQL Assistant – Import Data
INSERT INTO TABLENAME (COL1,COL2,COL3,COL4,COL5) VALUES (?,?,?,?,?);
Go to FILE –> Import Results. Now when you run the query, it will display the browse window and will ask you to select the file to which you want to use to import data.
Points to consider while importing data from table:
a) Delimiter: Whether you want to import FIXED WIDTH or DELIMITED file. If file is delimited then you need to select the delimiter for the input file. To select the appropriate Delimiter for your output file go to TOOLS–>OPTIONS –> EXPORT/IMPORT –> Set the Delimiter.
b) SKIP HEADER:There are other options available, like Ignore First Record (Skip Header) .So check these options also while importing data.
c) Suitable UTILITY:IMPORT via TERADATA SQL Assistant is not preferable if data is too huge in terms of records counts or row length. As it operates at row level, importing huge volume of data may take long time. For such cases, use FASTLOAD or MULTILOAD utility.
a) EXPLORER TREE: Explorer Tree is very useful and least used by most of the people working on Teradata SQL Assistant. I prefer having Explorer Tree enable all the time as it can save huge number of man hours. To enable EXPLORER TREE, go to
VIEW –> EXPLORER TREE
Once it is enabled, you can see it on left pane. Now Right Click on the Explorer Tree and Select ADD option. Enter the name of DATABASE and Click on OK. Once pressed OK, that DATABASE is added to EXPLORER TREE. You can now explore all the objects in that database. Right Click on the object and you can find several options which will help you in checking the Definition of the object; Browse option will give you SELECT query to retrieve all the records from the table. In the new version of Teradata SQL Assistant, more options are available which can help in generating various queries in Query Window.
b) There are few options (TOOLS–>OPTIONS) available in the Teradata SQL Assistant which you should consider while using it:
i) General: Close Answerset windows before submitting a new query: This will close the answerset window before executing new query. So if you wish to have multiple Answerset window , make sure that this option is uncheck.
ii) General: Display the string for NULL data fields: Set the String you want to show in the ANSWERSET for columns values which are NULL. It can be ? or you can mention your custom string like NULL etc.
iii) General: Display Dates in format: Teradata SQL Assistant displays date columns in the format you select here. So in Table as Data Format can be different however while displaying it , TSA may change the format as the one specified here.
iv) Query: Submit only the selected query text when highlighted: This will make sure that only SQL which you select is executed else all the SQL queries starting from the top will be executed.
v) Also, you can enable HISTORY; go to VIEW–> Show History. You can select the SQL Query from the history window and you don’t have to write it again. It will save time. Also you can refer to History to check result of any previously executed query.
vi) There are few keyboard shortcuts which will help in better and faster completion of work:
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 & Replace