I have a .sql file that I am trying to read into SQL database using R package RSQLite. To familiarize myself, first, I looked at R dataset mtcars:
mydb <- dbConnect(RSQLite::SQLite(), "")
dbWriteTable(mydb, "mtcars", mtcars)
str(mydb)
The structure is as follows:
Formal class 'SQLiteConnection' [package "RSQLite"] with 5 slots
..@ Id :<externalptr>
..@ dbname : chr ""
..@ loadable.extensions: logi TRUE
..@ flags : int 6
..@ vfs : chr ""
Next, I simply read five lines from the database:
dbGetQuery(mydb, 'SELECT * FROM mtcars LIMIT 5')
row_names mpg cyl disp hp drat wt qsec vs am gear carb
1 Mazda RX4 21.0 6 160 110 3.90 2.620 16.46 0 1 4 4
2 Mazda RX4 Wag 21.0 6 160 110 3.90 2.875 17.02 0 1 4 4
3 Datsun 710 22.8 4 108 93 3.85 2.320 18.61 1 1 4 1
4 Hornet 4 Drive 21.4 6 258 110 3.08 3.215 19.44 1 0 3 1
5 Hornet Sportabout 18.7 8 360 175 3.15 3.440 17.02 0 0 3 2
Now, I can see the column names, and maybe I am only interested in hp>200, so I do:
dbGetQuery(mydb, 'SELECT * FROM mtcars WHERE hp > 200')
row_names mpg cyl disp hp drat wt qsec vs am gear carb
1 Duster 360 14.3 8 360 245 3.21 3.570 15.84 0 0 3 4
2 Cadillac Fleetwood 10.4 8 472 205 2.93 5.250 17.98 0 0 3 4
3 Lincoln Continental 10.4 8 460 215 3.00 5.424 17.82 0 0 3 4
4 Chrysler Imperial 14.7 8 440 230 3.23 5.345 17.42 0 0 3 4
5 Camaro Z28 13.3 8 350 245 3.73 3.840 15.41 0 0 3 4
6 Ford Pantera L 15.8 8 351 264 4.22 3.170 14.50 0 1 5 4
7 Maserati Bora 15.0 8 301 335 3.54 3.570 14.60 0 1 5 8
Now, I am actually trying to look at my data of interest (not mtcars anymore) called myData.sql. I was told that this file is generic SQL, and that I could load into whatever database I preferred, and that it came from PostgreSQL.
So, I try this, which outputs the same structure as before:
mydb <- dbConnect(RSQLite::SQLite(), "myData.sql")
str(mydb)
Formal class 'SQLiteConnection' [package "RSQLite"] with 5 slots
..@ Id :<externalptr>
..@ dbname : chr "myData.sql"
..@ loadable.extensions: logi TRUE
..@ flags : int 6
..@ vfs : chr ""
Now, I would like to see what is stored in myData.sql, hoping to see a data table with columns (like for mtcars). However, I get errors when I try:
dbGetQuery(mydb, 'SELECT * LIMIT 5')
Error in sqliteSendQuery(con, statement, bind.data) :
error in statement: no tables specified
and
dbGetQuery(mydb, 'SELECT * FROM mydb LIMIT 5')
Error in sqliteSendQuery(con, statement, bind.data) :
error in statement: no such table: mydb
I am very unsure how to begin to explore the information in myData.sql. Any advice is greatly appreciated!
First things first, mydata.sql is not an SQLite database, it's a text file containing SQL statements that will create and populate your database.
I suggest you download an SQLite data browser such as this one, which is multiplatform. Using this tool, you can create an actual SQLite database. For instance, if you use sqlitebrowser, go to File > Import > Database from SQL file. This will create the database which you can then connect to from within R.
If you don't like the data browser I suggested, there are plenty other ones that you can try, they all do more or less the same thing. Good luck!
Edit
To do everything from R, here's a suggestion using RSQLite:
library(RSQLite)
con <- dbConnect(SQLite(), "my_db.sqlite")
If the sql file has everything it needs to create the tables, you can use RSQLite's dbSendQuery function:
fileName <- 'mydata.sql'
sql.query <- readChar(fileName, file.info(fileName)$size)
dbSendQuery(con, sql.query)
dbListTables(con) # to see a list of all created tables
If you run into problems at the dbSendQuery step, that's a case where the data browser tools come in particularly handy, as the error messages you'll get from the R interpreter might not give you all the necessary details to understand what's going wrong.