Tutorial: Retrieve data using a connection

This tutorial provides step-by-step instructions to retrieve data using a connection in Snoweaver.

The example used in this tutorial is the Electric Vehicle Population Data file, provided by DATA.GOV.

Prerequisites

Before continuing with this tutorial, ensure you have completed the following prerequisites:

  1. Open a Snowflake worksheet and execute the following SQL statements using a role with the necessary privileges:

    CREATE WAREHOUSE IF NOT EXISTS LEARN_SW_WH WAREHOUSE_SIZE=XSMALL AUTO_SUSPEND=60;
CREATE ROLE IF NOT EXISTS LEARN_DEV_DEVELOPER;
GRANT USAGE ON WAREHOUSE LEARN_SW_WH TO ROLE LEARN_DEV_DEVELOPER;
GRANT ROLE LEARN_DEV_DEVELOPER TO USER <Your User Account>;

Create a Network Rule

  1. Access the Snoweaver application with the owner role or a custom role with the SNOWEAVER.APP_ADMIN application role.

  2. Open the Network Rules page.

  3. Apply the following configuration to create a new network rule:

    Name:             wa_gov_access_rule
Allowed Domains:  data.wa.gov
    ../_images/11.png

  4. Save the Network Rule.

Create an external integration

  1. Open the External Integrations page.

  2. Apply the following configuration to create a new external integration:

    Name:                   wa_gov_access_integration
Allowed Network Rules:  wa_gov_access_rule
    ../_images/21.png

  3. Click Generate Script to generate a SQL script that creats a new Snowflake External Access Integration and grants Snoweaver access.

  4. Run the Script in a Snowflake worksheet. Ensure that the executing role has the CREATE INTEGRATION privilege, such as the AccountAdmin role.

    Note

    Currently, Snowflake does not support native applications requesting permissions to create integrations on consumer accounts. This step may be automated in the future should this restriction be lifted.

  5. Return to Snoweaver and register the new external access integraiton via the sidebar

    ../_images/4.png

Create a new connection

  1. Open the Connections page.

  2. Apply the following configuration:

    Name:                         wa_gov
Base URL:                     https://data.wa.gov/
External Access Integration:  wa_gov_access_integration
    ../_images/5.png

  3. Save the connection.

Retrieve JSON data using the request function

  1. Open a Snowflake worksheet and execute the following SQL statements with the owner role or a custom role with the SNOWEAVER.APP_ADMIN application role:

    GRANT APPLICATION ROLE SNOWEAVER.WA_GOV_OPERATOR TO ROLE LEARN_DEV_DEVELOPER;

  2. Execute the following SQL statements to retrieve and output the JSON data:

    USE ROLE LEARN_DEV_DEVELOPER;
USE SCHEMA SNOWEAVER.WA_GOV;

-- Preview the content of the request
select request(
    type=>'get',
    endpoint=>'resource/f6w7-q2d2.json',
    preview=>true
):data::varchar;

-- Retrieve the data and store it in a temporary table
create or replace temporary table ev_data as
select request('get','resource/f6w7-q2d2.json') as response;

-- Analyze the response
select
    response:success as success,
    response:data:status_code as status_code,
    response:data:payload as payload,
    response:data:headers as headers
from ev_data;
    ../_images/6.png

  3. Execute the following SQL statements to retrieve CSV data and save it as a file in the stage DATA:

    -- Retrieve the data and save it as a file in the stage DATA
call request(
   type=>'get',
   endpoint=>'api/views/f6w7-q2d2/rows.csv',
   parameters=>{'accesstype':'DOWNLOAD'},
   filepath=>'wa_gov/ev.csv'
);

list @data;
    ../_images/7.png