Bulk insert to Snowflake using only REST and SQL commands

Question

I am using Snowflake database and HiQ FRENDS integration platform. There isn't (as far as I know) Snowflake addons for FRENDS. I have sbnowflake ODBC driver installed on Windows platform where Frends server is running.

I am looking for a way to make bulk upload to from FRENDS to Snowflake. I've understood that I should upload my data file to stage area in Snowflake and then use COPY command to append it to DB. But I haven't find way to upload my data to stage through REST API (or any other method). Only documentation I've found related to this topic is Snowpipes (https://docs.snowflake.com/en/user-guide/data-load-snowpipe-rest-apis.html), but I'd prefer to not require pipes in my process. I know it is possible, because some software, for example Alteryx can do bulk upload without pipes in target database.

Does anyone find way to upload structured JSON/CSV data up Snowflake for bulk insert without calling external binaries such as put command?

Is this a Snowflake or a FRENDS question? I couldn't find much info about FRENDS — Felipe Hoffa
Alteryx is using the Snowflake ODBC driver or the Simba ODBC driver for Snowflake. The standard method to bulk load files is to use the Python connector or ODBC/JDBC driver depending on platform and language preferred The Python connector and ODBC/JDBC drivers have PUT file capability. The JDBC driver also has another file upload method. There's also a .NET driver, but it does not currently have put capability. — Greg Pavlik
@FelipeHoffa: More about Snowflake, I suppose. If I knew how to upload file with Curl, I'd know how to do it with FRENDS. And it is pity that there isn't tag for Frends, it is nice piece of software. Once I get enought reputation I'll add it. — ex4
@GregPavlik thank you for the information. So ODBC driver has all capability I'd need and I don't need to upload files separately with API. — ex4
Yes, the ODBC driver supports the PUT command to put the files to a stage. I'll add a code sample in C#. — Greg Pavlik

Greg Pavlik Greg Pavlik · Accepted Answer · 2021-02-13T15:16:36

The Snowflake ODBC driver supports the PUT command to upload files to internal stages https://docs.snowflake.com/en/sql-reference/sql/put.html#put

You can get the ODBC driver here https://sfc-repo.snowflakecomputing.com/odbc/index.html

You can then use the PUT command as you would any other SQL command through the ODBC driver using standard methods. Note that when the ODBC put fails the message that the ODBC driver provides is generic. You can get better information by going to the Snowflake History tab in the web UI to get a more detailed error message.

    static void Main(string[] args)
    {
        Console.WriteLine("Connecting to Snowflake...");

        string connetionString = null;
        OdbcConnection cnn;
        connetionString = "Driver={SnowflakeDSIIDriver};Server=XXXXXXXXXXXXX.snowflakecomputing.com;Database=TEST;UID=greg;PWD=*******";
        cnn = new OdbcConnection(connetionString);

        try
        {
            cnn.Open();
            Console.WriteLine("Connected.");

            String putCmd = "put file://C:\\Users\\greg\\Desktop\\MyFile.csv @TEST.PUBLIC.MY_STAGE";

            Console.WriteLine(putCmd);

            OdbcCommand cmd = new OdbcCommand(putCmd, cnn);
            OdbcDataReader rs = cmd.ExecuteReader();

            if (rs.HasRows)
            {
                while (rs.Read())
                {
                    Console.WriteLine("{0}\t{1}\t{2}\t{3}", 
                        rs.GetString(0), rs.GetString(1), rs.GetString(2), rs.GetString(3));
                }
            }
            else
            {
                Console.WriteLine("No rows found.");
            }
            rs.Close();
            cnn.Close();
        }
        catch (Exception e)
        {
            Console.Write("Error: " + e.Message);
        }
        Console.ReadKey();
    }
}

}

Bulk insert to Snowflake using only REST and SQL commands

1 Answers