Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
SnowflakeClientExtensions
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;
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment