How do I access nested SQL tables in R?

Question

From R Studio's ODBC database documentation I can see a simple example of how to read a SQL table into an R data frame:

data <- dbReadTable(con, "flights")

Let me paste a graphic of the BGBUref table(?) I'm trying to read to an R data frame. This is from my connection pane in R studio.

If I use the same syntax as above, where con is the output of my dbConnect(...) I get the following:

df <- dbReadTable(con, "BGBURef")
#> Error: <SQL> 'SELECT * FROM "BGBURef"' nanodbc/nanodbc.cpp:1587: 42S02: 
#> [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid object name
#> 'BGBURef'.

Is my understanding of what a "table" is incorrect? Or do I need to do something like this to get to the nested BGBUref table:

df <- dbReadTable(con, "QnRStore\dbo\BGBURef")
#> Error: '\d' is an unrecognized escape in character string starting ""QnRStore\d"

The BGBUref data frame will come up in R Studio if I click on the little spreadsheet icon. I just can't figure out how to get it into a defined data frame, in my case df.

Here's the output when I run these commands:

df <- dbReadTable(con, "QnRStore")
#> Error: <SQL> 'SELECT * FROM "QnRStore"'
#> nanodbc/nanodbc.cpp:1587: 42S02: [Microsoft][ODBC Driver 17 for SQL 
#> Server][SQL Server]Invalid object name 'QnRStore'.

and:

dbListTables(con)
#>  [1] "spt_fallback_db"                                         
#>  [2] "spt_fallback_dev"                                        
#>  [3] "spt_fallback_usg"                                        
#>  [4] "spt_monitor"                                             
#>  [5] "trace_xe_action_map"                                     
#>  [6] "trace_xe_event_map"                                      
#>  [7] "spt_values"                                              
#>  [8] "CHECK_CONSTRAINTS"                                       
#>  [9] "COLUMN_DOMAIN_USAGE"                                     
#> [10] "COLUMN_PRIVILEGES" 
#> ...
#> [650] "xml_schema_types"                                        
#> [651] "xml_schema_wildcard_namespaces"                          
#> [652] "xml_schema_wildcards"

`QnRStore` should be the output of the `dbConnect` function. — David Browne - Microsoft, Oct 08 '19 at 15:50
@David Browne - Microsoft Thanks for that first tip. I just updated the question to reflect these changes. Any additional input? — Display name, Oct 08 '19 at 16:08
That's not a "nested" table. It's a table named 'BGBUref" in the default schema 'dbo' of a database called QnRStore. The error you posted still says ` object 'QnRStore' not found`. Is that correct? — David Browne - Microsoft, Oct 08 '19 at 16:29
@David Browne - Microsoft Sorry, I didn't have the errors updated in my edited post. Now the error messages are correct (mirror what I actually get). Hope that helps. — Display name, Oct 08 '19 at 16:39
@David Browne - Microsoft Added the output of `dbListTables(con)` to my question. Also dumped the entire output into a text editor, searched for both `BGBURef` and `QnRStore` and they're not there. But I can click on that spreadsheet icon and `BGBURef` comes up in R Studio! — Display name, Oct 08 '19 at 17:09
@David Browne - Microsoft I changed the `Database` argument to `Database = QnRStore` and now everything works properly. Thanks for your patience. — Display name, Oct 08 '19 at 17:28

Parfait · Accepted Answer · 2019-10-09T03:03:00.773

General Background

Before anything, consider reading on the relational database architecture where tables are encapsulated in schemas which themselves are encapsulated in databases which are then encapsulated in servers or clusters. Notice the icons in your image correspond to the object type:

cluster/server < catalog/database < schema/namespace < table

Hence, there is no nested tables in your situation but a typical architecture:

myserver < QnRStore < dbo < BGBURef

To access this architecture from server-level in an SQL query, you would use period-qualifying names:

SELECT * FROM database.schema.table

SELECT * FROM QnRStore.dbo.BGBURef

The default schema for SQL Server is dbo (by comparison for Postgres it is public). Usually, DB-APIs like R's odbc connects to a database which allows connection to any underlying schemas and corresponding tables, assuming the connected user has access to such schemas. Please note this rule is not generalizable. For example, Oracle's schema aligns to owner and MySQL's database is synonymous to schema.

See further reading:

Specific Case

Therefore, to connect to an SQL Server database table in a default schema, simply reference the table, BGBURef, which assumes the table resides in the dbo schema of your connecting database.

df <- dbReadTable(con, "BGBURef")

If you use a non-default schema, you will need to specify it accordingly which recently you can do with DBI::Id and can use it similarly for dbReadTable and dbWriteTable:

s <- Id(schema = "myschema", table = "mytable")

df <- dbReadTable(con, s)

dbWriteTable(conn, s, mydataframe)

Alternatively, you can run the needed period qualifying SQL query:

df <- dbGetQuery(con, "SELECT * FROM [myschema].[mytable]")

And you can use SQL() for writing to persistent tables:

dbWriteTable(con, SQL("myschema.mytable"), mydataframe)

score 2 · Answer 2 · answered Oct 08 '19 at 19:59

When using dbplyr it appears that

df = tbl(con, from = 'BGBUref')

if roughly equivalent to

USE QnRStore
GO

SELECT * FROM BGBUref;

From @David_Browne's comment and the image it looks like you have:

A table named 'BGBUref'
In a schema named 'dbo'
In a database called 'QnRStore'

In this case you need the in_schema command.

If your connection (con) is to the QnRStore database then try this:

df = tbl(con, from = in_schema('dbo', 'BGBUref'))

If your connection (con) is not to the QnRStore database directly then this may work:

df = tbl(con, from = in_schema('QnRStore.dbo', 'BGBUref'))

(I use this form when accessing multiple databases via the same connection. Because dbplyr performs best if you use the same connection when joining between tables from different databases.)

How do I access nested SQL tables in R?

2 Answers2

Linked