Tutorial: Download a Data File

This tutorial provides step-by-step instructions for creating a Web Request job to download a CSV data file.

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:

Create a Network Rule

  1. Access the Admin Console with the owner role of Snoweaver 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_data_access_rule
Allowed Domains:  data.wa.gov
    ../_images/110.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_data_access_integration
Allowed Network Rules:  wa_gov_data_access_rule
    ../_images/23.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.

    ../_images/33.png

    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/43.png

Update the project configuration

  1. Open the Projects page.

  2. If the project LEARN_DEV is not already open, load it from the sidebar.

  3. Specify the configuration as follows:

    ../_images/52.png

    Note

    Your project configuration might differ from the screenshot if you have already updated it following other tutorials.

  4. Save the project.

Create a web request job

  1. Open the LEARN_DEV project from the top menu in Snoweaver with the LEARN_DEV_DEVELOPER role.

  2. Open the Jobs page.

  3. Specify the following general configuration:

    Name:                          wa_gov_data_get_ev_population
Job Type:                      Web Request
Instance Type:                 Procedure
Request Type:                  GET
Response Format:               File
Number of Threads:             4
Auto Compress:                 checked
External Integration:          wa_gov_data_access_integration
Enable URL Query Parameters:   checked
    ../_images/92.png

  4. Use the following Jinja template for specifying the job endpoint URL:

    https://data.wa.gov/api/views/f6w7-q2d2/rows.csv
    ../_images/102.png

  5. Use the following Jinja template for specifying the query parameters:

    {"accesstype":"DOWNLOAD"}

  6. Test the template to validate the output.

    ../_images/10b.png

  7. Use the following Jinja template for specifying the file path:

    ev-data-{{now().isoformat()}}.csv

  8. Test the template to validate the output.

    ../_images/111.png

Test and save the job

  1. Click Preview Request to examine the web request generated by the job.

    ../_images/122.png

  2. Click Make a Test Call to initiate a web request to the specified endpoint URL. The request requires 10 to 20 seconds to process and complete.

  3. The output will indicate the relative path within the DATA stage of the project where the downloaded file has been saved.

    ../_images/132.png

  4. Save the job.

  5. Open a new browser session or tab and log in to the Snowflake console. Navigate to Snoweaver -> LEARN_DEV -> Stages -> DATA using the LEARN_DEV_DEVELOPER role.

  6. Locate and verify the newly generated file within the wa_gov_data_get_ev_population_test_call folder.

    ../_images/141.png

Execute the job

  1. Return to the Jobs page within the LEARN_DEV Project.

  2. Click Build to build a job instance.

    ../_images/73.png

  3. Open a Snowflake worksheet and execute the following SQL statements to call the job instance:

    USE ROLE LEARN_DEV_DEVELOPER;
USE SCHEMA SNOWEAVER.LEARN_DEV;
CALL WA_GOV_DATA_GET_EV_POPULATION();
    ../_images/151.png

  4. Navigate to Snoweaver -> LEARN_DEV -> Stages -> DATA using the LEARN_DEV_DEVELOPER role.

  5. Locate and verify the newly generated file within the wa_gov_data_get_ev_population folder.

    ../_images/161.png

    Note

    Press F5 or ⌘ + R to reload your browser if the folder is not visible.

  6. Return to the Snowflake worksheet and and execute the following SQL statements to analyze the schema of the staged file:

    USE SCHEMA LEARN_SW.DEV;

CREATE OR REPLACE FILE FORMAT MY_CSV_FORMAT_INFER
TYPE = 'csv'
PARSE_HEADER = TRUE
FIELD_OPTIONALLY_ENCLOSED_BY  ='"';


SELECT *
FROM TABLE(
   INFER_SCHEMA(
      LOCATION=>'@SNOWEAVER.LEARN_DEV.DATA/wa_gov_data_get_ev_population/<Staged File Name>'
      , FILE_FORMAT=>'MY_CSV_FORMAT_INFER'
      )
   );

    Note

    Replace <Staged File Name> with the name of the newly generated file.

  7. To query the staged file, you can generate a script as follows:

    CREATE OR REPLACE TEMPORARY FILE FORMAT MY_CSV_FORMAT
TYPE = 'csv'
SKIP_HEADER = 1
FIELD_OPTIONALLY_ENCLOSED_BY  ='"';

SELECT
   $1::TEXT AS "VIN (1-10)",
   $2::TEXT AS "County",
   $3::TEXT AS "City",
   $4::TEXT AS "State",
   $5::NUMBER(5, 0) AS "Postal Code",
   $6::NUMBER(4, 0) AS "Model Year",
   $7::TEXT AS "Make",
   $8::TEXT AS "Model",
   $9::TEXT AS "Electric Vehicle Type",
   $10::TEXT AS "Clean Alternative Fuel Vehicle (CAFV) Eligibility",
   $11::NUMBER(3, 0) AS "Electric Range",
   $12::NUMBER(6, 0) AS "Base MSRP",
   $13::NUMBER(2, 0) AS "Legislative District",
   $14::NUMBER(9, 0) AS "DOL Vehicle ID",
   $15::TEXT AS "Vehicle Location",
   $16::TEXT AS "Electric Utility",
   $17::NUMBER(11, 0) AS "2020 Census Tract",
FROM @SNOWEAVER.LEARN_DEV.DATA/wa_gov_data_get_ev_population/<Staged File Name>
(FILE_FORMAT => MY_CSV_FORMAT);
    ../_images/181.png