cosmotech.coal.postgresql¶
Optional dependency
native_csv_ingestion and psycopg_utils need psycopg: pip install "cosmotech-acceleration-library[custom]".
cosmotech.coal.postgresql.native_csv_ingestion
¶
PostgreSQL native CSV ingestion module.
This module streams CSV files straight into existing PostgreSQL tables with the native COPY ... FROM STDIN
command. Python only reads bytes and forwards them: PostgreSQL parses the CSV and converts the values into the
column types declared on the table. Each file is loaded in a single transaction.
The connection is handled by cosmotech.coal.postgresql.psycopg_utils.
Functions:
| Name | Description |
|---|---|
ingest_csv_file |
Load one CSV file into an existing table, in one transaction. |
ingest_csv_folder |
Load every |
ingest_csv_file(csv_path, configuration, table=None, table_prefix=DEFAULT_TABLE_PREFIX, run_id=None, run_preflight=True)
¶
Load one CSV file into an existing table, in one transaction.
Parameters:
| Name | Type | Description | Default |
|---|---|---|---|
csv_path
|
Path
|
CSV file, with a header line |
required |
configuration
|
Configuration
|
coal Configuration with a postgres section |
required |
table
|
Optional[str]
|
target table, used verbatim; default is the lowercased table_prefix + file stem |
None
|
table_prefix
|
str
|
prefix used when no table is given |
DEFAULT_TABLE_PREFIX
|
run_id
|
Optional[str]
|
when set (--add-id), the csm_run_id column is filled with it through the transaction-local setting |
None
|
run_preflight
|
bool
|
whether to check the table and the header before loading (False if already done by the caller) |
True
|
Returns:
| Type | Description |
|---|---|
int
|
The number of rows copied |
ingest_csv_folder(folder, configuration, table_prefix=DEFAULT_TABLE_PREFIX, run_id=None)
¶
Load every *.csv file of a folder into its own table (lowercased table_prefix + file stem).
All files are checked before the first load. Then files are loaded one after the other, one connection and one transaction each: if a load fails, the tables already loaded stay committed.
Parameters:
| Name | Type | Description | Default |
|---|---|---|---|
folder
|
Path
|
folder containing the CSV files (not recursive) |
required |
configuration
|
Configuration
|
coal Configuration with a postgres section |
required |
table_prefix
|
str
|
prefix of the table names |
DEFAULT_TABLE_PREFIX
|
run_id
|
Optional[str]
|
when set (--add-id), the csm_run_id column is filled with it |
None
|
Returns:
| Type | Description |
|---|---|
dict[str, int]
|
The number of rows copied per table |
cosmotech.coal.postgresql.psycopg_utils
¶
PostgreSQL connection utilities (psycopg).
This module resolves the connection settings (from options, the configuration file or the environment) and opens psycopg connections.
psycopg is an optional dependency (custom extra), so it is imported lazily, so that importing this module
never fails where it is not installed.
Functions:
| Name | Description |
|---|---|
resolve_postgres_configuration |
Build the connection configuration from one single source: all the options or the configuration. |
connect |
Connect with the URI built by PostgresUtils and point the search_path to the configured schema. |
resolve_postgres_configuration(host=None, port=None, db=None, schema=None, user=None, password=None, encode_password=True, configuration=EC)
¶
Build the connection configuration from one single source: all the options or the configuration.
- host, db, schema and user all given: they are used, with the password (required in this case).
- none of them given:
configuration.postgresis used (configuration file or environment variables). - only some of them given: error, nothing is completed from the configuration.
Parameters:
| Name | Type | Description | Default |
|---|---|---|---|
host
|
Optional[str]
|
PostgreSQL host |
None
|
port
|
Optional[int]
|
PostgreSQL port (default 5432) |
None
|
db
|
Optional[str]
|
PostgreSQL database name |
None
|
schema
|
Optional[str]
|
PostgreSQL schema |
None
|
user
|
Optional[str]
|
PostgreSQL user name |
None
|
password
|
Optional[str]
|
PostgreSQL password |
None
|
encode_password
|
bool
|
force password encoding to percent encoding |
True
|
configuration
|
Configuration
|
coal Configuration used when no option is given |
ENVIRONMENT_CONFIGURATION
|
Returns:
| Type | Description |
|---|---|
Configuration
|
A Configuration with a postgres section |
Raises:
| Type | Description |
|---|---|
PostgresConnectionOptionError
|
if the settings are partial or incomplete |
connect(configuration, autocommit=False)
¶
Connect with the URI built by PostgresUtils and point the search_path to the configured schema.
Parameters:
| Name | Type | Description | Default |
|---|---|---|---|
configuration
|
Configuration
|
coal Configuration with a postgres section |
required |
autocommit
|
bool
|
psycopg autocommit mode |
False
|
Returns:
| Type | Description |
|---|---|
|
A psycopg connection |
Raises:
| Type | Description |
|---|---|
PostgresConnectionError
|
if the connection fails |
cosmotech.coal.postgresql.runner
¶
PostgreSQL runner operations module.
This module provides functions for interacting with PostgreSQL databases for runner metadata operations.
Functions:
| Name | Description |
|---|---|
send_runner_metadata_to_postgresql |
Send runner metadata to a PostgreSQL database. |
remove_runner_metadata_from_postgresql |
Removes run_id from metadata table that trigger cascade delete on other tables |
send_runner_metadata_to_postgresql(configuration)
¶
Send runner metadata to a PostgreSQL database.
Parameters:
| Name | Type | Description | Default |
|---|---|---|---|
configuration
|
Configuration
|
coal configuration |
required |
organization_id
|
Organization ID |
required | |
workspace_id
|
Workspace ID |
required | |
runner_id
|
Runner ID |
required |
remove_runner_metadata_from_postgresql(configuration)
¶
Removes run_id from metadata table that trigger cascade delete on other tables
Parameters:
| Name | Type | Description | Default |
|---|---|---|---|
configuration
|
Configuration
|
coal configuration |
required |
organization_id
|
Organization ID |
required | |
workspace_id
|
Workspace ID |
required | |
runner_id
|
Runner ID |
required |
cosmotech.coal.postgresql.store
¶
PostgreSQL store operations module.
This module provides functions for interacting with PostgreSQL databases for store operations.
Functions:
| Name | Description |
|---|---|
dump_store_to_postgresql |
Dump Store data to a PostgreSQL database. |
dump_store_to_postgresql_from_conf |
Dump Store data to a PostgreSQL database. |
dump_store_to_postgresql(store_folder, postgres_host, postgres_port, postgres_db, postgres_schema, postgres_user, postgres_password, table_prefix='Cosmotech_', replace=True, force_encode=False, selected_tables=[], fk_id=None)
¶
Dump Store data to a PostgreSQL database.
Parameters:
| Name | Type | Description | Default |
|---|---|---|---|
store_folder
|
str
|
Folder containing the Store |
required |
postgres_host
|
str
|
PostgreSQL host |
required |
postgres_port
|
int
|
PostgreSQL port |
required |
postgres_db
|
str
|
PostgreSQL database name |
required |
postgres_schema
|
str
|
PostgreSQL schema |
required |
postgres_user
|
str
|
PostgreSQL username |
required |
postgres_password
|
str
|
PostgreSQL password |
required |
table_prefix
|
str
|
Table prefix |
'Cosmotech_'
|
replace
|
bool
|
Whether to replace existing tables |
True
|
force_encode
|
bool
|
force password encoding to percent encoding |
False
|
selected_tables
|
list[str]
|
list of tables to send |
[]
|
fk_id
|
str
|
foreign key id to add to all table on all rows |
None
|
dump_store_to_postgresql_from_conf(configuration, replace=True, selected_tables=[], fk_id=None)
¶
Dump Store data to a PostgreSQL database.
Parameters:
| Name | Type | Description | Default |
|---|---|---|---|
configuration
|
Configuration
|
coal Configuration |
required |
replace
|
bool
|
Whether to replace existing tables |
True
|
selected_tables
|
list[str]
|
list of tables to send |
[]
|
fk_id
|
str
|
foreign key id to add to all table on all rows |
None
|
cosmotech.coal.postgresql.utils.PostgresUtils
¶
Methods:
| Name | Description |
|---|---|
get_postgresql_table_schema |
Get the schema of an existing PostgreSQL table using SQL queries. |
send_pyarrow_table_to_postgresql |
|
add_fk_constraint |
|
is_metadata_exists |
|
Attributes:
| Name | Type | Description |
|---|---|---|
table_prefix |
|
|
db_name |
|
|
db_schema |
|
|
host_uri |
|
|
host_port |
|
|
user_name |
|
|
user_password |
|
|
password_encoding |
|
|
full_uri |
str
|
|
metadata_table_name |
str
|
|
table_prefix
property
¶
db_name
property
¶
db_schema
property
¶
host_uri
property
¶
host_port
property
¶
user_name
property
¶
user_password
property
¶
password_encoding
property
¶
full_uri
property
¶
metadata_table_name
property
¶
get_postgresql_table_schema(target_table_name)
¶
Get the schema of an existing PostgreSQL table using SQL queries.
Parameters:
| Name | Type | Description | Default |
|---|---|---|---|
target_table_name
|
str
|
Name of the table |
required |
Returns:
| Type | Description |
|---|---|
Optional[Schema]
|
PyArrow Schema if table exists, None otherwise |
send_pyarrow_table_to_postgresql(data, target_table_name, replace)
¶
add_fk_constraint(from_table, from_col, to_table, to_col)
¶
is_metadata_exists()
¶
cosmotech.coal.postgresql.utils
¶
Functions:
| Name | Description |
|---|---|
adapt_table_to_schema |
Adapt a PyArrow table to match a target schema with detailed logging. |
adapt_table_to_schema(data, target_schema)
¶
Adapt a PyArrow table to match a target schema with detailed logging.