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:
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
SELECT * FROM people
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:
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
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;
Query id: 104191d4-9d63-4888-8e40-f754270233d4 Ok. 0 rows in set. Elapsed: 0.003 sec.
Not forgetting to then switch to that database:
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:
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;
Query id: 77c8c500-3309-4dd3-8244-e178af40fb41 ┌─name────┬─hobby────┐ │ Mark │ tennis │ │ Michael │ football │ │ James │ cricket │ └─────────┴──────────┘ 3 rows in set. Elapsed: 0.002 sec.