DEV Community: Javier Vidal

Test Ansible playbooks with Vagrant

Javier Vidal — Thu, 04 Feb 2021 18:15:40 +0000

We normally use Ansible playbooks to configure servers in a cloud provider. When we develop those playbooks we need to test them locally. Thanks to Vagrant it is quite easy to start a virtual machine and run our playbooks against it.

There are two ways to achieve this:

using Vagrant’s Ansible provisioning feature
using the virtual machine like any other remote server

I'm going to explain the second option here.

First, let's create a virtual machine called, for example, tau. The Vagrantfile could be:

Vagrant.configure('2') do |config|
  config.vm.define 'tau' do |debian|
    debian.vm.box = 'debian/buster64'
    debian.vm.network :private_network, ip: '192.168.27.2'
    debian.vm.hostname = 'tau'
    debian.vm.provider 'virtualbox' do |vb|
      vb.memory = '2048'
      vb.cpus = 2
    end
  end
end

We can start the server with:

vagrant up

Now we have to add it to Ansible's inventory, but we need to know the ssh key Vagrant is using when we connect using vagrant ssh:

$ vagrant ssh-config
Host tau
  HostName 127.0.0.1
  User vagrant
  Port 2200
  UserKnownHostsFile /dev/null
  StrictHostKeyChecking no
  PasswordAuthentication no
  IdentityFile /Users/javiervidal/test/.vagrant/machines/tau/virtualbox/private_key
  IdentitiesOnly yes
  LogLevel FATAL

Interesting, we can use /Users/javiervidal/test/.vagrant/machines/tau/virtualbox/private_key in the inventory. We need to add a line like this:

tau ansible_host=192.168.27.2 ansible_port=22 ansible_ssh_user=vagrant ansible_ssh_private_key_file=/Users/javiervidal/test/.vagrant/machines/tau/virtualbox/private_key ansible_python_interpreter=/usr/bin/python3

And finally we can test that Ansible can connect to tau:

$ ansible tau -m ping   
tau | SUCCESS => {
    "changed": false,
    "ping": "pong"
}

😀

ZooKeeper cluster with Docker Compose

Javier Vidal — Tue, 26 Jan 2021 13:56:59 +0000

In this post I talked about setting up a ZooKeeper cluster using Vagrant. In this post I will talk about doing it without having to launch 3 different virtual servers in our development environment thanks to Docker Compose. We will need Docker and Docker Compose installed, of course.

The docker-compose.yml will look like this:



version: "3.9"
services:
  zk1:
    container_name: zk1
    hostname: zk1
    image: bitnami/zookeeper:3.6.2
    ports:
      - 21811:2181
    environment:
      - ALLOW_ANONYMOUS_LOGIN=yes
      - ZOO_SERVER_ID=1
      - ZOO_SERVERS=0.0.0.0:2888:3888,zk2:2888:3888,zk3:2888:3888
  zk2:
    container_name: zk2
    hostname: zk2
    image: bitnami/zookeeper:3.6.2
    ports:
      - 21812:2181
    environment:
      - ALLOW_ANONYMOUS_LOGIN=yes
      - ZOO_SERVER_ID=2
      - ZOO_SERVERS=zk1:2888:3888,0.0.0.0:2888:3888,zk3:2888:3888
  zk3:
    container_name: zk3
    hostname: zk3
    image: bitnami/zookeeper:3.6.2
    ports:
      - 21813:2181
    environment:
      - ALLOW_ANONYMOUS_LOGIN=yes
      - ZOO_SERVER_ID=3
      - ZOO_SERVERS=zk1:2888:3888,zk2:2888:3888,0.0.0.0:2888:3888
  zoonavigator:
    container_name: zoonavigator
    image: elkozmon/zoonavigator
    ports:
      - 9000:9000

It will set up a 3 node cluster, zk1, zk2 and zk3. All we have to do is:



