Clickhouse query with dictionary

Question

I imported the database of ontime airlines from here https://clickhouse.com/docs/en/getting-started/example-datasets/ontime/

Then I created a dictionary mapping the 2 digit airplane codes to company names like this:

id,code,company
1,UA,United Airlines
2,HA,Hawaiian Airlines
3,OO,SkyWest
4,B6,Jetblue Airway
5,QX,Horizon Air
6,YX,Republic Airway
7,G4,Allegiant Air 
...
..

I used this query to generate it and it seems to be working:

CREATE DICTIONARY airlinecompany
(
    id UInt64, 
    code String,
    company String

)
PRIMARY KEY id 
SOURCE(FILE(path '/var/lib/clickhouse/user_files/airlinenames.csv' format 'CSVWithNames'))
LAYOUT(FLAT())
LIFETIME(3600)

In the main database (ontime) Looks like this:

SELECT Reporting_Airline AS R_air
FROM ontime
GROUP BY R_air
LIMIT 4


┌─R_air─┐
│ UA    │
│ HA    │
│ OO    │
│ B6    │
└───────┘

What I want to do is have a table that uses R_air's 2 code value and then checks it against the airlinecompany dict to create a mapping ie

R_Air Company
UA | United Airlines
HA | Hawaiian Airlines
00 | SkyWest
...
..

But I cant seem to form this query correctly:

SELECT
    Reporting_Airline AS R_Air,
    dictGetString('airlinecompany', 'company', R_Air) AS company
FROM ontime
GROUP BY R_Air
    

Received exception from server (version 22.3.3):
Code: 6. DB::Exception: Received from localhost:9000. DB::Exception: Cannot parse string 'UA' as UInt64: syntax error at begin of string. Note: there are toUInt64OrZero and toUInt64OrNull functions, which returns zero/NULL instead of throwing exception.: while executing 'FUNCTION dictGetString('airlinecompany' :: 1, 'company' :: 2, Reporting_Airline :: 0) -> dictGetString('airlinecompany', 'company', Reporting_Airline) String : 4'. (CANNOT_PARSE_TEXT)

What am I missing? I dont know why it thinks UA is a UInt64

Answer 1

LAYOUT = COMPLEX_KEY_HASHED

CREATE DICTIONARY airlinecompany
(
    id UInt64, 
    code String,
    company String

)
PRIMARY KEY code 
SOURCE(FILE(path '/var/lib/clickhouse/user_files/airlinenames.csv' format 'CSVWithNames'))
LAYOUT(COMPLEX_KEY_HASHED())
LIFETIME(3600)


SELECT dictGet('airlinecompany', 'company', tuple('UA'))
┌─dictGet('airlinecompany', 'company', tuple('UA'))─┐
│ United Airlines                                   │
└───────────────────────────────────────────────────┘


SELECT Reporting_Airline AS R_air,
       dictGetString('airlinecompany', 'company', tuple(R_Air)) AS company
FROM ontime
LIMIT 4;

┌─R_Air─┬─company───────────┐
│ B6    │ Jetblue Airway    │
│ G4    │ Allegiant Air     │
│ HA    │ Hawaiian Airlines │
│ OO    │ SkyWest           │
└───────┴───────────────────┘