government_hackathon

GOVERNMENT HACKATHON HANDS ON LAB DAY 1

Thank you for attending day one of the Government Hackathon. Today, we will be guiding you though snowflake which will help you get ‘armed’ for day 2. At the end of the day you will experience the following

These are the the key aspects in order to ensure you will make the most of the hackathon during Day 2

Set up a free trial account in AWS London

Log into Snowflake

Welcome to Snowflake. Before we can continue with the lab, we will be sharing you a private data share. in order to do this, we will need the account identifier of you newly created account.

I will share all the datasets with your newly created trial account during your overview session.

Enjoy your overview session

1 Create a Streamlit app

1.1 Setup

Today we will go though a working example of how data sharing can allow you to make better decisions. We will be going through how to create a policy simulator in order to estimate the impact of changing the cold weather payment policy. The application in its entirety could have been fully packaged as a native app. However, for this example, you will be manually loading the components just incase you would like to reuse any of the source code on day 2.

alt text

Creating your own database

You will now create a database to host all the assets needed to try out the simulator.


--------initial setup for simulator -------------


CREATE OR REPLACE DATABASE POLICY_CHANGE_SIMULATOR_STREAMLIT;

CREATE OR REPLACE WAREHOUSE POLICY_CHANGE_SIMULATOR_WH WITH WAREHOUSE_SIZE='SMALL';

CREATE OR REPLACE SCHEMA DATA;

CREATE OR REPLACE SCHEMA NOTEBOOKS;

CREATE OR REPLACE SCHEMA STREAMLITS;

create or replace stage streamlit_stage DIRECTORY = (ENABLE = TRUE);



create or replace stage streamlit_stage DIRECTORY = (ENABLE = TRUE);

CREATE or replace STREAMLIT "Policy Change Simulator"
ROOT_LOCATION = '@policy_change_simulator_streamlit.streamlits.streamlit_stage'
MAIN_FILE = '/Home.py'
QUERY_WAREHOUSE = POLICY_CHANGE_SIMULATOR_WH;

Adding the Files to run the app

Today we will manually add the files needed to run the app using the snowflake UI. however, in practice it is much easier to leverage Visual Studio Code as a ‘client IDE’ You may wish to leverage Visual Studio Code in the second part of the hackathon. We will be available to help you connect snowflake to Visual Studio Code if this is something you wish to do.

alt text

Download the following files

pages

alt text

alt text

Running the Streamlit app

1.2 Create a Policy Change Scenario using the new app

You will see summary metrics based on live calculation - all by using shared datasets.

alt text

FACT You can create a packaged app which have all the dependent SQL, python packages, images and streamlits which are called ‘Native apps’. This makes a fully functioning app easy to distribute.

Spend a few minutes trying out different scenarios before we start doing our own ad-hoc analysis using a Snowflake Notebook.

2 Data Analysis with a Notebook

We will use a notebook to do some analysis on the synthetic data. Before this you will need to link to an additional data share.

alt text

2.1 Viewing the data with a notebook

Now you have the data and have created a streamlit app, you will take a step back and have a look at how the raw data can be analysed.

The notebook compute comes pre-installed with some basic packages which include snowpark and streamlit. In this scenario we would also like to leverage matplotlib and pydeck. As this package is freely available within the Snowflake Annoconda channel, you can install these supported packages easily.

Go through the steps in the notebook which you have uploaded to snowflake. Once you have completed section 2, return to this guide.

2.2 Review of Section 2

So in summary we have looked at some techniques to understand the who, the when and the where. This is all featured around the impacts of cold weather payments. Another impact might be the cost of energy. For this exercise you will experience data sharing between one another.

3 Share data with Private Listings

In this section we will be looking at ingesting data, sharing the data, using a share and finally analysing data from both local and shared data.

3.1 Ingesting data

You saw before how to create a streamlit app - and then leveraged the notebook to analyse the data.

What if we want more data? There are lots of ways to ingest data. For this section we will do a simple approach. There is a dataset which features pre pay meter data.

Click on the links below to see an example data set you could use to complement the existing datasets.

https://www.gov.uk/government/statistics/postcode-level-electricity-statistics-2022

https://www.gov.uk/government/statistics/postcode-level-gas-statistics-2022

Gas Or Electric??

Some of you will be providers of Electric, others will be providers of gas

The Gas provider needs to download this

The Electric provider needs to download this

Ingest the Data

Click on Data and select the database policy_change_simulator_streamlit

Select the schema Data then on the top right hand corner, navigate and select Create Table from File

![load_data](/government_hackathon/assets/image-16.png)