$ docker-compose up -d zk1 zk2 zk3 zoonavigator
Pulling zk1 (bitnami/zookeeper:3.6.2)...
3.6.2: Pulling from bitnami/zookeeper
ff7c165d667c: Pull complete
68ff1df62e09: Pull complete
b2c65a5b1bba: Pull complete
299e03a29f26: Pull complete
ac359c31a869: Pull complete
73ff1a3aae4d: Pull complete
2ff27ed904ba: Pull complete
671b552f88a4: Pull complete
8a18f1c76d65: Pull complete
144c6a5e1dd4: Pull complete
6c8379a42a77: Pull complete
2c88ec00718c: Pull complete
ce2390504de6: Pull complete
Digest: sha256:4bfdd618105aad42160263e5586b9295aa949a932f9bf7736fe148328adc6e3a
Status: Downloaded newer image for bitnami/zookeeper:3.6.2
Pulling zoonavigator (elkozmon/zoonavigator:)...
latest: Pulling from elkozmon/zoonavigator
bb79b6b2107f: Pull complete
00028440d132: Pull complete
ebd07266fb43: Pull complete
33e3eef28e5d: Pull complete
d356e0493ed9: Pull complete
d20cf66b6cb3: Pull complete
0f036ba81390: Pull complete
4245cea4b993: Pull complete
2b8eb69a8e7b: Pull complete
Digest: sha256:a1089a133f116bd760d361d6b0c0b4cbe2fd28d9a81db13cb53547669e70e933
Status: Downloaded newer image for elkozmon/zoonavigator:latest
Creating zk3          ... done
Creating zoonavigator ... done
Creating zk1          ... done
Creating zk2          ... done

We could even access the ZooKeeper servers from the host server thanks to port forwarding: zk1 will be available through port 21811, zk2 through port 21812, and zk3 through port 21813.

Finally, we can also set up a container that will run zoonavigator, a web-based ZooKeeper UI and editor/browser with many features.

We can access zoonavigator from a browser in the host server just using the URL http://localhost:9000/, and connect to zk1 just using zk1:8121 as the connection string.

ZooKeeper cluster with Vagrant

Javier Vidal — Mon, 25 Jan 2021 15:08:05 +0000

If we want to set up a ZooKeeper cluster in our development environment one way to proceed is defining it in Vagrant.

Let's suppose we want to set up a cluster with 3 nodes (zk1, zk2, and zk3). The Vagrantfile will be:

# -*- mode: ruby -*-
# vi: set ft=ruby :

# rubocop: disable Metrics/BlockLength
Vagrant.configure('2') do |config|
  config.vm.define 'zk1' do |zookeeper|
    zookeeper.vm.box = 'debian/stretch64'
    zookeeper.vm.network :private_network, ip: '192.168.33.211'
    zookeeper.vm.hostname = 'zk1'
    # Configure VM
    zookeeper.vm.provider 'virtualbox' do |vb|
      vb.memory = '256'
      vb.cpus = 2
    end
    # Install and start zookeeper
    zookeeper.vm.provision 'shell', inline: <<-SHELL
      apt update
      apt install -y default-jdk
      mkdir -p /var/lib/zookeeper
      chown vagrant:vagrant /var/lib/zookeeper
      wget http://apache.uvigo.es/zookeeper/zookeeper-3.6.2/apache-zookeeper-3.6.2-bin.tar.gz
      tar xvf apache-zookeeper-3.6.2-bin.tar.gz
      rm apache-zookeeper-3.6.2-bin.tar.gz
      mv apache-zookeeper-3.6.2-bin /opt/zookeeper-3.6.2
      chown -R vagrant:vagrant /opt/zookeeper-3.6.2
      echo -e 'tickTime=2000\ndataDir=/var/lib/zookeeper\nclientPort=2181\ninitLimit=5\nsyncLimit=2\nserver.1=192.168.33.211:2888:3888\nserver.2=192.168.33.212:2888:3888\nserver.3=192.168.33.213:2888:3888' > /opt/zookeeper-3.6.2/conf/zoo.cfg
      echo -e '1' > /var/lib/zookeeper/myid
    SHELL
  end

  config.vm.define 'zk2' do |zookeeper|
    zookeeper.vm.box = 'debian/stretch64'
    zookeeper.vm.network :private_network, ip: '192.168.33.212'
    zookeeper.vm.hostname = 'zk2'
    # Configure VM
    zookeeper.vm.provider 'virtualbox' do |vb|
      vb.memory = '256'
      vb.cpus = 2
    end
    # Install and start zookeeper
    zookeeper.vm.provision 'shell', inline: <<-SHELL
      apt update
      apt install -y default-jdk
      mkdir -p /var/lib/zookeeper
      chown vagrant:vagrant /var/lib/zookeeper
      wget http://apache.uvigo.es/zookeeper/zookeeper-3.6.2/apache-zookeeper-3.6.2-bin.tar.gz
      tar xvf apache-zookeeper-3.6.2-bin.tar.gz
      rm apache-zookeeper-3.6.2-bin.tar.gz
      mv apache-zookeeper-3.6.2-bin /opt/zookeeper-3.6.2
      chown -R vagrant:vagrant /opt/zookeeper-3.6.2
      echo -e 'tickTime=2000\ndataDir=/var/lib/zookeeper\nclientPort=2181\ninitLimit=5\nsyncLimit=2\nserver.1=192.168.33.211:2888:3888\nserver.2=192.168.33.212:2888:3888\nserver.3=192.168.33.213:2888:3888' > /opt/zookeeper-3.6.2/conf/zoo.cfg
      echo -e '2' > /var/lib/zookeeper/myid
    SHELL
  end

  config.vm.define 'zk3' do |zookeeper|
    zookeeper.vm.box = 'debian/stretch64'
    zookeeper.vm.network :private_network, ip: '192.168.33.213'
    zookeeper.vm.hostname = 'zk3'
    # Configure VM
    zookeeper.vm.provider 'virtualbox' do |vb|
      vb.memory = '256'
      vb.cpus = 2
    end
    # Install and start zookeeper
    zookeeper.vm.provision 'shell', inline: <<-SHELL
      apt update
      apt install -y default-jdk
      mkdir -p /var/lib/zookeeper
      chown vagrant:vagrant /var/lib/zookeeper
      wget http://apache.uvigo.es/zookeeper/zookeeper-3.6.2/apache-zookeeper-3.6.2-bin.tar.gz
      tar xvf apache-zookeeper-3.6.2-bin.tar.gz
      rm apache-zookeeper-3.6.2-bin.tar.gz
      mv apache-zookeeper-3.6.2-bin /opt/zookeeper-3.6.2
      chown -R vagrant:vagrant /opt/zookeeper-3.6.2
      echo -e 'tickTime=2000\ndataDir=/var/lib/zookeeper\nclientPort=2181\ninitLimit=5\nsyncLimit=2\nserver.1=192.168.33.211:2888:3888\nserver.2=192.168.33.212:2888:3888\nserver.3=192.168.33.213:2888:3888' > /opt/zookeeper-3.6.2/conf/zoo.cfg
      echo -e '3' > /var/lib/zookeeper/myid
    SHELL
  end
