Trino C# Client

Introduction

A streaming C# Trino client library with ADO.NET interfaces. The priorties for this library are:

• Response performance (fast return of rows for both short and long running queries).
• Type support in .NET (with complex types, date time precision) and System.Data types.
• Authentication which is customizable and flexible (to support arbitrary custom token refresh, or unique cloud requirements).
• Full ADO.NET implementation.
• Streaming with read-ahead into customizable buffer size to allow for no-wait client paging.
• Session support, parameterized query support.
• No dependencies outside of .NET core except Newtonsoft.Json and Microsoft.Extensions.Logging. Authentication in separate library.
• Alignment with Trino Java client, similar class structure.

Resources

Project introduction at Trino Summit 2024 by George Fischer

Presentation deck

Libraries

These are the libraries that make up the client:

[!NOTE] .NET standard 2.0 provides compatibility with .NET Framework 4.7.2 which is widely used. Design note: IAsyncEnumerable is not available in .NET Standard 2.0 but due to asynchronous read-ahead you do not need to await reading every row.

Building

Command Line

1. Install .NET SDK latest version from https://dotnet.microsoft.com/en-us/download
2. From the root folder of the project, run msbuild TrinoDriver.sln

To make the nuget packages:

nuget pack Trino.Client\Trino.Client.nuspec -Version 1.0.0
nuget pack Trino.Data.ADO\Trino.Data.ADO.nuspec -Version 1.0.0
nuget pack Trino.Client.Auth\Trino.Client.Auth.nuspec -Version 1.0.0

Visual Studio

In Visual Studio, open TrinoDriver.sln and build.

Visual Studio Code

1. Install .NET SDK latest version from https://dotnet.microsoft.com/en-us/download
2. Install the C# extension for VS Code
3. Open the project folder in VS Code
4. Select a build configuration using the .NET: Select Project command
5. Build using Terminal > Run Build Task or dotnet build TrinoDriver.sln

Usage Examples

The client library provides two ways to interact with Trino:

1. ADO.NET Interface (Recommended for .NET applications) - Provides standardized database access through familiar ADO.NET abstractions (DbConnection, DbCommand, IDataReader). Use this if you want code that's consistent with other database providers or need to work with existing ADO.NET-based tools and frameworks. See the ADO.NET Connection section for detailed configuration options.

2. Trino SDK - Direct access to Trino functionality through a lightweight client implementation. This approach offers more fine-grained control and aligns closely with other Trino client implementations.

Quick Start Using ADO.NET

TrinoConnectionProperties properties = new TrinoConnectionProperties()
{
    Catalog = "tpch",
    Server = new Uri("https://trino.myhost.net/"),
    Auth = new TrinoJWTAuth(token: "...")
};

using (TrinoConnection connection = new TrinoConnection(properties))
{
    using (IDbCommand command = new TrinoCommand(connection, "SELECT * FROM tpch.tiny.customer LIMIT 5"))
    using (IDataReader reader = command.ExecuteReader())
    {
        while (reader.Read())
        {
            for (int i = 0; i < reader.FieldCount; i++)
            {
                Console.WriteLine($"{reader.GetName(i)} -> {reader.GetDataTypeName(i)} : {reader.GetValue(i)}");
            }
        }
    }
}

### Quick Start Using Trino SDK

#### Basic Query Execution

