atoti.Session.read_sql()#

Session.read_sql(sql, /, *, url, table_name, driver=None, keys=(), partitioning=None, types={}, default_values={})#

Create a table from the result of the passed SQL query.

Note

This method requires the atoti-sql plugin.

Parameters:

  • sql (str) – The result of this SQL query will be loaded into the table.

  • url (str) –

    The JDBC connection string of the database. The jdbc: prefix is optional but the database specific part (such as h2: or mysql:) is mandatory. For instance:

    • h2:file:/home/user/database/file/path;USER=username;PASSWORD=passwd

    • mysql://localhost:7777/example?user=username&password=passwd

    • postgresql://postgresql.db.server:5430/example?user=username&password=passwd

    More examples can be found here.

  • driver (str | None) – The JDBC driver used to load the data. If None, the driver is inferred from the URL. Drivers can be found in the atoti_sql.drivers module.

  • table_name (str) – The name of the table to create.

  • keys (Iterable[str]) –

    The columns that will become keys of the table.

    Inserting a row containing key values equal to the ones of an existing row will replace the existing row with the new one.

    Key columns cannot have None as their default_value.

  • partitioning (str | None) –

    The description of how the data will be split across partitions of the table.

    Default rules:

    • Only non-joined tables are automatically partitioned.

    • Tables are automatically partitioned by hashing their key columns. If there are no key columns, all the dictionarized columns are hashed.

    • Joined tables can only use a sub-partitioning of the table referencing them.

    • Automatic partitioning is done modulo the number of available cores.

    Example

    hash4(country) splits the data across 4 partitions based on the country column’s hash value.

  • types (Mapping[str, DataType]) – Types for some or all columns of the table. Types for non specified columns will be inferred from the SQL types.

  • default_values (Mapping[str, ConstantValue | None]) – Mapping from column name to column default_value.

Return type:

Table

Example

>>> table = session.read_sql(
...     "SELECT * FROM MYTABLE;",
...     url=f"h2:file:{RESOURCES}/h2-database;USER=root;PASSWORD=pass",
...     table_name="Cities",
...     keys=["ID"],
... )
>>> len(table)
5