dummy_tables: Create dummy tables in Presto for testing

In RPresto: DBI Connector to Presto

Create dummy tables in Presto for testing

Description

create_primitive_arrays_table() creates a dummy table that has ARRAYs of primitive Presto data types.

create_primitive_maps_table() creates a dummy table that has MAPs of primitive Presto data types.

create_primitive_types_table() creates a dummy table that has primitive Presto data types.

create_primitive_rows_table() creates a dummy table that has all primitive data types included in one ROW type column.

create_array_of_rows_table() creates a dummy table that has an ARRAY(ROW) column that has 2 ROW elements, each containing all 17 supported primitive data types.

create_array_of_maps_table() creates a dummy table that has 17 ARRAY(MAP) columns, each of which has an ARRAY of 2 MAP elements.

Usage

create_primitive_arrays_table(
  con,
  table_name = "presto_primitive_arrays",
  time_zone = "America/New_York",
  verbose = TRUE
)

create_primitive_maps_table(
  con,
  table_name = "presto_primitive_maps",
  time_zone = "America/New_York",
  verbose = TRUE
)

create_primitive_types_table(
  con,
  table_name = "presto_primitive_types",
  time_zone = "America/New_York",
  verbose = TRUE
)

create_primitive_rows_table(
  con,
  table_name = "presto_primitive_rows",
  time_zone = "America/New_York",
  verbose = TRUE
)

create_array_of_rows_table(
  con,
  table_name = "presto_array_of_rows",
  time_zone = "America/New_York",
  verbose = TRUE
)

create_array_of_maps_table(
  con,
  table_name = "presto_array_of_maps",
  time_zone = "America/New_York",
  verbose = TRUE
)

Arguments

con	A valid PrestoConnection object.
table_name	The resulting table name.
time_zone	Time zone string for data types that require a time zone. Default to "America/New_York".
verbose	Boolean indicating whether messages should be printed. Default to TRUE.

Details

We construct the arrays-of-primitive-types table by putting two different values of the same type and a NULL value in an array. In this way, the three values of the same type appear together in the source code and therefore are easier to compare. For integer values, we use the theoretical lower bound (i.e., minimum value) and the theoretical upper bound (i.e., maximum value) as the two values. The field names are taken from the Presto data types they represent.

Here's the complete primitive type values included in the table

Index	Column	Type	ARRAY values
1	boolean	BOOLEAN	[true, false, null]
2	tinyint	TINYINT	[-128, 127, null]
3	smallint	SMALLINT	[-32768, 32767, null]
4	integer	INTEGER	[-2147483647, 2147483647, null]
5	bigint	BIGINT	[-9007199254740991, 9007199254740991, null]
6	real	REAL	[1.0, 2.0, null]
7	double	DOUBLE	[1.0, 2.0, null]
8	decimal	DECIMAL	[-9007199254740991.5, 9007199254740991.5, null]
9	varchar	VARCHAR	['abc', 'def', null]
10	char	CHAR	['a', 'b', null]
11	varbinary	VARBINARY	['abc', 'def', null]
12	date	DATE	['2000-01-01', '2000-01-02', null]
13	time	TIME	['01:02:03.456', '02:03:04.567', null]
14	time_with_tz	TIME WITH TIME ZONE	['01:02:03.456 \<tz\>', '02:03:04.567 \<tz\>', null]
15	timestamp	TIMESTAMP	['2000-01-01 01:02:03.456', '2000-01-02 02:03:04.567', null]
16	timestamp_with_tz	TIMESTAMP WITH TIME ZONE	['2000-01-01 01:02:03.456 \<tz\>', '2000-01-02 02:03:04.567 \<tz\>', null]
17	interval_year_to_month	INTERVAL YEAR TO MONTH	['14' MONTH, '28' MONTH, null]
18	interval_day_to_second	INTERVAL DAY TO SECOND	['2 4:5:6.500' DAY TO SECOND, '3 7:8:9.600' DAY TO SECOND, null]

We construct the maps-of-primitive-types table by first creating a table with ARRAYs of all primitive data types. We then use the MAP() function to create the MAPs from ARRAYs.

We construct the primitive-types table by first creating a table with ARRAYs of all primitive data types. We then use Presto's UNNEST() function to expand the arrays into three separate rows. Each supported Presto data type has three rows in the table so that the resulting R data frame is distinctly different from a simple named list.

We construct the primitive-rows table by first creating a table with all primitive data types. We then use Presto's ⁠CAST(ROW() AS ROW())⁠ function to create the ROW column.

We construct the array-of-rows table by first creating a table with a ROW type column that includes all 17 supported primitive data types. We then use the ARRAY[] function to construct the 2-element ARRAY(ROW) column.

We construct the array-of-maps table by first creating a table a primitive MAP table and then calling the ARRAY[] function to create the ARRAY(MAP) columns.

RPresto documentation built on Nov. 2, 2023, 5:58 p.m.