logo标签列表

USQL执行缓慢

c#azure-data-lakeu-sql
3
我可以帮您翻译成中文。以下是需要翻译的内容：

我创建了一个简单的脚本来计算两个字符串之间的分数。请查看下面的USQL和后端.net代码。

CN_Matcher.usql：

REFERENCE ASSEMBLY master.FuzzyString;

@searchlog =
        EXTRACT ID int,
                Input_CN string,
                Output_CN string
        FROM "/CN_Matcher/Input/sample.txt"
        USING Extractors.Tsv();

@CleansCheck =
    SELECT ID,Input_CN, Output_CN, CN_Validator.trial.cleanser(Input_CN) AS Input_CN_Cleansed,
           CN_Validator.trial.cleanser(Output_CN) AS Output_CN_Cleansed
    FROM @searchlog;

@CheckData= SELECT ID,Input_CN, Output_CN, Input_CN_Cleansed, Output_CN_Cleansed,
                   CN_Validator.trial.Hamming(Input_CN_Cleansed, Output_CN_Cleansed) AS HammingScore,
                   CN_Validator.trial.LevinstienDistance(Input_CN_Cleansed, Output_CN_Cleansed) AS LevinstienDistance,
                   FuzzyString.ComparisonMetrics.JaroWinklerDistance(Input_CN_Cleansed, Output_CN_Cleansed) AS JaroWinklerDistance
                                       FROM @CleansCheck;

OUTPUT @CheckData
    TO "/CN_Matcher/CN_Full_Run.txt"
    USING Outputters.Tsv();

CN_Matcher.usql.cs:

using Microsoft.Analytics.Interfaces;
using Microsoft.Analytics.Types.Sql;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;

namespace CN_Validator
{
    public static class trial
    {

        public static string cleanser(string val)
        {
            List<string> wordsToRemove = "l.p. registered pc bldg pllc lp. l.c. div. national l p l.l.c international r. limited school azioni joint co-op corporation corp., (corp) inc., societa company llp liability l.l.l.p llc bancorporation manufacturing c dst (inc) jv ltd. llc. technology ltd., s.a. mfg rllp incorporated per venture l.l.p c. p.l.l.c l.p.. p. partnership corp co-operative s.p.a tech schl bancorp association lllp n r ltd inc. l.l.p. p.c. co district int intl assn. sa inc l.p co, co. division lc intl. lp professional corp. a l. l.l.c. building r.l.l.p co.,".Split(' ').ToList();
            return string.Join(" ", val.ToLower().Split(' ').Except(wordsToRemove));
        }

        public static int Hamming(string source, string target)
        {   
            int distance = 0;
            if (source.Length == target.Length)
            {
                for (int i = 0; i < source.Length; i++)
                {
                    if (!source[i].Equals(target[i]))
                    {
                        distance++;
                    }
                }
                return distance;
            }
            else { return 99999; }
        }

        public static int LevinstienDistance(string source, string target)
        {
            int n = source.Length;
            int m = target.Length;
            int[,] d = new int[n + 1, m + 1]; // matrix
            int cost; // cost
            // Step 1
            if (n == 0) return m;
            if (m == 0) return n;
            for (int i = 0; i <= n; d[i, 0] = i++) ;
            for (int j = 0; j <= m; d[0, j] = j++) ;
            for (int i = 1; i <= n; i++)
            {
                for (int j = 1; j <= m; j++)
                {
                    cost = (target.Substring(j - 1, 1) == source.Substring(i - 1, 1) ? 0 : 1);
                    d[i, j] = System.Math.Min(System.Math.Min(d[i - 1, j] + 1, d[i, j - 1] + 1),
                              d[i - 1, j - 1] + cost);
                }
            }
            return d[n, m];
        }

    }
}

我已经对100个输入运行了一批样本，并将并行度设置为1，优先级设置为1000。该作业在1.6分钟内完成。

我想用1000个输入测试同样的作业，并将并行度设置为1，优先级设置为1000。根据我的计算，由于100个输入需要1.6分钟，我认为1000个输入需要大约20分钟，但是它运行了超过50分钟，我没有看到任何进展。

所以我添加了另一个100个输入的作业并测试运行，与之前的时间相同。因此，我考虑增加并行度并将其增加到3，再次运行，但即使经过1小时也无法完成。

JOB_ID = 07c0850d-0770-4430-a288-5cddcfc26699

主要问题是我无法看到任何进展或状态。

请告诉我是否有做错什么。

有没有办法在USQL中使用构造函数？如果我能够这样做，我就不需要一遍又一遍地执行相同的清理步骤。

2个回答
2
我假设您正在使用文件集语法来指定1000个文件？不幸的是，当前的文件集默认实现无法很好地扩展，编译（准备）阶段将需要很长时间（执行也是如此）。我们目前有一个更好的实现正在预览中。请发送电子邮件至usql at Microsoft dot com，我会告诉您如何尝试预览实现。

谢谢 Michael

嗨，Michael，这不是1000个文件，而是一个有数千个输入的文件。我会给你发邮件。感谢您的回复。 - The6thSense
0

我研究了一种更基于集合的方法来完成这个任务。比如，不是在代码后台文件中存储需要移除的单词，而是将它们存储在一个 U-SQL 表格中，这样方便添加：

CREATE TABLE IF NOT EXISTS dbo.wordsToRemove
(
    word string,

    INDEX cdx_wordsToRemvoe CLUSTERED (word ASC) 
    DISTRIBUTED BY HASH (word)
);

