The most widely used window of Aginity Workbench application is the Query Analyzer window that allows exploring the database schema and executing SQL queries.
Query Analyzer is the main window that allows you to explore your database schema and execute SQL queries.
You can simultaneously connect to several database servers. A new Query Analyzer window is opened for each new connection.
You can use the Ctrl+Tab key combination or the Window menu for switching between several Query Analyzer windows.
The Query Analyzer window consists of three major parts: the Object Browser, the SQL Editor, and the Result Set.
If you use multiple monitors, you can move the Query Analyzer window to one of them. On the View menu, click Make Standalone Window and drag the window to another monitor.
The left section of the Query Analyzer window is called the Object Browser.
You can manage the visibility of the Object Browser. Press CTRL+B to hide or display the object tree.
If the Do not obtain HCAT information (BigSql) option (applies if you are connected to a Big SQL database) is selected in the application options, all Hadoop tables (managed and external) are displayed under the Tables nodes, and the External Tables node is empty. Also, certain Hive-provided information (such as storage options, SerDe parameters, and partition columns) is not automatically loaded for all tables at once into the Object Browser, but is loaded for an individual table on demand. To retrieve this information for a specific table, expand an appropriate node under the table node. The following image shows that the storage options are displayed for an external Hadoop table when the Storage Options node is expanded.
If you have connected to a Big SQL database and have federated servers configured on your BigInsights instance, you can see the servers under the Linked Servers node (as shown below). By navigating down the hierarchy displayed in the Linked Servers node, you can see information about the database entities in the federated database. If nicknames are created for objects on federated servers, the Aliases/Nicknames node in the Object Browser displays them. Icons in that node differentiate aliases (local objects) from nicknames (remote objects).
To quickly show or hide the schema groupings in the Object Browser, select or clear Group by Schema on the View menu. To show or hide the schema name in the object names, select or clear Entity Schema on the View menu.
If this is selected in the options, the table size of each analyzed managed or external Hadoop table is displayed in parentheses after the table name and the total size of all analyzed tables in a schema is displayed after the schema name, as shown below. An asterisk (*) after the schema size indicates that the schema contains unanalyzed tables.
By right-clicking a database, table, or another object, you can access functions specific to the corresponding object type.
The Object menu on the menu bar provides the same functions, as the shortcut menu available by left-clicking a tree node.
The following are some of the available functions, by object type:
If the Do not obtain HCAT information (BigSql) option (applies if you are connected to a Big SQL database) is selected in the application options, the script generated by the Script Database command will contain incomplete DDL for tables: certain Hive-related information, such as storage options, SerDe parameters and partition columns, will be missing. As a workaround, you can generate a complete DDL script for an individual Hadoop table by right-clicking the table in the Object Browser, pointing to Script, and clicking the appropriate DDL command on the menu.
You can create and apply an entity filter to the list of objects in the Object Browser to display the objects that are relevant to you and hide all other objects. You can also turn on a default filter to apply it automatically each time you connect to the target database.
If you don't see some of the objects in the Object Browser, they might be hidden by an applied entity filter.
Remove the filter. If a default filter has been turned on, it will be applied to the Object Browser each time you open a new Query Analyzer window or connect to a target database. You can turn off the default filter altogether.
The section in the upper right of the Query Analyzer window is used for editing SQL scripts.
To add a new tab to write a SQL query in, click New tab. You can also add a new tab by pressing CTRL+T if the Query Analyzer window is currently open.
To switch between several tabs in the active Query Analyzer window, press CTRL+F6 and CTRL + SHIFT + F6.
By right-clicking anywhere in the SQL Editor, you can access a number of useful additional features and SQL query management options. For example, you can override the row limit for the result set, use syntax highlighting and the code auto-complete capability, and more.
If the name of a database entity or an entity column selected in the Object Browser needs to be inserted into the SQL Editor, double-click the column. The column name will automatically be inserted into the spot where the cursor is currently positioned in the open Query Editor. You can choose from multiple text-formatting options by selecting Tools > Options >Object browser, and then selecting the most appropriate format from the On column double-click insert drop-down list.
In addition, you can drag any node from the Object Explorer tree to the SQL Editor to insert the text where the pointer is currently positioned, or at the point where the text is copied into the Query Editor (this behavior can also be customized using the Tools/Options windows).
If code autocompletion is enabled in the query options for a currently open SQL Editor tab, Workbench displays a list of suggestions for a name (for example, a table name) as you start typing it and press Ctrl+Space, as shown in the example below. These suggestions are possible matches from the list of database objects and other items in the current database.
Autocompletion does not limit the object choices to the current database schema that is selected in the Schema list. The result set from autocompletion is a distinct list of object names that you have access to with your credentials. Therefore, if Table A, for example, exists in two schemas (schema 1 and schema 2), autocompletion will only show Table A once. It's up to you to type the schema into the SQL Editor to make sure that you use the correct one.
If the application options specify automatic invocation of autocompletion, you can also display a list of related items after typing a full name and a period (.). For example, after you type a full table name and then a period, Workbench automatically displays a list of the columns from the table.
You can select other autocompletion options in the application options, such as whether to display a list of related parameters automatically or manually.
The SQL Editor can be split horizontally or vertically into two panes or into four panes. You may find a split view useful when working with a long, multistep query. You can see and edit two or more portions of the code in the panes that you otherwise couldn't see and edit in the SQL Editor at the same time. You can scroll the contents in one pane independently of the other panes.
To split the SQL Editor:
You can display information for a database object in a tooltip when you point to its name in the SQL Editor. For example, in your SELECT query on a SQL Editor tab, you can point to the table name to view a description of the table in a tooltip. To view details for a database object (for example, information about the columns of a table) in an expanded version of the tooltip, pause the pointer over the object name.
Tooltips are available for database objects if Enable Quick Info when the mouse hovers over a word in editor is selected in the application options.
The section at the bottom of the Query Analyzer window is called the Result Set. The section displays the SQL Query execution result in two tabs: the Output tab, and the Result tab.
The Output tab has three sub-tabs: Standard, providing a brief summary of the executed query, Text, providing a detailed description of the execution process, and Grid, allowing a grid view of the execution result.
The system displays the Output tab regardless of whether or not an error has occurred during a SQL script’s execution. If multiple statements were executed, and the server has returned several result sets, the result tabs are added for those queries that had been executed before the failed execution took place.
If you do not want the Text and Grid sub-tabs to be displayed in the Output tab, click Tools, select Options > Result set options /Output options, and then clear the Enable Text and Grid Output check boxes.
The Result Set allows you to drag a column from the Results tab, and perform grouping by this column. To do this, drag the column that you want to group by to the drag a column header here area.
You can export query execution results to the Excel 2003 XML or CSV formats. To do this, right-click anywhere in the Result tab, and then select Export. .
The Menu bar provides access to several menus that are used to manage the bulk of the system’s functionality. The Toolbar is located under the Menu bar and provides access to the most frequently used options.
The Schema list on the toolbar displays the name of the current schema in the database. By default all queries are executed in the current schema. When you open the Query Analyzer window for the first time, this list shows the schema associated with the user name that you used to connect to the database. You can change the current schema by selecting an appropriate schema in the list.
The Path list on the toolbar displays a list of the default schemas set in the database. If the date being queried is not found in the current schema selected in the Schema list, Workbench will attempt to execute the query in the schemas listed in the Path list.
The Status bar is located under the Result Set section. It shows helpful status information. If you select more than 1 row in the result set, the Status bar can show the minimum, maximum, average, and other data. To make this feature available, click the drop-down arrow in the right side of the Status bar, and select the options that you want to be displayed.