March 14, 2016 at 6:37 am
Hi all
My company has a product which is based on SQL Server 2014, and we're looking to add some integrations to our feature set. This will include both consuming data from third party cloud-based products, and posting data back to them.
Various products exist in our industry, and of course they have different ways of interacting with them (some use XML, some JSON, some supply paginated data, etc). Consumed data all needs to be brought into our SQL db, and when it changes at our end we need to feed it back to the third party products.
I imagine this is a pretty common requirement. Currently though my developers have SQL knowledge but are not API guys. My question is, what's the best place to start to learn about using API's with SQL Server? Is there online training out there that's focused on this topic?
Also interested to know your opinions on which SQL-friendly technologies are best for interfacing SQL and third-party API's. We could write custom code for each one, but I can't help thinking there must be some shortcuts out there (other than Zapier, which unfortunately doesn't work with the products we need to integrate with).
Thanks in advance.
March 15, 2016 at 4:22 pm
This is kind of a really open ended question. I think if you have specifics, like JSON <-> SQL Server, it's easier.
The API from third parties dictate what you need to do, and thus, your API. As far as how you move this data to/from SQL Server, that's really dependent on your skills.
Many people favor a RESTful API, but that might not work for you.
I think that an API is really a complex, convoluted topic. Different people do things in different ways, so there will be all sorts of advice on how to do an API well. Arguably MS has some good ones, and some horrible ones.
I would say that spend a few days researching, and having different people look at different types of APIs. Really you're saying that you need an
a)SQL API (procs/queries) - get data in and out of SQL.
b)external API - some method of getting/pushing data for customers/applications.
I'd look at b). a) is easy. Just map things through.
What's I'd consider is where things may change, what incorrect data you'll get, and how flexible you can be to accommodate data in/out of various tables. Can you accept incomplete info? How do you surface keys and update information over time.
April 9, 2016 at 7:47 am
This is how I tackle this problem.
Just to give some context. I work in the digital marketing industry. Most of our data consumption is via data feeds such as API's like Google and so forth. I'm also the data architect behind the RDBMS (SQL Server) that is absorbing some of these data feeds.
Most of the time, the vendor providing the data has a set of API client libraries that you can download in a number of different language flavors. Using Google as the example here, they offer their libraries in 10 different languages. If you can't find a language you want to use, you can normally find those languages on open source repositories like GitHub.
Google Client Library Homepage - https://developers.google.com/api-client-library/[/url]
These libraries come with sample code and ways to connect to almost every Google service you can think of from Youtube to Google Maps all in one installation of the language of your choice. A good number of vendors are starting to follow similar examples and at least offering something in one language.
That being said, I would start here. I'm sure you subscribe to at least one Google service you can test the client libraries with. If not, then I would shoot over to Twitter and download theirs. They will allow you to tap into the Twitter feed, your feed and more.
For your test, I would stick to only Python unless you have a another personal preference. Python is easy to learn and easy to connect to almost any API on the client side even if the vendor does not provide you with the API client libraries. Python has many packages that allow you to connect to anything via HTTPS, FTP or even just scraping someones website to create your own data feed from scratch and feeding it into your SQL Server.
In that, Python allows simple conversion from the ever popular JSON format to CSV or even directly feeding data from JSON into SQL Server with the use of ORM SQL ALchemy.
Once you get to a level of Python connecting to these vendors, pulling data down to a flatfile or even pushing data directly into a staging table in SQL Server, then you can integrate it by simply executing the Python scripts with SSIS or calling them directly with TSQL.
April 9, 2016 at 8:45 am
Here is a basic example of how easy it is to use Python to look at tweet feed.
This link walks you through Python, the Twitter API and even how to read/convert JSON.
# Import the necessary package to process data in JSON format
try:
import json
except ImportError:
import simplejson as json
# Import the necessary methods from "twitter" library
from twitter import Twitter, OAuth, TwitterHTTPError, TwitterStream
# Variables that contains the user credentials to access Twitter API
ACCESS_TOKEN = 'YOUR ACCESS TOKEN"'
ACCESS_SECRET = 'YOUR ACCESS TOKEN SECRET'
CONSUMER_KEY = 'YOUR API KEY'
CONSUMER_SECRET = 'ENTER YOUR API SECRET'
oauth = OAuth(ACCESS_TOKEN, ACCESS_SECRET, CONSUMER_KEY, CONSUMER_SECRET)
# Initiate the connection to Twitter Streaming API
twitter_stream = TwitterStream(auth=oauth)
# Get a sample of the public data following through Twitter
iterator = twitter_stream.statuses.sample()
# Print each tweet in the stream to the screen
# Here we set it to stop after getting 1000 tweets.
# You don't have to set it to stop, but can continue running
# the Twitter API to collect data for days or even longer.
tweet_count = 1000
for tweet in iterator:
tweet_count -= 1
# Twitter Python Tool wraps the data returned by Twitter
# as a TwitterDictResponse object.
# We convert it back to the JSON format to print/score
print json.dumps(tweet)
# The command below will do pretty printing for JSON data, try it out
# print json.dumps(tweet, indent=4)
if tweet_count <= 0:
break
In this example, it's basically looping through the first 1000 Twitter statuses of I think the global feed. It then prints the result via the JSON module to screen. In your case, you may want to instead convert the data from JSON to CSV and then save it as a flat file for SQL Server to ingest with an SSIS package.
Pretty simple, easy and all in one TwitterAPI.py file.
August 15, 2017 at 11:16 am
This was removed by the editor as SPAM
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply