主页 / dba / 问题 / 158883
Accepted
RK Kuppala
Asked: 2016-12-22 21:56:49 +0800 CST

Azure SQL 仓库 - 数据摄取 - 将巨大的固定宽度（带逗号）文件转换为分隔文件

  • 772

我什至不确定我是否正确地提出了这个问题，但我会尝试 - 我有一堆从 Linux 系统上的 Oracle 导出生成的巨大文本文件。每个文件大小约为 30 GB，我有大约 50 个。

目标是将此数据导出到 Azure SQL 数据仓库。在这种情况下，考虑到数据的大小，BCP 不是正确的方法，所以我不得不使用 Polybase。

从 ASCII 转换为 UTF8 编码后，我在查询外部表时遇到了问题。Polybase 不能很好地处理固定宽度的文本文件，每行都有换行符。

文本文件如下所示：

101,102,103,104,105,106,107
108,108,109,110,111,112,113
114,115,116,117,118,119,120
121,122,123

--这里什么都没有，只有一个空行

201,202,203,204,205,206,207
208,209,210,211,212,213,214
215,216,217

Polybase 尝试处理从 101 到 107 的错误，并抱怨此文件中没有足够的列来处理。

这是我认为正在发生的事情：固定宽度和换行符使其将换行符视为行分隔符。

如何将此文件转换为如下所示：

101,102,103,104,105,106,107,108,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,123{CR}{LF}
201,202,203,204,205,206,207,208,209,210,211,212,213,214,215,216,217{CR}{LF}

编辑：这是来自文件的示例数据。我在 Windows VM 上的 git bash 中打开它。

这些文件应该有 167 列,作为列分隔符。问题是，由于每一行产生多行，因此很难从 Polybase 外部表中处理它们。

import polybase

3 个回答

  1. Polybase 功能强大，但并不那么复杂，因此无法处理这种奇怪的格式。在我看来，您有三个选择：

    1. 更正源文件格式。不要使用固定宽度和分隔文件格式的奇怪组合，而是使用标准文件格式，例如 .csv。这种格式的列分隔符是回车符或逗号，我认为这真的很奇怪。有什么工具可以轻松阅读吗？这是您工作的常见格式吗？
    2. 将指定其中一个分隔符的文件导入一行，然后根据另一个分隔符将其分解。我开始用你的样本数据尝试这个，但没有走得很远。不同的行真的有不同的列数吗？在您的示例数据中，第 1 行有 24 列，第 2 行有 17 列。请提供一个小样本文件，例如通过gist准确表示您的数据。
    3. 编写一个高度定制的导入例程。使用标准数据交换格式（如 csv、制表符分隔、管道分隔、XML、JSON 等）的要点是，您不必在每次要导入某些数据时都编写高度自定义的例程。但是，如果您无法从源头更改文件或分阶段导入文件，这可能是一种选择。我最近一直在使用 Azure Data Lake Analytics (ADLA) 和 U-SQL，这也许可以做到这一点。

    请尝试回答我上面的问题并提供示例文件，我会尽力提供帮助。

    • 6

  2. 根据十六进制编辑器，您的示例文件具有用于某些行结尾的单换行符 (0A) 和两个换行符作为行之间的分隔符：

    示例文件的十六进制视图

    U-SQL 自定义提取器可能能够处理此文件，但我想知道我们是否会遇到完整的 30GB 文件的问题。

    指示

    1. 如果您还没有Azure Data Lake Analytics (ADLA) 帐户，请设置一个。
    2. 在 Visual Studio 中创建一个新的 U-SQL 项目 - 您将需要ADLA 工具。

    3. 添加 U-SQL 脚本并将以下文本添加到 U-SQL 代码隐藏文件中：

      using System.Collections.Generic;
using System.IO;
using System.Text;
using Microsoft.Analytics.Interfaces;

namespace Utilities
{
[SqlUserDefinedExtractor(AtomicFileProcessing = true)]
public class MyExtractor : IExtractor
{
    //Contains the row
    private readonly Encoding _encoding;
    private readonly byte[] _row_delim;
    private readonly char _col_delim;

    public MyExtractor()
    {
        _encoding = Encoding.UTF8;
        _row_delim = _encoding.GetBytes("\n\n");
        _col_delim = '|';
    }

    public override IEnumerable<IRow> Extract(IUnstructuredReader input, IUpdatableRow output)
    {
        string s = string.Empty;
        string x = string.Empty;

        foreach (var current in input.Split(_row_delim))
        {
            using (System.IO.StreamReader streamReader = new StreamReader(current, this._encoding))
            {
                while ((s = streamReader.ReadLine()) != null)
                {
                    //Strip any line feeds
                    //s = s.Replace("/n", "");

                     // Concatenate the lines
                    x += s;
                }

                //Create the output
                output.Set<string>(0, x);
                yield return output.AsReadOnly();

                // Reset
                x = string.Empty;

            }
        }
    }
}
}

    4. 使用自定义提取器处理文件：

      @input =
EXTRACT col string
FROM "/input/input42_2.txt"
USING new Utilities.MyExtractor();


// Output the file
OUTPUT @input
TO "/output/output.txt"
USING Outputters.Tsv(quoting : false);

    这产生了一个清理文件，我可以使用 Polybase 导入它：

    多基结果

    祝你好运！

    • 3
  3. Best Answer

    我最终使用 sed 来清理文件

    zcat myfile.txt.gz | sed -r 's/[ ]+/vin/g'|tr -d '\n'|tr 'vinvin' '\n'|grep -v '^$' > myfile.txt

    这解决了源文件的格式问题。将这些文件上传到 Azure blob 存储后，剩下的就很简单了。我通过 Polybase 创建了指向 blob 上文件的外部表，然后使用CREATE TABLE dbo.internal AS SELECT * FROM blob.external. 具有 700 DWH 容量的 Azure DWH 实例能够在 5 分钟内从外部表加载大约 5000 万行。

    • 2

相关问题