CODEWITHSUNDEEP
snowflake-cloud-data-platform
Luke Adams
Luke Adams

Reputation: 13

Snowflake ODBC driver vs SQL API

What are the main differences between connecting our RoR application to Snowflake using the ODBC driver vs SQL API?

The main use case is for read only access to run various custom queries against a few tables.

We've prototyped both connections. Both work well. ODBC appears to be faster when running simple queries.

One use case is to execute ~10 queries in one request. ODBC requires us to execute 10 separate SQL statements. While the SQL API allows us to submit the queries together, but then requires an additional API call for each statementHandle to get the results. The API calls are fast, but that's still 11 API calls.

Is ODBC the obvious choice here? What if ~10 queries grows to 50-100? What if the result set is 50-100k+ rows of data? I do see how SQL API partitions the results. That might come in handy. Not sure how ODBC handles that offhand.

Other thoughts on security, performance, etc to think about?

Thanks!

Upvotes: 1

Views: 1512

Answers (1)

Tom Meacham
Tom Meacham

Reputation: 191

First, it is possible to send a batch of statements with ODBC a single request: Executing a Batch of SQL Statements (Multi-Statement Support)

Other than that there are a few differences that might not matter for your use case. For example, the ODBC driver returns results in the Apache Arrow format. While the REST API will return the first partition of the results in JSON and subsequent partitions in gzipped JSON.

The rest api has some limitations largely based on the fact the REST API cannot maintain a session across requests and a few other quirks like not being able to PUT files into a stage (including unstructured).

The ODBC driver (and other official connectors) will always have access to any command on Snowflake because they can maintain a session. So it will ultimately come down to personal preference and what your app needs to do.

Another thing to consider is the ODBC driver has years of development and the SQL API is relatively new. (Though by all accounts, works great)

Limitations of the SQL API from the docs.

The following commands are not supported:

  • The PUT command (in Snowflake SQL)

  • The GET command (in Snowflake SQL)

  • The CALL command with stored procedures that return a table (stored procedures with the RETURNS TABLE clause).

The following commands and statements are supported only within a request that specifies multiple statements:

Commands that perform explicit transactions, including:

  • BEGIN
  • COMMIT
  • ROLLBACK

Commands that change the context of the session, including:

  • USE

  • ALTER SESSION

  • Statements that set session variables.

  • Statements that create temporary tables and stages (tables and stages that are available only in the current session).

Upvotes: 1

Related Questions