DEV Community: Marian Eerens

Navigating the Google Cloud Console and Platform

Marian Eerens — Tue, 02 Aug 2022 22:50:18 +0000

👩🏼‍💻 Originally posted on LinkedIn

I started using BigQuery in GCP (Google Cloud Platform) as my SQL sandbox and didn't really pay much attention to anything else. And while I will most likely never use a lot of the tools & services available, I at least wanted to understand the core concepts, how the platform is structured and what it actually is (and isn’t).

If you’re like me (a bit of nerd and always curious 🤓) and want to be able to contribute to conversations without feeling like an imposter then this shortlist of resources will help you get there.

① 𝗚𝗼𝗼𝗴𝗹𝗲 𝗖𝗹𝗼𝘂𝗱 𝗙𝘂𝗻𝗱𝗮𝗺𝗲𝗻𝘁𝗮𝗹𝘀: 𝗖𝗼𝗿𝗲 𝗜𝗻𝗳𝗿𝗮𝘀𝘁𝗿𝘂𝗰𝘁𝘂𝗿𝗲 (𝘃𝗶𝗮 𝗚𝗼𝗼𝗴𝗹𝗲 𝗖𝗹𝗼𝘂𝗱 𝗦𝗸𝗶𝗹𝗹𝘀 𝗕𝗼𝗼𝘀𝘁)

The content gets very technical about halfway through, but thought the first couple of modules were really helpful in framing up some of the basics. You can take the course for free but will need sign up for credits if you want to complete the labs (first 30 days are free).

② 𝗧𝗵𝗲 𝗚𝗖𝗣 𝗘𝘀𝘀𝗲𝗻𝘁𝗶𝗮𝗹𝘀 𝗣𝗹𝗮𝘆𝗹𝗶𝘀𝘁 (𝘃𝗶𝗮 𝘁𝗵𝗲 𝗚𝗼𝗼𝗴𝗹𝗲 𝗖𝗹𝗼𝘂𝗱 𝗧𝗲𝗰𝗵 𝗬𝗼𝘂𝗧𝘂𝗯𝗲 𝗰𝗵𝗮𝗻𝗻𝗲𝗹)

This playlist covers a lot of the basics and most of the different tools. I didn’t watch all of them but depending on why you are checking out GCP, you can pick and choose what’s relevant to you.

③ 𝗔𝗻 𝗜𝗻𝘁𝗿𝗼𝗱𝘂𝗰𝘁𝗶𝗼𝗻 𝘁𝗼 𝗚𝗖𝗣 𝗳𝗼𝗿 𝗦𝘁𝘂𝗱𝗲𝗻𝘁𝘀 (𝘃𝗶𝗮 𝘁𝗵𝗲 𝗚𝗼𝗼𝗴𝗹𝗲 𝗖𝗹𝗼𝘂𝗱 𝗧𝗲𝗰𝗵 𝗬𝗼𝘂𝗧𝘂𝗯𝗲 𝗰𝗵𝗮𝗻𝗻𝗲𝗹)

I love it when someone explains seemingly abstract concepts and makes it relatable by using easy to understand terminology. Definitely watch the intro, it really helped me frame up Google Cloud vs GCP and the different types of offerings (Infrastructure, Platform, Software).

④ 𝗚𝗼𝗼𝗴𝗹𝗲 𝗖𝗹𝗼𝘂𝗱 𝗗𝗶𝗴𝗶𝘁𝗮𝗹 𝗟𝗲𝗮𝗱𝗲𝗿 𝗖𝗲𝗿𝘁𝗶𝗳𝗶𝗰𝗮𝘁𝗶𝗼𝗻 𝗖𝗼𝘂𝗿𝘀𝗲 (𝘃𝗶𝗮 𝗳𝗿𝗲𝗲𝗰𝗼𝗱𝗲𝗰𝗮𝗺𝗽.𝗼𝗿𝗴)

If you were looking to get certified and in need of free training content, this 6hour course is incredibly detailed and offers a lot of additional free resources. You can scan the table of contents and navigate to specific topics you’re interested in and then watch parts of the course.

⑤ 𝗚𝗼𝗼𝗴𝗹𝗲 𝗖𝗹𝗼𝘂𝗱 𝗼𝘃𝗲𝗿𝘃𝗶𝗲𝘄 (𝘃𝗶𝗮 𝗚𝗼𝗼𝗴𝗹𝗲 𝗖𝗹𝗼𝘂𝗱 𝗗𝗼𝗰𝘂𝗺𝗲𝗻𝘁𝗮𝘁𝗶𝗼𝗻)