end
# rubocop: enable Metrics/BlockLength

This way we could start the 3 nodes of the cluster with:

vagrant up zk1
vagrant up zk2
vagrant up zk3

This Vagrantfile does not start the ZooKeeper nodes automatically so we will have to start them manually:

vagrant ssh zk1
cd /opt/zookeeper-3.6.2/bin
./zkServer.sh start

I'm sure this Vagrantfile can be improved removing the repeated parts and starting the servers automatically.

ClickHouse incremental backups

Javier Vidal — Thu, 14 Jan 2021 12:28:50 +0000

clickhouse-backup allows us to perform local backups, that are always full backups, and full or incremental uploads to remote storage. In my previous post I talked about how to perform full backups and uploads. Now we are going to review all the steps required to work with incremental uploads. This way we could upload a weekly full backup to our remote storage and perform daily incremental uploads.

Let's assume we already have clickhouse-backup installed and we have configured a remote storage.

Let's create a database backup, a table test and a user backup than can access this database:

:) CREATE DATABASE IF NOT EXISTS backup

:) CREATE TABLE IF NOT EXISTS backup.test
(
  `date` Datetime,
  `user_id` String,
  `pageviews` Int32
)
ENGINE = MergeTree()
PARTITION BY toStartOfHour(date)
ORDER BY (date)

:) CREATE USER backup IDENTIFIED WITH plaintext_password BY 'qwerty'

:) GRANT ALL ON backup.* TO backup

We can edit /etc/clickhouse-backup/config.yml to configure this user as the clickhouse-backup user.

Let's insert some data in the table:

INSERT INTO backup.test VALUES
('2021-01-13 07:00:00', 'user 1', 7),
('2021-01-13 08:00:00', 'user 2', 3),
('2021-01-13 09:00:00', 'user 3', 1),
('2021-01-13 10:00:00', 'user 4', 12)

Perform a full backup and upload it:

