Allow creating a Trino table using dataframe

[andythsu]

Describe the feature

Hello, we'd like to support creating a Trino table using dataframe.
API would be simple, something like

conn = connect()
cursor = conn.cursor()
df = pd.Dataframe()
cursor.insert_df("foo", df, 800*1000) # 800k

where

def insert_df(table: str, df: pd.Dataframe, max_size: int):
    :param table: table to create/replace.
    :param df: dataframe to insert.
    :param max_size: max number of characters allowed for the SQL query text. This should be <= query.max-length set on Trino to prevent 
    QUERY_TEXT_TOO_LARGE error.

This should be enough for phase 1. For future iterations, we can think about how to upsert data to an existing table....

Describe alternatives you've considered

No response

Are you willing to submit PR?

• Yes I am willing to submit a PR!

[hashhar]

You mention the API but not the implementation. Are you planning to issue INSERT queries in the implementation? If so then isn't this already possible today? Also what the performance impact?

It'll be more worthwhile to have support in the engine to perform bulk inserts and then use that in the client instead of adding APIs on the client which simply "wraps" an existing functionality with slow performance for this use-case.

[andythsu]

@hashhar I was thinking to use sqlalchemy to achieve this. It'll be a bulk INSERT query rather than multiple INSERT queries . In terms of performance I don't have concrete numbers but we are trying with 10MB of data and it took about 30 seconds.

In terms of implementation it'll be something similar to

from sqlalchemy.engine import create_engine
def insert_df(trino_connection: str, table: str, df: pd.Dataframe, max_size: int):
  engine = create_engine(trino_connection)
  result = df.to_sql(table_name, 
      engine, 
      if_exists="replace", 
      index=False, 
      method="multi", 
      chunksize=chunk_size)
  return result

where the client can then use this as

df = pd.read_csv("/path/to/my/csv")
max_size = 1_000_000
conn = connect(
    host="...",
    port="...",
    user="...",
    catalog="...",
    schema="...",
    http_scheme='...',
    request_timeout=...
)
cursor: Cursor = conn.cursor()
from trino.sqlalchemy import URL as trino_url
res = cursor.insert_df_2(trino_url(host="...",
                                   port="...",
                                   user="...",
                                   catalog="...",
                                   schema="..."), "foo", df, max_size)

I'm not sure if we can reuse the auth in the Connection class since sqlalchemy requires username and password in the connection string. If not, we can use trino_url to rebuild the connection string.

[hashhar]

It doesn't matter if you use multi or not in to_sql. The Python client's executemany sends multiple queries not a single query with a large VALUES list in this client.

It seems your suggestion is basically providing a "single method API" for wrapping pandas to_sql. I don't see a good reason to do that personally since functionally it's exactly the same. If/when we have a native way to receive dataframes on Trino side this API you propose will make much more sense.

[hashhar]

I think we're going to close this for now since right now the proposal is basically providing syntax sugar/wrapping SQLAlchemy + to_sql.

We can instead document best practice for how to write a DF to SQL.

If/when we have ability on the server to receive dataframes we can revisit this idea.