Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Masterclass: REST APIs and the Oracle Converged Database for DBAs

Masterclass: REST APIs and the Oracle Converged Database for DBAs

What YOU WILL LEARN in this session:
+ What does it mean for a service or API to be 'RESTful?'
+ HTTPS and JSON basics, and how they come together to make REST APIs possible
+ Best Practices for deploying Oracle REST Data Services (ORDS) for Oracle Database
+ How to build a SQL and PL/SQL based REST API
+ How to use AUTO features for existing tables, views, and PL/SQL
+ How to keep your APIs performant and secure
+ How to make your APIs developer friendly

More details at thatjeffsmith.com

thatjeffsmith

June 16, 2021
Tweet

More Decks by thatjeffsmith

Other Decks in Programming

Transcript

  1. REST APIs and
    the Oracle Converged Database
    Distinguished Product Manager
    [email protected]
    Tweets: @thatjeffsmith
    Blogs: https://www.thatjeffsmith.com
    Jeff Smith

    View full-size slide

  2. • 75 minutes lecture
    • Hands-On Lab introduction
    • Questions? Zoom Q&A
    • Recorded? YES
    • SLIDES? YES
    Format

    View full-size slide

  3. • What makes an API, RESTful?
    • Why should we care?
    • How it works/Architecture
    • examples
    • Oracle Converged Database
    • Database Management APIs
    Agenda

    View full-size slide

  4. • Representational Stateless Transfer
    • Architectural Style for networked applications
    • Communicated via HTTPS, but
    • HTTPS <> REST
    REST

    View full-size slide

  5. • Predictable, familiar for developers
    • Model nouns not verbs
    • Proper Responses
    • LINKS
    RESTful APIs
    RESTful Ugh!
    POST things/ GET delete_things/
    GET things/:id POST things/new_thing
    RESTful Ugh!
    Request: POST things/ Request: POST things/
    Response: 201 Created Response: 200 OK
    Location /things/123

    View full-size slide

  6. • Everything is HTTPS/JSON
    • Developers will go around you
    • Database needs CI/CD, automation, mon…
    • Microservices!
    Cool, but why do I care?

    View full-size slide

  7. HTTPS > SQL > HTTPS
    GET https://host/ords/hr/beers/1
    SELECT *
    FROM EMPS
    WHERE ID = 1
    HTTP/1.1 200 OK
    { "id": 1,
    "name": "Jeff",
    "job": "PM",
    "salary": 100 }
    {json}
    URI SQL & PLSQL
    Marshalls to Database
    HTTP Request
    HTTP Response Transforms to JSON SQL Result Set / Standard OUT
    Oracle REST Data Services

    View full-size slide

  8. • Webserver layout
    • Java Servlet
    • Tomcat/WLS or
    • Standalone Java app Tomcat, WLS
    ORDS
    Apache Static Files
    -HTML, CSS, JS
    /ords/…
    /db1/hr/emp/
    JSON
    Binary
    HTML

    HTTP(s)
    Results
    JDBC
    pool1 pool2 pool3
    Request
    JDBC JDBC
    ORDS Architecture

    View full-size slide

  9. • Each database gets a connection pool
    • CDBs, PDBs, or single instances
    • Each database gets a mapping pattern
    ords/
    ords/db2/…
    ords/db3/…
    TIP 1: 3 ORDS front-ended with a load balancer
    TIP 2: Data Guard/Scan Listener for your DB, ORDS will immediately pick up
    DatabaseS and HA Tips

    View full-size slide

  10. • JDBC Conn Pools
    • Default Size: 10
    • 1st pool => ords/
    • ORDS_PUBLIC_USER ..
    Tip 1 : Customers MUST tune connection pools
    Tip 2: Bigger Pools <> FASTER!
    Whitepaper: Real World Perf Sizing Guide
    Video: Office Hours Session
    Connection Pools

    View full-size slide

  11. • CLI for configuration changes or..
    • Edit pool XML and/or Standalone properties files
    Configuration

    View full-size slide

  12. Watch Later…

    View full-size slide

  13. ORDS & Your Database
    • ORDS_PUBLIC_USER
    • ORDS_METADATA
    • ORDS.ENABLE_SCHEMA()
    • APIs published to SCHEMA
    • APIs exec as USER
    Modeling ORDS_METADATA with Database Actions (ORDS)

    View full-size slide

  14. Where APIs come from
    1. REST Enable Schema
    2. REST Enable Object OR
    2. Publish a REST Module

    View full-size slide

  15. REST Enabling
    BEGIN
    ORDS.ENABLE_SCHEMA(
    p_enabled => TRUE,
    p_schema => 'HR',
    p_url_mapping_type => 'BASE_PATH',
    p_url_mapping_pattern => 'peeps',
    p_auto_rest_auth => TRUE);
    commit;
    END;

    View full-size slide

  16. Step 1…you’re not done

    View full-size slide

  17. Built-in Dev Tools

    View full-size slide

  18. AUTOREST
    •CRUD APIs, no code
    •Maintained by ORCL
    •Feature rich
    •Optimized
    RESTful Service
    •Your code -
    •Inputs, outputs, error handling,
    response codes, formatting
    •Full access to SQL/PLSQL
    •Easily exported, source controlled
    •Transparent
    No or Low Code

    View full-size slide

  19. AUTOREST: TABLE

    View full-size slide

  20. CRUD APIs, for free

    View full-size slide

  21. POST => INSERT

    View full-size slide

  22. PUT => UPDATE

    View full-size slide

  23. DELETE => DELETE

    View full-size slide

  24. POST…BatchLoad

    View full-size slide

  25. Oracle Confidential – Internal/Restricted/Highly Restricted
    Batchloading 10M rows

    View full-size slide

  26. Like coding? Modules!

    View full-size slide

  27. RESTful Service
    •Your code -
    •Inputs, outputs, error handling,
    response codes, formatting
    •Full access to SQL/PLSQL
    •Easily exported, source controlled
    •Transparent
    Techniques/tricks

    View full-size slide

  28. REST Module: 2+Tables

    View full-size slide

  29. Binds in the URI

    View full-size slide

  30. SQL & Paging

    View full-size slide

  31. ALT: AUTOREST a VIEW

    View full-size slide

  32. REST is LINK DRIVEN
    FAIL

    View full-size slide

  33. GENERATING LINKS

    View full-size slide

  34. Building your Templates
    Avoid dead-ends

    View full-size slide

  35. POST/PUT Tips
    • Request Mime Type: Application/JSON
    • :x – looks for {"x" :…}
    • Parameters
    • :bind is the VAR in the blog, name is the HEADER label
    • Use to work with REQUEST or RESPONSE elements of your API

    View full-size slide

  36. PL/SQL
    1. ORDS Enable PL/SQL (package, proc, func)
    2. OR Publish a REST Module
    AUTO
    • RPC for PL/SQL program
    • Input params in req body
    • OUT/RETURNs
    transformed and returned
    as JSON response
    RESTful Service
    • Your code -
    • Inputs, outputs, error handling, response
    codes, formatting
    • Print HTML, return JSON, download
    files…

    View full-size slide

  37. PL/SQL & REFCURSORs
    1. SELECT – func with OUT sys_refcursor
    2. ANON Block
    3. Call SP, send OUT to a RESULTSET OUT PARAM
    4. AUTO

    View full-size slide

  38. AUTO PL/SQL REFCUR

    View full-size slide

  39. ANON Block REFCUR

    View full-size slide

  40. PL/SQL Download File
    CREATE OR REPLACE PROCEDURE download_file ( media_id NUMBER ) AS
    vMIMETYPE VARCHAR2(256);
    vLENGTH NUMBER;
    vFILENAME VARCHAR2(2000);
    vBLOB BLOB;
    BEGIN
    SELECT file_name, content_type, content INTO vFILENAME, vMIMETYPE, VBLOB
    FROM media
    WHERE id = media_id;
    vLENGTH := DBMS_LOB.GETLENGTH(vBLOB);
    owa_util.mime_header(NVL(vMIMETYPE, 'application/octet'), FALSE);
    htp.p('Content-length: ' || vLENGTH);
    htp.p('Content-Disposition: attachment; filename=' || SUBSTR(vFILENAME, INSTR(vFILENAME, '/') + 1) || ‘’);
    owa_util.http_header_close;
    wpg_docload.download_file(vBLOB);
    END download_file;
    /

    View full-size slide

  41. Uploading Files - BLOBS

    View full-size slide

  42. Converged Database
    • One DB engine – ALL data & workloads
    • Support for Microservices, Events, REST, SaaS…
    • Accessed/Managed with SQL * PL/SQL
    Read more on Converged Databases with Maria Colgan

    View full-size slide

  43. GET HTTPS://.../ords/hr/soda/latest/thatjeffsmith/123456...
    JSON Documents
    POST HTTPS://.../ords/hr/soda/latest/thatjeffsmith/

    View full-size slide

  44. Spatial, GeoJSON

    View full-size slide

  45. Analytics & ML

    View full-size slide

  46. • Requests must be authenticated & authorized!
    • APIs are protected via required roles
    • Authentication can be managed by Web Server OR
    Use ORDS BASIC Auth
    Database Auth
    Built-in OAuth2 Workflow
    Secure APIS

    View full-size slide

  47. Security
    OAuth2

    View full-size slide

  48. DBA. Me. Want. Stuff!
    • Provide access, easily to your DBs
    • Automation
    • Monitoring

    View full-size slide

  49. DB-API: 500+ Endpoints
    Performance Monitoring PDB Lifecyle General
    Data Dictionary Reports
    • tables/,
    tables/{table}
    • indexes/…
    Multitenant Management
    • Create
    • Clone
    • Change State
    • Drop
    • Reports
    Problematic Activity
    • Sessions
    • Locks
    • Waits
    • Alert Log
    What isn’t running well?
    • ASH
    • AWR
    • RTSM
    • Top SQL Database Operations
    • DBCA
    • Data Pump

    View full-size slide

  50. DB-API: Turn it on
    true

    View full-size slide

  51. Oracle Docs OpenAPI.JSON Endpoint
    DB-API: Docs

    View full-size slide

  52. Service Catalog

    View full-size slide

  53. Data Pump via HTTPS
    {
    "datapump_dir":"DATA_PUMP_DIR",
    "filter" :"HOCKEY_STATS",
    "job_mode" :"TABLE",
    "threads" : 2
    }
    POST BODY Response

    View full-size slide

  54. Web Tooling for Users

    View full-size slide

  55. Extra Credit!

    View full-size slide

  56. Resources
    • SlideShare/SpeakerDeck
    • Blogs (thatjeffsmith.com)
    • GitHub
    • Articles
    UKOUG Scene Why REST, and What’s in it or Me?
    Oracle Mag AUTO REST & REST Enabled SQL
    • And don’t forget Oracle-Base!

    View full-size slide