Search code examples
sql-serverdplyrodbcdbplyrtbl

R : problem with the dplyr::tbl() function due to restricted permission

I work with large databases that needs to be stored into a server.

So, to work with them on Rstudio I have to open a connection to my Microsoft SQL Server with the dbConnect function :

conn <- dbConnect(odbc(),"myconnection",uid="***",pwd="***",schema="dbo",access="readonly")

and in order to use dplyr, I have to create data references with the tbl function :

data <- tbl(conn, "data")

But one of the online dataframe contains a columns that I can't read because I dont have the access, but I can read everything else. The SQL query behind the tbl() function is :

SELECT * FROM data

and this is my problem. Even when I try to select a specific column it doesn't work (see below), so I can't create my references and I can't work.

select(tbl(conn, "data"), "columnX")

=

SELECT columnX FROM data

I think this is the tbl() function and the call of "SELECT *" that blocks me.

Do you know what can I do ? Is there smilar functions that could resolve my problem ?

Solution

  • If you know the columns that you have access to, then one option is to bypass the default access SELECT * FROM ... with your own SQL query.

    A remote table is defined by two components:

    1. The database conneciton
    2. The query to the database

    When you connect with the default approach tbl(conn, 'data') then it defaults to a query SELECT * FROM data.

    But here is another approach:

    custom_query = 'SELECT columnX FROM data'
remote_table = tbl(conn, dbplyr::sql(customer_query))