How should we spell creating tables inside databases and schemas

[cpcloud]

We've made a lot of progress on cleaning up specifying database and schema (the hierarchy kind) in all the places in can be used.

Users can list_* inside schemas and databases where supported and they can also read tables across schemas and databases where supported.

There's one remaining major API where we don't have a clear way to do this: create_table.

It's a bit unfortunate, but we have an argument named schema that is for specifying column names and types, not a namespace inside of database.

There are a few options here.

Rename create_table's current schema argument to columns

We'd repurpose schema for hierarchies. database is unchanged.

This is likely to cause breakage and probably confusion since the same word now means something very different.

# db.my_schema.t
con.create_table("t", schema="my_schema", database="db", columns=ibis.schema(...))

Keep schema, replace database with catalog; database becomes schema

This is a pretty nuclear option, as we'd want to change everything else for consistency.

con.create_table("t", database="db", catalog="cat")

Keep database as is, and use it for specifying namespaces

con.create_table("table", database="db.schema")

Deprecate database and use dotted path syntax in the table name

Might be some annoying issues with quoting and backend specificity, but I think that can largely be punted to sqlglot.

con.create_table("db.schema.table")

If we do this we should probably support con.table("db.schema.table") as well.

Let's vote on the options!

I'm happy to add other options as they come up.

[gforsyth]

5th option: allow both options 3 and 4

con.create_table("table", database="db.schema")

con.create_table("db.schema.table")

and potentially (???)

con.create_table("schema.table", database="db")

[cpcloud]

If you voted, please vote again as I added a fifth option

[inigohidalgo]

@gforsyth read my mind with his suggestion, but I have one thought: do you think it might be confusing to people that it's possible to specify something which isn't a database in the database argument? I like the keyword namespace which sounds less prescriptive in what you'd need to supply.

It's certainly unfortunate that both meanings of schema are also so widespread, if the column schema was only an Ibis thing I'd definitely vote to rename it just to columns. Maybe columns_schema?

To summarize, I'm not proposing additional options, rather amendments to ones already proposed

• 1 but instead of just "columns", "column_schema", to preserve the meaning that we're defining datatypes rather than the values in those columns
• 5 but rename the new flexible "database" to namespace, (and maybe keep database as the current meaning?)

[inigohidalgo]

Btw I have to say, and I think you already are aware of this, but you should take any suggestions I make with a grain of salt, due to the fact that my experience with SQL is limited only to querying pre-existing databases, and always processed analytics stuff, nothing operational or OLTP so I'm probably missing a lot of context in how databases, namespaces and schemas etc are used elsewhere :)