Collations: Case-Insensitive SQL & Legacy Data Warehouse Migration

0:05Hey, I have the pleasure to host Kent for the second time and he's back to talk about collations. Hi, Kent. Hey, Yousef. How are you doing, man? >> All good. All good. Maybe as usual, introduce yourself. >> Yeah, of course. My name is Kent Martin. I'm the product manager at Databricks. I've been here for just over four years. I work on things like geospatial and

0:28helping out the team working on the query engine, the variant data type, but most recently in the last year focused on all things kind of legacy data warehouse core SQL constructs. So scripting, store procedures, recursive CTE and of course collations. uh collations is one of the features that I mean I'm not sure everyone like even

0:50everyone knows about but is certainly very helpful and very common when coming from like when I think when I think about coming from migrating from legacy data warehouses so yeah happy to talk about collations today it's a pretty cool feature >> yeah and and actually that was the main reason only few people knows about collation and that's why I wanted to bring you to be able to understand this is the reason why we have collations why

1:12they are helpful and how we can use them inside the DBC. >> Yeah, I mean let's talk about collations for a second. Like what are collations? Collations are rules that you can apply to how you sort and compare strings. So if I want uh to ignore, you know, the casing of my of my words. So instead of

1:35there being a capitalized version or a lowercase version and always compare these as equal, I can ignore the case of the words by using a collation specific rule. So there are other rules we can apply case sensitivity, accent sensitivity. We can use language specific rules. There's also the ability to trim trailing spaces. Uh so again

1:58there's a number of ways in which you can apply a collation rule and it makes in the end I think it makes just everything a little bit easier to sort of standardize upon how the data is processed and and analyzed but also it sort of cleans up your code. You no longer insert lower and upper and these other kind of expressions around. And the added benefit of using collations,

2:21this is the part that I think is like really cool, is that it can actually drive significant performance improvements by applying a collation as opposed to using things like upper and lower. So the actual query execution is more efficient when having a collation applied. >> Yeah, I remember I used to I used to use a lot this upper to upper to lower to make sure that I have the same the same

2:43string across all my uh all my all my text. And that was a nightmare. And I mean with this with this uh with collations things can get much easier reduce the number of functions and as you mentioned all their benefit is to improve the performance which is something we're looking for. Yeah, I mean we wanted to sort of simplify Databricks for customers who like this is

3:06such a common way in which you're working with data, right? I want to compare values. I want to sort values. Of course, you want to do this in the most efficient way possible. So building out collations makes a makes a ton of sense. And then we have kind of like an endless stream of customers who are moving from legacy data warehouse, enterprise data warehouse platforms like terod data, SQL server and others. And

3:29uh collations were used commonly across those implementations. And so when customers are looking to migrate, they want to have a story which is you know get me like to like kind of very similar or as similar as possible when I get to the new system. And so building out collations at that point just becomes sort of a no-brainer. And we know we've already got, you know, hundreds of customers that are using collations

3:52because it went into uh public preview in DVR version. I think it was 162, maybe it was 163, but since then we've kind of incrementally added new capabilities. So the first version was case and sensitivity sent in sensitivity sort of managing and all of the language versions and then from there we added RTR and then in the most recent data

4:14bricks runtime version 17.1 we've added support for collations to be specified at the catalog or schema level. So it just, you know, it's like trickle down, apply my collation rules to all my tables and the and the appropriate columns within those tables. So yeah, we've continued to advance collations since it's come out. >> That's amazing. And can you show us how we can like an example of uh how to use

4:37collation inside DBSQL? >> All right, Yousef, here we are. I've uh I'm attached to my SQL warehouse. Uh I'm using a notebook here just to kind of go vertically through it. um so everyone can easily follow along. All right. Uh yeah, I've got my catalog and schema set up. We've got uh our first sort of example here. I've rerun this uh just to

5:00sort of make it easier to walk through the code and the results uh of the output uh from each cell. So in this case I can I can actually uh see what the default collation is that is applied in the system. So when you use Databricks by default the collation is UTF8 binary. Uh so this is the way in which um you know your data will be sorted and compared right using this UT UTF8 uh

5:25binary collation. Now, if you want to see what that means, uh, let's go ahead. I've created a table, and this table includes a few interesting characters, right? There's some accents, there's some capitalization that's different. Basically, this is this is data that's coming in in a non-standard way, right? I have capitalized, I have lowercase, I've got names that are spelled the same. Um, you know, there's other

5:47characters in here. So, you want to see how how this works in Databricks today, you know, just by default. If I go ahead and say select from this table and order by the first name, you can see I get capitalized letters first, which is the structure of UTF8 binary, right? I get the capitalized letters first and then I get the lowercase letters and then there's the characters that have

