Skip to main content

Data Cleansing View in MySQL

I discussed before how I picked up parkrun data from my e-mails, they don't have an API as their system was never designed to cope with the millions of people that now take part. I only want my own data so this works just fine for me. I use a Zap to pick up the e-mail and plonk it in a Google Sheet and the Keboola to process the data into MySQL and maybe soon Snowflake. Actually given the setup I have it would only take 5 minutes in Keboola to add a step to the Flow to pass the output from the view below and put it into Snowflake as a table. I am leaning more towards using Snowflake as long as Retool stays free enough for me to use as the free MySQL database has a very limited session pool and therefore limits the visualisations I can do. 

Anyway the raw data from the e-mail is useless for visuals so I processed the data in MySQL. There might be more elegant solutions but for me it was some experience in how to code this in MySQL and what functions it has. Being primarily used to Oracle it has been interesting doing this in MySQL and Snowflake. The view uses that fact that the e-mail arrives in a standard format to pull out the details I am interested in, such as location, time, parkrun number etc. and allows me to then report on these. 

The code for my view can be seen here: 

Labels:

Comments

Post a Comment

Popular posts from this blog

Keboola Flows

Really finding Keboola was the thing that kickstarted this project otherwise I would be trying to build custom code on a python cloud server and building everything from scratch.  In Keboola you build you data sources and destinations using connection details which is fairly simple and something I will likely cover in another post, same goes for transformations etc. Here though I am going to discuss Flows, this is where you bring everything together. On my free account there are some limitations.  My easiest flow is very basic:  Pull parkrun results e-mail from Gmail to Google Sheets (actually done by Zap not Keboola).  Keboola will, as often as I like, in this case once a week, pull the data from the sheet into its storage.  It will then transfer this to the target database. Currently I have this setup to be MySQL database but I can and might expand that to the Snowflake instance within Keboola.  I then, outside of Keboola, connect to the MySQL database f...
Post a Comment
Read more

Zoho Analytics

Have I finally found my BI Tool, one that lets me import data from Snowflake and share it for free? I know, no sooner have I posted about how hard it was to find a tool that could do anything from Snowflake than I come across Zoho. You can check out my dashboard on the following page . Below is a diagram that outlines the processes I have used to obtain this data. In summary my parkrun e-mail is pushed to Google Sheets every week by Zapier and Forms I submit every day are used to track the strength training I do. Keboola is then used to ingest this data into MySQL and or Snowflake where I then use views or the built in transformation processes in Keboola to shift the data into a format for reporting. Google Data Studio then connects to MySQL and Zoho to Snowflake to visualise the data. 
Post a Comment
Read more

Generative AI News - 03/03/2024

Here are some of the latest news highlights related to Generative AI from this week: Venus Williams and Brain.ai : Tennis legend Venus Williams has ventured into interior design with Brain.ai , a new generative AI-powered platform 1 . It’s fascinating to see how generative AI is being integrated into various domains beyond smartphones. Google’s Gemini AI Controversy : Google’s chatbot, Gemini , faced controversy due to its image generator mistakenly placing people of colour in Nazi-era uniforms. This incident highlights the importance of responsible AI development 2 . Apple’s Focus on GenAI : Apple CEO Tim Cook announced that the company will “break new ground” in generative AI this year 2 . Keep an eye out for exciting developments from Apple. Morph Studio for Filmmaking : Morph Studio introduces an AI filmmaking platform that allows weaving Stability AI-generated video clips into movies 2 . A creative application of generative AI! Adobe’s Project Music : Adobe unveiled Project Music...
Post a Comment
Read more