SQL CLR – Flat File’ları okumak

Published on 20 February 2012 by in CLR, Genel T-SQL, Stored Procedure / Okunma: 2,849

0

Bu yazımda Flat File ‘ları (txt, csv), SQL CLR (SQL Common Language Runtime) kullanıp stored procedure ile birleştirerek nasıl okuyabileceğimizi anlatacağım.

İlk olarak aklınıza Flat File’ları zaten Import Data‘yı kullanarak ,Linked Server‘da provider ekleyerek veya OLE Automation SP’lerini kullanarak bir şekilde okuyabileceğiniz geliyordur. Fakat, bu üç çözümünde birbirinden farklı dezavantajları mevcut. Örneğin Import Data ile okumak istediğiniz dosyayı herzaman için bir tabloya import edip okumanız gerekiyor. Mağlesef anlık olarak dosya içerisinden sorgulamaya izin verilmiyor. Linked Server mantığında ise eklemek istediğiniz provider’ın library’sinin sunucunuzda kurulu olması ve bu library bilesenlerini kullanarak belirli kıstaslarda okumanıza hak tanınıyor. OLE Automation ‘da ise oncelikle OLE’yi server’ınızda enable edip kendi stored procedure’larını (sp_OACreate, sp_OADestroy, sp_OAGetProperty vb.) kullanarak dosyayı okuyabiliyorsunuz. Fakat bu yöntem çoğu DBA için SQL Server üzerinde alınan güvenlik protokollerinin üzerine çıkmak olduğundan çok tercih edilmez.

CLR gereği bu yazıda VB’ye de gireceğiz. Fakat çok detaylı olarak Visual Studio üzerinde adım-adım code anlatmak yazının genelinin dışına çıkacağından önemli noktalar üzerinde durup yazdığım code’u paylaşacağım.

