atoti.Session.read_sql()#
- Session.read_sql(sql, /, *, url, table_name, driver=None, keys=frozenset({}), partitioning=None, types={}, default_values={})#
Create a table from the result of the passed SQL query.
- 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 ash2:
ormysql:
) 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
driver
used to load the data.If
None
, it is inferred from url.table_name (str) – The name of the table to create.
keys (Set[str] | Sequence[str]) –
The columns that will become
keys
of the table.If a
Set
is given, the keys will be ordered as the table columns.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
modulo4(country)
splits the data across 4 partitions based on the country column’s dictionarized 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, bool | date | datetime | float | int | Sequence[int] | Sequence[float] | str | time | None]) – Mapping from column name to column
default_value
.
- Return type:
Example
>>> table = session.read_sql( ... "SELECT * FROM MYTABLE;", ... url=f"h2:file:{RESOURCES_PATH}/h2-database;USER=root;PASSWORD=pass", ... table_name="Cities", ... keys={"ID"}, ... ) >>> table.row_count 5