Package: rquery 1.4.99

John Mount

rquery: Relational Query Generator for Data Manipulation at Scale

A piped query generator based on Edgar F. Codd's relational algebra, and on production experience using 'SQL' and 'dplyr' at big data scale. The design represents an attempt to make 'SQL' more teachable by denoting composition by a sequential pipeline notation instead of nested queries or functions. The implementation delivers reliable high performance data processing on large data systems such as 'Spark', databases, and 'data.table'. Package features include: data processing trees or pipelines as observable objects (able to report both columns produced and columns used), optimized 'SQL' generation as an explicit user visible table modeling step, plus explicit query reasoning and checking.

Authors:John Mount [aut, cre], Win-Vector LLC [cph]

rquery_1.4.99.tar.gz
rquery_1.4.99.zip(r-4.7)rquery_1.4.99.zip(r-4.6)rquery_1.4.99.zip(r-4.5)
rquery_1.4.99.tgz(r-4.6-any)rquery_1.4.99.tgz(r-4.5-any)
rquery_1.4.99.tar.gz(r-4.7-any)rquery_1.4.99.tar.gz(r-4.6-any)
rquery_1.4.99.tgz(r-4.6-emscripten)
manual.pdf |manual.html✨
DESCRIPTION |NEWS
card.svg |card.png
rquery/json (API)

# Install 'rquery' in R:
install.packages('rquery', repos = c('https://winvector.r-universe.dev', 'https://cloud.r-project.org'))

Bug tracker:https://github.com/winvector/rquery/issues

Pkgdown/docs site:https://winvector.github.io

On CRAN:

Conda:

9.60 score 110 stars 3 packages 128 scripts 3.9k downloads 1 mentions 113 exports 1 dependencies

Last updated from:ce20c07536. Checks:7 NOTE, 2 OK. Indexed: yes.

TargetResultTimeFilesSyslog
linux-devel-x86_64NOTE131
source / vignettesOK220
linux-release-x86_64NOTE130
macos-release-arm64NOTE90
macos-oldrel-arm64NOTE94
windows-develNOTE105
windows-releaseNOTE93
windows-oldrelNOTE117
wasm-releaseOK102

Exports:actualize_join_planaffine_transformaggregate_nseaggregate_seapply_right_S4arrowassign_slicebuild_join_plancolumn_namescolumns_usedcommencifycomplete_designconvert_yaml_to_pipelinecount_null_colsdb_tddbi_tabledescribe_tablesdrop_columnsexexample_employee_dateexecuteexpand_gridextendextend_nseextend_seformat_nodegetDBOptiongraph_join_planif_else_blockif_else_opinspect_join_plankey_inspector_all_colskey_inspector_postgresqlkey_inspector_sqlitelocal_tdlookup_by_columnmake_assignmentsmap_column_valuesmark_null_colsmaterializematerialize_nodematerialize_sqlmk_tdnatural_joinnon_sql_nodenormalize_colsnull_replaceop_diagramorder_exprorder_expr_nseorder_expr_seorder_rowsorderbypick_top_kpre_sql_fnpre_sql_identifierpre_sql_stringpre_sql_sub_exprpre_sql_to_querypre_sql_tokenprojectproject_nseproject_sequantile_colsquantile_nodequote_identifierquote_literalquote_stringquote_table_namerename_columnsrlookrow_countsrq_colnamesrq_coltypesrq_connection_advicerq_connection_namerq_connection_testsrq_copy_torq_executerq_function_mappingsrq_get_queryrq_headrq_nrowrq_remove_tablerq_table_existsrquery_apply_to_data_framerquery_db_inforquery_default_db_inforquery_default_methodsrstrrsummaryrsummary_nodeselect_columnsselect_rowsselect_rows_nseselect_rows_seset_indicatorsetDBOptsetDBOptionsql_expr_setsql_nodestr_pre_sql_sub_exprtable_sourcetables_usedtheta_jointheta_join_nsetheta_join_seto_sqlto_transport_representationtokenize_for_SQLtopo_sort_tablesunionallwrap

Dependencies:wrapr

Parameterized rquery
Rendered fromParameterized_rquery.Rmdusingknitr::rmarkdown

Last update: 2023-08-20
Started: 2018-11-08

Query Generation
Rendered fromQueryGeneration.Rmdusingknitr::rmarkdown

Last update: 2022-01-22
Started: 2017-12-24

Assignment Partitioner
Rendered fromAssigmentPartitioner.Rmdusingknitr::rmarkdown

Last update: 2020-08-11
Started: 2018-01-03

Pipeable SQL
Introduction | sql_node() | Composing nodes | A non-trivial example | Working with sets of columns | SQL first | Ad-hoc mode | Table descriptions | Helper functions and notation | Conclusion | See also | Appendix: Always clean up on the way out
Rendered fromPipeableSQL.Rmdusingknitr::rmarkdown

Last update: 2020-08-11
Started: 2018-06-12

rquery Introduction
Introduction | Table descriptions | Operators | Non-SQL nodes | Standard interfaces | Assignment partitioning | Performance | Conclusion | See also | Appendix: Always clean up on the way out
Rendered fromrquery_intro.Rmdusingknitr::rmarkdown

Last update: 2020-08-11
Started: 2018-03-09

rquery Many Columns
Rendered fromrquery_many_columns.Rmdusingknitr::rmarkdown

Last update: 2019-12-16
Started: 2019-12-13

R mapping
Rendered fromR_mapping.Rmdusingknitr::rmarkdown

