14.27. Snowflake Connector
The Snowflake connector allows querying and creating tables in an external Snowflake database. This can be used to join data between different systems like Snowflake and Hive, or between different Snowflake instances.
This connector is bundled in Presto Enterprise and requires a license from Starburst. For more information about Presto Enterprise and the Snowflake connector, or to obtain a free trial, please contact firstname.lastname@example.org.
To configure the Snowflake connector, create a catalog properties file
etc/catalog named, for example,
mount the Snowflake connector as the
There are two flavors of the Snowflake connector -
snowflake-jdbc uses JDBC for all reads and writes and is more efficient
when the result set returned from Snowflake is small.
When larger result sets are extracted from Snowflake, the
connector may be a better choice. Instead of requesting query results over a JDBC
connection, the connector asks Snowflake to export them to S3 and Presto reads them
from there. Since both the write and the read are parallelized, this approach scales
better for large data sets, but has a higher latency.
Create the catalog properties file with the following contents, replacing the
connection properties as appropriate for your setup (for example, replace
<account_name> with the full name of your account, as provided by Snowflake).
connector.name=<snowflake-jdbc or snowflake-distributed> connection-url=jdbc:snowflake://<account_name>.snowflakecomputing.com/ connection-user=<user_name> connection-password=<password> snowflake.warehouse=<warehouse_name> snowflake.database=<database_name>
The role used by Snowflake to execute operations can be specified as
This configuration is optional, and can not be used together with User Impersonation.
Additionally, there are a number of configuration properties that apply only to the distributed connector.
Distributed Connector Configuration Properties
||Name of schema in which stages are created for exporting data.|
||Number of export retries.||3|
||Maximum block size when reading from the export file.||16MB|
||Maximum split size for processing the export file.||64MB|
||Maximum initial split size.||Half of
||Maximum size of files to create when exporting data.||16MB|
The Snowflake connector provides a schema for each Snowflake database.
Assuming the catalog name is
snowflake, you can see the available schemas by running
SHOW SCHEMAS FROM snowflake;
If you have a Snowflake database named
web, you can view the tables
in this database by running
SHOW TABLES FROM snowflake.web;
You can see a list of the columns in the
clicks table in
using either of the following:
DESCRIBE snowflake.web.clicks; SHOW COLUMNS FROM snowflake.web.clicks;
Finally, you can access the
clicks table in
SELECT * FROM snowflake.web.clicks;
Your privileges in these schemas are those of the user configured in the connection properties file. If the user does not have access to these tables, you will not be able to access them.
The Snowflake connector supports User Impersonation. It can be turned on with:
The Snowflake connector supports only table statistics. They are based on
INFORMATION_SCHEMA.TABLES table. Table statistics are
automatically updated by Snowflake.
Table Statistics Configuration Properties
||Enables table statistics.||
||Duration for which table and column statistics are cached.||
||Cache the fact that table statistics are not available.||