SnowflakeClientExtensions

SnowflakeClientExtensions.cs

public static class SnowflakeClientExtensions
{
    /// <summary>
    /// Instructs Snowflake to load data from a stage into a target table.
    /// </summary>
    public static int Load(this SnowflakeClient client, string stage, string targetTable, string[] files = null, string warehouse = null, string database = null, string schema = null, bool force = false)
    {
        IList<string> commands = DefineSnowflakeQueryContext(warehouse, database, schema);
        commands.Add(LoadSnowflakeCommand(stage, targetTable, files, force));
        return client.ExecuteNonQuery(commands.ToArray());
    }
    /// <summary>
    /// Instructs Snowflake to unload data from a query into a stage.
    /// </summary>  
    public static void Unload(this SnowflakeClient client, string stage, string query, string filePrefix, string warehouse = null, string database = null, string schema = null, bool singleFile = false, bool overwrite = false)
    {
        IList<string> commands = DefineSnowflakeQueryContext(warehouse, database, schema);
        commands.Add(UnloadSnowflakeCommand(stage, query, filePrefix, singleFile, overwrite));
        client.ExecuteNonQuery(commands.ToArray());
    }

    private static string UnloadSnowflakeCommand(string stage, string query, string filePrefix, bool singleFile, bool overwrite)
    {
        filePrefix = filePrefix != null ? "/" + filePrefix?.TrimStart('/') : string.Empty;
        StringBuilder sb = new StringBuilder($"COPY INTO '@{stage}{filePrefix}' FROM ({query})")
            .Append($" SINGLE={singleFile}")
            .Append($" OVERWRITE={overwrite};");
        return sb.ToString();
    }

    private static string LoadSnowflakeCommand(string stage, string targetTable, string[] files, bool force)
    {
        var sb = new StringBuilder($"COPY INTO {targetTable} FROM '@{stage}'");

        if (files?.Length > 0)
        {
            string filesOptions = string.Join(",", files.Select(o => $"'{o.Trim('\'')}'").ToArray());
            sb.Append($" FILES=({filesOptions})");
        }

        sb.Append($" FORCE={force};");

        return sb.ToString();
    }

    private static IList<string> DefineSnowflakeQueryContext(string warehouse, string database, string schema)
    {
        var context = new List<string>();

        if (!string.IsNullOrWhiteSpace(warehouse))
        {
            context.Add($"USE WAREHOUSE \"{warehouse.ToUpper()}\";");
        }

        if (!string.IsNullOrEmpty(database))
        {
            context.Add($"USE DATABASE \"{database.ToUpper()}\";");
            if (!string.IsNullOrEmpty(schema))
            {
                context.Add($"USE SCHEMA \"{schema.ToUpper()}\";");
            }
        }

        return context;
    }