ray.data.read_sql#

ray.data.read_sql(sql: str, connection_factory: Callable[[], Any], *, parallelism: int = -1, ray_remote_args: Dict[str, Any] | None = None, concurrency: int | None = None, override_num_blocks: int | None = None) → Dataset[source]#

Read from a database that provides a Python DB API2-compliant connector.

Note

By default, read_sql launches multiple read tasks, and each task executes a LIMIT and OFFSET to fetch a subset of the rows. However, for many databases, OFFSET is slow.

As a workaround, set override_num_blocks=1 to directly fetch all rows in a single task. Note that this approach requires all result rows to fit in the memory of single task. If the rows don’t fit, your program may raise an out of memory error.

Examples

For examples of reading from larger databases like MySQL and PostgreSQL, see Reading from SQL Databases.

import sqlite3

import ray

# Create a simple database
connection = sqlite3.connect("example.db")
connection.execute("CREATE TABLE movie(title, year, score)")
connection.execute(
    """
    INSERT INTO movie VALUES
        ('Monty Python and the Holy Grail', 1975, 8.2),
        ("Monty Python Live at the Hollywood Bowl", 1982, 7.9),
        ("Monty Python's Life of Brian", 1979, 8.0),
        ("Rocky II", 1979, 7.3)
    """
)
connection.commit()
connection.close()

def create_connection():
    return sqlite3.connect("example.db")

# Get all movies
ds = ray.data.read_sql("SELECT * FROM movie", create_connection)
# Get movies after the year 1980
ds = ray.data.read_sql(
    "SELECT title, score FROM movie WHERE year >= 1980", create_connection
)
# Get the number of movies per year
ds = ray.data.read_sql(
    "SELECT year, COUNT(*) FROM movie GROUP BY year", create_connection
)
Parameters:

  • sql – The SQL query to execute.

  • connection_factory – A function that takes no arguments and returns a Python DB API2 Connection object.

  • parallelism – This argument is deprecated. Use override_num_blocks argument.

  • ray_remote_args – kwargs passed to remote() in the read tasks.

  • concurrency – The maximum number of Ray tasks to run concurrently. Set this to control number of tasks to run concurrently. This doesn’t change the total number of tasks run or the total number of output blocks. By default, concurrency is dynamically decided based on the available resources.

  • override_num_blocks – Override the number of output blocks from all read tasks. By default, the number of output blocks is dynamically decided based on input data size and available resources. You shouldn’t manually set this value in most cases.

Returns:

A Dataset containing the queried data.

PublicAPI (alpha): This API is in alpha and may change before becoming stable.