/// <summary>
/// Generates a Kusto table mapping for a specific <see cref="Type"/>, by mapping it's properties to column mappings.
/// </summary>
/// <param name="client">The <see cref="ICslAdminProvider"/> client that we are extending.</param>
/// <param name="type">The <see cref="Type"/> that we are generating the JSON mapping for.</param>
/// <returns>The name of the mapping created.</returns>
public static string GenerateTableJsonMappingFromType(this ICslAdminProvider client, Type type)
{
var tableName = type.Name;
var mappingName = $"{tableName}_mapping";
var command = CslCommandGenerator.GenerateTableJsonMappingShowCommand(tableName, mappingName);
try
{
client.ExecuteControlCommand(command);
return(mappingName);
}
catch (KustoBadRequestException ex) when(ex.ErrorMessage.Contains("'JsonMappingPersistent' was not found"))
{
// soak
}
var mappings = type.GetProperties().Select(property => new JsonColumnMapping {
ColumnName = property.Name, JsonPath = $"$.{property.Name}"
}).ToList();
command = CslCommandGenerator.GenerateTableJsonMappingCreateCommand(tableName, mappingName, mappings);
client.ExecuteControlCommand(command);
return(mappingName);
}
/// <summary>
/// Generates a Kusto table mapping for a specific <see cref="Type"/>, by mapping it's properties to column mappings.
/// </summary>
/// <param name="client">The <see cref="ICslAdminProvider"/> client that we are extending.</param>
/// <param name="type">The <see cref="Type"/> that we are generating the JSON mapping for.</param>
/// <returns>The name of the mapping created.</returns>
public static async Task <string> GenerateTableJsonMappingFromType(this ICslAdminProvider client, Type type)
{
var tableName = type.Name;
var mappingName = $"{tableName}_mapping";
var tableMappings = new List <string>();
var command = CslCommandGenerator.GenerateTableJsonMappingsShowCommand(tableName);
var reader = await client.ExecuteControlCommandAsync(client.DefaultDatabaseName, command);
while (reader.Read())
{
tableMappings.Add(reader.GetString(0));
}
if (tableMappings.Contains(mappingName))
{
return(mappingName);
}
var mappings = type.GetProperties().Select(property => new JsonColumnMapping {
ColumnName = property.Name, JsonPath = $"$.{property.Name}"
}).ToList();
command = CslCommandGenerator.GenerateTableJsonMappingCreateCommand(tableName, mappingName, mappings);
await client.ExecuteControlCommandAsync(client.DefaultDatabaseName, command);
return(mappingName);
}
private static void PopulateTypesIndexData()
{
// Build list of columns and mappings to provision Kusto
var kustoColumns = new List <string>();
var columnMappings = new List <JsonColumnMapping>();
foreach (var entry in KustoColumnType)
{
var name = entry.Key;
kustoColumns.Add($"{name}:{entry.Value}");
columnMappings.Add(new JsonColumnMapping()
{
ColumnName = name, JsonPath = $"$.{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.GenerateTableJsonMappingCreateCommand(
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);
}
public void CreateTableIfNotExists(string table, string mappingName)
{
try
{
using (var kustoAdminClient = KustoClientFactory.CreateCslAdminProvider(_kustoConnectionStringBuilder))
{
// check if already exists.
var showTableCommands = CslCommandGenerator.GenerateTablesShowDetailsCommand();
var existingTables = kustoAdminClient.ExecuteControlCommand <IngestionMappingShowCommandResult>(DatabaseName, showTableCommands).Select(x => x.Name).ToList();
if (existingTables.Contains(table))
{
Logger.Info($"Table {table} already exists");
return;
}
// Create Columns
var command = CslCommandGenerator.GenerateTableCreateCommand(table, GetColumns());
kustoAdminClient.ExecuteControlCommand(databaseName: DatabaseName, command: command);
// Create Mapping
command = CslCommandGenerator.GenerateTableJsonMappingCreateCommand(
table, mappingName, GetJsonColumnMappings());
kustoAdminClient.ExecuteControlCommand(databaseName: DatabaseName, command: command);
}
}
catch (Exception ex)
{
Logger.Error($"Cannot create table due to {ex}");
}
}
/// <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 <JsonColumnMapping>();
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 JsonColumnMapping()
{
ColumnName = name, JsonPath = $"$.{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.GenerateTableJsonMappingCreateCommand(
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.GenerateTableJsonMappingCreateCommand(tableName, s_jsonMappingName, s_jsonMapping);
adminClient.ExecuteControlCommand(databaseName, createMappingCommand);
}
}
static void Main(string[] args)
{
var clusterName = "KustoLab";
var db = "KustoIngestClientDemo";
var table = "Table1";
var mappingName = "Table1_mapping_1";
var serviceNameAndRegion = "clusterNameAndRegion"; // For example, "mycluster.westus"
var authority = "AAD Tenant or name"; // For example, "microsoft.com"
// Set up table
var kcsbEngine =
new KustoConnectionStringBuilder($"https://{serviceNameAndRegion}.kusto.windows.net").WithAadUserPromptAuthentication(authority: $"{authority}");
using (var kustoAdminClient = KustoClientFactory.CreateCslAdminProvider(kcsbEngine))
{
var columns = new List <Tuple <string, string> >()
{
new Tuple <string, string>("Column1", "System.Int64"),
new Tuple <string, string>("Column2", "System.DateTime"),
new Tuple <string, string>("Column3", "System.String"),
};
var command = CslCommandGenerator.GenerateTableCreateCommand(table, columns);
kustoAdminClient.ExecuteControlCommand(databaseName: db, command: command);
// Set up mapping
var columnMappings = new List <JsonColumnMapping>();
columnMappings.Add(new JsonColumnMapping()
{
ColumnName = "Column1", JsonPath = "$.Id"
});
columnMappings.Add(new JsonColumnMapping()
{
ColumnName = "Column2", JsonPath = "$.Timestamp"
});
columnMappings.Add(new JsonColumnMapping()
{
ColumnName = "Column3", JsonPath = "$.Message"
});
command = CslCommandGenerator.GenerateTableJsonMappingCreateCommand(
table, mappingName, columnMappings);
kustoAdminClient.ExecuteControlCommand(databaseName: db, command: command);
}
// Create Ingest Client
var kcsbDM =
new KustoConnectionStringBuilder($"https://ingest-{serviceNameAndRegion}.kusto.windows.net").WithAadUserPromptAuthentication(authority: $"{authority}");
using (var ingestClient = KustoIngestFactory.CreateQueuedIngestClient(kcsbDM))
{
var ingestProps = new KustoQueuedIngestionProperties(db, table);
// For the sake of getting both failure and success notifications we set this to IngestionReportLevel.FailuresAndSuccesses
// Usually the recommended level is IngestionReportLevel.FailuresOnly
ingestProps.ReportLevel = IngestionReportLevel.FailuresAndSuccesses;
ingestProps.ReportMethod = IngestionReportMethod.Queue;
// Setting FlushImmediately to 'true' overrides any aggregation preceding the ingestion.
// Not recommended unless you are certain you know what you are doing
ingestProps.FlushImmediately = true;
ingestProps.JSONMappingReference = mappingName;
ingestProps.Format = DataSourceFormat.json;
// Prepare data for ingestion
using (var memStream = new MemoryStream())
using (var writer = new StreamWriter(memStream))
{
for (int counter = 1; counter <= 10; ++counter)
{
writer.WriteLine(
"{{ \"Id\":\"{0}\", \"Timestamp\":\"{1}\", \"Message\":\"{2}\" }}",
counter, DateTime.UtcNow.AddSeconds(100 * counter),
$"This is a dummy message number {counter}");
}
writer.Flush();
memStream.Seek(0, SeekOrigin.Begin);
// Post ingestion message
var res = ingestClient.IngestFromStreamAsync(memStream, ingestProps, leaveOpen: true);
}
// Wait a bit (20s) and retrieve all notifications:
Thread.Sleep(20000);
var errors = ingestClient.GetAndDiscardTopIngestionFailures().GetAwaiter().GetResult();
var successes = ingestClient.GetAndDiscardTopIngestionSuccesses().GetAwaiter().GetResult();
errors.ForEach((f) => { Console.WriteLine($"Ingestion error: {f.Info.Details}"); });
successes.ForEach((s) => { Console.WriteLine($"Ingested: {s.Info.IngestionSourcePath}"); });
}
}