Как заставить dbplyr in_schema ссылаться на другой склад

Question

Я использую dbplyr для доступа к сложному хранилищу в Snowflake, которое содержит несколько баз данных. У меня есть доступ для записи к одному из них и доступ для чтения к остальным. Образец структуры

      WH_a
 - schema_a 
   - table_aa
 - schema_b
   - table_ba
WH_b
 - schema_c
    - table_ca

Следуя документации dbplyr, я установил рабочую базу данных и схему «WH_a.schema_a»:

      dbGetQuery(conn, "USE DATABASE WH_a")
dbGetQuery(conn, "USE SCHEMA schema_a")

и попробуйте создать ссылки на таблицы. Простая ссылка на таблицу прекрасно работает в той же схеме:

      aa <- tbl(conn, "table_aa") 

если я хочу сослаться на таблицу в другой схеме (той же WH_a), я могу без проблем использовать функцию in_schema():

      ba <- tbl(conn, in_schema("schema_b", "table_ba")) 

Однако у меня возникают проблемы, когда я пытаюсь сослаться на таблицу в другом хранилище.

      ca <- tbl(conn, in_schema("WH_b.schema_c", "table_ca")) 

nanodbc/nanodbc.cpp:1374: 00000: SQL compilation error:
Schema 'WH_a."WH_b.schema_c"'  does not exist or not authorized.

Похоже, вызов in_schema наследует текущую базу данных и не может подняться на уровень выше. Я просмотрел всю документацию, но большинство примеров относятся к гораздо более простым базам данных, где это не проблема. Тестирование различных комбинаций установки и отключения различных схем/складов не привело к успеху... В конце концов я нашел обходной путь, передав прямой оператор sql

      ca <- tbl(conn, sql("SELECT * FROM WH_b.schema_c.table_ca")) 

Однако это создает очень уродливый (и, возможно, неэффективный) код SQL, где оператор select вставляется в скобки, а не просто имя таблицы. Это намного сложнее читать, и в конечном итоге кажется, что это правильно.

Есть ли более простое/эффективное решение?

Большое спасибо

Answer 1

Это может быть связано с тем, что, в отличие от Snowflake, довольно много баз данных не поддерживают соединения между базами данных, поэтому, возможно, это не учитывается при разработке dbplyr.

Вы можете использовать sql()Функция для включения строк database.schema или database.schema.table, которые из приведенного ниже тестирования, похоже, работают.

      # Create Databases, Schemas & Tables
dbGetQuery(conn, "CREATE DATABASE WH_A")
dbGetQuery(conn, "CREATE SCHEMA SCHEMA_A")
dbGetQuery(conn, "USE DATABASE WH_A")
dbGetQuery(conn, "USE SCHEMA SCHEMA_A")
dbGetQuery(conn, "CREATE TABLE TABLE_AA as 
                         select c1, c2
                          from (values (1, 'one'), (2, 'two')) as v1 (c1, c2);")
dbGetQuery(conn, "CREATE SCHEMA SCHEMA_B")
dbGetQuery(conn, "USE SCHEMA SCHEMA_B")
dbGetQuery(conn, "CREATE TABLE TABLE_BA as 
                         select c1, c2
                          from (values (1, 'one'), (2, 'two')) as v1 (c1, c2);")
dbGetQuery(conn, "CREATE DATABASE WH_B")
dbGetQuery(conn, "USE DATABASE WH_B")
dbGetQuery(conn, "CREATE SCHEMA SCHEMA_C")
dbGetQuery(conn, "USE SCHEMA SCHEMA_C")
dbGetQuery(conn, "SELECT CURRENT_DATABASE() as db, CURRENT_SCHEMA() as sc;")
dbGetQuery(conn, "CREATE TABLE TABLE_CA as 
                         select c1, c2
                          from (values (1, 'one'), (2, 'two')) as v1 (c1, c2);")
# Create tbl for TABLE_AA
dbGetQuery(conn, "USE DATABASE WH_A")
dbGetQuery(conn, "USE SCHEMA SCHEMA_A")
(aa <- tbl(conn, "TABLE_AA") )
aa$ops # Check database.schema.table reference

# Create tbl for TABLE_BA
(ba <- tbl(conn, in_schema("SCHEMA_B", "TABLE_BA")) )
ba$ops

# Create tbl for TABLE_CA
(ca <- tbl(conn, in_schema("WH_B.SCHEMA_C", "TABLE_CA")))       # Fails
(ca <- tbl(conn, in_schema(sql("WH_B.SCHEMA_C"), "TABLE_CA")))  # Works
ca$ops
# From: WH_B.SCHEMA_C."TABLE_CA"
# <Table: WH_B.SCHEMA_C."TABLE_CA">
ca <- tbl(conn, sql("WH_B.SCHEMA_C.TABLE_CA"))  # Also Works
ca$ops
# From: <derived table>
# <Table: WH_B.SCHEMA_C.TABLE_CA>

# Check cross schema join works
inner_join(aa,ba,by='C1')
# Check cross database join works  
inner_join(aa,ca,by='C1')

# Clean up 
dbGetQuery(conn, "DROP DATABASE WH_A")
dbGetQuery(conn, "DROP DATABASE WH_B")

Если ваши объекты базы данных используют смешанный регистр, вам нужно будет позаботиться о том, чтобы правильно указать их в Sql().

      dbGetQuery(conn, 'CREATE DATABASE "WH_b" ')
dbGetQuery(conn, 'USE DATABASE "WH_b" ')
dbGetQuery(conn, 'CREATE SCHEMA "schema_c" ')
dbGetQuery(conn, 'USE SCHEMA "schema_c" ')
dbGetQuery(conn, 'SELECT CURRENT_DATABASE() as db, CURRENT_SCHEMA() as sc;')
dbGetQuery(conn, 'CREATE TABLE "table_ca" as 
                         select c1, c2
                          from (values (1, \'one\'), (2, \'two\')) as v1 (c1, c2);')
dbGetQuery(conn, 'SELECT * FROM  "WH_b"."schema_c"."table_ca";')

(ca <- tbl(conn, sql(' "WH_b"."schema_c"."table_ca" ')))  # Quoted mixed case object names also Works
ca$ops

# Clean up 
dbGetQuery(conn, 'DROP DATABASE "WH_b" ')