Databases
- class Databases(session_kwargs, client, return_type='raw')
Examples
>>> import civis >>> client = civis.APIClient() >>> client.databases.list(...)
Methods
get
(id)Show database information
get_schema_privileges
(id, schema_name, *[, ...])Show schema privileges
get_schemas_tables
(id, schema_name, ...[, ...])Show basic table info
get_status_graphs_timeframe
(id, timeframe)Get the status graphs for this database
get_table_privileges_schema_name
(id, ...[, ...])Show table privileges
get_whitelist_ips
(id, whitelisted_ip_id)View details about a whitelisted IP
list
()List databases
Get the advanced settings for this database
list_groups
(id)List groups in the specified database
list_schemas
(id, *[, name, credential_id])List schemas in this database
list_schemas_tables
(id, schema_name, *[, ...])List tables in this schema
list_users
(id, *[, active])Show list of database users
List whitelisted IPs for the specified database
patch_advanced_settings
(id, *[, ...])Update the advanced settings for this database
patch_schemas_tables
(id, schema_name, ...[, ...])Update a table
post_schemas_scan
(id, schema, *[, ...])Creates and enqueues a schema scanner job
put_advanced_settings
(id, export_caching_enabled)Edit the advanced settings for this database
- get(id: int)
Show database information
- Parameters:
- idint
The ID for the database.
- Returns:
civis.response.Response
- idint
The ID for the database.
- namestr
The name of the database.
- adapterstr
The type of the database.
- cluster_identifierstr
The cluster identifier of the database.
- managedbool
True if the database is Civis-managed. False otherwise.
- get_schema_privileges(id: int, schema_name: str, *, credential_id: int = None)
Show schema privileges
- Parameters:
- idint
The ID of the database
- schema_namestr
The name of the schema
- credential_idint, optional
If provided, schemas will be filtered based on the given credential.
- Returns:
civis.response.Response
- granteestr
Name of the granted user or group
- grantee_typestr
User or group
- privilegesList[str]
Privileges that the grantee has on this resource
- grantable_privilegesList[str]
Privileges that the grantee can grant to others for this resource
- get_schemas_tables(id: int, schema_name: str, table_name: str, *, credential_id: int = None)
Show basic table info
- Parameters:
- idint
The ID of the database
- schema_namestr
The name of the schema
- table_namestr
The name of the table
- credential_idint, optional
If provided, schemas will be filtered based on the given credential.
- Returns:
civis.response.Response
- idint
The ID of the table.
- database_idint
The ID of the database.
- schemastr
The name of the schema containing the table.
- namestr
Name of the table.
- descriptionstr
The description of the table, as specified by the table owner
- is_viewbool
True if this table represents a view. False if it represents a regular table.
- row_countint
The number of rows in the table.
- column_countint
The number of columns in the table.
- size_mbfloat (float)
The size of the table in megabytes.
- ownerstr
The database username of the table’s owner.
- distkeystr
The column used as the Amazon Redshift distkey.
- sortkeysstr
The column used as the Amazon Redshift sortkey.
- refresh_statusstr
How up-to-date the table’s statistics on row counts, null counts, distinct counts, and values distributions are. One of: refreshing, stale, or current.
- last_refreshstr (date-time)
The time of the last statistics refresh.
- data_updated_atstr (date-time)
The last time that Civis Platform captured a change in this table.Only applicable for Redshift tables; please see the Civis help desk for more info.
- schema_updated_atstr (date-time)
The last time that Civis Platform captured a change to the table attributes/structure.Only applicable for Redshift tables; please see the Civis help desk for more info.
- refresh_idstr
The ID of the most recent statistics refresh.
- last_rundict
id : int
state : str
- created_atstr (time)
The time that the run was queued.
- started_atstr (time)
The time that the run started.
- finished_atstr (time)
The time that the run completed.
- errorstr
The error message for this run, if present.
- primary_keysList[str]
The primary keys for this table.
- last_modified_keysList[str]
The columns indicating an entry’s modification status for this table.
- table_tagsList[dict]
The table tags associated with this table.
- idint
Table Tag ID
- namestr
Table Tag Name
- ontology_mappingdict
The ontology-key to column-name mapping. See /ontology for the list of valid ontology keys.
- columnsList[dict]
- namestr
Name of the column.
- civis_data_typestr
The generic data type of the column (ex. “string”). Since this is database-agnostic, it may be helpful when loading data to R/Python.
- sql_typestr
The database-specific SQL type of the column (ex. “varchar(30)”).
- sample_valuesList[str]
A sample of values from the column.
- encodingstr
The compression encoding for this columnSee: http://docs.aws.amazon .com/redshift/latest/dg/c_Compression_encodings.html
- descriptionstr
The description of the column, as specified by the table owner
- orderint
Relative position of the column in the table.
- min_valuestr
Smallest value in the column.
- max_valuestr
Largest value in the column.
- avg_valuefloat (float)
This parameter is deprecated.
- stddevfloat (float)
This parameter is deprecated.
- value_distribution_percentdict
A mapping between each value in the column and the percentage of rows with that value.Only present for tables with fewer than approximately 25,000,000 rows and for columns with fewer than twenty distinct values.
- coverage_countint
Number of non-null values in the column.
- null_countint
Number of null values in the column.
- possible_dependent_variable_typesList[str]
Possible dependent variable types the column may be used to model. Null if it may not be used as a dependent variable.
- useable_as_independent_variablebool
Whether the column may be used as an independent variable to train a model.
- useable_as_primary_keybool
Whether the column may be used as an primary key to identify table rows.
- value_distributiondict
An object mapping distinct values in the column to the number of times they appear in the column
- distinct_countint
Number of distinct values in the column. NULL values are counted and treated as a single distinct value.
- joinsList[dict]
id : int
left_table_id : int
left_identifier : str
right_table_id : int
right_identifier : str
on : str
left_join : bool
created_at : str (time)
updated_at : str (time)
multipart_key : List[str]
- enhancementsList[dict]
type : str
created_at : str (time)
updated_at : str (time)
join_id : int
view_def : str
table_def : str
- outgoing_table_matchesList[dict]
- source_table_idint
Source table
- target_typestr
Target type
- target_idint
Target ID
- targetdict
name : str
- jobdict
id : int
name : str
type : str
from_template_id : int
- statestr
Whether the job is idle, queued, running, cancelled, or failed.
created_at : str (date-time)
updated_at : str (date-time)
- runsList[dict]
Information about the most recent runs of the job.
id : int
state : str
- created_atstr (time)
The time that the run was queued.
- started_atstr (time)
The time that the run started.
- finished_atstr (time)
The time that the run completed.
- errorstr
The error message for this run, if present.
- last_rundict
id : int
state : str
- created_atstr (time)
The time that the run was queued.
- started_atstr (time)
The time that the run started.
- finished_atstr (time)
The time that the run completed.
- errorstr
The error message for this run, if present.
- hiddenbool
The hidden status of the item.
- match_optionsdict
max_matches : int
threshold : str
- get_status_graphs_timeframe(id: int, timeframe: str)
Get the status graphs for this database
- Parameters:
- idint
The ID of the database.
- timeframestr
The span of time that the graphs cover. Must be one of 1_hour, 4_hours, 1_day, 2_days, 1_week.
- Returns:
civis.response.Response
- cpu_graph_urlstr
URL for the aws redshift cpu utliization graph.
- disk_graph_urlstr
URL for the aws redshift disk usage graph.
- queue_length_graph_urlstr
URL for the aws redshift queue length graph.
- status_graph_urlstr
URL for the aws redshift status graph.
- maintenance_graph_urlstr
URL for the aws redshift maintenance graph.
- query_duration_graph_urlstr
URL for the aws redshift table count graph.
- get_table_privileges_schema_name(id: int, schema_name: str, table_name: str, *, credential_id: int = None)
Show table privileges
- Parameters:
- idint
The ID of the database
- schema_namestr
The name of the schema
- table_namestr
The name of the table
- credential_idint, optional
If provided, schemas will be filtered based on the given credential.
- Returns:
civis.response.Response
- granteestr
Name of the granted user or group
- grantee_typestr
User or group
- privilegesList[str]
Privileges that the grantee has on this resource
- grantable_privilegesList[str]
Privileges that the grantee can grant to others for this resource
- get_whitelist_ips(id: int, whitelisted_ip_id: int)
View details about a whitelisted IP
- Parameters:
- idint
The ID of the database this rule is applied to.
- whitelisted_ip_idint
The ID of this whitelisted IP address.
- Returns:
civis.response.Response
- idint
The ID of this whitelisted IP address.
- remote_host_idint
The ID of the database this rule is applied to.
- security_group_idstr
The ID of the security group this rule is applied to.
- subnet_maskstr
The subnet mask that is allowed by this rule.
- authorized_bystr
The user who authorized this rule.
- is_activebool
True if the rule is applied, false if it has been revoked.
- created_atstr (time)
The time this rule was created.
- updated_atstr (time)
The time this rule was last updated.
- list()
List databases
- Returns:
civis.response.Response
- idint
The ID for the database.
- namestr
The name of the database.
- adapterstr
The type of the database.
- cluster_identifierstr
The cluster identifier of the database.
- managedbool
True if the database is Civis-managed. False otherwise.
- list_advanced_settings(id: int)
Get the advanced settings for this database
- Parameters:
- idint
The ID of the database this advanced settings object belongs to.
- Returns:
civis.response.Response
- export_caching_enabledbool
Whether or not caching is enabled for export jobs run on this database server.
- list_groups(id: int)
List groups in the specified database
- Parameters:
- idint
The ID for the database.
- Returns:
civis.response.Response
- group_namestr
The name of the group.
- membersList[str]
The members of the group.
- list_schemas(id: int, *, name: str = None, credential_id: int = None)
List schemas in this database
- Parameters:
- idint
The ID of the database.
- namestr, optional
If specified, will be used to filter the schemas returned.Substring matching is supported (e.g., “name=schema” will return both “schema1” and “schema2”). Does not apply to BigQuery databases.
- credential_idint, optional
If provided, schemas will be filtered based on the given credential.
- Returns:
civis.response.Response
- schemastr
The name of a schema.
- list_schemas_tables(id: int, schema_name: str, *, credential_id: int = None)
List tables in this schema
- Parameters:
- idint
The ID of the database
- schema_namestr
The name of the schema
- credential_idint, optional
If provided, schemas will be filtered based on the given credential.
- Returns:
civis.response.Response
- namestr
The name of the table.
- schemastr
The name of the schema containing the table.
- is_viewbool
True if this table represents a view. False if it represents a regular table.
- database_idint
The ID of the database server.
- list_users(id: int, *, active: bool = None)
Show list of database users
- Parameters:
- idint
The ID of the database.
- activebool, optional
If true returns active users. If false returns deactivated users. If omitted returns all users.
- Returns:
civis.response.Response
- usernamestr
Username
- activebool
Whether the user is active or deactivated
- list_whitelist_ips(id: int)
List whitelisted IPs for the specified database
- Parameters:
- idint
The ID for the database.
- Returns:
civis.response.Response
- idint
The ID of this whitelisted IP address.
- remote_host_idint
The ID of the database this rule is applied to.
- security_group_idstr
The ID of the security group this rule is applied to.
- subnet_maskstr
The subnet mask that is allowed by this rule.
- created_atstr (time)
The time this rule was created.
- updated_atstr (time)
The time this rule was last updated.
- patch_advanced_settings(id: int, *, export_caching_enabled: bool = None)
Update the advanced settings for this database
- Parameters:
- idint
The ID of the database this advanced settings object belongs to.
- export_caching_enabledbool, optional
Whether or not caching is enabled for export jobs run on this database server.
- Returns:
civis.response.Response
- export_caching_enabledbool
Whether or not caching is enabled for export jobs run on this database server.
- patch_schemas_tables(id: int, schema_name: str, table_name: str, *, credential_id: int = None, description: str = None)
Update a table
- Parameters:
- idint
The ID of the database
- schema_namestr
The name of the schema
- table_namestr
The name of the table
- credential_idint, optional
If provided, schemas will be filtered based on the given credential.
- descriptionstr, optional
The user-defined description of the table.
- Returns:
civis.response.Response
- idint
The ID of the table.
- database_idint
The ID of the database.
- schemastr
The name of the schema containing the table.
- namestr
Name of the table.
- descriptionstr
The description of the table, as specified by the table owner
- is_viewbool
True if this table represents a view. False if it represents a regular table.
- row_countint
The number of rows in the table.
- column_countint
The number of columns in the table.
- size_mbfloat (float)
The size of the table in megabytes.
- ownerstr
The database username of the table’s owner.
- distkeystr
The column used as the Amazon Redshift distkey.
- sortkeysstr
The column used as the Amazon Redshift sortkey.
- refresh_statusstr
How up-to-date the table’s statistics on row counts, null counts, distinct counts, and values distributions are. One of: refreshing, stale, or current.
- last_refreshstr (date-time)
The time of the last statistics refresh.
- data_updated_atstr (date-time)
The last time that Civis Platform captured a change in this table.Only applicable for Redshift tables; please see the Civis help desk for more info.
- schema_updated_atstr (date-time)
The last time that Civis Platform captured a change to the table attributes/structure.Only applicable for Redshift tables; please see the Civis help desk for more info.
- refresh_idstr
The ID of the most recent statistics refresh.
- last_rundict
id : int
state : str
- created_atstr (time)
The time that the run was queued.
- started_atstr (time)
The time that the run started.
- finished_atstr (time)
The time that the run completed.
- errorstr
The error message for this run, if present.
- primary_keysList[str]
The primary keys for this table.
- last_modified_keysList[str]
The columns indicating an entry’s modification status for this table.
- table_tagsList[dict]
The table tags associated with this table.
- idint
Table Tag ID
- namestr
Table Tag Name
- ontology_mappingdict
The ontology-key to column-name mapping. See /ontology for the list of valid ontology keys.
- columnsList[dict]
- namestr
Name of the column.
- civis_data_typestr
The generic data type of the column (ex. “string”). Since this is database-agnostic, it may be helpful when loading data to R/Python.
- sql_typestr
The database-specific SQL type of the column (ex. “varchar(30)”).
- sample_valuesList[str]
A sample of values from the column.
- encodingstr
The compression encoding for this columnSee: http://docs.aws.amazon .com/redshift/latest/dg/c_Compression_encodings.html
- descriptionstr
The description of the column, as specified by the table owner
- orderint
Relative position of the column in the table.
- min_valuestr
Smallest value in the column.
- max_valuestr
Largest value in the column.
- avg_valuefloat (float)
This parameter is deprecated.
- stddevfloat (float)
This parameter is deprecated.
- value_distribution_percentdict
A mapping between each value in the column and the percentage of rows with that value.Only present for tables with fewer than approximately 25,000,000 rows and for columns with fewer than twenty distinct values.
- coverage_countint
Number of non-null values in the column.
- null_countint
Number of null values in the column.
- possible_dependent_variable_typesList[str]
Possible dependent variable types the column may be used to model. Null if it may not be used as a dependent variable.
- useable_as_independent_variablebool
Whether the column may be used as an independent variable to train a model.
- useable_as_primary_keybool
Whether the column may be used as an primary key to identify table rows.
- value_distributiondict
An object mapping distinct values in the column to the number of times they appear in the column
- distinct_countint
Number of distinct values in the column. NULL values are counted and treated as a single distinct value.
- joinsList[dict]
id : int
left_table_id : int
left_identifier : str
right_table_id : int
right_identifier : str
on : str
left_join : bool
created_at : str (time)
updated_at : str (time)
multipart_key : List[str]
- enhancementsList[dict]
type : str
created_at : str (time)
updated_at : str (time)
join_id : int
view_def : str
table_def : str
- outgoing_table_matchesList[dict]
- source_table_idint
Source table
- target_typestr
Target type
- target_idint
Target ID
- targetdict
name : str
- jobdict
id : int
name : str
type : str
from_template_id : int
- statestr
Whether the job is idle, queued, running, cancelled, or failed.
created_at : str (date-time)
updated_at : str (date-time)
- runsList[dict]
Information about the most recent runs of the job.
id : int
state : str
- created_atstr (time)
The time that the run was queued.
- started_atstr (time)
The time that the run started.
- finished_atstr (time)
The time that the run completed.
- errorstr
The error message for this run, if present.
- last_rundict
id : int
state : str
- created_atstr (time)
The time that the run was queued.
- started_atstr (time)
The time that the run started.
- finished_atstr (time)
The time that the run completed.
- errorstr
The error message for this run, if present.
- hiddenbool
The hidden status of the item.
- match_optionsdict
max_matches : int
threshold : str
- post_schemas_scan(id: int, schema: str, *, stats_priority: str = None)
Creates and enqueues a schema scanner job
- Parameters:
- idint
The ID of the database.
- schemastr
The name of the schema.
- stats_prioritystr, optional
When to sync table statistics for every table in the schema. Valid options are the following. Option: ‘flag’ means to flag stats for the next scheduled run of a full table scan on the database. Option: ‘block’ means to block this job on stats syncing. Option: ‘queue’ means to queue a separate job for syncing stats and do not block this job on the queued job. Defaults to ‘flag’
- Returns:
civis.response.Response
- job_idint
The ID of the job created.
- run_idint
The ID of the run created.
- put_advanced_settings(id: int, export_caching_enabled: bool)
Edit the advanced settings for this database
- Parameters:
- idint
The ID of the database this advanced settings object belongs to.
- export_caching_enabledbool
Whether or not caching is enabled for export jobs run on this database server.
- Returns:
civis.response.Response
- export_caching_enabledbool
Whether or not caching is enabled for export jobs run on this database server.