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
– David Browne - Microsoft, Commented Oct 8, 2019 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
– Display name, Commented Oct 8, 2019 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
– David Browne - Microsoft, Commented Oct 8, 2019 at 16:29

Parfait · Accepted Answer · 2019-10-09 03:03:00Z

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)

Simon.S.A. · Accepted Answer · 2019-10-08 19:59:10Z

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.)

Collectives™ on Stack Overflow

How do I access nested SQL tables in R?

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Linked

Related