Dictionary from CH table with 115k rows, but id greather than 500.000 - identifier should be less than 500000

[kriticar]

Hi,

I have a table that contains id and description fields.

id field is in range from 100.100 to 99.000.634, the table has 115.787 rows.

I read in the clickhouse documentation that limit for dictionaries is 500.000 rows.

As I have 115k rows in my table I expected that I will be able to create a dictionary.

I have created a dictionary with UInt64 id field, but when I try to get the value from it I get the following error:

SQL Error [69]: ClickHouse exception, code: 69, host: localhost, port: 2001; Code: 69, e.displayText() = DB::Exception: CCD_Moblie_Stations: identifier should be less than 500000, e.what() = DB::Exception

Looks like regardless I don't have 500.000 rows, clickhouse doesn't accept id greather than 500.000.

Is this expected behavior?

Is there a workaround for it?

Regards.

[Denis Zhuravlev]

CH supports different types (layouts). Seems you tried to use FLAT it's a flat array with adressing via element number. It does not matter how many elements you have the flat dictionary supports max id 500000.
Just you another layout -- HASHED.

[kriticar]

Thanks for the tip Denis. I changed layout from flat to hashed as you have suggested, now new questions have showed up.

My dict looks like this:

    <structure>
      <key>
        <attribute>
          <name>ID</name>
          <type>String</type>
        </attribute>
      </key>
      <attribute>
        <name>Description</name>
        <type>String</type>
        <null_value>Unknown</null_value>
      </attribute>
      <attribute>
        <name>Description_long</name>
        <type>String</type>
        <null_value>Unknown</null_value>
      </attribute>
    </structure>

Please notice that ID key attribute is of type String, and layout is of type hashed.

If key is a string, I assumed that I will be able to call dict value with

select dictGetString('CCD_Moblie_Stations', 'Description', '1066800')

but now I get the error:

Illegal type String of third argument of function dictGetString, must be UInt64 or tuple(...).

How come that even when key is of String type I have to use toUInt64.

Problem with toUInt64 is tha when I have an empty string that should be converted to UInt64 format toUint64 function report an error

Attempt to read after eof: Cannot parse UInt64 from String, because value is too short

What I am trying to accomplish is to have id that I can use in dictGetString with as little transformations as possible.

Can you please help?

[Denis Zhuravlev]

>Problem with toUInt64 is tha when I have an empty string that should be converted to UInt64 format toUint64 function report an error

>Attempt to read after eof: Cannot parse UInt64 from String, because value is too short

I would convert empty string to 0 on source side (mysql or whatever you have).

But you can use a tuple trick (complex_key_hashed layout)

check Tips and Tricks https://www.altinity.com/blog/2017/4/12/dictionaries-explained

[kriticar]

Denis,

thank you very very much.

Everything now works.

Thanks.

Regars.