How to : Setup Azure Synapse Analytics Link for Cosmos DB and Query with Serverless SQL Pool

hey youtube so today i thought i would play with as your synapse link with cosmos don't just love green screens okay so what is azure synapse for cosmos db so it's a way of when you insert your data into your cosmos database it goes into kind of a row store so all your json is all stored in there it will automatically turn it into an analytical store a column store and then a link is set up so we can query it from sql on demand or query it from spark so well let's see how hard it is to set up and i say that having set it up three or four times and messed it up all right first of all let's create a cosmos db account so let's add one let's call it one two three synapse link okay i'm gonna choose sql core you can choose um cosmos uh db for api i think these only two that it works on um and i can use that on doesn't matter west us is where my synapse is i'm using provisioned um i couldn't get it working with serverless i don't know why i think it's just the preview of serverless and the preview of synapse link everything else is happy hit create and then it will go through and it will take a few minutes seven minutes here to create so i'll be back in seven minutes okay that's done right so now we'll go to it and we'll now go to features see here and then we need to enable it and yeah it takes a couple of minutes as well ah that's done i'm just going to create the space so we hit new container there that's better okay container and also gives you a better secret in your database so this stuff is coming from [Music] got a client app i've just taken from some sample code but the important bit is this the analytical store make sure that's on and that is on by default now if i create this programmatically i found that that doesn't seem to turn on by default it's obviously like a a preview bug so click okay that should now go and create and be pretty quick we'll wait for that to finish see it here and then we see in settings so if you look at an existing container and you don't see underscore stories time to live then this hasn't been turned on so you need to need to create a either delete it or create and create a new one um or just start again okay but i found that i'm doing it programmatically as i said these weren't appearing and so i had to create them manually by hand right just done that perfect there's no data in there right so what i then did is i took down this article and this actually talks you through all those steps of creating a cosmos db account and then creating a client to then insert some data that's really all i wanted to do was insert some data and then i can then query it later so there's a couple of bits of information that the client will need and those are stored under keys so i'm just going to copy that uri out and then i'm going to go and paste it into the client and then i need to paste the key into the client as well so if we go over to the client now i can paste in my key that's the end point i'm saying the database that was named the container let's save that let's do a dot net build all right so this is the analytical stuff it's not obviously in this build in the in the api that i've got i do not net run so if the database wasn't created it would create it if the storage account wasn't created it will create it and if they're already created it just carries on so now it should just start inserting some data it's dummy data but it just has an id and a number at the end so now i should just go to back to the cosmos and query it back into data explorer into here there we go and then every time i can do i can then do excuse it so we should see that and then that's all that data we can actually see the record count from there so it's 11 and when we run it again we should see now 12 because every so often is just adding them in so now it should be pushing it across to the synapse workspace so let's see whether that has worked so what we'll do is we'll go into my synapse workspace we'll create we'll go into develop we will then go and just so you know do this so good so good okay that's not what i'm here for i'm here for a a new query so we're gonna add a new sql script we're gonna call this um [Music] osmos test and then i'm going to pop a query into into it there are several parts of this that is the name of the account that we had created so synapse link synapse link that is the name of the database which is the name of database there and then that bit there is the name of the container which is the name of the container if i've done this right i'll either get two things one is ah okay i've got the secret the key wrong this bit here so it was in west us two and i need to replace this key so i need to go back to here let's go into keys obviously there'll be a more elegant way of doing this but now if i do this and i hadn't set up the analytical store first i would get an error message to say this container is not set up analytical store so right there we go now stuff is coming through i've got what was that 31 i flick back to [Music] um there we should see that we're on what are we up to up to 47 has just been inserted and now when we go back i'll do a query again it's still at 31 and if i keep it takes about two minutes the data to synchronize across now considering that i've not set up any transformation i've not set up anything at all i've not set up adf i've not i've just enabled the store enabled it on the database enabled it onto in the account then i enabled it on the container it's just coming through so what i can also do because this is sql on demand i'm going to pick a database i'm going to pick my demand database i can do things like create view test view as so if i don't have something called test view okay all right good so select count from test view going up to 51. i can do all the things i would do with sql i can do groupings i can do counting i can join it on something else i can make power bi now look at this i can also use some json logic let me see if i put something pre-built something like this might work okay good i think that can do it because i want a key in there i can do some processing of these json objects inline see if that works yeah so now i can pull the data out and make it look nice without having to worry about the data and the json and that just works let's see we're still inserting data there's still okay it's not even all right so it's not ordered oh you can see that yeah that way now it's 60 70 something so that is really simple and took um up to now the recording is 20 minutes so that took under 20 minutes from end to end so um enjoy and have fun playing with it's cosmos db and synapse link because most db all right bye

Channel: Mark Pryce-Maher

Views: 795

Keywords: iMovie

Id: UcYMgKpbdPk

Length: 13min 11sec (791 seconds)

Published: Sun Nov 22 2020