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

list_advanced_settings(id)

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_whitelist_ips(id)

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.