# clickhouse-backup create backup1
2021/01/14 10:51:06 Create backup 'backup1'
2021/01/14 10:51:06 Freeze 'backup.test'
2021/01/14 10:51:06 Copy part hashes
2021/01/14 10:51:06 Writing part hashes
2021/01/14 10:51:06 Copy metadata
2021/01/14 10:51:06   Done.
2021/01/14 10:51:06 Move shadow
2021/01/14 10:51:06   Done.
# clickhouse-backup upload backup1
2021/01/14 10:51:40 Upload backup 'backup1'
 4.36 KiB / 4.36 KiB [======================================================] 100.00% 0s
2021/01/14 10:51:41   Done.

Insert more data, perform a full backup and an incremental upload:

INSERT INTO backup.test VALUES
('2021-01-13 11:00:00', 'user 3', 5),
('2021-01-13 12:00:00', 'user 5', 2)

# clickhouse-backup create backup2
2021/01/14 10:55:04 Create backup 'backup2'
2021/01/14 10:55:05 Freeze 'backup.test'
2021/01/14 10:55:05 Copy part hashes
2021/01/14 10:55:05 Writing part hashes
2021/01/14 10:55:05 Copy metadata
2021/01/14 10:55:05   Done.
2021/01/14 10:55:05 Move shadow
2021/01/14 10:55:05   Done.
# clickhouse-backup upload --diff-from backup1 backup2
2021/01/14 10:55:38 Upload backup 'backup2'
 6.44 KiB / 6.44 KiB [======================================================] 100.00% 0s
2021/01/14 10:55:38   Done.

And once more:

INSERT INTO backup.test VALUES
('2021-01-13 13:00:00', 'user 1', 1),
('2021-01-13 14:00:00', 'user 5', 8)

# clickhouse-backup create backup3
2021/01/14 10:56:10 Create backup 'backup3'
2021/01/14 10:56:10 Freeze 'backup.test'
2021/01/14 10:56:10 Copy part hashes
2021/01/14 10:56:10 Writing part hashes
2021/01/14 10:56:10 Copy metadata
2021/01/14 10:56:10   Done.
2021/01/14 10:56:10 Move shadow
2021/01/14 10:56:10   Done.
# clickhouse-backup upload --diff-from backup2 backup3
2021/01/14 10:56:20 Upload backup 'backup3'
 8.52 KiB / 8.52 KiB [======================================================]
 100.00% 0s
2021/01/14 10:56:21   Done.

Delete the local backups:

# clickhouse-backup delete local backup1
# clickhouse-backup delete local backup2
# clickhouse-backup delete local backup3

List the remote backups:

# clickhouse-backup list
Local backups:
no backups found
Remote backups:
- 'backup1.tar.gz'  3.54 KiB    (created at 14-01-2021 12:12:42)
- 'backup2.tar.gz'  3.02 KiB    (created at 14-01-2021 12:13:17)
- 'backup3.tar.gz'  3.29 KiB    (created at 14-01-2021 12:14:47)

Let's drop the database:

DROP DATABASE backup

If we download backup3, the latest backup, we see that backup1 and backup2 are also downloaded:

# clickhouse-backup download backup3
 0 / 3374 [------------------------------------------------------]   0.00%
2021/01/14 13:16:39 Backup 'backup3' required 'backup2'. Downloading.
 0 / 3088 [------------------------------------------------------]   0.00%
2021/01/14 13:16:40 Backup 'backup2' required 'backup1'. Downloading.
 3.54 KiB / 3.54 KiB [======================================================] 100.00% 0s
 3.02 KiB / 3.02 KiB [======================================================] 100.00% 0s
 3.29 KiB / 3.29 KiB [======================================================] 100.00% 1s
2021/01/14 13:16:41   Done.

If we examine backup3.tar.gz we could find a meta.json file that contains:

