feat(ddl): write table to a specific schema in databases that support it

[raheems]

Hello, I was wondering if it is possible to write to a specific schema within the database?

Suppose, there is a schema named "q_2021" under q_database and I want to write a table inside q_2021 schema.

create_table(self, name, expr=None, schema=None, database=None, force=False)

How to specify the schema name here?

The create_table() writes to the q_database directly.

[raheems]

Can we mark it as a feature request, please? And thank you.

[cpcloud]

Hi @raheems, can you give an minimal working example of how you're currently using the API, that shows what backend you're using, how you're connecting and how you're calling the create_table API?

There might already be a way to do what you're asking.

[raheems]

Connection

con = ibis.postgres.connect(
    user='XXX',
    password='XXX',
    host='XXX.redshift.amazonaws.com',
    port=5439,
    database='q_database',
)

import ibis
ibis.__version__

'3.0.2'

ibis.options.interactive = True
ibis.options.sql.default_limit = 100

Define the condition table in q_2021 schema

tbl_condition = con.table('condition', schema = "q_2021")

Running the .execute() works

tbl_condition.head().execute()
# Works

# Creatring a table object to write back to schema
tbl_sample = tbl_condition[tbl_condition['diagnosis_code_code'].like('C50%')]
tbl_sample.head() # works

Checking the type of the object

type(tbl_sample)

ibis.expr.types.relations.TableExpr

con.create_table('tbl_to_write', expr=tbl_sample)
# writes to the q_database

Writing tbl_sample as 'tbl_to_write' works but it only writes to the 'q_database'. I want to write it to the new_schema schema. How to do that? The schema parameter doesn't accept schema = 'new_schema'

I have write access to the new_schema

[raheems]

@cpcloud Any suggestions please?

[cpcloud]

create_table doesn't appear to have support for interacting with postgres schemas, but you can always use con.raw_sql("CREATE TABLE {schema}.{table_name} ...") for this in a pinch.

[cpcloud]

Duplicate of #6821