tsql_validate_field_types: Validates whether a named vector of TSQL data types is...

In PHSKC-APDE/rads: Assisted computation of King County public health data

Validates whether a named vector of TSQL data types is compatible with a data.table

Description

tsql_validate_field_types checks whether a named vector of TSQL data types is compatible with a given data.table that you wish to upload to Microsoft SQL Server. The function does not cover every possible situation! For example, you might want to push your R 'POSIXct' column to a SQL Server table as an 'nvarchar()' datatype, but this function will expect you to map it to a more typical data type such as 'datetime'. Think of this function as a second set of eyes to make sure you didn't do something careless.

Usage

tsql_validate_field_types(ph.data = NULL, field_types = NULL)

Arguments

ph.data	The name of a single data.table/data.frame to be loaded to SQL Server.
field_types	A named character vector with the desired TSQL datatypes for your upload. For example, c(col1 = 'int', col2 = 'float', col3 = 'date'). Note that the names in field_types must be the same as the names in ph.data. This is often read into memory from a *.yaml file, but can also be manually created.

Details

Note that this function may not thoroughly evaluate if the allocated length for character strings, i.e., nvarchar() and varchar(), is sufficient.

Note that this replaces rads::validate_yaml_data. To use this function to check the compatibility of field types in a yaml file with your dataset, do the following:

1. load the yaml file, e.g., yaml <- yaml::read_yaml("X:/code/myyaml.yaml")

2. unlist the variable descriptions, e.g., yaml_field_types = unlist(yaml$vars)

3. use tsql_validate_field_types, e.g., tsql_validate_field_types(ph.data = mydt, field_types = yaml_field_types)

Examples

# example of a success
 library(data.table)
 mydt = data.table(col1 = 1:10000L,  # creates integers
                   col2 = 1:10000/3) # creates floats
 mydt[, col3 := as.Date(Sys.Date()) - col1] # creates dates
 mydt[, col4 := as.character(col3)] # create strings
 mydt[, col5 := Sys.time()] # create POSIXct

 myfieldtypes <- c(col1 = 'int',
                   col2 = 'float',
                   col3 = 'date',
                   col4 = 'nvarchar(255)',
                   col5 = 'datetime')

 tsql_validate_field_types(ph.data = mydt, field_types = myfieldtypes)

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