create_table() overhead time

[vmsaipreeth]

Hi folks!

We have the latest version of ibis-framework which is 6.1.0

We have trino as our backend and we were exploring create_table() in order to write tables to trino.

Below is the sample code.

# connection to trino
connection = ibis.trino.connect(trino_connection_parameters)

# fetch data from trino to ibis dataframe
df_ibis = connection.sql(“””SQL STATEMENT ”””)

# perform manipulations on df_ibis

# write into trino
connection.create_table(“table_name”, obj=df_ibis, overwrite=True)

We have also tried using create_table() with only providing schema parameter and then using connection.insert() approach

We tried multiple tests with increasing no. of rows and noticed some overhead time(roughly 1 min) while creating tables in trino using above mentioned approached

Here are some appropriate write times with no. of rows:

Test 1:
No. of rows: 14
Time took for writing tables: 1min 10s

Test 2:
No. of rows: ~36,000
Time took for writing tables: 1min 15s

Test 3:
No. of rows: 1.8Million
Time took for writing tables: 2min 30s

Test 4:
No. of rows: 2.3Million
Time took for writing tables: 2min 55s

Is there any way that we can reduce the overhead time here?

Please free to open an issue regarding the same.

Thanks!

[cpcloud]

@vmsaipreeth Thanks for opening a discussion!

Can you confirm the cost of executing the queries without calling create_table?

I'd like to be able to isolate the cost of the SQL query from the cost of create_table.

Can you show the time to run df_ibis alone?

[cpcloud]

Can you show the following things?

