常用操作

1. 参数测试
   insert overwrite table graph_embedding.hs_tmp_208
   select cast(a.label as bigint) as label, cast(b.score as bigint) as score from
   (select * from graph_embedding.hs_query_title_inference_gt_2)a join (select * from graph_embedding.hs_dssm_result_3)b on a.query_id == b.query_id and a.title_id == b.video_id;

hs_dssm_result_2;| query_id | video_id | score | query_emb | video_emb |

tunnel download -fd '|' graph_embedding.hs_tmp_208 /home/hengsong/hs_label_score.txt;

2. dssm常用表

create table hs_dssm_dic_query_7 as
select distinct query_id, query from hs_tmp_247;

create table hs_dssm_dic_title_11 as
select distinct item_id, title from hs_tmp_247;

create table graph_embedding.hs_dssm_dic_title_12 as
select graph_embedding:hs_split_1(item_id, pair, "|") as (item_id, word, weight) from
(select bi_udf:bi_split_value(item_id, tag_result, "%") as (item_id, pair) from
(select item_id, search_kg:alinlp_termweight_ecom(title, "%", "{weight}", 1, 0) as tag_result from graph_embedding.hs_dssm_dic_title_11 where lengthb(title) > 0)a)b where lengthb(b.pair) > 0;

create table graph_embedding.hs_dssm_dic_query_8 as
select graph_embedding:hs_split_1(query_id, pair, "|") as (query_id, word, weight) from
(select bi_udf:bi_split_value(query_id, tag_result, "%") as (query_id, pair) from
(select query_id, search_kg:alinlp_termweight_ecom(query, "%", "{weight}", 1, 0) as tag_result from graph_embedding.hs_dssm_dic_query_7 where lengthb(query) > 0)a)b where lengthb(b.pair) > 0;

create table hs_dssm_dic_query_9 as select query_id as id, word, search_kg:alinlp_word_embedding(hs_return_clean(word), "100", "CONTENT_SEARCH") as emb from hs_dssm_dic_query_8;

create table hs_dssm_dic_query_10 as
select b.id, a.word, b.emb, a.weight, graph_embedding:change_weight_query_key_1(a.word, a.weight) as new_weight from hs_dssm_dic_query_8 a join hs_dssm_dic_query_9 b on a.query_id == b.id and a.word == b.word;

create table hs_dssm_dic_query_11 as
select id, return_concat_1(new_weight, emb) as query_emb from hs_dssm_dic_query_10 group by id;

create table hs_dssm_dic_title_13 as select item_id as id, word, search_kg:alinlp_word_embedding(hs_return_clean(word), "100", "CONTENT_SEARCH") as emb from hs_dssm_dic_title_12;

create table hs_dssm_dic_title_14 as
select b.id, a.word, b.emb, a.weight, graph_embedding:change_weight_query_key_1(a.word, a.weight) as new_weight from hs_dssm_dic_title_12 a join hs_dssm_dic_title_13 b on a.item_id == b.id and a.word == b.word;

create table hs_dssm_dic_title_15 as
select id, return_concat_1(new_weight, emb) as title_emb from hs_dssm_dic_title_14 group by id;

---

train_query : hs_dssm_dic_query_7 - | query_id | query |
hs_dssm_dic_query_11 : | id | query_emb |
train_title : hs_dssm_dic_title_11 - | item_id | title |
hs_dssm_dic_title_15 : | id | title_emb |

---

inference_query : hs_dssm_dic_query_inf_7 - | query_id | query |
hs_dssm_dic_query_inf_11 : | id | query_emb |
inference_title : hs_dssm_dic_title_inf_10 - | item_id | title |
hs_dssm_dic_title_inf_14 : | id | title_emb |

---

hs_tmp_157 : | title_id | query_id |

hs_query_title_inference_gt_3 : | query | title | label | query_id | title_id |

添加资源

drop resource hs_udtf_82_1.py;
yes
add py /home/hengsong/query_co_video/src/hs_udtf_82_1.py;
CREATE FUNCTION hs_return_brand AS hs_udtf_82_1.return_brand USING hs_udtf_82_1.py;

按组标号

drop table hs_tmp_239;
yes
create table hs_tmp_239 as
select *,row_number() over (partition by query_id order by cast(corr_len as bigint) desc) from hs_tmp_236;