Listing SQL Server 2017 Installed Anaconda Packages Using PowerShell

SQL Server 2017 comes with the option to include Python (Anaconda) during SQL Server installation. It will install Anaconda with a small set of python packages for the purpose of creating Data Science solution that sre executed within T-SQL statement(s). Unfortunately, there’s no documentation of what Anaconda packages are installed with SQL Server.

Much Easier with Full Installation

Doing the full Anaconda installation, gives the necessary commands to query what has been installed in your system. This makes it much easier to list all existing installed packages.

In the full installation of Anaconda, done separate from SQL Server, you can use the following command to list all packages installed:

conda info

But, with SQL Server 2017 is a different story.

Where’s my SQL Server Anaconda packages?

These packages are found in the default installation location: “C:\Program Files\Microsoft SQL Server\”YourSQLServerInstanceName”\PYTHON_SERVICES\conda-meta

All packages are of file type *json. Each Anaconda package will named with: the package name, package version, and python version number. But, this makes it hard to view using “File Explorer“.

So, solution to list the SQL Server Anaconda packages in a proper format will be needed.

PowerShell To The Rescue

So, here’s a PowerShell function that will list all installed Anaconda packages in SQL Server 2017. This will required to enter some parameters, such as: SQL Server Installation Location, and SQL Server Instance name.

function Get-SQLServerAnacondaPkgList
{
[CmdletBinding()]
Param (
[string]
$SQLServerInstallationDrive = 'C:',
[string]
$SQLServerInstanceName
)

$SQLServerInstallationLocation = "$($SQLServerInstallationDrive)\Program Files\Microsoft SQL Server\MSSQL14.$($SQLServerInstanceName)\PYTHON_SERVICES\conda-meta"
$SqlAnaconda = Get-ChildItem $SQLServerInstallationLocation -File *.json;

[array]$global:SqlCondaPkgList = $null;
[array]$global:SqlCondaPkgList = foreach ($Pkg in $SqlAnaconda.name)
{
## - Build PSCustomObject:
[PSCustomObject]$PkgList = New-Object PSObject -Property @{
PackageName = $Pkg.Split('-')[0];
PackageVersion = $Pkg.Split('-')[1];
PackageLocation = $SQLServerInstallationLocation;
}; $PkgList;
};
$global:SqlCondaPkgList;
}

## To execute function:
$SQLServerInstallationDrive = 'C:'
$SQLServerInstanceName = "MSQL2K17A"

Get-SQLServerAnacondaPkgList -SQLServerInstallationDrive $SQLServerInstallationDrive `
-SQLServerInstancename $SQLServerInstanceName;

## - Or, after executing the function, go back to use
## - the existing global variable:
$global:SqlCondaPkgList | Select-Object PackageName, PackageVersion

Bottom line

Executing Anaconda within T-SQL seems only available on Windows version. But, you can still create the Python code and do some testing on Linux.

The total number of packages provided with Microsoft SQL Server 2017 is about 146. Now, in the full version of Anaconda, there is a total of about 217 python packages.

Full listing of all Anaconda Packages installed for SQL Server 2017 (See below):

PackageName PackageVersion
----------- --------------
alabaster 0.7.10
babel 2.4.0
blaze 0.10.1
bleach 1.5.0
bokeh 0.12.5
bottleneck 1.2.0
bzip2 1.0.6
cffi 1.9.1
chest 0.2.3
click 6.7
cloudpickle 0.2.2
colorama 0.3.7
conda 4.3.22
conda env
configobj 5.0.6
console_shortcut 0.1.1
cryptography 1.7.1
curl 7.52.1
cycler 0.10.0
cython 0.25.2
cytoolz 0.8.2
dask 0.14.1
datashape 0.5.4
decorator 4.0.11
dill 0.2.5
docutils 0.13.1
entrypoints 0.2.2
et_xmlfile 1.0.1
flask 0.12.1
flask cors
freetype 2.5.5
h5py 2.7.0
hdf5 1.8.15.1
heapdict 1.0.0
html5lib 0.999
icu 57.1
idna 2.2
imagesize 0.7.1
ipykernel 4.6.0
ipython_genutils 0.2.0
ipython 5.3.0
ipywidgets 6.0.0
itsdangerous 0.24
jdcal 1.3
jinja2 2.9.6
jpeg 9b
jsonschema 2.5.1
jupyter_client 5.0.1
jupyter_console 5.1.0
jupyter_core 4.3.0
jupyter_kernel_gateway 2.0.0
jupyter 1.0.0
libpng 1.6.27
libtiff 4.0.6
llvmlite 0.16.0
locket 0.2.0
lxml 3.7.3
markupsafe 0.23
matplotlib 2.0.0
menuinst 1.4.2
mistune 0.7.4
mkl 2017.0.1
mkl service
mpmath 0.19
multipledispatch 0.4.9
nbconvert 5.1.1
nbformat 4.3.0
networkx 1.11
nltk 3.2.2
notebook 5.0.0
numba 0.31.0
numexpr 2.6.2
numpy 1.12.1
numpydoc 0.6.0
odo 0.5.0
olefile 0.44
openpyxl 2.4.1
openssl 1.0.2k
pandas 0.19.2
pandas datareader
pandasql 0.7.3
pandocfilters 1.4.1
partd 0.3.7
path.py 10.1
pathlib2 2.2.1
patsy 0.4.1
pickleshare 0.7.4
pillow 4.1.0
pip 9.0.1
prompt_toolkit 1.0.14
psutil 5.2.1
py 1.4.33
pyasn1 0.2.3
pycosat 0.6.1
pycparser 2.17
pycrypto 2.6.1
pycurl 7.43.0
pygments 2.2.0
pyodbc 4.0.16
pyopenssl 16.2.0
pyparsing 2.1.4
pyqt 5.6.0
pytables 3.2.2
pytest 3.0.7
python 3.5.2
python dateutil
pytz 2017.2
pywavelets 0.5.2
pywin32 220
pyyaml 3.12
pyzmq 16.0.2
qt 5.6.2
qtconsole 4.3.0
requests 2.13.0
requests file
ruamel_yaml 0.11.14
scikit image
scikit learn
scipy 0.19.0
seaborn 0.7.1
setuptools 27.2.0
simplegeneric 0.8.1
sip 4.18
six 1.10.0
snowballstemmer 1.2.1
sphinx 1.5.4
sqlalchemy 1.1.9
sqlparse 0.1.19
statsmodels 0.8.0
sympy 1.0
testpath 0.3
tk 8.5.18
toolz 0.8.2
tornado 4.4.2
traitlets 4.3.2
unicodecsv 0.14.1
vs2015_runtime 14.0.25123
wcwidth 0.1.7
werkzeug 0.12.1
wheel 0.29.0
widgetsnbextension 2.0.0
win_unicode_console 0.5
xlrd 1.0.0
xlsxwriter 0.9.6
xlwt 1.2.0
zlib 1.2.8

So, there’s plenty of room to learn with Python Data Science and SQL Server 2017.

Be Bold! Learn PowerShell Core!