ContextQMD
Back to Trino

Snowflake connector

docs/src/main/sphinx/connector/snowflake.md

4809.1 KB
Original Source

Snowflake connector

{raw}

The Snowflake connector allows querying and creating tables in an external Snowflake account. This can be used to join data between different systems like Snowflake and Hive, or between two different Snowflake accounts.

Configuration

To configure the Snowflake connector, create a catalog properties file in etc/catalog named, for example, example.properties, to mount the Snowflake connector as the snowflake catalog. Create the file with the following contents, replacing the connection properties as appropriate for your setup:

none
connector.name=snowflake
connection-url=jdbc:snowflake://<account>.snowflakecomputing.com
connection-user=root
connection-password=secret
snowflake.account=account
snowflake.database=database
snowflake.role=role
snowflake.warehouse=warehouse

The Snowflake connector uses Apache Arrow as the serialization format when reading from Snowflake. Add the following required, additional JVM argument to the :

none
--add-opens=java.base/java.nio=ALL-UNNAMED
--sun-misc-unsafe-memory-access=allow

Multiple Snowflake databases or accounts

The Snowflake connector can only access a single database within a Snowflake account. Thus, if you have multiple Snowflake databases, or want to connect to multiple Snowflake accounts, you must configure multiple instances of the Snowflake connector.

{include}
{include}
{include}
{include}

% snowflake-type-mapping:

Type mapping

Because Trino and Snowflake each support types that the other does not, this connector {ref}modifies some types <type-mapping-overview> when reading or writing data. Data types may not map the same way in both directions between Trino and the data source. Refer to the following sections for type mapping in each direction.

List of Snowflake data types.

Snowflake type to Trino type mapping

The connector maps Snowflake types to the corresponding Trino types following this table:

:::{list-table} Snowflake type to Trino type mapping :widths: 30, 30, 40 :header-rows: 1

    • Snowflake type
    • Trino type
    • Notes
    • BOOLEAN
    • BOOLEAN
    • FLOAT, FLOAT4, FLOAT8
    • DOUBLE
    • The names FLOAT, FLOAT4, and FLOAT8 are for compatibility with other systems; Snowflake treats all three as 64-bit floating-point numbers. See Snowflake data types for floating point numbers for more information.
    • NUMBER
    • DECIMAL
    • Default precision and scale are (38,0).
    • VARCHAR
    • VARCHAR
    • CHAR, CHARACTER
    • VARCHAR
    • Synonymous with VARCHAR except default length is VARCHAR(1). See Snowflake String & Binary Data Types for more information.
    • BINARY
    • VARBINARY
    • DATE
    • DATE
    • TIME
    • TIME
    • TIMESTAMP_NTZ
    • TIMESTAMP
    • TIMESTAMP with no time zone; time zone, if provided, is not stored. See Snowflake Date & Time Data Types for more information.
    • TIMESTAMP
    • TIMESTAMP
    • Alias for one of the TIMESTAMP variations (TIMESTAMP_NTZ by default). This connector always sets TIMESTAMP_NTZ as the variant.
    • TIMESTAMP_TZ
    • TIMESTAMP WITH TIME ZONE
    • TIMESTAMP with time zone. :::

No other types are supported.

Trino type to Snowflake type mapping

The connector maps Trino types to the corresponding Snowflake types following this table:

:::{list-table} Trino type to Snowflake type mapping :widths: 30, 30, 40 :header-rows: 1

    • Trino type
    • Snowflake type
    • Notes
    • BOOLEAN
    • BOOLEAN
    • TINYINT
    • NUMBER(3, 0)
    • SMALLINT
    • NUMBER(5, 0)
    • INTEGER
    • NUMBER(10, 0)
    • BIGINT
    • NUMBER(19, 0)
    • REAL
    • DOUBLE
    • DOUBLE
    • DOUBLE
    • DECIMAL
    • NUMBER
    • VARCHAR
    • VARCHAR
    • CHAR
    • VARCHAR
    • VARBINARY
    • BINARY
    • VARBINARY
    • VARBINARY
    • DATE
    • DATE
    • TIME
    • TIME
    • TIMESTAMP
    • TIMESTAMP_NTZ
    • TIMESTAMP WITH TIME ZONE
    • TIMESTAMP_TZ

:::

No other types are supported.

{include}

(snowflake-sql-support)=

SQL support

The connector provides read access and write access to data and metadata in a Snowflake database. In addition to the globally available and read operation statements, the connector supports the following features:

  • , see also

(snowflake-insert)=

{include}

(snowflake-procedures)=

Procedures

{include}
{include}

(snowflake-table-functions)=

Table functions

The connector provides specific table functions to access Snowflake.

(snowflake-query-function)=

query(varchar) -> table

The query function allows you to query the underlying database directly. It requires syntax native to Snowflake, because the full query is pushed down and processed in Snowflake. This can be useful for accessing native features which are not available in Trino or for improving query performance in situations where running a query natively may be faster.

Find details about the SQL support of Snowflake that you can use in the query in the Snowflake SQL Command Reference, including PIVOT, lateral joins and other statements and functions.

{include}

As a simple example, query the example catalog and select an entire table:

SELECT
  *
FROM
  TABLE(
    example.system.query(
      query => 'SELECT
        *
      FROM
        tpch.nation'
    )
  );

As a practical example, you can use the Snowflake SQL support for PIVOT to pivot on all distinct column values automatically with a dynamic pivot.

SELECT
  *
FROM
  TABLE(
    example.system.query(
      query => '
        SELECT *
        FROM quarterly_sales
          PIVOT(SUM(amount) FOR quarter IN (ANY ORDER BY quarter))
        ORDER BY empid;
      '
    )
  );
{include}

Performance

The connector includes a number of performance improvements, detailed in the following sections.

(snowflake-pushdown)=

Pushdown

The connector supports pushdown for a number of operations:

{ref}Aggregate pushdown <aggregation-pushdown> for the following functions:

  • {func}avg
  • {func}count
  • {func}max
  • {func}min
  • {func}sum
  • {func}stddev
  • {func}stddev_pop
  • {func}stddev_samp
  • {func}variance
  • {func}var_pop
  • {func}var_samp
  • {func}covar_pop
  • {func}covar_samp
  • {func}corr
  • {func}regr_intercept
  • {func}regr_slope
{include}