Azure SQL Warehouse - Ingestão de Dados - Converter um enorme arquivo de largura fixa (com vírgulas) para delimitado

O Polybase é poderoso, mas não tão sofisticado, então não será capaz de lidar com esse formato estranho. Você tem três opções a meu ver:

1. Corrija o formato do arquivo na origem. Em vez de ter uma mistura estranha de largura fixa e formato de arquivo delimitado, use um formato de arquivo padrão , como .csv. Esse formato que você tem onde o delimitador de coluna é retorno de carro ou vírgula, acho genuinamente estranho. Existem ferramentas que podem ler isso facilmente? Este é um formato comum onde você trabalha?
2. Importe o arquivo especificando um dos delimitadores em uma linha e, em seguida, fragmente-o com base no outro delimitador. Comecei a tentar isso com seus dados de amostra, mas não fui muito longe. Linhas diferentes realmente têm números diferentes de colunas? Em seus dados de amostra, a linha 1 tem 24 colunas e a linha 2 tem 17 colunas. Forneça um pequeno arquivo de amostra, por exemplo, via gist , que represente com precisão seus dados.
3. Escreva uma rotina de importação altamente personalizada. O ponto de usar formatos de intercâmbio de dados padrão como csv, delimitado por tabulação, delimitado por pipe, XML, JSON etc é que você não deve ter que escrever uma rotina altamente personalizada toda vez que quiser importar alguns dados. No entanto, isso pode ser uma opção, se você não puder alterar seu arquivo na origem ou importá-lo em etapas. Eu tenho trabalhado com Azure Data Lake Analytics (ADLA) e U-SQL recentemente, e isso pode ser feito.

Tente responder às minhas perguntas acima e forneça um arquivo de amostra e tentarei ajudar.

De acordo com um editor hexadecimal, seu arquivo de amostra possui alimentação de linha única (0A) para algumas terminações de linha e duas alimentações de linha como delimitador entre as linhas:

Um extrator personalizado U-SQL pode ser capaz de lidar com esse arquivo, mas me pergunto se teremos problemas com o arquivo completo de 30 GB.

Instruções

1. Configure uma conta do Azure Data Lake Analytics (ADLA) se ainda não tiver uma.
2. Crie um novo projeto U-SQL no Visual Studio - você precisará das ferramentas ADLA .

3. Adicione um script U-SQL e adicione o texto abaixo ao arquivo code-behind 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. Processe o arquivo usando o extrator personalizado:

   @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);
   

Isso produziu um arquivo de limpeza que pude importar usando o Polybase:

Boa sorte!

Acabei usando o sed para limpar o arquivo

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

Isso resolveu problemas de formatação com os arquivos de origem. Depois que esses arquivos foram carregados no armazenamento de blob do Azure, o resto foi fácil. Criei uma tabela externa apontando para arquivos no blob via Polybase e criei tabelas internas usando CREATE TABLE dbo.internal AS SELECT * FROM blob.external. Uma instância do Azure DWH com capacidade de 700 DWH conseguiu carregar cerca de 50 milhões de linhas em 5 minutos da tabela externa.