public async Task ExecuteAsync()
{
string url = @"http:\\localhost:8088";
await using var context = new KSqlDBContext(url);
var http = new HttpClientFactory(new Uri(url));
restApiClient = new KSqlDbRestApiClient(http);
await CreateOrReplaceStreamAsync();
var statement = context.CreateTableStatement(MaterializedViewName)
.As <IoTSensor>("sensor_values")
.GroupBy(c => c.SensorId)
.Select(c => new { SensorId = c.Key, AvgValue = c.Avg(g => g.Value) });
var query = statement.ToStatementString();
var response = await statement.ExecuteStatementAsync();
response = await InsertAsync(new IoTSensor { SensorId = "sensor-1", Value = 11 });
await PullSensor(context);
}
public async Task ExecuteAsync()
{
string url = @"http:\\localhost:8088";
await using var context = new KSqlDBContext(url);
var http = new HttpClientFactory(new Uri(url));
restApiClient = new KSqlDbRestApiClient(http);
await CreateOrReplaceStreamAsync();
var statement = context.CreateTableStatement(MaterializedViewName)
.As <IoTSensor>(StreamName)
.GroupBy(c => c.SensorId)
.WindowedBy(new TimeWindows(Duration.OfSeconds(5)).WithGracePeriod(Duration.OfHours(2)))
.Select(c => new { SensorId = c.Key, AvgValue = c.Avg(g => g.Value) });
var response = await statement.ExecuteStatementAsync();
var statementResponses = await response.ToStatementResponsesAsync();
await Task.Delay(TimeSpan.FromSeconds(1));
response = await InsertAsync(new IoTSensor { SensorId = "sensor-1", Value = 11 });
}
private static IDisposable Entries(KSqlDBContext context)
{
bool sorted = true;
var subscription = context.CreateQuery <Movie>()
.Select(c => new
{
Entries = KSqlFunctions.Instance.Entries(new Dictionary <string, string>()
{
{ "a", "value" },
{ "b", c.Title }
}, sorted)
})
.Subscribe(c =>
{
Console.WriteLine("Entries:");
foreach (var entry in c.Entries)
{
var key = entry.K;
var value = entry.V;
Console.WriteLine($"{key} - {value}");
}
}, error => { });
return(subscription);
}
private static IDisposable FullOuterJoinTables(KSqlDBContext context)
{
var query = context.CreateQueryStream <MovieNullableFields>("Movies")
.FullOuterJoin(
Source.Of <Lead_Actor>(nameof(Lead_Actor)),
movie => movie.Title,
actor => actor.Title,
(movie, actor) => new
{
movie.Id,
Title = movie.Title,
movie.Release_Year,
ActorTitle = actor.Title,
ActorName = actor.Actor_Name
}
);
var joinQueryString = query.ToQueryString();
return(query
.Subscribe(c =>
{
if (c.Id.HasValue)
{
Console.WriteLine($"{c.Id}: {c.ActorName} - {c.Title} - {c.ActorTitle}");
}
else
{
Console.WriteLine($"No movie id: {c.ActorName} - {c.Title} - {c.ActorTitle}");
}
}, exception => { Console.WriteLine(exception.Message); }));
}
public override void TestInitialize()
{
base.TestInitialize();
var contextOptions = new KSqlDBContextOptions(TestParameters.KsqlDBUrl);
KSqlDBContext = new KSqlDBContext(contextOptions);
}
private static IDisposable NotNull(KSqlDBContext context)
{
return(context.CreateQueryStream <Click>()
.Where(c => c.IP_ADDRESS != null)
.Select(c => new { c.IP_ADDRESS, c.URL, c.TIMESTAMP })
.Subscribe(message => Console.WriteLine(message), error => { Console.WriteLine($"Exception: {error.Message}"); }));
}
public static async Task Main(string[] args)
{
var ksqlDbUrl = @"http:\\localhost:8088";
var contextOptions = new KSqlDBContextOptions(ksqlDbUrl);
await using var context = new KSqlDBContext(contextOptions);
using var disposable = context.CreateQueryStream <Tweet>()
.Where(p => p.Message != "Hello world" || p.Id == 1)
.Where(c => K.Functions.Like(c.Message.ToLower(), "%ALL%".ToLower()))
.Where(p => p.RowTime >= 1510923225000) //AND RowTime >= 1510923225000
.Select(l => new { l.Id, l.Message, l.RowTime })
.Take(2) // LIMIT 2
.ToObservable() // client side processing starts here lazily after subscription
.ObserveOn(TaskPoolScheduler.Default)
.Subscribe(onNext: tweetMessage =>
{
Console.WriteLine($"{nameof(Tweet)}: {tweetMessage.Id} - {tweetMessage.Message}");
Console.WriteLine();
}, onError: error => { Console.WriteLine($"Exception: {error.Message}"); }, onCompleted: () => Console.WriteLine("Completed"));
Console.WriteLine("Press any key to stop the subscription");
Console.ReadKey();
Console.WriteLine("Subscription completed");
}
private static async Task AddAndSaveChangesAsync(KSqlDBContext context)
{
context.Add(MoviesProvider.Movie1);
context.Add(MoviesProvider.Movie2);
var saveResponse = await context.SaveChangesAsync();
}
public override void TestInitialize()
{
base.TestInitialize();
ContextOptions = new KSqlDBContextOptions(KSqlDbRestApiProvider.KsqlDbUrl);
Context = new KSqlDBContext(ContextOptions);
}
private static IDisposable DynamicFunctionCall(KSqlDBContext context)
{
var ifNullQueryString = context.CreateQueryStream <Tweet>()
.Select(c => new { c.Id, c.Amount, Col = K.F.Dynamic("IFNULL(Message, 'n/a')") as string })
.ToQueryString();
return(context.CreateQueryStream <Tweet>()
.Select(c => K.Functions.Dynamic("ARRAY_DISTINCT(ARRAY[1, 1, 2, 3, 1, 2])") as int[])
.Subscribe(
message => Console.WriteLine($"{message[0]} - {message[^1]}"),
public async Task TestInitialize()
{
contextOptions = new KSqlDBContextOptions(KSqlDbRestApiProvider.KsqlDbUrl);
context = new KSqlDBContext(contextOptions);
pullQueryProvider = new SensorsPullQueryProvider();
await pullQueryProvider.ExecuteAsync();
}
public static async Task Main(string[] args)
{
var ksqlDbUrl = @"http:\\localhost:8088";
var httpClientFactory = new HttpClientFactory(new Uri(ksqlDbUrl));
var restApiProvider = new KSqlDbRestApiProvider(httpClientFactory);
var moviesProvider = new MoviesProvider(restApiProvider);
await moviesProvider.CreateTablesAsync();
var contextOptions = CreateQueryStreamOptions(ksqlDbUrl);
var context = new KSqlDBContext(contextOptions);
var subscription = context.CreateQuery <Movie>()
.Where(p => p.Title != "E.T.")
.Where(c => K.Functions.Like(c.Title.ToLower(), "%hard%".ToLower()) || c.Id == 1)
.Where(p => p.RowTime >= 1510923225000) //AND RowTime >= 1510923225000
.Select(l => new { Id2 = l.Id, l.Title, l.Release_Year, l.RowTime })
.Take(2) // LIMIT 2
.ToObservable() // client side processing starts here lazily after subscription. Switches to Rx.NET
.ObserveOn(TaskPoolScheduler.Default)
.Subscribe(onNext: movie =>
{
Console.WriteLine($"{nameof(Movie)}: {movie.Id2} - {movie.Title} - {movie.RowTime}");
Console.WriteLine();
}, onError: error => { Console.WriteLine($"Exception: {error.Message}"); }, onCompleted: () => Console.WriteLine("Completed"));
await moviesProvider.InsertMovieAsync(MoviesProvider.Movie1);
await moviesProvider.InsertMovieAsync(MoviesProvider.Movie2);
await moviesProvider.InsertLeadAsync(MoviesProvider.LeadActor1);
try
{
await new PullQueryExample().ExecuteAsync();
}
catch (Exception e)
{
Console.WriteLine(e);
}
Console.WriteLine("Press any key to stop the subscription");
Console.ReadKey();
await context.DisposeAsync();
using (subscription)
{
}
Console.WriteLine("Subscription completed");
}
public override void TestInitialize()
{
base.TestInitialize();
ContextOptions = new KSqlDBContextOptions(KSqlDbRestApiProvider.KsqlDbUrl)
{
ShouldPluralizeFromItemName = false
};
Context = new KSqlDBContext(ContextOptions);
}
private static async Task AsyncEnumerable(KSqlDBContext context)
{
var cts = new CancellationTokenSource();
var asyncTweetsEnumerable = context.CreateQueryStream <Tweet>().ToAsyncEnumerable();
await foreach (var tweet in asyncTweetsEnumerable.WithCancellation(cts.Token))
{
Console.WriteLine(tweet.Message);
cts.Cancel();
}
}
private static async Task ToQueryStringExample(string ksqlDbUrl)
{
var contextOptions = new KSqlDBContextOptions(ksqlDbUrl);
await using var context = new KSqlDBContext(contextOptions);
var ksql = context.CreateQueryStream <Person>().ToQueryString();
//prints SELECT * FROM People EMIT CHANGES;
Console.WriteLine(ksql);
}
private static IDisposable CountDistinct(KSqlDBContext context)
{
var subscription = context.CreateQuery <Tweet>()
.GroupBy(c => c.Id)
.Select(g => new { Id = g.Key, Count = g.LongCountDistinct(c => c.Message) })
.Subscribe(c =>
{
Console.WriteLine($"{c.Id} - {c.Count}");
}, exception => { Console.WriteLine(exception.Message); });
return(subscription);
}
public static async Task ClassInitialize(TestContext testContext)
{
contextOptions = new KSqlDBContextOptions(KSqlDbRestApiProvider.KsqlDbUrl);
context = new KSqlDBContext(contextOptions);
pullQueryProvider = new SensorsPullQueryProvider();
await pullQueryProvider.ExecuteAsync();
await Task.Delay(TimeSpan.FromSeconds(6));
}
private static IDisposable KQueryWithObserver(string ksqlDbUrl)
{
var contextOptions = new KSqlDBContextOptions(ksqlDbUrl);
var context = new KSqlDBContext(contextOptions);
var subscription = context.CreateQueryStream <Tweet>()
.Where(p => p.Message != "Hello world" && p.Id != 1)
.Take(2)
.Subscribe(new TweetsObserver());
return(subscription);
}
private static IDisposable ToObservableExample(string ksqlDbUrl)
{
var contextOptions = new KSqlDBContextOptions(ksqlDbUrl);
var context = new KSqlDBContext(contextOptions);
var subscriptions = context.CreateQueryStream <Tweet>()
.ToObservable()
.Delay(TimeSpan.FromSeconds(2)) // IObservable extensions
.Subscribe(new TweetsObserver());
return(subscriptions);
}
private static async Task GroupBy()
{
var ksqlDbUrl = @"http:\\localhost:8088";
var contextOptions = new KSqlDBContextOptions(ksqlDbUrl);
contextOptions.QueryStreamParameters["auto.offset.reset"] = "latest";
await using var context = new KSqlDBContext(contextOptions);
context.CreateQueryStream <Tweet>()
.GroupBy(c => c.Id)
.Select(g => new { Id = g.Key, Count = g.Count() })
.Subscribe(count =>
{
Console.WriteLine($"{count.Id} Count: {count.Count}");
Console.WriteLine();
}, error => { Console.WriteLine($"Exception: {error.Message}"); }, () => Console.WriteLine("Completed"));
context.CreateQueryStream <Tweet>()
.GroupBy(c => c.Id)
.Select(g => g.Count())
.Subscribe(count =>
{
Console.WriteLine($"Count: {count}");
Console.WriteLine();
}, error => { Console.WriteLine($"Exception: {error.Message}"); }, () => Console.WriteLine("Completed"));
context.CreateQueryStream <Tweet>()
.GroupBy(c => c.Id)
.Select(g => new { Count = g.Count() })
.Subscribe(count =>
{
Console.WriteLine($"Count: {count}");
Console.WriteLine();
}, error => { Console.WriteLine($"Exception: {error.Message}"); }, () => Console.WriteLine("Completed"));
//Sum
var subscription = context.CreateQueryStream <Tweet>()
.GroupBy(c => c.Id)
//.Select(g => g.Sum(c => c.Id))
.Select(g => new { Id = g.Key, MySum = g.Sum(c => c.Id) })
.Subscribe(sum =>
{
Console.WriteLine($"{sum}");
Console.WriteLine();
}, error => { Console.WriteLine($"Exception: {error.Message}"); }, () => Console.WriteLine("Completed"));
var groupBySubscription = context.CreateQueryStream <IoTSensorChange>("sqlserversensors")
.GroupBy(c => new { c.Op, c.After !.Value })
public void Initialize()
{
var ksqlDbUrl = @"http:\\localhost:8088";
var httpClientFactory = new HttpClientFactory(new Uri(ksqlDbUrl));
restApiClient = new KSqlDbRestApiClient(httpClientFactory);
var contextOptions = new KSqlDBContextOptions(KSqlDbRestApiProvider.KsqlDbUrl)
{
ShouldPluralizeFromItemName = true
};
Context = new KSqlDBContext(contextOptions);
}
private static async Task PullSensor(KSqlDBContext context)
{
string windowStart = "2019-10-03T21:31:16";
string windowEnd = "2025-10-03T21:31:16";
var result = await context.CreatePullQuery <IoTSensorStats>(MaterializedViewName)
.Where(c => c.SensorId == "sensor-1")
//.Where(c => Bounds.WindowStart > windowStart && Bounds.WindowEnd <= windowEnd)
.GetAsync();
Console.WriteLine($"Id: {result?.SensorId} - Avg Value: {result?.AvgValue} - Window Start {result?.WindowStart}");
string ksql = "SELECT * FROM avg_sensor_values WHERE SensorId = 'sensor-1';";
result = await context.ExecutePullQuery <IoTSensorStats>(ksql);
}
private static IDisposable ClientSideBatching(KSqlDBContext context)
{
var disposable = context.CreateQueryStream <Tweet>()
.ToObservable()
.Buffer(TimeSpan.FromMilliseconds(250), 100)
.Where(c => c.Count > 0)
//.ObserveOn(System.Reactive.Concurrency.DispatcherScheduler.Current) //WPF
.Subscribe(tweets =>
{
foreach (var tweet in tweets)
{
Console.WriteLine(tweet.Message);
}
});
return(disposable);
}
private static async Task DeeplyNestedTypes(KSqlDBContext context)
{
var moviesStream = context.CreateQuery <Movie>();
var source = moviesStream.Select(c => new
{
c.Id,
Arr = new[]
{
new MovieStruct
{
Title = c.Title,
Id = c.Id,
},
new MovieStruct
{
Title = "test",
Id = 2,
}
},
MapValue = new Dictionary <string, Dictionary <string, int> >
{
{ "a", new Dictionary <string, int> {
{ "a", 1 }, { "b", 2 }
} },
{ "b", new Dictionary <string, int> {
{ "c", 3 }, { "d", 4 }
} },
},
MapArr = new Dictionary <int, string[]>
{
{ 1, new[] { "a", "b" } },
{ 2, new[] { "c", "d" } }
},
Str = new MovieStruct {
Title = c.Title, Id = c.Id
},
c.Release_Year
}).ToAsyncEnumerable();
await foreach (var movie in source)
{
Console.WriteLine($"{movie.Str.Title} - {movie.Release_Year}");
}
}
public async Task ExecuteAsync()
{
string ksqlDbUrl = @"http:\\localhost:8088";
var contextOptions = new KSqlDbContextOptionsBuilder()
.UseKSqlDb(ksqlDbUrl)
.SetBasicAuthCredentials("fred", "letmein")
.Options;
contextOptions.DisposeHttpClient = false;
await using var context = new KSqlDBContext(contextOptions);
var httpClient = new HttpClient()
{
BaseAddress = new Uri(ksqlDbUrl)
};
var httpClientFactory = new HttpClientFactory(httpClient);
restApiClient = new KSqlDbRestApiClient(httpClientFactory)
.SetCredentials(new BasicAuthCredentials("fred", "letmein"));
((KSqlDbRestApiClient)restApiClient).DisposeHttpClient = false;
await CreateOrReplaceStreamAsync();
var statement = context.CreateTableStatement(MaterializedViewName)
.As <IoTSensor>("sensor_values")
.GroupBy(c => c.SensorId)
.WindowedBy(new TimeWindows(Duration.OfSeconds(5)).WithGracePeriod(Duration.OfHours(2)))
.Select(c => new { SensorId = c.Key, AvgValue = c.Avg(g => g.Value) });
var query = statement.ToStatementString();
var response = await statement.ExecuteStatementAsync();
response = await InsertAsync(new IoTSensor { SensorId = "sensor-1", Value = 11 });
await PullSensor(context);
}
private static async Task PullSensor(KSqlDBContext context)
{
string windowStart = "2019-10-03T21:31:16";
string windowEnd = "2025-10-03T21:31:16";
var pullQuery = context.CreatePullQuery <IoTSensorStats>(MaterializedViewName)
.Where(c => c.SensorId == "sensor-1")
.Where(c => Bounds.WindowStart > windowStart && Bounds.WindowEnd <= windowEnd)
.Take(5);
var sql = pullQuery.ToQueryString();
await foreach (var item in pullQuery.GetManyAsync().OrderBy(c => c.SensorId).ConfigureAwait(false))
{
Console.WriteLine($"Pull query - GetMany result => Id: {item?.SensorId} - Avg Value: {item?.AvgValue} - Window Start {item?.WindowStart}");
}
var list = await pullQuery.GetManyAsync().OrderBy(c => c.SensorId).ToListAsync();
string ksql = "SELECT * FROM avg_sensor_values WHERE SensorId = 'sensor-1';";
var result2 = await context.ExecutePullQuery <IoTSensorStats>(ksql);
}
private static IDisposable Window(KSqlDBContext context)
{
var subscription1 = context.CreateQueryStream <Tweet>()
.GroupBy(c => c.Id)
.WindowedBy(new TimeWindows(Duration.OfSeconds(5)).WithGracePeriod(Duration.OfHours(2)))
.Select(g => new { g.WindowStart, g.WindowEnd, Id = g.Key, Count = g.Count() })
.Subscribe(c => { Console.WriteLine($"{c.Id}: {c.Count}: {c.WindowStart}: {c.WindowEnd}"); }, exception => { Console.WriteLine(exception.Message); });
var query = context.CreateQueryStream <Tweet>()
.GroupBy(c => c.Id)
.WindowedBy(new HoppingWindows(Duration.OfSeconds(5)).WithAdvanceBy(Duration.OfSeconds(4))
.WithRetention(Duration.OfDays(7)))
.Select(g => new { Id = g.Key, Count = g.Count() });
var hoppingWindowQueryString = query.ToQueryString();
var subscription2 = query
.Subscribe(c => { Console.WriteLine($"{c.Id}: {c.Count}"); }, exception => { Console.WriteLine(exception.Message); });
return(new CompositeDisposable {
subscription1, subscription2
});
}
private static IDisposable JoinTables(KSqlDBContext context)
{
var query = context.CreateQueryStream <Movie>()
.Join(
//.LeftJoin(
Source.Of <Lead_Actor>(nameof(Lead_Actor)),
movie => movie.Title,
actor => actor.Title,
(movie, actor) => new
{
movie.Id,
Title = movie.Title,
movie.Release_Year,
ActorName = K.Functions.RPad(K.Functions.LPad(actor.Actor_Name.ToUpper(), 15, "*"), 25, "^"),
ActorTitle = actor.Title,
Substr = K.Functions.Substring(actor.Title, 2, 4)
}
);
var joinQueryString = query.ToQueryString();
return(query
.Subscribe(c => { Console.WriteLine($"{c.Id}: {c.ActorName} - {c.Title} - {c.ActorTitle}"); }, exception => { Console.WriteLine(exception.Message); }));
}
public static async Task Main(string[] args)
{
var ksqlDbUrl = @"http:\\localhost:8088";
var loggerFactory = CreateLoggerFactory();
var httpClientFactory = new HttpClientFactory(new Uri(ksqlDbUrl));
var restApiProvider = new KSqlDbRestApiProvider(httpClientFactory, loggerFactory)
{
DisposeHttpClient = false
};
restApiProvider.SetCredentials(new BasicAuthCredentials("fred", "letmein"));
var moviesProvider = new MoviesProvider(restApiProvider);
await moviesProvider.CreateTablesAsync();
var contextOptions = CreateQueryStreamOptions(ksqlDbUrl);
await using var context = new KSqlDBContext(contextOptions, loggerFactory);
var query = context.CreateQueryStream <Movie>() // Http 2.0
// var query = context.CreateQuery<Movie>() // Http 1.0
.Where(p => p.Title != "E.T.")
.Where(c => c.Title.ToLower().Contains("hard".ToLower()) || c.Id == 1)
.Where(p => p.RowTime >= 1510923225000)
.Select(l => new { Id = l.Id, l.Title, l.Release_Year, l.RowTime })
.Take(2); // LIMIT 2
var ksql = query.ToQueryString();
Console.WriteLine("Generated ksql:");
Console.WriteLine(ksql);
Console.WriteLine();
using var disposable = query
.ToObservable() // client side processing starts here lazily after subscription. Switches to Rx.NET
.Finally(() => { Console.WriteLine("Finally"); })
.Subscribe(onNext: movie =>
{
Console.WriteLine($"{nameof(Movie)}: {movie.Id} - {movie.Title} - {movie.RowTime}");
Console.WriteLine();
}, onError: error => { Console.WriteLine($"Exception: {error.Message}"); }, onCompleted: () => Console.WriteLine("Completed"));
await CreateOrReplaceTableStatement(context);
await moviesProvider.InsertMovieAsync(MoviesProvider.Movie1);
await moviesProvider.InsertMovieAsync(MoviesProvider.Movie2);
await moviesProvider.InsertLeadAsync(MoviesProvider.LeadActor1);
try
{
await new PullQueryExample().ExecuteAsync();
}
catch (Exception e)
{
Console.WriteLine();
Console.WriteLine(e.Message);
}
string explain = await query.ExplainAsStringAsync();
ExplainResponse[] explainResponses = await query.ExplainAsync();
Console.WriteLine($"{Environment.NewLine} Explain => ExecutionPlan:");
Console.WriteLine(explainResponses[0].QueryDescription?.ExecutionPlan);
Console.WriteLine("Press any key to stop the subscription");
Console.ReadKey();
await moviesProvider.DropTablesAsync();
Console.WriteLine("Finished.");
}
