The purpose of the BYUI Data Science SQL Server is to twofold:
- Provide a convenient mechanism for Data Science students to store and retrieve data for large projects.
- Provide an authentic experience that prepares Data Science students to interface with a SQL Server in the workplace.
To fulfill this ongoing purpose, this guide provides instruction for maintenance and use of the BYUI Data Science SQL Server. Two distinct roles, each with their own set of server permissions have been created to preserve the rigorous structure of the server. These roles are titled “Editor” and “Student”. Editors are responsible for creating a new database on the server for each project, uploading initial data to that database, and maintaining the database throughout the lifetime of its associated project. Students have the ability to use the server to read data into their sessions of RStudio. The remainder of this guide provides specific instruction for each of these two roles.
Editors are responsible for creating a new database on the server for each project, uploading initial data to that database, and maintaining the database throughout the lifetime of its associated project.
Microsoft SQL Server Management Studio (SSMS) is an interactive interface to manage databases on a server.
- Visit https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms and follow the instuctions to download the newest version of Microsoft SQL Server Management Studio (SSMS).
- Execute the downloaded file to install the program.
You will need to login to the server every time you launch Microsoft SSMS.
- In the dialogue box that appears titled "Connect to Server" enter the following information:
Server type: Database Engine
Server name: shiny.byui.edu
Authentication: SQL Server Authentication
Login: editor
Password: (Reach out to Brother Hathaway to get the password to our SQL Server editor account.) - Click "connect"
We will use one new database to hold all necessary datatables for each new project on the Server.
- Within the "Object Explorer" pane in Microsoft SSMS, right-click on the folder titled "Databases".
- Click the option "New Database" in the dropdown menu.
- Type a name for the database in the "Database name" field.
- Click "OK" to initialize your empty new database.
When using SQL Server only for data storage we don't need to worry much about datatypes and keys.
- Ensure that the data is downloaded on your computer and saved as a
.csv
file. - Within the "Object Explorer" pane in Microsoft SSMS, expand the folder titled "Databases".
- Right-click on the name of your database object.
- Hover over the "Tasks" item in the dropdown list and click on the "Import Flat File..." option.
- Click "Next".
- Click "Browse" and navigate to your data.
- Enter the name for your new datatable.
- Click "Next" two times.
- Check the "Allow Nulls" box for every item.
- Click "Next".
- Click "Finish".
- Within the "Object Explorer" pane in Microsoft SSMS, expand the folder titled "Databases".
- Expand your database object and the folder inside titled "Tables".
- Right click on the table you would like to delete.
- Click the "Delete" option the dropdown menu.
- Click "OK".
- Within the "Object Explorer" pane in Microsoft SSMS, expand the folder titled "Databases".
- Right click on the database you would like to delete.
- Click the "Delete" option the dropdown menu.
- Click "OK".
Students have the ability to use the server to read data into their sessions of RStudio.
To access your database, you must first create a special "connection" object.
- Run the following code snippet within your RStudio session to load a function called "connect" into your environment:
source("https://bit.ly/byuisql")
- Speak to your project's designated Editor to learn the name of your project's database on the server.
- Run the following code snippet within your RStudio session to create a connection object named "connection" that points to your database:
connection <- connect("name of your database")
- Enter the password. (Reach out to Brother Hathaway to get the password to our SQL Server student account.)
A query object specifies exactly what information you would like to read from the database.
- Speak to your project's designated Editor to learn the name of your project's tables on the server.
- Create a query object using the
tbl()
function from thedplyr
package.
For example:query <- tbl(connection, "name_of_the_table")
If a dataset in its full form is to large to fit on your computer, you can specify a way to summarize it before transfering it to your device.
- Transform your query object using dplyr functions such as
mutate()
,filter()
,select()
,group_by()
, andsummarize()
. (Nearly any dplyr operation works.)
For example:
query <- query %>%
mutate(new_col = colA * colB) %>%
filter(new_col > 5) %>%
group_by(colC) %>%
summarize(top = mean(new_col))
Turning a query object into a dataframe only requires one simple step.
- Use the
collect()
function from the dbplyr package to convert your query into a local dataframe.
For example:data <- collect(query)
.