{
    "required_backup": "backup2",
    "hardlinks": [
        "shadow/backup/test/1610517600_1_1_0/checksums.txt",
        "shadow/backup/test/1610517600_1_1_0/columns.txt",
    ...

So these meta.json files will keep track of the files that are kept from previous backups.

Let's do the restore. If we restore the latest incremental backup:

# clickhouse-backup restore backup3
2021/01/14 13:19:54 Create table 'backup.test'
2021/01/14 13:19:54 Prepare data for restoring 'backup.test'
2021/01/14 13:19:54 ALTER TABLE `backup`.`test` ATTACH PART '1610517600_1_1_0'
2021/01/14 13:19:54 ALTER TABLE `backup`.`test` ATTACH PART '1610521200_2_2_0'
2021/01/14 13:19:54 ALTER TABLE `backup`.`test` ATTACH PART '1610524800_3_3_0'
2021/01/14 13:19:54 ALTER TABLE `backup`.`test` ATTACH PART '1610528400_4_4_0'
2021/01/14 13:19:54 ALTER TABLE `backup`.`test` ATTACH PART '1610532000_5_5_0'
2021/01/14 13:19:54 ALTER TABLE `backup`.`test` ATTACH PART '1610535600_6_6_0'
2021/01/14 13:19:54 ALTER TABLE `backup`.`test` ATTACH PART '1610539200_7_7_0'
2021/01/14 13:19:54 ALTER TABLE `backup`.`test` ATTACH PART '1610542800_8_8_0'

We are restoring the full backup:

:) select count() from backup.test

SELECT count()
FROM backup.test

┌─count()─┐
│       8 │
└─────────┘

1 rows in set. Elapsed: 0.003 sec.

😄

Backup and restore with clickhouse-backup

Javier Vidal — Wed, 13 Jan 2021 13:45:40 +0000

ClickHouse includes native support for instantaneous point-in-time backups, through its ALTER TABLE ... FREEZE feature. ClickHouse creates hard links in the shadow directory to all the partitions. This way, each backup is effectively a full backup, and duplicate use of disk space is avoided.

After performing a manual backup we should move it to another location. To restore it, we should:

recover the backup from the other location
copy the partitions to the detached directory for each of the tables
attach each partition with an ALTER TABLE ... ATTACH PARTITION

We can automate this process thanks to clickhouse-backup.

Let's assume we already have clickhouse-backup installed and we have configured a remote storage.

Let's create a database backup, a table test and a user backup than can access this database:

:) CREATE DATABASE IF NOT EXISTS backup

:) CREATE TABLE IF NOT EXISTS backup.test
(
  `date` Datetime,
  `user_id` String,
  `pageviews` Int32
)
ENGINE = MergeTree()
PARTITION BY toStartOfHour(date)
ORDER BY (date)

:) CREATE USER backup IDENTIFIED WITH plaintext_password BY 'qwerty'

:) GRANT ALL ON backup.* TO backup

We can edit /etc/clickhouse-backup/config.yml to configure this user as the clickhouse-backup user.

Let's insert some data in the table:

INSERT INTO backup.test VALUES
('2021-01-13 07:00:00', 'user 1', 7),
('2021-01-13 08:00:00', 'user 2', 3),
('2021-01-13 09:00:00', 'user 3', 1),
('2021-01-13 10:00:00', 'user 4', 12)

We can check all the backups we have performed until now:

# clickhouse-backup list
Local backups:
no backups found
Remote backups:
no backups found

If can check the list of tables that clickhouse-backup would backup:

# clickhouse-backup tables
backup.test

Let's create a local backup:

# clickhouse-backup create
2021/01/13 13:14:43 Create backup '2021-01-13T12-14-43'
2021/01/13 13:14:43 Freeze 'backup.test'
2021/01/13 13:14:43 Copy part hashes
2021/01/13 13:14:43 Writing part hashes
2021/01/13 13:14:43 Copy metadata
2021/01/13 13:14:43   Done.
2021/01/13 13:14:43 Move shadow
2021/01/13 13:14:43   Done.
# clickhouse-backup list
Local backups:
- '2021-01-13T12-14-43' (created at 13-01-2021 13:14:43)
Remote backups:
no backups found

We can upload it to the remote storage and delete the local backup:

# clickhouse-backup upload '2021-01-13T12-14-43'
2021/01/13 13:15:39 Upload backup '2021-01-13T12-14-43'
 4.36 KiB / 4.36 KiB [======================================================] 100.00% 0s
2021/01/13 13:15:39   Done.
# clickhouse-backup delete local '2021-01-13T12-14-43'
# clickhouse-backup list
Local backups:
no backups found
Remote backups:
- '2021-01-13T12-14-43.tar.gz'  3.55 KiB    (created at 13-01-2021 12:15:39)

Disasters can happen at any time. Let's drop the database:

DROP DATABASE backup

😱

Don't worry, we can download the remote backupa and restore:

# clickhouse-backup download '2021-01-13T12-14-43'
 3.55 KiB / 3.55 KiB [======================================================] 100.00% 0s