1. The exact code you're using, minus the SQL if you're not able to show that.
2. The table schema you're using: column names and types. If you're unable to show the column names, listing them as column1, column2, ..., columnN` is fine!
3. The version of Trino you're using
4. The connectors involved

Thank you!

[vmsaipreeth]

Sure!
The code shared above is almost the actual code.

# connection to trino
connection = ibis.trino.connect(host = 'HOST', user = 'USER', password = 'PASSWORD', port = 123, database = 'DATABASE', http_schema = 'https', schema = 'SCHEMA')
# connection time: ~2.5 secs 

# fetch data from trino to ibis dataframe
df_ibis = connection.sql(“””SQL STATEMENT”””)
# fetching data time(single dataframe): ~2 secs

print(df_ibis.schema())
# prints the following column names and types: no.of columns = 19
# ibis.Schema {
#     column_1    int64
#     column_2    int64
#     column_3    int64
#     column_4    string
#     column_5    int64
#     column_6    int64
#     column_7    string
#     column_8    string
#     column_9    int64
#     column_10    string
#     column_11    string
#     column_12    int64
#     column_13    int64
#     column_14    string
#     column_15    string
#     column_16    timestamp
#     column_17    timestamp
#     column_18    timestamp
#     column_19    timestamp
# }

# write into trino
connection.create_table(“table_name”, obj=df_ibis, overwrite=True)
# writing table to trino time: ~1min 10secs

And we are on Trino version: 0.326.0

I hope I answered your questions.

Please let me know if I missed anything or if any further information is required.

Thanks!

[cpcloud]

Was able to get back to this!

I can't seem to reproduce the performance issue with trino 425:

In [1]: from ibis.interactive import *
c
In [2]: con = ibis.trino.connect(database="hive", schema="default")
t
In [3]: t = con.table("faker")

In [4]: %time tf = con.create_table("test_faker", obj=con.sql("SELECT * FROM faker"), overwrite=True)
CPU times: user 92.6 ms, sys: 6.31 ms, total: 98.9 ms
Wall time: 5.99 s

In [5]: t.count()
Out[5]: 2500000

In [6]: t.schema()
Out[6]:
ibis.Schema {
  column01  int64
  column02  int64
  column03  int64
  column04  string
  column05  int64
  column06  int64
  column07  string
  column08  string
  column09  int64
  column10  string
  column11  string
  column12  int64
  column13  int64
  column14  string
  column15  string
  column16  timestamp(6)
  column17  timestamp(6)
  column18  timestamp(6)
  column19  timestamp(6)
}

In [8]: t.head()
Out[8]:
┏━━━━━━━━━━━┳━━━━━━━━━━━┳━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━┳━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━┳━━━━━━━━━━┳━━━━━━━━━━┳━━━━━━━━━━━┳━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━┓
┃ column01  ┃ column02  ┃ column03  ┃ column04        ┃ column05  ┃ column06  ┃ column07                                           ┃ column08            ┃ column09  ┃ column10 ┃ column11 ┃ column12  ┃ column13  ┃ column14          ┃ column15 ┃ column16            ┃ column17            ┃ column18            ┃ column19            ┃
┡━━━━━━━━━━━╇━━━━━━━━━━━╇━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━╇━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━╇━━━━━━━━━━╇━━━━━━━━━━╇━━━━━━━━━━━╇━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━┩
│ int64     │ int64     │ int64     │ string          │ int64     │ int64     │ string                                             │ string              │ int64     │ string   │ string   │ int64     │ int64     │ string            │ string   │ timestamp(6)        │ timestamp(6)        │ timestamp(6)        │ timestamp(6)        │
├───────────┼───────────┼───────────┼─────────────────┼───────────┼───────────┼────────────────────────────────────────────────────┼─────────────────────┼───────────┼──────────┼──────────┼───────────┼───────────┼───────────────────┼──────────┼─────────────────────┼─────────────────────┼─────────────────────┼─────────────────────┤
│ 163999854 │ 197562768 │ 677964146 │ Bianca Palmer   │ 530341931 │ 874651105 │ 430 Kathleen Crossing\nZhangville, VT 97197        │ New Christophertown │ 351121674 │ PA       │ 40630    │ 828415635 │ 854992467 │ Sharon Frey       │ 46421    │ 2013-05-21 08:20:39 │ 2022-01-03 14:21:41 │ 2000-02-21 15:56:35 │ 1976-05-25 09:02:39 │
│ 478478617 │ 380068992 │ 480775762 │ Kathryn Ballard │ 957973599 │ 572750739 │ 231 Buchanan Stream\nEast Brianberg, HI 26619      │ West Ashleyshire    │ 641258256 │ DE       │ 42743    │ 265904595 │ 689730463 │ Victoria Valencia │ 73709    │ 2021-03-23 21:34:45 │ 2000-10-26 02:34:19 │ 2017-09-30 20:28:12 │ 2009-05-10 06:03:28 │
│ 103922683 │  67111794 │ 497998811 │ Jeremy Walker   │ 629626493 │ 524524726 │ 0646 Kimberly Manors\nNew Amber, KS 65814          │ Lynnborough         │ 388392951 │ CO       │ 49941    │ 818372957 │   2598646 │ Richard Johnson   │ 24332    │ 2008-08-02 10:28:12 │ 1997-01-25 06:30:11 │ 1979-08-04 00:57:00 │ 1980-02-26 03:41:48 │
│  72137975 │ 627735774 │ 189885661 │ David Hicks     │ 824787164 │  40309768 │ 70866 Shannon Hills Apt. 519\nSarahmouth, LA 83304 │ Newtonside          │ 156757364 │ KS       │ 49579    │ 490540978 │ 573235520 │ Patricia Powell   │ 45989    │ 1971-02-05 10:14:50 │ 1984-07-29 00:22:13 │ 2003-02-08 13:24:56 │ 2001-08-28 21:24:34 │
│ 327128935 │ 995598998 │ 398893655 │ Linda Lyons     │ 189820315 │ 888291514 │ PSC 5583, Box 6110\nAPO AA 93718                   │ Webertown           │ 587807180 │ MA       │ 33444    │ 520197864 │ 348129985 │ Laura Reed        │ 50498    │ 1986-07-25 19:09:26 │ 1986-12-04 21:39:24 │ 1984-01-05 04:58:32 │ 2022-02-20 09:06:51 │
└───────────┴───────────┴───────────┴─────────────────┴───────────┴───────────┴────────────────────────────────────────────────────┴─────────────────────┴───────────┴──────────┴──────────┴───────────┴───────────┴───────────────────┴──────────┴─────────────────────┴─────────────────────┴─────────────────────┴─────────────────────┘

I'm going to try with trino 351, which is the oldest docker image available.

[cpcloud]

Hm 351 doesn't seem to work with our current test configuration. @vmsaipreeth Are there any more details you can provide?

• What connector is the source table?
• What connector are you writing to?

[cpcloud]

Without more information it's hard to move forward here.