Overview

The Visual Query Builder is a user-friendly visualization tool that can help you to create queries to the database and see results. You do not need to know SQL language to work in it. The Visual Query Builder may be useful for:

• building queries;
• complex queries analysis;
• easy query editing.

To open the Visual Query Builder, click the Query Builder tab in the SQL Editor right toolbar.

1. Select tables in the Navigator tree and drag-and-drop them into the Visual Query Builder area. The existing connections between the tables will automatically be displayed. The tables will also be added to the SQL expression which can be found in the field to the right of the diagram.

2. To create a new join between tables, connect their columns holding the left mouse button. The connection between the selected columns of the tables will appear in the diagram and the Inner Join will be added to the SQL script.

3. You can change a join type clicking the join label on the connection line.
4. To remove a join between tables, click on the line, then press the Delete button. The connection will be removed from the diagram and the join will disappear from the SQL script.
5. By default all tables’ columns are included in the query. If you only want to see certain columns in your query result, select the checkbox near the column name.

6. WHERE condition with the filter value is used for filtering. To add a filter, write it in the top filter field.

Filter example:

7. To apply a sorting condition to a column, press the sorting icon next to a column name on the diagram. The column will be sorted in ascending order and the conditional expression ORDER BY will be added to the SQL script. To sort the column in descending order, press the sorting icon again to select the down arrow. If you want to remove a condition, continue to click the sorting icon to deactivate it. Sorting can be applied to multiple columns in different tables. First, apply sorting on the first column you wish to sort, and then on the second, third and so on. You can sort numbers, texts, dates, time and other values.

Use the Execute SQL statement button on the left pane to execute a query and get the results in the same tab. If you want to see the result in a new tab, press the Execute SQL statement in a new tab button .

You can use the same shortcuts as in the SQL Editor to execute the Visual Query.

The Visual Query Builder uses the following visual tools to display queries on the diagram:

Symbol	Description
	Table Primary Key is bold and displayed at the top of the table.
	Table Alias is used to shorten your Join Statement.
	Colored table header marks the first table in your Join Statement.
	Colorless header marks a joined table in your Join Statement.
	Line goes from the joined table to the first table.

Available Join types are described in the table below. The Visual Query Builder can show results only for those types of Joins that are supported by your database.

Symbol	Description
	Inner Join
	Left Join
	Left Outer Join
	Right Join
	Right Outer Join
	Full Join
	Full Outer Join
	Cross Join

You can customize the diagram view using the bottom toolbar to make the work with the diagram easier.

• Layout updates the diagram view to display all of its objects in the most optimal way.

• Zoom in and Zoom out enlarges or shrinks the diagram view.

• Settings menu contains additional settings of the Visual Query Builder. Press the Settings button at the bottom toolbar to open it.

  • Layout on update enables Auto-layout feature. As soon as you add a new object to the diagram, the diagram view will automatically be updated to display all of its objects in the most optimal way.
  • Show join type on entities moves Join labels from lines into headers of joined tables.
  • Show Type adds information about column types into entities.
  • Show Icons adds icons of column types into entities.
  • Notation changes the representation of connection lines. Simple notation is set by default. You can change it to the IDEF1X language type.

If you write a JOIN statement by yourself and then want to convert it to the diagram view, just switch the SQL Editor with your statement to the Visual Query Builder.

Note: the Visual Query Builder can transform the syntax of your query, but it does not affect the query result in the Result set.

Team Edition supports the visualization and management of spatial data. Spatial data, which includes geographic locations and geometric shapes.

Spatial data, often represented as either geometric or geographical values, can be visualized on a map or graph. A geometric object is typically composed of a sequence of points that define its shape. For a more comprehensive understanding of spatial data, you can refer to this detailed explanation.

Team Edition’s support of spatial data covers the following databases:

• PostgreSQL (PostGIS)
• Greenplum
• CockroachDB
• MySQL
• MariaDB
• SQLite (GeoPackage)
• H2GIS
• SAP HANA
• DuckDB
• Redshift
• Exasol
• Altibase
• BigQuery
• Oracle
• SQL Server
• Snowflake

• AlloyDB

To access the spatial data viewer, select one or more rows containing GIS data from your table. After selecting the rows, open the Value Panel to display the selected spatial data on a map. For more information, see Value Panel.

• Zooming: Allows you to zoom in and out to tailor the map view, using either the mouse wheel or the on-screen icons.
• Map display options: You can choose between street or topography map view.
• Data interaction: When you click on a spatial object on the map, Team Edition displays associated information from every other column in the corresponding row.
• Coordinate Reference System (CRS) selection: A dropdown menu in the viewer allows you to select the appropriate CRS for your data. For more information about CRS, see Spatial reference system.

