Flattening DynamoDB JSON in ClickHouse

[MUSIC] Welcome to the Click House Podcast, your trusted guide to mastering Click House, the powerhouse of columnar databases. Whether you're just getting started or already managing complex data workloads, this podcast has something for you. Enjoy a mix of AI-generated conversations along with interviews from real-world experts who've scaled Click House to meet today's toughest data challenges. Whether you're here to learn, optimize, or get ahead of the curve, we promise you'll leave every episode a little sharper, a little more inspired, and maybe even a little obsessed with the art of data at scale. Tune in and explore how to get the most out of this powerful database. [MUSIC] This episode of the Click House Podcast is sponsored by Propel. Propel empowers teams to harness the power of Click House without the headaches of managing servers. Get instant access to high-performance analytics by visiting www.propelldata.com. [MUSIC] I ever feel like you're sitting on this goldmine of data, but you just can't unlock it. Oh, tell me about it. You've got DynamoDB. It's working great for your apps, but when you need to do some serious analysis, it's like trying to find a needle in a haystack of JSON. It's the worst, but guess what? What? Today, we're going to fix that. Oh, are we? We are. We're talking DynamoDB Analytics, and we're bringing in the big guns Click House. Okay, now we're talking. So we're diving into a blog post, right? We are from Propel Data. They're like the gurus of Click House, especially that serverless Click House stuff. And they've got this really cool method, this process, to transform your DynamoDB data, make it sing for Click House, which, let's face it, they were made for each other. It's the match made in Data Heaven. It totally, but really, it comes down to this. DynamoDB is great for applications, but those JSON structures, they're not exactly analysis-friendly. No, not at all. Click House, though, it devours data, but it likes it organized. Totally. It's like, imagine reading a book, but it's just one long paragraph. Ugh, my eyes hurt just thinking about it. Right. All the information's there, but good luck making sense of it quickly. That's where this whole idea of flattening comes in. Flattening, okay, I'm intrigued. We're taking that messy JSON, and we're turning it into a beautiful, organized spreadsheet. Ah, now you're speaking my language. And the best part is, Propel Data, they break it down into four easy to follow steps. Music to my ears. Okay, walk us through it. Where do we start? Step one, the foundation. We got to get that DynamoDB data flowing into Click House. Think of it like laying the groundwork. Yeah, you can't build a house without a solid foundation, right? Exactly. Now, Propel Data, they recommend their own platform for this, naturally. Sure. Sure. But the idea is the same no matter what. You need a reliable pipeline to get that data moving. Data streams flowing, I like it. So, data's in place, then what? Then the fun begins. Step two, we're building a materialized view in Click House. Okay, hold on, materialized view. Sounds a little intimidating, not gonna lie. I know it's a mouthful, right? But stay with me, it's actually pretty cool. All right, you've peaked my curiosity. What is a materialized view? Okay, imagine this. You've got this massive library. Okay, I can picture it. And you want to find, say, all the books on Astray Physics published after the year 2000, written by women. It's not specific, I like it. A materialized view is like saying, hey, Click House. I'm gonna be looking for those books a lot. Can you just pull them aside and keep them handy? It's pre-processing a specific subset of data to make analysis lightning fast. So, it's not changing the original data. It's more like creating a shortcut. Exactly. And the cool thing is, this blog post we're talking about, it gives you the actual SQL code to create this materialized view. No way. They're just giving way the secret sauce. Oh, yeah. They're all about empowering people to analyze their data. I love it. But what exactly are we looking at with this materialized view? What's so special about the data it pulls out? Great question. So, it's designed to grab the key elements from that DynamoDBJ sound we talked about. Time stamps. Super important. Right. Got to know when things happen. Exactly. It also grabs those primary and un-sort keys. You know, the ones that uniquely identify your data. And here's the really clever bit. It captures both the new image and the old image of your data. Hold up. New image, old image. I do. Those sound intriguing. Tell me more. All right, so picture this. You're tracking customer orders and boom. Someone changes their shipping address. Happens all the time, right? The new image is like a snapshot of the order after the change. New address. Bam. There it is. Okay, big sense. But here's where it gets really useful. The old image. That's the order before the change. Ah, so you've got a record of both. You've got it. It's like a before and after. You can see exactly what changed when it changed. And that can give you some really valuable insights. Oh, I see. Like, if you start noticing a lot of address changes right after a big marketing campaign, maybe something's up with your shipping estimates on those products. Exactly. It's all about connecting those dots. And speaking of connecting dots, remember those timestamps we talked about? Yeah, the whole when aspect of the data. This materialized view, it doesn't just grab any old timestamp. It converts them into a format that Clickhouse absolutely loves. Because Clickhouse has a thing about time, right? Time is everything in data analysis. Especially for time series analysis, which let's face it is kind of what we're all about here, right? Totally. Seeing how things change over time, spotting those trends. It's like having a crystal ball, but for data. And a well-organized crystal ball at that. All right. So we've got our data flowing. We've created this materialized view. We're capturing those key elements. What's next in our data transformation journey? All right. Step three, we're going deeper. Now we're talking about flattening individual dynamo DB tables. Okay. Getting granular. So instead of just looking at our whole library, now we're organizing by genre. You got it. Imagine you've got separate tables for different parts of your application. Users, products, orders, that kind of thing. Right. Makes sense. Keep things tidy. Exactly. So we create separate materialized views for each of those tables. Ah, so each section of our library gets its own little shortcut. You're getting it. This way, when we're analyzing user behavior, we're not waiting through product data. When we're looking at order trends, we're not tripping over user information. It's all about making those queries laser-focused. Efficiency is key, I hear you. But, you know, some of our listeners out there, they might be thinking, hold on. I'm a single table, dynamo DB kind of person. I like to keep everything in one place. What about me? Excellent point. Single table design. It's definitely having its moment. And for good reason, it can be incredibly efficient. So how do we handle those single table setups? Does our trustee materialized view still apply? It absolutely does. It's just, well, we need to add a little finesse for our single table fans. Finesse. Okay, I'm all ears. So with single table design, instead of separate tables for every data thing, you're putting different types of data together in the same table. Right. I like that one drawer where I throw everything. Keys, mail, snacks. Exactly. And that's great for storage. But when you want to analyze something specific. It's a night. It can be. And that's where step four comes in. It's all about helping click house, understand those different entities within your single table. So even though it's one table, we're still setting up those little boundaries for analysis. You got it. Let's say you've got customer data and order data all in one table. Step four is saying, okay, for a customer analysis, we use this materialized view. For order trends, we use this other one. Divide and conquer, even within our single table. Exactly. It might seem like an extra step, but trust me, the payoff and speed and clarity. It's huge. This whole deep dive has been eye opening. We've gone from feeling lost in a sea of JSON to having this awesome toolkit for turning that data into something truly powerful. And the best part, you don't have to be a data scientist to make it work. Propel data, they lay it all out in their blog post. Instructions, sample code, the works. They're like the fairy godparents of data analysis, but instead of a magic wand, they give you sequel queries. But before we wrap up, I got to ask, it's got me thinking about the whole timing aspect. We talked about how to transform the data, but how often do we need to do this? Is real time always necessary? Oh, such a good question. And the answer is, it depends. If you're talking about something like high frequency trading, where every millisecond counts, then yeah, real time is key. Right, you need that data fresh off the presses. Exactly. But for a lot of businesses analyzing things like customer behavior, maybe a nightly or even weekly batch process makes more sense. So it's all about finding that balance between what's possible and what's practical for your specific needs. Absolutely. What's technically amazing isn't always what's strategically necessary. So for our listeners out there, don't be afraid to play around with these tools, explore your options, and find that sweet spot for your own data needs. And hey, if you figure out how to make my snack drawer is organized as a materialized view, let me know. You and me both. Thanks for joining us on this deep dive into DynamoDB and Clickhouse. Anytime. Until next time, keep those data insights flowing. Today's episode is brought to you by Propel, your Clickhouse partner in the cloud. We'll tell handles all the heavy lifting of running Clickhouse, letting you focus on what matters most, your data insights. Check them out at www.propelldata.com. (upbeat music) (upbeat music) (upbeat music) (upbeat music)