SQream Acceleration Studio 5.3.3

The SQream Studio is a web-based client for use with SQream DB. The Studio provides users with all functionality available from the command line in an intuitive and easy-to-use format. This includes running statements, managing roles and permissions, and managing SQream DB clusters.

This page describes the following:

Getting Started

Setting Up and Starting Studio

Studio is included with all dockerized installations of SQream DB. When starting Studio, it listens on the local machine on port 8080.

Logging In to Studio

To log in to SQream Studio:

  1. Open a browser to the host on port 8080.

    For example, if your machine IP address is 192.168.0.100, insert the IP address into the browser as shown below:

    $ http://192.168.0.100:8080

  2. Fill in your SQream DB login credentials. These are the same credentials used for sqream sql or JDBC.

    When you sign in, the License Warning is displayed.

Executing Statements and Running Queries from the Editor

The Editor is used for the following:

  • Selecting an active database and executing queries.
  • Performing statement-related operations and showing metadata.
  • Executing pre-defined queries.
  • Writing queries and statements and viewing query results.

The following is a brief description of the Editor panels:

No. Element Description
1 Toolbar Used to select the active database you want to work on, limit the number of rows, save query, etc.
2 Database Tree and System Queries panel Shows a heirarchy tree of databases, views, tables, and columns
3 Statement panel Used for writing queries and statements
4 Results panel Shows query results and execution information.

Executing Statements from the Toolbar

The following figure shows the Toolbar pane:

../../_images/studio_editor_toolbar_5.3.0.png

You can access the following from the Toolbar pane:

  • Database dropdown list - select a database that you want to run statements on.
  • Service dropdown list - select a service that you want to run statements on. The options in the service dropdown menu depend on the database you select from the Database dropdown list.
  • Execute - lets you set which statements to execute. The Execute button toggles between Execute and Stop, and can be used to stop an active statement before it completes:
    • Statements - executes the statement at the location of the cursor.
    • Selected - executes only the highlighted text. This mode should be used when executing subqueries or sections of large queries (as long as they are valid SQLs).
    • All - executes all statements in a selected tab.

For more information on stopping active statements, see the STOP_STATEMENT command.

  • Format SQL - Lets you reformat and reindent statements.
  • Download query - Lets you download query text to your computer.
  • Open query - Lets you upload query text from your computer.
  • Max Rows - By default, the Editor fetches only the first 10,000 rows. You can modify this number by selecting an option from the Max Rows dropdown list. Note that setting a higher number may slow down your browser if the result is very large. This number is limited to 100,000 results. To see a higher number, you can save the results in a file or a table using the CREATE TABLE AS command.

Back to Editor

Executing Pre-Defined Queries from the System Queries Panel

The System Queries panel lets you execute pre-defined queries and includes the following system query types:

  • Catalog queries - used for analyzing table compression rates, users and permissions, etc.
  • Admin queries - queries related to available (describe the functionality in a general way). Queries useful for SQream database management:

Clicking an item pastes the query into the Statement pane, and you can undo a previous operation by pressing Ctrl + Z.

Writing Statements and Queries from the Statement Panel

The multi-tabbed statement area is used for writing queries and statements, and is used in tandem with the toolbar. When writing and executing statements, you must first select a database from the Database dropdown menu in the toolbar. When you execute a statement, it passes through a series of statuses until completing. Knowing the status helps you with statement maintenance, and the statuses are shown in the Results panel.

The following table shows the statement statuses:

Status Description
Pending The statement is pending.
In queue The statement is waiting for execution.
Initializing The statement has entered execution checks.
Executing The statement is executing.
Statement stopped The statement has been stopped.

You can add and name new tabs for each statement that you need to execute, and Studio preserves your created tabs when you switch between databases. You can add new tabs by clicking icon-plus , which creates a new tab to the right with a default name of SQL and an increasing number. This helps you keep track of your statements.

../../_images/statement_pane_adding_statement_5.3.0.png

You can also rename the default tab name by double-clicking it and typing a new name and write multiple statements in tandem in the same tab by separating them with semicolons (;).If too many tabs to fit into the Statement Pane are open at the same time, the tab arrows are displayed. You can scroll through the tabs by clicking icon-left or icon-right, and close tabs by clicking icon-close. You can also close all tabs at once by clicking Close all located to the right of the tabs.

Tip

If this is your first time using SQream, see First steps with SQream DB.

Back to Editor

