How to setup clickhouse dictionary from CSV file with flat layout?

[kriticar]

Hi,

 

I have created a file (-rwxrwxrwx 1 clickhouse clickhouse   522 Aug 27 14:22 SeqTypesKeyValues.csv) with the following contents:

 

$ cat /etc/clickhouse-server/SeqTypesKeyValues.csv

0;Zero

1;One

 

In /etc/clickhouse-server/config.xml above </yandex> tag, I have put the following:

 

               <dictionary>

                              <name>SeqTypes</name>

                              <source>

                                <file>

                                             <path>/etc/clickhouse-server/SeqTypesKeyValues.csv</path>

                                             <format>CSV</format>

                                </file>

                              </source>

                              <lifetime>30</lifetime>

                              <layout>

                                             <flat />

                              </layout>

                              <structure>

                                             <id>

                                                            <name>SeqID</name>

                                             </id>

                                             <attribute>

                                                            <name>SeqType</name>

                                                            <type>String</type>

                                             </attribute>

                              </structure>

                </dictionary>    

 

 

In clickhouse-server.log there are no errors.

When I execute:

 

select dictGetString('SeqTypes', 'SeqType', toUInt64(1))

 

I get

 

SQL Error [36]: ClickHouse exception, code: 36, host: localhost, port: 2001; Code: 36, e.displayText() = DB::Exception: No such external dictionary: SeqTypes, e.what() = DB::Exception

 

How come that SeqTypes directory does not exist?

Can someone help?

 

Regards.

[Denis Zhuravlev]

Check system.dictionaries last_exception column for errors.

select * from system.dictionaries where name = 'SeqTypes'

CSV comma separated, so

0,Zero

1,One

[kriticar]

select * from system.dictionaries doesn't return any rows.

I have changed all ";" in the file and replaced them with ","

than edited config.xml again in order to touch it, but again, no errors in the errors log file, and nothing in system.dictionaries.

What I have done wrongly?

[Denis Zhuravlev]

try to surround it with <dictionaries> tag

<dictionaries>

    <dictionary>

                              <name>SeqTypes</name>

    </dictionary>

</dictionaries>

On Monday, 27 August 2018 09:42:06 UTC-3, kriticar wrote:

[kriticar]

This is the last version of the config.txt

  <dictionaries>
  <dictionary>
    <name>SeqTypes</name>

    <source>
      <file>
        <path>/etc/clickhouse-server/SeqTypesKeyValues.csv</path>
        <format>CSV</format>
      </file>
    </source>
    <lifetime>30</lifetime>
    <layout>
      <flat />
    </layout>
    <structure>
      <id>
        <name>SeqID</name>
      </id>
      <attribute>
        <name>SeqType</name>
        <type>String</type>
      </attribute>
    </structure>
  </dictionary>

  </dictionaries>

