dbWriteTableEm: Creates PostgreSQL Partitioned Table/Indexes and Partitions

In AndreMikulec/econModel: Meaningful Social and Economic Data from ALFRED and elsewhere

Description

Creates a "partitioned table/index" (of "LIST" partitions) and its
"table/index partitions"

Basically, this is the method (from "much of" the URL reference):

create table a(c1 int, c2 int, c3 int, c4 int) partition by list(c1);
create index a_c1c2c3c on only a(c1,c2,c3);
-- Needed to be able to do: UPDATE a . . . ON CONFICT a_pk DO UPDATE . . .
alter table only a add constraint a_pk primary key(c1, c2);

create table     a_1(like a including defaults);
create index     a_1_c1c2c3c on a_1(c1,c2,c3);
alter table only a_1 add constraint a_1_pk    primary key(c1, c2);
alter table only a_1 add constraint a_1_check check(c1 = 1);

DBI::dbWriteTable (to load data or any other data loading method)

alter table a         attach partition a_1 for values in (1);
alter index a_c1c2c3c attach partition a_1_c1c2c3c;

Note, the following is needed for an efficient query plan:

SET constraint_exclusion TO partition;
SET enable_partition_pruning TO on;

-- SQL statements follow: "where c1 = 1"
-- "UPDATE a . . . ON CONFICT a_pk DO UPDATE" statements follow

Usage

dbWriteTableEM(
  connName,
  name = substitute(value),
  value,
  partition.of = character(),
  part.key.col = character(),
  primary.key = character(),
  indexes = list(),
  lower.df.name = TRUE,
  lower.col.names = TRUE,
  dots.to.underscores = TRUE,
  env,
  display = TRUE,
  exec = TRUE,
  ...
)

Arguments

connName	String. Default is "connEM". Contains the name of the variable that contains the name of the "connection" in the environment "env".
name	String. Default is substitute(value). The name of the table to which the data frame is to be loaded.
value,	data.frame. Required. To be loaded to the database.
partition.of	String. If the table is (or to be) a participant as a Partition, then this table is a partition of the this partitioned table "partition.of".
part.key.col	String. If the table is (or to be is) a Partitioned table is of that List partition and the partition key column.
primary.key	Vector of Strings. If present, then of the [to be] primary-keyed table, this is the the vector of values (in order) that are [to be] the primary-keyed columns.
indexes	List of "Vector of Strings". If present, then of the [to be] indexed table, this is the the vector of values (in order) that are [to be] the indexed columns. The name of the index is taken from the name of the vector.
lower.df.name	Logical. Default is TRUE. Make the target database table name to be in lowercase.
lower.col.names	Logical. Default is TRUE. Make the target database table column names to be in lowercase.
dots.to.underscores	Logical. Default is TRUE. Make the target database table column names internal "dots" be converted to underscores(_).
env	Environment. Default is the .Global environment. This is the environment to return the connection object "connEM".
display	Logical. Whether to display the query (defaults to TRUE).
exec	Logical. Whether to execute the query (defaults to TRUE).'
...	Dots. Other parameters passed to R CRAN package DBI function dbWriteTable.

References

5.11. Table Partitioning https://www.postgresql.org/docs/13/ddl-partitioning.html

Examples

## Not run: 


mtcars2 <- mtcars
mtcars2[["model"]] <- rownames(mtcars2)
mtcars2 <- DataCombine::MoveFront(mtcars2, Var = "model")
mtcars2[["vs"]] <- as.logical(mtcars2[["vs"]])
mtcars2[["gear"]] <- as.integer(mtcars2[["gear"]])
mtcars2[["carb"]] <- zoo::as.Date(mtcars2[["carb"]])
rownames(mtcars2) <- NULL
#
# Creates the table (with zero rows).
# Appends data (with the value(data.frame) having the same columns
# on the server).
mtcars2s <- mtcars2[1:5,]

dbWriteTableEM(name = "mtcars",  value = mtcars2s,
  part.key.col = "gear", primary.key = c("gear", "model"),
  indexes = list(gear_model_vs = c("gear", "model", "vs"))
)

# Appends data (with the value(data.frame) having less columns
# than that of the server database).
# Those server columns, that are not found in the value(data.frame),
# are added to the value(data.frame).
mtcars2lDf <- mtcars2[6:10, "model", drop = F]

dbWriteTableEM(name = "mtcars",  value = mtcars2lDf,
  part.key.col = "gear", primary.key = c("gear", "model"),
  indexes = list(gear_model_vs = c("gear", "model", "vs"))
)

# Appends data (with the server database having less columns
# than that of the value(data.frame)).
# Those value(data.frame) columns, that are not found in the server,
# are added to the sever.
mtcars2lSv <- {DfNew <- mtcars2[11:15, c("model","vs", "am", "gear", "carb")]
               colnames(DfNew) <- paste0(colnames(DfNew),"_new")
               DfNew[["model"]] <- DfNew[["model_new"]]
               DfNew <- DataCombine::MoveFront(DfNew, Var = "model")
               DfNew
              }; rm(DfNew)

dbWriteTableEM(name = "mtcars",  value = mtcars2lSv,
  part.key.col = "gear", primary.key = c("gear", "model"),
  indexes = list(gear_model_vs = c("gear", "model", "vs"))
)


# to query
dbExecuteEM(Statement = "SET constraint_exclusion TO partition;")
dbExecuteEM(Statement = "SET enable_partition_pruning TO on;")


## End(Not run)

AndreMikulec/econModel documentation built on June 30, 2021, 9:48 a.m.