时间戳

首页 > 解决方案 > 如何使用 R 和 dplyr.snowflakedb 列出架构中的表

r - 如何使用 R 和 dplyr.snowflakedb 列出架构中的表

问题描述

我可以使用以下代码块创建与数据库的连接：

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(
              warehoue = "PUBLIC",
              db = "PROD",
              schema = "SCHEMA")
)

当我运行时：

class(conn)

我得到：

类（conn）[1]“src_snowflakedb”“src_sql”“src”

如果我跑

db_list_tables(conn$con)

结果是：

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” “管道” “PROCEDURES”
[37] “ REFERENTIAL_CONSTRAINTS” " "REPLICATION_DATABASES" "SCHEMATA" "SEQUENCES"
[41] "STAGES" "TABLES" "TABLE_CONSTRAINTS" "TABLE_PRIVILEGES"
[45] "TABLE_STORAGE_METRICS" "USAGE_PRIVILEGES" "VIEWS"

如何列出架构中的实际表，即产品、销售、客户等？

标签： rdplyrsnowflake-cloud-data-platform

解决方案

虽然我对 snowflakedb 不熟悉，但我建议您尝试包dbListTables中的命令DBI：

dbListTables(connection)

但是，这不会让您限制到特定模式，也不会返回模式名称。为此，我建议您查看这个问题，他们使用 DBI 执行返回表名的数据库查询：

answer = dbGetQuery(connection, "string_that_is_database_code_for_fetching_table_names")

这种方法应该很容易适应雪花数据库。

推荐阅读