Fetching Query Results From Snowflake Just Got a Lot Faster With Apache Arrow

February 12, 2020 Harsha Kapre

We took our first step toward the adoption of Apache Arrow with the release of our latest JDBC and Python clients. Fetching result sets over these clients now leverages the Arrow columnar format to avoid the overhead previously associated with serializing and deserializing Snowflake data structures which are also in columnar format.

This means you can fetch result sets much faster while conserving memory and CPU resources. If you work with Pandas DataFrames, the performance is even better with the introduction of our new Python APIs, which download result sets directly into a Pandas DataFrame. Internal tests show an improvement of up to 5x for fetching result sets over these clients, and up to a 10x improvement if you download directly into a Pandas DataFrame using the new Python client APIs. 

Performance Benchmarks

The following charts show the results of some of our internal benchmarks comparing client driver versions that use the new Arrow format to previous versions that did not:

Figure 1. JDBC fetch performance benchmark for JDBC client version 3.11.0 versus 3.9.x

Figure 2. Python fetch performance benchmark for Python client version 2.1.1 versus 2.0.x

Figure 3. Pandas fetch performance benchmark for the pd.read_sql API versus the new Snowflake Pandas fetch_pandas_all API

Getting Started with the JDBC Client

Download and install the latest Snowflake JDBC client (version 3.11.0 or higher) from the public repository and leave the rest to Snowflake. (Note: The most recent version is not always at the end of the list. Versions are listed alphabetically, not numerically. For example, 3.10.x comes after 3.1.x, not after 3.9.x.)

You must use JDBC version 3.11.0 or higher to take advantage of this feature. Check our Client Change Log for more details.

Getting Started with the Python Client

Download the latest version of the Snowflake Python client (version 2.2.0 or higher). 

To take advantage of the new Python APIs for Pandas, you will need to do the following:

  1. Ensure you have met the following requirements:
    • Snowflake Connector 2.2.0 (or higher) for Python, which supports the Arrow data format that Pandas uses
    • Python 3.5, 3.6, or 3.7
    • Pandas 0.25.2 (or higher); earlier versions may work but have not been tested
    • pip 19.0 (or higher)
  2. Install the Pandas-compatible version of the Snowflake Connector for Python:
pip install snowflake-connector-python[pandas]

     3. Use the new APIs:

Example:

ctx = snowflake.connector.connect(
          host=host,
          user=user,
          password=password,
          account=account,
          warehouse=warehouse,
          database=database,
          schema=schema,
          protocol='https',
          port=port)
# Create a cursor object.
cur = ctx.cursor()
# Execute a statement that will generate a result set.
sql = "select * from t"
cur.execute(sql)
# Fetch the result set from the cursor and deliver it as the Pandas DataFrame.
df = cur.fetch_pandas_all()
# ...

Example:

ctx = snowflake.connector.connect(
          host=host,
          user=user,
          password=password,
          account=account,
          warehouse=warehouse,
          database=database,
          schema=schema,
          protocol='https',
          port=port)
 
# Create a cursor object.
cur = ctx.cursor()
 
# Execute a statement that will generate a result set.
sql = "select * from t"
cur.execute(sql)
 
# Fetch the result set from the cursor and deliver it as the Pandas DataFrame.
for df in cur.fetch_pandas_batches():
    my_dataframe_processing_function(df)
 
# ...

Refer to the following page for more details.

Next Steps

We are excited to take this first step and will be working to implement Apache Arrow with our remaining clients (ODBC, Golang, and so on) over the next few months.

 

The post Fetching Query Results From Snowflake Just Got a Lot Faster With Apache Arrow appeared first on Snowflake.

Previous Article
Using SSO Between Power BI and Snowflake
Using SSO Between Power BI and Snowflake

Customers love Snowflake Cloud Data Platform for its secure and easy access to any data with infinite scala...

Next Article
Tips for Optimizing the Data Vault Architecture on Snowflake
Tips for Optimizing the Data Vault Architecture on Snowflake

Data Vault is an architectural approach that includes a specific data model design pattern and methodology ...

×

Subscribe to email updates from the Snowflake Blog

You're subscribed!
Error - something went wrong!