Thread by @youngchingjui

Hey all, I’m doing a weekly twitter standup, mostly for myself. But if you or anyone else would like to join, feel free to ping me. I’m thinking a weekly basis won’t be too time consuming. Pick a day of the week, and I’ll @ you to remind you. #weeklytwitterstandup

Did last week:
✅ finally built script to track usage of my servers. It’s a python script uploaded to AWS lambda. The script pings my half-dozen servers for data usage and writes the data onto 1) a MySQL server hosted on AWS RDS, 2) BigQuery database, and 3) google sheets

✅ built a dashboard on server usage data using Tableau public. I now have the dashboard scrolling on my Life Flips app.

✅ went through another few hours of the Udemy course I’m taking on writing shell scripts. This has been a much needed course!

What I aim to accomplish this week:
▫️get react native set up on my computer again (I did a hard hard drive reset last week)
▫️redesign my Podcast Social app on Sketch
▫️convert the sketch elements into react native code

What I’m stuck on:
(Sometimes I don’t know how to answer this question. Not necessarily “stuck” on anything - just mounds of work to get through!)
- I guess working with React Native? This will be my first react native project, so I expect lots of delays and learning!

This itself is a funny story. Why make my #Python script write to 3 different databases? Here’s how it started:

This is my first time working with and setting up databases. I wanted to store structured usage data for analysis in the future. Didn’t really know the difference between DB options (#MySQL vs. PostgreSQL vs. BigQuery...?). But a relational database sounded nice.

Initially I went with Google Cloud Platform’s #CloudSQL option. Set it up, I have free credits left, and tested it out. I was able to interact with it with my Python script - great!

But over the next few days, I realized that my #GCP MySQL database was eating up lots of credits. It was costing me USD2.50 a day! What the heck? I wasn’t using it, wasn’t writing new data to it, no new queries were made....

Later I found out that the new Cloud SQL engine doesn’t automatically shut off when inactive. Not sure why they have this policy. But it made it very expensive to play around with Cloud SQL

Fine, let’s try out #AWS Relational Database Service. They had a vanilla MySQL option. I’ve heard of MySQL before...it’s probably popular. And they have a free tier. Great.

Setup was seamless, their browser console is more responsive than GCPs, and I got a quickly server up and running. I downloaded MySQLWorkbench to connect remotely...and it works! I quickly wrote some Python script to test out reading and writing - it works!

Great. I incorporated the AWS MySQL DB into my Python script. I would extract usage data from my GCP-hosted VMs and write the data to MySQL. Eventually, I want to build an easy dashboard that automatically retrieves data from this database.

After this was all set up, I tried using Google’s #datastudio (it’s free, which is much cheaper than #Tableau) to connect to MySQL. But unfortunately no matter what I tried, Data Studio wouldn’t connect. On top of that, documentation and support from Google is scant.

I tried this a couple of times. Error code was always different. Google search of error code didnt’ lead me anywhere. I started looking for a different route.

I wanted to use Data Studio for easy dashboarding that I could load onto my Life Flips app (cloud based). And I’ve connected my #BigQuery database to DataStudio before, so I knew that connection would work. And I know I write data to BQ with Python. So I figured let’s try that.

On reflection, I wonder if it could have been an #AWS Virtual Private Cloud networking issue. But I thought I had opened up my #VPC to allow all traffic (not safe!)...

Anyways, I setup another schema on my existing BQ DB. Test out some Python script - it works. Great. Now I’m writing to 2 databases with my Python script (didn’t want to delete the code for the MySQL portion yet).

Once I got that working, and BigQuery was successfully storing my usage data, I started buildiing my dashboard on Data Studio. Here comes the next issue:

Data Studio’s dashboard features are pretty standard, though UI is not great. And there was one specific thing I wanted to build into my dashboard, and I couldn’t figure out how to do it with Data Studio. BUT, I know how to do it with #tableau.

Tableau has a free version - Tableau Public. BUT, it only connects with local files, OData or Google Sheets. Not BigQuery or MySQL (I believe this is offered for their paid versions). Dang it.

OK. Since I know I can build the dashboard the way I want it on Tableau Public, but the issue is finding a way to connect the data to it...maybe I can consider saving the data on Google Sheets too. Sigh...

I’ve been writing for 30 minutes now. To summarize the Google Sheets portion, I learned how to authenticate via OAuth to Google APIs (not so straightforward) and write data to Google Sheets. Now I’m writing to 3 different databases.

Finally, I learned how to upload this Python script to AWS Lambda - it also took a bit of learning, but their documentation is much better than Google’s, and I felt much more accomplished when I learned this new tool.

Lambda works great. I added a cron job to execute every 3 hours. And my Python script is retrieving data and writing them to 3 different databases. Final step: Create the dashboard.

I’ve used Tableau before, so I knew how to craft the dashboard I wanted. Did it in 15 minutes, and uploaded it to the cloud (that’s the only option for saving your file with Tableau Public). And now it shows up on my LifeFlips app with the latest data.

Finally! Everything is working as it should...and it only took me like 30+ hours to get this sorted. Given I did learn a lot about Python scripts, databases, connecting to databases, and automating scripts on AWS Lambda.

But I realize now that writing to #GoogleSheets causes large hangups when Lambda runs...and Lambda script often runs multiple times, because it doesn’t always finish on the first or second try. It seems like some sort of Google Sheets networking issue. Problem for another day.

@threadreaderapp unroll please! Will post this on my blog as well

@threadreaderapp unroll please