After about 5 seconds you should get something like this:

alt text

Creating a new Notebook

Adding packages and import libraries

alt text


from snowflake.snowpark import functions as F
from snowflake.snowpark import types as T
meter_data = session.table('POLICY_CHANGE_SIMULATOR_STREAMLIT.DATA.ENERGY_USAGE_POSTCODE')
meter_data.limit(10)

You will see that there is a column that says ‘All postcodes - this dataset has summary data for each postcode area. This is useful as the cold weather payment is worked out by postcode area.


meter_data_pcd_area = meter_data.filter(F.col('POSTCODE')=='All postcodes').drop('POSTCODE')
meter_data_pcd_area


meter_data_pcd = meter_data.filter(F.col('POSTCODE')!='All postcodes')
meter_data_pcd.sample(0.05)

3.1 Prepare Secure Views of data for Sharing

Now as a data provider, I would like to share this data in this format to other organisations. For this we need to create secure views of the data (or tables/dynamic tables)


meter_data_pcd.create_or_replace_view('DATA."Electric Meter by Postcode"')
meter_data_pcd_area.create_or_replace_view('DATA."Electric Meter by Postcode Area"')

Once created you will see 2 views appear in the data schema. You can see this by toggling from Files to Databases within the left hand window pane.

alt text

As we are sharing the data, we need to make these views secure.


ALTER VIEW DATA."Electric Meter by Postcode" SET SECURE;
ALTER VIEW DATA."Electric Meter by Postcode Area" SET SECURE;

3.2 Create a Private Listing

We will now create a private listing using provider studio.

alt text

Once the assigned provider of the other energy source has shared their data, you will be able to see it in Private sharing.

Download the data as before.

alt text

3.3 Analysing both local and shared data from a private listing

So you should now have access to both gas and electricity data. One local and one from a share.

If you haven’t already done so, get the other energy listing data from the private shares.

HINT You have already done this step before with the initial data share. The new Data share will be in the Private Sharing area.


CREATE OR REPLACE VIEW "Energy by Postcode Area"

as
SELECT *, 'GAS' as "Energy Type" FROM ENERGY_USAGE__GAS.DATA."Gas Meter by Postcode Area"

UNION 

SELECT *, 'ELECTRIC' as "Energy Type" FROM DATA."Electric Meter by Postcode Area"

You have just created a simple view which combines the two datasets together.

Note - The above sql will fail for the Gas provider - you will need to change the database paths. The Electric Provider will only need supply the schema and table as the notebook is saved inside the database where the data is situated. You can see the exact paths by selecting the Databases tab within the left hand pane.


total_energy_area = session.table('"Energy by Postcode Area"')

total_energy_area;




total_energy = total_energy_area.group_by('"Energy Type"').agg(F.sum('NUM_METERS'),
                                          F.mean('MEAN_CONS_KWH'),
                                          F.median('MEDIAN_CONS_KWH'))

total_energy

We will now add some variables to change the price cap as well as the current prices of gas and electric


electric_KWh = st.number_input('Electric KWh in pence',1.00,30.00,22.36)
gas_KWh = st.number_input('Gas KWh in pence',1.00,7.00,5.48)
price_cap = st.number_input('Price Cap',1,6000,2000)

alt text

Next apply the price variables for gas and electric to the data

total_energy_avg_price = total_energy.with_column(‘Price’,F.when(F.col(‘“Energy Type”’)==’GAS’, F.col(‘AVG(MEAN_CONS_KWH)’)F.lit(gas_KWh/100)).else_(F.col(‘AVG(MEAN_CONS_KWH)’)F.lit(electric_KWh/100)))

total_energy_avg_price



Next add the % change of prices based on the price cap variable

