Clickhouse Dictionaries: why CREATE DICTIONARY needs a database?

Question

The new CREATE DICTIONARY idiom in ClickHouse will create the dictionary inside a database:

CREATE DICTIONARY [IF NOT EXISTS] [db.]dictionary_name [ON CLUSTER cluster]

Dictionaries created by XML file are created as empty-string database:

<dictionaries>
  <dictionary>
  <name>rates</name>
  <source>
  ...
</dictionaries>

and they generate an entry on system.dictionaries like this:

:) select * from system.dictionaries \G
...
Row 4:
──────
database:                    
name:                        rates

which is empty-string database, that is understood as a global dictionary that can be referred to without a database prefix.

However, with the CREATE DICTIONARY SQL idiom, if db is not specified, the DICTIONARY is created in the default database, no as a global dictionary:

:) CREATE DICTIONARY IF NOT EXISTS rates (...)

:) select * from system.dictionaries \G

Row 2:
──────
database:                    default
name:                        rates

Question: Is there a way to create a global dictionary via SQL idiom?

Answer 1

DDL dictionary cannot reside in the global scope because it does not exist in terms of DDL.

As I understood, your problem is a backward incompatibility xml-based dictionary and DDL one, so the once way to fix it is to add the database-prefix (for example, if it defines in table schema need to apply ALTER TABLE MODIFY COLUMN-statement). To make migration painlessly from xml- to DDL-based dictionary need to temporarily host both types of dictionaries (xml and DDL) and switched all consumers one by one to DDL dictionaries.

---

DDL dictionaries are much more maintainable and flexible than xml-based ones, because:

• easy to manage them (add, remove, etc) without touching xml-files
• ON CLUSTER-clause allows doing some actions on all the servers of a specified cluster
• manage access privileges
• starting from v20.4.2.9, 2020-05-12 dictionary can be efficiently used in JOIN-clause instead of dictGet (see Altinity: Clickhouse Dictionaries Reloaded)
• work with it as ordinal tables