Tables
- class Tables(session_kwargs, client, return_type='raw')
Methods
delete_projects
(id, project_id)Remove a Table from a project
delete_tags
(id, table_tag_id)Add a tag to a table
get
(id)Show basic table info
get_enhancements_cass_ncoa
(id, source_table_id)get_enhancements_geocodings
(id, source_table_id)list
(*[, database_id, schema, name, search, ...])List tables
list_columns
(id, *[, name, limit, page_num, ...])List columns in the specified table
list_projects
(id, *[, hidden])List the projects a Table belongs to
patch
(id, *[, ontology_mapping, ...])Update a table
post_enhancements_cass_ncoa
(source_table_id, *)post_enhancements_geocodings
(source_table_id)post_refresh
(id)post_scan
(database_id, schema, table_name, *)Creates and enqueues a single table scanner job on a new table
put_projects
(id, project_id)Add a Table to a project
put_tags
(id, table_tag_id)Add a tag to a table
Examples
>>> import civis >>> client = civis.APIClient() >>> client.tables.post_enhancements_geocodings(...)
- delete_projects(id: int, project_id: int)
Remove a Table from a project
- Parameters:
- idint
The ID of the Table.
- project_idint
The ID of the project.
- Returns:
- None
Response code 204: success
- delete_tags(id: int, table_tag_id: int)
Add a tag to a table
- Parameters:
- idint
The ID of the table.
- table_tag_idint
The ID of the tag.
- Returns:
- None
Response code 200: success
- get(id: int)
Show basic table info
- Parameters:
- idint
- 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_enhancements_cass_ncoa(id: int, source_table_id: int)
Warning
Warning: The tables/:source_table_id/enhancements/cass-ncoa/:id endpoint is deprecated and will be removed after January 1, 2021.
View the status of a CASS / NCOA table enhancement
- Parameters:
- idint
The ID of the enhancement.
- source_table_idint
The ID of the table that was enhanced.
- Returns:
civis.response.Response
- idint
The ID of the enhancement.
- source_table_idint
The ID of the table that was enhanced.
- statestr
The state of the enhancement, one of ‘queued’ ‘running’ ‘succeeded’ ‘failed’ or ‘cancelled’.
- enhanced_table_schemastr
The schema name of the table created by the enhancement.
- enhanced_table_namestr
The name of the table created by the enhancement.
- perform_ncoabool
Whether to update addresses for records matching the National Change of Address (NCOA) database.
- ncoa_credential_idint
Credential to use when performing NCOA updates. Required if ‘performNcoa’ is true.
- output_levelstr
The set of fields persisted by a CASS or NCOA enhancement.For CASS enhancements, one of ‘cass’ or ‘all.’For NCOA enhancements, one of ‘cass’, ‘ncoa’ , ‘coalesced’ or ‘all’.By default, all fields will be returned.
- batch_sizeint
The maximum number of records processed at a time. Note that this parameter is not available to all users.
- get_enhancements_geocodings(id: int, source_table_id: int)
Warning
Warning: The tables/:source_table_id/enhancements/geocodings/:id endpoint is deprecated and will be removed after January 1, 2021.
View the status of a geocoding table enhancement
- Parameters:
- idint
The ID of the enhancement.
- source_table_idint
The ID of the table that was enhanced.
- Returns:
civis.response.Response
- idint
The ID of the enhancement.
- source_table_idint
The ID of the table that was enhanced.
- statestr
The state of the enhancement, one of ‘queued’ ‘running’ ‘succeeded’ ‘failed’ or ‘cancelled’.
- enhanced_table_schemastr
The schema name of the table created by the enhancement.
- enhanced_table_namestr
The name of the table created by the enhancement.
- list(*, database_id: int = None, schema: str = None, name: str = None, search: str = None, table_tag_ids: List[int] = None, credential_id: int = None, limit: int = None, page_num: int = None, order: str = None, order_dir: str = None, iterator: bool = None)
List tables
- Parameters:
- database_idint, optional
The ID of the database.
- schemastr, optional
If specified, will be used to filter the tables returned. Substring matching is supported with “%” and “*” wildcards (e.g., “schema=%census%” will return both “client_census.table” and “census_2010.table”).
- namestr, optional
If specified, will be used to filter the tables returned. Substring matching is supported with “%” and “*” wildcards (e.g., “name=%table%” will return both “table1” and “my table”).
- searchstr, optional
If specified, will be used to filter the tables returned. Will search across schema and name (in the full form schema.name) and will return any full name containing the search string.
- table_tag_idsList[int], optional
If specified, will be used to filter the tables returned. Will search across Table Tags and will return any tables that have one of the matching Table Tags.
- credential_idint, optional
If specified, will be used instead of the default credential to filter the tables returned.
- limitint, optional
Number of results to return. Defaults to 50. Maximum allowed is 1000.
- page_numint, optional
Page number of the results to return. Defaults to the first page, 1.
- orderstr, optional
The field on which to order the result set. Defaults to schema. Must be one of: schema, name, search, table_tag_ids, credential_id.
- order_dirstr, optional
Direction in which to sort, either asc (ascending) or desc (descending) defaulting to asc.
- iteratorbool, optional
If True, return a generator to iterate over all responses. Use when more results than the maximum allowed by limit are needed. When True, limit and page_num are ignored. Defaults to False.
- Returns:
civis.response.PaginatedResponse
- 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.
- 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.
- table_tagsList[dict]
The table tags associated with this table.
- idint
Table Tag ID
- namestr
Table Tag Name
- list_columns(id: int, *, name: str = None, limit: int = None, page_num: int = None, order: str = None, order_dir: str = None, iterator: bool = None)
List columns in the specified table
- Parameters:
- idint
- namestr, optional
Search for columns with the given name, within the specified table.
- limitint, optional
Number of results to return. Defaults to its maximum of 50.
- page_numint, optional
Page number of the results to return. Defaults to the first page, 1.
- orderstr, optional
The field on which to order the result set. Defaults to name. Must be one of: name, order.
- order_dirstr, optional
Direction in which to sort, either asc (ascending) or desc (descending) defaulting to asc.
- iteratorbool, optional
If True, return a generator to iterate over all responses. Use when more results than the maximum allowed by limit are needed. When True, limit and page_num are ignored. Defaults to False.
- Returns:
civis.response.PaginatedResponse
- 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.
- list_projects(id: int, *, hidden: bool = None)
List the projects a Table belongs to
- Parameters:
- idint
The ID of the Table.
- hiddenbool, optional
If specified to be true, returns hidden items. Defaults to false, returning non-hidden items.
- Returns:
civis.response.Response
- idint
The ID for this project.
- authordict
- idint
The ID of this user.
- namestr
This user’s name.
- usernamestr
This user’s username.
- initialsstr
This user’s initials.
- onlinebool
Whether this user is online.
- namestr
The name of this project.
- descriptionstr
A description of the project.
- usersList[dict]
Users who can see the project.
- idint
The ID of this user.
- namestr
This user’s name.
- usernamestr
This user’s username.
- initialsstr
This user’s initials.
- onlinebool
Whether this user is online.
auto_share : bool
created_at : str (time)
updated_at : str (time)
- archivedstr
The archival status of the requested item(s).
- patch(id: int, *, ontology_mapping: dict = None, description: str = None, primary_keys: List[str] = None, last_modified_keys: List[str] = None)
Update a table
- Parameters:
- idint
The ID of the table.
- ontology_mappingdict, optional
The ontology-key to column-name mapping. See /ontology for the list of valid ontology keys.
- descriptionstr, optional
The user-defined description of the table.
- primary_keysList[str], optional
A list of column(s) which together uniquely identify a row in the data.These columns must not contain NULL values.
- last_modified_keysList[str], optional
The columns indicating when a row was last modified.
- 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.
- post_enhancements_cass_ncoa(source_table_id: int, *, perform_ncoa: bool = None, ncoa_credential_id: int = None, output_level: str = None, batch_size: int = None)
Warning
Warning: The tables/:source_table_id/enhancements/cass-ncoa endpoint is deprecated and will be removed after January 1, 2021.
Standardize addresses in a table
- Parameters:
- source_table_idint
The ID of the table to be enhanced.
- perform_ncoabool, optional
Whether to update addresses for records matching the National Change of Address (NCOA) database.
- ncoa_credential_idint, optional
Credential to use when performing NCOA updates. Required if ‘performNcoa’ is true.
- output_levelstr, optional
The set of fields persisted by a CASS or NCOA enhancement.For CASS enhancements, one of ‘cass’ or ‘all.’For NCOA enhancements, one of ‘cass’, ‘ncoa’ , ‘coalesced’ or ‘all’.By default, all fields will be returned.
- batch_sizeint, optional
The maximum number of records processed at a time. Note that this parameter is not available to all users.
- Returns:
civis.response.Response
- idint
The ID of the enhancement.
- source_table_idint
The ID of the table that was enhanced.
- statestr
The state of the enhancement, one of ‘queued’ ‘running’ ‘succeeded’ ‘failed’ or ‘cancelled’.
- enhanced_table_schemastr
The schema name of the table created by the enhancement.
- enhanced_table_namestr
The name of the table created by the enhancement.
- perform_ncoabool
Whether to update addresses for records matching the National Change of Address (NCOA) database.
- ncoa_credential_idint
Credential to use when performing NCOA updates. Required if ‘performNcoa’ is true.
- output_levelstr
The set of fields persisted by a CASS or NCOA enhancement.For CASS enhancements, one of ‘cass’ or ‘all.’For NCOA enhancements, one of ‘cass’, ‘ncoa’ , ‘coalesced’ or ‘all’.By default, all fields will be returned.
- batch_sizeint
The maximum number of records processed at a time. Note that this parameter is not available to all users.
- post_enhancements_geocodings(source_table_id: int)
Warning
Warning: The tables/:source_table_id/enhancements/geocodings endpoint is deprecated and will be removed after January 1, 2021.
Geocode a table
- Parameters:
- source_table_idint
The ID of the table to be enhanced.
- Returns:
civis.response.Response
- idint
The ID of the enhancement.
- source_table_idint
The ID of the table that was enhanced.
- statestr
The state of the enhancement, one of ‘queued’ ‘running’ ‘succeeded’ ‘failed’ or ‘cancelled’.
- enhanced_table_schemastr
The schema name of the table created by the enhancement.
- enhanced_table_namestr
The name of the table created by the enhancement.
- post_refresh(id: int)
Warning
Warning: The tables/:id/refresh endpoint is deprecated. Please use tables/scan from now on.
Request a refresh for column and table statistics
- Parameters:
- idint
- 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_scan(database_id: int, schema: str, table_name: str, *, stats_priority: str = None)
Creates and enqueues a single table scanner job on a new table
- Parameters:
- database_idint
The ID of the database.
- schemastr
The name of the schema containing the table.
- table_namestr
The name of the table.
- stats_prioritystr, optional
When to sync table statistics. 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_projects(id: int, project_id: int)
Add a Table to a project
- Parameters:
- idint
The ID of the Table.
- project_idint
The ID of the project.
- Returns:
- None
Response code 204: success
- put_tags(id: int, table_tag_id: int)
Add a tag to a table
- Parameters:
- idint
The ID of the table.
- table_tag_idint
The ID of the tag.
- Returns:
civis.response.Response
- idint
The ID of the table.
- table_tag_idint
The ID of the tag.