Teradata Studio Express
Teradata Studio Express is an information discovery tool that retrieves data from Teradata and Aster Database systems and allows the data to be manipulated and stored on the desktop. It is built on top of the Eclipse Rich Client Platform (RCP). This allows the product to take advantage of the RCP framework for building and deploying native GUI applications to a variety of desktop operating systems. Teradata Studio Express can be run on Windows, Linux, and Mac OSX operating systems. The Eclipse platform is designed as an open tools platform but architected so that its components (or a subset of its components) can be used to build RCP applications. This allows Teradata Studio Express to benefit from the many high quality Eclipse features available while focusing on value-add for Teradata and Aster Database.
Teradata Studio Express includes the following key features:
- Modular display that allows you to move panels around, creating a custom look and feel.
- Connection Profile Wizard to create connection profiles using the Teradata or Aster JDBC Driver.
- Data Source Explorer to view database objects, such as databases, users, tables, macros, stored procedures, user-defined functions, user-defined types, views, and triggers.
- Data Source Explorer menu options to run Stored Procedures, Macros, and User-defined Functions.
- SQL Editor to create, modify, and execute individual or multiple SQL Statements. This includes parsing the SQL statement for syntax errors and formatting the SQL according to selected formatting options (Teradata only).
- AutoCommit options allows user to manually control autocommit of SQL Statements.
- Support for Parameterized Queries including Named Parameters
- SQL Editor Templates (INSERT, SELECT, DELETE, UPDATE, and more)
- Result Set Viewer that displays result set data, allowing the user to select rows and cells, copy and format data, sort, hide, and move columns, and search for result set data values.
- SQL History view that stores historical data for executed SQL statements with time values and status information.
- Ability to export result set data as plain text, XML, HTML, CSV, as well as Excel format.
- SQL Query Builder to visually build and edit SQL queries (Teradata only).
- Data wizard to extract data from the Teradata or Aster Database to a file on the desktop.
- Data wizard to load data from the desktop file system to the Teradata or Aster Database.
- Table Data Editor to easily Update, Insert, or Delete data for a table.
- Smart Loader to easily load delimited text or Excel files into Teradata or Aster.
For detailed step-by-step demonstration of the Studio Express features, refer to the Studio Usage Videos article.
The Teradata Studio Express display includes the Teradata Studio Express Toolbar along with five view panels: the Data Source Explorer View, the Project Explorer View, the SQL Editor, the Teradata SQL History View, and the Teradata Result Set Viewer.
Teradata Studio Express contains a toolbar that provides buttons to allow quick access to functions that are used while creating and modifying SQL files.
A view can also contain its own toolbar and drop down menu. These toolbar buttons and menu items provide actions on the data managed by the view. Included in the view toolbar are buttons to minimize, maximize, and restore the view. Maximizing the view will cause the view to take over the entire Teradata Studio Express window display. Click the Restore button to return the view back to its previous display.
You can customize the look and feel of Teradata Studio Express by moving view panels within the main display window. A view can be moved by selecting the view and dragging it to a new location. As you move the view over the display, the mouse pointer will change depending on where you want to drop or dock the view. You can also drag the view outside the display area. The view will then become a "detached" view. You can also remove a view panel by clicking the "X" on the view tab. A view can be re-displayed by selecting the view from the Window>Show View option in the main menu. Or to return the views to their original display, click Window>Reset Perspective...
The Data Source Explorer View panel contains the database object tree display. It is here that you can create connection profiles to your Teradata or Aster Database systems and interact with the database objects. The Data Source Explorer view provides a hierarchical view of Teradata Database objects, nesting child databases and users within the parent database or user. Preferences are provided that allow you to choose which objects to load and display in the Data Source Explorer.
You can create connections to your database using the Connection Profile Wizard. The wizard is invoked from the Data Source Explorer by right clicking on the Database Connections folder or selecting the 'New Connection Profile' button, , from the Data Source Explorer toolbar.
Teradata Studio Express provides an option to create connection profiles for additional database servers other than Teradata. You must provide the specific database server JDBC drivers for databases other than Teradata or Aster Database.
Select Teradata Database from the Connection Profile Types, enter a Name and click Next.
The Connection Profile Wizard is based on a driver template that references an embedded Teradata JDBC driver. Enter the database server name, user name, password, and default database name into the wizard dialog screen. The Authentication Mechanism allows you to choose an advanced logon mechanism, such as LDAP or Kerberos, or select the default mechanism, PASSWORD_PROTECTED. If you wish to save your password to disk, check the Save Password box. Otherwise, the password is stored in the profile and only active throughout the Teradata Studio Express session. When Teradata Studio Express is closed, the unsaved password is removed before the profile is written to disk. You will be prompted for the password the next time you connect to that profile.
Optionally, you can choose a different Teradata JDBC driver than the embedded Teradata JDBC driver. You can also add or remove the JDBC properties, as well as save the properties to an external file. The external file is called jdbcConnectionProperties.properties and located in the workspace .metadata/Teradata directory. The jdbcConnectionProperties.properties file allows you to share a set of JDBC properties with other Teradata Studio Express users. If the file exists when creating a new Teradata connection profile, it will load those property values into the list of JDBC connection properties. If the file exists but it is empty, the connection wizard will assume that no JDBC properties are specified and the Teradata JDBC driver will use the default settings.
You can also cache schema metadata, saving time when loading the database objects in the Data Source Explorer. Click the Cache Properties button to select schemas (or databases) to cache.
Aster Connection Profiles are created using a similar connection wizard with the embedded Aster JDBC Driver. Database server name, user name, password, port number, and database name fields are provided for user input. You can also cache Aster Database schema metadata to save time loading the Aster database objects in the Data Source Explorer.
When Teradata Studio Express is launched, a SQL Editor area is provided for you to enter your SQL statements. Once you have a connection to your Teradata or Aster database, you can begin to enter SQL statements in the SQL Editor window. Click the Save button to save the SQL Editor to a file in the workspace or Export to export the file to the desktop file system.
Another option to create a SQL file is through the Data Source Explorer. Within the Data Source Explorer, select your database connection and click the SQL Editor icon, , from the Data Source Explorer toolbar. This will create a SQL file in the SQL Editor with the name 'SQL Editor [n]' and the connection profile set to your database connection. You are now ready to enter your SQL statement.
You can also open an existing SQL file, located in the Project Explorer, into the SQL Editor and associate it with a connection profile. SQL files can be imported into the Project Explorer from your local desktop file system. Right click on the SQL project and choose the Import menu option.
The SQL Editor provides a scratchpad for you to enter your SQL statements. As an option, you can enable the auto activation of code assist by clicking the toolbar button. If this option is enabled, as you type your SQL statement, code assist will pop up annotations showing a list of expected keywords. If a database name is entered followed by a dot, the content assist will display the list of tables from your database for you to select from. (Note: By default, auto activation of code assist is disabled. Also, you must select a Teradata or Aster Database connection profile type in order for SQL code assist to work.)
Code assist will also provide a list of SQL Templates. At the start of the SQL statement, hit the Cntrl+SPACE keys and a pop up list of templates is displayed. Scroll down until you find the template you want and double click on it to insert the template into your SQL Editor file. Anchor fields are provided that you can navigate by tabbing. You can also edit or add your own SQL Templates via the SQL Editor Preferences page.
Parameterized queries can also be created in the SQL Editor. The query contains parameters indicated by question marks. When the query is executed, the user is prompted for the parameter value(s) in the Configure Parameters dialog. Named parameters (appending backslash ,'\',<parameter name>) allows the user to specify a name for the parameter. When the Configure Parameters dialog is presented, the parameters names will be used to reference the paramters.
The SQL Editor also provides syntax coloring for your SQL statements. You can change the colors of comments, keywords, identifiers, types, or other syntax items via the SQL Editor Preferences page.
When right clicking in the SQL Editor window, a SQL Editor Context menu appears with options to execute the SQL statement(s) in your SQL file.
The following options are presented:
- Execute all of the SQL statements
- Execute the selected SQL statement
- Execute the SQL statements as individual statements (with a commit occurring between statements)
- Execute the selected statement as one statement
- Execute the current statement where the cursor is located
- Execute the SQL statements as a single transaction (Aster only)
- Visual Query Builder (Teradata only)
You can also choose the Execute options from the SQL Editor or top level toolbar, , , .
An Autocommit checkbox is provided in the SQL Editor to allow the user to toggle Autocommit on or off. By default, Autocommit is ON. If the option is turned off, then the Commit and Rollback options are enabled for the user to manually control the commit of their SQL statements to the database or rollback if an error occurs.
Teradata Studio Express provides a graphical interface for creating SQL statements for Teradata, referred to as the SQL Query Builder. You can edit an existing statement using the SQL Query Builder by selecting the statement within the SQL Editor and choosing the ‘Edit in SQL Query Builder…’ menu option. (Refer to figure above).
In addition, you can open a SQL file in the SQL Query Builder via the Project Explorer menu item, Open With...>Teradata SQL Query Builder.
This option will open the SQL Query Builder inside the SQL Editor view. This allows you to drag and drop the table objects from the Data Source Explorer into the diagram area of the SQL Query Builder.
You can also add additional tables and table joins by right clicking in the SQL diagram area and select Add Table or Create Join options. When adding a table, a list of tables and views is presented for you to choose from. Click OK to save your SQL statement back to the SQL Editor.
The Data Source Explorer provides an option to extract and load data using Teradata Load and Teradata Export. Large tables and files will use the JDBC FastLoad or FastExport option depending on the number of rows and column data types supported. Otherwise the JDBC load batch interface is used. Right click on the table and select the option from the Data drop down context menu.
Selecting the Load Data option invokes the Load Data Wizard. The wizard will prompt the user for the file to load and define the necessary file format needed to identify columns when loading data into the table. File format options include the column delimiter (comma, semicolon, space, tab, or vertical bar “|”) along with the character string delimiter (double quote, single quote, or none) and File Encoding. If data already exists in the table, the load data will append or replace the existing data. You can also indicate whether column labels are in the first row, as well as whether to lock the table during the load process.
Selecting the Export Data option invokes the Export Data Wizard. The user provides similar information as the Load Data Wizard.
The status of the data load or export job is displayed in the Transfer Progress View. When the job has completed, an entry is placed in the Transfer History View. To open the Transfer History View in Studio Express, go to Window>Show View>Transfer History View. Refer to the section below regarding the Transfer History View.
Sample Contents is another option on the Data menu. The Sample Contents option will perform a “SELECT * FROM <table> SAMPLE 100" SQL operation on the table and display the results in the Teradata Result Set Viewer. If a Sample Contents invoked on an Aster table, a "SELECT * FROM <table> LIMIT 100" SQL operation is performed. The Sample (or Limit) size can be changed in the SQL Handling Pereference page.
The Smart Loader feature allows the user to load a text delimited or Excel file into a Teradata or Aster table. The Smart Loader is invoked from the Tables folder within the destination database.
Like the other load data utilities, the Smart Loader will prompt the user for the input file.
Next, the Smart Loader will scan the file to determine the "best" column types and present these to the user.
Clicking the ellipses button (...) provides an option to modify the column definition.
Next, the table DDL is presented. The user can choose the 'Show in Editor' option to save the table DDL to the SQL Editor and manually change the table definition. Otherwise, clicking Finish will execute the DDL statement and begin loading the data into the newly created table.
The status of the data load job is displayed in the Transfer Progress View. When the job has completed, an entry is placed in the Transfer History View. To open the Transfer History View in Studio Express, go to Window>Show View>Transfer History View.
The information in the Transfer History View is stored in an embedded Derby database. The data transfer job can be re-executed by selecting the entry and pressing the Re-execute toolbar button. The output of the transfer data job is displayed by pressing the Job Output button.
The Table Data Editor provides an enhanced feature to edit tables within the Data Source Explorer for Teradata or Aster tables. The data in the editor is broken up into pages, which allows for efficiency in updating large tables. Along with the standard insert, update, and delete, data filtering, sorting, and in-memory filtering are also supported. The user also has the option of exporting the data to Excel or Text files. It may be advantageous to export the data before it is modified.
The Table Data Editor is accessed via the table Data>Edit Data menu option. Select the table in the Data Source Explorer and right click and choose the Data>Edit Data... option.
The Table Data Editor will present the current rows of data from the table in a table grid display within the SQL Editor. You can click on a cell in the table grid to modify the cell contents or add a new row by clicking on the <new row> entry. A toolbar contains navigation aids and action buttons to control the row displayed, number of rows per page, page controls, as well as Filter, Sort, and Refresh actions.
Column operations are provided to hide or resize columns, find, replace or show values.
Row operations are provided to copy and paste row data, insert and delete rows, or mark rows for export.
Row background colors are used to indicate whether a row has been updated (rose), inserted (yellow), or deleted (grey strike-through). These colors can be changed, along with other Table Editor options in the Table Data Editor Preference Page.
Depending on the edit option, a SQL UPDATE, INSERT, or DELETE statement is created. SQL Preview toolbar button, , is provided to show the SQL commands that will be run. Click the 'Commit Changes to the Database' toolbar button, , to run the SQL and commit the changes. A status message is displayed to indicate the actions committed to the database.
Project Explorer View
The Project Explorer View panel provides a hierarchical view of the Projects and their resource files. It is here that you can select SQL files for editing or create new SQL files. SQL files can also be imported from or linked to your file system. When Teradata Studio Express is first installed, an initial project called 'SQL' is created for you. You can create additional projects using the New>Project... menu option.
Within the Project Explorer, you can also choose an option to compare two files. The Compare Option displays the two files side by side with buttons that allow you to jump from one difference to another or move changes from one file to another. This is very handy when comparing SQL files or result set data.
The Project Explorer also provides an option to execute one or more SQL files from list of files.
Explain, SELECT Statement, and Row Count Menu Options
An Explain shortcut key, ,in the toolbar or Tools drop down menu, will perform an EXPLAIN on a given SQL statement. You can also quickly invoke an EXPLAIN using the shortcut key sequence F6.
The SELECT statement option for tables in the Data Source Explorer generates a quick SELECT statement for the table in a SQL Editor window.
The Row Count option will perform a "SELECT Count(*)" SQL statement on a table or view in the Data Source Explorer.
The Result Set Viewer displays the result set or parameter data from executing SQL statements and running database routines, such as stored procedures, macros, user-defined functions, and triggers. The Result Set Viewer provides an enhanced display of the Result Set data. It displays the result sets as tabs within the Teradata Result Set Viewer, allowing the user to select rows or cells from the Result Set data table grid. The Result Set Viewer provides menu options to hide or show all columns, move and sort columns, find a value within the result set data, perform aggregate functions on selected numeric columns, filter column data, format cells, and copy cells. Select a row or cell and right click to display the menu options.
Multiple result sets can be displayed as tabs or in a side-by-side sash display.
Large Objects (LOBS) can also be displayed in the Result Set Viewer. When a query result is being processed that contains an LOB result column, a dialog appears for the user to specify where and how to store the LOB values on the file system.
A toolbar is also provided with options to rename the Result Set tab, show the SQL associated with the result set, switch the display between text and grid display, switch the display between tabs and side-by-side sash display, and save, export, and print result set data. SQL result data can also be saved to a file within your Project or exported to an external file in the file system.
If the Export or Export All option is chosen, the Export Result dialog is displayed. Select a file name, format, and export options. Then click the finish button to create the exported file. The Save option presents a similar dialog.
The following screen capture shows the result data with totals on integer columns as exported to an Excel file.
The SQL History View provides an enhanced display of the SQL History information. As SQL statements are executed from the SQL Editor or functions are run from the Data Source Explorer, an entry is placed in the SQL History. The SQL History view displays the data in a grid format, allowing the user to select rows and cells. The SQL History provides extensive information about the SQL execution, such as execution elapsed time, DBMS time, and fetch time, database server, row count, parameter display for macros and stored procedures, SQL statement, and SQL statement type. It provides options to copy cells or rows, edit the 'Note' or SQL for the SQL History entry, search for result history data, and re-execute SQL statements. Choosing the 'Edit SQL' option will open a SQL Editor file with the selected history entry's SQL statement. This allows users to further fine tune their SQL statements.
The SQL History view also provides a toolbar menu with options to delete rows or all of the history entries, filter columns, format cells, and sort columns, The SQL History uses an embedded Derby Database to manage the SQL History entries.
The Object Viewer contains more detailed information about the database objects. When an object is selected in the Data Source Explorer, it is automatically displayed in the Object Viewer. You can click on the tabs to display additional information on the database object.
Help is provided by clicking on the Help>Help Contents option in the main toolbar.
While Teradata Studio Express is not necessarily intended to replace the current Teradata SQL Assistant product, it provides an alternative for customers whose client platform includes systems other than Windows. Supporting multiple platforms (Windows 32/64, Linux 32/64, and Mac OS X) in this way allows customers to be more flexible when choosing a client operating system to connect to their Teradata or Aster Database system.
To download Teradata Studio Express, go to the Developer Exchange Downloads area and select Teradata Studio Express from the table.
To ask questions or discuss issues, refer to the Teradata Studio Forum and post your question.