Integrate Google Cloud Storage with ClickHouse

### Start ClickHouse server[​](#start-clickhouse-server "Direct link to Start ClickHouse server")

On `chnode1` and `chnode` run:

```
sudo service clickhouse-server start
```

```
sudo service clickhouse-server status
```

### Verification[​](#verification "Direct link to Verification")

#### Verify disk configuration[​](#verify-disk-configuration "Direct link to Verify disk configuration")

`system.disks` should contain records for each disk:

- default
- gcs
- cache

```
SELECT *
FROM system.disks
FORMAT Vertical
```

```
Row 1:
──────
name:             cache
path:             /var/lib/clickhouse/disks/gcs/
free_space:       18446744073709551615
total_space:      18446744073709551615
unreserved_space: 18446744073709551615
keep_free_space:  0
type:             s3
is_encrypted:     0
is_read_only:     0
is_write_once:    0
is_remote:        1
is_broken:        0
cache_path:       /var/lib/clickhouse/disks/gcs_cache/

Row 2:
──────
name:             default
path:             /var/lib/clickhouse/
free_space:       6555529216
total_space:      10331889664
unreserved_space: 6555529216
keep_free_space:  0
type:             local
is_encrypted:     0
is_read_only:     0
is_write_once:    0
is_remote:        0
is_broken:        0
cache_path:

Row 3:
──────
name:             gcs
path:             /var/lib/clickhouse/disks/gcs/
free_space:       18446744073709551615
total_space:      18446744073709551615
unreserved_space: 18446744073709551615
keep_free_space:  0
type:             s3
is_encrypted:     0
is_read_only:     0
is_write_once:    0
is_remote:        1
is_broken:        0
cache_path:

3 rows in set. Elapsed: 0.002 sec.
```

#### Verify that tables created on the cluster are created on both nodes[​](#verify-that-tables-created-on-the-cluster-are-created-on-both-nodes "Direct link to Verify that tables created on the cluster are created on both nodes")

```
-- highlight-next-line
create table trips on cluster 'cluster_1S_2R' (
 `trip_id` UInt32,
 `pickup_date` Date,
 `pickup_datetime` DateTime,
 `dropoff_datetime` DateTime,
 `pickup_longitude` Float64,
 `pickup_latitude` Float64,
 `dropoff_longitude` Float64,
 `dropoff_latitude` Float64,
 `passenger_count` UInt8,
 `trip_distance` Float64,
 `tip_amount` Float32,
 `total_amount` Float32,
 `payment_type` Enum8('UNK' = 0, 'CSH' = 1, 'CRE' = 2, 'NOC' = 3, 'DIS' = 4))
ENGINE = ReplicatedMergeTree
PARTITION BY toYYYYMM(pickup_date)
ORDER BY pickup_datetime
-- highlight-next-line
SETTINGS storage_policy='gcs_main'
```

```
┌─host───────────────────────────────────────┬─port─┬─status─┬─error─┬─num_hosts_remaining─┬─num_hosts_active─┐
│ chnode2.us-east4-c.c.gcsqa-375100.internal │ 9000 │      0 │       │                   1 │                1 │
└────────────────────────────────────────────┴──────┴────────┴───────┴─────────────────────┴──────────────────┘
┌─host───────────────────────────────────────┬─port─┬─status─┬─error─┬─num_hosts_remaining─┬─num_hosts_active─┐
│ chnode1.us-east1-b.c.gcsqa-375100.internal │ 9000 │      0 │       │                   0 │                0 │
└────────────────────────────────────────────┴──────┴────────┴───────┴─────────────────────┴──────────────────┘

2 rows in set. Elapsed: 0.641 sec.
```

#### Verify that data can be inserted[​](#verify-that-data-can-be-inserted "Direct link to Verify that data can be inserted")

```
INSERT INTO trips SELECT
    trip_id,
    pickup_date,
    pickup_datetime,
    dropoff_datetime,
    pickup_longitude,
    pickup_latitude,
    dropoff_longitude,
    dropoff_latitude,
    passenger_count,
    trip_distance,
    tip_amount,
    total_amount,
    payment_type
FROM s3('https://ch-nyc-taxi.s3.eu-west-3.amazonaws.com/tsv/trips_{0..9}.tsv.gz', 'TabSeparatedWithNames')
LIMIT 1000000
```

#### Verify that the storage policy `gcs_main` is used for the table.[​](#verify-that-the-storage-policy-gcs_main-is-used-for-the-table "Direct link to verify-that-the-storage-policy-gcs_main-is-used-for-the-table")

```
SELECT
    engine,
    data_paths,
    metadata_path,
    storage_policy,
    formatReadableSize(total_bytes)
FROM system.tables
WHERE name = 'trips'
FORMAT Vertical
```

```
Row 1:
──────
engine:                          ReplicatedMergeTree
data_paths:                      ['/var/lib/clickhouse/disks/gcs/store/631/6315b109-d639-4214-a1e7-afbd98f39727/']
metadata_path:                   /var/lib/clickhouse/store/e0f/e0f3e248-7996-44d4-853e-0384e153b740/trips.sql
storage_policy:                  gcs_main
formatReadableSize(total_bytes): 36.42 MiB

1 row in set. Elapsed: 0.002 sec.
```

#### Verify in Google Cloud console[​](#verify-in-google-cloud-console "Direct link to Verify in Google Cloud console")

Looking at the buckets you will see that a folder was created in each bucket with the name that was used in the `storage.xml` configuration file. Expand the folders and you will see many files, representing the data partitions.

#### Bucket for replica one[​](#bucket-for-replica-one "Direct link to Bucket for replica one")

#### Bucket for replica two[​](#bucket-for-replica-two "Direct link to Bucket for replica two")

- [GCS backed MergeTree](#gcs-backed-mergetree)
  - [Creating a disk](#creating-a-disk)- [Creating a table](#creating-a-table)- [Handling replication](#handling-replication)- [Learn more](#learn-more)- [Using Google Cloud Storage (GCS)](#gcs-multi-region)
    - [Plan the deployment](#plan-the-deployment)- [Prepare virtual machines](#prepare-vms)- [Create two buckets](#create-two-buckets)- [ch\_bucket\_us\_east1](#ch_bucket_us_east1)- [ch\_bucket\_us\_east4](#ch_bucket_us_east4)- [Generate an access key](#generate-an-access-key)- [Create a service account HMAC key and secret](#create-a-service-account-hmac-key-and-secret)- [Add a new service account](#add-a-new-service-account)- [Configure ClickHouse Keeper](#configure-clickhouse-keeper)- [Configure ClickHouse server](#configure-clickhouse-server)- [Start ClickHouse Keeper](#start-clickhouse-keeper)- [Start ClickHouse server](#start-clickhouse-server)- [Verification](#verification)

Was this page helpful?