The documentation on Google Cloud is very extensive. I like this overview because it covers the basics and points you towards more in depth documentation if you want to go into more detail.

Happy learning!

Lessons learnt from the 8 week sql challenge: string_agg

Marian Eerens — Tue, 07 Jun 2022 12:16:25 +0000

👩🏼‍💻 using Google BigQuery SQL syntax
📁 check the Github repository for context

A neat function I discovered during the challenge is STRING_AGG which strings together (concatenates) non null string values in a single field.

This was by no means a requirement but instead of the output below, I wanted to return a table that shows the 3 customer id's and for each customer id strings together the different product names in a single cell.

So what's the syntax for creating the output below?

Turns out it's a lot simpler than I thought.

Instead of this ...

SELECT
customer_id,
product_name
FROM q5_items_sold_ranked
WHERE count_rank = 1
ORDER BY customer_id ASC;

... do this.

SELECT
customer_id,
STRING_AGG(product_name, ', ') AS most_popular_items
FROM q5_items_sold_ranked
WHERE count_rank = 1
GROUP BY customer_id
ORDER BY customer_id ASC;

Hope you find this useful, let me know in the comments if any questions.

Lessons learnt from the 8 week sql challenge: window functions & ranking

Marian Eerens — Wed, 04 May 2022 19:40:57 +0000

👩🏼‍💻 using Google BigQuery SQL syntax
📁 check the Github repository for context

For the longest time I've had a love/hate relationship with WINDOW functions in SQL but recently had a moment where I finally started seeing some of the light.

DENSE_RANK RANK and ROW_NUMBER are amongst the more powerful WINDOW functions and it's important to understand how they behave differently so you make the right choice for your use case.

Let's take a closer look at an example to help frame things up.

The query below ...

SELECT 
s.customer_id,
s.order_date,
m.product_name, 
ROW_NUMBER () OVER (PARTITION 
  BY customer_id ORDER BY order_date ASC) AS row_number,
RANK () OVER (PARTITION 
  BY customer_id ORDER BY order_date ASC) AS rank,
DENSE_RANK () OVER(PARTITION 
  BY customer_id ORDER BY order_date ASC) AS dense_rank
FROM sqlchallenge_week1.sales AS s
  JOIN sqlchallenge_week1.menu AS m
    ON s.product_id = m.product_id
ORDER BY customer_id;

... will generate the output below.

Can you tell from the image above how a WINDOW function actually behaves and how it's different from a standard AGGREGATE function?

WINDOW functions create a partition of the table based on a set of parameters. In our the case the partition is created for every customer id, the records in that partition are sorted by order date ascending.
Next we're calling a function on that partition where for every row in a value is generated that will go into a new column that gets added to the table.

When you look at the example in our table you'll notice that results are not grouped meaning the same amount of rows (14) went in as came out. And that's how WINDOW functions (also called analytical functions) are different from AGGREGATE functions. In case of our example an AGGREGATE function like COUNT() would have grouped all the rows per customer id to then count the rows in each group and return a single value per customer id resulting in the output below.

Now let's go back to our WINDOW functions and take a look at how RANK, DENSE_RANK and ROW_NUMBER are different from each other.

ROW_NUMBER
Every row in the partition or window gets assigned a number which starts at 1 being the first row in the ordered partition and so on. You'll have noticed every row in the window is assigned a unique number and the order of those numbers is sequential with increments of 1.

RANK
When assigning a number to each row in the partition or window we follow the same logic as ROW_NUMBER. The only difference being that rows with the same order date receive the same rank number, and the rank number for every row in the next subset isn't incremented by 1 but 1 + number of rows in the previous subset.

DENSE_RANK
For DENSE_RANK every row in our subset in the partition gets assigned a unique number which is incremented by 1 for next subset.

Hope you find this useful, let me know in the comments if any questions.

📚 For a broader view on windows functions I can recommend the following blog posts:

An Easy Guide to Advanced SQL Window Functions

Take your SQL from Good to Great: Part 4 (Window Functions)

Learn sql we must

Marian Eerens — Mon, 25 Apr 2022 11:28:56 +0000

If you're new to SQL and are looking for some free resources to help you get started, then this list of resources is for you.

My SQL Resources

Content is currently grouped in these 4 categories:

Free learning tutorials, video series and courses
Useful blogs on important concepts
Practice environments & challenges
Useful tools and other resources

I'm always looking to add more so please share your suggestions and recommendations in the comments.