İlk olarak bilmeyenler için SQL CLR, SQL Server üzerinde yazdığımız kompleks T-SQL code’ların işin içinden çıkılmaz hale geldiğinde devreye giren, SQL Server nesnelerini kullanarak basit bir şekilde .NET framework üzerinde (VB, C#) yazdığımız kurtarıcı DLL’ler dir. Bu teknolojiyi Microsoft bizimle SQL Server 2005 ile birlikte tanıştırdı. SQL CLR ‘ler genelde Trigger, Stored Procedure ve Function olarak oluşturulurlar.

Yukarıda bahsettiğimiz gibi Flat File’ları nasıl okuyacağız, hep birlikte adım-adım code’larımızı yazarak ilerleyelim. Ben, Visual Studio 2010 / SQL Server 2008 R2 kullanacağım.

İlk olarak Visual Studio’muzu açarak New Project diyoruz. Burada Installed Template kısmında Database – SQL Server node’una geliyor ve Visual Basic SQL CLR Database Project ‘i seçiyor ve solution name’imizi giriyoruz. Ben VB ile yazacağım. Yukarıdan SQL Server üzerinde kurulu olan .NET Framework verionunuzu seçmelisiniz.

 

Karşımıza gelen ekranda oluşturacağımız DLL’in deploy edileceği Database’i seçmemiz isteniyor. Bunun için Add New Reference diyip connection string bilgilerimizi dolduruyoruz.

 

Şuanda Solution’ımızı yaratmış olduk. Fonksiyon niteliğinde bir library hazırlayacağımızdan Solution ismimize sağ tıklayıp Add – User-Defined Function diyoruz.

 

Çok fazla detaya girmeden ilgili code’u olusturdugumuz fonksiyon sayfamıza kopyalıyoruz.

Option Strict On

Imports System
Imports System.Runtime.InteropServices
Imports Microsoft.SqlServer.Server

Partial Public Class UserDefinedFunctions

    <SqlFunction(DataAccess:=DataAccessKind.Read, _
                 FillRowMethodName:="FlatFileOku", _
                 TableDefinition:="Sonuc nvarchar(4000)")> _
    Public Shared Function DosyaOku(ByVal path As String) As IEnumerable
        Return IO.File.ReadAllLines(path)
    End Function

    Public Shared Sub FlatFileOku(Oku As Object, <Out()> ByRef Satir As String)
        Dim Deger = TryCast(Oku, String)
        Satir = Deger
    End Sub

End Class

 

Son olarak Solution ismimize sağ tıklayıp özelliklerine giriyoruz. Database sekmesindeki Permission Level’ı External olarak işaretliyor sırasıyla Save, Build ve Deploy diyip DLL’imizi SQL Server üzerinde register ediyoruz. Eğer deploy esnasında hata alırsanız bu muhtemelen Database’inizin güvenilirliğinin disable olduğu içindir. External DLL’e access verebilmek için aşağıdaki code ile Database güvenilirliğini enable konumuna getirmelisiniz.

ALTER DATABASE DatabaseIsmi SET TRUSTWORTHY ON

 

Deploy sonucundan Success aldıktan sonra SQL Server üzerinden devam edebiliriz. Artık Visual Studio ile işimiz kalmadı. İlk olarak Database’imize deploy ettiğimiz external Function’ımızın Table-valued Function kısmında olup olmadığını check ediyoruz. Olduğundan emin olduktan sonra herhangi bir txt dosyamızı aşağıdaki gibi okuyup test ediyoruz.

SELECT * FROM [DB].[dbo].[DosyaOku] ('C:\FlatFile.txt')

 

İşin kolay kısmını hallettik. Asıl önemli olan ayraç ile bölünmüş değerleri ayrı kolonlarda bir tablo ya select çeker gibi output alabilmek. Bunun için T-SQL de bir SP hazırlayacağız. En mantıklısı dinamik pivot oluşturmak ve içerisinden DLL’imizi çağırıp dosyamızı okumak.

Öncelikle test için virgül ile ayrılmış bir test TXT dosyası yaratıyoruz.

a1,b1,c1,d1
a2,b2,c2,d2
a3,b3,c3,d3
a4,b4,c4,d4
a5,b5,c5,d5

 

Sonrasında dinamik pivot yazıyoruz. Dinamik olarak hazırlamamızın nedeni de pivot table’ı hazırlayabilmek için kolon isimlerimizi ve kaç adet olduğunu önceden bilmemiz gerektiğidir. Aşağıdaki SP’yi Database’imizde oluşturduktan sonra Flat File’ımızı SP miz ile okuyoruz.

CREATE PROCEDURE FlatFileOku (@Dosya NVARCHAR(MAX), @Ayrac NVARCHAR(2))
AS

SELECT [ID]   = ROW_NUMBER() OVER(PARTITION BY GETDATE() ORDER BY [Sonuc]),
       [Veri] = [Sonuc] INTO #Temp_Tablo FROM dbo.DosyaOku (@Dosya)

DECLARE @Pivot  NVARCHAR(MAX)
DECLARE @Select NVARCHAR(MAX)

SELECT @Pivot    = COALESCE(@Pivot + ',', '') + '[Column' + CAST(number + 1 AS VARCHAR(10)) + ']'
  FROM [master].[dbo].[spt_values]
 WHERE [type]    = 'p'
   AND [number] <= (SELECT MAX(LEN([Veri])-LEN(REPLACE([Veri],@Ayrac,''))) FROM #Temp_Tablo)

SELECT @Select='
              SELECT ' + @Pivot + '
        FROM (SELECT [ID]
                    ,[Gelen] = SUBSTRING([Veri], [strIlk] + 2, [strSon] - [strIlk] - 2)
                    ,[n]     = ''Column'' + CAST(ROW_NUMBER() OVER(PARTITION BY ID ORDER BY [strIlk]) AS VARCHAR(10))
        FROM (SELECT
                     [ID]
                    ,[Veri]
                    ,[strIlk] = [n]
                    ,[strSon]   = CHARINDEX('''+ @Ayrac + ''',[Veri], n + 2)
        FROM (SELECT [n] = [NUMBER]
                FROM [master].[dbo].[spt_values]
               WHERE [type] = ''p'') [num]
  CROSS JOIN (SELECT [ID]
                    ,[Veri] = '''+ @Ayrac + ''' + [Veri] +'''+ @Ayrac + '''
                FROM #Temp_Tablo
              ) [m]
       WHERE [n] < LEN([Veri]) - 1
         AND SUBSTRING([Veri], [n] + 1, 1) = '''+ @Ayrac + ''') AS [Veri]
              ) [pvt]
       PIVOT (MAX([Gelen])FOR [n] IN ('+@Pivot+')) [p]'
EXEC(@Select)

DROP TABLE #Temp_Tablo

 

EXEC FlatFileOku 'C:\FlatFile.txt', ','

 

 

Leave a Reply