Data Management with SQL

Woods Hole Oceanographic Institution

Online

June 2, 2021

12:30 pm - 4:30 pm

Instructors: Brett Longworth, Joe Futrelle, Audrey Mickle

Helpers: Lyndsey Lefebvre, Kodiak Firesmith

General Information

Software Carpentry aims to help researchers get their work done in less time and with less pain by teaching them basic research computing skills. This hands-on workshop will cover basic concepts and tools, for data management with relational databases and SQL. Participants will be encouraged to help one another and to apply what they have learned to their own research problems.

For more information on what we teach and why, please see our paper "Best Practices for Scientific Computing".

We will use lessons from the Software Carpentry Databases and SQL course.

This workshop is funded by a WHOI Technical Staff Training award through the office of the Deputy Director and Vice President of Science and Engineering (DDVP-SE).

Who: The course is aimed at WHOI technical staff. You don't need to have any previous knowledge of the tools that will be presented at the workshop.

Where: This training will take place online. The instructors will provide you with the information you will need to connect to this meeting.

When: June 2, 2021. Add to your Google Calendar.

Requirements: Participants must have access to a computer with a Mac, Linux, or Windows operating system (not a tablet, Chromebook, etc.) that they have administrative privileges on. They should have a few specific software packages installed (listed below).

Accessibility: We are dedicated to providing a positive and accessible learning environment for all. Please notify the instructors in advance of the workshop if you require any accommodations or if there is anything we can do to make this workshop more accessible to you.

Contact: Please email blongworth@whoi.edu , jfutrelle@whoi.edu or amickle@whoi.edu for more information.

Roles: To learn more about the roles at the workshop (who will be doing what), refer to our Workshop FAQ.


Code of Conduct

Everyone who participates in Carpentries activities is required to conform to the Code of Conduct. This document also outlines how to report an incident if needed.


Surveys

Please be sure to complete these surveys before and after the workshop.

Pre-workshop Survey

Post-workshop Survey


Schedule

Before Pre-workshop survey
12:30 Welcome and introduction to SQL
12:45 SQL queries - Selecting, Sorting and Filtering
13:20 Break
13:30 SQL queries - Calculation, Missing data and Aggregation
14:00 Joining tables
14:40 Break
14:50 Data structure and hygiene
15:10 Creating databases, tables, and adding data
15:30 Break
15:40 Programming with databases - R
16:10 Programming with databases - python Notebook
16:30 END

Setup

To participate in a Software Carpentry workshop, you will need access to software as described below. In addition, you will need an up-to-date web browser.

SQLite databases can be used through multiple interfaces. We will use the GUI SQLite DB Browser, the sqlite3 command line tool, and scripted access through R and python. If you do not wish to use the command line, or you are not interested in R or python, you may install the SQLite browser and just the programming language of your choice below. Please contact an instructor if you have questions!

We will work with a database of Antarctic survey data during the course. Please download this file and have it available for the course.

survey.db

Test your GUI install by opening DB Browser and using the file menu to open `survey.db`

Test your command line install by opening a shell, navigating to the directory that contains `survey.db`, and typing `sqlite3 survey.db`.

Test your python install by opening a python prompt or notebook and typing `import sqlite3`. This won't produce output if it's working, but if there's a problem, you'll see an error. Similarly, test your R install by typing `library(RSQLite)` at an R prompt.

We maintain a list of common issues that occur during installation as a reference for instructors that may be useful on the Configuration Problems and Solutions wiki page.

Install the videoconferencing client

If you haven't used Zoom before, go to the official website to download and install the Zoom client for your computer.

Set up your workspace

Like other Carpentries workshops, you will be learning by "coding along" with the Instructors. To do this, you will need to have both the window for the tool you will be learning about (a terminal, RStudio, your web browser, etc..) and the window for the Zoom video conference client open. In order to see both at once, we recommend using one of the following set up options:

This blog post includes detailed information on how to set up your screen to follow along during the workshop.

SQLite

SQL is a specialized programming language used with databases. We use a database manager called SQLite in our lessons.

  • Run "Git Bash" from the Start menu
  • Copy the following curl -fsSL https://blongworth.github.io/2021-06-02-WHOI-SQL/getsql.sh | bash
  • Paste it into the window that Git Bash opened. If you're unsure, ask an instructor for help
  • You should see something like 3.27.2 2019-02-25 16:06:06 ...

If you want to do this manually, download sqlite3, make a bin directory in the user's home directory, unzip sqlite3, move it into the bin directory, and then add the bin directory to the path.

If you prefer a GUI to the command line, please install the SQLite Browser.

SQLite comes pre-installed on macOS. If you prefer a GUI to the command line, please install the SQLite Browser.

SQLite comes pre-installed on Linux. If you prefer a GUI to the command line, please install the SQLite Browser.

If you installed Anaconda, it also has a copy of SQLite without support to readline. Instructors will provide a workaround for it if needed.

The Bash Shell

