Contact UsGet started
© 2024 Iron Hive LLC Belgrade

Configuring access to Object Storage from a ClickHouse® cluster

Written by
Updated at November 18, 2024

Managed Service for ClickHouse® supports using Yandex Object Storage to:

To access Object Storage bucket data from a cluster, set up password-free access to the bucket using a service account:

  1. Connect the service account to the cluster.
  2. Set up access rights for the service account.

See also Examples of working with objects.

Before you begin, make sure your account in Yandex Cloud has the iam.serviceAccounts.user role or higher. You will need this role in the following cases:

  • To create or modify a cluster and link it to a service account.
  • To restore a cluster linked to a service account from its backup.

Connect the service account to the cluster

  1. When creating or updating a cluster, either select an existing service account or create a new one.

  2. Make sure that this account is assigned the correct roles from the storage.* role group. If required, assign it the appropriate roles, e.g., storage.viewer and storage.uploader.

Tip

To link Managed Service for ClickHouse® clusters to Object Storage, we recommend using dedicated service accounts. This allows you to work with any buckets, including those to which you cannot or should not allow public access.

Set up access rights

  1. In the management console, select the folder where the bucket is located. If there is no bucket, create one and populate it with the required data.

  2. Select Object Storage.

  3. Set up the bucket ACL or object ACL:

    1. In the list of buckets or objects, select the required element and click .
    2. Click Bucket ACL or Object ACL.
    3. In the Select a user drop-down list, specify the service account connected to the cluster.
    4. Set the required permissions for the service account from the drop-down list.
    5. Click Add and Save.

    Note

    If necessary, revoke access from one or more users by clicking Cancel in the appropriate line.

Examples of working with objects

You can get a link to an object in a bucket in the following format: https://storage.yandexcloud.net/<bucket_name>/<object_name>. You can use it to work with geotags and schemas or to use the s3 table function and the S3 table engine.

The S3 table engine is similar to File and URL engines, except that data is stored in an S3-compatible storage (such as Yandex Object Storage), rather than a file system or remote HTTP/HTTPS server. This engine allows reading data from or writing data to the storage using standard SQL queries, such as SELECT and INSERT.

The s3 table function provides the same functionality as the S3 table engine, but you do not need to create a table in advance to use it.

For example, if the Object Storage bucket contains a file named table.tsv that stores table data in TSV format, then you can create a table or function to work with this file. You need to set up password-free access and get a link to the table.tsv file first.

  1. Assign the managed-clickhouse.editor and storage.uploader roles to the service account linked to the Managed Service for ClickHouse® cluster.

  2. Create a table:

    CREATE TABLE test (n Int32) ENGINE = S3('https://storage.yandexcloud.net/<bucket_name>/table.tsv', 'TSV');

  3. Run test queries to the table:

    INSERT INTO test VALUES (1);
SELECT * FROM test;

┌─n─┐
│ 1 │
└───┘

  1. Assign the managed-clickhouse.editor and storage.uploader roles to the service account linked to the Managed Service for ClickHouse® cluster.

  2. Insert data:

    INSERT INTO FUNCTION s3('https://storage.yandexcloud.net/<bucket_name>/table.tsv', 'TSV', 'n Int32') VALUES (1);

  3. Run a test query:

    SELECT * FROM s3('https://storage.yandexcloud.net/<bucket_name>/table.tsv', 'TSV', 'n Int32');

┌─n─┐
│ 1 │
└───┘

ClickHouse® is a registered trademark of ClickHouse, Inc.

Previous
Managing machine learning models
Next
Managing shards