Anna Geller anna-anisienia

## timestream_sample_query_daily_avg_for_last_week.sql
-- Find the average load and max speed for each truck for the past week.
SELECT
    bin(time, 1d) as binned_time,
    fleet,
    truck_id,
    make,
    model,
    AVG(CASE WHEN measure_name = 'load'
            THEN measure_value::double ELSE NULL END) AS avg_load_tons,
    MAX(CASE WHEN measure_name = 'speed'

## sql_server_inserts_pyodbc
import pyodbc
import pandas as pd

df = pd.read_csv('myfile.csv')
MY_TABLE = 'some_tbl'

conn = pyodbc.connect(driver='{ODBC Driver 17 for SQL Server}',
                      server='MYSERVER',
                      database='MYDB',
                      uid='MYUSER', pwd='MYPASSWORD')

## pyodbc_sql_server_fast_executemany.py
import pyodbc
import pandas as pd

df = pd.read_csv('myfile.csv')
MY_TABLE = 'some_tbl'

conn = pyodbc.connect(driver='{ODBC Driver 17 for SQL Server}',
                      server='MYSERVER',
                      database='MYDB',
                      uid='MYUSER', pwd='MYPASSWORD')

## fix.py
df['measurement'] = [format(i, '.3f') for i in df['measurement']]

## eda.py
import pandas as pd

df = pd.read_csv('yellow_tripdata_2020-06.csv')
print(len(df))  # 549,760 rows

x = df[df.payment_type == 4]
print(len(x))  # 2275 rows

## eda.py
import pandas as pd

df = pd.read_csv('yellow_tripdata_2020-06.csv')
print(len(df))  # 549,760 rows

x = df[df.payment_type == 5]
print(len(x))  # 12 rows only!

## upload_to_s3.py
import boto3

LOCAL_FILE = 'yellow_tripdata_2020-06.csv'
S3_KEY = 'taxi_2020-06.csv'
S3_BUCKET = 'playground-datasets'

s3_client = boto3.client(service_name='s3')
s3_client.upload_file(LOCAL_FILE, S3_BUCKET, S3_KEY)

## s3_select.py
import boto3
import os

S3_KEY = 'taxi_2020-06.csv'
S3_BUCKET = 'playground-datasets'
TARGET_FILE = 'unknown_payment_type.csv'

s3_client = boto3.client(service_name='s3')
query = """SELECT VendorID, tpep_pickup_datetime, tpep_dropoff_datetime,
        passenger_count, trip_distance, tip_amount, total_amount

## test_result.py
import pandas as pd

df = pd.read_csv('unknown_payment_type.csv')
print(f'Nr of rows: {len(df)}')
print(df[['ID', 'distance', 'tip', 'total']])  # well-formatted dataframe

## package_lambda_api.bash
root_dir='/Users/anna/Desktop/mediumAPI'
venv_dir="$root_dir/venv/lib/python3.8/site-packages"
cd $venv_dir && zip -r9 "$root_dir/lambda.zip" . \
&& cd "$root_dir/api" && zip -g ../lambda.zip -r .
	-- Find the average load and max speed for each truck for the past week.
	SELECT
	bin(time, 1d) as binned_time,
	fleet,
	truck_id,
	make,
	model,
	AVG(CASE WHEN measure_name = 'load'
	THEN measure_value::double ELSE NULL END) AS avg_load_tons,
	MAX(CASE WHEN measure_name = 'speed'
	import pyodbc
	import pandas as pd

	df = pd.read_csv('myfile.csv')
	MY_TABLE = 'some_tbl'

	conn = pyodbc.connect(driver='{ODBC Driver 17 for SQL Server}',
	server='MYSERVER',
	database='MYDB',
	uid='MYUSER', pwd='MYPASSWORD')
	import pandas as pd

	df = pd.read_csv('yellow_tripdata_2020-06.csv')
	print(len(df)) # 549,760 rows

	x = df[df.payment_type == 4]
	print(len(x)) # 2275 rows
	import boto3

	LOCAL_FILE = 'yellow_tripdata_2020-06.csv'
	S3_KEY = 'taxi_2020-06.csv'
	S3_BUCKET = 'playground-datasets'

	s3_client = boto3.client(service_name='s3')
	s3_client.upload_file(LOCAL_FILE, S3_BUCKET, S3_KEY)
	import boto3
	import os

	S3_KEY = 'taxi_2020-06.csv'
	S3_BUCKET = 'playground-datasets'
	TARGET_FILE = 'unknown_payment_type.csv'

	s3_client = boto3.client(service_name='s3')
	query = """SELECT VendorID, tpep_pickup_datetime, tpep_dropoff_datetime,
	passenger_count, trip_distance, tip_amount, total_amount
	import pandas as pd

	df = pd.read_csv('unknown_payment_type.csv')
	print(f'Nr of rows: {len(df)}')
	print(df[['ID', 'distance', 'tip', 'total']]) # well-formatted dataframe
	root_dir='/Users/anna/Desktop/mediumAPI'
	venv_dir="$root_dir/venv/lib/python3.8/site-packages"
	cd $venv_dir && zip -r9 "$root_dir/lambda.zip" . \
	&& cd "$root_dir/api" && zip -g ../lambda.zip -r .