Importing Data from a SQL Database

Accessing data stored in SQL databases is a common requirement for data analysis and modeling. In this tutorial, we will demonstrate how to connect to a SQL database and import data into R using the DBI and RSQLite packages, which are part of the tidyverse ecosystem.

Installing and Loading the Required Packages

#install.packages("DBI")
#install.packages("RSQLite")

library(DBI)
library(RSQLite)

# Establish a connection to the SQLite database
con <- dbConnect(RSQLite::SQLite(), dbname = '../Datasets/chinook.db')

# Print the connection object to verify
print(con)

OUTPUT SQLiteConnection Path: /Users/sifael/Desktop/Data-Analytics/R-Complete/Datasets/chinook.db Extensions: TRUE

Listing Tables in the Database

You can list all the tables available in the database using the dbListTables function:

# List all tables in the database
tables <- dbListTables(con)

# Print the list of tables
tables

OUTPUT'albums'• 'artists'• 'customers'• 'employees'• 'genres'• 'invoice_items'• 'invoices'• 'media_types'• 'playlist_track'• 'playlists'• 'sqlite_sequence'• 'sqlite_stat1'• 'tracks'

Importing Data from a Specific Table

To import data from a specific table, use the dbReadTable function. Note that in the example below, we use the customers table as an example.

# retrieve data from a table
data_table <- dbReadTable(con, 'customers')

# Print the imported data
data_table <- data_table[, c("CustomerId", "FirstName", "LastName", "City", "Country", "Email", "SupportRepId")]
head(data_table)

CustomerId	FirstName	LastName	City	Country	Email	SupportRepId
1	Lúís	Gonçalves	São José dos Campos	Brazil	[email protected]	3
2	Leonie	Köhler	Stuttgart	Germany	[email protected]	5
3	François	Tremblay	Montréal	Canada	[email protected]	3
4	Bjørn	Hansen	Oslo	Norway	[email protected]	4
5	František	Wichterlóva	Prague	Czech Republic	[email protected]	4
6	Helena	Holý	Prague	Czech Republic	[email protected]	5

Executing SQL Queries

Finally, you can also execute custom SQL queries using the dbGetQuery function to retrieve specific subsets of data. This gives you the power you'd normaly have on a DBMS and can be run through directly from R. Below is an example that returns customers from Germany

# Write your SQL query
query <- "SELECT CustomerId, FirstName, LastName, City, Country, Email, SupportRepId 
          FROM customers 
          WHERE Country = 'Germany'
          LIMIT 4"

# Execute the SQL query and import the data
data_query <- dbGetQuery(con, query)

# Print the imported data from the query
data_query

CustomerId	FirstName	LastName	City	Country	Email	SupportRepId
2	Leonie	Köhler	Stuttgart	Germany	[email protected]	5
36	Hannah	Schneider	Berlin	Germany	[email protected]	5
37	Fynn	Zimmermann	Frankfurt	Germany	[email protected]	3
38	Niklas	Schröder	Berlin	Germany	[email protected]	3

We have seen a few ways to import data into an R object so that we can perform analysis and modeling. The next step goes a step further to help you develop details combining data sets.