SQream Acceleration Studio 5.3.2

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:

• SQream Acceleration Studio 5.3.2
  • Getting Started
    • Setting Up and Starting Studio
    • Logging In to Studio
    • Using Studio
  • Dashboard
    • Data Storage Panel
    • Services Panel
    • Workers
    • License Information
  • Editor
    • Toolbar
    • Database Tree and System Queries Panel
    • Statement Pane
    • Results Panel
    • Analyzing the Results
  • Logs
    • Filter Area
    • Queries
    • Sessions
    • System
    • Log Lines

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.

You can start Studio using sqream-console.

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.

Using Studio

When you log in, you are automatically taken to the Editor screen.

The Studio’s main functions are displayed in the Navigation pane on the left side of the screen.

From here you can navigate between the main areas of the Studio:

Element	Description
Dashboard	Only users with superuser permissions have access to the Dashboard.
Editor	All users have access to the Editor and to databases that they have permissions for.
Logs	Only users with the superuser permissions have access to the logs.

By clicking the user icon , you can also use it for logging out and viewing the following:

• User information
• Connection type
• SQream version
• SQream Studio version
• Data size limitations
• Log out

Dashboard

If you signed in with a SUPERUSER role, you can access the Dashboard.

The Dashboard includes the panes shown below:

The following is a brief description of each pane:

No.	Element	Description
1	Data Storage panel	Used to monitor cluster storage.
2	Services panel	Used for viewing and monitoring the defined service queues.
3	Workers panel	Monitors system health and shows each Sqreamd worker running in the cluster.
4	License information	Shows the remaining amount of days left on your license.

Data Storage Panel

The Data Storage area displays your system’s total disk usage (percentage) and data storage (donut graph).

Your data storage is shows as the following four components:

• Data – Storage occupied by databases in SQream DB.
• Free – Free storage space.
• Deleted – Storage that is temporarily occupied, but has not been reclaimed. For more information, see Deleting Data. The deleted value is estimated and may not be accurate.
• Other – Storage used by other applications. On a dedicated SQream DB cluster this should be near zero.

Clicking the expand arrow on the Data Storage panel expands it to show more information.

The expanded Data Storage panel can be used to drill down into each database’s storage footprint, and displays a breakdown of how much storage each database in the cluster uses.

The database information is displayed in a table and shows the following:

• Database name - the name of the database.
• Raw Size – the estimated size of (uncompressed) raw data in the database.
• Storage Size – the physical size of the compressed data.
• Ratio – the effective compression ratio
• Deleted Data – Storage that is temporarily occupied, but has not been reclaimed.

Below the table, an interactive line graph displays the database storage trends. By default, the line graph displays the total storage for all databases. You can show a particular database’s graph by clicking it in the table.

You can also change the line graph’s timespan by selecting one from the timespan dropdown menu.

Back to Dashboard

Services Panel

Services are used to categorize and associate (also known as subscribing) workers to particular services. The Service panel is used for viewing, monitoring, and adding defined service queues.

The Dashboard includes the four panes shown below:

The following is a brief description of each pane:

No.	Description
1	Adds a worker to the selected service.
2	Shows the service name.
3	Shows a trend graph of queued statements loaded over time.
4	Adds a service.
5	Shows the currently processed queries belonging to the service/total queries for that service in the system (including queued queries).

Adding A Service

You can add a service by clicking Add and defining the service name.

Note

If you do not associate a worker with the new service, it will not be created.

You can manage workers from the Workers panel. For more information on managing workers, see Workers.

Back to Dashboard

Workers

The Workers panel section describes the following:

• Using the Workers panel
• Adding a worker to a service
• Viewing a worker’s active query information
• Viewing a worker’s execution plan

Using the Worker’s Panel

The Worker panel shows each worker (sqreamd) running in the cluster. Each worker has a status bar that represents the status over time. The status bar is divided into 20 equal segments, showing the most dominant activity in that segment.

You can set the time scale of the displayed information by selecting a timeframe in the time scale dropdown menu.