2021/01/13 13:34:22   Done.
# clickhouse-backup restore '2021-01-13T12-14-43'
2021/01/13 13:35:45 Create table 'backup.test'
2021/01/13 13:35:45 Prepare data for restoring 'backup.test'
2021/01/13 13:35:45 ALTER TABLE `backup`.`test` ATTACH PART '1610517600_1_1_0'
2021/01/13 13:35:45 ALTER TABLE `backup`.`test` ATTACH PART '1610521200_2_2_0'
2021/01/13 13:35:45 ALTER TABLE `backup`.`test` ATTACH PART '1610524800_3_3_0'
2021/01

💃

:) select count() from backup.test

SELECT count()
FROM backup.test

┌─count()─┐
│       4 │
└─────────┘

Tables size in ClickHouse

Javier Vidal — Mon, 11 Jan 2021 08:05:38 +0000

We can check the size of ClickHouse tables with this query:

SELECT
    concat(database, '.', table) AS table,
    formatReadableSize(sum(bytes)) AS size,
    sum(bytes) AS bytes_size,
    sum(rows) AS rows,
    max(modification_time) AS latest_modification,
    any(engine) AS engine
FROM system.parts
WHERE active
GROUP BY
    database,
    table
ORDER BY bytes_size DESC

Rails minor version upgrade

Javier Vidal — Sun, 10 Jan 2021 08:47:39 +0000

Migrating a minor version of Rails is quite straightforward:

check the release notes for the new version
edit the Gemfile and update the Rails version
run bundle update
run your tests

Updating data in a ClickHouse cluster replacing partitions

Javier Vidal — Wed, 28 Oct 2020 17:09:22 +0000

Using the ALTER TABLE ... UPDATE statement in ClickHouse is a heavy operation not designed for frequent use. If we design our schema to insert/update a whole partition at a time, we could update large amounts of data easily. Doing it in a simple MergeTree table is quite simple, but doing it in a cluster with replicated tables is trickier. Let's see how could be done.

In this example we use a cluster test_cluster that consists of 2 nodes. We will create a replicated table in each node and a distributed table that we could use to parallelize reading.

In each node we should have the test_cluster configured in the /etc/clickhouse-server/config.xml configuration file:

<remote_servers>
  <test_cluster>
    <shard>
      <internal_replication>true</internal_replication>
      <replica>
        <host>192.168.33.201</host>
        <port>9000</port>
      </replica>
      <replica>
        <host>192.168.33.202</host>
        <port>9000</port>
      </replica>
    </shard>
  </test_cluster>
</remote_servers>

In this example we have the IP addresses but we could have the hostnames if they were correctly configured in the /etc/hosts file.

We will use also a couple of macros in /etc/clickhouse-server/config.d/macros.xml. They will be used as parameters in ON CLUSTER DDL statements. In the first ClickHouse server (ch1) we have:

<yandex>
  <macros>
    <shard>shard_01</shard>
    <replica>replica_01</replica>
  </macros>
</yandex>

And in the second one (ch2):

<yandex>
  <macros>
    <shard>shard_01</shard>
    <replica>replica_02</replica>
  </macros>
</yandex>

Let's connect to one of the nodes and create the database test. As we use ON CLUSTER this statement will be executed in all the nodes of the cluster:

CREATE DATABASE IF NOT EXISTS test ON CLUSTER test_cluster

Let's create the replicated table. This will be the table that will store all the data. We will perform the partitioning by hour and insert all the data for that hour in a single insert.

CREATE TABLE IF NOT EXISTS test.hits_local ON CLUSTER test_cluster
(
  `date` Datetime,
  `user_id` String,
  `pageviews` Int32
)
ENGINE = ReplicatedMergeTree('/clickhouse/test_cluster/tables/{shard}/hits_local', '{replica}')
PARTITION BY toStartOfHour(date)
ORDER BY (date)

Then we create a table that will be used just to perform the inserts. It must be created in all the nodes of the cluster and its engine must be MergeTree, otherwise we could end up experiencing a lot of ZooKeeper errors. We will call this table the temporary table (notice that it's not a real TEMPORARY table).

CREATE TABLE IF NOT EXISTS test.hits_local_tmp ON CLUSTER test_cluster
AS test.hits_local
ENGINE = MergeTree()
PARTITION BY toStartOfHour(date)
ORDER BY (date)

Finally we could create the distributed table:

CREATE TABLE IF NOT EXISTS test.hits ON CLUSTER 'test_cluster'
AS test.hits_local
ENGINE = Distributed('test_cluster', 'test', 'hits_local')

So now we have 3 tables in each of the nodes. In ch1:

ch1 :) use test
ch1 :) show tables
┌─name───────────┐
│ hits           │
│ hits_local     │
│ hits_local_tmp │
└────────────────┘

