20190710工作进展

1. 将udf得到的结果进行title和query的对应

pai -name pytorch -project algo_public_dev -Dpython=3.6 -Dscript="file:///apsarapangu/disk1/hengsong.lhs/origin_deep_cluster_odps_5.tar.gz" -DentryFile="test_query_with_title.py" -Dtables="odps://graph_embedding/tables/hs_heter_graph_embedding_out_nearest_neighbor_006,odps://graph_embedding/tables/hs_jingyan_query_related_video_pool_2_3,odps://graph_embedding/tables/hs_jingyan_query_related_top_query_1" -Doutputs="odps://graph_embedding/tables/hs_query_title_7" -Dbucket="oss://bucket-automl/" -Darn="acs:ram::1293303983251548:role/graph2018" -Dhost="cn-hangzhou.oss-internal.aliyun-inc.com" -DuserDefinedParameters="" -DworkerCount=1;

2. 得到url
   select get_json_object(body, '$.entities.k0.url/s') from hs_jingyan_query_related_video_pool_2_3 limit 1;

select hs_jingyan_query_related_top_query_1.query from hs_jingyan_query_related_top_query_1 join hs_query_title_6 where hs_query_title_6.index == hs_jingyan_query_related_top_query_1.id limit 10;

hs_query_title_6
hs_jingyan_query_related_top_query_1

select hs_query_title_6.index, hs_query_title_6.query, hs_query_title_6.title_id, hs_query_title_6.title, hs_query_title_6.score, hs_jingyan_query_related_top_query_1.query as origin_query from hs_jingyan_query_related_top_query_1 join hs_query_title_6 where hs_query_title_6.index + 1 == hs_jingyan_query_related_top_query_1.id limit 10;

select a.query, get_json_object(b.body, '$.entities.k0.url/s')
from (
select query,id from hs_jingyan_query_related_top_query_1
)a join (
select * from hs_query_title_6
) b on a.id=b.index;

select get_json_object(body, '$.entities.k0.url/s') as url,hs_query_title_6.index, hs_query_title_6.query, hs_query_title_6.title_id, hs_query_title_6.title, hs_query_title_6.score, hs_jingyan_query_related_top_query_1.query as origin_query from hs_jingyan_query_related_top_query_1 join hs_query_title_6 where hs_query_title_6.index + 1 == hs_jingyan_query_related_top_query_1.id limit 10;

hs_jingyan_query_related_video_pool_2_3:id
hs_test_3:title_id

create table hs_result_title_query_1w as
select index, origin_query, query, title_id, title, score, url_k2, url_k3
from (
select get_json_object(body, '.entities.k3.play_url/s') as url_k3,id from hs_jingyan_query_related_video_pool_2_3
)a join (
select * from hs_test_3
) b on a.id=b.title_id;

select get_json_object(body, '$.entities.k3.play_url/s') as url from hs_jingyan_query_related_video_pool_2_3 where id == 212099128936;

8. 叶子类目数据处理：
   select id, title from hs_jingyan_query_related_video_pool_2_3 where title in (select title from hs_jingyan_query_related_video_pool_2_3 limit 100);

select item_id,title, cate_id, cate_name,cate_level from tbcdm.dim_tb_itm where ds=max_pt('tbcdm.dim_tb_itm') and title == '买一送一冬季棉拖鞋女室内家居情侣保暖厚底居家用毛绒包跟棉鞋男';

select distinct cate_name from tbcdm.dim_tb_itm where ds=max_pt('tbcdm.dim_tb_itm') and title == '买一送一冬季棉拖鞋女室内家居情侣保暖厚底居家用毛绒包跟棉鞋男';

create table hs_result_title_query_1w as
select distinct cate_name from tbcdm.dim_tb_itm where ds=max_pt('tbcdm.dim_tb_itm') and title in (select title from hs_jingyan_query_related_video_pool_2_3 limit 2);

9. 构造title和叶子类目的联系表

create table hs_leaf_class_for_title as
select * from (select distinct title as title_tb, cate_id, cate_name,cate_level from tbcdm.dim_tb_itm where ds=max_pt('tbcdm.dim_tb_itm') and title in (select title from hs_jingyan_query_related_video_pool_2_3))a join (select * from hs_jingyan_query_related_video_pool_2_3)b on a.title_tb == b.title;

http://logview.odps.aliyun-inc.com:8080/logview/?h=http://service-corp.odps.aliyun-inc.com/api&p=graph_embedding&i=2019071011411420gzx5j859&token=c2xwd0RBVVQ5MzlWeDNpMFpsdnE3a1F0SzVRPSxPRFBTX09CTzoxMjkzMzAzOTgzMjUxNTQ4LDE1NjMzNjM2NzQseyJTdGF0ZW1lbnQiOlt7IkFjdGlvbiI6WyJvZHBzOlJlYWQiXSwiRWZmZWN0IjoiQWxsb3ciLCJSZXNvdXJjZSI6WyJhY3M6b2RwczoqOnByb2plY3RzL2dyYXBoX2VtYmVkZGluZy9pbnN0YW5jZXMvMjAxOTA3MTAxMTQxMTQyMGd6eDVqODU5Il19XSwiVmVyc2lvbiI6IjEifQ==