You can hover over segments in the status bar to see the date and time corresponding to each activity type:

• Idle – the worker is idle and available for statements.
• Compiling – the worker is compiling a statement and is preparing for execution.
• Executing – the worker is executing a statement after compilation.
• Stopped – the worker was stopped (either deliberately or due to an error).
• Waiting – the worker was waiting on an object locked by another worker.

Adding A Worker to A Service

You can add a worker to a service by clicking the add button.

Clicking the add button shows the selected service’s workers. You can add the selected worker to the service by clicking Add Worker. Adding a worker to a service does not break associations already made between that worker and other services.

Viewing A Worker’s Active Query Information

You can view a worker’s active query information by clicking Queries, which displays them in the selected service.

Each statement shows the query ID, status, service queue, elapsed time, execution time, and estimated completion status. In addition, each statement can be stopped or expanded to show its execution plan and progress. For more information on viewing a statement’s execution plan and progress, see Viewing a Worker’s Execution Plan below.

Viewing A Worker’s Host Utilization

While viewing a worker’s query information, clicking the down arrow expands to show the host resource utilization.

The graphs show the resource utilization trends over time, and the CPU memory and utilization and the GPU utilization values on the right.

You can hover over the graph to see more information about the activity at any point on the graph.

Error notifications related to statements are displayed as shown in the figure below, and you can hover over them for more information about the error.

Viewing a Worker’s Execution Plan

Clicking the ellipsis in a service shows the following additional options:

Clicking Stop Query stops the query. Clicking Show Execution Plan shows the execution plan in a table. The columns in the Show Execution Plan table can be sorted.

For more information on the current query plan, see SHOW_NODE_INFO.

For more information on checking active sessions across the cluster, see SHOW_SERVER_STATUS.

Statement status values

Status	Description
Preparing	Statement is being prepared
In queue	Statement is waiting for execution
Initializing	Statement has entered execution checks
Executing	Statement is executing
Stopping	Statement is in the process of stopping

Managing Worker Status

In some cases you may want to stop or restart workers for maintenance purposes. Each Worker line has a ⋮ menu used for stopping, starting, or restarting workers.

Starting or restarting workers terminates all queries related to that worker.

When you stop a worker, its background turns gray.

Back to Dashboard

License Information

The license information is a counter showing the amount of time in days remaining on the license.

Back to Dashboard

Editor

The Editor’s main screen includes the four panes shown below:

The following is a brief description of each pane:

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.

Toolbar

The following figure shows the Toolbar pane:

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

Database Tree and System Queries Panel

The following figure shows the Database Tree and System Queries panel, with the Database Tree tab selected.

The database tree contains two tabs showing the following information:

• Database Tree tab - shows the database object hierarchy (such as tables, views, functions, and columns), lets you perform statement-related operations, and shows metadata (such as a number indicating the amount of rows in the table).
• System Queries tab - contains predefined catalog queries for execution.

Database Tree

The following figure shows the TABLES object data opened, and the database object functions in the calcs table object:

The database object functions are used to perform the following:

• To generate a SELECT statement in the Statement panel .
• To copy table names into the Statement panel .
• To perform additional operations .

Generating a SELECT Statement in Statement Panel

Clicking copies the selected table’s columns into the Statement panel as SELECT parameters:

Copying Tables into Statement Panel

Clicking copies the selected table’s name into the Statement panel:

Using Additional Object Options

Clicking displays the following additional options:

The following table describes the additional options:

Function	Description
Insert statement	Generates an INSERT statement for the selected table in the editing area.
Delete statement	Generates a DELETE statement for the selected table in the editing area.
Create Table As statement	Generates a CREATE TABLE AS statement for the selected table in the editing area.
Rename statement	Generates an RENAME TABLE AS statement for renaming the selected table in the editing area.
Adding column statement	Generates an ADD COLUMN statement for adding columns to the selected table in the editing area.
Truncate table statement	Generates a TRUNCATE_IF_EXISTS statement for the selected table in the editing area.
Drop table statement	Generates a DROP statement for the selected object in the editing area.
Table DDL	Generates a DDL statement for the selected object in the editing area. To get the entire database DDL, click the icon next to the database name in the tree root. See also Seeing System Objects as DDL.
DDL Optimizer	The DDL Optimizer lets you analyze database tables and recommends possible optimizations. For more information, see the Optimization and Best Practices guide.

