How to upload Query Result from Snowflake to S3 Directly?

Question

I have a Query Interface where the user writes a SQL Query and Gets Result, The warehouse we use is Snowflake to Query Data and display the Queried SQL Result. We use Snowflake JDBC to establish a connection, Asynchronously Queue the Query get a Query ID(UUID) from snowflake and use the Query ID to get status and fetch the Result.

Sample Code:

try {
            ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
            int numColumns = resultSetMetaData.getColumnCount();

            for (int i = 1; i <= numColumns; i++) {
                arrayNode.add(objectMapper.createObjectNode().put("name", resultSetMetaData.getColumnName(i))
                        .put("attribute_number", i)
                        .put("data_type", resultSetMetaData.getColumnTypeName(i))
                        .put("type_modifier", (Short) null)
                        .put("scale", resultSetMetaData.getScale(i)).put("precision",
                                resultSetMetaData.getPrecision(i)));
            }
            rootNode.set("metadata", arrayNode);
            arrayNode = objectMapper.createArrayNode();
            while (resultSet.next()) {
                ObjectNode resultObjectNode = objectMapper.createObjectNode();
                for (int i = 1; i <= numColumns; i++) {
                    String columnName = resultSetMetaData.getColumnName(i);
                    resultObjectNode.put(columnName, resultSet.getString(i));
                }
                arrayNode.add(resultObjectNode);
            }
            rootNode.set("results", arrayNode);
            // TODO: Instead of returning the entire result string, send it in chunk to S3 utility class for upload
            resultSet.close();
            jsonString = objectMapper.writeValueAsString(rootNode);
        }

As you can see here our use case is we need to send the metadata info(column details) along with the result. The result set is then uploaded to S3 and users are given a S3 link to view the results.

I am trying to figure if this scenario can be handled in Snowflake itself, where snowflake can generate the metadata for the query and upload the result set to a user-defined bucket SO that consumers of Snowflake won't have to do this. I have read about Snowflake Stream, Copy from Stages. Can someone help me understand if this is feasible and if yes how this can be achieved?
Is there any way where I can upload the result of a Query using QueryId from snowflake to S3 directly without fetching and uploading it to S3.

user_12756381 · Answer

You can store the results in an S3 bucket using the COPY command. This is a simplified example showing the process on a temporary internal stage. For your use case, you would create and use an external stage in S3:

create temp stage FOO;
select * from "SNOWFLAKE_SAMPLE_DATA"."TPCH_SF1"."NATION";
copy into @FOO from (select * from table(result_scan(last_query_id())));

The reason you want to use COPY from a previous select is that the COPY command is somewhat limited in what it can use for the query. By running the query as a regular select first and then running a select * from that result, you get past those limitations.

The COPY command supports other file formats. This way will use the default CSV format. You can also specify JSON, Parquet, or a custom delimited format using a named file format.

https://docs.snowflake.com/en/sql-reference/sql/copy-into-location.html

How to upload Query Result from Snowflake to S3 Directly?

1 Answers