ClickHouse - Tapedrive

ClickHouse is a column-oriented database for analytics. Its s3 table function reads and writes objects directly, and a MergeTree table can keep its data on an S3 disk. Both work against a Tapedrive bucket with the endpoint and your keys.

Before you start

You need a bucket and a credential. A bucket is a tape that has delegated writes to the gateway, and a credential is an access key pair the gateway operator issues you. Set up a bucket walks through both. A table’s data parts are many files. Run it against your own gateway (gateway setup) rather than a public one.

Configure

The s3 table function takes the endpoint and the keys in the call, so it needs no configuration. A disk takes them in the storage configuration:

/etc/clickhouse-server/config.d/tape.xml

<clickhouse>
  <storage_configuration>
    <disks>
      <tape>
        <type>s3</type>
        <endpoint>https://s3.example.com/my-bucket/clickhouse/</endpoint>
        <access_key_id>TAPE4Q7ZK2M9XH3PD8AW</access_key_id>
        <secret_access_key>mZ9vQ3nL8kR2tW6yB4cX7dF1hJ5pS0aG3uE8iO2q</secret_access_key>
        <metadata_path>/var/lib/clickhouse/disks/tape/</metadata_path>
      </tape>
    </disks>
    <policies>
      <tape>
        <volumes>
          <main>
            <disk>tape</disk>
          </main>
        </volumes>
      </tape>
    </policies>
  </storage_configuration>
</clickhouse>

Everyday queries

-- write a query result to the bucket as CSV
INSERT INTO FUNCTION s3('https://s3.example.com/my-bucket/events.csv', 'TAPE4Q7ZK2M9XH3PD8AW', 'mZ9vQ3nL8kR2tW6yB4cX7dF1hJ5pS0aG3uE8iO2q', 'CSVWithNames')
SELECT number AS id, number * 2 AS value FROM numbers(200000);

-- read it back
SELECT count(), sum(value)
FROM s3('https://s3.example.com/my-bucket/events.csv', 'TAPE4Q7ZK2M9XH3PD8AW', 'mZ9vQ3nL8kR2tW6yB4cX7dF1hJ5pS0aG3uE8iO2q', 'CSVWithNames');

-- Parquet, and a glob over several files
SELECT count()
FROM s3('https://s3.example.com/my-bucket/events/*.parquet', 'TAPE4Q7ZK2M9XH3PD8AW', 'mZ9vQ3nL8kR2tW6yB4cX7dF1hJ5pS0aG3uE8iO2q', 'Parquet');

-- a table that lives on the bucket
CREATE TABLE events (id UInt64, value UInt64)
ENGINE = MergeTree ORDER BY id
SETTINGS storage_policy = 'tape';

INSERT INTO events SELECT number, number * 2 FROM numbers(50000);
SELECT count() FROM events;

Writing 200,000 rows through the table function returns in under a second. Twenty inserts of 50,000 rows into the table on the disk take about 1.5 s in total and leave one million rows readable across five parts.

Next

DuckDB for querying the same objects in place.
Uploads for how multipart parts are stored.