Transitioning from SQL Assistant to Studio Express
This article is intended to help users as they transition from Teradata SQL Assistant to Teradata Studio Express. Included are some of the more frequently used SQL Assistant features along with a detailed 'How To' guide for the same features in Studio Express. Note: this article is relevant for Studio Express 15.10 or later.
The following topics are discussed:
- Connecting to a Database Server
- Adding a Database to the Data Source Explorer
Using the SQL Editor/Query window
- Entering SQL Statements and Executing Queries
- Syntax Checking and Code Assist
- Clearing Query window
- Displaying Line Numbers
- Executing Highlighted Text
- SQL Templates
- Named Parameters
- Audible Notification
- Changing the Font
- Formatting and Syntax Handling
- SQL Statement Outline Marks
- Rectangular Block Selection
- Result Set Viewer/Answerset Options
- Exporting Data from a Table
- Loading Data into a Table
- Opening an existing SQL file into Studio Express
- Saving a SQL Query to the File System
- Changing Shortcut Keys (Key Bindings)
- Importing SQL Assistant History into Studio Express
- Getting Help
In SQL Assistant, the user would define an ODBC or .NET Data Source (Tools>Define ODBC Data Source, for example).
Studio Express uses JDBC to connect to your Teradata Database, Aster Database, or Hadoop system. ODBC is not supported with Studio Express. The Teradata JDBC driver, Aster JDBC driver, and HiveServer2 (v0.13) JDBC driver come embedded in the Studio Express product. 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.
Studio Express provides an option to create connection profiles for additional database servers other than Teradata. You must provide the specific database server's JDBC driver for databases other than the default connection profile types: Teradata Database, Aster Database, or Hadoop Hortonworks. Refer to the section, Connecting to Non Teradata Database, for configurating connections to Oracle, DB2, or SQL Server.
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.
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.
NOTE: In Studio Express, the default Teradata Mode (TMODE) is set to ANSI and the default TMODE setting with SQL Assistant is TERA. As the recommended TMODE setting is ANSI, users can change the TMODE to TERA. To change the TMODE property, press the Add button and select the property TMODE. Choose the property value TERA and click OK to replace the existing TMODE property. Please refer to the Teradata Documentation (SQL Request and Transaction Processing) for differences between Teradata and ANSI Session Modes. One particular difference is that by default, character comparisons are always CASESPECIFIC in ANSI session mode, where character comparisons are always NOT CASESPECIFIC in Teradata session mode.
Once you have changed or added new JDBC Properties, you can set your new properties as the default properties for any new connection profiles by clicking the Save Properties button. This will save the properties to a file in your workspace called jdbcConnectionProperties.properties located in your workspace in the <WORKSPACE>/.metadata/Teradata directory.
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.
With Studio Express, you can display multiple connection profiles going to the same (or different) Database Server(s). This is useful when you want to compare the database objects or, for the same Database Server, use filters to control what database objects are displayed in one connection profile versus the other. For example, one connection profile may require quering your metadata databases and include filters to just include those databases. And the other connection profile (to the same Database Server) is used for activities that involve a semantic layer so provide a filter to just include those databases.
Refer to the section below, Creating Database Tree Filters, for the steps to create a filter on a Data Source Explorer folder.
In SQL Assistant, go to Tools and select 'Change Password ...' menu option
In Studio Express, select the database node in the Data Source Explorer tree and right click. Choose Teradata>Change Password... menu option.
In SQL Assistant, the user would create a non Teradata Data Source and choose it for the connection
In Studio Express, the default database types are Aster, Hadoop, and Teradata. To connect to DB2, Oracle, or SQL Server, the user would do the following:
- Go to Window>Preferences>Teradata Datatools Preferences>New Profile Connection Types and enable the non Teradata connection type in the New Profile Connection Types list. The default values are Aster Database, Hadoop System, Hortonworks, and Teradata Database. In the screen capture below, we have checked the Oracle connection type.
- Press the New Connection Profile and choose Oracle. Enter a Name for the connection profile and press Next.
- Click the 'New Driver Definition' button, and create an Oracle Driver definition.
- Choose the Oracle Database and enter a Driver name, then click the JAR List tab to specify the location of the Oracle JDBC driver. Note: You must download from Oracle the Oracle JDBC driver and place it on your desktop. Then press the Add JAR/Zip... and specify the location of the JDBC driver jar.
- Click OK to return to the New Connection Profile Wizard. Here you will specify the SID, Host, User name and Password, as well as any optional JDBC properties. Click the Test Connection button to test the connection, then click Finish to create the connection profile and return to Studio Express.
In SQL Assistant, the user would right click in the Database Explorer and choose 'Add Database'.
In Studio Express, the Data Source Explorer view provides the database tree with its database objects presented in folders, such as Databases, Tables, Macros, Views, etc. By default, the database tree is presented in a hierarchical display with child databases and users nested under their parent database or user. Also, by default, all of the database and users are displayed. To change this and only add one database at a time, the user would follow these steps:
- Go to the Data Source Explorer Preference page and uncheck the option 'Show Databases and Users in Hierarchical Display'
- Select the 'User Choice' option.
- Return to the Data Source Explorer, right click on the Databases folder and choose Teradata>Load Database...
- This will invoke the Add Database dialog. Enter the name of the database or user you wish to add and click the Add button. You can repeat this process, adding additional names. Close the dialog when all names have been added.
- The Databases folder is refreshed and the newly added database or user is loaded in the Data Source Explorer.
Another option provided in Studio Express is to set the Root location for the database tree. If you would prefer to display the database tree in hierarchical format (nesting child databases and users) but still reducing the number of databases and users displayed in the tree, you can set the Root of the database tree. This will restrict the display to your database or user and its child databases and users. For this option:
- Go to the Data Source Explorer Preference page and choose the 'All Database and Users' option and check the option 'Show Databases and Users in Hierarchical Display'
- Next, return to the Database Source Explorer and right click on the Databases folder and choose Set Root...
- This will invoke the Set Root Database dialog. Enter the Root database or user name and click OK.
- The Databases folder is refreshed and the ROOT is set to the value specified.
- Open the root database or user and any nested child databases or users are loaded in the Data Source Explorer.
Studio Express allows you to set a filter on the folders displayed in the Data Source Explorer (DSE), filtering out unwanted database objects. This applies to not only the Databases folder, but also the Tables, Views, Stored Procedures, and User Defined Functions. Setting a filter on the Database folder is another way to control what databases you see in the DSE. The following are the steps to create a filter:
- Right click on the DSE folder and choose the Filter... option. This will invoke the Filter Dialog.
- Next, in the Filter Dialog, uncheck the 'Disable filter' option and choose Expression or Selection. The Expression option allows you to choose between such expressions as: 'Starts with the characters', 'Contains the characters', 'Ends with the characters', 'Does not start with the characters', 'Does not contain the characters', and 'Does not end with the characters'. The Selection allows you to choose the objects from the list to be included or excluded in the display.
- Click OK to reflect the filter in the DSE tree.
In SQL Assistant, the user would enter the SQL statement in the Query window and press the feet icon, . The statement(s) is executed and the result set is displayed in the Answserset window. In SQL Assistant, users can also press the F5 key to execute the SQL statements.
In Studio Express, the user would open a SQL Editor associated with the connection profile. Select the connection profile in the Data Source Explorer and press the toolbar option, 'Open SQL Editor', , or choose File>Open SQL Editor.
Then enter the SQL statement in the SQL Editor and press the execute icon, . The user can also press Ctrl+ALT+X. The statement(s) is executed and the result set is displayed in the Result Set Viewer. The Execute icon is available on the top toolbar, as well in the SQL Editor toolbar. The user can also right click in the SQL Editor and choose an 'Execute' option from the context menu.
For multiple statements, the Execute All option, , is provided. This will send all of the statements in the SQL Editor to the database to be executed and return a group of result sets displayed in the Result Set Viewer.
As the user enters SQL statements in the SQL Editor, code assist will help the user with a list of 'next' words in the SQL syntax. By default, Code Assist Autoactivation is set to true. The user can toggle this option on or off by pressing the Code Assist Autoactivation icon, on the toolbar.
Studio Express parses the SQL statements entered in the SQL Editor and underscores syntax errors in red. It uses the grammar for the version of the database that is chosen in the Connection Profile drop down list in the SQL Editor. A list of expected words is provided if the user hovers at the error location.
The user can choose to ignore the errors and still execute the SQL statement using the Execute All toolbar icon or Ctrl+Alt+X key sequence.
In SQL Assistant, choose the top level toolbar option, 'Clear Query', to remove the text in the Query window.
In Studio Express, choose the SQL Editor toolbar option, 'Clear Query' , to remove the text in the SQL Editor.
In SQL Assistant, go to Tools>Options>Code Editor and check the 'Display line numbers' option.
In Studio Express, right click on the left margin of the SQL Editor and check the 'Show Line Numbers' option.
In SQL Assistant, go to Tools>Options>Query and check the option ‘Submit only the selected query text, when highlighted’.
In Studio Express, select the text in the SQL Editor, right click and choose the option ‘Execute Selected Text’.
In SQL Assistant, go to Help>Query Builder and choose a template from list of SQL Statements.
In Studio Express, in the SQL Editor, go to the beginning of the line and enter Ctrl+Space. A pop up window appears. Scroll down and choose the SQL template from list. Once the template is presented in the SQL Editor, press the tab key to move from value to value within the template definition.
In SQL Assistant:
- Usage: SELECT * FROM db.customer WHERE region = ‘?region’;
- Go to Tools>Query and check the option ‘Allow use of Named Parameters in queries’
- Run the query and enter the value for the region parameter.
In Studio Express:
- Usage: SELECT * FROM db.customer WHERE region = ?\region;
- No preference is needed. Within the SQL Editor, choose the option to execute the query. The Configure Parameters dialog appears for the user to enter the value for the region parameters.
- Studio Express also allows for non-named parameterized queries to be executed, such as " SELECT * FROM db.customer WHERE region = ?;"
- As with SQL Assistant, parameter data can also come from a file. Click on 'Specify file' and an ellipse icon,, will appear. Click on the ellipse to choose the file that contains the parameter data.
- Also note that in Studio Express, parameters ('?') are used for only data value substituions in the SQL. It does not perform direct string substitution as in SQL Assistant.
After executing the parameterized query, users can view the parameters in the SQL History by scrolling over to the Parameters column and double clicking on the Parameters button. The user can then re-execute the same query from the SQL History and it will prompt the user for new parameters.
In SQL Assistant, go to Tools>Options and set the option ‘Provide audible notification when query ends’.
In Studio Express, go to Window>Preferences>Teradata Datatools Preferences>SQL Handling and click the Sounds tab. Choose the option 'System Beep' or 'Select Sound' and choose a sound file for successes and failures. There is a shortcut to the SQL Handling Preferences page by clicking the preferences icon, , on the SQL Editor toolbar.
In SQL Assistant, right click in the Query window and choose 'Set Font' from the menu.
In Studio Express, go to Window>Preferences>General>Appearance>Colors and Fonts. For changing the font in the SQL Editor, select Basic>Text Font. Click the Edit button and choose the font and size desired. To restore the font back to its default, select Text Font and click Restore Defaults. For changing the font in the Result Set Viewer, choose the Dialog Font.
In SQL Assistant, go to Tools>Options>Code Editor and choose formatting and syntax color options.
In Studio Express, for Formatter Preferences, go to Window>Preferences>Teradata Datatools Preferences>SQL Formatter Preferences and choose formatting options. For Syntax Color, go to Window>Preferences>Data Management>SQL Development>SQL Editor>Syntax Coloring and choose syntax color options.
In SQL Assistant, outline marks are always displayed in the Query window.
In Studio Express, the outline is presented in a separate view called the Outline view. To open the Outline view, go to Window>Show View and choose Outline. The Outline view will appear at the bottom of the screen. Click and drag the Outline view up to the right hand side of the SQL Editor. As you select SELECT Statements from the Outline view, the SQL statement is highlighted in the SQL Editor window.
Rectangular block selection or column mode selection allows you to select the text vertically within the text.
In SQL Assistant, SHIFT+ALT+arrow key will allow the user to select text in column mode.
In Studio Express, SHIFT+ALT+A enables the SQL Editor for column mode selection or also known as Rectangular Selection. SHIFT+ALT+A again will return to normal mode selection.
In SQL Assistant, go to Tools>Options>Answerset.
In Studio Express, go to Window>Preferences>Teradata Datatools Preferences>Result Set Viewer Preferences page. There is also a shortcut to the Result Set Viewer Preferences page by clicking the preferences icon, , on the Result Set Viewer toolbar.
In SQL Assistant, go to Tools>Options>General. For the option 'Use a separate Answer window for' and select 'Each Result Set'.
In Studio Express, asa mentioned above, choose the Execute All option, . This will send all of the statements in the SQL Editor to the database to be executed and return a group of result sets displayed in the Result Set Viewer. Once you have the result sets, you can display them side by side or in a tab format (choose toggle Sash or Tab, ).
In SQL Assistant, right click in Answerset and close the individual Answerset window.
In Studio Express, in the SQL Editor toolbar, choose Clear Result Sets, , to close all opened result set windows.
In SQL Assistant, go to Tools>Options>Query and check the option ‘Close Answerset windows before submitting new query’.
In Studio Express, go to the Result Set Viewer Preferences and check the option ‘Reuse Result Set Viewer Window’.
In SQL Assistant, select the Answerset window and go to File>Save As. Choose the name of the file to save the answerset data.
In Studio Express, there are two options for saving the result set data. You can save the result set data to the file system (Export) or to a file stored in the workspace (Save). Select the Result Set window and from the Result Set Viewer toolbar, choose Save or Export. This will invoke the Export or Save Result Set dialog to choose the name of the file to save the result set data.
In SQL Assistant, go to Answerset and select the cells and press Ctrl+C. Open Excel file and press Ctrl+V.
In Studio Express, go to Result Set Viewer and select the cells and press Ctrl+C. Open Excel file and press Ctrl+V. To include Column Headers, set the Result Set Viewer Preference, 'Copy Include Column Headers'. Select the cells to copy, right click and choose the 'Copy Cells' menu option. In your Excel file, press Ctrl+V. The Copy Cells option will copy the data using HTML format, which allows the copy to include format and font information, that can then be pasted into other products, such as Excel, Outlook, or HTML.
Below is an example of changing the background and foreground of cells in the Result Set Viewer, selecting the cells and copying (using 'Copy Cells') then pasting the cells into Excel. The 'Copy Include Column Headers' and 'Copy Include Grid Lines' preferences were also checked.
In SQL Assistant, go to the File menu and toggle the 'Export Results' option. Then enter your SELECT statement into the Query window and click the icon to execute the query. The Export File dialog appears for you to enter the name of the file to export the data to.
In Studio Express, go to Window>Preferences>Teradata Datatools Preferences>SQL Handling preference page and set the Results Handler to 'Teradata Export Wizard (File Export)'.
Then enter your SELECT statement into the SQL Editor and click the icon to execute the query. The Export Data wizard appears for you to enter the file options to export the data to. As mentioned above, there is a shortcut to the SQL Handling Preferences page by clicking the preferences icon, , on the SQL Editor toolbar.
In SQL Assistant, go to Tools>Options>Data Format and choose the CLOB and BLOB size options.
In Studio Express, go to Window>Preferences>Teradata Datatools Preferences>SQL Handling and choose the tab Teradata or Aster. Enter the CLOB and BLOB size options. If a LOB is encountered while processing the result set, a pop up dialog is presented for the LOB dispositioning.
In SQL Assistant, you would repeat the steps above by going to the File menu and toggle on the 'Export Results' option. Then in the Query window, run a SELECT * statement for the table in question. This will launch the Export File dialog.
In Studio Express, locate and select the table in the Data Source Explorer. Right click and choose the Data>Export Data... option. This will launch the Export Data Wizard.
- Choose the name of the Output File and File Type (Delimited Text, Excel (.xlsx), Excel 97-2003 (.xls))
- Check the box if you want column labels in the first row.
- Choose the File Options, such as column delimiters, character string delimiter, and file encoding. Click Next
- Here you can filter out columns you don't want to export by unchecking the 'Use' checkbox.
- Click Finish to run the Export Data job.
- To view the output from the Export Data job, you will need to open the Transfer History View. Go to Window>Show View and choose the Transfer History View from the drop down menu.
7. This will open the Transfer History View. Select your Export Data entry from the transfer history table and click the Job Details toolbar icon, , to view the details of the export job.
In SQL Assistant, go to the File menu and toggle on the 'Import Data' option. Then enter your INSERT statement into the Query window and click the icon to execute the query. The Import File dialog appears for you to enter the name of the file to import the data from.
In Studio Express, locate and select the table in the Data Source Explorer. Right click and choose the Data>Load Data... option. This will launch the Load Data Wizard.
- Browse the file system for the Input File.
- Choose the File Type (Delimited Text, Excel (.xlsx), Excel 97-2003 (.xls)).
- Check the box if your input file has the column labels in the first row.
- If you want the load to stop after 'X' number of errors that occur, check the box and enter the error row count value 'X'.
- Check the box if you want to lock the table during the load
- The Load Data Wizard will detect if data already exists in the table. Check the box if you are replacing existing data. Otherwise, it will append the data to the table.
- Choose the File Options, such as column delimiters, character string delimiter, file encoding, and start loading row number. The Load Data Wizard will display a preview of the data to be loaded, using the file options specified.
- Click Finish to run the Load Data job.
- To view the output from the Load Data job, you will need to open the Transfer History View. Select your Load Data entry from the transfer history table and click the Job Details toolbar icon, , to view the details of the load data job.
In SQL Assistant, go to the File menu and choose Open Query... This will launch the Open Query dialog to locate the SQL file. The SQL file is opened in the Query window.
In Studio Express, SQL files are managed by the Project Explorer. When Studio Express is first launched, a 'SQL' project is created for you. You can create additional projects to organize your SQL files via the Project Wizard. Go to File>New Wizards>Project.
To open an existing SQL File in Studio Express, go to the File>Import... menu:
- Select General>File System
- Press 'Next' and browse for the directory that contains the SQL File. This will display the folder in the left hand side with all of the files in the right hand side.
- Check the box next to the file(s) that you want to import into Studio Express.
- Next, browse your Studio Express workspace to locate a project folder to import the SQL file into ('Into folder'). Choose the SQL folder and click Finish.
- Next, go to the Project Explorer view and open the SQL folder.
- You can also import SQL Files directly into your SQL folder by right clicking on the SQL folder in the Project Explorer and choosing Import... menu option. This will launch the same Import dialog.
- Locate your SQL file. Right click and choose the menu option 'Open With>Teradata SQL Editor'. This will open the SQL file in the SQL Editor. The SQL Editor will default to the connection profile selected. You can change this to a different connection profile in the Connection Profile drop down list.
As with SQL Assistant, drag and drop is yet another way to import SQL file into Studio Express. Locate the SQL file in Windows Explorer, drag and drop it into the Project Explorer. This will invoke the File Operation dialog. Choose 'Copy files' option to copy the file into your SQL folder.
Another feature of the Project Explorer is the ability to compare two or three SQL files. Select the SQL files in the Project Explorer, right click and choose the Compare With>Each Other menu option.
This will open the files in the Compare Editor, showing a side-by-side comparison with toolbar buttons to navigate the differences.
In SQL Assistant, select the Query window and go to File>Save As menu option. Then choose the file location to save the query in the Query window.
In Studio Express, go to the SQL Editor toolbar and choose 'Export to File System'. Then choose the file location to save the query in the SQL Editor window.
In SQL Assistant, go to Tools>Customize... and press the Keyboard... button. Select the command from the list of Categories and Commands.
Studio Express has tried to replicate the shortcut keys provided by SQL Assistant. But you can change the key binding for a command via the Keys preference page. In Studio Express, go to Window>Preferences>General>Keys. Select the command from the list of Commands. The 'key binding' is displayed for you to edit.
A 'key binding' is the assignment of a 'key sequence' to a command. A 'key sequence' is one or more key strokes. A 'key stroke' is the pressing of a key on the keyboard, while optionally holding down one or more of these modifier keys:
Option on the Macintosh),
Command (only on the Macintosh.) For example, holding down
Ctrl then pressing
A produces the key stroke
Ctrl+A. The pressing of the modifier keys themselves do not constitute key strokes. Keyboard shortcuts should not be more than four strokes in length.
You can restore bindings to their default values by pressing the Restore Defaults button.
Studio Express provides an option to import the history file from SQL Assistant. Go to the Teradata SQL History view and press the Import History option, , on the SQL History toolbar. This will invoke the Import History dialog.
Chose the Source Type (.mdb, .xml) and browse for your SQL Assistant history file. Click OK to import the history data. You will notice that the Destination column is blank for the entries that were imported from SQL Assistant.
In SQL Assistant, go to the Help menu option and click 'Help Topics'.
In Studio Express, go to Help> Help Contents. Navigate the help topics on the left or type in a search text in the Search box.
- Context sensitive help is also available on dialogs or views by pressing the F1 key.
- Studio Express also provides help via the Teradata Studio Forum located on the Teradata Developer Exchange site. Open a new topic or post your comments or questions to an existing topic.
- Studio Express is a fully supported product. Customer support is available around-the-clock, seven days a week through the Global Technical Support Center (GSC). To learn more, go to http://tays.teradata.com.