单词查询用字典查询

发布于 2025-01-21 11:04:59 字数 1856 浏览 1 评论 0原文

我从此处导入Ontime Airlines的数据库 https:https:// clickhouse。 com/doc/en/get-start/example-datasets/ontime/

然后,我创建了一个词典映射2个数字飞机代码到这样的公司名称:

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 
...
..

我使用此查询来生成它,并且似乎正在工作:

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)

在主数据库中(ONTAIRE)看起来像这样:

SELECT Reporting_Airline AS R_air
FROM ontime
GROUP BY R_air
LIMIT 4


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

我想做的是使用R_AIR 2代码值的表,然后根据airlinecompany doct of tode ot to创建映射IE,

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

但我不能似乎正确地形成了此查询:

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)

我缺少什么?我不知道为什么它认为ua是uint64

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

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

清君侧 2025-01-28 11:04:59

布局= 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           │
└───────┴───────────────────┘

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           │
└───────┴───────────────────┘

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文