Virtual Warehouses

Overview

A virtual warehouse is a cluster of computing resources in Bytehouse that we can scale out on demand. A warehouse provides the required resources, such as CPU, memory, and temporary storage, to perform the following operations:

• Executing SQL statements that require compute resources (e.g. retrieving rows from tables and views) for short and long-running queries.

• Performing DML operations, such as:

  • Updating rows in tables (DELETE , INSERT , UPDATE).

  • Loading data into tables

  • Unloading data from tables

Virtual warehouses have the following advantages:

1. Read-write separation and resource separation: Separate Virtual Warehouses can be created to perform data-loading tasks, or run queries in real-time. Creating Virtual Warehouses also allows different departments or business lines to have a clear separation of resources, avoiding potential impact on performances between different queries.

2. One-click scaling: Virtual Warehouses can easily scale up or down according to business requirements for cost-effective resource utilisation.

3. Access control: Virtual warehouses can also be granted different permissions depending on the applied role. This can provide users with fine-grained access control for access to data.

Virtual Warehouse Sizes

• XS (Extra Small)
• S (Small)
• M (Medium)
• L (Large)
• XL (Extra Large)

Managing Virtual Warehouses

Bytehouse Virtual Warehouse page provides an interface for creating, starting, and suspend virtual warehouse on-demand, as well as configuring and scaling up/down its compute resources.

Creating Virtual Warehouse

1. In the Virtual Warehouse page, click the +New button on the top right corner

2. Create Virtual Warehouse dialog will open and using this dialog we can specify the virtual warehouse attribute to allocate compute resources.

3. Once done, click OK and the virtual warehouse will be automatically created and started.

• Note: ByteHouse Virtual Warehouses have auto-suspend/resume functionality enabled by default.

If auto-suspend is enabled, the virtual warehouse will be shut down if there is no request during the time interval.

If auto-resume is enabled, users do not need to manually enable the virtual warehouses. Instead, the Virtual Warehouse will resume when a new query is submitted. Please take note that a longer time is needed for the first query to run when a virtual warehouse has auto-resumed.

Updating a Virtual Warehouse

Virtual Warehouse configurations can be updated at any time with several limitations:

1. Only the size, auto-suspend, auto-resume, and description can be updated

2. If the change results in the Virtual Warehouse being suspended, this may cause running queries to be interrupted.

3. Changes to Virtual Warehouse sizes will only take effect after the Virtual Warehouse resumes functionality from a suspended state.

Resuming a Virtual Warehouse

Resuming a Virtual Warehouse that was suspended can be done using any one of the following options:

1. Resume one or multiple virtual warehouses using item selection and click ▷ the button.

2. Resume one particular virtual warehouses using triple-dot drop-down and click Resume

3. After enabling auto-resume, users can directly choose virtual warehouse from SQL worksheet, then turn on the virtual warehouse by query running.

Suspending Virtual Warehouse

A Virtual Warehouse can be suspended using any one of the following options:

1. Resume one or multiple virtual warehouses using item selection and click ⏸️ button.

2. Resume one particular virtual warehouses using triple-dot drop-down and click Stop

3. After enabling auto-suspend, if there is no request during the time interval, the virtual warehouse will be auto-suspended. Currently we support the following intervals:

• 5 minutes
• 30 minutes
• 1 hour
• 6 hours

NOTE: suspending a virtual warehouse can cause any queries that run within this warehouse to fail instantly. Be careful when performing this operation.

Dropping Virtual Warehouse

Dropping a Virtual Warehouse effectively suspends all the running queries and removes the cluster from deployment and the virtual warehouse from the lists of available virtual warehouses.