```csharp
// Create a session with default settings
ClientSession session = new ClientSession(
    sessionProperties: new ClientSessionProperties()
    {
        Server = new Uri("http://localhost:8080/")
    });

// Execute query and read results into a DataTable
DataTable dt = await(
    await RecordExecutor.Execute(
        session: session,
        statement: "select * from tpch.tiny.customer"
        ).ConfigureAwait(false)
    ).BuildDataTableAsync().ConfigureAwait(false);
Console.WriteLine($"Read {dt.Rows.Count} rows");

Row-by-Row Processing

RecordExecutor records = await RecordExecutor.Execute(
        session: session,
        statement: "select * from tpch.tiny.customer"
        ).ConfigureAwait(false);

// Results stream automatically with configurable buffer
foreach (var row in records)
{
    Console.WriteLine(string.Join(",", row));
}

Advanced SDK Features

For more complex scenarios, the SDK provides direct access to Trino features:

ITrinoAuth auth = new TrinoJWTAuth() { AccessToken = "token" };

ClientSession session = new TrinoConnectionProperties()
{
    Catalog = "tpch",
    Server = new Uri("https://trino.myhost.net/"),
    Auth = auth,
    SessionProperties = new Dictionary<string, string>() 
    { 
        { "dictionary_aggregation", "false" } 
    }
}.GetSession();

RecordExecutor records = await RecordExecutor.Execute(
    logger: null, // optional detailed logging
    queryStatusNotifications: [(stats, error) => 
    { 
        Console.WriteLine($"Processed rows: {stats.processedRows}"); 
    }], // Output statistics to console as query runs
    session: session,
    statement: "select * from tpch.tiny.customer where custkey = ?",
    queryParameters: new List<QueryParameter>() { new(751) },
    bufferSize: 1024 * 1024 * 5, // 5 MB
    isQuery: true,
    CancellationToken.None).ConfigureAwait(false);

DataTable dt = await records.BuildDataTableAsync().ConfigureAwait(false);
Console.WriteLine("Data table created with rows: " + dt.Rows.Count);

ADO.NET Connection

The ADO.NET implementation provides standard DbConnection, DbCommand, and DataReader interfaces for Trino. This allows you to use Trino with existing ADO.NET-based tools and patterns.

Connection Properties

When establishing an ADO.NET connection you declare your connection using the TrinoConnectionProperties object which can be serialized into a query string and can be set using a query string. Example:

TrinoConnectionProperties properties = new TrinoConnectionProperties()
{
    Server = new Uri("https://trino.myhost.net/"),
    Auth = new TrinoJWTAuth(token: "...")
};

or

TrinoConnectionProperties properties = new TrinoConnectionProperties()
{
    Server = new Uri("http://localhost:8080/")
};

Connection String

The Trino ADO.NET library supports connection strings. The connection string is expressed as semicolon delimetered assignments. The server name needs to be broken down into host, port, and enablessl properties.

[!NOTE] Any auth class used in the connections string must be in a loaded assembly. If you have a custom authenticator it must be in a loaded assembly in order to be discovered.

Example connection using a connection string:

using (TrinoConnection tc = new TrinoConnection())
{
    tc.ConnectionString = $"catalog=delta;schema=nyc;host={demoClusterHost};auth=TrinoJWTAuth;AccessToken={token}";
    using (IDbCommand trinoCommand = new TrinoCommand(tc, all_types, TimeSpan.MaxValue, null, logger))
    {
        IDataReader idr = trinoCommand.ExecuteReader();
        while (idr.Read())
        {
            // consume data
        }
    }
}

Session Management

The connection maintains session properties that affect query execution. These can be set through properties or SQL commands:

using (TrinoConnection connection = new TrinoConnection(properties))
{
    // Direct property setting
    connection.ConnectionSession.Properties.Properties["query_max_memory"] = "1GB";
    
    // Using SQL commands
    using (IDbCommand command = new TrinoCommand(connection, "SET SESSION query_max_memory = '2GB'"))
    {
        command.ExecuteNonQuery();
    }
}

Connection String/TrinoConnectionProperties

Authentication

Authentication is handled through implementations of ITrinoAuth. Each provider must implement:

• AuthorizeAndValidate(): Initializes or refreshes authorization
• AddCredentialToRequest(): Adds credentials to each HTTP request

Built-in providers include:

JWT Bearer Token

Simple token-based authentication:

Auth = new TrinoJWTAuth(token: "your-token-here")

You can implement token refresh:

TrinoJWTAuth auth = new TrinoJWTAuth(getInitialToken());
using (TrinoConnection connection = new TrinoConnection(properties))
{
    // Setup automatic refresh every 45 minutes
    Task.Run(async () =>
    {
        while (true)
        {
            await Task.Delay(TimeSpan.FromMinutes(45));
            auth.AccessToken = getNewToken();
        }
    });
}

Azure Default Credentials

Uses Azure Identity with automatic token management:

Auth = new TrinoAzureDefaultAuth(scope: "https://myapplication/.default")

OAuth Client Credentials

OAuth 2.0 client credentials flow:

Auth = new TrinoOauthClientSecretAuth(
    tokenEndpoint: "login.microsoftonline.com",
    clientId: "client-id",
    clientSecret: "client-secret",
    scope: "https://myscope/")

Custom Authentication

You can implement custom authentication by implementing ITrinoAuth:

public class CustomAuth : ITrinoAuth
{
    public void AuthorizeAndValidate()
    {
        // Initialize or refresh authorization
        // Called when connection is opened
    }

    public void AddCredentialToRequest(HttpRequestMessage request)
    {
        // Add authentication headers or other credentials
        // Called for each request to Trino
        request.Headers.Add("Authorization", "Custom " + GetCredential());
    }
}

Using the custom auth provider:

properties.Auth = new CustomAuth();

When using connection strings, any auth class must be in a loaded assembly to be discovered.

Session Properties

Session properties exist for the duration of the Trino connection. These can be set directly in a SQL command such as USE tpch.sf1 or SET SESSION ..., but you can set and consume these properties directly on the connection.

Example setting session properties:

using (TrinoConnection connection = new TrinoConnection(properties))
{
    connection.ConnectionSession.Properties.Properties.Add("query_cache_enabled", "false");
    connection.ConnectionSession.Properties.Source = "Client name";

    ...
}

Example reading session properties that are set as part of a query:

using (TrinoConnection connection = new TrinoConnection(properties))
{
    using (IDbCommand trinoCommand = new TrinoCommand(connection, "USE tpch.sf10"))
    {
        trinoCommand.ExecuteNonQuery();
    }
    using (IDbCommand trinoCommand = new TrinoCommand(connection, "SET SESSION hive.insert_existing_partitions_behavior = 'OVERWRITE'"))
    {
        trinoCommand.ExecuteNonQuery();
    }
    Console.WriteLine($"Catalog: {connection.ConnectionSession.Properties.Catalog}, Schema: {connection.ConnectionSession.Properties.Schema}");
    Console.WriteLine(string.Join(", ", connection.ConnectionSession.Properties.Properties.Keys.ToArray()));

Output:

Catalog: tpch, Schema: sf10
hive.insert_existing_partitions_behavior

Streaming

Results are consumed from the IDataReader interface. As the rows are consumed Trino will asynchronously produce results which are then asynchronously pulled by the client. The default buffer size is 50MB (defined as MaxTargetResultSizeMB * 10), which means the client will pull in 50MB of data before blocking until those rows are consumed from the client.

To customize the buffer size, provide the byte size of the buffer when creating the reader. If you provide a value of 0, the client will read one page ahead and wait until that page is consumed.

IDataReader idr = trinoCommand.ExecuteReader(1024 * 1024 * 100); // 100 mb