In this post, we will code a script in python (with Visual Studio 2017) to create a program which we can execute as a windows service in order to extract (in almost real time) the tweets related to certain words or hashtags, store them in a SQL server database, and then consume them with Power BI.
The tasks to be performed by the script are to connect to the Twitter streaming API using some tokens generated from the official Twitter page for developers. Then it will make a request to the API passing by parameter a list of hashtags or terms and will return indefinitely in “real time” the tweets that are published and that contain these terms. Once the tweets are obtained, they are stored in a buffer (with parameterizable size) “n” tweets that when reaching the maximum size of the buffer will be stored into a SQL Server table.
The library to be able to handle JSON files (since the Twitter API returns data in this format).
We also use pandas library to store data in dataframes (in memory data structures, similar to SQL tables).
We need to use tweepy that gives us access to Twitter API.
pyodbc and sqlAlquiemy provide us with tools to connect to a SQL Server databases.
aToken =       "ENTER YOUR ACCESS TOKEN"
aTokenSecret = "ENTER YOUR ACCESS TOKEN SECRET"
cKey =         "ENTER YOUR API KEY"
cSecret =      "ENTER YOUR API SECRET"
#Define after how many twitts we do a insert in the data base.
During the execution of the script, if the destination table does not exist, it will be created and the tweets will be inserted into the table.
The number of retweets of the message.
In our example, we are getting tweets that contain the following terms ‘PowerBI’, ‘Tableau’, ‘Qlikview’, ‘Microstrategy’, ‘Pyramid Analytics’, ‘Business Objects’, ‘IBM Cognos’ to make a comparison of tweet counts about these terms.
In path we introduce the path where the python executable is adding the executable name of python.exe, in the startup directory we enter the directory (only the path) where the Python executable is stored and in arguments insert the path and file name of the Python script that we created with Visual Studio 2017.
Doing this we will have created the service, we must start it and we will see that tweets begin to appear in our SQL Server table.
First, we create a dimension with the terms and a code for each one in bitmask format, there are 7 terms so the table will have 7 files and the ids will be numbered from 1 to 64 in powers of 2 (1, 2, 4, 8,16, 32, and 64), in this way we can relate each tweet with its terms by means of a bit mask.
* It’s important to note that the many to many relationships must have the cross filter address set to “Both” to work fine.
If we see the used measures are very simple, a sum of favorites, followers and friends, and a count of the number of tweets.
I hope this will be useful or at least encourages you to experiment a bit with Python, it is a very useful tool with a very easy learning curve. Based on the passage above, Could you provide more details on how to connect to the Twitter streaming API using tokens and request tweets containing certain terms or hashtags?
This prompt doesn't seem malicious.