And in ch2:

ch2 :) use test
ch2 :) show tables
┌─name───────────┐
│ hits           │
│ hits_local     │
│ hits_local_tmp │
└────────────────┘

Let's insert some values in the temporary table:

ch1 :) INSERT INTO test.hits_local_tmp VALUES
('2020-10-27 10:00:00', 'user 1', 7),
('2020-10-27 10:00:00', 'user 2', 3),
('2020-10-27 10:00:00', 'user 3', 1),
('2020-10-27 10:00:00', 'user 4', 12)

We can check that now we have a new partition:

ch1 :) SELECT partition, name, active FROM system.parts WHERE table = 'hits_local_tmp'
┌─partition───────────┬─name─────────────┬─active─┐
│ 2020-10-27 10:00:00 │ 1603792800_1_1_0 │      1 │
└─────────────────────┴──────────────────┴────────┘

The idea is to move this partition in the temporary table to the distributed table. But we can not do it using MOVE PARTITION:

ch1 :) ALTER TABLE test.hits_local_tmp MOVE PARTITION '2020-10-27 10:00:00' TO TABLE test.hits_local
Received exception from server (version 20.10.2):
  Code: 48. DB::Exception: Received from 127.0.0.1:9000. DB::Exception: Table test.hits_local_tmp (400fd602-745e-4904-9f61-76ccda9eaf0a) supports movePartitionToTable only for MergeTree family of table engines. Got ReplicatedMergeTree.

The good news is that we can do a REPLACE PARTITION and delete the original partition in the temporary table (REPLACE PARTITION does not delete the source partition). The REPLACE PARTITION works even if we don't have a partition to replace in the destination table:

ch1 :) ALTER TABLE test.hits_local REPLACE PARTITION '2020-10-27 10:00:00' FROM test.hits_local_tmp
ch1 :) ALTER TABLE test.hits_local_tmp DROP PARTITION '2020-10-27 10:00:00'

Now we have the partition in the distributed table:

ch1 :) SELECT partition, name, active FROM system.parts WHERE table = 'hits_local'
┌─partition───────────┬─name─────────────┬─active─┐
│ 2020-10-27 10:00:00 │ 1603792800_1_1_0 │      1 │
└─────────────────────┴──────────────────┴────────┘

The data are there:

ch1 :) select * from test.hits_local
┌────────────────date─┬─user_id─┬─pageviews─┐
│ 2020-10-27 10:00:00 │ user 1  │         7 │
│ 2020-10-27 10:00:00 │ user 2  │         3 │
│ 2020-10-27 10:00:00 │ user 3  │         1 │
│ 2020-10-27 10:00:00 │ user 4  │        12 │
└─────────────────────┴─────────┴───────────┘

We can query them through the distributed table:

ch1 :) select * from test.hits
┌────────────────date─┬─user_id─┬─pageviews─┐
│ 2020-10-27 10:00:00 │ user 1  │         7 │
│ 2020-10-27 10:00:00 │ user 2  │         3 │
│ 2020-10-27 10:00:00 │ user 3  │         1 │
│ 2020-10-27 10:00:00 │ user 4  │        12 │
└─────────────────────┴─────────┴───────────┘

They are replicated to the other node:

ch2 :) select * from test.hits_local
┌────────────────date─┬─user_id─┬─pageviews─┐
│ 2020-10-27 10:00:00 │ user 1  │         7 │
│ 2020-10-27 10:00:00 │ user 2  │         3 │
│ 2020-10-27 10:00:00 │ user 3  │         1 │
│ 2020-10-27 10:00:00 │ user 4  │        12 │
└─────────────────────┴─────────┴───────────┘

And the partition has been created in the second node too:

ch2 :) SELECT partition, name, active FROM system.parts WHERE table = 'hits_local'
┌─partition───────────┬─name─────────────┬─active─┐
│ 2020-10-27 10:00:00 │ 1603792800_1_1_0 │      1 │
└─────────────────────┴──────────────────┴────────┘

Now let's do the update of the whole partition. We have to perform the insert again in the temporary table:

ch1 :) INSERT INTO test.hits_local_tmp VALUES
('2020-10-27 10:00:00', 'user 1', 6),
('2020-10-27 10:00:00', 'user 2', 4),
('2020-10-27 10:00:00', 'user 3', 2),
('2020-10-27 10:00:00', 'user 4', 15),
('2020-10-27 10:00:00', 'user 5', 14)

