sql/sql-workshop.Rmd at master · feature-engineering-studio/sql · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
---
title: "SQL Workshop"
author: "Charles Lang"
date: "9/19/2019"
output: html_document
---

```{r}
install.packages(c("DBI", "dbplyr", "dbplot", "modeldb", "tidypredict", "config", "RSQLite", "blob"))

library(DBI)
library(dbplyr)
library(dplyr)
library(dbplot)
library(ggplot2)
library(modeldb)
library(tidypredict)
library(config)
library(blob)
library(RSQLite)
```

## Connect to Database & Reading Tables
```{r}
con <- DBI::dbConnect(RSQLite::SQLite(), dbname = "mydatabase.db")
con
summary(con)
dbListTables(con)
dbReadTable(con, 'COMPANY')
```
## Getting into SQL
```{r}
#Query a portion of the database
dbGetQuery(con, "select * from ecom limit 10")

#Read Data in Batches
query  <- dbSendQuery(con, 'select * from ecom')

dbFetch(query, n = 10)

dbFetch(query, n = 10)

#Find the status of a query
dbHasCompleted(query)

#Get the info of a query
dbGetInfo(query)

#Latest query
dbGetStatement(query)

#Rows queried
dbGetRowCount(query)

#Rows changed by query
dbGetRowsAffected(query)

#Column info
dbColumnInfo(query)

#Clear all queries
dbGetInfo(query)
dbClearResult(query)
dbGetInfo(query)
```