Skip to content

Instantly share code, notes, and snippets.

Avatar

Jess Panni jesspanni

  • endjin
View GitHub Profile
View SnowflakeClient.cs
/// <summary>
/// A client for submitting queries to Snowflake.
/// </summary>
public class SnowflakeClient
{
private readonly string connectionString;
/// <summary>
/// Initializes a new instance of the <see cref="SnowflakeClient"/> class.
/// </summary>
@jesspanni
jesspanni / SnowflakeClientExtensions.cs
Last active Apr 24, 2019
SnowflakeClientExtensions
View 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());
View batchsql.sql
USE DATABASE SALES;
USE SCHEMA SALES;
SELECT * FROM LINEITEMS;
View 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());
View UnloadCommand.cs
public class UnloadCommand
{
public string Database { get; set; }
public string Schema { get; set; }
public string Warehouse { get; set; }
public string Stage { get; set; }
public string Query { get; set; }
public string FilePrefix { get; set; }
public bool SingleFile { get; set; }
public bool Overwrite { get; set; }
View LoadCommand.cs
public class LoadCommand
{
public string Database { get; set; }
public string Schema { get; set; }
public string Warehouse { get; set; }
public string Stage { get; set; }
public string TargetTable { get; set; }
public string[] Files { get; set; }
public bool Force { get; set; }
@jesspanni
jesspanni / LoadFunction.cs
Created Apr 24, 2019
SnowflakeLoadAzureFunction
View LoadFunction.cs
[FunctionName("Load")]
public static async Task<HttpResponseMessage> Run(
[HttpTrigger(AuthorizationLevel.Function, "post", Route = "v1/load")]
HttpRequestMessage req,
ILogger logger,
ExecutionContext context)
{
ServiceProvider serviceProvider = Initializer.Initialize(context);
string body = await req.Content.ReadAsStringAsync().ConfigureAwait(false);
@jesspanni
jesspanni / UnloadFunction.cs
Created Apr 24, 2019
SnowflakeUnloadFunction
View UnloadFunction.cs
public static async Task<HttpResponseMessage> Run(
[HttpTrigger(AuthorizationLevel.Function, "post", Route = "v1/unload")]
HttpRequestMessage req,
ILogger logger,
ExecutionContext context)
{
ServiceProvider serviceProvider = Initializer.Initialize(context);
string body = await req.Content.ReadAsStringAsync().ConfigureAwait(false);
UnloadCommand command = JsonConvert.DeserializeObject<UnloadCommand>(body);
@jesspanni
jesspanni / adf-snowflake.sql
Last active Apr 25, 2019
SnowflakeADFConnectorDatabaseSetup
View adf-snowflake.sql
CREATE DATABASE IF NOT EXISTS "ADF-DB";
USE DATABASE "ADF-DB";
CREATE SCHEMA IF NOT EXISTS "SALES";
USE SCHEMA "SALES";
CREATE TABLE IF NOT EXISTS LINEITEM (
L_ORDERKEY NUMBER(38,0),
@jesspanni
jesspanni / input.csv
Created Apr 25, 2019
adf-snowflake-sample-data
View input.csv
2400001 132304 4818 1 10.00 13363.00 0.03 0.02 N O 2016-06-30 2016-06-25 2016-07-17 NONE SHIP egular dolphins1
2400001 24513 2020 2 14.00 20125.14 0.04 0.03 R F 2016-05-01 2016-05-05 2016-05-10 COLLECT COD SHIP es wake quickly bold packages. sly idea1
2400001 175232 7750 3 18.00 23530.14 0.01 0.07 N O 2016-07-20 2016-05-06 2016-08-02 NONE REG AIR ic deposits. carefully blithe packages1
2400001 119658 4681 4 2.00 3355.30 0.09 0.08 A F 2016-05-15 2016-06-17 2016-05-28 DELIVER IN PERSON REG AIR hinder slyly quickly silent deposi1
2400001 89532 4549 5 13.00 19779.89 0.07 0.03 A F 2016-04-08 2016-06-27 2016-04-12 COLLECT COD REG AIR ges believe slyly furiously bold courts1
2400002 188783 3820 1 14.00 26204.92 0.09 0.01 R F 2015-09-13 2015-09-25 2015-10-06 TAKE BACK RETURN MAIL around the slyly ironi1
2400002 67505 5024 2 1.00 1472.50 0.00 0.04 R F 2015-11-14 2015-10-30 2015-12-07 COLLECT COD REG AIR ding depen1
2400002 142916 2917 3 7.00 13712.37 0.03 0.04 R F 2015-08-29 2015-09-29 2015-09-17 NONE MAIL sits are