Bulk insert to Snowflake using only REST and SQL commands

user_2740928 0
Viewed 891

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?

snowflake-cloud-data-platform
1 Answers

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();
    }
}

}

user_127563810
Related