# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: Python 3
#     language: python
#     name: python3
# ---

# # Procure Safegraph Pattersn on Amazon EC2
#
# This notebook is intended to speed up extracting the patterns data by taking the processing to the data. The Safeagraph Data resides in a Simple Storage Service (S3) bucet in the the US-East-2 region. Therefore, the most efficient way to get the data we need...aside from recruiting a true _big data_ infrastructure stack, is to run an Amazon Elastic Compute Cloud (EC2) instance with Python, Jupyter Notebook and a copy of this repository, and use this execution environment to extract just the data we need.

# +
from pathlib import Path

from sg_data import SafegraphClient

# +
dir_prj = Path('./').absolute().parent

dir_data = dir_prj/'data'

dir_raw = dir_data/'raw'
# -

# For the notebook to run, you will need to populate the variables below with the information you get from your own account credentials provided by Safegraph for data access.

# +
AWS_KEY = 'AO1L8A67NB5QUYLQOYLU'
AWS_SECRET = '<KEY>'

sg_poi = 'sg:af471021a929414cbf69854e6f8f1b0c'  # white pass
# -

# Using the credentials provided above, a Safegraph Client object instance can be created. This object is part of the Safegraph Data Utilities provided with this package.

# +
sg = SafegraphClient(access_key=AWS_KEY, secret_key=AWS_SECRET)

sg
# -

# One of the functions of the Safegraph Client provides introspection, the ability to interrogate the available resources to discover what is available as a Pandas DataFrame.

# +
ptrns_df = sg.content_dataframe

ptrns_df.head()
# -

# This DataFrame can be filtered and organized to discover what is available. In this case we are interested in what `patterns` data is available, and to more easily see the range available, we are sorting by month and year.

ptrns_df = ptrns_df[ptrns_df['resource_type'] == 'patterns'].sort_values(['year', 'month']).copy()

# Finally, although we are going to retrieve three years worth of data, since interested only in ski season, we are filtering to November through April. Although not instantanious, when I performed the same data pull on a local machine in my office, this process took over six hours, so this is a dramatic improvement.

row = ptrns_df.iloc[0]
src = row.source_path

# +
# https://aws.amazon.com/blogs/storage/querying-data-without-servers-or-databases-using-amazon-s3-select/
# https://docs.aws.amazon.com/AmazonS3/latest/API/API_SelectObjectContent.html
resp = sg.s3.select_object_content(
    Bucket=sg.bucket,
    Key=src,
    ExpressionType='SQL',
    Expression=f"SELECT * FROM s3object s where s.\"safegraph_place_id\" = '{sg_poi}'",
    InputSerialization = {'CSV': {"FileHeaderInfo": "Use"}, 'CompressionType': 'GZIP'},
    OutputSerialization = {'CSV': {}},
)

for event in resp['Payload']:
    if 'Records' in event:
        records = event['Records']['Payload'].decode('utf-8')
        print(records)
    elif 'Stats' in event:
        statsDetails = event['Stats']['Details']
# -



StarcoderdataJupyterScriptsDedupFiltered