private static string CreateADXTableFromDefinition(string databaseName, string table,
KustoConnectionStringBuilder kustoConnectionStringBuilder, IDictionary <string, string> tableDefinition)
{
var command = "";
var tuple = new Tuple <string, string> [tableDefinition.Count()];
int cnt = 0;
foreach (var keyvaluepair in tableDefinition)
{
tuple[cnt] = new Tuple <string, string>(keyvaluepair.Key, keyvaluepair.Value);
cnt++;
}
using (var kustoClient = KustoClientFactory.CreateCslAdminProvider(kustoConnectionStringBuilder))
{
command =
CslCommandGenerator.GenerateTableCreateCommand(
table, tuple);
var tablePolicyAlterCommand = CslCommandGenerator.GenerateTableAlterStreamingIngestionPolicyCommand(table, isEnabled: true);
kustoClient.ExecuteControlCommand(databaseName, command);
kustoClient.ExecuteControlCommand(databaseName, tablePolicyAlterCommand);
}
return(command);
}
/// <summary>
/// Generates a Kusto table for a specific <see cref="Type"/>, by mapping it's properties to columns.
/// </summary>
/// <param name="client">The <see cref="ICslAdminProvider"/> that we are extending.</param>
/// <param name="type">The <see cref="Type"/> that we are generating a table for.</param>
/// <returns>The name of the table created.</returns>
public static async Task <string> GenerateTableFromType(this ICslAdminProvider client, Type type)
{
var tableName = type.Name;
var tables = new List <string>();
var command = CslCommandGenerator.GenerateTablesShowCommand();
var reader = await client.ExecuteControlCommandAsync(client.DefaultDatabaseName, command);
while (reader.Read())
{
tables.Add(reader.GetString(0));
}
if (tables.Contains(tableName))
{
return(tableName);
}
var columns = type.GetProperties().Select(property => Tuple.Create(property.Name, property.PropertyType.FullName)).ToList();
command = CslCommandGenerator.GenerateTableCreateCommand(tableName, columns);
await client.ExecuteControlCommandAsync(client.DefaultDatabaseName, command);
return(tableName);
}
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}");
}
}
private static string CreateADXTable(string databaseName, string table,
KustoConnectionStringBuilder kustoConnectionStringBuilder)
{
var command = "";
using (var kustoClient = KustoClientFactory.CreateCslAdminProvider(kustoConnectionStringBuilder))
{
command =
CslCommandGenerator.GenerateTableCreateCommand(
table,
new[]
{
Tuple.Create("id", "System.Int32"),
Tuple.Create("date", "System.DateTime"),
Tuple.Create("time", "System.DateTime"),
Tuple.Create("sym", "System.String"),
Tuple.Create("qty", "System.Double"),
Tuple.Create("px", "System.Double")
});
kustoClient.ExecuteControlCommand(databaseName, command);
//if (!isBatch)
//{
// var tablePolicyAlterCommand =
// CslCommandGenerator.GenerateTableAlterStreamingIngestionPolicyCommand(table, isEnabled: true);
// kustoClient.ExecuteControlCommand(databaseName, tablePolicyAlterCommand);
//}
return(command);
// kustoClient.ExecuteControlCommand(databaseName, ".create table StreamingDataTable (['id']:int)");
}
}
public static string GenerateTableCreateCommand(KustoTableInfo kustoTable)
{
if (kustoTable == null)
{
throw new ArgumentNullException(nameof(kustoTable));
}
var columns = kustoTable.Columns
.Select(column => new Tuple <string, Type>(column.Value.Name, column.Value.Type));
return(CslCommandGenerator.GenerateTableCreateCommand(kustoTable.TableName, columns));
}
static void ResetTable(KustoConnectionStringBuilder kscb, string tableName, Type type)
{
using (var admin = KustoClientFactory.CreateCslAdminProvider(kscb))
{
string dropTable = CslCommandGenerator.GenerateTableDropCommand(tableName, true);
admin.ExecuteControlCommand(dropTable);
string createTable = CslCommandGenerator.GenerateTableCreateCommand(tableName, type);
admin.ExecuteControlCommand(createTable);
string enableIngestTime = CslCommandGenerator.GenerateIngestionTimePolicyAlterCommand(tableName, true);
admin.ExecuteControlCommand(enableIngestTime);
}
}
public void CreateTable(string tableName, IEnumerable <Tuple <string, string> > rowFields, string databaseName)
{
try
{
var command = CslCommandGenerator.GenerateTableCreateCommand(
tableName,
rowFields);
this.client.ExecuteControlCommand(databaseName, command);
}
catch (Exception e)
{
throw e;
}
}
/// <summary>
/// Wrap the call to CslCommandGenerator.GenerateTableCreateCommand and allow special formatting if the user
/// has enabled the setting flag for it. Also choose between "create" and "create merge" based on setting
/// </summary>
/// <param name="table">The table schema to convert to a string</param>
/// <param name="forceNormalizeColumnName">True to force the column names to be normalized/escaped</param>
/// <returns></returns>
public static string GenerateTableCreateCommand(TableSchema table, bool forceNormalizeColumnName = false)
{
string result = SettingsWrapper.CreateMergeEnabled == true
? CslCommandGenerator.GenerateTableCreateMergeCommandWithExtraProperties(table, forceNormalizeColumnName)
: CslCommandGenerator.GenerateTableCreateCommand(table, forceNormalizeColumnName);
if (SettingsWrapper.TableFieldsOnNewLine == true)
{
// Add a line break between each field
result = result.Replace(", ['", ",\r\n ['");
// Add a line break before the first field
int parameterStartIndex = result.LastIndexOf("([");
result = result.Insert(parameterStartIndex + 1, "\r\n ");
}
return(result);
}
/// <summary>
/// Write a table to the file system
/// </summary>
/// <param name="tableSchema">The table to write</param>
/// <param name="rootFolder">The root folder for all the CSL files</param>
public static void WriteToFile(this TableSchema tableSchema, string rootFolder)
{
string tableFolder = rootFolder;
if (!string.IsNullOrEmpty(tableSchema.Folder))
{
string cleanedFolder = string.Join("", tableSchema.Folder.Split(Path.GetInvalidPathChars()));
tableFolder = Path.Combine(rootFolder, "Tables", cleanedFolder);
}
string destinationFile = Path.Combine(tableFolder, tableSchema.Name + ".csl");
if (!Directory.Exists(tableFolder))
{
Directory.CreateDirectory(tableFolder);
}
File.WriteAllText(destinationFile, CslCommandGenerator.GenerateTableCreateCommand(tableSchema, true));
}
/// <summary>
/// Generates a Kusto table for a specific <see cref="Type"/>, by mapping it's properties to columns.
/// </summary>
/// <param name="client">The <see cref="ICslAdminProvider"/> that we are extending.</param>
/// <param name="type">The <see cref="Type"/> that we are generating a table for.</param>
/// <returns>The name of the table created.</returns>
public static string GenerateTableFromType(this ICslAdminProvider client, Type type)
{
var tableName = type.Name;
var command = CslCommandGenerator.GenerateTableShowCommand(tableName);
try
{
client.ExecuteControlCommand(command);
return(tableName);
}
catch (KustoBadRequestException ex) when(ex.ErrorMessage.Contains("'Table' was not found"))
{
// soak
}
var columns = type.GetProperties().Select(property => new Tuple <string, string>(property.Name, property.PropertyType.FullName)).ToList();
command = CslCommandGenerator.GenerateTableCreateCommand(tableName, columns);
client.ExecuteControlCommand(command);
return(tableName);
}
/// <summary>
/// Create a comparison for the node that was clicked on
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void tvComparison_NodeMouseClick(object sender, TreeNodeMouseClickEventArgs e)
{
string objectName = e.Node.Text;
string sourceText = "";
string targetText = "";
if (_sourceSchema.Functions.ContainsKey(objectName) && e.Node.FullPath.StartsWith(_functionTreeNodeText))
{
sourceText = CslCommandGenerator.GenerateCreateOrAlterFunctionCommand(_sourceSchema.Functions[objectName], true);
}
if (_sourceSchema.Tables.ContainsKey(objectName) && e.Node.FullPath.StartsWith(_tablesTreeNodeText))
{
sourceText = CslCommandGenerator.GenerateTableCreateCommand(_sourceSchema.Tables[objectName], true);
}
if (_targetSchema.Functions.ContainsKey(objectName) && e.Node.FullPath.StartsWith(_functionTreeNodeText))
{
targetText = CslCommandGenerator.GenerateCreateOrAlterFunctionCommand(_targetSchema.Functions[objectName], true);
}
if (_targetSchema.Tables.ContainsKey(objectName) && e.Node.FullPath.StartsWith(_tablesTreeNodeText))
{
targetText = CslCommandGenerator.GenerateTableCreateCommand(_targetSchema.Tables[objectName], true);
}
var diffBuilder = new InlineDiffBuilder(new Differ());
DiffPlex.DiffBuilder.Model.DiffPaneModel diff = diffBuilder.BuildDiffModel(targetText, sourceText);
rtbSourceText.Clear();
int longestLine = 98;
if (diff.Lines.Any())
{
longestLine = Math.Max(diff.Lines.Max(l => l.Text.Length), longestLine);
}
foreach (DiffPlex.DiffBuilder.Model.DiffPiece line in diff.Lines)
{
switch (line.Type)
{
case DiffPlex.DiffBuilder.Model.ChangeType.Inserted:
rtbSourceText.SelectionBackColor = System.Drawing.Color.Yellow;
rtbSourceText.SelectedText = line.Text.PadRight(longestLine);
break;
case DiffPlex.DiffBuilder.Model.ChangeType.Deleted:
rtbSourceText.SelectionBackColor = System.Drawing.Color.Red;
rtbSourceText.SelectedText = line.Text.PadRight(longestLine);
break;
case DiffPlex.DiffBuilder.Model.ChangeType.Imaginary:
break;
default:
rtbSourceText.SelectionBackColor = System.Drawing.Color.White;
rtbSourceText.SelectedText = line.Text.PadRight(longestLine);
break;
}
rtbSourceText.SelectedText += "\n";
}
}
/// <summary>
/// Write a table to Kusto
/// </summary>
/// <param name="tableSchema">The table to write</param>
/// <param name="kustoQueryEngine">An initialized query engine for issuing the Kusto command</param>
public static void WriteToKusto(this TableSchema tableSchema, QueryEngine kustoQueryEngine)
{
kustoQueryEngine.CreateOrAlterTableAsync(CslCommandGenerator.GenerateTableCreateCommand(tableSchema, false), tableSchema.Name).Wait();
}
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}"); });
}
}