Datalake解析联接 [英] Datalake analytic join

问题描述

我有2张桌子.我想要表[Activite_Site]中的机密URL我已经尝试了下面的查询，但是它不起作用...任何人都知道. 预先谢谢你

I have 2 table. I want classified URL who is in table [Activite_Site] I've try the query below, but it doesn't work... Anyone have idea. Thank you in advance

Table [Categorie]
URL                         CAT
http//www.site.com/business B2B
http//www.site.com/office   B2B
http//www.site.com/job      B2B
http//www.site.com/home     B2C

Table [Actvite_Site]
URL
http//www.site.com/business/page2/test.html
http//www.site.com/business/page3/pagetest/tot.html
http//www.site.com/office/all/tot.html
http//www.site.com/home/holiday/paris.html
http//www.site.com/home/private/moncompte.html

I would like OUTPUT :

URL_SITE                                            CATEGORIE
http//www.site.com/business/page2/test.html         B2B
http//www.site.com/business/page3/pagetest/tot.html B2B
http//www.site.com/office/all/tot.html              B2B
http//www.site.com/home/holiday/paris.html          B2C
http//www.site.com/home/private/moncompte.html      B2C
http//www.site.com/test/pte.html                    Null

My query :

    SELECT A.URL AS URL_SITE
           C.CAT AS  CATEGORIE  
    FROM Actvite_Site as A
        LEFT Categorie as C ON C.URL==A.URL.PadLeft(C.URL.Lenght)

推荐答案

RE错误E_CSC_USER_JOINCOLUMNSEXPECTEDONEACHSIDEOFCONDITION，U-SQL当前在连接条件中不支持派生列.

RE error E_CSC_USER_JOINCOLUMNSEXPECTEDONEACHSIDEOFCONDITION, U-SQL does not currently support derived columns in join conditions.

一种实现此目的的方法可能是先找到匹配的URL，然后将不匹配的URL和UNION在一起.

One way to achieve this might be to find the matched URLs, then the unmatched and UNION them together.

@category = SELECT *
     FROM (
        VALUES
            ( "http//www.site.com/business", "B2B" ),
            ( "http//www.site.com/office", "B2B" ),
            ( "http//www.site.com/job", "B2B" ),
            ( "http//www.site.com/home", "B2C" )
        ) AS x(url, cat);


@siteActivity = SELECT *
     FROM (
        VALUES
            ( "http//www.site.com/business/page2/test.html" ),
            ( "http//www.site.com/business/page3/pagetest/tot.html" ),
            ( "http//www.site.com/office/all/tot.html" ),
            ( "http//www.site.com/home/holiday/paris.html" ),
            ( "http//www.site.com/home/private/moncompte.html" ),
            ( "http//www.site.com/test/pte.html" )
        ) AS x(url);


// Find matched URLs
@working =
    SELECT sa.url,
           c.cat
    FROM @siteActivity AS sa
         CROSS JOIN
             @category AS c
         WHERE sa.url.Substring(0, c.url.Length) == c.url;


// Combine the matched and unmatched URLs
@output =
    SELECT url,
           cat
    FROM @working

    UNION ALL

    SELECT url,
           (string) null AS cat
    FROM @siteActivity AS sa
         ANTISEMIJOIN
             @working AS w
         ON sa.url == w.url;



OUTPUT @output TO "/output/output.csv"
USING Outputters.Csv(quoting:false);

我想知道是否还有一种更有效的方法.

I am wondering if there is a more efficient way though.

这篇关于Datalake解析联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！