Issues with loading Maxmind Data into Clickhouse Database using a local file

Question

I'm trying to insert Maxmind Data into a Clickhouse Dictionary but defining it source as a local file where I can running my Client from.

so to define my dictionary I use the query:

CREATE DICTIONARY usage_analytics.city_locations(
    geoname_id UInt64 DEFAULT 0,
    ...
    ...
    ...
    ...
)
PRIMARY KEY geoname_id
SOURCE(File(path '/home/ubuntu/maxmind_csv/GeoLite2-City-Locations-en.csv' format 'CSVWithNames'))
SETTINGS(format_csv_allow_single_quotes = 0)
LAYOUT(HASHED())
LIFETIME(300);

yet I keep getting hit with the error of:

Failed to load dictionary 'usage_analytics.city_locations': std::exception. Code: 1001, type: std::__1::__fs::filesystem::filesystem_error, e.what() = filesystem error: in canonical: No such file or directory [\home/ubuntu/maxmind_csv/GeoLite2-City-Locations-en.csv] [/],

According to the documentation, I have to use its absolute path, which I did by using readlink, and still it cannot detect my file. I am running a clickhouse client from a remote machine and have the files on the remote machine. Am I suppose to have my files else where or what?

score 1 · Answer 1 · answered Aug 23 '21 at 22:55

1

It looks like this file is not available, to fix it need to to set right ownership for file:

chown clickhouse:clickhouse /home/ubuntu/maxmind_csv/GeoLite2-City-Locations-en.csv

# chown -R clickhouse:clickhouse /home/ubuntu/maxmind_csv

answered Aug 23 '21 at 22:55

vladimir

13,428
2
44
70

score 1 · Answer 2 · answered Aug 23 '21 at 23:33

.XML dictionary allows to read files from any folder. SQL dictionary does not.

https://clickhouse.tech/docs/en/sql-reference/dictionaries/external-dictionaries/external-dicts-dict-sources/#dicts-external_dicts_dict_sources-local_file

When dictionary with source FILE is created via DDL command (CREATE DICTIONARY ...), the source file needs to be located in user_files directory, to prevent DB users accessing arbitrary file on ClickHouse node.

/etc/clickhouse-server/config.xml

    <!-- Directory with user provided files that are accessible by 'file' table function. -->
    <user_files_path>/var/lib/clickhouse/user_files/</user_files_path>

Issues with loading Maxmind Data into Clickhouse Database using a local file

2 Answers2