PDF《Session 4:》

Copyright ? 2018, Oracle and/or its affiliates.

All rights reserved. | Session 4: Oracle R Enterprise 1.5.1 Embedded R Execution C SQL Interface Mark Hornick Director, Advanced Analytics and Machine Learning [email protected] October

2018 Oracle R Technologies Copyright ?

2018 Oracle and/or its affiliates. All rights reserved. | Safe Harbor Statement The following is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracle's products remains at the sole discretion of Oracle. Copyright ?

2018 Oracle and/or its affiliates. All rights reserved. | Topics ? Introduction to Embedded R Execution: What and Why? ? Embedded R Scripts C Execution through the SQL interface ? ORE-defined graphics function examples ? Returning R statistical results as a database table ? Summary Copyright ?

2018 Oracle and/or its affiliates. All rights reserved. | Embedded R Execution ? Execute R code on the database server machine ? Have Oracle Database control and manage spawning of R engines ? Eliminate loading data to user's client R engine and result write-back to Oracle Database ? Execute user-defined R functions using data- and task-parallelism ? Invoke R from SQL and return results in Oracle tables ? Use open source CRAN packages at the database server ? Store and manage user-defined R functions in the database ? Schedule user-defined R functions for automatic execution Copyright ?

2018 Oracle and/or its affiliates. All rights reserved. | Motivation C why embedded R execution? ? Facilitate application use of R script results C Develop/test user-defined R functions interactively with R interface C Invoke user-defined R functions directly from SQL for production applications C User-defined R functions C scripts C stored in Oracle Database ? Improved performance and throughput C Oracle Database-enabled data- and task-parallelism C Memory and compute resources of database server, e.g., Exadata C More efficient read/write of data between Oracle Database and R Engine C Parallel simulations ? Image/plot generation at database server ? Rich XML for structured and image (PNG) data Copyright ?

2018 Oracle and/or its affiliates. All rights reserved. | Embedded R Scripts C SQL Interface Copyright ?

2018 Oracle and/or its affiliates. All rights reserved. | Embedded Script Execution C SQL Interface SQL Interface function Purpose rqEval() Invoke stand-alone R script rqTableEval() Invoke R script with full table as input rqRowEval() Invoke R script on one row at a time, or multiple rows in chunks "rqGroupEval()" Invoke R script on data partitioned by grouping column sys.rqScriptCreate Create named R script sys.rqScriptDrop Drop named R script rqGrant Grant access to an R script rqRevoke Revoke access to an R script Copyright ?

2018 Oracle and/or its affiliates. All rights reserved. | rq*Eval() Table Functions rqEval, rqTableEval, "rqGroupEval", rqRowEval rq*Eval( cursor(select * from ), cursor(select * from ), 'select from t', , '') ? Input cursor C Depending on the function, input passed as a whole table, group, or one row at a time to the R closure (not for rqEval) ? Parameters cursor C Parameters are specified through a select statement, scalars only C single row ? Output table definition C a query specifying the format of the result If NULL, output is a serialized BLOB If 'PNG', images only as BLOB column If 'XML', XML string of images and return values ? Group name (optional) C Name of the grouping column ? Number of rows (optional) C number of rows to provide to function at one time ? Name of R function in repository to execute Copyright ?