Hello and welcome to ZappySys ODBC PowerPack 101.
Today you will learn how to load REST API data into SQL Server using ZappySys data gateway
and ZappySys ODBC drivers.
Before starting the presentation let me briefly tell what ZappySys data gateway is.
It's a tiny SQL Server which gets installed as Windows service as a part of ZappySys ODBC
PowerPack.
Basically you can view it as a bridge between your client application and a data source
you want to access.
In our case the client application is a SQL Server and the data source is a REST API service.
Actually with ZappySys data gateway you can also load Soap API data, XML, CSV files, ODBC
sources or other ZappySys ODBC driver based sources.
At the end of the video I will go through other use cases of when to use ZappySys data
gateway and tell more about it.
Okay so we can start.
So suppose you have this REST API service and it returns customers in JSON format.
Many customers, and it returns it as JSON Array under property value.
And you want to load all these customers into your SQL Server.
And it's almost like you want to write a query like this, select customers into customer
table from this REST API.
And actually ZappySys data gateway provides you with that kind of functionality.
The thing you will need to do is just create a linked server to the gateway.
And write a similar query like this and load the customers into the table.
So to start first thing you should do is open ZappySys data gateway configuration manager.
Which is found in Windows start menu in ZappySys folder.
And once you do that create a user.
It's very similar to SQL Server logins and users.
So we will just create one, make it an administrator.
And after that you are ready to create a data source.
So click Add, and give it a name, I will name it OData.
And in the connector type there are several options you can use.
So use ODBC generic and if you have a data source create it in ODBC data source administrator.
Just make sure that the data source is created under system DSN tab.
And that you run 64 bit version of ODBC data source administrator.
Because otherwise the gateway won't be able to access the data source.
And then you can use native ZappySys ODBC drivers.
So it will just create data source right in the gateway.
I should mention that it is as twice fast as ODBC data source based on ZappySys ODBC
drivers so if you have one I suggest switching to native.
So now I'm selecting ZappySys JSON drivers and since our data is in JSON this box tells
me I have to configure the settings.
And now I will just have to paste the link here, go to preview to make sure I get the
data.
Click OK, I got the data.
The problem here is that all customers are in one row in one cell.
So I will just exchange replace this dollar sign with the column name where the customers
are located.
To make things easier and quicker you can also use this list to select data from the
populated tables.
Okay I'm doing preview again.
And okay, so I get the customers as I want, one customer per row.
Okay I'm clicking Okay, then I have to edit the users.
Add the admin, give it full permissions, clicking okay.
Now I have to go to general tab and make note on what port the gateway is running and make
sure SQL Server compatibility check box is checked too.
Okay so once I do that I can just save the data source and restart the gateway.
And then go back to SSMS and add a linked server.
Okay.
I will give it a name, gateway, in the provider SQL Server native client version 11.
In the data source enter the address where your gateway is running, local host in my
case.
Then put a comma and then enter the port number on which it runs.
In the catalog field enter the data source name.
In our case it's OData.
Then don't forget to go to security tab and configure the user.
It's the same user we created and assigned to the data source.
Clicking okay, created the linked server.
And now it's time to execute the query.
Okay, but before executing the query let's modify it a bit.
Gateway.
And I will use the same query we used in the preview.
Okay, 20 customers loaded.
Checking on that, and voila, and as you see customers are loaded into the SQL Server.
So that's how easy to run, to load REST API data into SQL Server using ZappySys data gateway.
The next part I will show you how to get data from products.
Not from customers.
And how to reuse the same data source but just override the URL.
We start from where we left.
So you were using this query and getting customers very happily.
And then you discovered that you need to get products as well.
So the service stays the same, just to the table name of the OData changes.
Meaning the URL changes.
And you don't need to create another data source, just for products.
We can use the data source for customers and just override the URL.
And to do that just go to the gateway, again configure the data source.
We will be overriding this property.
And to do that open query builder.
And you can override many properties here.
We will override URL, we can override let's say HTTP request method and basically all
properties you saw in the properties tab.
I'm clicking okay, I replacing dollar sign with value to get one product row.
Okay.
We got the products.
And to make things easier we can go to code generator and copy the T-SQL
query it has generated for us.
So I'm copying the query, clicking okay.
Going back to SMS, based in the query.
We need to use our gateway.
And let's execute the query.
And you see instead of customers we retrieved products.
So you see how it's easy to override a URL for the same data source and reuse it.
But it's not always possible to override a property and sometimes you have to create
another data source.
So if that's the case you have to know that for that data source you will need to create
another linked server.
So one linked server for one data source.
In the next part I will show you how to parametrize a query inside a Stored Procedure.
So we start from where we left again.
And you are querying products and customers from REST API successfully.
And then you notice that throughout all your database you have these queries repeating
themselves in all over the places.
And you start thinking that maybe you are about to create a start procedure.
And make it parametric so you have a variable there and you can define and set what data
you want to retrieve, particularly products or customers.
So let's do that, let's create a start procedure.
So I'm copying this query and pasting it here in the start procedure template I have.
As you see it has a variable, a parameter table name which will set what URL to retrieve.
But only the last part of the URL.
And as you see we will define the query, then we will execute it here.
Okay.
And the problem now is that we cannot insert variable here because open query doesn't allow
us that.
So we will have to make this whole query dynamic not only this part.
To do that we will need to escape single quote.
So what I do I just select the query and then I replace one single quote with two single
quotes.
Let's do that.
Okay.
And once the single quotes are escaped I can put the query here.
And now I'm ready to make this query dynamic.
So namely I can replace this part products with variable.
Table name.
Okay, looks good.
Let's select the procedure and create it.
Okay.
Now we are ready to query the customers.
Okay, we retrieved customers.
Now let's do the same for products.
And we've also got the products.
So this concludes the demo of how to load REST API data into a SQL Server override a
URL and execute dynamic queries.
In the next part I am going to cover other use cases and talk about ZappySys data gateway
in depth.
As I talked in the introduction you can use ZappySys data gateway as a bridge between
your client application and a data source.
It uses Microsoft TDS protocol for communication so you can connect it as you would connect
to a Microsoft SQL Server.
Therefore any SQL Server compatible driver can be used to connect to the gateway.
For example in JDBC, ADO.net, OLEDB or ODBC driver.
Your client application can be a SQL Server as you learned today, Pentaho,
Java application or any application with any SQL Server compatible driver.
Basically ZappySys data gateway can feed data to your client app from ODBC data source or
a data source based on a ZappySys ODBC driver.
In turn, an ODBC data source would get data from the underlying source, for example a
database Excel file etc.
And similarly ZappySys ODBC driver would connect to a REST API, Soap API and XML, CSV or other
file, and then retrieve data too.
Now let's move on and let me introduce you with other use cases of the gateway.
You can use ZappySys data gateway in the following scenarios.
In the first scenario your client application doesn't support ODBC connectivity.
For example it's a SQL Server and Java application on a Google spreadsheet.
In the second use case you need to access ZappySys ODBC driver based data source on
a non Windows machine.
For instance a computer with a Linux Mac Os or Solaris.
So you would install ZappySys ODBC PowerPack on another machine based on Windows and then
connect to ZappySys data gateway from a non Windows machine.
In the third scenario you want all data sources to be accessed from one place instead of configuring
each ODBC data source on every client machine.
In the last case you are not allowed to install third party ODBC drivers on the server due
to a compliance reason, simply meaning system administrator doesn't allow you to install
ZappySys ODBC PowerPack directly on the server you want to access data on.
Therefore you will need to install ODBC Power Pack on another machine and configure data
sources using the gateway.
Today you learned how to get data from REST API and load it into SQL Server using ZappySys
data gateway and ZappySys ODBC drivers.
At first we executed a simple query and queried customers, then over wrote the URL of the
data source and retrieved products.
Finally we created a start procedure with a parameter which can query any OData table
by overriding the URL.
Now you can easily use the concepts we went through to load data into SQL Server from
almost any source be it Soap API, XML or CSV file or ODBC data source.
Please share your thoughts and questions in comments section.
Also don't forget to check other ODBC Power Pack videos for more use cases.
Thanks for watching.