How can I create an external dictionary from url in clickhouse?

user_13195768 0
Viewed 647

I'm trying to create a dictionary from http://standards-oui.ieee.org/oui/oui.csv With this code:

CREATE DICTIONARY TestDict (
            registry String DEFAULT '',
            assignment String DEFAULT '',
            name String DEFAULT '',
            address String DEFAULT ''
        )
        PRIMARY KEY assignment
        SOURCE(HTTP(
            url 'http://standards-oui.ieee.org/oui/oui.csv'
            format 'CSVWithNames'
        ))
        LAYOUT(FLAT())
        LIFETIME(300)

But when I try to select * from default.TestDict it returns error "Table default.testDict doesn't exist". And then dictionary status turns to "FAILED". What am I doing wrong?

clickhouse
1 Answers

First let's look at dictionary last_exception-param:

SELECT *
FROM system.dictionaries
FORMAT Vertical
/*
..
last_exception: Code: 27, e.displayText() = DB::Exception: Cannot parse input: expected , before: MA-L,002272,American Micro-Fuel Device Corp.,2181 Buchanan Loop Ferndale WA US 98248 \r\nMA-L,00D0EF,IGT,9295 PROTOTYPE DRIVE RENO NV US 89511 \r\nMA-L,086195,Rockw: (at row 1)

Row 1:
Column 0,   name: assignment, type: UInt64, ERROR: text "MA-L,00227" is not like UInt64
..
*/

The reason for the problem in the wrong selection of layout: FLAT-layout expects the numeric primary key with type UInt64 instead of String.

It needs to use composite key that support String-type and related COMPLEX_KEY_HASHED-layout.

CREATE DICTIONARY TestDict
(
    registry String DEFAULT '', 
    assignment String DEFAULT '', 
    name String DEFAULT '', 
    address String DEFAULT ''
)
PRIMARY KEY registry, assignment, name
SOURCE(HTTP(URL 'http://standards-oui.ieee.org/oui/oui.csv' FORMAT CSVWithNames))
LIFETIME(MIN 0 MAX 300)
LAYOUT(COMPLEX_KEY_HASHED())

Take into account that primary key consists of three columns (registry, assignment, name) to uniquely identify each row.

SELECT count()
FROM default.TestDict
/* result
┌─count()─┐
│   27742 │
└─────────┘
*/
user_3032980
Related