• Apache Kyuubi
• ClickHouse
• Db2 iSeries/AS 400 for IBM i
• Db2 LUW
• DuckDB
• Firebird
• H2 Embedded
• MariaDB
• MySQL
• Oracle
• PostgreSQL
• SQL Server
• SQLite
• Trino
• Altibase
• Apache Arrow
• Apache Calcite Avatica
• Apache Drill
• Apache Hive
• Apache Ignite
• Athena
• Azure Databricks
• Azure SQL Server
• Babelfish
• Cache
• Cassandra
• CloudSQL – MySQL
• CloudSQL – PostgreSQL
• CloudSQL – SQL Server
• CockroachDB
• CosmosDB (Cassandra)
• CosmosDB (MongoDB)
• Couchbase
• Couchbase 5+
• CouchDB
• CrateDB
• CUBRID
• Dameng
• Db2 for z/OS
• DocumentDB
• Dremio
• DynamoDB
• EDB
• Elasticsearch
• Exasol
• Firestore
• Fujitsu
• Google AlloyDB
• Google BigQuery
• Google Cloud Bigtable
• Google Cloud Spanner
• Greenplum
• HANA
• HSQL Sever
• Huawei GaussDB
• InfluxDB
• InfluxDB 2
• InfluxDB 3
• Informix
• Ingres
• InterSystems IRIS
• Kafka (ksqlDB)
• Keyspaces
• Materialize
• MaxDB
• MongoDB
• NDB Cluster
• Neo4j
• Neptune
• Netezza
• NetSuite
• OceanBase
• OpenSearch
• Raima
• Redis
• Redshift
• RisingWave
• Salesforce
• Salesforce CDP
• SAP ASE jConnect
• ScyllaDB
• SingleStore
• Snowflake
• StarRocks
• Sybase
• TDengine
• TDengine Cloud
• Teradata
• TiDB
• TimescaleDB
• Timestream
• Vertica
• Yellowbrick
• Yugabyte CQL
• YugabyteDB

Note: you can add other drivers manually using the instruction.

It is possible to configure Team Edition to save database credentials (user names and passwords) in Team Edition storage.
In this case, users won’t need to enter database credentials every time they connect to a database.

However, the most secure way is to disable this option. See options “Save credentials” and “Save user credentials” in administrator console, page “Server configuration”.

There are two types of database connections: global and user.
Global connections are managed by Team Edition administrators, user connections are managed by users themselves.

Global database configuration is stored in workspace sub-folder

User configuration are stored in workspace sub-folders

