TMSL Xmla在现有数据库上创建表-表格模型脚本语言-Azure Analysis Services

Question

我的问题是：使用xmla文件和SSMS向现有数据库添加2个或多个表的正确语法是什么我有一个部署到Azure Analysis Services服务器的模型。所以数据库已经创建好了。我想通过在SSMS中运行一个xmla脚本来创建或替换表。

当我使用下面的脚本来创建一个表时，它非常有效。但我需要的是使用一个xmla脚本创建几个表(而不仅仅是一个(。

适用于一个表的脚本

下面的脚本可以完美而正确地"创建或替换"数据库中的一个表。

{
"createOrReplace": {
"object": {
"database": "MyDatabase",
"table": "MyTable"
},
"table": {
"name": "MyTable",
"columns": [
{
"name": "MyTableId",
"dataType": "int64",
"sourceColumn": "MyTableId"
},
{
"name": "MyTable",
"dataType": "string",
"sourceColumn": "MyTable"
}
],
"partitions": [
{
"name": "Partition",
"dataView": "full",
"source": {
"type": "m",
"expression": [
"let",
"    Source=GetFileList(),",
"    #"MyTable txt" = Source{[Name="MyTable.txt"]}[Content],",
"    #"Imported CSV" = Csv.Document(#"MyTable txt",[Delimiter=",", Columns=9, Encoding=1252, QuoteStyle=QuoteStyle.None]),",
"    #"Promoted Headers" = Table.PromoteHeaders(#"Imported CSV", [PromoteAllScalars=true]),",
"    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"MyTableId", Int64.Type}, {"MyTable", type text}, {"Description", type text}}),",
"    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Description"})",
"in",
"    #"Removed Columns""
]
}
}
]
}
}
}

我尝试使用下面的代码添加2个表，但出现错误JSON DDL请求失败，错误如下：无法识别的JSON属性：表。检查路径"表格"，第6行，位置16.

{   
"createOrReplace": {   
"database": {   
"name": "MyDatabase",   
"tables": [   
{"name": "TableA",
"columns": [
{
"name": "TableAId",
"dataType": "int64",
"sourceColumn": "TableAId"
},
{
"name": "TableA",
"dataType": "string",
"sourceColumn": "TableA"
}
],
"partitions": [
{
"name": "Partition",
"dataView": "full",
"source": {
"type": "m",
"expression": [
"let",
"    Source=GetFileList(),",
"    #"TableA txt" = Source{[Name="TableA.txt"]}[Content],",
"    #"Imported CSV" = Csv.Document(#"TableA txt",[Delimiter=",", Columns=9, Encoding=1252, QuoteStyle=QuoteStyle.None]),",
"    #"Promoted Headers" = Table.PromoteHeaders(#"Imported CSV", [PromoteAllScalars=true]),",
"    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"TableAId", Int64.Type}, {"TableA", type text}, {"Description", type text}}),",
"    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Description"})",
"in",
"    #"Removed Columns""
]
}
}
] },   
{"name": "TableB",
"columns": [
{
"name": "TableBId",
"dataType": "int64",
"sourceColumn": "TableBId"
},
{
"name": "TableB",
"dataType": "string",
"sourceColumn": "TableB"
}
],
"partitions": [
{
"name": "Partition",
"dataView": "full",
"source": {
"type": "m",
"expression": [
"let",
"    Source=GetFileList(),",
"    #"TableB txt" = Source{[Name="TableB.txt"]}[Content],",
"    #"Imported CSV" = Csv.Document(#"TableB txt",[Delimiter=",", Columns=11, Encoding=1252, QuoteStyle=QuoteStyle.None]),",
"    #"Promoted Headers" = Table.PromoteHeaders(#"Imported CSV", [PromoteAllScalars=true]),",
"    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"TableBId", Int64.Type}, {"TableB", type text}, {"Description", type text}}),",
"    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Description"})",
"in",
"    #"Removed Columns""
]
}
}
] } 
]      
}   
}   
}  

我也尝试了下面的代码，但得到了错误错误-1055784777:JSON DDL请求失败，并出现以下错误：无法识别的JSON属性：表。检查路径"createOrReplace.tables"，第6行，位置14。。JSON DDL请求失败，错误如下：无法识别的JSON属性：表。检查路径"createOrReplace.tables"，第6行，位置14。

{
"createOrReplace": {
"object": {
"database": "MyDatabase"
},
"tables": [
{
"name": "TableA",
"columns": [
{
"name": "TableAId",
"dataType": "int64",
"sourceColumn": "TableAId"
},
{
"name": "TableA",
"dataType": "string",
"sourceColumn": "TableA"
}
],
"partitions": [
{
"name": "Partition",
"dataView": "full",
"source": {
"type": "m",
"expression": [
"let",
"    Source=GetFileList(),",
"    #"TableA txt" = Source{[Name="TableA.txt"]}[Content],",
"    #"Imported CSV" = Csv.Document(#"TableA txt",[Delimiter=",", Columns=9, Encoding=1252, QuoteStyle=QuoteStyle.None]),",
"    #"Promoted Headers" = Table.PromoteHeaders(#"Imported CSV", [PromoteAllScalars=true]),",
"    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"TableAId", Int64.Type}, {"TableA", type text}, {"Description", type text}}),",
"    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Description"})",
"in",
"    #"Removed Columns""
]
}
}
]
},
{
"name": "TableB",
"columns": [
{
"name": "TableBId",
"dataType": "int64",
"sourceColumn": "TableBId"
},
{
"name": "TableB",
"dataType": "string",
"sourceColumn": "TableB"
}
],
"partitions": [
{
"name": "Partition",
"dataView": "full",
"source": {
"type": "m",
"expression": [
"let",
"    Source=GetFileList(),",
"    #"TableB txt" = Source{[Name="TableB.txt"]}[Content],",
"    #"Imported CSV" = Csv.Document(#"TableB txt",[Delimiter=",", Columns=11, Encoding=1252, QuoteStyle=QuoteStyle.None]),",
"    #"Promoted Headers" = Table.PromoteHeaders(#"Imported CSV", [PromoteAllScalars=true]),",
"    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"TableBId", Int64.Type}, {"TableB", type text}, {"Description", type text}}),",
"    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Description"})",
"in",
"    #"Removed Columns""
]
}
}
]
}
]
}
}

Answer 1

使用序列命令：

{
"sequence": {
"operations": [
{ CREATE TABLE 1},
{ CREATE TABLE 2}
]
}
}

您可以包含任意数量的操作。