· clickhouse clickhouse-local til

ClickHouse: Code: 60. DB::Exception: Table does not exist

I’ve been playing with clickhouse-local again this week and ran into an interesting issue when persisting a table that I thought I’d document for future Mark.

You can install ClickHouse on your machine by running the following command:

curl https://clickhouse.com/ | sh

Or you could use HomeBrew if you’re working on a Mac:

brew install clickhouse

We can then launch clickhouse-local, which lets you run ClickHouse in what I think of as an embedded mode. I’m also going to specify a directory for it to store any data that I create:

./clickhouse local -m --path people.chdb

We’re going to create a table based on the following CSV file:

Table 1. people.csv
name hobby

Mark

tennis

Michael

football

James

cricket

Which we can do by running this query:

create table people ENGINE=MergeTree() ORDER BY tuple() AS
select *
from file('people.csv', CSVWithNames);

If we then query the peoples table:

SELECT *
FROM people
Output
Query id: 8567a2c0-5a98-4981-9097-fd5ed33d173e

┌─name────┬─hobby────┐
│ Mark    │ tennis   │
│ Michael │ football │
│ James   │ cricket  │
└─────────┴──────────┘

3 rows in set. Elapsed: 0.002 sec.

All good so far! But if we close that session, launch ClickHouse Local again, and then re-run the query we’ll get the following error:

Output
Query id: ffab38e6-b39c-4be4-88b0-834f43a67cd9


0 rows in set. Elapsed: 0.262 sec.

Received exception:
Code: 60. DB::Exception: Table _local.people does not exist. (UNKNOWN_TABLE

The problem that we’ve run into is described on this GitHub issue:

The behavior is expected. The default database of clickhouse-local is using the Memory engine that does not persist the .sql metadata files.

So if we want the data to be persisted, we need to first create a database, rather than using the default one. Let’s do that:

CREATE DATABASE mark;
Output
Query id: 104191d4-9d63-4888-8e40-f754270233d4

Ok.

0 rows in set. Elapsed: 0.003 sec.

Not forgetting to then switch to that database:

USE mark;

And then we’ll run our create table command again. Once we’ve done that, we’ll close clickhouse-local and launch it again.

Let’s check that our database is still there:

SHOW DATABASES:
Output
Query id: 56eb7dd5-20b5-46db-89da-f0f0d0b87293

┌─name───────────────┐
│ INFORMATION_SCHEMA │
│ _local             │
│ information_schema │
│ mark               │
│ system             │
└────────────────────┘

5 rows in set. Elapsed: 0.001 sec.

And we can query it too:

select * from mark.people;
Output
Query id: 77c8c500-3309-4dd3-8244-e178af40fb41

┌─name────┬─hobby────┐
│ Mark    │ tennis   │
│ Michael │ football │
│ James   │ cricket  │
└─────────┴──────────┘

3 rows in set. Elapsed: 0.002 sec.
  • LinkedIn
  • Tumblr
  • Reddit
  • Google+
  • Pinterest
  • Pocket