SQream Acceleration Studio¶
The studio is a web-based client for use with SQream DB.
It can be used to run statements, manage roles and permissions, and manage a SQream DB cluster.
In this topic:
Setting up and starting the studio¶
The studio is included with all dockerized installations of SQream DB.
When starting the studio, it listens on the local machine, on port 8080.
Logging in¶
Open a browser to the host, on port 8080. (e.g. If the machine is 196.168.0.100, navigate to http://192.168.0.100:8080) .
Fill in your login details for SQream DB. These are the same credentials you might use when using sqream sql or JDBC.
Your user level in SQream DB changes what you see.
Statement editor¶
Familiarizing yourself with the editor¶
The editor is built up of main panes.
Toolbar - used to select the active database you want to work on, limit the number of rows, save query, etc.
Statement area - The statement area is a multi-tab text editor where you write SQL statements. Each tab can connect to a different database.
Results - Results from a query will populate here. This is where you can copy or save query results, or show query execution details.
Database tree - contains a heirarchy tree of databases, views, tables, and columns. Can be used to navigate and perform some table operations.
See more about each pane below:
Navigation and user information¶
The user information menu is located on the bottom left portion of the screen 
.
The menu contains information about the currently signed-in user, as well as version information.
You can sign out of the current user at any point, by selecting Logout in the user menu.
Toolbar¶
In the toolbar, you can perform the folllowing operations (from left to right):
Database dropdown - Select the database you want to the statements to run on.
Queue - specify which service queue the statement should run in
⯈ Execute / STOP - Use the ⯈ EXECUTE button to execute the statement in the Editor pane. When a statement is running, the button changes to STOP, and can be used to stop the active statement.
Format SQL - Reformats and reindents the statement
Download query - save query text to your computer
Open query - load query text from your computer
Max. Rows - By default, the editor will only fetch the first 1000 rows. Click the number to edit. Click outside the number area to save. Setting a higher limit can slow down your browser if the result set is very large. This number is limited to 100000 results (To see more results, consider saving the results to a file or a table with CREATE TABLE AS).
Statement area¶
The multi-tabbed statement area is where you write queries and statements.
Select the database you wish to use in the toolbar, and then write and execute statements.
A new tab can be opened for each statement. Tabs can be used to separate statements to different databases. Clicking the 
will open a new tab with a default name of SQL + a running number.
Multiple statements can be written in the same tab, separated by semicolons (;).
If too many tabs are open, pagination controls will appear. Click 
or 
to scroll through the tab listings.
Rename a tab by double clicking it’s name.
Close a tab by clicking 
To close all tabs, click Close all, to the right of the tabs.
Tip
If this is your first time with SQream DB, see our first steps guide.
Formatting your SQL¶
The 
button can be used to automatically indent and reformat your SQL statements.
Saving statements¶
The 
saves the tab contents to your computer.
Loading SQL to a tab¶
The 
button loads a local file from your computer into a new editor tab.
Executing SQL statements¶
Clicking 
will execute statements from the active tab.
The button has three modes, which can be selected with the dropdown arrow ᐯ
Execute statements – executes the statements where the cursor is located.
Execute selected – executes the exact highlighted text. This mode is good for executing a subquery or other part of a large query (as long as it is a valid SQL).
Execute all – executes all statements in the active tab, regardless of any selection
When a statement is running, the button changes to STOP, and can be used to stop the active statement.
Results¶
The results pane shows query results and execution information. By default, only the first 10000 results are returned (modify via the Toolbar).
By default, executing several statements together will open a separate results tab for each statement.
Statements will be executed serially. Any failed statement will cancel subsequent statements.
If the 
switch is on, new statements will create new tabs. When off, existing result will be cleared.
If too many result tabs are open, pagination controls will appear. Click or to scroll through the tab listings.
Close a tab by clicking
To close all tabs, click Close all, to the right of the tabs.
Sorting results¶
After the results have appeared, sort them by clicking the column name.
Viewing execution information¶
During query execution the time elapsed is tracked in seconds.
The Show Execution Details button opens the query’s execution plan, for monitoring purposes.
Saving results to a file or clipboard¶
Query results can be saved to a clipboard (for pasting into another text editor) or a local file.
Database tree¶
The database tree shows the database objects (e.g. tables, columns, views), as well as some metadata like row counts.
It also contains a few predefined catalog queries for execution.
Each level contains a context menu relevant to that object, accessible via a right-click.
In this topic:
System Queries¶
The studio editor comes with several predefined catalog queries that are useful for analysis of table compression rates, users and permissions, etc.
Clicking on the System queries tab in the Tree section will show a list of pre-defined system queries.
Clicking on an item will paste the query into the editing area.
Filtering (searching) for objects¶
Clicking the 
filter icon by columns or tables opens an editable field that can be used for searching.
To remove the filter, click the icon again or select ❌.
Copying object names¶
Clicking the 
icon will copy the object name
Generating SELECT statements¶
Clicking the 
icon will generate a SELECT query for the selected table in the editing area.
Generating an INSERT statement¶
Clicking the Insert statement option under the ⋮ menu generates an INSERT statement for the selected table in the editing area.
Generating a DELETE statement¶
Clicking the Delete statement option under the ⋮ menu generates a DELETE statement for the selected table in the editing area.
Generating a CREATE TABLE AS statement¶
Clicking the Create table as option under the ⋮ menu generates a CREATE TABLE AS statement for the selected table in the editing area.
Renaming a table¶
Clicking the Rename table option under the ⋮ menu generates an ALTER TABLE statement for renaming the selected table in the editing area.
Adding columns to table¶
Clicking the Add column option under the ⋮ menu generates an ALTER TABLE statement for adding columns to the selected table in the editing area.
Truncate a table¶
Clicking the Truncate table option under the ⋮ menu generates a TRUNCATE_IF_EXISTS statement for the selected table in the editing area.
Dropping an object¶
Clicking the Drop table, Drop view, or Drop function option under the ⋮ menu generates a DROP statement for the selected object in the editing area.
Generating DDL statements¶
Clicking the Table DDL, View DDL, or Function function option under the ⋮ menu 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.
The DDL optimizer¶
The DDL optimizer tab analyzes database tables and recommends possible optimizations, per the Optimization and best practices guide.
Using the DDL optimizer¶
Navigate to the DDL optimizer module by selecting DDL Optimizer from the ⋮ (“More”) menu.
Rows- number of rows to scan for analysis. Defaults to 1,000,000Buffer Size- overhead threshold to use when analyzingVARCHARfields. Defaults to 10%.Optimize NULLs- attempt to figure out field nullability.
Click Run Optimizer to start the optimization process.
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.
Administration Dashboard¶
If you signed in with a SUPERUSER role, you can enter the administration dashboard.
Enter the administration dashboard by clicking the 
icon in the navigation bar.
Familiarizing yourself with the dashboard¶
The main dashboard screen contains two main panes:
Data storage pane - monitor the SQream DB cluster’s storage
can be expanded to drill down into database storage
Worker pane - monitor system health
the worker pane used to monitor workers and service queues in the cluster.
Data storage pane¶
The left section of the Admin Dashboard shows you the status of your system’s storage as a donut.
Storage is displayed broken up into four components:
Data – Storage occupied by databases in SQream DB
Free – Free storage space
Deleted – Storage that is temporarily occupied but hasn’t been reclaimed (see our delete guide to understand how data deletion works).
(This value is estimated and may not be accurate)
Other – Storage used by other applications. On a dedicated SQream DB cluster, this should be close to zero.
Database storage¶
Expanding the storage pane (
) will show a breakdown of how much storage is used by each database in the cluster.
This can be used to drill down into each database’s storage footprint.
Databases are displayed in a table, containing the following information: * Database name * Raw storage size – the estimated size of raw data (uncompressed) in the database * Storage size – the physical size of the compressed data * Ratio – effective compression ratio * Deleted data – storage that is temporarily occupied but hasn’t been reclaimed (see our delete guide to understand how data deletion works). (This value is estimated and may not be accurate)
Below the table, a graph shows the database storage trends.
By default, the graph shows the total storage for all databases. Clicking a database in the table will filter to show just that database.
The scale of the presented information can be controlled by changing the timeframe in the scale dropdown (
).
Service and workers pane¶
This pane shows the cluster status in workers and their service queues.
Services¶
The services bar shows the defined service queues.
Services are used to divide workers and associate (subscribe) workers to services.
Each service queue contains the following details: * Service name * A graph of load over time (statements in that queue) * Currently processed queries of the Service / total queries for that service in the system (including queued queries)
Creating new service queues¶
Click the 
button above the service list. Type the service queue name and associate new workers to the service queue.
Note
if you choose not to associate a worker with the new service, it will not be created.
Associating a worker with an existing service¶
Clicking on the 
icon on a service name is used to attach workers to a service.
Clicking on a service queue in the services bar will display the list of workers in the main pane.
In this mode, the ⋮ icon (more menu) can be used to detach a worker from a service.
You can select a Worker from the list that is available to process queries of the relevant Service and by clicking on the button of that Worker that Worker will be associated with the Service. After that the page will go back to its normal layout and you will be able to click the Service and see the Worker associated with the Service. Other Services associated with that Worker will remain associated to it.
Workers¶
The worker pane shows each worker (sqreamd) running in the cluster.
Each worker has a status bar that represents the status over time. The scale of the presented information can be controlled by changing the timeframe in the scale dropdown ().
The status bar is divided into 20 equal sections, showing the most dominant activity in that slice. Hover over the status bar sections to see the activity:
Idle – worker is idle and available for statements
Compiling – Compiling a statement, in preparation for execution
Executing – executing a statement after compilation
Stopped – worker was stopped (either deliberately or due to a fault)
Waiting – worker was waiting on an object locked by another worker
Show host resources¶
Clicking the 
button below each host will expand to show the host resource utilization.
The host resource utilization includes information about:
CPU utilization
Memory utilization
GPU utilization
Graphs show resource utilization over time. Current values are shown on the right.
Hover over the graph line to see the activity at a given time.
Active queries¶
Clicking the button on a worker will expand to show the active statements running.
Each statement has a statement ID, status, service queue, elapsed time, execution time, and estimated completion status.
Each statement can be stopped or expanded to show its execution plan and progress (SHOW_NODE_INFO).
Status |
Description |
|---|---|
|
Statement is being prepared |
|
Statement is waiting for execution |
|
Statement has entered execution checks |
|
Statement is executing |
|
Statement is in the process of stopping |
Control worker status (start, stop, restart)¶
In some cases, it may be useful to stop or restart workers for maintenance.
Each Worker line has a ⋮ menu (more menu). This menu allows stopping, starting, or restarting workers.
When a worker is stopped, it has a gray background and its status is “Stopped”.