Skip to content

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 *.csv file of a folder into its own table (lowercased table_prefix + file stem).

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.postgres is 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.