# The Databricks kit — lakehouse tables as entities, and exports back

> An optional package that makes Databricks tables ordinary Osy# entities: your app reads them with LINQ, and each query runs on your SQL warehouse as one statement already carrying the reading person's access rule. The other direction sends your own entities into a Delta table as they change. This page says how to take the dependency, what to register, what runs as one statement, and what is refused.

<!-- id: config-databricks · area: config · stability: preview · html: https://osysharp.com/reference/config/databricks/ -->

## Summary        {#summary}

`Osysharp.Warehouses.Databricks` is an optional **kit**: an app that never reads a lakehouse pays nothing for it. Taking the
dependency gives you a **data source**, so tables in a Databricks workspace are entities your pages and functions
query like any other, and an **export sink**, so an entity of your own arrives in a Delta table minutes after each
commit.

The kit is a package of pure Osy#, not a platform feature. It implements the generic seams described in
[data held somewhere else (app.DataSources)](https://osysharp.com/reference/config/data-sources/) and [data sent somewhere else, as it changes (app.Exports)](https://osysharp.com/reference/config/exports/) — and it exists partly to prove those seams are not shaped around one
vendor, since it landed against the same contract [The Snowflake kit — warehouse tables as entities, and exports back](https://osysharp.com/reference/config/snowflake/) did.

## Signature      {#signature}

```osy title="app.osy — the manifest takes the dependency AND grants it" syntax
use Osysharp.Warehouses.Databricks {
  egress "*.cloud.databricks.com";
  secret "DatabricksToken";
}
```

## Description    {#description}

### Taking the dependency, and what each line is for   {#install}

Two declarations turn it on. The manifest takes the dependency **and grants it what it needs** — a package reaches no
host and reads no secret unless the app says so. Grant the egress for the cloud your workspace runs in:

```osy title="app.osy — the dependency, and a grant per cloud you use" syntax
app Revenue {
  model "**/*.osy";
  use Osysharp.Warehouses.Databricks {
    egress "*.cloud.databricks.com";
    egress "*.azuredatabricks.net";
    egress "*.gcp.databricks.com";
    secret "DatabricksToken";
  }
}
```

```osy title="the app's own file — declare the secret, then register the workspace" syntax
using Osysharp.Warehouses.Databricks;

app.Secrets = [ new Secret("DatabricksToken") ];

app.DataSources = [
  new DatabricksSource("Lakehouse") {
    Host = "adb-1234567890123456.7.azuredatabricks.net", WarehouseId = "abcdef1234567890",
    Catalog = "main", Schema = "sales"
  }
];
```

`Host` is your workspace's host name as its URL spells it. `WarehouseId` is the SQL warehouse's id, shown in the
warehouse's connection details. Register one `DatabricksSource` per workspace, warehouse or catalog you read from.

### Giving it a token, and the grants that token needs   {#credentials}

```console
osy secret set DatabricksToken     # a personal access token, or an OAuth token for a service principal
```

Give the identity behind the token `CAN USE` on the warehouse and `SELECT` on the tables your entities name — plus
`MODIFY` on the ones the app updates or deletes from — and nothing else.

### Declaring the tables you read   {#entities}

An entity bound to the source names its table with `[ExternalName]` and the column or columns that identify a row
with `[Key]`. Its `security { }` is the app's own, and it is what the warehouse is told:

```osy title="one table, one entity — the access rule travels with every query" syntax
[DataSource(DataSource.Lakehouse), ExternalName("sales.orders")]
entity Order {
  [Key, ExternalName("order_id")] string Number;
  [ExternalName("region")] string Region;
  [ExternalName("total")] decimal Total;
  [ExternalName("placed")] DateOnly Placed;
  security { allow read where Region == user.Region; }
}
```

Two entities of the same source may reference each other; the reference's `ExternalName` names the column holding the
target's key, and the target declares exactly one `[Key]`:

```osy title="a reference is the column holding the target's key" syntax
[DataSource(DataSource.Lakehouse), ExternalName("sales.customers")]
entity Customer {
  [Key, ExternalName("code")] string Code;
  [ExternalName("name")] string Name;
  [ExternalName("region")] string Region;
  security { allow read where Region == user.Region; }
}

// on Order:
  [ExternalName("customer_code")] Customer? Customer;
```

Reading through the reference is a `LEFT JOIN` over only the target rows the reading person may read, so an order
whose customer they may not read keeps its row and reads the customer as `null`. A reference to an entity in another
source, or in the platform's own storage, is refused when the app compiles.

### What runs on the warehouse as one statement   {#queries}

`Order.Where(o => o.Total > 100).OrderBy(o => o.Placed).Take(20)` is one statement, and so are `Count`, `Any`, `Sum`,
`Average`, `Min`, `Max`, `GroupBy` with a `.Where` on the groups, `Select(…).Distinct()`, arithmetic, text joined with
`+`, `??` and `?:` over a row's columns, a date's parts and a date moved (`AddDays` through `AddYears`, `.Date`,
`DayOfWeek`, `DayOfYear`, `DayNumber`), `Math.Round` to a number of digits under every `MidpointRounding` (half to even by default, as in C#), `Floor`, `Ceiling`, `Abs`, `Truncate`, `Sign`, `Min`,
`Max`, `Pow`, `Sqrt`, `Clamp`, `Trim`/`TrimStart`/`TrimEnd`, `Replace`, `IsNullOrEmpty`, `IsNullOrWhiteSpace`,
`Like`, `ToUpper`, `ToLower` and `Length`.

Case is mapped by .NET's own table, sent with the query, where this engine's own mapping is the full one; `Length`
counts UTF-16 units as .NET counts them; and an `Average` is asked for as its sum and its count and divided the way
C#'s `decimal` divides, so it carries every digit a `decimal` holds rather than the warehouse's rounding.

Each of these is one statement too: `Where(…).Update(o => { … })` and `Where(…).Delete()`, with an
`OrderBy(…).Take(n)` page before them, under the reading person's access rule and the update or delete rule;
`Other.Where(…).Insert(o => new Order { … })` from another table of the warehouse, or
`list.Insert(x => new Order { … })` from a list the app holds, under the create rule of the entity inserted into;
another entity read inside a condition, under that entity's own rule; `Union`/`Concat`/`Intersect`/`Except` of two
reads of one entity; a `Window.*` value in a projection; and `Join`, `LeftJoin` and `SelectMany` between entities of
the one warehouse.

### What is refused, and why   {#refused}

`Convert.ToInt32`, `Convert.ToInt64` and `Convert.ToDecimal` are **refused when the app compiles**. They read text
into C#'s `decimal`, whose scale is whatever the text says, and a Databricks `DECIMAL` has one fixed scale — so the
kit declines rather than answer with a different number than the platform would. Convert in memory after `.ToList()`.

`Convert.ToString`, `Convert.ToBoolean`, `Convert.ToDouble` and the numeric casts are translated.

### Sending your own entities into the lakehouse   {#exports}

The other direction is [data sent somewhere else, as it changes (app.Exports)](https://osysharp.com/reference/config/exports/): declare which entity's changes leave and which columns go with them, and
the platform delivers every change in commit order. Each batch is one `MERGE`, so a redelivery cannot undo it.
`DatabricksExport` creates the Delta table and widens it as the shape changes.

### What the package contains   {#ships}

| file | what it is |
|---|---|
| `DatabricksConnection.osy` | the workspace settings, the bearer token and the statement client a source and an export share |
| `DatabricksSource.osy` | runs a query plan through the SQL Statement Execution API and hands back its rows, or runs an update or delete and answers the `num_affected_rows` the warehouse reports; describes a catalog's tables |
| `DatabricksExport.osy` | creates and widens the export's table, and merges each batch into it |
| `DatabricksSql.osy` | translates a query plan into one Databricks SQL statement with named, typed parameters |
| `DatabricksResponses.osy` | the SQL Statement Execution API's response shapes |

What every statement it writes guarantees:

- each value is a bound parameter, never text spliced into the SQL;
- strings compare, sort and group ordinally (`COLLATE UTF8_BINARY`), whatever collation a schema or table defaults to;
- null sorts as C# sorts it — first ascending, last descending — and `StartsWith`, `EndsWith` and `Contains` match
  their text literally;
- distinct rows compare strings exactly and count `null` as one value, as C#'s `Distinct()` does;
- a computed value means what it does in C#: whole numbers divide truncating toward zero, a remainder keeps the sign
  of the number divided, and arithmetic on `null` is `null`;
- the reading person's access rule is part of the same `WHERE` as the query's own filter, so paging counts only rows
  they may read;
- a join on a value, and a read of another entity inside a condition, read that entity under its own access rule;
- what C# throws on — `Math.Clamp` with its minimum above its maximum, `Replace` of an empty string, a number with no
  place in an `int` — fails the statement with `raise_error`, whatever the session's ANSI setting;
- a `TIMESTAMP` column arrives with its UTC offset, so it names the same instant whatever time zone the warehouse
  uses.

Results are requested inline as JSON and read chunk by chunk as they are consumed. A statement still running is
checked again after a pause doubling from a quarter of a second to eight seconds; one still running after
`TimeoutSeconds` (600 by default) is cancelled, so it stops using the warehouse, and the read fails. A failed
statement throws `ExternalServiceException` with the warehouse's own message.

`DescribeSchema` — what `osy import-schema` calls — lists the tables in `Catalog` (in `Schema` alone when one is set)
with each column's type and each table's primary key. Columns of a type a query plan cannot carry (`BINARY`, `ARRAY`,
`MAP`, `STRUCT`, `VARIANT`, `INTERVAL`) are left out. A `TIMESTAMP` column is described as `DateTimeOffset` and a
`TIMESTAMP_NTZ` one as `DateTime`.

The ordinal collation needs a runtime that supports `COLLATE`; a SQL warehouse on a current channel does.

### Writing a provider for another warehouse   {#own}

Another workspace, warehouse or catalog needs no change to the package: register one `DatabricksSource` per name.
Another **warehouse product** is an `IEntitySource` of your own, and this package is a worked example — a translator
from the query plan to that engine's SQL, a client for its API over `Http.*`, and the hosts and secrets it needs
declared in its manifest. Fork the repository, rename the package to match where it lives (`package Acme.Warehouse`
lives at `github.com/acme/warehouse`), change the dialect and the endpoints, and `osy publish`. The query plan
conformance cases — every plan, and the rows it must return — are what to hold your translator to.

## Examples       {#examples}

```osy title="the whole dependency: the grant, the token, the warehouse and one table" test app=config-databricks
app Revenue {
  use Osysharp.Warehouses.Databricks {
    egress "*.cloud.databricks.com";
    egress "*.azuredatabricks.net";
    egress "*.gcp.databricks.com";
    secret "DatabricksToken";
  }
}

using Osysharp.Warehouses.Databricks;

app.Secrets = [ new Secret("DatabricksToken") ];

app.DataSources = [
  new DatabricksSource("Lakehouse") {
    Host = "adb-1234567890123456.7.azuredatabricks.net", WarehouseId = "abcdef1234567890",
    Catalog = "main", Schema = "sales"
  }
];

[DataSource(DataSource.Lakehouse), ExternalName("sales.orders")]
entity Order {
  [Key, ExternalName("order_id")] string Number;
  [ExternalName("total")] decimal Total;
  [ExternalName("placed_at")] DateTime Placed;
}
```

```osy title="a page reading lakehouse rows, secured by the app's own rule" syntax
[Route("/orders")]
component Orders() {
  live var mine = Order.Where(o => o.Total > 100m).OrderBy(o => o.Placed).Take(20).ToList();
  render {
    Stack {
      foreach (var o in mine) { Text($"{o.Number} — {o.Total:C} — {o.Placed:yyyy-MM-dd}"); }
    }
  }
}
```

## See also       {#see-also}

- [data held somewhere else (app.DataSources)](https://osysharp.com/reference/config/data-sources/) — the generic seam this kit implements, and what any provider must declare
- [The Snowflake kit — warehouse tables as entities, and exports back](https://osysharp.com/reference/config/snowflake/) — the same two shapes for a Snowflake account
- [data sent somewhere else, as it changes (app.Exports)](https://osysharp.com/reference/config/exports/) — sending an entity's changes out, the direction `DatabricksExport` serves
- [declaring secrets (app.Secrets)](https://osysharp.com/reference/config/secrets/) — declaring the secret the manifest grants
- [use](https://osysharp.com/reference/types/use/) — what `use` does, and why a package is granted its hosts explicitly
- [package.osy](https://osysharp.com/reference/project/package/) — the manifest that makes a repository a publishable package