in this video we'll walk through how you can connect and query your
snowflake database directly through sql server
so as you can see i'm here in my snowflake environment and what we'll want to do is go
to help download and then in our case we want the odbc driver and we'll select snowflake repository
it'll open up a new tab here and in my case i want the 64-bit windows so i'll select that latest
and we'll download this file this is going to be the driver the odbc driver that's
compatible with snowflake that we can put on our computer i'll go ahead and save this
and when it's done go ahead and open it you may get this message that you know just kind
of giving you a heads up that it's an executable that's okay so go ahead and click ok and now we'll
walk through this wizard pretty straightforward next you can select the location to save it
next and install that's it okay so now this is complete and now the next step
is to add a new odbc source to our machine that points to snowflake
to do that let's go to search here i'm on windows and we'll do oh we'll do odbc and in my case i did
the 64-bit so i'll click this now you can add this this data source for just a particular user that
you're on or you can do it for the entire system which is what we'll do here so go to system dsn
and you'll see i have the 32-bit installed from a previous video but don't worry about this here
for now we're going to go through the steps to add a new one select add and we'll select the
snowflake driver that we just installed finish now here we're going to configure this data source to
point to our particular snowflake environment this first box here data source is what the actual name
of the data source is going to be you know as you refer to it once we get to sql server here you can
see for this old one i called this snowflake so i'll call this one it's called snowflake as well
and all this here is going to be based on your particular environment so i'll go ahead and
fill this out in terms of server all you need here is from your account number to snowflake
computing.com you don't need https or anything else so select copy and we'll paste that here
for database and schema in this example we'll primarily look at this
here this 5 train database google sheets so i'll go ahead and put that in here
but this will obviously be based on what you're working with
google sheets warehouse i'm going to do the compute warehouse
roll i'm going to do account admin just so there's no permissions issues but obviously like anything
else with snowflake you'll likely create a very specific role or something like this and you know
lock it down a bit more tracing we won't get into this too much i'll just say 4 for this select ok
so now we can see here we've added this 64-bit data source called snowflake using this driver
okay now here we are in sql server management studio and our next step is to create a linked
server that uses that new data source that odbc data source that we just created to point to
our snowflake environment to do that we'll go down to server objects and here is link servers
we can see there's a bunch of providers and one of the steps we actually need to take here in
order for this to work is right here with this msd asql provider go ahead and right click on
that this is this is a specific requirement here now we see here that the only thing selected
here is allow in processes and for this to work correctly with snowflake here what we want to do
is select a few more so go ahead and select nested queries level 0 only and supports like operator
so it should look like this when you're all done one two three four click ok now what we want to do
is actually add a new link server so to do that we can right click here and say new link server
now we have this pretty standard windows wizard here and we'll walk through this each step of
the way here so here the link server this is the name the link server will be referenced by we'll
call this snowflake test now you could create a link server to another sql server instance
but in our case what we're going to do is select other data source because our data
source is going to be using the odbc data source that we just created for provider so drop down
we'll drop down here and say microsoft oledb for odbc drivers select that guy
now product name product name is the data source to add as the link server you can just match it
to what you have here so we'll call this snowflake test and then really what's important is the data
source here as it says the system dsn of odbc data source so this actually has to be a system dsn now
we see this here before we saw there was that user and system option it must be a system dsn
and what we called it was just snowflake if you recall that was that first config attribute
there so we'll call this we'll call snowflake and this will let sql server know that it should look
on our system for a dsn odbc dsn called snowflake okay next is security and here what we'll do is go
for a login we'll select be made using this security context and again we're going to add our
snowflake username and password so i'll do mine and press ok now it's going to try to create this
and if you didn't get any errors here that's a good sign that means it was able to establish
that connection now at this point we have a linked server on our sql server instance that is pointing
actually to snowflake pretty cool and just to verify that everything is good here you
can right click and do test connection and we can see test connection to the link server succeeded
i'm going to open up a file that has a few queries here and the way we're going to do
this is using the open query function within sql server so what this will do is select all
from open query right here is the name of the link server so in our case we called it snowflake test
so whatever you called it you would put here and here is where you're going to pass the actual
query that you want so i know it says select all here but this is the query that will be run
on snowflake so just to give you a little preview of what this looks like i'm going to select run
and here's what it looks like it's from a previous video it's just five records of some nba players
their team and and whatnot so we can see all this information here now let's see if we can query
this same information but from sql server so let's run this query right here and see what happens
and there it is we were able to get the same result set in sql server by querying snowflake
so down here i just have a few other examples of how you might want to do this you know maybe
instead of having to always reference this you could pull everything into a cte and
and call it that way so then anytime you use nba players you're actually referencing this table
you could add it to a temp table and now work with it this way and just to clarify what's happening
as we said this query is being executed here so you're taking advantage at this point
of the snowflake processing power and everything is being tied as if you ran it from here
another thing that's pretty cool is that you can write other commands it's not just
select depending on your role and the permissions that you have you can update the data as well so
we see this right here let's say we want to update jimmy butler to have a position of
instead of forward to be test let's just say we're going to update we're going to run it
this way update open query and you know it's going to select that database it's going to return this
model and we're going to set the position and just run and run a set statement let's run that
one row affected so that actually worked and let's try to select from here again
and there it is now let's also look here let's see just confirm
and there it is we're interacting with the same data but from two different places getting back
to those initial reasons why you might do this you know instead of having data in different places
maybe you're migrating et cetera you can see how this is really valuable in terms of keeping
the data in one consolidated location but still being able to reference it in sql server as needed
the last thing i'll point out is the query history so if you go to history and i'm looking at user
we can see here odbc 2.231 these are all the commands the queries that were
run through sql server so it's capturing that here and we can see the select all i just wanted
to show that this is all here everything is getting its own query id and you can track
it just as if you were running it here through this interface itself because really it is very
similar the only difference is the interface that you're using but it's still using the same
compute interacting with the same storage but this time it's just being called through sql server
that'll do it for this video and hopefully now you can take this
information and run with it however you need to based on your particular
environment