# Dremio Data Connector

Run federated SQL queries against Dremio datasets over a gRPC endpoint, with paths that name each Dremio space and folder.

[Dremio](https://www.dremio.com/) is a data lake engine that enables high-performance SQL queries directly on data lake storage. It provides a unified interface for querying and analyzing data from various sources without the need for complex data movement or transformation.

This connector enables using Dremio as a data source for federated SQL queries.

```yaml
- from: dremio:datasets.dremio_dataset
  name: dremio_dataset
  params:
    dremio_endpoint: grpc://127.0.0.1:32010
    dremio_username: demo
    dremio_password: ${secrets:my_dremio_pass}
```

## Configuration

### `from`

The `from` field takes the form `dremio:dataset` where `dataset` is the fully qualified name of the dataset to read from. The name can have any number of dot-separated parts, one for each space and folder in the Dremio path (for example, `dremio:my_space.my_folder.my_subfolder.my_dataset`).

> **Note**
>
> Unquoted identifiers are normalized to lowercase. To reference a dataset with mixed-case characters, wrap each case-sensitive part in double quotes: `dremio:my_source."MixedCaseDataset"`. See [Identifier Case Sensitivity](https://spice.ai/docs/building-blocks/data-connectors#identifier-case-sensitivity-and-quoting).

### `name`

The dataset name. This will be used as the table name within Spice.

Example:

```yaml
datasets:
  - from: dremio:datasets.dremio_dataset
    name: cool_dataset
    params: ...
```

```sql
SELECT COUNT(*) FROM cool_dataset;
```

```shell
+----------+
| count(*) |
+----------+
| 6001215  |
+----------+
```

The dataset name cannot be a [reserved keyword](https://spiceai.org/docs/reference/spicepod/keywords).

### `params`

| Parameter Name | Description |
| - | - |
| `dremio_endpoint` | Required. The endpoint used to connect to the Dremio server. |
| `dremio_username` | Optional. The username used to connect to the Dremio endpoint. |
| `dremio_password` | Optional. The password used to connect to the Dremio endpoint. Use the [secret replacement syntax](https://spice.ai/docs/building-blocks/data-connectors/dremio#secrets) to load the password from a secret store, e.g. `${secrets:my_dremio_pass}`. |

## Examples

### Connecting to a GRPC endpoint

```yaml
- from: dremio:datasets.dremio_dataset
  name: dremio_dataset
  params:
    dremio_endpoint: grpc://127.0.0.1:32010
    dremio_username: demo
    dremio_password: ${secrets:my_dremio_pass}
```

## Types

The table below shows the Dremio data types supported, along with the type mapping to Apache Arrow types in Spice.

| Dremio Type | Arrow Type |
| - | - |
| `INT` | `Int32` |
| `BIGINT` | `Int64` |
| `FLOAT` | `Float32` |
| `DOUBLE` | `Float64` |
| `DECIMAL` | `Decimal128` |
| `VARCHAR` | `Utf8` |
| `VARBINARY` | `Binary` |
| `BOOL` | `Boolean` |
| `DATE` | `Date64` |
| `TIME` | `Time32` |
| `TIMESTAMP` | `Timestamp(Millisecond, None)` |
| `INTERVAL` | `Interval` |
| `LIST` | `List` |
| `STRUCT` | `Struct` |
| `MAP` | `Map` |

## Secrets

Spice integrates with multiple secret stores to help manage sensitive data securely. For detailed information on supported secret stores, refer to the [secret stores documentation](https://spiceai.org/docs/components/secret-stores). Additionally, learn how to use referenced secrets in component parameters by visiting the [using referenced secrets guide](https://spiceai.org/docs/components/secret-stores#using-secrets).

## Limitations

- Dremio connector does not support queries with the EXCEPT and INTERSECT keywords in Spice REPL. Use DISTINCT and IN/NOT IN instead. See the example below.

```SQL
# fail
SELECT ws_item_sk FROM web_sales
INTERSECT
SELECT ss_item_sk FROM store_sales;

# success
SELECT DISTINCT ws_item_sk FROM web_sales
WHERE ws_item_sk IN (
    SELECT DISTINCT ss_item_sk FROM store_sales
);

# fail
SELECT ws_item_sk FROM web_sales
EXCEPT
SELECT ss_item_sk FROM store_sales;

# success
SELECT DISTINCT ws_item_sk FROM web_sales
WHERE ws_item_sk NOT IN (
    SELECT DISTINCT ss_item_sk FROM store_sales
);
```

## Cookbook

- A cookbook recipe to configure Dremio as data connector in Spice. [Dremio Data Connector](https://github.com/spiceai/cookbook/tree/trunk/dremio#readme)