Is there a way to extract the DDL generated by the create_table method?

[csubhodeep]

I would like to manipulate some of the data types that ibis is deciding for some of the fields of a table which I intend to create on a given backend e.g. doing the below

import ibis

t = ibis.memtable({"a": [1.0,2,3], "b": ["x", "y", "zz"]})

conn = ibis.mssql.connect()

ddl_sql = t.schema().to_sql(
    # dialect = "mssql",  #  either this or the backend as another argument
    conn
)
print(ddl_sql)

could give me

CREATE TABLE [dbo].[<some name>]
(
	[a] [bigint]  NULL,
	[b] [varchar(max)]  NULL -- this is the one I would like to change to, let's say, `varchar(2)`
)

and then maybe use ibis.raw_sql as mentioned in this thread

I know, a method like this does not exist in the current version of ibis but is there any workaround because I know ibis would generate the DDL when the conn.create_table method would be called.

[cpcloud]

I think we could probably add a .compile() method to our Schema object, which would get you everything between the parentheses and would probably clean up a bunch of our internals in the various create_table methods.

For example, we could likely easily get you the expressions list here:

In [1]: import sqlglot as sg

In [2]: sg.parse_one("create table t (x int, y int)")
Out[2]:
Create(
  this=Schema(
    this=Table(
      this=Identifier(this=t, quoted=False)),
    expressions=[
      ColumnDef(
        this=Identifier(this=x, quoted=False),
        kind=DataType(this=Type.INT, nested=False)),
      ColumnDef(
        this=Identifier(this=y, quoted=False),
        kind=DataType(this=Type.INT, nested=False))]),
  kind=TABLE)

[csubhodeep]

ok this would be great !

[csubhodeep]

Also I asked this because as long as #9382 is not solved, I am trying to workaround it. I won't need to go this way if we could simply have something like

t = t.mutate(
    some_string_column = t['some_string_column'].cast(ibis.dtype('string(2)')) # which can get translated to `VARCHAR(2)` in the DDL query used by `create_table`
)

[cpcloud]

Ok, well funny enough I put up a draft PR for that earlier this morning 😄