Visualize and Analyze Strava Data on Autonomous Database
A new quickstart was published on Oracle Cloud Console’s homepage this week. This new quick start uses one of our Product Manager’s Strava data to show off how easy the new Dashboaring and Charting feature work in Database Actions ( sqldev-web ) with Autonomous Database.
This quick start creates an Always Free Autonomous Database, loads the Strava Data, then produces a dashboard over it all.
Free
Oracle ALWAYS Free Tier is free, always offers many services including a free Oracle Autonomous Database. So for someone wanting to try this out for free, just sign up and kick the tires. There’s a number of other free services including compute, storage, function , containers,….
Use your Strava Data
After seeing this a number of Oracle Employees asked how to do this on their own data.
Getting the data from Strava
This is the scariest part because Strava for some reason connects Downloading your data to Deleting the Profile completely! They clearly did not consider the Developer Experience in folks wanting to get at their own data. This is a nice read on what the Developer Experience should be.
icymi - i wrote a thing about developer experience. https://t.co/6CGrnYNxJ2
— Go on then, drop it! (@monkchips) February 22, 2022
In the account page, there’s section at the bottom. Click the Getting Started.
Now in section 2, click “Request your Archive”
Wait for an email
The email
Obviously click the download button.
Now Load the Data
I’m recommend make a new directory named strava and unzip into that. There’s everything in this zip including the gpx/fit/tcx files, images, authorized apps, every aspect of the Strava account.
LOAD into the database.
Now that you have the data in csv there’s many many ways to load the data. Some options are outlined here and here
SQLcl has for a long time had a load csv option. You can learn more about the generic from Jeff’s blog here from ‘19 SQLcl Load Command
This is the sql script which will load the data. It’s quite simple in that the 1st line set load scan_rows 10000 tells sqlcl how many rows to scan for auto calculating the data type and size. Without this if the first 10 rows column X is say 100 chars long then row 11 is 101, the load would blow out. This let’s it scan more and work out the type and length by scanning more data.
The next is 1 line per csv file load strava_activities activities.csv new The command load takes in the new table name, the file to load, and new meaning make a new table automatically.
| set load scan_rows 10000 | |
| drop table strava_activities; | |
| drop table strava_applications; | |
| drop table strava_bikes; | |
| drop table strava_blocks; | |
| drop table strava_clubs; | |
| drop table strava_comments; | |
| drop table strava_components; | |
| drop table strava_connected_apps; | |
| drop table strava_contacts; | |
| drop table strava_email_preferences; | |
| drop table strava_events; | |
| drop table strava_favorites; | |
| drop table strava_flags; | |
| drop table strava_followers; | |
| drop table strava_following; | |
| drop table strava_general_preferences; | |
| drop table strava_global_challenges; | |
| drop table strava_goals; | |
| drop table strava_group_challenges; | |
| drop table strava_kudos; | |
| drop table strava_local_legend_segments; | |
| drop table strava_logins; | |
| drop table strava_memberships; | |
| drop table strava_metering; | |
| drop table strava_mobile_device_identifiers; | |
| drop table strava_monthly_recap_achievements; | |
| drop table strava_orders; | |
| drop table strava_partner_opt_outs; | |
| drop table strava_photos; | |
| drop table strava_posts; | |
| drop table strava_privacy_zones; | |
| drop table strava_profile; | |
| drop table strava_routes; | |
| drop table strava_segment_feedback; | |
| drop table strava_segments; | |
| drop table strava_shoes; | |
| drop table strava_social_settings; | |
| drop table strava_starred_routes; | |
| drop table strava_starred_segments; | |
| drop table strava_support_tickets; | |
| drop table strava_visibility_settings; | |
| load strava_activities activities.csv new | |
| load strava_applications applications.csv new | |
| load strava_bikes bikes.csv new | |
| load strava_blocks blocks.csv new | |
| load strava_clubs clubs.csv new | |
| load strava_comments comments.csv new | |
| load strava_components components.csv new | |
| load strava_connected_apps connected_apps.csv new | |
| load strava_contacts contacts.csv new | |
| load strava_email_preferences email_preferences.csv new | |
| load strava_events events.csv new | |
| load strava_favorites favorites.csv new | |
| load strava_flags flags.csv new | |
| load strava_followers followers.csv new | |
| load strava_following following.csv new | |
| load strava_general_preferences general_preferences.csv new | |
| load strava_global_challenges global_challenges.csv new | |
| load strava_goals goals.csv new | |
| load strava_group_challenges group_challenges.csv new | |
| load strava_kudos kudos.csv new | |
| load strava_local_legend_segments local_legend_segments.csv new | |
| load strava_logins logins.csv new | |
| load strava_memberships memberships.csv new | |
| load strava_metering metering.csv new | |
| load strava_mobile_device_identifiers mobile_device_identifiers.csv new | |
| load strava_monthly_recap_achievements monthly_recap_achievements.csv new | |
| load strava_orders orders.csv new | |
| load strava_partner_opt_outs partner_opt_outs.csv new | |
| load strava_photos photos.csv new | |
| load strava_posts posts.csv new | |
| load strava_privacy_zones privacy_zones.csv new | |
| load strava_profile profile.csv new | |
| load strava_routes routes.csv new | |
| load strava_segment_feedback segment_feedback.csv new | |
| load strava_segments segments.csv new | |
| load strava_shoes shoes.csv new | |
| load strava_social_settings social_settings.csv new | |
| load strava_starred_routes starred_routes.csv new | |
| load strava_starred_segments starred_segments.csv new | |
| load strava_support_tickets support_tickets.csv new | |
| load strava_visibility_settings visibility_settings.csv new |
SQLcl has always been able to run remote scripts making this gist running able straigh off github using the raw link or of course save to a local file and run it.
SQL> @https://gist.githubusercontent.com/krisrice/2b06df3ad4b51de278fe53672831f5ea/raw/99792be40b8881f0799700afb66ac991ed356512/load_strava.sql
SQLcl will scrub all the column names into Oracle Database column names such as this for the visibility_settings.csv. Again Developer Experience here is lacking w/Strava and these column names. The tool will report the before and after of the names such as>
#INFO COLUMN 1: Include your activities in Metro and Heatmap => INCLUDE_YOUR_ACTIVITIES_IN_MET
#INFO COLUMN 2: Who can see that you were part of a group activity => WHO_CAN_SEE_THAT_YOU_WERE_PART
#INFO COLUMN 3: Who can see your activity in Flybys => WHO_CAN_SEE_YOUR_ACTIVITY_IN_F
#INFO COLUMN 4: Who can view your training log => WHO_CAN_VIEW_YOUR_TRAINING_LOG
#INFO COLUMN 5: Profile Visibility => PROFILE_VISIBILITY
#INFO COLUMN 6: Default Activity Visibility => DEFAULT_ACTIVITY_VISIBILITY
SQLcl will also print out the full details per file for the load and what settings it used
Create new table and load data into table KLRICE.STRAVA_VISIBILITY_SETTINGS
csv
column_names on
delimiter ,
enclosures ""
encoding UTF8
row_limit off
row_terminator default
skip_rows 0
skip_after_names
batch_rows 50
batches_per_commit 10
clean_names transform
column_size rounded
commit on
date_format
errors 50
map_column_names off
method insert
timestamp_format
timestamptz_format
locale English United States
scan_rows 5000
truncate off
unknown_columns_fail on
SQL For-The-Win
Now that it’s loaded into relational tables, have fun. For example, here’s my highest elevation things and most are in the virtual world of Zwift and a trip we did to see the mountain gorrillas in Bwindi Impenetrable Forest.
Good Luck. Happy SQL-ing