trino multi insert python

hsnyate

3 min read

·

11-03-2025

4

0

Share

Trino, the distributed SQL query engine, doesn't directly support multi-insert statements in the same way some databases do (e.g., INSERT INTO ... VALUES (...), (...)). However, you can achieve efficient multi-insert functionality using Python and Trino's capabilities. This guide explores several effective methods, comparing their performance and suitability for different use cases. We'll focus on leveraging Trino's power for large-scale data ingestion.

Method 1: Batch Inserts using psycopg2 (for PostgreSQL-compatible connectors)

If you're using a Trino connector compatible with PostgreSQL's COPY command, this offers the most efficient method for large datasets. psycopg2, a popular Python PostgreSQL adapter, can be leveraged to send batch inserts to Trino.

Prerequisites:

• A Trino server running and accessible.
• A Trino connector compatible with COPY (check your connector's documentation).
• psycopg2 installed (pip install psycopg2-binary).

Code Example:

import psycopg2

def multi_insert_copy(conn, table_name, data):
    """Inserts data into Trino table using COPY command."""
    cur = conn.cursor()
    try:
      # Construct the COPY command.  Adapt column names as needed.
      copy_sql = f"""COPY {table_name} FROM STDIN WITH (FORMAT CSV, HEADER TRUE)"""
      cur.copy_expert(copy_sql, data)
      conn.commit()
      print(f"Successfully inserted {len(data)} rows into {table_name}")
    except psycopg2.Error as e:
        conn.rollback()
        print(f"Error during COPY: {e}")
    finally:
        cur.close()

# Example Usage:
conn_params = {
    "host": "your_trino_host",
    "port": 8080,  # Or your Trino port
    "database": "your_database",
    "user": "your_user",
    "password": "your_password"
}
conn = psycopg2.connect(**conn_params)
data = [("value1", "value2"), ("value3", "value4"), ("value5", "value6")] # Replace with your data

multi_insert_copy(conn, "your_table_name", data)  # Replace with your table name
conn.close()

Advantages: High performance for large datasets due to optimized bulk loading.

Disadvantages: Requires a compatible connector and careful handling of data formatting to match the COPY command's requirements. Error handling is crucial.

Method 2: Multiple Single-Insert Statements with Python

This approach involves iterating through your data and executing individual INSERT statements for each row. It's straightforward but less efficient for large datasets.

Code Example:

import trino
from trino.dbapi import connect

def multi_insert_single(conn, table_name, data):
  """Inserts data into Trino table using individual INSERT statements."""
  cur = conn.cursor()
  try:
    for row in data:
        insert_sql = f"INSERT INTO {table_name} VALUES ({','.join(['%s'] * len(row))})"
        cur.execute(insert_sql, row)
    conn.commit()
    print(f"Successfully inserted {len(data)} rows into {table_name}")
  except trino.exceptions.TrinoException as e:
    conn.rollback()
    print(f"Error during insert: {e}")
  finally:
    cur.close()

# Example usage (similar to Method 1, replace connection details)
conn = connect(**conn_params) #Use the conn_params from Method 1 example
data = [("value1", "value2"), ("value3", "value4"), ("value5", "value6")] #Replace with your data

multi_insert_single(conn, "your_table_name", data) #Replace with your table name
conn.close()

Advantages: Simple to implement and understand. Works with any Trino connector.

Disadvantages: Significant performance overhead for large datasets due to numerous individual database calls.

Method 3: Using a Pandas DataFrame and Trino's write_pandas (if applicable)

If your data is in a Pandas DataFrame, some Trino clients (check your client library's documentation) might provide a write_pandas function for direct DataFrame insertion. This can be more efficient than manual iteration.

Code Example (Illustrative – Check your client library's documentation):

import pandas as pd
import your_trino_client  # Replace with your Trino client library

# ... (your data in a Pandas DataFrame called 'df') ...

your_trino_client.write_pandas(df, "your_table_name", conn)

Advantages: Potentially efficient if your client library supports it and you're already working with Pandas.

Disadvantages: Relies on client library support. Might not be as efficient as the COPY method for truly massive datasets.

Choosing the Right Method

• For very large datasets (millions of rows): The COPY command (Method 1) offers the best performance.
• For smaller datasets or when compatibility with COPY is an issue: Method 2 (individual inserts) is simpler but less efficient.
• If using Pandas: Explore your Trino client library for a write_pandas function (Method 3). Benchmark to compare performance with other methods.

Remember to always include robust error handling and consider using transactions for data integrity. The choice ultimately depends on your data volume, the Trino connector you are using, and the available client library features. Always benchmark different approaches to determine the optimal strategy for your specific use case and data size.