json daten in oracle datenbank 12c release 2 - rainfocus · oracle datenbank 12c release 2 ... •...
TRANSCRIPT
Ulrike Schwinn ([email protected], @uschwinn)
JSON Daten in Oracle Datenbank 12c Release 2
Copyright © 2017, Oracle and/or its affiliates. All rights reserved.Oracle Technology Monthly | BU Core & Cloud Technologies 1
Ulrike Schwinn ([email protected], @uschwinn)
BU Core & Cloud TechnologiesOracle Deutschland B.V. & Co KG
Warum überhaupt JSON in der Datenbank?
Getrennte Datenhaltung hat Folgen ...
• Umständliche Integration
• Langsame Interaktion
• Schwierige Verwaltung und Kontrolle
Copyright © 2017, Oracle and/or its affiliates. All rights reserved.Oracle Technology Monthly | BU Core & Cloud Technologies 2
• Schwierige Verwaltung und Kontrolle
Texte, XML, Semantische Netze ...
Bilder, Videos....Strukturierte Daten
Warum überhaupt JSON in der Datenbank?
• Ein einziges zentrales Repository => zentrales Management
• Nutzung der Mechanismen einer Datenbank wie
– Backup/Recovery und Security
Copyright © 2017, Oracle and/or its affiliates. All rights reserved.Oracle Technology Monthly | BU Core & Cloud Technologies 3
– Massendatenverarbeitung und Performance Techniken wie Indizierung,
Partitionierung, Parallelisierung, Laden der Daten usw.
• Einheitliche Werkzeuge
• Zugriff auf bestehende (relationale) Stammdaten und JSON Daten
über SQL
Die Oracle Datenbank für “all your data”
select p.id, p.name, sl.json_doc.quantity anzahl
from
Copyright © 2017, Oracle and/or its affiliates. All rights reserved.Oracle Technology Monthly | BU Core & Cloud Technologies 4
from
product p, customer c, store s, sales sl
where sl.json_value(json_doc,'$.product_id')=p.id
and
contains(p.notes,fuzzy('Lieferkosten'), 1) > 0
and
sl.json_value(json_doc,'$.CUSTNumber' returning
number) = c.cust_ids
and
s.store_id = 1234
and
sdo_within_distance(c.location,
s.location,'distance=20')='TRUE'
Alle Arten von Daten mit
Advanced Analytics, OLAP, R, PHP, .NET,
SQL ...
JSON in der Datenbank – ein Kurzprofil
• Integraler Bestandteil der Datenbank seit 12c Release 1 (12.1.0.2)
– Keine zusätzliche Installation oder Konfiguration erforderlich
– In allen Datenbank- und Cloud- Editionen
– Mit allen Standard Funktionen der Datenbank verwendbar
Copyright © 2017, Oracle and/or its affiliates. All rights reserved.Oracle Technology Monthly | BU Core & Cloud Technologies 5
– Mit allen Standard Funktionen der Datenbank verwendbar
• Zugriff über SQL und PL/SQL Schnittstelle
– Funktionen und Operatoren für Zugriff über JSON Pfad Ausdrücke
– Indizierung - sogar linguistisch
– Relationale View Generierung
• JSON Generierung
Anforderung an einem Beispiel
1. Bestellungen (JSON Dokumente)
– Export Format erzeugt von üblichen NoSQL Datenbanken
2. Suche nach einer bestimmten Bestellung
– Name des Auftraggebers ist nicht eindeutig
Herausforderungen
Copyright © 2017, Oracle and/or its affiliates. All rights reserved.Oracle Technology Monthly | BU Core & Cloud Technologies 6
Herausforderungen
– Einfache übersichtliche Codierung
– Größe des(der) Dokument(e)
– Große Anzahl an Dokumenten
– Ungenauigkeit der Anfrage
– Hohe Performance Anforderungen
JSON Dokument
Copyright © 2017, Oracle and/or its affiliates. All rights reserved.Oracle Technology Monthly | BU Core & Cloud Technologies 7
JSON Dokument
Copyright © 2017, Oracle and/or its affiliates. All rights reserved.Oracle Technology Monthly | BU Core & Cloud Technologies 8
JSON Daten und die Datenbank• Ausgangssituation:
– Daten liegen außerhalb der Datenbank im Export Format vor
– Oracle Beispiel: https://github.com/oracle/db-sample-schemas/blob/master/order_entry/PurchaseOrders.dmp
• Vorgehensweise
– Zugriff über logisches Directory
Copyright © 2017, Oracle and/or its affiliates. All rights reserved.Oracle Technology Monthly | BU Core & Cloud Technologies 9
– Zugriff über logisches Directory
– External Table mit Spalte für die JSON Daten in der Datenbank
create table json_contents (json_document CLOB)
organization external
(type oracle_loader default directory json_dir
access parameters
(records delimited by 0x'0A'
fields (json_document CHAR(5000)))
location ('PurchaseOrders.dmp')) reject limit unlimited;
create directory json_dir as '/home/oracle/db-sample-schemas-12.2.0.1/order_entry';
• JSON_EXISTS, JSON_VALUE, JSON_QUERY …
Zugriffe: Dokumente, skalare Werte, Fragmente ...
SQL> select min(json_value (json_document, '$.PONumber' Returning number)) min_po,
SQL> select * from json_contents where rownum = 1;
Copyright © 2017, Oracle and/or its affiliates. All rights reserved.Oracle Technology Monthly | BU Core & Cloud Technologies 10
SQL> select min(json_value (json_document, '$.PONumber' Returning number)) min_po,
max(json_value (json_document, '$.PONumber' Returning number)) max_po
from json_contents;
SQL> select json_query(json_document,'$.ShippingInstructions')
from json_contents
where json_value(json_document,'$.PONumber' returning number) = 1000;
SQL> select count(*) from json_contents
where json_exists(json_document, '$.ShippingInstructions.Address.zipCode');
• IS JSON Bedingung prüft auf Gültigkeit
Tabelle Laden und Indizieren
create table json_tab
(id number generated as identity,
json_document clob constraint ensure_json CHECK (json_document IS JSON));
insert into json_tab (json_document)
select json_document from json_contents;
Copyright © 2017, Oracle and/or its affiliates. All rights reserved.Oracle Technology Monthly | BU Core & Cloud Technologies 11
• Function Based Index für Tuning
select json_document from json_contents;
Commit;
create index ponumber_idx on json_tab (json_value(json_document, '$.PONumber'
returning number error on error));
Demo
Index Nutzung
Copyright © 2017, Oracle and/or its affiliates. All rights reserved.Oracle Technology Monthly | BU Core & Cloud Technologies 12
JSON in Tabellenform: Relationale Projektionen
Copyright © 2017, Oracle and/or its affiliates. All rights reserved.Oracle Technology Monthly | BU Core & Cloud Technologies 13
PURCHASETAB_VIEW
ponumber requestor special adresse
1 Martha Sullivan Surface Mail {"street":"200 Sporting Green","city“:
2 Trenna Rajs Courier {"street":"2014 Oxford Rd","city":…
... ... ... ...
Relationale Projektion mit JSON_TABLE
create or replace view purchasetab_view
as
select jt. ponumber, jt.requestor, jt.adresse, jt.special
from json_tab,
json_table (json_document, '$'
COLUMNS
( ponumber number PATH '$.PONumber'
Copyright © 2017, Oracle and/or its affiliates. All rights reserved.Oracle Technology Monthly | BU Core & Cloud Technologies 14
( ponumber number PATH '$.PONumber'
,requestor varchar2(32) PATH '$.Requestor'
,special varchar2(10) PATH '$."Special Instructions"'
,adresse varchar2(400) FORMAT JSON PATH '$.ShippingInstructions.Address')) jt
where json_value(json_document,'$.PONumber' returning number) < 5000;
SQL> select ponumber, adresse from purchasetab_view
where special='Air Mail' and requestor='Karen Partners';
JSON aus relationalen Daten
CUSTOMERS
{"custid":25451,CUST_ID CUST_LAST_NAME CUST_CITY
Copyright © 2017, Oracle and/or its affiliates. All rights reserved.Oracle Technology Monthly | BU Core & Cloud Technologies 15
"custid":25451,"name":“Everett","City":“Heilbronn“
}
CUST_ID CUST_LAST_NAME CUST_CITY
25451 Everett Heilbronn
25452 Odenwalld Heilbronn
... ... ...
• JSON_OBJECT macht aus Key Value Paaren JSON Objekte
JSON Code generieren - ganz einfach mit SQL
select json_object('custid' is CUST_ID,
'name' is CUST_LAST_NAME,
'City' is CUST_CITY) ausgabe
from customers
Copyright © 2017, Oracle and/or its affiliates. All rights reserved.Oracle Technology Monthly | BU Core & Cloud Technologies 16
• JSON_OBJECTAGG ist eine Aggregatsfunktionselect cust_city,
json_objectagg('name' is cust_last_name) cust_per_city
from customers
where cust_city like 'Co%'
group by cust_city
Ergebnis
CUST_CITY
------------------------------
CUST_PER_CITY
--------------------------------------------------------------------------------
Coburg
{"name":"Rice","name":"Tillman","name":"Barnett","name":"Zimmer","name":"Klesser
","name":"Ingersoll","name":"Bane","name":"Vankirk","name":"Lefevre","name":"Tri
mmer","name":"Smeed","name":"Lipp","name":"Klemm","name":"Obrien","name":"Kelley
Copyright © 2017, Oracle and/or its affiliates. All rights reserved.Oracle Technology Monthly | BU Core & Cloud Technologies 17
","name":"Titus","name":"Felt","name":"Joseph","name":"Colven","name":"Snodgrass
","name":"Rosenblum","name":"Burgess","name":"Kraft","name":"Krebs","name":"Zhao
","name":"Carr","name":"Kish","name":"Dwyer","name":"Eastwood","name":"Haole","n
ame":"Lamar","name":"Lengel","name":"Knox","name":"Mannings","name":"Curr","name
":"Jeffreys","name":"Lickey","name":"Ingold","name":"Goode","name":"Kelleher","n
ame":"Ladd","name":"Greenley","name":"Rush","name":"Gilmour","name":"Baley"}
Cochem
{"name":"Eden","name":"Pack","name":"Hale","name":"Newsome","name":"Calahan","na
me":"Lucy","name":"Newkirk","name":"Jewell","name":"Parks","name":"Rust","name":
"Sampler","name":"Stone","name":"Poindexter","name":"Geralt","name":"Wiser","nam
e":"Valentino","name":"Bakker"}
• JSON_ARRAYAGG ist eine Aggregatsfunktion und gibt ein JSON Array aus
JSON Code generieren - ganz einfach mit SQL
select json_object('country_name' is c.country_name,
'ctinfo' is
(select json_arrayagg(
json_object('name' is cust_last_name,
'status' is cust_marital_status,
Copyright © 2017, Oracle and/or its affiliates. All rights reserved.Oracle Technology Monthly | BU Core & Cloud Technologies 18
'status' is cust_marital_status,
'city' is cust_city,
'street' is cust_street_address absent on null)
absent on null)
from customers co
where co.country_id=c.country_id and cust_year_of_birth>1988)
absent on null) as ct1
from countries c;
Demo
Das Ergebnis - ein Ausschnitt
Copyright © 2017, Oracle and/or its affiliates. All rights reserved.Oracle Technology Monthly | BU Core & Cloud Technologies 19
Und jetzt zu unserem Beispiel
Suche nach einer bestimmten Bestellung
– Name des Auftraggebers ist nicht eindeutig
• Lösung: (Hierarchischer) Data Guide legt Strukturen an um
– Views zu erzeugen
Copyright © 2017, Oracle and/or its affiliates. All rights reserved.Oracle Technology Monthly | BU Core & Cloud Technologies 20
– Views zu erzeugen
– linguistisch zu suchen (Textindex)
– virtuelle Spalten zu erzeugen oder zu löschen
create search index JSON_TAB_GUIDE
on JSON_TAB (JSON_DOCUMENT)
for json PARAMETERS ('DATAGUIDE ON SYNC (ON COMMIT)')
Data Guide und JSON Strukturen
Copyright © 2017, Oracle and/or its affiliates. All rights reserved.Oracle Technology Monthly | BU Core & Cloud Technologies 21
Data Guide und relationale View Erzeugung
• Ausführung von DBMS_JSON.CREATE_VIEW_ON_PATH
execute dbms_json.create_view_on_path
(viewName => 'JSON_PO_VIEW',
tableName => 'JSON_TAB',
Copyright © 2017, Oracle and/or its affiliates. All rights reserved.Oracle Technology Monthly | BU Core & Cloud Technologies 22
tableName => 'JSON_TAB',
jcolname => 'JSON_DOCUMENT',
path => '$')
execute dbms_json.create_view_on_path
(viewName => 'JSON_PO_VIEW_PART',
tableName => 'JSON_TAB',
jcolname => 'JSON_DOCUMENT',
path => '$.ShippingInstructions.Address')
Relationale View
Copyright © 2017, Oracle and/or its affiliates. All rights reserved.Oracle Technology Monthly | BU Core & Cloud Technologies 23
(*) Spalten lassen sich umbenennen
Data Guide und Virtuelle Spalten
• Nutzung von DBMS_JSON.ADD_VIRTUAL_COLUMNS
• Auch automatisch mit CREATE SEARCH Index
execute dbms_json.add_virtual_columns (tableName => 'JSON_TAB',
jcolname => 'JSON_DOCUMENT',
Copyright © 2017, Oracle and/or its affiliates. All rights reserved.Oracle Technology Monthly | BU Core & Cloud Technologies 24
jcolname => 'JSON_DOCUMENT',
frequency => 100)
SQL> desc json_tab
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER
JSON_DOCUMENT CLOB
JSON_DOCUMENT$User VARCHAR2(8)
JSON_DOCUMENT$PONumber NUMBER
JSON_DOCUMENT$Reference VARCHAR2(32)
JSON_DOCUMENT$Requestor VARCHAR2(32)
Die (linguistischen) Abfragen
• Operatoren wie JSON_TEXTCONTAINS oder JSON_EXISTS verwenden
json_textcontains(json_document, '$.CostCenter', 'A40')
json_textcontains(json_document,'$','Sporting near green')
Copyright © 2017, Oracle and/or its affiliates. All rights reserved.Oracle Technology Monthly | BU Core & Cloud Technologies 25
json_textcontains(json_document,'$.ShippingInstructions','fuzzy(fransesco)')
json_exists(json_document,'$.ShippingInstructions.Address.zipCode')
json_textcontains(json_document, '$','near((United, America), 3)')
json_textcontains(json_document, '$.LineItems', '$tie')
json_textcontains(json_document, '$.Requestor', 'fuzzy(Sarat)')
Das Ergebnis
Copyright © 2017, Oracle and/or its affiliates. All rights reserved.Oracle Technology Monthly | BU Core & Cloud Technologies 26
Demo
Ausführungsplan
Copyright © 2017, Oracle and/or its affiliates. All rights reserved.Oracle Technology Monthly | BU Core & Cloud Technologies 27
Performance
• Definition von virtuellen Spalten
• Indizierung über
– Function Based Index mit CREATE INDEX und JSON_VALUE
– Volltext Index (Data Guide) mit CREATE SEARCH INDEX
Copyright © 2017, Oracle and/or its affiliates. All rights reserved.Oracle Technology Monthly | BU Core & Cloud Technologies 28
– Volltext Index (Data Guide) mit CREATE SEARCH INDEX
• JSON Optimierungen bei In-Memory Columnstore Verwendung
– Performancegewinn für Funktionen wie JSON_TABLE, JSON_VALUE oder JSON_EXISTS durch spezielles Binary Format
– Speicherung von virtuellen Spalten
• Monitoring über Ausführungspläne
Oracle Database Cloud Services
Wo ausprobieren?
Exadata Express Database - EC Database - BM Exadata
Copyright © 2017, Oracle and/or its affiliates. All rights reserved.Oracle Technology Monthly | BU Core & Cloud Technologies 29
Exadata Express Database - EC Database - BM Exadata
Editions EE with Lockdown SE2, EE, HP, EP SE2, EE, HP, EP EP
Implementation 1 PDB in Shared CDB 1 Dedicated CDB 1+ Dedicated DBs 1+ Dedicated DBs
Management Oracle Customer Customer Customer
Max DB size 5G, 20G, 50GB 50GB – 11.2TB 4.2TB – 9.6TB 42TB – Petabytes
CPU range ~1 1 – 16 2 – 36 16 – 272
Storage Exadata,Flash:Fixed ZFS Block: $0.50 per GB Local NVMe:Fixed Exadata,Flash:Fixed
LiveSQL.oracle.com
Wo ausprobieren?
• http://livesql.oracle.com
• SQL Scratch Pad in der Oracle Cloud
• Browser und OTN Account nötig
• Kostenlos
Copyright © 2017, Oracle and/or its affiliates. All rights reserved.Oracle Technology Monthly | BU Core & Cloud Technologies 30
• Kostenlos
• “Save und Share” von SQL Beispielen
• 12.2 Tutorials
Weitere Informationen
• Deutschsprachige Blogeinträge: blogs.oracle.com/dbacommunity_deutsch
– JSON und die Oracle Datenbank: Basisfunktionen
– JSON in 12.2: JSON Generierung, neues Data Guide Konzept, Objekttypen
• Textblog: http://oracle-text-de.blogspot.co.uk/
Artikel im Oracle Magazine: JSON in Motion
Copyright © 2017, Oracle and/or its affiliates. All rights reserved.Oracle Technology Monthly | BU Core & Cloud Technologies 31
• Artikel im Oracle Magazine: JSON in Motion
• Handbücher wie Database JSON Developer's Guide
• Dojos: http://tinyurl.com/dojoonline
• Kontakt: [email protected] @uschwinn
Copyright © 2017, Oracle and/or its affiliates. All rights reserved.Oracle Technology Monthly | BU Core & Cloud Technologies 32