K#18. SQL function of the week: TO_HEX

Converting data types on SQL for table joins

Khouloud El Alami

Nov 11, 2024

Hey all, hope you’re all well.

For the new joiners: hi I’m K and each week I share learnings from my data science job at Spotify ✨

We’re currently 3278 in this newsletter, thanks a lot for reading me!

From my side, I've been doing well. For those who've been following me for a while, you might have noticed I've been quieter on the “content” front. Truth is, I recently realized how much of my life I've put on hold since I started creating content a year ago. What began as a fun side project alongside my full-time job turned into something else entirely - I'm terrible at doing things halfway. I ended up treating it like a second job, and that wasn't the plan. So I'm taking a breather to figure out what I really want to do with my "internet presence". One thing I know for sure is that I want to give my YouTube channel a real shot, and I'll be back at it once I'm properly rested (probably around January).

This week, I want to share a simple but effective SQL function that makes some table joins possible.

Picture this: I'm trying to join a stream table with playback data. Simple enough, right? Well not quite. The playback ID was:

STRING in one table
BYTES in another

Without matching formats, my join would return nothing but sadness and empty rows.

If you’ve ever tried joining tables only to realize the same feature has different formats in both tables, then you might be familiar with the struggle. If not, then I guarantee that day will come for you.

What's TO_HEX()?

TO_HEX() is this SQL function that converts BYTES or numeric values into their hexadecimal STRING representation. It’s a translator between different data formats.

Here's how TO_HEX() saved the day:

-- This query is a representative example of what happened (I do not share confidential data from Spotify)

WITH streams_table AS (
  SELECT 
    playback_id,  -- This is in BYTES
    duration_ms,
    timestamp
  FROM `spotify.streams`
),

playback_table AS (
  SELECT 
    playback_id,  -- This is in STRING
    user_id,
    device_type
  FROM `spotify.playback_events`
)

SELECT 
  s.*,
  p.user_id,
  p.device_type
FROM streams_table s
JOIN playback_table p
  ON TO_HEX(s.playback_id) = p.playback_id  -- Magic happens here! ✨

Other common use cases:

Working with binary data in databases
Handling hash values or encrypted data that needs to be compared

Just make sure to always check your data types before joining tables.

Where can you use it?

Cloud Data Warehouses: BigQuery (my use case), Snowflake
Traditional Databases: PostgreSQL, Oracle, MySQL, SQL Server

Note: Syntax might vary slightly across platforms, but the concept remains the same!

That's it for today! Hope this helps someone out there struggling with mismatched data types.

If you enjoyed this edition, please leave a ❤️ or drop a comment—I’d love to know I’m not just talking to a wall. Feel free to ask questions if something is not clear.

My socials: YouTube, Instagram, LinkedIn & Medium

Take care & see you soon 👋🏼