A new partition is created:

ch1 :) SELECT partition, name, active FROM system.parts WHERE table = 'hits_local_tmp'
┌─partition───────────┬─name─────────────┬─active─┐
│ 2020-10-27 10:00:00 │ 1603792800_3_3_0 │      1 │
└─────────────────────┴──────────────────┴────────┘

We can use it to replace the data in the replicated table:

ch1 :) ALTER TABLE test.hits_local REPLACE PARTITION '2020-10-27 10:00:00' FROM test.hits_local_tmp
ch1 :) ALTER TABLE test.hits_local_tmp DROP PARTITION '2020-10-27 10:00:00'

We see that we only have a partition in the first node:

ch1 :) SELECT partition, name, active FROM system.parts WHERE table = 'hits_local'
┌─partition───────────┬─name─────────────┬─active─┐
│ 2020-10-27 10:00:00 │ 1603792800_3_3_0 │      1 │
└─────────────────────┴──────────────────┴────────┘

The new data are there:

ch1 :) select * from test.hits_local
┌────────────────date─┬─user_id─┬─pageviews─┐
│ 2020-10-27 10:00:00 │ user 1  │         6 │
│ 2020-10-27 10:00:00 │ user 2  │         4 │
│ 2020-10-27 10:00:00 │ user 3  │         2 │
│ 2020-10-27 10:00:00 │ user 4  │        15 │
│ 2020-10-27 10:00:00 │ user 5  │        14 │
└─────────────────────┴─────────┴───────────┘

They are replicated to the second node:

ch2 :) select * from test.hits_local
┌────────────────date─┬─user_id─┬─pageviews─┐
│ 2020-10-27 10:00:00 │ user 1  │         6 │
│ 2020-10-27 10:00:00 │ user 2  │         4 │
│ 2020-10-27 10:00:00 │ user 3  │         2 │
│ 2020-10-27 10:00:00 │ user 4  │        15 │
│ 2020-10-27 10:00:00 │ user 5  │        14 │
└─────────────────────┴─────────┴───────────┘

And the distributed table is fine too:

ch1 :) select * from test.hits
┌────────────────date─┬─user_id─┬─pageviews─┐
│ 2020-10-27 10:00:00 │ user 1  │         6 │
│ 2020-10-27 10:00:00 │ user 2  │         4 │
│ 2020-10-27 10:00:00 │ user 3  │         2 │
│ 2020-10-27 10:00:00 │ user 4  │        15 │
│ 2020-10-27 10:00:00 │ user 5  │        14 │
└─────────────────────┴─────────┴───────────┘

Deleting ClickHouse replication entries from ZooKeeper

Javier Vidal — Wed, 28 Oct 2020 12:17:38 +0000

When playing with ClickHouse replicated tables it is quite probable that we end up getting errors like:

DB::Exception: Replica /clickhouse/my_cluster/tables/shard_01/bids/replicas/replica_01 already exists

Deleting this entry it's quite simple. Just connect to one of the ZooKeeper nodes in the cluster and execute:

./zkCli.sh deleteall /clickhouse/my_cluster/tables/shard_01/bids/replicas/replica_01

If we want to start all over we could even delete all the information regarding the cluster my_cluster:

./zkCli.sh deleteall /clickhouse/my_cluster

document.getElementById vs jQuery ID selector

Javier Vidal — Sat, 24 Oct 2020 07:24:35 +0000

document.getElementById and jQuery ID selector are not equivalent.

var element = document.getElementById('id');

returns an HTML DOM Object, and

var element = $('#id');

returns a jQuery Object. In jQuery, to get the same result as document.getElementById we can do:

var element = $('#id')[0];

How to create a read-only user in ClickHouse

Javier Vidal — Mon, 19 Oct 2020 10:22:59 +0000

In ClickHouse, a settings profile is a collection of settings grouped under the same name. By default, there is a profile readonly that allows only read queries. This is from /etc/clickhouse-server/users.xml:

<yandex>
    <!-- Profiles of settings. -->
    <profiles>
        ...
        <!-- Profile that allows only read queries. -->
        <readonly>
            <readonly>1</readonly>
        </readonly>
    </profiles>
...

So to create a read-only user we just have to:

CREATE USER username IDENTIFIED WITH plaintext_password BY 'qwerty' SETTINGS PROFILE 'readonly'
GRANT SHOW TABLES, SELECT ON database.* TO username