Apache.Calcite.Cosmos.Adapter 1.0.0-pre.316

Apache.Calcite.Cosmos.Adapter

An Apache Calcite adapter for Azure Cosmos DB for NoSQL. Query Cosmos containers with ordinary SQL from .NET: as much of each query as Cosmos can evaluate is translated to Cosmos SQL and run by the service, and the rest — relational joins, set operations, functions Cosmos lacks — is evaluated in process by Calcite, which runs inside your application through IKVM.

User manual · Source

Install

dotnet add package Apache.Calcite.Cosmos.Adapter
dotnet add package Apache.Calcite.Data

Apache.Calcite.Data is the ADO.NET provider your code talks to; this package plugs Cosmos into it.

Register a database

{
  "version": "1.0",
  "defaultSchema": "COSMOS",
  "schemas": [{
    "name": "COSMOS",
    "type": "custom",
    "factory": "Apache.Calcite.Cosmos.Adapter.CosmosSchemaFactory, Apache.Calcite.Cosmos.Adapter",
    "operand": {
      "endpoint": "https://myaccount.documents.azure.com:443/",
      "database": "inventory",
      "containers": [ "products", "orders" ]
    }
  }]
}

Give a key to use an account key, or none to sign in with Microsoft Entra ID. Omit containers to expose every container, or database to expose the whole account.

The factory must be named assembly-qualified, as above, and its assembly loaded before the model is read — _ = new Apache.Calcite.Cosmos.Adapter.CosmosSchemaFactory(); at start-up does it.

Query

await using var connection = new CalciteConnection(new CalciteConnectionStringBuilder
{
    Model = "inline:" + model,
    CaseSensitive = true,
    DefaultNullCollation = "LOW",
}.ConnectionString);

await connection.OpenAsync();

await using var command = connection.CreateCommand();
command.CommandText = """
    SELECT c."id", JSON_VALUE(c."DOC", '$.name') AS "name"
    FROM "products" AS c
    WHERE c."$.category" = 'bikes'
    """;

await using var reader = await command.ExecuteReaderAsync();
while (await reader.ReadAsync())
    Console.WriteLine(reader.GetString(1));

Each container is a table with one column holding the whole document, DOC, reached with JSON_VALUE and JSON_QUERY, plus id, _ts, _etag and the partition key. Set DefaultNullCollation = "LOW" so sorts can run at the service, and read with the asynchronous methods — the Cosmos SDK has no synchronous reads.

Features

• Filters, projections, sorts, paging, aggregation and array traversal pushed down — partially where a whole operator cannot be
• Partition routing and point reads recovered from the predicate
• Lookup joins that fetch only the documents another source's keys match
• INSERT, UPDATE and DELETE
• JSON Schema and UNIQUE declarations that let UUIDs, timestamps and numbers stored as text push down
• Full text search, vector search and geodesic geography
• Request-unit metrics and traces through System.Diagnostics

Documentation

The user manual covers configuration, the row model, what pushes down and why, writing, declarations, performance, monitoring and troubleshooting.

• Quick start
• Connecting and authenticating
• Tables, columns and documents
• Troubleshooting
• Limitations

License

Apache License 2.0.