Still, no errors in the errors log file, nor rows in system.dictionaries. :(

[Denis Zhuravlev]

check /var/logs/clickhouse-server/clickhouse-server.err.log and /var/logs/clickhouse-server/clickhouse-server.log

On Monday, 27 August 2018 09:42:06 UTC-3, kriticar wrote:

[kriticar]

Denis,

after updating config.xml, err.log is unchanged. In it I can only see the error after executing select dictGetString('SeqTypes', 'SeqType', toUInt64(1))

In .log there are many entries, do you have an idea what should I search for?

As I said earlier, system.dictionaries is empty.

[Oleg A]

You should place your dictionary config in file named SeqTypes_dictionary.xml

Mask for dictionaries config files defined in config.xml and default is  <dictionaries_config>*_dictionary.xml</dictionaries_config>

понедельник, 27 августа 2018 г., 15:42:06 UTC+3 пользователь kriticar написал:

[kriticar]

Oleg,

after puting <dictionaries_config>*_dictionary.xml</dictionaries_config> in config.xml, with SeqTypes_dictionary.xml with the following contents:

bash-4.2$ cat SeqTypes_dictionary.xml

<dictionary>
    <name>SeqTypes</name>
    <source>
      <file>
        <path>/etc/clickhouse-server/SeqTypesKeyValues.csv</path>
        <format>CSV</format>
      </file>
    </source>
    <lifetime>30</lifetime>
    <layout>
      <flat />
    </layout>
    <structure>
      <id>
        <name>SeqID</name>
      </id>
      <attribute>
        <name>SeqType</name>
        <type>String</type>
      </attribute>
    </structure>
</dictionary>

In error log, now I can find these four rows:

2018.08.28 09:39:34.952677 [ 31684 ] <Warning> ExternalDictionaries: /etc/clickhouse-server/SeqTypes_dictionary.xml: unknown node in file: 'name', expected 'dictionary'
2018.08.28 09:39:34.952702 [ 31684 ] <Warning> ExternalDictionaries: /etc/clickhouse-server/SeqTypes_dictionary.xml: unknown node in file: 'source', expected 'dictionary'
2018.08.28 09:39:34.952710 [ 31684 ] <Warning> ExternalDictionaries: /etc/clickhouse-server/SeqTypes_dictionary.xml: unknown node in file: 'lifetime', expected 'dictionary'
2018.08.28 09:39:34.952718 [ 31684 ] <Warning> ExternalDictionaries: /etc/clickhouse-server/SeqTypes_dictionary.xml: unknown node in file: 'layout', expected 'dictionary'
2018.08.28 09:39:34.952745 [ 31684 ] <Warning> ExternalDictionaries: /etc/clickhouse-server/SeqTypes_dictionary.xml: unknown node in file: 'structure', expected 'dictionary'

select * from system.dictionaries returns a row with "name" = SeqTypes, "origin" = /etc/clickhouse-server/SeqTypes_dictionary.xml and "last_exception" = Poco::Exception. Code: 1000, e.code() = 0, e.displayText() = Not found: dictionary.structure.attribute.null_value, e.what() = Not found

Contents of the file /etc/clickhouse-server/SeqTypesKeyValues.csv is:

bash-4.2$ cat SeqTypesKeyValues.csv
0,First
1,Second

Looks, like I am progressing a little bit, but what is wrong with unknown nodes?

I added <null_value>0</null_value> to the attribute sections which now looks like:

      <attribute>
        <name>SeqType</name>
        <type>String</type>

    <null_value>0</null_value>
      </attribute>

After select dictGetString('SeqTypes', 'SeqType', toUInt64(1)), error log shows:

2018.08.28 09:49:42.867692 [ 320 ] <Error> executeQuery: Poco::Exception. Code: 1000, e.code() = 0, e.displayText() = Not found: dictionary.structure.attribute.null_value, e.what() = Not found (from [::1]:56584) (in query: select dictGetString('SeqTypes', 'SeqType', toUInt64(1)) FORMAT TabSeparatedWithNamesAndTypes)
2018.08.28 09:49:42.867769 [ 320 ] <Error> HTTPHandler: Poco::Exception. Code: 1000, e.code() = 0, e.displayText() = Not found: dictionary.structure.attribute.null_value, e.what() = Not found

Where I should put null_value?

[Oleg A]

you should have additional top-level tag:

<dictionaries>

<dictionary>

<name>SeqTypes</name>

...........

</dictionary>

</dictionaries>

понедельник, 27 августа 2018 г., 15:42:06 UTC+3 пользователь kriticar написал:

[kriticar]

Oleg, thanks, now it works.

Looks like original documentation fromhttps://clickhouse.yandex/docs/en/query_language/dicts/external_dicts/

is very misleading. For example, dictionaries should be defined in config.xml, with no dictionaries tag.

<yandex>
    <comment>An optional element with any content. Ignored by the ClickHouse server.</comment>

    <!--Optional element. File name with substitutions-->
    <include_from>/etc/metrika.xml</include_from>


    <dictionary>
        <!-- Dictionary configuration -->
    </dictionary>

    ...

    <dictionary>
        <!-- Dictionary configuration -->
    </dictionary>
</yandex>

Anyway, now dictionary works. Thank you all very very much.
Best regards.