Virtual warehouses

Overview

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

  • Executing SQL statements that require computing 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. Virtual warehouses also allow different departments or business lines to have a clear separation of resources, avoiding impact on the performance of 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: We can also grant virtual warehouses different permissions depending on the applied role. This can provide granular access control for 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 suspending a virtual warehouse on-demand, as well as configuring and scaling up/down its computing resources.

2866

Creating a virtual warehouse

2718
  1. On the virtual warehouse page, click the +New button in the upper right corner

  2. Create Virtual Warehouse dialog will open, with which we can specify the virtual warehouse attributes to allocate computing resources.

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

Note: ByteHouse virtual warehouses have auto-suspend/resume enabled by default.

If auto-suspend is enabled, the virtual warehouse will shut down if there is no computing request for a certain time interval.

If auto-resume is enabled, the paused virtual warehouse will wake up and resume computing when a new query is submitted. Please note that on resumption, a longer time is needed for the first query to run.

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

We can resume a suspended virtual warehouse by:

  1. Resume one or multiple virtual warehouses by selecting the items and clicking the ▷ button.
1003
  1. Resume a particular virtual warehouse using the triple-dot menu and choosing Resume.
999
  1. After enabling auto-resume, users can directly choose virtual warehouse from SQL worksheet, then turn it on by executing a query.
1950

Suspending a virtual warehouse

A virtual warehouse can be suspended by:

  1. Suspend one or multiple virtual warehouses by selecting the items and clicking ⏸️ button.
1002
  1. Suspend a particular virtual warehouses using triple-dot menu and choosing Stop.
1010
  1. After enabling auto-suspend, if there is no computing request for a certain 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 (deleting) a virtual warehouse

Dropping a virtual warehouse effectively suspends all the running queries. It removes the cluster from deployment and the virtual warehouse from the list of available virtual warehouses.

1222