SNOW-1646792: Support multiple result sets in a raw Arrow format

[CurtHagenlocher]

What is the current behavior?

When using snowflakeConn.QueryArrowStream it's only possible to return a single result set.

What is the desired behavior?

Either via QueryArrowStream or some new function, it ought to be possible to use WithMultiStatement to execute multiple statements and get multiple results -- just as it is for the row-based interface.

How would this improve gosnowflake?

This is a generally useful scenario, and it would also go towards ultimately enabling apache/arrow-adbc#1358.

[sfc-gh-dszmolka]

hi and thanks for submitting this issue. unfortunately, multi-statement query results returned as JSON are a limitation of the Snowflake backend, and thus affects all our drivers (not just gosnowflake). Same reason as #816.
That said, it is entirely possible of course to submit a feature request for this, but it should be more like targeting the backend.

If you (or anyone reading this, needing the same feature) are already a Snowflake customer or partner, can you please reach out to your account team and let them know you would need Snowflake engine to support returning multi-statement query results as Arrow ?

I'm going to keep this issue open (indefinitely) for tracking, but please be aware that no progress can be made until backend is ready to fully support this. Thank you !

[seanwang051]

hi and thanks for submitting this issue. unfortunately, multi-statement query results returned as JSON are a limitation of the Snowflake backend, and thus affects all our drivers (not just gosnowflake). Same reason as #816. That said, it is entirely possible of course to submit a feature request for this, but it should be more like targeting the backend.

If you (or anyone reading this, needing the same feature) are already a Snowflake customer or partner, can you please reach out to your account team and let them know you would need Snowflake engine to support returning multi-statement query results as Arrow ?

I'm going to keep this issue open (indefinitely) for tracking, but please be aware that no progress can be made until backend is ready to fully support this. Thank you !

Hi, might be a dump question, I was using Snowflake ADO.NET driver which can return mulitple result set, was there any difference between those 2 underlying mechanisms? Please kindly help me understand it

[sfc-gh-dszmolka]

the request is about to be able to return results of a multi-statement query in Arrow format specifically; backend needs to support this first.

[sfc-gh-pfus]

@seanwang051 let me clarify it a bit further. Most drivers work in row based mode - you select data and iterate over them row by row. In that case, multistatements/multiple result sets are available in all drivers. Apart from that, Go driver exposes the API mentioned above, which allows to read native arrow response in columnar manner. This is a thing that is currently supported only by Go and Python drivers (and JDBC is coming in the upcoming future). .NET driver doesn't have such API. In this case, multistatements are not supported by Snowflake backend.

[davidhcoe]

It also appears I can't multiple statements that only have one result when using Arrow. For example, I can't run:

ALTER SESSION SET MULTI_STATEMENT_COUNT = 3;

followed by

CREATE TEMPORARY TABLE temp_table (
    id INT,
    name STRING
);

INSERT INTO temp_table (id, name) VALUES
    (1, 'Alice'),
    (2, 'Bob');

SELECT * FROM temp_table;

I get the error:

JavaScript execution error: Uncaught Execution of multiple statements failed on statement "CREATE TEMPORARY TABLE temp_ta..." (at line 1, position 0). Actual statement count 1 did not match the desired statement count 3. in SYSTEM$MULTISTMT at ' throw `Execution of multiple statements failed on statement {0} (at line {1}, position {2}).`.replace('{1}', LINES[i])' position 4 stackstrace: SYSTEM$MULTISTMT line: 10

So for some reason it still sees the request as a single statement, but I still would only have one actual result.

[sfc-gh-dszmolka]

This is very interesting @davidhcoe - and surely doesn't come from this library (gosnowflake) where this issue is raised, but the backend, Snowflake itself. If this is a problem for you, could you please raise it with Snowflake Support ?

As a mitigation, you can try using ALTER SESSION SET MULTI_STATEMENT_COUNT = 0; to allow for an undefined number of multi-statement count.

[davidhcoe]

That works @sfc-gh-dszmolka in the sense that it doesn't fail, but the only result I get back in this case is a string that says "Multiple statements executed successfully." where I would expect to get 2 results that include Bob and Alice.

[davidhcoe]

Circling back - any updates on this?

[sfc-gh-dszmolka]

since this is a backend change, i would not expect any progress in the coming quarter. Even after backend change is implemented, we need to revisit this item to adjust driver if needed. But that won't be too long - the longer period is getting this into the backend.

So there won't be any updates on this item probably, for the foreseeable future, and i will be happy to be wrong (and then update this issue of course). Hoping you folks already reached out to your account team or partner sales engineer or your 'Snowflake person', to express how important this would be to implement in the backend? If not, i would highly recommend it, because it help things getting prioritized. Thank you in advance !