Viewing Statement and Query Results from the Results Panel

The results pane shows statment and query results. By default, only the first 10,000 results are returned, although you can modify this from the Executing Statements from the Toolbar, as described above.

../../_images/studio_editor_results_5053.png

By default, executing several statements together opens a separate results tab for each statement. Executing statements together executes them serially, and any failed statement cancels all subsequent executions.

The following is a brief description of the elements on the Results panel views:

Element Description
Results view Lets you view search query results.
Execution Details view Lets you view execution details, such as statement ID, number of rows, and averge number of rows in chunk.
SQL view Lets you see the SQL view.
Save results to clipboard Lets you save your search results to the clipboard to paste into another text editor.
Save results to local file Lets you save your search query results to a local file.

Searching Query Results in the Results View

The Results view lets you view search query results.

From this view you can also do the following:

  • View the amount of time (in seconds) taken for a query to finish executing.
  • Switch and scroll between tabs.
  • Close all tabs at once.
  • Enable keeping tabs by selecting Keep tabs.
  • Sort column results.

In the Results view you can also run parallel statements, as described in Running Parallel Statements below.

Running Parallel Statements

While Studio’s default functionality is to open a new tab for each executed statement, Studio supports running parallel statements in one statement tab. Running parallel statements requires using macros and is useful for advanced users.

The following shows the syntax for running parallel statements:

$ @@ parallel
$ $$
$ select 1;
$ select 2;
$ select 3;
$ $$

The following figure shows the parallel statement syntax in the Editor:

../../_images/running_parallel_statements.png

Execution Details View

The Execution Details view lets you view a query’s execution plan for monitoring purposes. Most importantly, the Execution Details view highlights rows based on how long they ran relative to the entire query.

This can be seen in the timeSum column as follows:

  • Rows highlighted red - longest runtime
  • Rows highlighted orange - medium runtime
  • Rows highlighted yellow - shortest runtime
../../_images/execution_details_view_3.png

Viewing Wrapped Strings in the SQL View

The SQL View panel allows you to more easily view certain queries, such as a long string that appears on one line. The SQL View makes it easier to see by wrapping it so that you can see the entire string at once. It also reformats and organizes query syntax entered in the Statement panel for more easily locating particular segments of your queries. The SQL View is identical to the Format SQL feature in the Toolbar, allowing you to retain your originally constructed query while viewing a more intuititively structured snapshot of it.

The following figure shows the SQL view:

../../_images/sql_view_5.0.3.png

Saving Results to the Clipboard

The Save results to clipboard function lets you save your results to the clipboard to paste into another text editor or into Excel for further analysis.

Saving Results to a Local File

The Save results to local file functions lets you save your search query results to a local file. Clicking Save results to local file downloads the contents of the Results panel to an Excel sheet. You can then use copy and paste this content into other editors as needed.

Analyzing Results

When results are produced, a Generate CREATE statement button is displayed. Clicking this button creates a new tab with an optimized CREATE TABLE statement, and an INSERT statement to copy the data to the new table.

Back to Editor

Viewing Logs

The Logs screen is used for viewing logs and includes the following elements:

Element Description
Filter area Lets you filter the data shown in the table.
Query tab Shows basic query information logs, such as query number and the time the query was run.
Session tab Shows basic session information logs, such as session ID and user name.
System tab Shows all system logs.
Log lines tab Shows the total amount of log lines.

Filtering Table Data

From the Logs tab, from the FILTERS area you can also apply the TIMESPAN, ONLY ERRORS, and additional filters (Add). The Timespan filter lets you select a timespan. The Only Errors toggle button lets you show all queries, or only queries that generated errors. The Add button lets you add additional filters to the data shown in the table. The Filter button applies the selected filter(s).

Some filters require you to type text to define the filter.

../../_images/logs_filters_5.3.0.png

Other filters require you to select an item from a dropdown menu:

  • INFO
  • WARNING
  • ERROR
  • FATAL
  • SYSTEM

You can also export a record of all of your currently filtered logs in Excel format by clicking Download located above the Filter area.

Back to Viewing Logs

Viewing Query Logs

The QUERIES log area shows basic query information, such as query number and the time the query was run. The number next to the title indicates the amount of queries that have been run.

From the Queries area you can see and sort by the following:

  • Query ID
  • Start time
  • Query
  • Compilation duration
  • Execution duration
  • Total duration
  • Details (execution details, error details, successful query details)