- Copy and paste the following code in a new **python** cell
```python

price_cap_change = total_energy_avg_price.agg(F.sum('PRICE')).with_column('cap_price',
                                                                          F.lit(price_cap)).with_column('% change',
                                                                                                        F.div0('SUM(PRICE)',
                                                                                                               'CAP_PRICE'))

price_cap_change

Finally apply the % change to all postcode areas


total_energy_area_changes = total_energy_area.with_column('Price',F.when(F.col('"Energy Type"')=='GAS',
                                        F.col('MEAN_CONS_KWH')*F.lit(gas_KWh/100)).else_(F.col('MEAN_CONS_KWH')*F.lit(electric_KWh/100)))

total_energy_area_changes = total_energy_area_changes.\
join(price_cap_change.select('"% change"')).with_column('"New Price"',
                                                    F.col('PRICE')+ F.col('PRICE')*F.col('"% change"'))

total_energy_area_changes

alt text

3.4 Create a heatmap using H3 using the detailed energy information


CREATE OR REPLACE VIEW DATA."Energy by Postcode Detail"

as

SELECT *, 'GAS' as "Energy Type" FROM ENERGY_USAGE__GAS.DATA."Gas Meter by Postcode"

UNION 

SELECT *, 'ELECTRIC' as "Energy Type" FROM DATA."Electric Meter by Postcode"





total_energy_detail = session.table('DATA."Energy by Postcode Detail"')

total_energy_detail_changes = total_energy_detail.with_column('Price',F.when(F.col('"Energy Type"')=='GAS',
                                        F.col('MEAN_CONS_KWH')*F.lit(gas_KWh/100)).else_(F.col('MEAN_CONS_KWH')*F.lit(electric_KWh/100)))

total_energy_detail_changes = total_energy_detail_changes.\
join(price_cap_change.select('"% change"')).with_column('"New Price"',
                                                    F.col('PRICE')+ F.col('PRICE')*F.col('"% change"'))

postcodes = session.table('RESIDENTIAL_POSTCODES.GEOLOCAL.GEOLOCAL_RESIDENTIAL_POSTCODE').select('PCD','LAT','LON')
fuel_cost = total_energy_detail_changes.join(postcodes,postcodes['PCD']==total_energy_detail_changes['POSTCODE'])
fuel_cost = fuel_cost.group_by('POSTCODE').agg(F.any_value('LAT').alias('LAT'),
                                              F.any_value('LON').alias('LON'),
                                              F.mean('"New Price"').alias('"New Price"'),
                                              F.mean('PRICE').alias('"Price"'))

Index the latitude and longitude and group by H3

Create a new python cell using the code below:


H3 = fuel_cost.with_column('H3',F.call_function('H3_LATLNG_TO_CELL_STRING',F.col('LAT'),F.col('LON'),F.lit(5)))\
.group_by('H3').agg(F.mean('"Price"').alias('"Current_Price"'),
                   F.mean('"New Price"').alias('"New_Price"'))

Now we will leverage the previously installed pydeck package to render a map in H3.

Copy and paste the following python code below:


import pydeck as pdk

H3pd = H3.to_pandas()

color_scheme = f"""[
    0 * (New_Price/{price_cap} < 1) + 255 * (New_Price/{price_cap} >= 1),
    114 * (New_Price/{price_cap} < 1) + 100 * (New_Price/{price_cap} >= 1),
    189 * (New_Price/{price_cap} < 1) + 0 * (New_Price/{price_cap} >= 1)
    ]"""







h3 = pdk.Layer(
        "H3HexagonLayer",
        H3pd,
        pickable=True,
        stroked=True,
        filled=True,
        extruded=False,
        get_hexagon="H3",
        get_fill_color=color_scheme,
        line_width_min_pixels=0,
        opacity=0.4)

#### render the map showing trainstations based on overture maps

tooltip = {
   "html": """<b>H3:</b> {H3} <br> <b>New Price:</b> {New_Price}""",
   "style": {
       "width":"50%",
        "backgroundColor": "steelblue",
        "color": "white",
       "text-wrap": "balance"
   }
}

st.pydeck_chart(pdk.Deck(
    map_style=None,
    initial_view_state=pdk.ViewState(
        latitude=53,
        longitude=2.4,
        zoom=5,
        height=600
        ),
    
layers= [h3], tooltip = tooltip

))

You should see a map like this:

alt text

Blue indicates households who will be typically below the price cap and orange indicate above the price cap. There are various parts which are not covered. This may be because these areas have postcodes that cover a much wider area than the H3 cells can fit. You can correct this by leveraging polygons of all the boundaries and filling them with H3 cells.

Well done, you have created a share to enrich your own data in order to find out what the average yearly cost of fuel will be across all postcode areas and depdending on the simulated price cap. Feel free to reuse any of the code provided in this lab for your own Hackathon project.

4.0 Create your own Private Listing based on YOUR data

Hopefully, you will have access to your own data which you anticipate to share with other users in Snowflake. Now is the time to have a go at doing this today

Uploading from the UI

Here you will have the option to modify column names - and it will also let you know if there are any errors. One of the common errors are invalid column names. If you wish the names to include lowercase, spaces and special characters, you will need to enclose them with double quotes.

You will also be able to chose what file format you would like to ingest. The UI supports CSV, JSON, Parquet, Avro or ORC. (Note you can ingest many other file formats programmatically).

Once you have created your new table, you will be able to see it in the notebooks area.

Useful Documentation