public static void CreateTableMappingFromDefinition(string databaseName, string table,
string tableMappingName, KustoConnectionStringBuilder kustoConnectionStringBuilder,
IDictionary <string, string> tableDefinition)
{
using (var kustoClient = KustoClientFactory.CreateCslAdminProvider(kustoConnectionStringBuilder))
{
var columnMappings = new List <ColumnMapping>();
int cnt = 0;
foreach (var keyvaluepair in tableDefinition)
{
columnMappings.Add(new ColumnMapping()
{
ColumnName = keyvaluepair.Key,
Properties = new Dictionary <string, string>()
{
{ MappingConsts.Ordinal, cnt.ToString() }
}
});
cnt++;
}
var command =
CslCommandGenerator.GenerateTableMappingCreateCommand(
Kusto.Data.Ingestion.IngestionMappingKind.Csv,
table,
tableMappingName, columnMappings);
kustoClient.ExecuteControlCommand(databaseName, command);
}
}
public static void CreateTableMapping(string databaseName, string table, string tableMappingName,
KustoConnectionStringBuilder kustoConnectionStringBuilder)
{
using (var kustoClient = KustoClientFactory.CreateCslAdminProvider(kustoConnectionStringBuilder))
{
var command =
CslCommandGenerator.GenerateTableMappingCreateCommand(
Kusto.Data.Ingestion.IngestionMappingKind.Csv,
table,
tableMappingName,
new[] {
new ColumnMapping()
{
ColumnName = "id", Properties = new Dictionary <string, string>()
{
{ MappingConsts.Ordinal, "0" }
}
},
new ColumnMapping()
{
ColumnName = "date", Properties = new Dictionary <string, string>()
{
{ MappingConsts.Ordinal, "1" }
}
},
new ColumnMapping()
{
ColumnName = "time", Properties = new Dictionary <string, string>()
{
{ MappingConsts.Ordinal, "2" }
}
},
new ColumnMapping()
{
ColumnName = "sym", Properties = new Dictionary <string, string>()
{
{ MappingConsts.Ordinal, "3" }
}
},
new ColumnMapping()
{
ColumnName = "qty", Properties = new Dictionary <string, string>()
{
{ MappingConsts.Ordinal, "4" }
}
},
new ColumnMapping()
{
ColumnName = "px", Properties = new Dictionary <string, string>()
{
{ MappingConsts.Ordinal, "5" }
}
}
});
kustoClient.ExecuteControlCommand(databaseName, command);
}
}
/// <summary>
/// Populate the Kusto backend with test data.
/// </summary>
/// <param name="kusto"></param>
/// <param name="db"></param>
/// <param name="table"></param>
/// <param name="mapping"></param>
/// <param name="structure">JSON file containing the Elasticsearch index structure. Elasticsearch types will be converted to Kusto types. Note that the method only supported a small set of Elasticsearch types.</param>
/// <param name="dataFile">Gzipped JSON file containing the data to be loaded.</param>
/// <returns>Bulk Insert operation result.</returns>
public static async Task <IKustoIngestionResult> Populate(KustoConnectionStringBuilder kusto, string db, string table, string mapping, string structure, string dataFile)
{
var struc = JObject.Parse(structure);
var properties = struc["mappings"]["_doc"]["properties"] as JObject;
// Build list of columns and mappings to provision Kusto
var kustoColumns = new List <string>();
var columnMappings = new List <ColumnMapping>();
foreach (var prop in properties)
{
string name = prop.Key;
JObject value = prop.Value as JObject;
string type = (string)value["type"];
if (ES2KUSTOTYPE.ContainsKey(type))
{
type = ES2KUSTOTYPE[type];
}
kustoColumns.Add($"{name}:{type}");
columnMappings.Add(new ColumnMapping()
{
ColumnName = name,
Properties = new Dictionary <string, string>
{
["Path"] = $"$.{name}",
},
});
}
using (var kustoAdminClient = KustoClientFactory.CreateCslAdminProvider(kusto))
{
// Send drop table ifexists command to Kusto
var command = CslCommandGenerator.GenerateTableDropCommand(table, true);
kustoAdminClient.ExecuteControlCommand(command);
// Send create table command to Kusto
command = $".create table {table} ({string.Join(", ", kustoColumns)})";
Console.WriteLine(command);
kustoAdminClient.ExecuteControlCommand(command);
// Send create table mapping command to Kusto
command = CslCommandGenerator.GenerateTableMappingCreateCommand(IngestionMappingKind.Json, table, mapping, columnMappings);
kustoAdminClient.ExecuteControlCommand(command);
}
// Log information to console.
// Can't use Console.WriteLine here: https://github.com/nunit/nunit3-vs-adapter/issues/266
TestContext.Progress.WriteLine($"Ingesting {dataFile} as compressed data into Kusto");
// Populate Kusto
using Stream fs = File.OpenRead(dataFile);
return(await KustoIngest(kusto, db, table, mapping, fs));
}
/// <summary>
/// Check table for existense of JSON mapping and create one if necessary
/// </summary>
/// <param name="kcsb">KustoConnectionStringBuilder object configured to connect to the cluster</param>
/// <param name="databaseName">Name of the database</param>
/// <param name="tableName">Name of the table</param>
static void CreateJsonMappingIfNotExists(KustoConnectionStringBuilder kcsb, string databaseName, string tableName)
{
using (var adminClient = KustoClientFactory.CreateCslAdminProvider(kcsb))
{
var showMappingsCommand = CslCommandGenerator.GenerateTableJsonMappingsShowCommand(tableName);
var existingMappings = adminClient.ExecuteControlCommand <IngestionMappingShowCommandResult>(databaseName, showMappingsCommand);
if (existingMappings.FirstOrDefault(m => String.Equals(m.Name, s_jsonMappingName, StringComparison.Ordinal)) != null)
{
return;
}
var createMappingCommand = CslCommandGenerator.GenerateTableMappingCreateCommand(Kusto.Data.Ingestion.IngestionMappingKind.Json, tableName, s_jsonMappingName, s_jsonMapping);
adminClient.ExecuteControlCommand(databaseName, createMappingCommand);
}
}
public void CreateTableMapping(string tableMappingName, IEnumerable <ColumnMapping> mapping, string tableName, string databaseName)
{
try
{
var command = CslCommandGenerator.GenerateTableMappingCreateCommand(
Kusto.Data.Ingestion.IngestionMappingKind.Json,
tableName,
tableMappingName,
mapping);
this.client.ExecuteControlCommand(databaseName, command);
}
catch (Exception e)
{
throw e;
}
}
private static void PopulateTypesIndexData()
{
// Build list of columns and mappings to provision Kusto
var kustoColumns = new List <string>();
var columnMappings = new List <ColumnMapping>();
foreach (var entry in KustoColumnType)
{
var name = entry.Key;
kustoColumns.Add($"{name}:{entry.Value}");
columnMappings.Add(new ColumnMapping()
{
ColumnName = name,
Properties = new Dictionary <string, string>
{
["Path"] = $"$.{name}",
},
});
}
// Send drop table ifexists command to Kusto
var command = CslCommandGenerator.GenerateTableDropCommand(TypesIndex, true);
KustoExecute(command);
// Send create table command to Kusto
command = $".create table {TypesIndex} ({string.Join(", ", kustoColumns)})";
Console.WriteLine(command);
KustoExecute(command);
// Send create table mapping command to Kusto
command = CslCommandGenerator.GenerateTableMappingCreateCommand(IngestionMappingKind.Json, TypesIndex, TypesMapping, columnMappings, true);
KustoExecute(command);
command = ".append types_index <|" +
"print x = true, datetime('2020-02-23T07:22:29.1990163Z'), guid(74be27de-1e4e-49d9-b579-fe0b331d3642), int(17), long(17), real(0.3), 'string type', 30m, decimal(0.3), dynamic({'a':123, 'b':'hello'})";
KustoExecute(command);
}