Potentially, if an intruder/malware software will get access to Team Edition server filesystem, then it may get access to all stored user credentials. To increase security it is recommended to configure the server to keep workspace on a shared encrypted network folder (e.g. S3, see S3 Server-side encryption.

• How to configure S3 encrypted file system on Ubuntu

This article gives you an overview of how to create user in Team Edition.

There are two types of users:

• Local users: Created by the Administrator.
• AWS and Federated users: These users are managed externally through AWS or federated identity providers and are authorized to access the system via Single Sign-On (SSO).

For more information on Identity providers, see Authentication methods.

To create a new local user, follow these steps:

1. As an administrator, go to Settings -> Administration -> Users and Teams -> Teams.
2. Click on the + Create button.

3. Fill in the necessary details in the provided fields.

   

   Field Name	Description	Additional Info
   Username	Enter the desired username for the account.
   User password	Set a password for the account.	The user can change their password after initial setup.
   Repeat password	Re-enter the password for verification.
   Role	Select the user’s role from the dropdown.	For more information about roles, see Roles in Team Edition.
   User Status	Toggle to enable or disable the user.	Default status is Enabled.
   User Team	(Optional) Assign the user to one or more teams.	A team defines the permissions a user has within the system. For more information on teams, see Teams.
   First Name	(Optional) Provide the user’s first name.
   Last Name	(Optional) Provide the user’s last name.
   AWS Role ARN	(Optional) Enter the AWS Role ARN.	For more information about AWS roles, see AWS Settings.
   Microsoft Entra ID User ID	(Optional) Enter the Microsoft Entra ID.	For more information, see Microsoft Entra ID authentication.

4. To complete the process, click on the Create button.

   Once created, the user can be authenticated using local authentication methods. The user’s permissions will be determined by their assigned profile.

If necessary, you can provide the user with connection access. This setting can be found and adjusted within the Connection Access tab.

See the additional information on Connection management.

Remember, user management is an important aspect of maintaining system security. Always ensure that users are granted only the access and permissions necessary for their tasks.

If necessary, you can provide the user with access to specific projects. This setting can be adjusted within the Project tab.

For additional information on managing projects, see Project Management.

Remember, user management is an important aspect of maintaining system security. Always ensure that users are granted only the access and permissions necessary for their tasks.

To allow a local user to authenticate through AWS/Federated auth methods, the local user’s username must match the user’s email address, which will connect via SSO (Single Sign-On). This alignment is necessary for the federated authentication process to succeed.

Note: This step is crucial during user creation as the username cannot be changed later.

When a user logs in using AWS or Federated authentication for the first time, Team Edition automatically creates a user profile assigned to the default team. Administrators can later change this team assignment as necessary.

Note: Administrators cannot create AWS or Federated users directly in the application. Team Edition only works with existing AWS and Federated users. For more information on Identity providers, see Authentication methods.

The process of editing a user is similar to creating one, except you need to access an existing user.

When editing an existing user, you also have additional options:

In the Auth Methods tab, administrators can see and remove the authentication methods associated with a user.

You can remove an existing authentication method:

1. Select the desired method from the dropdown menu.
2. Click the DELETE button.

Tip: If you need to restore local authentication for the user, navigate to the Info tab and assign a new password to the user. This action will re-enable local authentication.

If you need to permanently remove a user from the system, you can do so through the Delete user option. When you attempt to delete a user, a confirmation dialog will appear to ensure that this action is intentional.

1. To delete a user, select the Delete option.

2. Follow the prompts in the dialog to confirm the deletion.

   

   Tip: If you prefer to keep the user but prevent their access, consider using the Disable option in the dialog. Alternatively, you can disable a user by selecting the checkbox in the Info section of the user profile.

In the Team Edition desktop, the functionality remains similar but the user interface (UI) differs. To access the user management section navigate as an administrator to Window -> Show View -> Administration -> Users.

In desktop interface, administrator have access to several management options:

Tip: Administrators have the ability to assign users to Teams and Projects.

• Teams: Administrators can add or remove users from teams to manage group-based permissions. For more information about team management, see Teams.
• Projects: Administrators can include users in different projects, which allows for controlled access to various resources. For more details on project management, see Projects.

The Value Panel provides additional space in the Data editor in which you can manipulate data. The panel is handy if you work with complex types (structures, arrays), long text data or BLOBs.

To open the panel, click the Value button on the right hand side of the Data tab. Alternatively, you can open the Value panel by clicking Show the in value panel in the cell context menu.

To close the panel, click the Value button again.

The Value Panel displays just one value that is currently selected or in focus and allows editing.

At the top of the Value Panel, you can find several tabs. The tabs depend on the current value type. For example, if your current value is a string, you will find 4 tabs (Plain text, HTML, XML, JSON), each representing a format the string can be shown in.

There are 3 available tabs for BLOB type of data:

• Text
• HEX
• Base64

This article provides a guide to configuring Team Edition, using the main server configuration file (

Note: Some configuration values support variables that can be set using command line arguments, optional settings when running Docker Desktop, or defined within the Docker Compose file.

The main configuration file is

The server configuration uses a

You can also set configuration options in another file:

Tip: If you’re using Docker, the

.cloudbeaver.runtime.conf

file can be especially useful for customizing configurations across different containers easily.

Note: All paths can be absolute or relative to the server start directory (or current directory).

Example configuration:

{

    server: {

        serverName: "CloudBeaver CE Server",

        serverURL: "http://localhost:8090",

        serverPort: "${CLOUDBEAVER_SERVICE_PORT:8978}",

        workspaceLocation: "${CLOUDBEAVER_WORKSPACE_LOCATION:workspace}",

        contentRoot: "web",

        driversLocation: "drivers",

        sslConfigurationPath:"${CLOUDBEAVER_SSL_CONF_PATH:workspace/.data/ssl-config.xml}",

        rootURI: "${CLOUDBEAVER_ROOT_URI:/}",

        serviceURI: "/api/",

        expireSessionAfterPeriod: "${CLOUDBEAVER_EXPIRE_SESSION_AFTER_PERIOD:1800000}",

        develMode: "${CLOUDBEAVER_DEVEL_MODE:true}",

        productSettings: {

            "core.localization.localization": "en",

            "core.theming.theme": "light",

            "plugin.log-viewer.disabled": false,

            "plugin.log-viewer.logBatchSize": 1000.0,

            "plugin.log-viewer.maxFailedRequests": 3.0,

            "plugin.log-viewer.maxLogRecords": 2000.0,

            "plugin.log-viewer.refreshTimeout": 3000.0,

            "plugin.sql-editor.autoSave": true,

            "plugin.sql-editor.disabled": false,

            "plugin.sql-editor.maxFileSize": 10240.0,

            "sql.proposals.insert.table.alias": "PLAIN"

        },

        database: {

            driver: "${CLOUDBEAVER_DB_DRIVER:h2_embedded_v2}",

            url: "${CLOUDBEAVER_DB_URL:jdbc:h2:${workspace}/.data/cb.h2v2.dat}",

            schema: "${CLOUDBEAVER_DB_SCHEMA:''}",

            user: "${CLOUDBEAVER_DB_USER:''}",

            password: "${CLOUDBEAVER_DB_PASSWORD:''}",

            initialDataConfiguration: "${CLOUDBEAVER_DB_INITIAL_DATA:conf/initial-data.conf}",

            pool: {

                minIdleConnections: "${CLOUDBEAVER_DB_MIN_IDLE_CONNECTIONS:4}",

                maxIdleConnections: "${CLOUDBEAVER_DB_MAX_IDLE_CONNECTIONS:10}",

                maxConnections: "${CLOUDBEAVER_DB_MAX_CONNECTIONS:100}",

                validationQuery: "${CLOUDBEAVER_DB_VALIDATION_QUERY:SELECT 1}"

            },

            backupEnabled: "${CLOUDBEAVER_DB_BACKUP_ENABLED:true}"

        },

        sm: {

            enableBruteForceProtection: "${CLOUDBEAVER_BRUTE_FORCE_PROTECTION_ENABLED:true}",

            expiredAuthAttemptInfoTtl: "${CLOUDBEAVER_EXPIRED_AUTH_ATTEMPT_INFO_TTL:60}",

            maxFailedLogin: "${CLOUDBEAVER_MAX_FAILED_LOGINS:10}",

            minimumLoginTimeout: "${CLOUDBEAVER_MINIMUM_LOGIN_TIMEOUT:1}",

            blockLoginPeriod: "${CLOUDBEAVER_BLOCK_PERIOD:300}",

            passwordPolicy: {

                minLength: "${CLOUDBEAVER_POLICY_MIN_LENGTH:8}",

                requireMixedCase: "${CLOUDBEAVER_POLICY_REQUIRE_MIXED_CASE:true}",

                minNumberCount: "${CLOUDBEAVER_POLICY_MIN_NUMBER_COUNT:1}",

                minSymbolCount: "${CLOUDBEAVER_POLICY_MIN_SYMBOL_COUNT:0}"

            }

        }

    },

    qm: {

        driver: "${CLOUDBEAVER_QM_DB_DRIVER:h2_embedded_v2}",

        url: "${CLOUDBEAVER_QM_DB_URL:jdbc:h2:${workspace}/.metadata/qmdb/qmdb}",

        user: "${CLOUDBEAVER_QM_DB_USER:''}",

        password: "${CLOUDBEAVER_QM_DB_PASSWORD:''}",

        schema: "${CLOUDBEAVER_QM_DB_SCHEMA:''}",

        pool: {

            minIdleConnections: "${CLOUDBEAVER_QM_DB_MIN_IDLE_CONNECTIONS:4}",

            maxIdleConnections: "${CLOUDBEAVER_QM_DB_MAX_IDLE_CONNECTIONS:10}",

            maxConnections: "${CLOUDBEAVER_QM_DB_MAX_CONNECTIONS:100}",

            validationQuery: "${CLOUDBEAVER_QM_DB_VALIDATION_QUERY:SELECT 1}"

        },

        backupEnabled: "${CLOUDBEAVER_QM_DB_BACKUP_ENABLED:true}"

    },

    app: {

        anonymousAccessEnabled: "${CLOUDBEAVER_APP_ANONYMOUS_ACCESS_ENABLED:true}",

        supportsCustomConnections: "${CLOUDBEAVER_APP_SUPPORTS_CUSTOM_CONNECTIONS:false}",

        publicCredentialsSaveEnabled: "${CLOUDBEAVER_APP_PUBLIC_CREDENTIALS_SAVE_ENABLED:true}",

        adminCredentialsSaveEnabled: "${CLOUDBEAVER_APP_ADMIN_CREDENTIALS_SAVE_ENABLED:true}",

        enableReverseProxyAuth: false,

        forwardProxy: "${CLOUDBEAVER_APP_FORWARD_PROXY:false}",

        linkExternalCredentialsWithUser: true,

        redirectOnFederatedAuth: false,

        resourceManagerEnabled: "${CLOUDBEAVER_APP_RESOURCE_MANAGER_ENABLED:false}",

        showReadOnlyConnectionInfo: "${CLOUDBEAVER_APP_READ_ONLY_CONNECTION_INFO:false}",

        grantConnectionsAccessToAnonymousTeam: "${CLOUDBEAVER_APP_GRANT_CONNECTIONS_ACCESS_TO_ANONYMOUS_TEAM:false}",

        systemVariablesResolvingEnabled: "${CLOUDBEAVER_SYSTEM_VARIABLES_RESOLVING_ENABLED:false}",

        defaultUserTeam: "${CLOUDBEAVER_APP_DEFAULT_USER_TEAM:user}",

        resourceQuotas: {

            dataExportFileSizeLimit: "${CLOUDBEAVER_RESOURCE_QUOTA_DATA_EXPORT_FILE_SIZE_LIMIT:10000000}",

            resourceManagerFileSizeLimit: "${CLOUDBEAVER_RESOURCE_QUOTA_RESOURCE_MANAGER_FILE_SIZE_LIMIT:500000}",

            sqlMaxRunningQueries: "${CLOUDBEAVER_RESOURCE_QUOTA_SQL_MAX_RUNNING_QUERIES:100}",

            sqlResultSetRowsLimit: "${CLOUDBEAVER_RESOURCE_QUOTA_SQL_RESULT_SET_ROWS_LIMIT:100000}",

            sqlResultSetMemoryLimit: "${CLOUDBEAVER_RESOURCE_QUOTA_SQL_RESULT_SET_MEMORY_LIMIT:2000000}",

            sqlTextPreviewMaxLength: "${CLOUDBEAVER_RESOURCE_QUOTA_SQL_TEXT_PREVIEW_MAX_LENGTH:4096}",

            sqlBinaryPreviewMaxLength: "${CLOUDBEAVER_RESOURCE_QUOTA_SQL_BINARY_PREVIEW_MAX_LENGTH:261120}"

        },

        defaultNavigatorSettings: {

            showOnlyEntities: true,

            hideFolders: true,

            hideVirtualModel: true

        },

        enabledFeatures: [],

        enabledAuthProviders: [

            "local"

        ],

        enabledDrivers: [],

        disabledDrivers: [

            "h2:h2_embedded",

            "h2:h2_embedded_v2",

            "clickhouse:yandex_clickhouse",

            "generic:duckdb_jdbc",

            "sqlite:sqlite_jdbc"

        ]

    }

}

Configure main server settings directly in the file or using environment variables (when available).

Configures the database for storing users, credentials, and permissions in

Configure the connection pool used by the Team Edition database in

In the section

Configure the following resource quotas in the

For more information on Resource Manager, see our documentation.

Configure the following properties in the

Example configuration:

    defaultNavigatorSettings: {

        showOnlyEntities: true,

        hideFolders: true,

        hideVirtualModel: true

    }

For more information on Database Navigator, see our documentation.

To configure password policies, refer to the Password policy guide.

To learn about configuring the query manager database, see the Query manager database configuration documentation.

When you start the Team Edition server for the first time, an administrator interface guides you through the server setup. However, in some cases—like when running in a Kubernetes environment—you might need to configure the server automatically.

To set up the server automatically, specify the following parameters:

How to specify these parameters:

• OS environment variables: Set these variables directly in your system’s environment settings.
• Configuration file: Add them to a
  .cloudbeaver.auto.conf
  file, located in the same directory as the
  cloudbeaver.conf
  file.

The Session Manager in Team Edition allows you to view and search for active database sessions.

• Altibase
• Amazon Redshift
• Db2 for LUW
• Exasol
• Greenplum
• MariaDB
• Microsoft SQL Server
• MongoDB
• MySQL
• Oracle Database

• PostgreSQL

To open the Session Manager:

1) In the Database Navigator, navigate to your desired database.

2) Right-click and select Session Manager.

Once you have opened the Session Manager, you will have an ability to view all active database sessions.

To terminate a session, click the Terminate Session button at the bottom of the Session Manager window.

For more detailed information, click the context menu button on a session and select Show in value panel from the context menu:

Available tabs:

• Details: Detailed information about the session.
• Query: A summary of the SQL query associated with the session.

Team Edition offers keyboard shortcuts to help you work more efficiently. You can find these shortcuts within the web application by clicking on the Shortcuts icon in the top toolbar. You can also refer to the detailed list provided in this article below.