Skip to content

Latest commit

 

History

History
248 lines (195 loc) · 10.1 KB

README.md

File metadata and controls

248 lines (195 loc) · 10.1 KB
Raw

Snowflake.Data.Xt ❄️

Nuget package for fluent snowflake access.

Usage

dotnet add package Snowflake.Data.Xt

Example

This package provides multiple attributes and extension methods to try to make it easier querying data from snowflake.
This is an example for the table EXAMPLE_COST joined with COST_CENTER.
It uses a left join. Check the attribute implementation for all join options.

Example.cs

using System;
using Snowflake.Data.Xt;

namespace SnowflakeApplication;

// Define the main table to read from. Attribute is only allowed once.
[SnowflakeTable(
  name: "EXAMPLE_COST",     // Name is optional. If not provided, it would be parsed from the class name.
  alias: "cost")]           // Alias has to be set for joins, otherwise is optional.
// Define a table to join from.
[SnowflakeJoin(
  table: "COST_CENTER",              // The table to join from.
  alias: "costCenter",               // The alias to use.
  type: SnowflakeJoinAttribute.Left, // The join type (Cross, FullOuter, Inner (default), Left, LeftOuter, Natural, Right or RightOuter).
  condition: "cost.COST_CENTER_ID = costCenter.ID")] // The join condition, aliases have to be used.
public class Example
{
  [SnowflakeColumn(name: "VALUE")] // Defines the column to map from. Uses the default table if not provided otherwise.
  public decimal Value { get; set; }

  [SnowflakeColumn(name: "ADDRESS", table: "COST_CENTER")] // Uses the joined table.
  public string Address { get; set; }

  [SnowflakeColumn] // If no name is provided, it will be parsed from the property name. Here: IS_READY
  public bool IsReady { get; set; }
}

ExamplesRepository.cs

using System.Collections.Generic;
using System.Data;
using System.Threading;
using System.Threading.Tasks;
using Snowflake.Data.Xt;

namespace SnowflakeApplication;

public class ExamplesRepository : IExamplesRepository
{
  public async Task<IList<Example>> GetByAddressAsync(string address, CancellationToken cancellationToken = default)
  {
    var snowflakeCommand = new SnowflakeCommand<Example>() // Creates a default select statement
      .Select(item => item.Value)               // Only select the value
      .Where(item => item.Address == address)   // Filter for the address
      .OrderByAsc(item => item.Value);          // Sort by value ascending

    var list = await snowflakeCommand
      .ToListAsync(                             // Returns a list
        new List<(string, DbType, object)> { }, // Parameter list if required, here empty
        cancellationToken)
      .ConfigureAwait(false);

    return list;
  }
}

You can also join a table on itself (if you provide an table alias):

using System;
using Snowflake.Data.Xt;

namespace SnowflakeApplication;

[SnowflakeTable(
  name: "COST_CENTER",
  alias: "costCenter1")]
[SnowflakeJoin(
  table: "COST_CENTER",
  alias: "costCenter2",
  type: SnowflakeJoinAttribute.Left,
  condition: "costCenter1.COST_CENTER_ID = costCenter2.COST_CENTER_ID")]
public class Example
{
  [SnowflakeColumn(name: "VALUE")]
  public decimal Value { get; set; }

  [SnowflakeColumn(name: "ADDRESS", table: "COST_CENTER")]
  public string Address { get; set; }

  [SnowflakeColumn(name: "IS_READY", table: "COST_CENTER", tableAlias: "costCenter2")] // Alias must be provided
  public bool IsReady { get; set; }
}

... and reuse same column names (if you provide an column alias):

using System;
using Snowflake.Data.Xt;

namespace SnowflakeApplication;

[SnowflakeTable(
  name: "COST_CENTER",
  alias: "costCenter1")]
[SnowflakeJoin(
  table: "COST_CENTER",
  alias: "costCenter2",
  type: SnowflakeJoinAttribute.Left,
  condition: "costCenter1.COST_CENTER_ID = costCenter2.COST_CENTER_ID")]
public class Example
{
  [SnowflakeColumn(name: "VALUE")]
  public decimal Value { get; set; }

  [SnowflakeColumn(name: "ADDRESS", table: "COST_CENTER")]
  public string Address { get; set; }

