Oracle database 12c_and_DevOps

Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | 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. Safe Harbor Statement

Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | • Reputation: Cautious & Risk Averse • Primary focus: Longevity & Security • Favorite line: “Let’s not get hacked” Public 4 Meet the DBA DBA

Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Together they must build a new web application for the recently acquired coffee shops chain Oracle Confidential – Internal/Restricted/Highly Restricted 5

Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Developer 6Public We need to build a new web application for the coffee shops we recently acquired in a hurry, so we want to use a flexible schema using JSON documents. Can you setup a MongoDB database for us? She cannot differentiate a blaster from a stick… Oh no! What does he want now? DBA Why don’t you just use Oracle?

Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | 7 Oracle Database 12c as a Document Store 12.2 JSON SQL> CREATE TABLE orders (order_details BLOB CONSTRAINT is_json CHECK (order_details IS JSON) ); Table containing JSON documents Public • Oracle stores JSON in table columns • No special data type • Can be VARCHAR2, BLOB or CLOB • JSON supported by all Oracle features • Analytics, Encryption, In-Memory, RAC, Replication, Parallel SQL, … • Plus can index any JSON element

Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | 8 Oracle Database 12c as a Document Store 12.2 JSON SQL> CREATE TABLE orders (order_details BLOB CONSTRAINT is_json CHECK (order_details IS JSON) ); Table containing JSON documents Public • Oracle stores JSON in table columns • No special data type • Can be VARCHAR2, BLOB or CLOB • JSON supported by all Oracle features • Analytics, Encryption, In-Memory, RAC, Replication, Parallel SQL, … • Plus can index any JSON element IS_JSON constraint ensures only legal JSON documents can be inserted

Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | DBADeveloper 9Public But the JSON document is BLOB. That’s not what we need. We need to be able to query the elements in the JSON documents? Don’t panic! Oracle offers native SQL support for JSON. Let me show you

Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | 10 Native SQL Support for JSON 12.2 JSON Public • JSON can be queried using simple SQL dot notation SELECT o.order_details.location.city FROM orders o; Location -------------------- Boston Los Angeles New York San Francisco Washington SQL> desc ORDERS NAME TYPE ------------------ ----------- ORDER_DETAILS BLOB Table containing JSON documents {"date": "2017-05-03 10:40:36", "loyaltyCardNo": 1230, "location": { "city": "Dallas", "state": "TX”}, "salesAmount": 7.55, "order": [ { "item": "Espresso”, …. } }

Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | DBADeveloper 12Public Hey, we have decided to do everything via REST and we know that MongoDB can speak REST So it looks like we need a document store after all Dude, REST is out of the box with Oracle! Let me show you

Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted 13 Conn establishment Ackn Request Response Conn close Ackn 13 Request Response Conventional communication RESTful communication REST in a nutshell

Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Oracle REST Data Services HTTP(s) API App-Dev with Relational Tables in Oracle Database { “loyaltyCardNo”: 1001, “name”: “Scott King”, “Location”: “500 Main street, Innovation CA” “date”: “Feb 27, 2017” "salesAmount": 4.15 “orders”: [{"item": "Mocha","size": "medium", "salesAmount": 4.15, "coffee_used_gram": 15}] } Oracle REST Data Services ORDS maps standard URI requests to corresponding relational SQL (not schemaless): e.g. SQL SELECT from customers and orders table. ORDS also transforms the SQL results into the highly popular JavaScript Object Notation (JSON), other formats include HTML, binary and CSV. Fully committed to supporting any and all standards required by Fusion / SaaS / FMW; we are actively engaged in the ongoing dialog. SQL URI Request or HTTP(s) post mapped to SQL request JDBC Connection Pool JSON SQL result to JSON transformation https://myhost/myapp/hr/customerorders/1001 URI

Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Configuring REST with Oracle Exadata Express Cloud Service 18 Specify the type of service Specify the actual command to be used by the service

Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | REST Endpoint For Developer https://xxxx-5342177.db.us.oraclecloudapps.com/apex/coffeeshop/orders Oracle Confidential – Internal/Restricted/Highly Restricted 19

Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Developer 24Public I’m heading home for the day and I want to keep working on my commute but I won’t have good internet connectivity. Can we build the same environment in Docker? DBA Absolutely! You will need to download the Docker image for Oracle Database 12c & Oracle SQL Developer Let me show you

Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | 1. Click on the tools menu 2. Click on the REST Data Services 3. Click on Install Oracle Confidential – Internal/Restricted/Highly Restricted 25 Enabling SQL Developer as ORDS

Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | 1. Use Oracle REST Data Service that’s included with SQL Dev 2. Specify the location for your REST config file 3. Click next Oracle Confidential – Internal/Restricted/Highly Restricted 26 Enabling SQL Developer as ORDS

Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | 1. Fill in you the Service Name for your database in docker 2. Specify the password for your ORDS_PUBLIC_USER 3. Click next Oracle Confidential – Internal/Restricted/Highly Restricted 27 Enabling SQL Developer as ORDS

Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | 1. Click Run Standalone mode 2. Specify HTTP PORT (8080) 3. Click next 4. Click finish Oracle Confidential – Internal/Restricted/Highly Restricted 28 Enabling SQL Developer as ORDS

Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | REST Endpoint For Docker http://localhost:8080/ords/coffeeshop/orders/order Oracle Confidential – Internal/Restricted/Highly Restricted 29

Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | DBADeveloper 31Public How’s it going? Isn’t the CRM data already in the database with the JSON data? Yeah, exactly. Now we just need to build some classes to get that corresponding relational data for the JSON orders Hey, we are working on integrating the CRM data with that JSON data we stored

Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | DBA 32Public There is no need to integrate at the app level. You can use SQL to run a query against the JSON & the CRM data. Let me show you but first I’ll need to check what the structure of your JSON looks like. Developer

Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Data Guide : Understanding Your JSON Documents • Metadata discovery: discovers the structure of collection of JSON documents – Optional: deep analysis of JSON for List of Values, ranges, sizing etc. • Automatically Generates – Virtual columns – Relational views • De-normalized relational views for arrays – Reports/Synopsis of JSON structure 33

Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | 34 SQL> SELECT JSON_DATAGUIDE(o.order_details) FROM orders o; JSON_DATAGUIDE(O.ORDER_DETAILS) ---------------------------------------------------------------------- [ {"o:path": "$.date", "type": "string", "o:length": 32 }, {"o:path": "$.order", "type": "array", "o:length": 256 }, {"o:path": "$.location", "type": "object", "o:length": 32}, {"o:path": "$.location.city", "type": "string", "o:length": 8}, .... { "o:path": "$.salesAmount", "type": "number", "o:length": 4 }, { "o:path": "$.loyaltyCardNo","type": "number", "o:length": 8}] Data Guide : Understanding Your JSON Documents

Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | 35 SQL> SELECT JSON_DATAGUIDE(o.order_details) FROM orders o; JSON_DATAGUIDE(O.ORDER_DETAILS) ---------------------------------------------------------------------- [ {"o:path": "$.date", "type": "string", "o:length": 32 }, {"o:path": "$.order", "type": "array", "o:length": 256 }, {"o:path": "$.location", "type": "object", "o:length": 32}, {"o:path": "$.location.city", "type": "string", "o:length": 8}, .... { "o:path": "$.salesAmount", "type": "number", "o:length": 4 }, { "o:path": "$.loyaltyCardNo","type": "number", "o:length": 8}] Data Guide : Understanding Your JSON Documents

Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Query JSON with Relational Data SQL> SELECT c.c_name, o.order_details.location.city, SUM(To_number(o.order_details.salesAmount)) total FROM customers c, orders o WHERE c.c_custid = o.order_details.loyaltyCardNo GROUP BY c.c_name, o.order_details.location.city ORDER BY total Desc FETCH FIRST 10 ROWS ONLY; 36 FETCH FIRST 10 ROWS ONLY • New syntax to limit number of rows returned • Replaces SELECT * FROM WHERE ROWNUM<11

Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | DBA 37Public To make it easier for you, I can create a view so the developers don’t need to know all of the join conditions Developer My developers can write Java code in their sleep but this SQL stuff seems really complicated Can you do anything to help them with that?

Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Creating a View on Top of JSON & Relational Data SQL> CREATE OR REPLACE VIEW crm_data AS SELECT c.c_name, o.order_details.location.city, SUM(To_number(o.order_details.salesAmount)) total FROM customers c, orders o WHERE c.c_custid = o.order_details.loyaltyCardNo GROUP BY c.c_name, o.order_details.location.city ORDER BY total Desc FETCH FIRST 10 ROWS ONLY 38

Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | DBADeveloper 40Public No way, that just leads to a lot of round trips between app and database, which will make things even slower. And besides, this problem has already been solved. Ever heard of Materialized Views? That's great but how will that work when we have GBs of data in there. Surely just selecting the JSON first and then retrieving the corresponding customer data is faster and safer performance wise

Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | What is a Materialized View? • A materialized view is a database object that contains the results of a query • The query can be base on tables, view or other materialized views • Typically used to avoid repeating costly aggregations • Optimizer transforms queries against original tables into a semantically equivalent query against the materialized view • A trade off between disk space and CPU Oracle Confidential – Internal/Restricted/Highly Restricted 42

Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Creating Real-Time MV on Top of JSON & Relational Data SQL> CREATE MATERIALIZED VIEW top_customers_mv ENABLE query rewrite AS SELECT c.c_name, o.order_details.location.city, SUM(To_number(o.order_details.salesAmount)) total FROM customers c, orders o WHERE c.c_custid = o.order_details.loyaltyCardNo GROUP BY c.c_name, o.order_details.location.city; 43

Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Queries Automatically Redirected to Use Materialized View 44 SQL> SELECT * FROM crm_data;; --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 10 | 20550 | |* 1 | VIEW | | 10 | 20550 | |* 2 | WINDOW SORT PUSHED RANK | | 1 | 2029 | | 3 | MAT_VIEW REWRITE ACCESS FULL| TOP_CUSTOMERS_MV | 1 | 2029 | ---------------------------------------------------------------------------

Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Developer 45Public I’m still not sure this approach will scale as we need the queries to be returned in sub-seconds. Can we try it with a bigger data set? DBA Sure. Where is the bigger data set?

Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Test MV Query Against Larger Dataset 2.56 million JSON documents Oracle Confidential – Internal/Restricted/Highly Restricted 46 SQL> CREATE MATERIALIZED VIEW top_customers_history_mv ENABLE query rewrite ENABLE on query computation AS SELECT c.c_name, o.order_details.location.city, SUM(To_number(o.order_details.salesAmount)) total FROM customers c, orders_history o WHERE c.c_custid = o.order_details.loyaltyCardNo GROUP BY c.c_name, o.order_details.location.city;

Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | 47Public The what? Developer Oh that’s great for the known reports but the marketing folks want to run ad-hoc reports too. What can we do to make sure they stay within 30 seconds? DBA Don’t panic, we can put the tables into the In-Memory column store

Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Oracle Database In-Memory: Dual Format Architecture • BOTH row and column formats for same table • Simultaneously active and transactionally consistent • OLTP uses proven row format • Analytics & reporting use new in-memory Column format • Seamlessly built into Database • All enterprise features work - RAC, Dataguard, Flashback, etc 48 Buffer Cache New In-Memory Column Store SALES SALES Row Format Column Format SALES

Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | • Full JSON documents populated using an optimized binary format • Additional expressions can be created on JSON columns (e.g. JSON_VALUE) & stored in column store • Queries on JSON content or expressions automatically directed to In-Memory format Superfast / Multi-Model Analytics: In-Memory JSON Relational In-Memory Colum Store In-Memory Virtual Columns In-Memory JSON Format Relational Virtual JSON 49 {"date": "2016-05-03 10:40:36", "loyaltyCardNo": 1230, "location": { "city": "Dallas", "state": "TX”}, "salesAmount": 7.55, "order": [ { "item": "Espresso”, …. } }

Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | SQL> ALTER TABLE orders_history ADD city AS (JSON_VALUE(order_details, '$.location.city')); SQL> ALTER TABLE orders_history ADD state AS (JSON_VALUE(order_details, '$.location.state')); SQL> ALTER TABLE orders_history ADD salesamount AS (JSON_VALUE(order_details, '$.salesAmount')); Create Virtual Columns on the Orders_History table 50

Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | SQL> ALTER TABLE customers INMEMORY; SQL> ALTER TABLE orders_history INMEMORY; SQL> SELECT count(*) FROM customers; SQL> SELECT count(*) FROM orders_history; SQL> SELECT segment_name, populate_status, bytes_not_populated FROM v$im_segments; Place Tables into IM Column Store 51

Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | DBADeveloper 52Public Wow, that’s fantastic. Will it automatically stay In-Memory? Yup, all done for you by the database! This is awesome! That's exactly what you want from a database. Schema flexibility, analytics and performance.

Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | DBADeveloper 54Public Hey one more thing, Mark has given us some ridiculous security requirements. Nobody is allowed to see the loyalty card number and stuff like that. Can we do something there too?" Sure we can Let's implement data redaction.

Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Define a Redaction Policy to Redact Employee Last Name SQL> BEGIN dbms_redact.add_policy( object_schema => ’coffeeshop', object_name => ’customers', column_name => ’c_custid', policy_name => 'redact_Loyal_crd', function_type => DBMS_REDACT.FULL, expression => '1=1'); END; / Oracle Confidential – Internal/Restricted/Highly Restricted 55

Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | DBADeveloper 57Public Man, I wish we would have talked to you sooner. You pretty much got our go-live down from 6 to 1 week! Hey, can we just include you going forward in our discussions and project planning? I'd be delighted to! I always thought we should work much closer together. It's also great for me to have better understand what apps you are building in order to support them.

Copyright © 2015, Oracle and/or its affiliates. All rights reserved. | Summary: Oracle Database 12c & Developers = Best DevOps Oracle Confidential – Internal/Restricted/Highly Restricted 58 Flexibility Native JSON support REST Services Scalability Scale-out and Scale-up Real-Time Analytics JSON in the In-Memory Column Store Security Data encrypted at rest Data Redaction

Oracle database 12c_and_DevOps

More Related Content

What's hot

Similar to Oracle database 12c_and_DevOps

More from Maria Colgan

Recently uploaded

Oracle database 12c_and_DevOps

Editor's Notes