Using the DDL Optimizer

As described in the previous table, you can access the DDL Optimizer by clicking the additional options icon and selecting DDL Optimizer.

The following image shows the DDL Optimizer screen:

The following table describes the DDL Optimizer screen:

No.	Element	Description
1	Column area	Shows the column names and column types from the selected table. You can scroll down or to the right/left for long column lists.
2	Optimization area	Shows the following: The number of rows to sample as the basis for running an optimization. The default setting when running an optimization (1,000,000). This is also the overhead threshold used when analyzing VARCHAR fields. The default percent buffer to add to VARCHAR lengths (10%). Attempts to determine field nullability.
3	Run Optimizer	Starts the optimization process.

Clicking Run Optimizer adds a tab to the Statement panel showing the optimized results of the selected object. The figure below shows the calcs Optimized tab for the optimized calcs table.

Back to top

System Queries

The Editor includes the following system query types:

• Catalog queries - used for analyzing table compression rates, users and permissions, etc.
• Admin queries - queries related to available queries useful for SQream database management.

Clicking an item pastes the query into the Statement pane.

You can undo your previous operation by pressing Ctrl + Z.

Back to Editor

Statement Pane

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. 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 , 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.

You can also rename the default tab name by double-clicking it and typing a new name.

You can 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 or , and close tabs by clicking . 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 with SQream DB, see First steps with SQream DB.

Back to Editor

Results Panel

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

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 figure shows the main functions available from the Results panel:

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

No.	Element	Description
1	Results view	Lets you view search query results. For more information on running parallel statements, see Running Parallel Statements
2	Execution Details view	Lets you view execution details, such as statement ID, number of rows, and averge number of rows in chunk.
3	SQL view	Lets you see the SQL view.
4	Save results to clipboard	Lets you save your search results to the clipboard to paste into another text editor.
5	Save results to local file	Lets you save your search query results to a local file.

Results View

The following figure shows the Results view:

The following is a brief description of the Results view:

No.	Element
1	Lets you switch between tabs.
2	Lets you close all tabs. You can also close tabs by clicking .
3	Lets you scroll between tabs if too many result tabs are open to fit in the panel. Click or to scroll through the tab listings.
4	Lets you keep each new statement that you execute in a separate tab. When Keep tabs is disabled, each new statement you execute clears all existing results.
5	Lets you sort column results.
6	Lets you view the amount of time (in seconds) taken for a query to finish executing.

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:

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

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:

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.

The following figure shows the contents of the Results panel downloaded to an Excel sheet:

Analyzing the Results

When results are produced, a Generate CREATE statement button will appear. Clicking the 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

Logs

The following figure shows the Logs screen.

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

No.	Element	Description
1	Filter	Lets you filter the data shown in the table.
2	Queries	Shows basic query information, such as query number and the time the query was run.
3	Sessions	Shows basic session information, such as session ID and user name.
4	System	Shows all system logs.
5	Log lines	Shows the total amount of log lines.

Filter Area

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.

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

You can also export a record of all of your currently filtered logs in Excel format by clicking Download.

Back to Logs

Queries

Clicking Queries displays the query data table and is used for viewing and keeping track of queries that you have run. The number at the top indicates the amount of queries that have been run.

From here 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 Query table, you can click on the Statement ID and Query items to set them as your filters.

The following figure shows the Details options:

Clicking one of the details options shows a more detailed explanation of the query, such as the error example below:

Back to Logs

Sessions

Clicking Sessions displays the sessions 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 Logs

System

Clicking System displays the system 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. You can also click on an item in the Message column to show more information about the message.

Back to Logs

Log Lines

Clicking LOG LINES 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 Logs

Back to Editor