Azure Synapse
About Synapse
Azure synapse is an enterprise analytics service created by Microsoft. With Synapse, you can quickly load data into your own personal filesystem and use SQL and Spark to get insights. Synapse also has a robust pipeline feature that allows for the creation of complex ETL operations.
We recommend using Synapse for projects that require extensive data manipulation.
Accessing Synapse
To best make use of Azure Synapse, we recommend connecting to our SQL servers via your local development environment. In this tutorial, we'll be connecting via Visual Studio Code, using the ms-sql extension.
Installing Visual Studio Code
Go to the VSCode Download Page. Download and install the appropriate version for your operating system (Windows, macOS, or Linux). Launch VSCode after installation.
Installing MSSQL
In VS Code, go to the Extensions tab, or press Ctrl + Shift + X (Windows)/Cmd + Shift + X (macOS).
Search for “MSSQL” (by Microsoft). Click Install to add the plugin.
Connecting to SQL Servers
After installing MSSQL, Click on the MSSQL icon in the sidebar. Choose Create new connection (Connection Profile).
Next, fill in the connection profile details like so:
Profile name: Choose a descriptive name (e.g., Research Data Lab).
Input Type: Select Parameters.
Server Name: researchdatalab.sql.azuresynapse.net
Authentication Type: Select Microsoft Entra ID - Universal with MFA support.
Azure Account: Log in using your Darden email. Follow the prompts to authenticate via the web browser if needed.
Database Name: researchdatalab
Click Connect. The MSSQL sidebar should display the researchdatalab server and its database schema.
Querying Data
In the menu on the left under tables, right-click any table and choose Select Top 1000. A new SQL file with a pre-generated query will open.
To run the query, hit the Execute button or press Ctrl + Shift + E (Windows)/Cmd + Shift + E (MacOS). The results will appear in the RESULTS tab below the editor.
For custom queries, either change the auto-generated file or create a new .sql file, like the following:
To save a query, click File -> Save As.
Troubleshooting Common Issues
Connection Timeout: Ensure the UVA Anywhere VPN is active (if required) and your internet is stable.
Execute Query: Ctrl + Shift + E (Windows) or Cmd + Shift + E (macOS).
New SQL File: Ctrl + N (Windows) or Cmd + N (macOS).
Using SQL
Structured Query Language (SQL) is the standard interface to query databases in Research Data Lab. SQL provides an easy-to-use syntax for searching, aggregating, or joining datasets. Get familliar with SQL using this basic tutorial.
Note: Not all versions of SQL are the same, but most are similar. The Research Data Lab uses Microsoft’s Transact-SQL (T-SQL).
For learning and exploring purposes, IMDb movie database is hosted as the model demo database, which is available in the Data Lab under the schema “imdb“. See the full details of this database.
Selecting Data
Use SELECT to select data from a table in a database. Then, specify the column names separted by commas, then specify schema and source table after FROM.
Use TOP to limit the number of rows returned. Otherwise, all rows that match the statement will return. Many tables contain millions of rows, which would take a long time to return and use up resources.
If you would like to select all columns in the table, use * in place of the column names. Avoid using * if there are many columns in the table.
Filtering Data
To filter data down to the results you are looking for, use the WHERE clause followed by a condition.
To match multiple conditions, attach AND and OR clauses to the WHERE statement.
Ordering
To order results, use the ORDER BY clause followed by ASC for ascending or DESC for descending order (the default).
Getting Statistics
SQL provides functions to compute common summary statistics. To use a function, provide a variable name as an arguement to the function.
Use the COUNT() function to return the number of rows that matches the specified query.
Use the MIN() function to find the minimum value of a selected variable. You can similarly use MAX() to find the maximum value of a selected variable
Use the AVG() function to find the average value of a numeric variable across all matching rows.
Use the SUM() function to find the total sum of a numeric variable.
Grouping
The GROUP BY statement groups rows that have the same values into summary rows. This statement is often used with aggregate functions (COUNT, MIN, MAX, SUM, AVG) to group the results by one or more variables.
Joining Data
A JOIN clause is used to combine rows from two or more tables, based on a related column between them. By default, the join is an INNER JOIN which means that only records with matching values in both tables are returned. Use LEFT JOIN if you want to get all values from the first table (a.k.a. “left” table) and matching values from the second table. A LEFT JOIN will return all records from the left table, even if there are no matches in the right table. Use FULL OUTER JOIN if you want to get values when there is a match in either the first table or the second table.
More Resources
SQL comes with many features not listed here that you may find helpful in your analysis. Visit the following resources to learn more about SQL and its advanced features.
SQL Limitations
Because SQL is a query language, there are some abilities that it cannot provide out of the box. To do more with data, such as running more advanced statistical, econometric and machine learning operations, you must utilize SQL inside of another programming languages such as Python and R. Up next, see how to use SQL when programming with Python and the R language.
Using Personal Schemas
Each user has their own personal schema. This feature allows users to create interim or staging tables from other databasets, all within a private schema. This guide walks you through the process of creating tables and populating them within your private schema.
Accessing Your Personal Schema
Before creating tables, ensure you’re logged into the Research Data Lab environment and have access to the researchdatalab database. Navigate to your preferred development environment where you can execute queries.
Once connected, you will now be able to use your personal schema. Your schema name is your firstname. If it conflicts with other user, we will let you know of the proper name to use. Note that your schema is only accessible to you.
Copying Tables from Research Data Lab Datasets to a personal schema.
A more practial and widely used feature in the research data lab is saving the query results as personal schema tables. To copy a table from a dataset to a private schema table, you can use a SQL command 'SELECT INTO FROM'. Use the following syntax to create a copy of an official dataset table:
