create_table() adding quotes automatically

[vmsaipreeth]

When we pass a string to create_table’s ‘name’ parameter, quotes are getting appended to the passed string.

Example:
table_name = ‘’’”a”.”b”.”c”’’’
connection.create_table(table_name, table_obj, overwrite=True)

It’s returning: CREATE TABLE “””””a””.””b””.”””c”””” (…)

Eventually, we need either “a”.”b”.”c” or a.b.c

[gforsyth]

Hi @vmsaipreeth -- could you add some triple backticks (```) around your examples so it's easier to see which sorts of quotes you are using in which positions?

And can you explain a little bit more about what you are doing and which backend(s) you are using?

Thanks!

[vmsaipreeth]

Sure.
We are using trino as backend. We are trying to write ibis table into trino.

Here’s my example:

connection = ibis.trino.connect( trino_connection_parameters)
. . .
table_name = ‘’’ ”a”.”b”.”c” ’’’
# table_obj -> ibis.expr.sql.SQLString
connection.create_table(table_name, table_obj, overwrite=True)

This returns an SQL which is

CREATE TABLE “””a””.””b””.””c””” ( . . . )

We also tried passing table_name as a simple string

table_name = ‘a.b.c’

But the create_table() would return SQL as

CREATE TABLE “a.b.c” ( . . . )

we are hoping to get rid of quotes completely so that create_table() would only interpret as

CREATE TABLE a.b.c ( . . . )

Thanks

[gforsyth]

Thanks for clarifying! If I understand correctly, when you have a table a.b.c you are referring to a table named c in a schema named b in a catalog named a, yes?

This is a little hard to deal with right now, because there is a lot of fragmentation among the SQL engines about what to call each of these levels. We are hoping to resolve that soon, tracking issue is #6331.

Right now, you might do something like

import ibis

con = ibis.trino.connect(user="user", host="localhost", port=8080, database="memory", schema="default")

there, database is equivalent to catalog, and schema is equivalent to schema (both in the Trino sense)

Mine is empty, so there are no tables:

[ins] In [3]: con = ibis.trino.connect(
         ...:     user="user", host="localhost", port=8080, database="memory", schema="default"
         ...: )

[ins] In [4]: con.list_tables()
Out[4]: []

I can create an ibis schema (names are hard) which is the mapping of column names to datatypes, and use that to create a new (empty) table:

[ins] In [5]: import ibis.expr.schema as sch

[ins] In [6]: s = sch.from_mapping({"id": "int32", "name": "str"})

[ins] In [7]: s
Out[7]: 
ibis.Schema {
  id    int32
  name  string
}

[ins] In [8]: con.create_table("my_first_table", schema=s)
Out[8]: 
DatabaseTable: my_first_table
  id   int32
  name string

[ins] In [9]: con.list_tables()
Out[9]: ['my_first_table']

the fully qualified name of my_first_table is memory.default.my_first_table.

Now, Ibis doesn't currently implement new Trino schema creation, but you can use raw_sql on the connection to bypass that:

[ins] In [12]: con.raw_sql("CREATE SCHEMA my_new_schema")
Out[12]: <sqlalchemy.engine.cursor.LegacyCursorResult at 0x7fcf33fd06d0>

now I can make a new connection to that Trino schema, and it will have no tables (because I just created it):

[ins] In [13]: con = ibis.trino.connect(
          ...:     user="user", host="localhost", port=8080, database="memory", schema="my_new_schema"
          ...: )

[ins] In [14]: con.list_tables()
Out[14]: []

However, you can list the tables in the original schema default as so:

Note: this will change -- here, database is equivalent to a Trino schema (this is very confusing)

[ins] In [21]: con.list_tables(database="default")
Out[21]: ['my_first_table']

Now we can create a new table in my_new_schema:

[ins] In [22]: s2 = sch.from_mapping({"id": "int32", "last_name": "str"})

[ins] In [23]: con.create_table("my_second_table", schema=s2)
Out[23]: 
DatabaseTable: my_second_table
  id        int32
  last_name string

[ins] In [24]: con.list_tables()
Out[24]: ['my_second_table']

the fully qualified name of this table is memory.my_new_schema.my_second_table

You can have table objects in Ibis that refer across these Trino schema, e.g.

[ins] In [26]: t1 = con.table("my_first_table", schema="default")

[ins] In [27]: t2 = con.table("my_second_table")
[ins] In [28]: expr = t1.join(t2, "id")

[ins] In [29]: expr.schema()  # this 'schema' is the mapping of names to dtypes
Out[29]: 
ibis.Schema {
  id         int32
  name       string
  last_name  string
}

So finally, if you want to create a table where the fully qualified name would be a.b.c, you would do:

 con = ibis.trino.connect(user="user", host="localhost", port=8080, database="a", schema="b")
con.create_table("c", table_obj, overwrite=True)

Hopefully that helps -- it's also possible I'm missing some context.

[vmsaipreeth]

Thank you @gforsyth for the detailed explanation! Highly appreciate it.

Yes, in my example, a is the catalog, b is the schema and c is the table.

We already tried passing the ibis schema via the ‘schema’ parameter.

table_obj.schema() returns ibis.Schema

connection= ibis.trino.connect(user="user", host="host", port=port, catalog=“a", schema="b")

connection.create_table(‘c’, obj = table_obj, schema= table_obj.schema())

We see a NoSuchTableError : c even though the table is created in the backend. However, the created table has completely different rows than the actual output. We are debugging the cause of this.

We also tried passing the SQL string:

. . . # computation on ibis table
table_obj_sql = ibis.to_sql(table_obj)
. . . # table_obj_sql is ibis.SQLString -> so converted to a string
connection.create_table(‘c’, obj = connection.sql(table_obj_sql), schema= table_obj.schema())

But above piece of code throwed a ParseError

ParseError: expected 'EOF' at 0:7

Alternatively, we also thought of writing to a csv and reading from csv

table_obj.to_csv('test.csv')
table_obj_test.read_csv('test.csv')
connection.create_table(‘c’, obj = table_obj_test, schema= table_obj_test.schema())

But noticed a NoSuchModuleError: Can't load plugin: sqlalchemy. dialects:duckdb

[gforsyth]

What about leaving out the schema argument?

connection= ibis.trino.connect(user="user", host="host", port=port, catalog=“a", schema="b")

connection.create_table(‘c’, obj = table_obj)

As for the ModuleError, that's a strange error to get with the Trino backend -- if you have a reliable reproducer for that, please file an issue. (Also, if you just want the error to go away, you can pip install 'ibis-framework[duckdb, trino]')

[vmsaipreeth]

Yes, Tried that too.

connection= ibis.trino.connect(user="user", host="host", port=port, catalog=“a", schema="b")

connection.create_table(‘c’, obj = table_obj)

we got NoSuchTableError: c

Also, the NoSuchModuleError is now solved. We did a pip install 'ibis-framework[duckdb]' and now we are able to read from cav
However, we are still unable to write into trino.

connection= ibis.trino.connect(user="user", host="host", port=port, catalog=“a", schema="b")
. . . # computation on ibis table
table_obj.to_csv('test.csv')
table_obj_test.read_csv('test.csv') # works now

connection.create_table(‘c’, obj = table_obj_test)

Here, the weird part is that it is throwing NoSuchTableError: c but the table is getting created (with the specified schema) in trino.

we also tried having a try and except block for this since it was throwing NoSuchTableError: c

try: 
    connection.create_table(‘c’, schema = table_obj.schema())
except:
    connection.insert('c', obj = table_obj)

We again see the same NoSuchTableError: c for the insert statement.

[gforsyth]

Can you paste the code that's triggering the exception and the full traceback?

[vmsaipreeth]

Sure.
Here is what we are doing.

import ibis
import pyarrow.csv as pcsv

connection= ibis.trino.connect(user="user", host="host", port=port, catalog=“a", schema="b")
. . . # computation on ibis table
table_obj.to_csv('test.csv') # table_obj is of type <class 'ibis.expr.types.relations.Table' >
pyarrow_table = pcsv.read_csv('test.csv') 

print(type(pyarrow_table))
# the above statement prints -- <class 'pyarrow.lib.Table'>

print(pyarrow_table)  # this is just an example table
# the above statment prints -- 
# pyarrow.Table
# year: int64
# n_legs: int64
# animals: string
# ----
# year: [[2020,2022,2019,2021]]
# n_legs: [[2,4,5,100]]
# animals: [["Flamingo","Horse","Brittle stars","Centipede"]]

connection.create_table(‘c’, obj = pyarrow_table) # This line is causing the exception

and below is the full traceback of the error

---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
Cell In[22], line 1
----> 1 connection.create_table(‘c’, obj = pyarrow_table)

File ~\AppData\Local\Programs\Python\Python310\lib\site-packages\ibis\backends\base\sql\alchemy\__init__.py:228, in BaseAlchemyBackend.create_table(self, name, obj, schema, database, temp, overwrite)
    223         raise com.IbisTypeError(
    224             'Expression schema is not equal to passed schema. '
    225             'Try passing the expression without the schema'
    226         )
    227 if schema is None:
--> 228     schema = obj.schema()
    230 self._schemas[self._fully_qualified_name(name, database)] = schema
    231 table = self._table_from_schema(
    232     name,
    233     schema,
    234     database=database or self.current_database,
    235     temp=temp,
    236 )

TypeError: 'pyarrow.lib.Schema' object is not callable

[gforsyth]

The line number in that traceback, (File ~\AppData\Local\Programs\Python\Python310\lib\site-packages\ibis\backends\base\sql\alchemy\__init__.py:228) doesn't match as of versrion 6.0.

Can you confirm (in the Python repr) what ibis.__version__ says? There might be an environment issue here (is ipython installed in the environment where you have ibis 6.x installed?)

[vmsaipreeth]

Apologies for the delay.

We made sure we are on ibis version 6.0.0. We played around with the latest version and tried all the possible ways(discussed above) to use create_table().

Eventually, the table name which we pass as a parameter to create_table() is case sensitive and only accepts lower case. Not sure if this is the same scenario with other backends.

However, we are now able to use create_table() directly.

Thank you @gforsyth and @cpcloud for quick responses and work arounds. We highly appreciate it. Will reach out if we have any following questions.