This blog post has also been published in English.
Sie haben vielleicht unseren Beitrag deriving metadata from SQL Server into BimlStudio gesehen, können die Funktionalität jedoch nicht nutzen, da Sie mit BimlExpress statt BimlStudio arbeiten? Es ist korrekt, dass BimlExpress das Biml interne Metadaten Modell nicht unterstützt, es gibt jedoch ähnliche Wege zur Automatisierung von Metadaten in Biml.
Dieser Beitrag zeigt Ihnen, wie Sie ein in SQL Server erstelltes Metadaten-Modell einschliesslich Beziehungen usw. nach Biml importieren und dort sehr flexibel nutzen können.
Um zu starten legen wir zunächst ein Beispielmodell inklusive entsprechender Testdaten an. Die erforderlichen Scripts können Sie entweder unter https://www.solisyon.de/files/SetupDerivedMetadata.sql herunterladen oder Sie scrollen zum Ende dieser Seite. Auch wenn Ihr eigenes Modell sicherlich hiervon abweichen wird, empfehlen wir Ihnen zunächst mit unserem Beispiel zu starten.
Die meiste Arbeit wird von einer Datei namens INC_MetaData.biml erledigt, welche wir in den anderen Biml Dateien folglich teilweise referenzieren werden.
Im ersten Schritt liest es per ImportDB das Schema “meta” in ein Dataset ein, welches ebenfalls Meta heisst. Hierbei werden sowohl die Strukturen einschliesslich Relationen als auch die eigentlichen Daten eingelesen.
Das erlaubt uns durch das Modell zu navigieren indem wir zum Zugriff auf die Connections-Tabelle beispielsweise einfach meta(“connections”) aufrufen.
Zusätzlich stellt die Datei zwei Funktionen bereit:
1. GetChildList
Diese Funktion nimmt als Parameter ein DataRow-Array sowie einen Spaltennamen an. Als Resultat gibt es diese Spalte aus dem gesamten Array als Liste zurück. Sollte das Array leer sein fügt sie noch einen Dummy-Eintrag hinzu. Somit können wir mit dieser Funktion zum Beispiel alle Tabellen aus unseren Metadaten als Liste an eine Funktion wie GetDatabaseSchema durchreichen.
2. GetAnnotations
Diese Funktion erstellt für jede Spalte einer DataRow eine Annotation in Biml. Somit kann durch diesen Funktionsaufruf das komplette Modell von SQL nach Biml transformiert werden. Würden wir zum Beispiel in der Tabelle meta.Tables eine neue Spalte “WHERE_String” einfügen, so wäre diese sofort auch als Annotation in unserem Biml Code enthalten, ohne hieran irgendwelche Änderung vornehmen zu müssen.
<#@ template language="VB" optionexplicit="false"#>
<#@ import namespace="System.Data" #>
<# connMetadata = "Data Source=.;Initial Catalog=BimlDemo_DerivedMetadata;Provider=SQLNCLI11;Integrated Security=SSPI;"
Dim MetaConnection = SchemaManager.CreateConnectionNode("Meta", connMetadata)
Dim MetaSchema = MetaConnection.ImportDB("meta",nothing,nothing)
Dim Meta as new DataSet
for each tbl in MetaSchema.Tablenodes
DT = ExternalDataAccess.GetDataTable(connMetaData,"select * from " & tbl.schemaqualifiedname)
DT.TableName = tbl.name
meta.tables.add(DT)
next
for each tbl in MetaSchema.Tablenodes
for each fk in tbl.Columns.OfType(Of AstTableColumnTableReferenceNode)
PK = MetaSchema.TableNodes.Where(function(c) c.Name = fk.foreigntable.Name).First.Columns.Where(Function(c) c.IsUsedInPrimaryKey = true).First.Name
meta.relations.add(fk.foreigntable.Name & "_" & tbl.Name, _
meta.Tables(fk.foreigntable.Name).Columns(PK), _
meta.Tables(tbl.name).Columns(fk.Name))
next
next
#>
<#+ Public Function GetChildList (RS as DataRow(),Col as String) as List(of String)
Dim LST as new List(of String)
For each rec in RS
LST.Add(rec(Col))
next
If LST.count = 0 Then LST.add ("NONEMPTYSPACER")
Return LST
end function
Function GetAnnotations(tbl as DataRow) as String
Dim annostring as String
for each col in tbl.Table.Columns
annostring = annostring + "<Annotation AnnotationType=""Tag"" Tag=""" + col.caption + """>" + tbl(col) +"</Annotation>" + vbcrlf
next
return annostring
end function
#>
Wie bereits erwähnt findet die eigentliche Importlogik der Metadaten innerhalb der Datein INC_MetaData.biml statt. Um daraus etwas sinnvolles zu machen müssen wir nun basierend auf dem Modell noch etwas Biml Code erstellen.
Klassischerweise starten Biml Projekte mit der Definition von Verbindungen, Datenbanken und Schemas. Nachdem wir all das bereits in unserem Modell haben können wir die entsprechenden Biml Daten rein durch loopen über die Metadaten erstellen und befüllen.
Für die Verbindungen geben wir auch noch alle Spalten des Metadaten-Modells als Annotation aus:
<#@ template language="VB" optionexplicit="false" tier="10" #>
<#@ include file="INC_Metadata.biml"#>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<Connections>
<# For Each conn In Meta.Tables("Connections").Rows #>
<OleDbConnection Name="<#= Conn("Name") #>" ConnectionString="<#= conn("ConnectionString")#>">
<Annotations>
<#= GetAnnotations(Conn) #>
</Annotations>
</OleDbConnection>
<# next #>
</Connections>
<Databases>
<# For Each db In Meta.Tables("Databases").Rows #>
<Database Name="<#= db("DB")#>" ConnectionName="<#= db("Connection") #>"></Database>
<# next #>
</Databases>
<Schemas>
<# For Each schem In Meta.Tables("Schemas").Rows #>
<Schema Name="<#= schem("Schema")#>" DatabaseName="<#= schem("DB") #>"></Schema>
<# next #>
</Schemas>
</Biml>
Jetzt, wo wir unsere Connections angelegt haben können wir wiederum für jede Verbindung die zugehörigen Tabellen ermitteln (da wir im SQL Server mit Fremdschlüsseln gearbeitet haben können wir die Funktion GetChildRows nutzen) und diese als Liste per GetDatabaseSchema an die jeweilige Verbindung als Filterkriterium weiterleiten.
Für jede Tabelle laden wir uns auch noch die zusätzlichen Metadaten aus dem meta-Dataset und legen diese im Objekt Metatbl ab.
Je nachdem ob eine Tabelle zugehörige Einträge in der Tabelle meta.Columns besitzt fügen wir entweder nur die dort definierten oder alle Spalten der Tabelle zu unserer RootNode hinzu.
Als letztes fügen wir auch hier alle Werte aus dem Metadaten-Modell als Annotation hinzu:
<#@ template language="VB" optionexplicit="false" tier="20" #>
<#@ include file="INC_Metadata.biml"#>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<Tables>
<# For Each conn In Meta.Tables("Connections").Select("type = 'Source'")
Dim SRCConn as AstDbConnectionNode = RootNode.Connections(conn("Name"))
tbls = GetChildList(conn.GetChildRows("Connections_Tables"),"TableName")
Dim importResult = SRCConn.GetDatabaseSchema(nothing,tbls,nothing)
for each tbl in ImportResult.Tablenodes
Metatbl = Meta.Tables("Tables").Select("Connection = '" & conn("name") & "' and schemaname = '" & tbl.schema.name & "' and tablename = '" & tbl.name & "'").First #>
<Table Name="<#= Metatbl("ID")#>" SchemaName="<#= Metatbl("TargetSchema")#>">
<Columns>
<#if Metatbl.GetChildRows("Tables_Columns").Length = 0 then #>
<#= tbl.Columns.GetBiml()#>
<# else #>
<#= tbl.Columns.Where(Function(c) GetChildList(Metatbl.GetChildRows("Tables_Columns"),"ColumnName").ConvertAll(function(u) u.ToUpper()).IndexOf(c.Name.ToUpper) <> -1).GetBiml()#>
<# end if#>
</Columns>
<Annotations>
<#= GetAnnotations(Metatbl) #>
</Annotations>
</Table>
<# next
next #>
</Tables>
</Biml>
Diese drei Dateien erstellen uns somit eine RootNode welche aus Verbindungen, Datenbanken, Schemata sowie Tabellen bestehen. Ab jetzt ist es also “Biml as usual” – wir können durch die RootNode loopen um darauf basierend die Ziel-Schemata und Tabellen in der Zieldatenbank anzulegen:
<#@ template language="VB" optionexplicit="false" tier="30" #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<Packages>
<Package Name="01_Create Staging" ConstraintMode="Linear" ProtectionLevel="EncryptSensitiveWithUserKey">
<Tasks>
<# for each Schem in RootNode.Schemas #>
<ExecuteSQL Name="Create Schema <#= Schem.Connection.Name#>_<#=Schem.Name#>" ConnectionName="<#= RootNode.Connections.Where(function(c) c.GetTag("Type") = "Target").First.Name #>">
<DirectInput>
<#=Schem.GetDropAndCreateDdl()#>
IF NOT EXISTS (SELECT schema_name FROM information_schema.schemata WHERE schema_name = '<#= Schem.name #>')
BEGIN
EXEC sp_executesql N'CREATE SCHEMA [<#= Schem.name #>]'
END
GO
</DirectInput>
</ExecuteSQL>
<# next #>
<# for each tbl in RootNode.Tables #>
<ExecuteSQL Name="Create Table <#=tbl.Name#>" ConnectionName="<#= RootNode.Connections.Where(function(c) c.GetTag("Type") = "Target").First.Name #>">
<DirectInput>
<#=tbl.GetDropAndCreateDdl()#>
</DirectInput>
</ExecuteSQL>
<# next #>
</Tasks>
</Package>
</Packages>
</Biml>
Und nachdem wir die Zielumgebung erstellt haben können wir sie schlussendlich auch noch befüllen:
<#@ template tier="40" optionexplicit="false" language="VB" #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<Packages>
<Package Name="02_Populate Tables" ConstraintMode="Parallel" ProtectionLevel="EncryptSensitiveWithUserKey">
<Tasks>
<# for each tbl in RootNode.Tables #>
<Container Name="Transfer <#=tbl.Schema.Name#>_<#=tbl.Name#>" ConstraintMode="Linear">
<Tasks>
<ExecuteSQL Name="Truncate" ConnectionName="<#= RootNode.Connections.Where(function(c) c.GetTag("Type") = "Target").First.Name #>">
<DirectInput>truncate table <#=tbl.ScopedName#></DirectInput>
</ExecuteSQL>
<Dataflow Name="Copy <#=tbl.Schema.Name#>_<#=tbl.Name#>">
<Transformations>
<OleDbSource Name="Get Rows" ConnectionName="<#= tbl.GetTag("Connection") #>">
<DirectInput>SELECT <#= tbl.GetColumnList() #> FROM [<#=tbl.GetTag("SchemaName")#>].[<#=tbl.GetTag("TableName")#>]</DirectInput>
</OleDbSource>
<OleDbDestination Name="Set Rows" ConnectionName="<#= RootNode.Connections.Where(function(c) c.GetTag("Type") = "Target").First.Name #>">
<TableOutput TableName="<#=tbl.ScopedName#>" />
</OleDbDestination>
</Transformations>
</Dataflow>
</Tasks>
</Container>
<# next #>
</Tasks>
</Package>
</Packages>
</Biml>
Der nächste logische Schritt wäre nun die Erweiterung unseres Modells um beispielsweise andere Datenquellen als OLEDB oder andere Load Patterns als TRUNCATE/LOAD zu unterstützen. Durch die recht universelle Technik beim einlesen der Metadaten sollte diese jedoch sehr wenig Anpassungen an unserem Kern-Code erfordern.
Haben Sie hierzu Fragen oder Anmerkungen? Wir freuen uns auf Ihren Input unter !
Weitere Informationen zu Biml, einschließlich Terminen und Blog Beiträgen finden Sie auch auf unserer Biml Seite.
Viel Spaß beim Biml’n!
PS: Hier wie versprochen noch der T-SQL Code zum Aufbau des Beispielmodells inklusive der Beispieldaten:
USE master GO CREATE DATABASE BimlDemo_DerivedMetadata GO USE BimlDemo_DerivedMetadata GO CREATE SCHEMA meta GO CREATE TABLE [meta].[Connections]( [Name] [nvarchar](50) NOT NULL, [ConnectionString] [nvarchar](500) NULL, [Type] [nvarchar](50) NULL, CONSTRAINT [PKC_Connection] PRIMARY KEY CLUSTERED ( [Name] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO CREATE TABLE [meta].[Databases]( [Connection] [nvarchar](50) NOT NULL, [DB] [nvarchar](50) NOT NULL, CONSTRAINT [PKC_Database] PRIMARY KEY CLUSTERED ( [DB] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [meta].[Databases] WITH CHECK ADD CONSTRAINT [FK_Databases_REF_connection] FOREIGN KEY([Connection]) REFERENCES [meta].[Connections] ([Name]) ON DELETE CASCADE GO ALTER TABLE [meta].[Databases] CHECK CONSTRAINT [FK_Databases_REF_connection] GO CREATE TABLE [meta].[Schemas]( [ID] AS (([DB]+'.')+[Schema]) PERSISTED NOT NULL, [DB] [nvarchar](50) NOT NULL, [Schema] [nvarchar](50) NOT NULL, CONSTRAINT [PKC_Schema] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [meta].[Schemas] WITH CHECK ADD CONSTRAINT [FK_schema_REF_Database] FOREIGN KEY([DB]) REFERENCES [meta].[Databases] ([DB]) ON DELETE CASCADE GO CREATE TABLE [meta].[Tables]( [ID] AS ((([Connection]+'_')+([SchemaName]+'_'))+[Tablename]) PERSISTED NOT NULL, [Connection] [nvarchar](50) NOT NULL, [SchemaName] [nvarchar](50) NOT NULL, [TableName] [nvarchar](100) NOT NULL, [TargetSchema] [nvarchar](101) NOT NULL, CONSTRAINT [PKC_Table] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [meta].[Tables] ADD CONSTRAINT [DF_Tables_TargetSchema] DEFAULT (N'BimlDemo_DerivedMetadata.Stage') FOR [TargetSchema] GO ALTER TABLE [meta].[Tables] WITH CHECK ADD CONSTRAINT [FK_tables_REF_Connections] FOREIGN KEY([Connection]) REFERENCES [meta].[Connections] ([Name]) ON DELETE CASCADE GO ALTER TABLE [meta].[Tables] CHECK CONSTRAINT [FK_tables_REF_Connections] GO ALTER TABLE [meta].[Tables] WITH CHECK ADD CONSTRAINT [FK_tables_REF_Schemas] FOREIGN KEY([TargetSchema]) REFERENCES [meta].[Schemas] ([ID]) GO ALTER TABLE [meta].[Tables] CHECK CONSTRAINT [FK_tables_REF_Schemas] GO CREATE TABLE [meta].[Columns]( [ID] AS (([TableID]+'_')+[ColumnName]) PERSISTED NOT NULL, [TableID] [nvarchar](202) NOT NULL, [ColumnName] [nvarchar](50) NOT NULL, CONSTRAINT [PKC_Column] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [meta].[Columns] WITH CHECK ADD CONSTRAINT [FK_columns_REF_tables] FOREIGN KEY([TableID]) REFERENCES [meta].[Tables] ([ID]) ON DELETE CASCADE GO ALTER TABLE [meta].[Columns] CHECK CONSTRAINT [FK_columns_REF_tables] GO
INSERT INTO [meta].[Connections] ( [Name]
,[ConnectionString]
,[Type])
VALUES ('AW','Data Source=.;Initial Catalog=AdventureWorks2014;Provider=SQLNCLI11;Integrated Security=SSPI;','Source')
INSERT INTO [meta].[Connections] ( [Name]
,[ConnectionString]
,[Type])
VALUES ('Stage','Data Source=.;Initial Catalog=BimlDemo_DerivedMetadata;Provider=SQLNCLI11;Integrated Security=SSPI;','Target')
INSERT INTO [meta].[Databases]
([Connection]
,[DB])
VALUES
('Stage'
,'BimlDemo_DerivedMetadata')
INSERT INTO [meta].[Schemas]
([DB]
,[Schema])
VALUES
('BimlDemo_DerivedMetadata'
,'stage')
INSERT INTO [meta].[Tables]
([Connection]
,[SchemaName]
,[TableName])
VALUES
('AW'
,'Person'
,'Person')
INSERT INTO [meta].[Tables]
([Connection]
,[SchemaName]
,[TableName])
VALUES
('AW'
,'Person'
,'PersonPhone')
INSERT INTO [meta].[Columns]
([TableID]
,[ColumnName])
VALUES
('AW_Person_PersonPhone'
,'PhoneNumber')