How to list tables in schema using R and dplyr.snowflakedb

Question

I can create a connection to the database using the following code block:

library("RJDBC", "DBI")
library("dplyr.snowflakedb")
library("dplyr")

options(dplyr.jdbc.classpath = "/jar_files/snowflake-jdbc-3.10.0.jar")

conn <- src_snowflakedb(
  user = "username",
  password = "password",
  account = "account",
  host = "account.eu-west-1.snowflakecomputing.com",
  opts = list(
              warehouse = "PUBLIC",
              db = "PROD",
              schema = "SCHEMA")
)

When I run:

class(conn)

I get:

class(conn) [1] "src_snowflakedb" "src_sql" "src"

and if I run

db_list_tables(conn$con)

The result is:

db_list_tables(conn$con) [1] "GA_CUSTOMER_CDP_TP" "APPLICABLE_ROLES" "COLUMNS" "DATABASES"
[5] "ENABLED_ROLES" "EXTERNAL_TABLES" "FILE_FORMATS" "FUNCTIONS"
[9] "INFORMATION_SCHEMA_CATALOG_NAME" "LOAD_HISTORY" "OBJECT_PRIVILEGES" "PIPES"
[13] "PROCEDURES" "REFERENTIAL_CONSTRAINTS" "REPLICATION_DATABASES" "SCHEMATA"
[17] "SEQUENCES" "STAGES" "TABLES" "TABLE_CONSTRAINTS"
[21] "TABLE_PRIVILEGES" "TABLE_STORAGE_METRICS" "USAGE_PRIVILEGES" "VIEWS"
[25] "APPLICABLE_ROLES" "COLUMNS" "DATABASES" "ENABLED_ROLES"
[29] "EXTERNAL_TABLES" "FILE_FORMATS" "FUNCTIONS" "INFORMATION_SCHEMA_CATALOG_NAME" [33] "LOAD_HISTORY" "OBJECT_PRIVILEGES" "PIPES" "PROCEDURES"
[37] "REFERENTIAL_CONSTRAINTS" "REPLICATION_DATABASES" "SCHEMATA" "SEQUENCES"
[41] "STAGES" "TABLES" "TABLE_CONSTRAINTS" "TABLE_PRIVILEGES"
[45] "TABLE_STORAGE_METRICS" "USAGE_PRIVILEGES" "VIEWS"

How do I list the actual tables in the schema i.e product, sales, customer e.t.c?

have you already looked at this https://www.snowflake.com/blog/integrating-the-snowflake-data-warehouse-with-r-via-dplyr/ — hopeIsTheonlyWeapon, Aug 31 '20 at 17:24

score 4 · Answer 1 · answered Aug 31 '20 at 21:01

While I am not familiar with snowflakedb, I recommend you try the dbListTables command in the DBI package:

dbListTables(connection)

However this will not let you restrict to a specific schema, nor will it return schema names. For this, I recommend you look at this question where they use DBI to execute a database query that returns tables names:

answer = dbGetQuery(connection, "string_that_is_database_code_for_fetching_table_names")

This approach should adapt easily to snowflakedb.

How to list tables in schema using R and dplyr.snowflakedb

1 Answers1