GLOBAL关键字拼错了,SQL为什么还能执行?
发布于 6 个月前 作者 kikanjuu 540 次浏览 来自 问答

我有以下SQL,其中的GLOBAL关键字故意拼错了(GaaLOBAL)。我发现它还能执行。这是为啥?

select count(distinct(device_id)), city_name FROM (select distinct(deviceId) as device_id from client_log_all) GaaLOBAL ALL INNER JOIN trait_all using device_id group by city_name

image.png

这句SQL所关联的两张表都是分布式表。schema如下:

CREATE TABLE client_log_all
(
 serverDate Date,
 channel String,
 version String,
 carrierOperator String,
 deviceId String,
 deviceName String,
 deviceType String,
 manufacturer String,
 networkMode String,
 os String,
 ip String,
 userAgent String,
 serverTime UInt64,
 uid String,
 height UInt32,
 width UInt32,
 latitude Float64,
 longitude Float64,
 responseCode String,
 resource String,
 clientTime UInt64,
 tid String,
 traffic String,
 playResource String,
 srcPage String,
 srcPageId String,
 srcModule String,
 srcPosition String,
 srcSubModule String,
 moduleType String,
 item String,
 itemId String,
 appName String,
 x_abtest_bucketIds String,
 offline_data_gentime UInt64,
 x_client_sendtime UInt64,
 type String,
 copy UInt32
) ENGINE = Distributed(four, default, client_log, rand())
CREATE TABLE IF NOT EXISTS trait_all
(
insert_date Date,
device_id String,
device_categories String,
device_download_counts Int32,
generic_categories String,
p_counts_30_days Int32,
p_days_30_days Int32,
p_du_30_days Int32,
p_language_distribution_30_days String,
p_period_dist_30_days String,
p_time_distribution_7_30_days String,
p_unwd_period_dist_30_days String,
p_wd_period_dist_30_days String,
subscribe_album_counts_30_days String,
p_time_distribution_7_7days String,
age_type_code Int32,
all_tags_list String,
city_name String,
device_click_interest_list String,
device_click_meta String,
current_channel String,
current_device_manufacturer String,
current_device_type String,
current_os String,
current_os_version String,
current_version String,
front_age_range String,
front_gender String,
front_interested_categories String,
front_lastupdate_time String,
device_game_level String,
gender_type_code Int32,
device_if_install_live_app String,
last_playlocation String,
latest_7days_search_keywords_cut String,
latest_7days_search_keywords_cut_word String,
life_state String,
live_last_play_time String,
live_playroom_num_30days Int32,
live_play_num_30days Int32,
live_preference String,
long_term_top_class_interest_list String,
user_music_style_trait String,
os_name String,
device_outter_app String,
device_outter_app_cate String,
device_outter_app_custom_cate String,
device_phone_type String,
play_first_album_id Int64,
play_first_anchor_id Int64,
play_first_count Int32,
play_first_duration Float64,
play_first_time String,
play_first_trackid Int64,
play_last_time String,
price_prefer String,
province_name String,
search_keywords String,
search_keywords_cut String,
select_categories String,
short_term_top_class_interest_list String,
total_duration Float64,
user_long_term_interest String,
user_short_term_interest String,
user_cutwordstags String,
user_metatags String,
user_tags_list String,
dau_play_flag String,
mau_play_flag String,
nau_play_flag String,
insert_timestamp DateTime
) ENGINE = Distributed(four, default, trait, rand())

另外,我想请教下distributed_product_mode这个配置。 它的作用是什么?它的可能的值有哪些?官方文档没说。

2 回复

同求这个distributed_product_mode的设置样例

@fuzimu 我后来找到了。districuted_product_mode的可能值有: ‘deny’, ‘local’, ‘global’, ‘allow’

'local’和’global’两种分布式JOIN的区别,参见https://clickhouse.yandex/docs/en/query_language/queries/#distributed-subqueries

回到顶部