add_index: Add an index to a SQL table

In PHSKC-APDE/apde: Common functions used in APDE's work

Add an index to a SQL table

Description

add_index adds an index to a SQL table using specified variables or a YAML config file.

Usage

add_index(
  conn,
  server = NULL,
  config = NULL,
  config_url = NULL,
  config_file = NULL,
  to_schema = NULL,
  to_table = NULL,
  index_type = NULL,
  index_name = NULL,
  index_vars = NULL,
  drop_index = T,
  test_schema = NULL
)

Arguments

conn	SQL server connection created using odbc package.
server	Name of server being used (only applies if using a YAML file). Useful if the same table is loaded to multiple servers but with different names or schema.
config	Name of object in global environment that contains configuration information. Use one of config, config_url, or config_file. Should be in a YAML format with at least the following variables: to_schema, to_table, and index_name, with possibly index_type and index_vars variables. to_schema and to_table should be nested under the server name if applicable, other variables should not. If index_type is not specified and index_vars fields are named, the assumption is a clustered (CL) index.
config_url	URL of a YAML config file. Use one of config, config_url, or config_file. Note the requirements under config.
config_file	File path of a YAML config file. Use one of config, config_url, or config_file. Note the requirements under config.
to_schema	Name of the schema to apply the index to (if not using YAML input).
to_table	Name of the table to apply the index to (if not using YAML input).
index_type	Which index type will be used, either 'ccs' or 'cl' (if not using YAML input).
index_name	Name of the index to be added (if not using YAML input).
index_vars	Vector of variables to index on if using a clustered ('cl') index (if not using YAML input). Should take the format c("a", "b", "c").
drop_index	Remove any existing clustered or clustered column store indices. Default is TRUE.
test_schema	Add index to a temporary/development schema when testing out table creation. Will use the to_schema (specified or in the YAML file) to make a new table name of {to_schema}_{to_table}. Schema must already exist in the database. Most useful when the user has an existing YAML file and does not want to overwrite it. Default is NULL.

Details

This function adds a clustered column store (CCS) or clustered (CL) index to a SQL table using specified variables or a YAML configuration file. Users can specify some input functions (e.g., to_table) and rely on the config file for the rest of the necessary information. For all arguments that could be specified or come from a YAML file, the hierarchy is specified > argument under server in YAML > argument not under server in YAML.

Example YAML file with no server and a CCS index

(Assume the indentation is appropriate)

to_schema: raw
to_table: mcaid_elig
*optional other components like a qa_schema and qa_table, variables, etc.*
index_type: ccs
index_name: idx_ccs_raw_mcaid_elig

Example YAML file with servers (phclaims, hhsaw) and clustered index

(Assume the indentation is appropriate)

phclaims:
    to_schema: raw
    to_table: mcaid_elig
hhsaw:
    to_schema: raw
    to_table: mcaid_elig
*optional other components like a qa_schema and qa_table, variables, etc.*
index_type: cl
index_name: idx_raw_mcaid_elig_id_date
index_vars: 
    - id_apde
    - start_date

Examples

## Not run: 
add_index(conn = db_claims, server = "hhsaw", config = load_config)

## End(Not run)

PHSKC-APDE/apde documentation built on April 14, 2025, 10:46 a.m.