6:09accents, you know, at the end. That's not what you want. That's not what I wanted. Um, what I do want though is to apply a standard set of rules. And I can do that by applying a default collation. So I can go ahead and alter this table and apply a default collation. In this case I'll choose UTF8 LC case. So I want to basically do sorting and comparisons as if you know all the data was

6:32lowercased. So here I've altered oh let's just see what I've done here. This is column test. This is the table. Oh yeah. And then I can actually this is neat too, right? Just because I have a collation at the table level um doesn't mean I can't have a different collation for a different column. I don't know how common that that is, but hey, the system is flexible. You can

6:54do many things. I can have different collations as needed. Uh, anytime I go ahead and alter uh an existing column uh to apply a collation, I'm always going to follow best practices here and recomputee statistics. Good for me. Okay. So now, if I have an existing table and I want to apply a collation, what I'm basically showing here is I I need to go and alter the table and alter

7:17those columns. the so there's a few that I've done here at my my demo is a little out of order so bear with me I'll scroll back up here just to say we have UTFA LC case at the table level we have unicode case insensitive and accent insensitive at the column level and then here I'm applying UTFA LC case RT trim on a different column a new column that I'm

7:39adding I hope you can keep track of all these columns because I almost am struggling I can >> so what you know one question here is what's the RTIM gonna This is this is the a cell that uh I'll just run here to show you. If I have text that I say I have trailing spaces clearly you can see the trailing spaces on this side. Would this return false or true with an Rtrim base collation? It's

8:03trimming the blank the blank spaces at the end. And of course it does return true. So yes, that's how Rtrim works. Okay. So let's go back to our table where I've added specific collations. I can do a describe. Now I can see that I have different collations for different columns. Okay, previously I loaded the same data or or I I have the data that we looked at previously already loaded in this table. So if I go back to

8:25my first name column which is case insensitive and accent insensitive now and I run this query select first name and do an order by you can see now >> the order makes sense and everyone's happy and now I get the results that I was looking for. So if I run down here again, Yousef, I I snuck one in here for you, buddy, just to emphasize the point

8:48about do I need to work with lower or upper. So I've created a new table. I've applied a default collation uh to the table. So again, this is not column specific collations. I can apply it at the table level. I've added some new values into my table. Yousef with a capital Y. Um, and if I come down here to my uh simple query and say select where name equals Yousef, but I use

9:11lowercase, it still returns the right result, which is doing the same thing as applying a lower. But in this case, when working with larger scale data, you're going to see efficiencies when using the collation, which is great. The last thing I want to show around collations is just what's happened uh in the Databricks runtime runtime 17.1 version, which is support at the catalog and schema level. So here I can say let's

9:34create a new schema and apply a default coalation. Again this is great because the coalation will cascade down to the lower entities within uh you know that schema. So the tables and columns where it could be applied um will honor the collation that's set. So here I've just taken the same data and I've put it you know here I'm working with um a new

9:57table called test two. This is all going into that schema uh that I just created. You can see when I describe the table, there's no collation value specified for these columns. Um but when I run this query again when I say select first name and order by, I get the right result. So again, it's applying uh the collation

10:19from the schema level down. So that's it. Um collations I can apply them at uh the expression level. I can apply them at the table, schema, and catalog level. Uh, and they're they're kind of easy to get started with. >> Yeah. So, they improve the performance and the benefit from the inheritance. You can go to the column level, which means you have full flexibility to do

10:41anything you want and make sure that you can backport what you have actually on your legacy data warehouse to DBSQL without any problem. That's awesome. Yeah. >> Let me let me just show you in the docs. Ah, >> of course. Go ahead. Yeah, there's great doc reference pages here on the collations and how to work with them. More importantly, I wanted to show just all of the possible collations that are supported. So, can you count those up as

11:04I scroll? No, there's a lot. >> No, that's >> this is all the language specific uh collations that we can apply. So, you know, for anyone in the world, this this is really like an important feature especially for, you know, global you know the globalized world, right? So, you know, in Europe where they speak many different languages with different accents and across the world, it's not just always English, right? Um, so and

11:26not always a a you know, the English alphabet. So, Calians can help you out uh across that. Uh the last thing I wanted to show was that we do have a pretty good blog. This blog talks about all of the uh the expressions and how to use them with examples that you can follow along. uh if you if you have Greek data the this is a great case.

11:49Um and then the last uh part here is just explaining the differences between performance that I think is worth calling out. So yeah, there's a there's blogs, there's docs. We'll make sure the notebook gets shared. Uh Yousef, thanks so much for having me on. >> You're welcome. Thank you. Thank you again. T is a