Introduction to databases on Cedar
This is a 2-day course.
Instructors: Alex Lopes and Wolfgang Richter (SFU / WestGrid)
Course plan:
- Short introduction to SQL on Cedar Databases
- why databases
- frontends
- basic commands
- Overview of the two database servers on Cedar: cedar-mysql-vm (MariaDB MySQL v.10.4) and cedar-pgsql-vm (PostgreSQL v.10.1 with PostGIS v.2.4)
- Deeper dive into PostgreSQL on Cedar
- how to get a Postgres account and database on the server for your research
- how to use the psql client from the Compute Canada headnode to interactively issue SQL commands or simply pipe in a set of commands to be executed
- using a scripting language such as Python to access Postgres
- tips on optimizing your SQL commands; importance of creating indexes; explain a long running command to see where it can be optimized
Target audience: general
Level: beginner
Prerequisites: This is an introductory course, no previous experience is required. We will provide guest accounts on Cedar cluster.
Software: All attendees will need a remote secure shell (SSH) client installed on their computer in
order to participate in the course exercises. On Windows we recommend
the free Home Edition of MobaXterm. On Mac and Linux
computers SSH is usually pre-installed (try typing ssh in a terminal to make sure it is there).
Background information: The database services in WestGrid are now offered through high-performance database MySQL and Postgres servers on Cedar and Graham. Here is the official documentation.
Materials:
June-24
Basic commands and SQL queries (8 min)
Learning further about basic SQL (13 min)
How to use Postgres on Cedar (5 min)
Second part (14 min)
Queries on Cedar and Exercises (5 min)
Create programs and Slurm jobs that connect to the database (13 min)
In this course, we will be submitting jobs to the reservation on Cedar:
cd ~/scratch
sbatch --account=def-training-wa_cpu --reservation=wgss2-wr_cpu scriptName.sh
Day 1 exercises:
- Quiz: https://www.w3schools.com/sql/sql_quiz.asp
- Take some time to review the queries and examples on the w3schools website
June-25
Introduction to MySQL on Cedar (4 min)
How to use MariaDB on Cedar (12 min)
Create programs and Slurm jobs that connect to the database (8 min)
Optional videos:
If you need help to connect to Cedar via SSH from Windows (2 min)
Quick overview about database servers (8 min)
Day 2 exercises:
- Creating tables and experiment commands on Cedar databases
- Submit a job that connects to the database and execute an operations
- Take a look at the perl examples at the end of the slides if you would like to learn more