Model Data in Snowflake Data Warehouse

Configure your on-premises environment so that you can model data in a Snowflake database.

You can model Snowflake data in Semantic Modeler or Model Administration Tool. This task describes the process using Model Administration Tool.
Both local and remote connections to Snowflake from a semantic model require an installation of Data Gateway in order for the Model Administration Tool to import and model tables from a Snowflake data source. Configure the Data Gateway agent with a suitable driver to connect to Snowflake. Make sure that remote Data Gateway connections are available when queries are run. However, once the Snowflake tables are modeled and the semantic model is published to Oracle Analytics, for local connections, Data Gateway can be disabled or removed as it isn't used when queries are run from Oracle Analytics.

You can create either a local or a remote connection to Snowflake from the semantic model. When creating a local (not remote) connection, the connection pool in the semantic model uses a JDBC connection.

Before you start, install Data Gateway and Model Administration Tool on the same Windows computer in your on-premises environment.
  1. Configure a local Data Gateway agent to facilitate a connection from the Developer Client Tool to Snowflake.
    1. Download the latest Snowflake JDBC driver (for example, in file snowflake-jdbc-3.9.0.jar).
    2. Copy the downloaded JAR file to the Data Gateway installation folder.
      In a server deployment, copy the JAR file into:
      <Data Gateway folder>/domain/jettybase/lib/ext
      In a personal deployment, copy the JAR file into:
      <install directory>\war\datagateway\WEB-INF\lib
    3. Re-start Data Gateway.
  2. Configure a connection to your Snowflake database.

Create a Local Semantic Model Connection to Snowflake

Connect to a local Snowflake database so that you can model Snowflake data.

  1. In Model Administration Tool, enable the JDBC connection pool functionality by loading Java data sources. See Step 3 in Configure and Register Data Gateway for Reporting.
  2. In Model Administration Tool, create a database and set the type to Snowflake.
  3. Add a Connection Pools and specify these details on the General tab:
    • Call Interface: JDBC(Direct Driver).
    • Require fully qualified table names: Yes.
    • Data source name: Enter the connection string, this format:jdbc:snowflake://xxxx.snowflakecomputing.com?db=ODEV&warehouse=xxxxxx&schema=xxxxxx
    • RDC Version: Leave this field blank.
  4. On the Miscellaneous tab, specify these details:
    • JDS Server URL: Leave this field blank (remove any entry in this field).
    • Driver Class: net.snowflake.client.jdbc.SnowflakeDriver.
    • Use SQL over HTTP: false.
  5. Model your data using this connection.
  6. Upload or publish your semantic model to Oracle Analytics Cloud when you have completed your model.
    Oracle Analytics Cloud connects to the Snowflake database without using Data Gateway.
You can now model your data using this connection.

Create a Remote Semantic Model Connection to Snowflake

Connect to a local Snowflake database so that you can model Snowflake data.

  1. In Model Administration Tool, enable the JDBC connection pool functionality by loading Java data sources. See Step 3 in Configure and Register Data Gateway for Reporting.
  2. In Model Administration Tool, create a database and set the type to Snowflake.
  3. Add a Connection Pools and specify these details on the General tab:
    • Call Interface: JDBC(Direct Driver).
    • Require fully qualified table names: Yes.
    • Data source name: Enter the connection string, in this format: jdbc:snowflake://xxxx.snowflakecomputing.com?db=ODEV&warehouse=xxxxxx&schema=xxxxxx
    • RDC Version: Set to 2.
  4. On the Miscellaneous tab, specify these details:
    • JDS Server URL: Leave this field blank (remove any entry in this field).
    • Driver Class: net.snowflake.client.jdbc.SnowflakeDriver.
    • Use SQL over HTTP: true.
  5. Model your data using this connection.
  6. Upload or publish the semantic model to Oracle Analytics Cloud when you have completed your model.
    Note: Oracle Analytics Cloud connects to Snowflake using any configured Data Gateway agent.
  7. Copy the Snowflake driver file to each Data Gateway agent installation folder.
    • In a server deployment, copy the JAR file into: <Data Gateway install_location>/domain/jettybase/thirdpartyDrivers.
    • In a personal deployment on Windows, copy the JAR file into: <Data Gateway_extract_path>\thirdpartyDrivers.
    • In a personal deployment on MacOS, copy the JAR file into: <Application->Show Package Contents>Resources->app.nw-> thirdpartyDrivers.
  8. Re-start Data Gateway. See Maintain Data Gateway.