Bash is a commonly-used shell that gives you the power to do tasks more quickly.

  1. Download the Git for Windows installer.
  2. Run the installer and follow the steps below:
    1. Click on "Next" four times (two times if you've previously installed Git). You don't need to change anything in the Information, location, components, and start menu screens.
    2. From the dropdown menu select "Use the Nano editor by default" (NOTE: you will need to scroll up to find it) and click on "Next".
    3. On the page that says "Adjusting the name of the initial branch in new repositories", ensure that "Let Git decide" is selected. This will ensure the highest level of compatibility for our lessons.
    4. Ensure that "Git from the command line and also from 3rd-party software" is selected and click on "Next". (If you don't do this Git Bash will not work properly, requiring you to remove the Git Bash installation, re-run the installer and to select the "Git from the command line and also from 3rd-party software" option.)
    5. Ensure that "Use the native Windows Secure Channel Library" is selected and click on "Next".
    6. Ensure that "Checkout Windows-style, commit Unix-style line endings" is selected and click on "Next".
    7. Ensure that "Use Windows' default console window" is selected and click on "Next".
    8. Ensure that "Default (fast-forward or merge) is selected and click "Next"
    9. Ensure that "Git Credential Manager Core" is selected and click on "Next".
    10. Ensure that "Enable file system caching" is selected and click on "Next".
    11. Click on "Install".
    12. Click on "Finish" or "Next".
  3. If your "HOME" environment variable is not set (or you don't know what this is):
    1. Open command prompt (Open Start Menu then type cmd and press Enter)
    2. Type the following line into the command prompt window exactly as shown:

      setx HOME "%USERPROFILE%"

    3. Press Enter, you should see SUCCESS: Specified value was saved.
    4. Quit command prompt by typing exit then pressing Enter

This will provide you with both Git and Bash in the Git Bash program.

Video Tutorial

The default shell in some versions of macOS is Bash, and Bash is available in all versions, so no need to install anything. You access Bash from the Terminal (found in /Applications/Utilities). See the Git installation video tutorial for an example on how to open the Terminal. You may want to keep Terminal in your dock for this workshop.

To see if your default shell is Bash type echo $SHELL in Terminal and press the Return key. If the message printed does not end with '/bash' then your default is something else and you can run Bash by typing bash

If you want to change your default shell, see this Apple Support article and follow the instructions on "How to change your default shell".

Video Tutorial

The default shell is usually Bash and there is usually no need to install anything.

To see if your default shell is Bash type echo $SHELL in a terminal and press the Enter key. If the message printed does not end with '/bash' then your default is something else and you can run Bash by typing bash.

R

R is a programming language that is especially powerful for data exploration, visualization, and statistical analysis. To interact with R, we use RStudio. We will use the RSQLite package to work with SQLite from R.

Install R by downloading and running this .exe file from CRAN. Also, please install the RStudio IDE. Note that if you have separate user and admin accounts, you should run the installers as administrator (right-click on .exe file and select "Run as administrator" instead of double-clicking). Otherwise problems may occur later, for example when installing R packages.

Once R is installed and running, install the RSQLite package by typing `install.packages("RSQLite")` at an R prompt.

Video Tutorial

Install R by downloading and running this .pkg file from CRAN. Also, please install the RStudio IDE.

Once R is installed and running, install the RSQLite package by typing `install.packages("RSQLite")` at an R prompt.

Video Tutorial

Instructions for R installation on various Linux platforms (debian, fedora, redhat, and ubuntu) can be found at <https://cran.r-project.org/bin/linux/>. These will instruct you to use your package manager (e.g. for Fedora run sudo dnf install R and for Debian/Ubuntu, add a ppa repository and then run sudo apt-get install r-base). Also, please install the RStudio IDE.

Once R is installed and running, install the RSQLite package by typing `install.packages("RSQLite")` at an R prompt.

Python

Python is a popular language for research computing, and great for general-purpose programming as well. Installing all of its research packages individually can be a bit difficult, so we recommend Anaconda, an all-in-one installer.

Regardless of how you choose to install it, please make sure you install Python version 3.x (e.g., 3.6 is fine).

We will teach Python using the Jupyter Notebook, a programming environment that runs in a web browser (Jupyter Notebook will be installed by Anaconda). For this to work you will need a reasonably up-to-date browser. The current versions of the Chrome, Safari and Firefox browsers are all supported (some older browsers, including Internet Explorer version 9 and below, are not).

  1. Open https://www.anaconda.com/products/individual#download-section with your web browser.
  2. Download the Anaconda for Windows installer with Python 3. (If you are not sure which version to choose, you probably want the 64-bit Graphical Installer Anaconda3-...-Windows-x86_64.exe)
  3. Install Python 3 by running the Anaconda Installer, using all of the defaults for installation except make sure to check Add Anaconda to my PATH environment variable.

Video Tutorial

  1. Open https://www.anaconda.com/products/individual#download-section with your web browser.
  2. Download the Anaconda Installer with Python 3 for macOS (you can either use the Graphical or the Command Line Installer).
  3. Install Python 3 by running the Anaconda Installer using all of the defaults for installation.

Video Tutorial

  1. Open https://www.anaconda.com/products/individual#download-section with your web browser.
  2. Download the Anaconda Installer with Python 3 for Linux.
    (The installation requires using the shell. If you aren't comfortable doing the installation yourself stop here and request help at the workshop.)
  3. Open a terminal window and navigate to the directory where the executable is downloaded (e.g., `cd ~/Downloads`).
  4. Type
    bash Anaconda3-
    and then press Tab to autocomplete the full file name. The name of file you just downloaded should appear.
  5. Press Enter (or Return depending on your keyboard). You will follow the text-only prompts. To move through the text, press Spacebar. Type yes and press enter to approve the license. Press Enter (or Return) to approve the default location for the files. Type yes and press Enter (or Return) to prepend Anaconda to your PATH (this makes the Anaconda distribution the default Python).
  6. Close the terminal window.