INSERT INTO dbo.wordsToRemove ( word )
SELECT word
FROM (
VALUES
    ( "l.p." ),
    ( "registered" ),
    ( "pc" ),
    ( "bldg" ),
    ( "pllc" ),
    ( "lp." ),
    ( "l.c." ),
    ( "div." ),
    ( "national" ),
    ( "l" ),
    ( "p" ),
    ( "l.l.c" ),
    ( "international" ),
    ( "r." ),
    ( "limited" ),
    ( "school" ),
    ( "azioni" ),
    ( "joint" ),
    ( "co-op" ),
    ( "corporation" ),
    ( "corp.," ),
    ( "(corp)" ),
    ( "inc.," ),
    ( "societa" ),
    ( "company" ),
    ( "llp" ),
    ( "liability" ),
    ( "l.l.l.p" ),
    ( "llc" ),
    ( "bancorporation" ),
    ( "manufacturing" ),
    ( "c" ),
    ( "dst" ),
    ( "(inc)" ),
    ( "jv" ),
    ( "ltd." ),
    ( "llc." ),
    ( "technology" ),
    ( "ltd.," ),
    ( "s.a." ),
    ( "mfg" ),
    ( "rllp" ),
    ( "incorporated" ),
    ( "per" ),
    ( "venture" ),
    ( "l.l.p" ),
    ( "c." ),
    ( "p.l.l.c" ),
    ( "l.p.." ),
    ( "p." ),
    ( "partnership" ),
    ( "corp" ),
    ( "co-operative" ),
    ( "s.p.a" ),
    ( "tech" ),
    ( "schl" ),
    ( "bancorp" ),
    ( "association" ),
    ( "lllp" ),
    ( "n" ),
    ( "r" ),
    ( "ltd" ),
    ( "inc." ),
    ( "l.l.p." ),
    ( "p.c." ),
    ( "co" ),
    ( "district" ),
    ( "int" ),
    ( "intl" ),
    ( "assn." ),
    ( "sa" ),
    ( "inc" ),
    ( "l.p" ),
    ( "co," ),
    ( "co." ),
    ( "division" ),
    ( "lc" ),
    ( "intl." ),
    ( "lp" ),
    ( "professional" ),
    ( "corp." ),
    ( "a" ),
    ( "l." ),
    ( "l.l.c." ),
    ( "building" ),
    ( "r.l.l.p" ),
    ( "co.," )
) AS words(word);

然后进行比较时，我将原始短语分割，删除我们不需要的单词，然后再将短语重新组合起来，就像这样：

//DECLARE @inputFile string = "input/input.csv"; // 500 companies, Standard & Poor 500 companies from wikipedia
DECLARE @inputFile string = "input/input2.csv"; // 850,000 companies, part 1 of extract from Companies House


@searchlog =
    EXTRACT id int,
            Input_CN string,
            Output_CN string
    FROM @inputFile
    USING Extractors.Csv(silent : true);
    //USING Extractors.Csv(skipFirstNRows:1);


// Split the input string to remove unwanted words
@Input_CN =
    SELECT id,
           new SQL.ARRAY<string>(Input_CN.Split(' ')) AS splitWords
    FROM @searchlog;


@Output_CN =
    SELECT id,
           new SQL.ARRAY<string>(Output_CN.Split(' ')) AS splitWords
    FROM @searchlog;


// Remove unwanted words from input string
@Input_CN =
    SELECT *
    FROM
    (
        SELECT o.id,
               x.splitWord.ToLower() AS splitWord
        FROM @Input_CN AS o
             CROSS APPLY
                 EXPLODE(splitWords) AS x(splitWord)
    ) AS y    
    ANTISEMIJOIN
        dbo.wordsToRemove AS w
    ON y.splitWord == w.word;

// Remove unwanted words from output string
@Output_CN =
    SELECT *
    FROM
    (
        SELECT o.id,
               x.splitWord.ToLower() AS splitWord
        FROM @Output_CN AS o
             CROSS APPLY
                 EXPLODE(splitWords) AS x(splitWord)
    ) AS y
    ANTISEMIJOIN
        dbo.wordsToRemove AS w
    ON y.splitWord == w.word;




// Put the input string back together again
@Input_CN =
    SELECT id,
           String.Join( " ", ARRAY_AGG (splitWord) ) AS Input_CN_Cleansed
    FROM @Input_CN
    GROUP BY id;


@Output_CN =
    SELECT id,
           String.Join( " ", ARRAY_AGG (splitWord) ) AS Output_CN_Cleansed
    FROM @Output_CN
    GROUP BY id;



@output =
    SELECT i.id,
           i.Input_CN_Cleansed,
           o.Output_CN_Cleansed,
           CN_Validator.trial.Hamming(i.Input_CN_Cleansed, o.Output_CN_Cleansed) AS HammingScore,
           CN_Validator.trial.LevinstienDistance(i.Input_CN_Cleansed, o.Output_CN_Cleansed) AS LevinstienDistance
    FROM @Input_CN AS i
         INNER JOIN
             @Output_CN AS o
         ON i.id == o.id;



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

我发现性能相似，但可能设计更易于维护。我的代码在不到几分钟的时间内运行了850k+记录，而不是50多分钟，所以可能还有其他问题。注意：我缺少FuzzyString库，因此没有在测试中包含它-这可能解释了差异。

如果您从Microsoft那里得到更新，请在此线程中回复，甚至可以将其标记为答案。

如果我解决了这个问题，我一定会在这里发布。感谢代码改进。由于不建议在SQL上进行规范化，我想在.NET端完成它，但是您的代码看起来易于维护，并且似乎正在充分利用USQL的全部功能。 - The6thSense
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接