In the Queries table, you can click on the Statement ID and Query items to set them as your filters. In the Details column you can also access additional details by clicking one of the Details options for a more detailed explanation of the query.

Back to Viewing Logs

Viewing Session Logs

The SESSIONS tab shows the sessions log table and is used for viewing activity that has occurred during your sessions. The number at the top indicates the amount of sessions that have occurred.

From here you can see and sort by the following:

  • Timestamp
  • Connection ID
  • Username
  • Client IP
  • Login (Success or Failed)
  • Duration (of session)
  • Configuration Changes

In the Sessions table, you can click on the Timestamp, Connection ID, and Username items to set them as your filters.

Back to Viewing Logs

Viewing System Logs

The SYSTEM tab shows the system log table and is used for viewing all system logs. The number at the top indicates the amount of sessions that have occurred. Because system logs occur less frequently than queries and sessions, you may need to increase the filter timespan for the table to display any system logs.

From here you can see and sort by the following:

  • Timestamp
  • Log type
  • Message

In the Systems table, you can click on the Timestamp and Log type items to set them as your filters. In the Message column, you can also click on an item to show more information about the message.

Back to Viewing Logs

Viewing All Log Lines

The LOG LINES tab is used for viewing the total amount of log lines in a table. From here users can view a more granular breakdown of log information collected by Studio. The other tabs (QUERIES, SESSIONS, and SYSTEM) show a filtered form of the raw log lines. For example, the QUERIES tab shows an aggregation of several log lines.

From here you can see and sort by the following:

  • Timestamp
  • Message level
  • Worker hostname
  • Worker port
  • Connection ID
  • Database name
  • User name
  • Statement ID

In the LOG LINES table, you can click on any of the items to set them as your filters.

Back to Viewing Logs

Back to Editor

Creating, Assigning, and Managing Roles and Permissions

Overview

In the Roles area you can create and assign roles and manage user permissions.

The Type column displays one of the following assigned role types:

Role Type Description
Groups Roles with no users.
Enabled users Users with log-in permissions and a password.
Disabled users Users with log-in permissions and with a disabled password. An admin may disable a user’s password permissions to temporary disable access to the system.

Note

If you disable a password, when you enable it you have to create a new one.

Viewing Information About a Role

Clicking a role in the roles table displays the following information:

  • Parent Roles - displays the parent roles of the selected role. Roles inherit all roles assigned to the parent.
  • Members - displays all members that the role has been assigned to. The arrow indicates the roles that the role has inherited. Hovering over a member displays the roles that the role is inherited from.
  • Permissions - displays the role’s permissions. The arrow indicates the permissions that the role has inherited. Hovering over a permission displays the roles that the permission is inherited from.

Creating a New Role

You can create a new role by clicking New Role.

../../_images/role_button.png

An admin creates a user by granting login permissions and a password to a role. Each role is defined by a set of permissions. An admin can also group several roles together to form a group to manage them simultaneously. For example, permissions can be granted to or revoked on a group level.

Clicking New Role lets you do the following:

  • Add and assign a role name (required)
  • Enable or disable log-in permissions for the role.
  • Set a password.
  • Assign or delete parent roles.
  • Add or delete permissions.
  • Grant the selected user with superuser permissions.

From the New Role panel you view directly and indirectly (or inherited) granted permissions. Disabled permissions have no connect permissions for the referenced database and are displayed in gray text. You can add or remove permissions from the Add permissions field. From the New Role panel you can also search and scroll through the permissions. In the Search field you can use the and operator to search for strings that fulfill multiple criteria.

When adding a new role, you must select the Enable login for this role and Has password check boxes.

Editing a Role

Once you’ve created a role, clicking the Edit Role button lets you do the following:

  • Edit the role name.
  • Enable or disable log-in permissions.
  • Set a password.
  • Assign or delete parent roles.
  • Assign a role administrator permissions.
  • Add or delete permissions.
  • Grant the selected user with superuser permissions.

From the Edit Role panel you view directly and indirectly (or inherited) granted permissions. Disabled permissions have no connect permissions for the referenced database and are displayed in gray text. You can add or remove permissions from the Add permissions field. From the Edit Role panel you can also search and scroll through the permissions. In the Search field you can use the and operator to search for strings that fulfill multiple criteria.

Deleting a Role

Clicking the delete icon displays a confirmation message with the amount of users and groups that will be impacted by deleting the role.