Using SQL Commands in R Environment

This post explains how to use SQL commands in the R environment without installing or connecting to a database. It outlines two alternative solutions.

Alternative 1: Manipulate data frames with SQL using the sqldf package

# Load required packages
library(sqldf)

Let’s create a table named employees in R environment using R language and print it:

employees <- data.frame(
  id = 1:5,
  name = c("John", "Bob", "Eve", "Charlie", "Emma"),
  department = c("HR", "Engineering", "Marketing", "Engineering", "HR"),
  salary = c(50000, 60000, 55000, 70000, 52000)
)
print(employees)

  id    name  department salary
1  1    John          HR  50000
2  2     Bob Engineering  60000
3  3     Eve   Marketing  55000
4  4 Charlie Engineering  70000
5  5    Emma          HR  52000

Now, we can use the sqldf function to apply SQL statements to the employees table:

# filter 'HR' department and save it as a new data frame
employees_HR <- sqldf("SELECT * FROM employees WHERE department = 'HR'")
print(employees_HR)

  id name department salary
1  1 John         HR  50000
2  5 Emma         HR  52000

To learn more about this package, you can visit https://cran.r-project.org/web/packages/sqldf/index.html.

Alternative 2: Create a database on memory using RSQLite package

This method is a more database-like application compared to first one.

# Load required packages
library(DBI)
library(RSQLite)

# Create a temporary database in memory
con <- dbConnect(RSQLite::SQLite(), ":memory:")

# remove the tables created earlier
rm(employees, employees_HR)

Now, we can create a table in the temporary in-memory database:

# Create "employees" table
dbExecute(con, "
CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name TEXT,
  department TEXT,
  salary REAL
)")

# Insert statements to add data to employees table
dbExecute(con, "INSERT INTO employees (id, name, department, salary) VALUES (1, 'John', 'HR', 50000)")
dbExecute(con, "INSERT INTO employees (id, name, department, salary) VALUES (2, 'Bob', 'Engineering', 60000)")
dbExecute(con, "INSERT INTO employees (id, name, department, salary) VALUES (3, 'Eve', 'Marketing', 55000)")
dbExecute(con, "INSERT INTO employees (id, name, department, salary) VALUES (4, 'Charlie', 'Engineering', 70000)")
dbExecute(con, "INSERT INTO employees (id, name, department, salary) VALUES (5, 'Emma', 'HR', 52000)")

The function sqldf() returns the output of the query stated; in this case it returns the employees table from the in-memory database.

print(dbGetQuery(con, "SELECT * FROM employees"))

  id    name  department salary
1  1    John          HR  50000
2  2     Bob Engineering  60000
3  3     Eve   Marketing  55000
4  4 Charlie Engineering  70000
5  5    Emma          HR  52000

Filtering the HR department only:

print(dbGetQuery(con, "SELECT * FROM employees WHERE department = 'HR'"))

  id name department salary
1  1 John         HR  50000
2  5 Emma         HR  52000

In the second method, we initially used the dbExecute() function to create a table from scratch and then inserted data into it. Manually writing insert statements may not be practical, especially if the data we need is already in a data frame within the R environment. In such cases, we can usethe dbWriteTable() function to write this table to in-memory database and continue using it there.

Since there is already a table named employees in the database, it has to be deleted first:

dbExecute(con, "DROP TABLE IF EXISTS employees")

The same code block from alternative 1 is used to create the employees table in R environment again:

employees <- data.frame(
  id = 1:5,
  name = c("John", "Bob", "Eve", "Charlie", "Emma"),
  department = c("HR", "Engineering", "Marketing", "Engineering", "HR"),
  salary = c(50000, 60000, 55000, 70000, 52000)
)

Now, we can write newly created employees table to SQL database on memory using dbWriteTable() function:

dbWriteTable(con, "employees", employees)

Finally, query the table using dbGetQuery() function:

print(dbGetQuery(con, "SELECT * FROM employees"))

  id    name  department salary
1  1    John          HR  50000
2  2     Bob Engineering  60000
3  3     Eve   Marketing  55000
4  4 Charlie Engineering  70000
5  5    Emma          HR  52000

To learn more about this package, you can visit: https://cran.r-project.org/web/packages/RSQLite/index.html.