“Slow down, you move too fast…You got to make the morning last.”
Happy International Day of Families and Happy Chocolate Chip Day!
This week’s Journey was a bit of a laggard in comparisons to previous week’s journeys but still productive, nonetheless. This week we took a break from Machine Learning while sticking to our repertoire and with our reptilian programing friend. Our first stop was to head over to installing Python on Windows Server which we haven’t touched on so far.
As we tend to make things more challenging than they need to be we targeted an Oldie but a goodie Windows Server 2012 R2 running SQL Server 2016. Our goal to configure a SQL Server Scheduled Job that runs a simple Python Script which seemed liked a pretty simple task. We found an nice example of this exact scenario on MSSQL Tips — Run Python Scripts in SQL Server Agent
First, we installed Python and followed the steps and lo and behold it didn’t work right away. To quote the great Gomer Pyle “Surprise, surprise, surprise”. No worries we had this… After a little bit of troubleshooting and trying to interpret the vague error messages in the SQL Server Agent Error log we got it working… In turns out, we had a multitude of issues ranging from the FID that was running the SQL Agent service not having the proper entitlements to the directory where the py script lived and the more important prerequisite of Python not being in the User Environment Variables for the Service account to know where to launch the executable. Once resolved, we were off to the races or at least we got the job working.
At this point we were feeling pretty ambitious, so we decided rather than using lame MS Dos style Bat file we would use a cool PowerShell Script as a wrapper for our python code for the job… Cool but not so cool on Windows Server 2012 R2. First, we started out with set-executionpolicy remotesigned command which needs to be specified in order to execute PowerShell but because we were using an old jalopy OS we had to upgrade the version of the .Net runtime as well as the version of PowerShell. Once upgraded and we had executed a few additional commands and then we were good to go…
[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
Install-PackageProvider -Name NuGet -RequiredVersion 184.108.40.206 -Force
Install-Module -Name SqlServer -AllowClobber
After spending a few days here, we decided to loiter a little bit longer and crank out some SQL maintenance tasks in Python like a simple backup Job. This was pretty straight forward once we executed a few prerequisites.
python -m pip install — upgrade pip
Pip install pip
pip install pyodbc
pip install pymssql-2.1.4-cp38-cp38-win_amd64.whl
pip install — upgrade pymssql
Our final destination for the was head back over to previous jaunt and play with streaming market data and Python. This time we decided to stop being cheap and pay for an IEX account
Fortunately, they offer pay by the month option with opt out any time so it shouldn’t get too expensive.
To get re-acclimated we leveraged Jupyter notebooks and banged out a nifty python/pandas/matlib script that generates the top 5 US Banks and there 5-year performance.
“I have only come here seeking knowledge… Things they would not teach me of in college”
Below are some topics I am considering for my adventures next week:
- Vagrant with Docker
- Data Pipelines
- Google Cloud Pub/Sub (Streaming Data Pipelines)
- Google Cloud Data Fusion ( ETL/ELT)
- Back to Machine Learning
- ONTAP Cluster Fundamentals
- Google Big Query
- Python -> Stream Data from IEX -> Postgres
- Data Visualization Tools (i.e. Looker)
- ETL Solutions (Stitch, FiveTran)
- Process and Transforming data/Explore data through ML (i.e. Databricks)
- Getting Started with Kubernetes with an old buddy (Nigel)
Stay safe and Be well -
Originally published at http://sqlsquirrels.com on May 15, 2020.