Add SQL Server 2022 workflow contained on runner VM with Python #38
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| # This workflow uses actions that are not certified by GitHub. | |
| # They are provided by a third-party and are governed by | |
| # separate terms of service, privacy policy, and support | |
| # documentation. | |
| # | |
| # See https://github.com/r-lib/actions/tree/master/examples#readme for | |
| # additional example workflows available for the R community. | |
| name: R 4.2 - Python 3.10 | |
| on: | |
| push: | |
| branches: [ master ] | |
| pull_request: | |
| branches: [ master ] | |
| workflow_dispatch: | |
| jobs: | |
| SQLServer2022: | |
| runs-on: windows-2022 | |
| env: | |
| # Define CI to skip some test case. | |
| CI: True | |
| r-version: "4.2.0" | |
| python-version: "3.10.2" | |
| sql-platform: "box" | |
| defaults: | |
| run: | |
| shell: cmd | |
| steps: | |
| # Set password in Github env but don't display output. | |
| # Note for github action you need to use double %, when testing in a local VM | |
| # %i would be the correct format. | |
| - name: Generate password | |
| run: | | |
| setlocal enabledelayedexpansion | |
| set password=%random%%random%%random%qaZ~@ | |
| for /f "delims=" %%i in ('echo !password!') do echo dbPassword=%%i >> "%GITHUB_ENV%" | |
| # /SAPWD=%dbPassword% /SECURITYMODE=SQL /TCPENABLED=1 allows connecting using localhost without windows account. | |
| # /FEATURES=SQLEngine,ADVANCEDANALYTICS are the machine learning services options. | |
| - name: Install SQL Server 2022 | |
| run: | | |
| set CurrentDir=%cd% | |
| choco install sql-server-2022 -y -params "'/SAPWD=%dbPassword% /SECURITYMODE=SQL /TCPENABLED=1 /INDICATEPROGRESS /ACTION=Install /FEATURES=SQLEngine,ADVANCEDANALYTICS /INSTANCENAME=MSSQLSERVER /IACCEPTSQLSERVERLICENSETERMS'" | |
| shell: cmd | |
| - name: Install sqlcmd | |
| run: choco install sqlcmd | |
| shell: cmd | |
| # Saves the chocolately logs which will likely hold the errors needed to debug a failed installation of SQL Server 2019. | |
| - name: Save Logs as artifact | |
| if: always() | |
| uses: actions/upload-artifact@v4 | |
| with: | |
| name: Chocolatey Logs | |
| path: C:\ProgramData\chocolatey\logs\chocolatey2022.log | |
| retention-days: 20 | |
| - name: Check Connectivity to SQL Database | |
| run: | | |
| sqlcmd -S localhost -U SA -P %dbPassword% -d Master -l 5 -Q "SELECT @@VERSION" | |
| shell: cmd | |
| - name: Checkout Branch | |
| uses: actions/checkout@v2 | |
| - name: Move AirlineTestDB.bak to correct file location for backup command | |
| run: copy "AirlineTestDB.bak" "C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Backup\AirlineTestDB.bak" | |
| - name: Restore AirlineTestDB | |
| run: sqlcmd -S localhost -U SA -P %dbPassword% -l 5 -Q "USE [master]; RESTORE DATABASE [AirlineTestDB] FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Backup\AirlineTestDB.bak' WITH FILE = 1, MOVE N'AirlineTestDB' TO N'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\AirlineTestDB_Primary.mdf', MOVE N'AirlineTestDB_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\AirlineTestDB_Primary.ldf', NOUNLOAD, STATS = 5;" | |
| - name: Create Logins for Test DBs | |
| run: sqlcmd -S localhost -U SA -P %dbPassword% -l 5 -Q " | |
| CREATE LOGIN AirlineUser WITH PASSWORD = '%dbPassword%', CHECK_POLICY=OFF, CHECK_EXPIRATION = OFF, DEFAULT_DATABASE=AirlineTestDB; | |
| CREATE LOGIN AirlineUserdbowner WITH PASSWORD = '%dbPassword%', CHECK_POLICY=OFF, CHECK_EXPIRATION = OFF, DEFAULT_DATABASE=AirlineTestDB;" | |
| - name: Alter AirlineUser | |
| run: sqlcmd -S localhost -U SA -P %dbPassword% -l 5 -Q " | |
| USE AirlineTestDB; | |
| ALTER USER [AirlineUser] WITH LOGIN=[AirlineUser]" | |
| - name: Alter AirlineUserdbowner | |
| run: sqlcmd -S localhost -U SA -P %dbPassword% -l 5 -Q " | |
| USE AirlineTestDB; | |
| ALTER USER [AirlineUserdbowner] WITH LOGIN=[AirlineUserdbowner]" | |
| - name: Alter Authorization | |
| run: sqlcmd -S localhost -U SA -P %dbPassword% -l 5 -Q " | |
| USE AirlineTestDB; | |
| ALTER AUTHORIZATION ON SCHEMA::[db_owner] TO [AirlineUserdbowner]" | |
| # https://learn.microsoft.com/sql/machine-learning/install/sql-machine-learning-services-windows-install-sql-2022?view=sql-server-ver16#setup-r-support | |
| - name: Install R for MLS | |
| run: | | |
| curl -L -o R-4.2.0-win.exe https://cloud.r-project.org/bin/windows/base/old/4.2.0/R-4.2.0-win.exe | |
| R-4.2.0-win.exe /VERYSILENT /DIR=C:\MLS\R | |
| - name: Install CompatibilityAPI and RevoScaleR after installing their dependencies (iterators, foreach, R6, and jsonlite). | |
| run: | | |
| C:\MLS\R\bin\Rscript.exe -e "install.packages(c('iterators', 'foreach', 'R6', 'jsonlite'), repos='https://cloud.r-project.org/', lib='C:\\MLS\\R\\library')" | |
| C:\MLS\R\bin\Rscript.exe -e "install.packages('https://aka.ms/sqlml/r4.2/windows/CompatibilityAPI_1.1.0.zip', repos=NULL, lib='C:\\MLS\\R\\library')" | |
| C:\MLS\R\bin\Rscript.exe -e "install.packages('https://aka.ms/sqlml/r4.2/windows/RevoScaleR_10.0.1.zip', repos=NULL, lib='C:\\MLS\\R\\library')" | |
| - name: Configure the R runtime installed for MLS with SQL Server | |
| run: C:\MLS\R\library\RevoScaleR\rxLibs\x64\RegisterRext.exe /configure /rhome:"C:\MLS\R" /instance:"MSSQLSERVER" | |
| # https://learn.microsoft.com/sql/machine-learning/install/sql-machine-learning-services-windows-install-sql-2022?view=sql-server-ver16#setup-python-support | |
| #- name: Install Python for MLS | |
| # run: | | |
| # curl -Lo python-installer.exe https://www.python.org/ftp/python/3.10.2/python-3.10.2-amd64.exe | |
| # python-installer.exe /passive InstallAllUsers=1 TargetDir=C:\MLS\Python /log C:\MLS\install.log | |
| #- name: Save Python Logs as artifact | |
| # if: always() | |
| # uses: actions/upload-artifact@v4 | |
| # with: | |
| # name: Python Installation Logs | |
| # path: C:\MLS\install.log | |
| # retention-days: 20 | |
| - name: Set up Python ${{ env.python-version }} | |
| uses: actions/setup-python@v5 | |
| with: | |
| python-version: ${{ env.python-version }} | |
| - name: Install revoscalepy and dependencies | |
| working-directory: ${{ env.Python3_ROOT_DIR }} | |
| run: | | |
| python -m pip install -t "${{ env.Python3_ROOT_DIR }}\Lib\site-packages" dill numpy==1.22.0 pandas patsy python-dateutil | |
| python -m pip install -t "${{ env.Python3_ROOT_DIR }}\Lib\site-packages" https://aka.ms/sqlml/python3.10/windows/revoscalepy-10.0.1-py3-none-any.whl | |
| - name: Grant READ/EXECUTE access to installed libraries | |
| run: | | |
| icacls "${{ env.Python3_ROOT_DIR }}\Lib\site-packages" /grant "NT Service\MSSQLLAUNCHPAD":(OI)(CI)RX /T | |
| icacls "${{ env.Python3_ROOT_DIR }}\Lib\site-packages" /grant *S-1-15-2-1:(OI)(CI)RX /T | |
| - name: Configure the Python runtime installed for MLS with SQL Server | |
| working-directory: ${{ env.Python3_ROOT_DIR }}\Lib\site-packages\revoscalepy\rxLibs | |
| run: .\RegisterRext.exe /configure /pythonhome:"${{ env.Python3_ROOT_DIR }}" /instance:"MSSQLSERVER" | |
| - name: Enable External Scripts | |
| run: sqlcmd -S localhost -U SA -P %dbPassword% -Q "EXEC sp_configure 'external scripts enabled', 1;" | |
| - name: Reconfigure | |
| run: sqlcmd -S localhost -U SA -P %dbPassword% -Q "RECONFIGURE WITH OVERRIDE;" | |
| - name: Restart the Service | |
| run: | | |
| sqlcmd -S localhost -U SA -P %dbPassword% -Q "SHUTDOWN WITH NOWAIT" | |
| timeout /t 5 /nobreak | |
| net start "MSSQLSERVER" | |
| - name: Execute SPEES for R | |
| run: sqlcmd -S localhost -U SA -P %dbPassword% -l 5 -Q " | |
| EXEC sp_execute_external_script @language =N'R', | |
| @script=N' | |
| OutputDataSet <- InputDataSet; | |
| ', | |
| @input_data_1 =N'SELECT 1 AS hello' | |
| WITH RESULT SETS (([hello] int not null));" | |
| - name: Execute SPEES for Python | |
| run: sqlcmd -S localhost -U SA -P %dbPassword% -l 5 -Q " | |
| EXEC sp_execute_external_script @language =N'Python', | |
| @script=N'OutputDataSet = InputDataSet;', | |
| @input_data_1 =N'SELECT 1 AS hello' WITH RESULT SETS (([hello] int not null));" | |
| - name: Set up R ${{ env.r-version }} Runtime | |
| uses: r-lib/actions/setup-r@v2 | |
| with: | |
| r-version: ${{ env.r-version }} | |
| - name: Install R Package Dependencies | |
| uses: r-lib/actions/setup-r-dependencies@v2 | |
| with: | |
| cache-version: 2 | |
| working-directory: ./R | |
| extra-packages: | |
| #Retrieves most recent odbc pkg from cran to avoid errors seen in older versions. | |
| #Current version needs R >=3.6 and is currently failing | |
| cran::odbc | |
| cran::xml2 | |
| rcmdcheck | |
| - uses: r-lib/actions/check-r-package@v2 | |
| with: | |
| working-directory: ./R | |
| env: | |
| PASSWORD_AIRLINE_USER: "${{ env.dbPassword }}" | |
| PASSWORD_AIRLINE_USER_DBOWNER: "${{ env.dbPassword }}" | |
| - name: Install Python dependencies | |
| working-directory: ./Python | |
| run: | | |
| python -m pip install --upgrade pip | |
| python -m pip install flake8 pytest | |
| pip install -r requirements.txt | |
| - name: Build Python Package | |
| working-directory: ./Python | |
| run: ./buildandinstall.cmd | |
| - name: Run pytest | |
| working-directory: ./Python/tests | |
| run: | | |
| pytest | |
| env: | |
| USER: "AirlineUserdbowner" | |
| PASSWORD: "${{ env.dbPassword }}" | |
| PASSWORD_AIRLINE_USER: "${{ env.dbPassword }}" |