Die Oracle DB 12c JSON, REST und mehr

Karin Patenge | Leitende Systemberaterin [email protected] | @kpatenge | oracle-spatial.blogspot.de Oracle Deutschland B.V. & Co KG | GS Potsdam DOAG Regionalgruppe Thüringen | 29. September 2015 | Jena

Copyright © 2014 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 © 2014 Oracle and/or its affiliates. All rights reserved. |

2

Agenda

Und ein bisschen mehr

Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |

3

Und ein bisschen mehr

Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |

4

Oracle12c für Entwickler_innen Die Datenbank für alle wichtigen Plattformen

Ruby

JSON

Oracle ADF

Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |

Oracle APEX

Oracle RDS

Oracle Database 12c für Entwickler_innen • Standardaufgaben schneller und einfacher: SQL und PL/SQL 32k VARCHAR2, PL/SQL WITH-Klausel, Identity Columns, OFFSET-Klausel ...

• Neue Analysemöglichkeiten mit SQL: SQL Pattern Matching • Native JSON-Unterstützung in der Datenbank SQL/JSON, JSON-Indizierung und mehr

• Unstrukturierte Daten: XML, Texte und mehr ... • Sichere Anwendungen mit SQL und PL/SQL Rollen für PL/SQL, Code-Based Access-Control

• Werkzeuge und Umgebungen SQL Developer, Application Express, Oracle REST Data Services Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |

6

Eindeutige Schlüssel generieren  Identity Columns Bisher CREATE TABLE kunden_tab ( id NUMBER(10) PRIMARY KEY name VARCHAR2(200), ... ) / CREATE SEQUENCE kunden_seq START WITH 1 INCREMENT BY 1 / CREATE OR REPLACE TRIGGER pk kunden_trg BEFORE INSERT on kunden_tab FOR EACH ROW BEGIN :new.id := kunden_seq.nextval; END; /

Seit 12.1 CREATE TABLE kunden_tab ( id NUMBER(10) PRIMARY KEY GENERATED ALWAYS AS IDENTITY START WITH INCREMENT BY 1, name VARCHAR2(200) ... ) /

Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |

SQL Pattern Matching • Aufgabenstellung: Finde alle "Double Bottoms" im Kursverlauf einer Aktie X

Y

W

Z

• Wann beginnt das Muster? • Wann endet das Muster? • Welcher Aktienkurs? • Muster innerhalb einer Woche? FIRST_X

LAST_Z

1

9

13

19