  [SnowflakeColumn(name: "ADDRESS", columnAlias: "ADDRESS2", table: "COST_CENTER", tableAlias: "costCenter2")] // Table and column alias must be provided
  public string AddressMapped { get; set; }
}

You can also reuse a snowflake database connection:

using System.Data;
using System.Threading;
using System.Threading.Tasks;
using Snowflake.Data.Client;
using Snowflake.Data.Xt;

namespace SnowflakeApplication;

public class ExamplesRepository : IExamplesRepository
{
  public async Task<Owner> GetByAddressAsync(string address, CancellationToken cancellationToken = default)
  {
    // Setup of the snowflake database connection
    using var snowflakeDbConnection = new SnowflakeDbConnection
    {
      ConnectionString = EnvironmentExtensions.GetSnowflakeConnectionString(),
    };

    // If you do not open the connection here, the first command which will run, will open it for you
    await snowflakeDbConnection
      .OpenAsync(cancellationToken)
      .ConfigureAwait(false);

    var example = new SnowflakeCommand<Example>(snowflakeDbConnection) // Provide the snowflake database connection in the constructor
      .Where(item => item.Address == address)
      .FirstOrDefaultAsync(cancellationToken)
      .ConfigureAwait(false);

    var owner = new SnowflakeCommand<Owner>(snowflakeDbConnection) // Provide the snowflake database connection in the constructor and it will be reused
      .Where(item => item.Name == example.OwnerName)
      .FirstOrDefaultAsync(cancellationToken)
      .ConfigureAwait(false);

    // Do not forget to close the connection
    await snowflakeDbConnection
      .CloseAsync(cancellationToken)
      .ConfigureAwait(false);

    return owner;
  }
}

Modifier

You can add multiple modifiers to you command:

Name Parameters Description
GroupBy string text or a predicate Add a group by clause. e.g. GROUP BY PLANR
Having string text Add a having predicate. e.g. HAVING count(*) > 10
IsDistinct - The select statement will be distinct.
OrderBy string text or a predicate Add an order by text. e.g. PLANR ASC
Select a predicate Add a select statement to only query selected columns.
Top int amount The select statement will return the TOP AMOUNT found entries.
Where string text or a predicate Add a where clause text. e.g. PLANR IS NOT NULL AND PLANR LIKE "%test"

Methods

You can query for a single entry or a list - sync or async:

Name Parameters Description
FirstOrDefault IList<(string, DbType, object)>? parameterList = default Query synchron for one entry.
FirstOrDefaultAsync IList<(string, DbType, object)>? parameterList = default, CancellationToken cancellationToken = default Query asynchron for one entry.
ToList IList<(string, DbType, object)>? parameterList = default Query synchron for a list.
ToListAsync IList<(string, DbType, object)>? parameterList = default, CancellationToken cancellationToken = default Query asynchron for a list.

Environment Variables

Name Required Default value Description
SNOWFLAKE_ACCOUNT true null The snowflake account. Also check official docs for .NET connector.
SNOWFLAKE_AUTHENTICATOR true null The method of authentication in snowflake. Also check official docs for .NET connector. Should be snowflake_jwt.
SNOWFLAKE_DATABASE false null The database to connect to.
SNOWFLAKE_LOG_ENABLED false true Whether to log information using serilog or not.
SNOWFLAKE_PRIVATE_KEY_PASSWORD true null The password for the private key. Also check official docs for .NET connector.
SNOWFLAKE_PRIVATE_KEY_FILE true null The path to the private key file. Should be the absolute path to the file. Also check official docs for .NET connector.
SNOWFLAKE_SCHEMA false null The schema to use.
SNOWFLAKE_USER true null The user for the snowflake login. Also check official docs for .NET connector.
SNOWFLAKE_WAREHOUSE false null The warehouse to use.

License

Snowflake.Data.Xt is distributed under the MIT license. See LICENSE for details.

MIT License

Copyright (c) 2023-today Jonas Schubert

Permission is hereby granted, free of charge, to any person obtaining a copy
of this software and associated documentation files (the "Software"), to deal
in the Software without restriction, including without limitation the rights
to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
copies of the Software, and to permit persons to whom the Software is
furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all
copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
SOFTWARE.