Last update: 2019-06-25
Started: 2019-06-13

SQL quoting
Rendered fromsql_quoting.Rmdusingknitr::rmarkdown

Last update: 2018-11-20
Started: 2018-11-19

Readme and manuals

Help Manual

Help pageTopics
'rquery': Relational Query Generator for Data Manipulationrquery-package rquery
Execute an ordered sequence of left joins.actualize_join_plan
Implement an affine transformatonaffine_transform
Apply pipeline to a database.apply_right_S4,ANY,rquery_db_info-method
S4 dispatch method for apply_right.apply_right_S4,data.frame,relop_arrow-method
S4 dispatch method for apply_right.apply_right_S4,relop_arrow,relop_arrow-method
Execute pipeline treating pipe_left_arg as local data to be copied into database.apply_right.relop
Data arrowarrow
Assign a value to a slice of data (set of rows meeting a condition, and specified set of columns).assign_slice
Build a join plan.build_join_plan
Return column namescolumn_names
Return columns usedcolumns_used
Hyderdrive (science fiction show) synonym for 'execute'commencify
Complete an experimental design.complete_design
Convert a series of simple objects (from YAML deserializaton) to an rquery pipeline.convert_yaml_to_pipeline
Count NULLs per row for given column set.count_null_cols
Construct a table description from a database source.dbi_table db_td
Build a nice description of a table.describe_tables
Make a drop columns node (not a relational operation).drop_columns
Execute a wrapped execution pipeline.ex
Execute an operator tree, bringing back the result to memory.execute
Cross product vectors in database.expand_grid
Extend data by adding more columns.extend extend_nse
Extend data by adding more columns.extend_se
Format a single node for printing.format_node
Get a database connection option.getDBOption
Build a draw-able specification of the join diagramgraph_join_plan
Build a sequence of statements simulating an if/else block-'if(){}else{}'.if_else_block
Build a 'relop' node simulating a per-row block-'if(){}else{}'.if_else_op
check that a join plan is consistent with table descriptions.inspect_join_plan
Return all columns as guess of preferred primary keys.key_inspector_all_cols
Return all primary key columns as guess at preferred primary keys for a PostgreSQL handle.key_inspector_postgresql
Return all primary key columns as guess at preferred primary keys for a SQLite handle.key_inspector_sqlite
Construct a table description of a local data.frame.local_td
Use one column to pick values from other columns.lookup_by_column
Make a list of assignments, applying many functions to many columns.make_assignments
Remap values in a set of columns.map_column_values
Indicate NULLs per row for given column set.mark_null_cols
Materialize an optree as a table.materialize
Create a materialize node.materialize_node
Make a table description directly.mk_td table_source
Make a natural_join node.natural_join
Wrap a non-SQL node.non_sql_node
Build an optree pipeline that normalizes a set of columns so each column sums to one in each partition.normalize_cols
Create a null_replace node.null_replace
Build a diagram of a optree pipeline.op_diagram
Make a order_expr node.order_expr order_expr_nse
Make a order_expr node.order_expr_se
Make an orderby node (not a relational operation).order_rows
Make an orderby node (not a relational operation).orderby
Build an optree pipeline that selects up to the top k rows from each group in the given order.pick_top_k
pre_sql_sub_exprpre_sql_sub_expr
project data by grouping, and adding aggregate columns.aggregate_nse project project_nse
project data by grouping, and adding aggregate columns.aggregate_se project_se
Compute quantiles of specified columns (without interpolation, needs a database with window functions).quantile_cols
Compute quantiles over non-NULL values (without interpolation, needs a database with window functions).quantile_node
Quote an identifier.quote_identifier
Quote a valuequote_literal
Quote a stringquote_string
Quote a table name.quote_table_name
Make a rename columns node (copies columns not renamed).rename_columns
Build an optree pipeline counts rows.row_counts
List table column names.rq_colnames
Get column types by example values as a data.frame.rq_coltypes
Get advice for a DB connection (beyond tests).rq_connection_advice
Build a canonical name for a db connection class.rq_connection_name
Try and test database for some option settings.rq_connection_tests
Copy local R table to remote data handle.rq_copy_to
Execute a query, typically an update that is not supposed to return results.rq_execute
Execute a get query, typically a non-update that is supposed to return results.rq_get_query
Get head of db tablerq_head
Count rows and return as numericrq_nrow
Remove tablerq_remove_table
Check if a table exists.rq_table_exists
Execute optree in an environment where d is the only data.rquery_apply_to_data_frame
Build a db information stand-inrquery_db_info
An example 'rquery_db_info' object useful for formatting 'SQL' without a database connection.rquery_default_db_info
Quick look at remote datarlook rstr
Compute usable summary of columns of remote table.rsummary
Create an rsumary relop operator node.rsummary_node
Make a select columns node (not a relational operation).select_columns
Make a select rows node.select_rows select_rows_nse
Make a select rows node.select_rows_se
Make a set indicator node.set_indicator
Set a database connection option.setDBOpt
Set a database connection option.setDBOption
Build a query that applies a SQL expression to a set of columns.sql_expr_set
Make a general SQL node.sql_node
Return vector of table names used.tables_used
Make a theta_join node.theta_join theta_join_nse
Make a theta_join node.theta_join_se
Return SQL implementation of operation tree.to_sql
Convert an rquery op diagram to a simple representation, appropriate for conversion to YAML.to_transport_representation
Topologically sort join plan so values are available before uses.topo_sort_tables
Make an unionall node (not a relational operation).unionall
Wrap a data frame for later execution.wrap