Read database tables as data frames

Source: R/DBI.R

Reads a database table to a data frame, optionally converting a column to row names and converting the column names to valid R identifiers. Use dbReadTableArrow() instead to obtain an Arrow object.

# S4 method for class 'DatabaseConnectorConnection,character'
dbReadTable(conn, name, databaseSchema = NULL, ...)

Arguments

conn

A DBIConnection object, as returned by dbConnect().

name

The table name, passed on to dbQuoteIdentifier(). Options are:

  • a character string with the unquoted DBMS table name, e.g. "table_name",

  • a call to Id() with components to the fully qualified table name, e.g. Id(schema = "my_schema", table = "table_name")

  • a call to SQL() with the quoted and fully qualified table name given verbatim, e.g. SQL('"my_schema"."table_name"')

databaseSchema

The name of the database schema. See details for platform-specific details.

...

Other parameters passed on to methods.

Value

dbReadTable() returns a data frame that contains the complete data from the remote table, effectively the result of calling dbGetQuery() with SELECT * FROM <name>.

An empty table is returned as a data frame with zero rows.

The presence of rownames depends on the row.names argument, see sqlColumnToRownames() for details:

  • If FALSE or NULL, the returned data frame doesn't have row names.

  • If TRUE, a column named "row_names" is converted to row names.

  • If NA, a column named "row_names" is converted to row names if it exists, otherwise no translation occurs.

  • If a string, this specifies the name of the column in the remote table that contains the row names.

The default is row.names = FALSE.

If the database supports identifiers with special characters, the columns in the returned data frame are converted to valid R identifiers if the check.names argument is TRUE, If check.names = FALSE, the returned table has non-syntactic column names without quotes.

Details

The databaseSchema argument is interpreted differently according to the different platforms: SQL Server and PDW: The databaseSchema schema should specify both the database and the schema, e.g. 'my_database.dbo'. Impala: the databaseSchema should specify the database. Oracle: The databaseSchema should specify the Oracle 'user'. All other : The databaseSchema should specify the schema.