SELECT first_x, last_z Aktienkurs FROM ticker MATCH_RECOGNIZE ( PARTITION BY name ORDER BY time MEASURES FIRST(x.time) AS first_x LAST(z.time) AS last_z ONE ROW PER MATCH PATTERN (X+ Y+ W+ Z+) DEFINE X AS (price < PREV(price)) Y AS (price > PREV(price)) W AS (price < PREV(price)) Z AS (price > PREV(price)) Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |

Tage

Oracle Database 12c Release 1 (12.1.0.2) Auszug aus New Features Liste (Alle Details hier) Feature

Beschreibung

Oracle In-Memory

Option

JSON Unterstützung

Feature der Datenbank

Oracle REST Data Services

Feature der Datenbank

Oracle Multitenant

Option, wenn mehr als eine PDB genutzt wird

Advanced Index Compression

Teil der Option Oracle Advanced Compression

Attribute Clustering

Feature der Datenbank

Full Database Caching

Feature der Datenbank

Automatic Big Table Caching

Feature der Datenbank

Approximate Count Distinct

Feature der Datenbank Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |

9

Und ein bisschen mehr

Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |

10

JSON – JavaScript Object Notation • Sehr einfaches Datenaustauschformat

• Einfachere Konzepte als XML • Basiert auf JavaScript-Code • Javascript auch auf Server wichtig: node.js • Breite Unterstützung durch Web-APIs – Google, Twitter, Facebook & Co.

• JSON-Path Anfragen (analog zu XPATH) • Setzt sich mehr und mehr durch

Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |

Ein Beispiel für den Einsatz von JSON • Inhalt von Blog-Postings als JSON abrufen – http://oracle-spatial.blogspot.com/feeds/posts/default?alt=json

Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |

12

Native JSON Unterstützung in der Oracle DB 12g • JSON Dokumente speichern und verwalten – JSON als Text speichern (VARCHAR2, CLOB, BLOB)  Ohne Schema - volle Flexibilität – Kein eigener Datentyp: Nutzung der Standard-Datentypen für Zeichenketten bzw. Binärdaten JSON ermöglicht schemalose Datenhaltung – Indizierung mit einem JSON aware Index in der Oracle-Datenbank ...

• API Zugriff auf JSON

... aber komplett integriert mit der – REST Services relationalen Welt! – Java API (weitere Programmiersprachen in Planung)

• SQL Abfragen direkt auf JSON-Dokumente – Reporting, Analyse und relationaler Zugriff direkt auf JSON-Dokumente – Dualität JSON / SQL Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |

13

JSON / SQL Dualität Oracle Database 12c JSON

Datenzugriff per REST oder Native API

SQL

JSON wird in der Datenbank gespeichert

Analysen / Queries per SQL

Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |

14

Wie geht das mit der Dualität? JSON-Sicht auf relationale Daten • • • •

Relationale Sicht auf JSON

Node.js Treiber für die Oracle DB APEX_JSON ORDS PL/SQL

Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |

15

Flexible Entwicklung mit JSON • Agilität und Continous Delivery – Änderungen an Datenstrukturen immer häufiger und flexibler – Lange Zyklen für Schema-Änderungen werden immer weniger akzeptiert

• Ansatz: Ablage als JSON und Parsing zur Query Zeit – Einfache Ablage der JSON-Dokumente in einem Document Store – Validierung über IS JSON Security ...? – Parsing zur Abfragezeit – IT Betrieb ...? fehlende Attribute "fehlen dann einfach" Replikation ...? – NoSQL-Datenbanken, Funktionsbibliothek ...? Hadoop Cluster

Relationale Applikationen ...? Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |

JSON indizieren • Function Based Indexes für … – Bekannte Abfragemuster – Attribute kommen im JSON nur einmal vor – keine Arrays – Basiert auf Function Based Indexes

• Volltextindizierung für … – Unbekannte Abfragemuster – inkl. Volltextrecherche – Beliebige JSON-Attribute – Basiert auf Oracle TEXT

Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |

Anwendungsbeispiel: Oracle DB als JSON Document Store Social Media (Twitter) Analyse

JSON Dokumente als CLOBs

Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |

21

Demo: JSON in der Oracle DB

Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |

22

Einfach mal selbst ausprobieren? • Prebuilt Database Application Developer VM für VirtualBox http://www.oracle.com/technetwork/community/developervm/index.html – Hands-On Lab

• Blog-Postings in den deutschsprachigen Blogs http://json-rest-oracledb.blogspot.com http://sql-plsql-de.blogspot.com/search?q=json

Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |

23

Und ein bisschen mehr

Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |

24

REST – Representational State Transfer • Programmierparadigma für verteilte Systeme, insbesondere Webservices – Übergang von einem Zustand zum nächsten (einer Applikation) durch Transfer von Daten

• Über eine URI wird genau ein (Web-) Seiteninhalt präsentiert – In der URI werden Ort und Name der Ressource angegeben

• Wird eingesetzt für Maschine-zuMaschine Kommunikation • Einfache Alternative zu SOAP und WSDL

Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |

25

Oracle REST Data Service Allgemeines

Funktionen

• Ursprung im APEX Listener

• Webserver für Application Express

– Java-basierter Webserver für Application Express

• Erweiterung um REST-Webservices – Umbenennung in Oracle REST Data Services (ORDS)

• Möglichkeit, REST Endpoints für Tabellen und Views in einem DBSchema bereitzustellen.

• REST-Endpoint für relationale Tabellen und Views • REST-Endpoint für PL/SQL Funktionen, Prozeduren und anonyme Blöcke • REST-Endpoint für die JSON-Funktionen in der Oracle Datenbank 12c (SODA) • REST-Endpoint für die Oracle NoSQL DB

Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |

26

Wer oder was nutzt REST? • Öffentlich verfügbare Services mit RESTful APIs – Twitter, Netflix, Dropbox, Flickr, Oracle Database Cloud Service, Amazon S3, ...

• Produkte und Werkzeuge mit RESTful APIs – Glassfish Application Server Admin, Selenium WebDriver, ...

• RESTful Frameworks – APEX RESTful Services, Jersey (JAX-RS), Restlet, Restify, ...

• REST Clients oder Browser Plugins Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |

27

„Funktionsset“ • Constrained set – GET (Lesezugriff) – PUT (Schreibzugriff – idempotent) – DELETE (Schreibzugriff – idempotent) – POST (nicht sicher bzw. Idempotent)

• HTTP Methoden für RESTful Services – GET (Retrieve) – PUT (Update – oder Create) – DELETE (Delete) – POST (Create - Sub resource)

• Rückgabecodes 1xx, 2xx, 3xx, 4xx, 5xx

REST

CRUD

SQL

GET

Read

SELECT

POST

Create

INSERT

PUT

Update or Create

UPDATE or INSERT

DELETE

Delete

DELETE Vereinfachte Darstellung

Siehe auch: http://www.restapitutorial.com/lessons/httpmethods.html

Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |

29

REST API für JSON • REST Webservice API zur Arbeit mit JSON-Dokumenten: Kein SQL nötig! • Zwei Deployment-Varianten: – Oracle Rest Data Services (aka 'APEX Listener') – Oracle XML DB Protokollserver (XML DB)

• Unterstützt die REST Standard-Operationen – PUT : Create / Update – GET : Retrieve – DELETE : Delete – POST : JSON Queries, Utility-Funktionen

• Konfiguration des REST Service wird komplett in der Datenbank gespeichert

Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |

Anwendungsbeispiele Oracle Document Cloud Service

Oracle Database Schema Service

• URI Pattern: • Stellt RESTful Web Service API https://[:]/ bereit /api// • Bereitstellung RESTful Web Service • Beispiel: über APEX im DB Schema Service https://oradocs.documents.us2.ora clecloud.com/documents/api/1.1/f olders • Result: JSON Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |

31

Demo: REST / RESTful Web Services

Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |

32

Einfach mal selbst ausprobieren? • Prebuilt Database Application Developer VM für VirtualBox http://www.oracle.com/technetwork/community/developervm/index.html – Hands-On Lab

Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |

33

Weitere Informationen • Oracle Dokumentation – XML DB Developers Guide • Kapitel 39: JSON in Oracle Database http://docs.oracle.com/database/121/ADXDB/json.htm#ADXDB6246

• Blog Postings zu JSON in der Oracle DB – http://sql-plsql-de.blogspot.com/ • Juli 2014: Oracle 12.1.0.2 ist da: Ein Einblick in die JSON-Unterstützung

– http://json-rest-oracledb.blogspot.com/

• Twitter – u.a. @jeffthatsmith @cczarski

Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |

34

[email protected]

Blog: http://oracle-spatial.blogspot.de Twitter: @kpatenge

Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |

Bonus-Folien  JSON in der Oracle-Datenbank Vorgehensweise und Funktionen im Detail

Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |

36

Schritt 1: Tabelle anlegen • Für JSON wird kein eigener Datentyp verwendet • Alle Typen, die Text aufnehmen können, sind nutzbar VARCHAR2, CLOB, BLOB • JSON- und Nicht-JSON Inhalte können in einer Tabelle gemischt werden create table po_json ( filename varchar2(200), json clob );

Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |

37

Schritt 2: JSON Dokumente laden

Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |

38

Schritt 3: Was ist JSON, was ist nicht JSON • SQL Operatoren IS JSON und IS NOT JSON select count(*) from po_json where json is json; select count(*) from po_json where json is not json;

• Check Constraint stellt sicher, dass nur noch JSON gespeichert wird alter table po_json add constraint ck_isjson check (json is json);

Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |

39

Schritt 4: SQL Abfragen ... • SQL Funktionen JSON_EXISTS, JSON_VALUE, JSON_QUERY und JSON_TABLE • Arbeitsweise immer gleich – JSON-Tabellenspalte oder Literal – JSON Pfadausdruck (JSON Path Expression) – Returning Klausel – Error Handling

Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |

40

JSON_QUERY extrahiert JSON "Fragmente" select json_query( json, '$.PurchaseOrder.ShippingInstructions' PRETTY ) from po_json; JSON_QUERY -------------------------------------------------------------------------------[ { "name" : ["Gerry B. Ford"], "address" : ["100 Oracle Parkway\r\nRedwood Shores\r\nCA\r\n94065\r\nUSA"], "telephone" : ["650 506 7100"] } ]

1 Zeile wurde ausgewählt.

Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |

41

Im Fehlerfall ... wird NULL zurückgegeben select json_query( json, '$.PurchaseOrder.ShippingInstrs' PRETTY ) from po_json; JSON_QUERY -------------------------------------------------------------------------------? 1 Zeile wurde ausgewählt.

Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |

42

... oder eine Fehlermeldung. select json_query( json, '$.PurchaseOrder.ShippingInstrs' PRETTY ERROR ON ERROR ) from po_json; * FEHLER in Zeile 1: ORA-40462: Auswertung von JSON_QUERY hat keinen Wert ergeben

Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |

43

JSON_VALUE extrahiert "skalare Werte" select json_value( json, '$.PurchaseOrder.Reference[0]' RETURNING VARCHAR2 ) from po_json where rownum < 10; JSON_VALUE -----------------------------------------------------------------FORD-20021009123336872PDT JONES-20011127121050471PST MARTIN-20011127121050401PST CLARK-20021009123337764PDT :

Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |

44

JSON_EXISTS stellt fest, ob ein JSON-Pfad existiert select filename from po_json where json_exists( json, '$.PurchaseOrder.Reference' ); FILENAME ----------------------------------------------------------------FORD-20021009123336872PDT.js JONES-20011127121050471PST.js MARTIN-20011127121050401PST.js CLARK-20021009123337764PDT.js : Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |

45

JSON_TABLE für relationale Sichten auf JSON select reference, requestor, costcenter from po_json, json_table( json, '$.PurchaseOrder' columns ( reference varchar2(30) path '$.Reference[0]', requestor varchar2(25) path '$.Requestor[0]', costcenter varchar2(4) path '$.CostCenter[0]' ) ) /

Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |

46

JSON_TABLE für relationale Sichten auf JSON REFERENCE --------------------------FORD-20021009123336872PDT JONES-20011127121050471PST MARTIN-20011127121050401PST CLARK-20021009123337764PDT JONES-20011127121042590PST MARTIN-20011127121040547PST JONES-20021009123336602PDT WARD-20011127121050511PST BLAKE-20011127121055689PST MARTIN-20011127121048889PST :

REQUESTOR -----------------Gerry B. Ford Richard J Jones Thomas D. Martin Edward K. Clark Richard J Jones Thomas D. Martin Richard J Jones Stephen B. Ward David E. Blake Thomas D. Martin :

COST ---R20 R20 S30 A10 R20 S30 R20 S30 S30 S30 :

Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |

47

JSON_TABLE für geschachtelte Strukturen select reference, requestor, num, descr, quantity From po_json, json_table( json, '$.PurchaseOrder' columns ( reference varchar2(30) path '$.Reference[0]', requestor varchar2(25) path '$.Requestor[0]', CostCenter varchar2(4) path '$.CostCenter[0]', nested path '$.LineItems[*].LineItem[*]' columns ( num number path '$."\u0024".ItemNumber', descr varchar2(40) path '$.Description[0]', quantity number path '$.Part[0]."\u0024"."Quantity"' ) ) ) Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |

48

JSON_TABLE für relationale Sichten auf JSON REFERENCE --------------------------FORD-20021009123336872PDT FORD-20021009123336872PDT FORD-20021009123336872PDT FORD-20021009123336872PDT FORD-20021009123336872PDT FORD-20021009123336872PDT FORD-20021009123336872PDT FORD-20021009123336872PDT FORD-20021009123336872PDT FORD-20021009123336872PDT FORD-20021009123336872PDT FORD-20021009123336872PDT FORD-20021009123336872PDT FORD-20021009123336872PDT FORD-20021009123336872PDT : FORD-20021009123336872PDT

REQUESTOR NUM DESCR QUANTITY ------------------ ---- ---------------------------------------- -------Gerry B. Ford 1 Ordet 4 Gerry B. Ford 2 The Naked Kiss 3 Gerry B. Ford 3 Charade 2 Gerry B. Ford 4 Robocop 2 Gerry B. Ford 5 Cries and Whispers 4 Gerry B. Ford 6 Autumn Sonata 4 Gerry B. Ford 7 Getrud 3 Gerry B. Ford 8 And the Ship Sails on 1 Gerry B. Ford 9 Flesh for Frankenstein 4 Gerry B. Ford 10 The Hidden Fortress 2 Gerry B. Ford 11 The Importance of Being Earnest 3 Gerry B. Ford 12 The Vanishing 4 Gerry B. Ford 13 Cries and Whispers 4 Gerry B. Ford 14 Beauty and the Beast 2 Gerry B. Ford 15 And God Created Woman 1 : : : : Gerry B. Ford 16 The Magic Flute 1

20 Zeilen ausgewählt.

Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |

49

Schritt 5: Volltextindizierung mit Oracle TEXT • Erstellung eines Oracle TEXT Index für JSON CREATE INDEX po_search_idx ON po_json (json) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS ( 'section group CTXSYS.JSON_SECTION_GROUP SYNC (ON COMMIT)' ) /

Index wurde erstellt.

Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |

50

Schritt 5: Volltextindizierung mit Oracle TEXT • Erstellung eines Oracle TEXT Index für JSON select filename from po_json WHERE json_textcontains( json, '$.PurchaseOrder.LineItems.LineItem.Description', 'Magic' ); TURNER-20021009123338565PDT.js CLARK-2001112712105223PST.js : Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |

51