Analytics and Data Summit 2020 Analytics and Data Summit 2020 - PDF document

Analytics and Data Summit 2020

Analytics and Data Summit 2020

Analytics and Data Summit 2020

Analytics and Data Summit 2020 You are still ill in in tim ime to change room

Analytics and Data Summit 2020

Analytics and Data Summit 2020

Analytics and Data Summit 2020 450+ Technical Experts Helping Peers Globally bit.ly/OracleACEProgram Nominate yourself or someone you know: acenomination.oracle.com

Analytics and Data Summit 2020

Analytics and Data Summit 2020 A Pro roperty Gra raph Ve Verte tex (a (als lso call called nod node) edge

Analytics and Data Summit 2020 edge ed ver vertex (no (node) ed edge ID ID dir irected ed edge edge pr ed properti ties vertex ver pr properti ties edge lab ed abel ver vertex ID ID a ver a vertex ca can hav ave a a lab abel

Analytics and Data Summit 2020

Analytics and Data Summit 2020 Every row of a table has a fixed, identical structure Nodes and edges can have any number of properties Connections at a table level (not row) Connections at a node level (can be seen as row level)

Analytics and Data Summit 2020 PGX R Integration (OAAgraph) Graph Analytics Python, Perl, PHP, Ruby, Spark integration In-memory Analytics Engine REST Web Service Visualization Javascript, … Java APIs Graph Storage Management Java APIs/JDBC/SQL/PLSQL Scalable and Persistent Storage

Analytics and Data Summit 2020

Analytics and Data Summit 2020 Catalog RPD From 45,700 nodes with 105,406 edges, to 85 nodes with 218 edges in seconds

Analytics and Data Summit 2020 John Doe Money laundering and VAT frauds Owns Buys from Company B Company A Buys from Buys from Company D Located in Company C Located in Buys from Located in Located in Italy Spain

Analytics and Data Summit 2020

Analytics and Data Summit 2020 Client (Shell UI, Zeppelin, Graph Viz) Server Client and (Shell UI, Zeppelin, Graph Client Viz) Server PGX Package PGX Server: deployed and Run PGQL queries standalone, Client in WLS, or and graph analytics Package Tomcat PGQL Queries run in database Load graph Run queries in into memory database Three tier Two tier

Analytics and Data Summit 2020 Load from standard tables 1 into PGX (new feature) PGX Server Load into Property Graph 2a schema (required for 1 2b running PGQL in the database) Load from Property Graph Edge table Vertex table 2b schema into PGX 2a Property Graph schema Database tables

Analytics and Data Summit 2020

Analytics and Data Summit 2020 Username and password are case sensitive!

Analytics and Data Summit 2020 Username and password are case sensitive!

Analytics and Data Summit 2020

Analytics and Data Summit 2020 • • • • • •

Analytics and Data Summit 2020

Analytics and Data Summit 2020 Simplif lified ver version • • • • • •

Analytics and Data Summit 2020 Ref eference Product Order Con onfirm Cou ountry Customer Liv LivesIn In

Analytics and Data Summit 2020 Ref eference Product Order Co Confir irm Customer Country= “<country>”

Analytics and Data Summit 2020 Co Confir irms Customer Order Country= “<country>” Product= “<product>”

Analytics and Data Summit 2020 ??? ??? ??? ??? ??? ??? ??? ???

Analytics and Data Summit 2020

Analytics and Data Summit 2020

Analytics and Data Summit 2020

Analytics and Data Summit 2020 Buy uys Product Customer Quantity= “” Amount= “” OrderDate= “” Liv LivesIn In Cou ountry Let’s implement this one

Analytics and Data Summit 2020

Analytics and Data Summit 2020 • Graph Analytics • R Integration (OAAgraph) In-memory Analytics Engine Python, Perl, PHP, Ruby, Spark integration REST Web Service • Visualization Javascript, … • Java APIs Graph Storage Management Java APIs/JDBC/SQL/PLSQL Scalable and Persistent Storage •

Analytics and Data Summit 2020 • • BEGIN OPG_APIS.CREATE_PG('name_of_your_graph'); END;

Analytics and Data Summit 2020 •

Analytics and Data Summit 2020

Analytics and Data Summit 2020 Name Null? Type Name Null? Type ----- -------- ----------------------- ------- -------- ------------------------ VID NOT NULL NUMBER EID NOT NULL NUMBER VL NVARCHAR2(3100) SVID NOT NULL NUMBER K NVARCHAR2(3100) DVID NOT NULL NUMBER T INTEGER EL NVARCHAR2(3100) V NVARCHAR2(15000) K NVARCHAR2(3100) VN NUMBER T INTEGER VT TIMESTAMP WITH TIMEZONE V NVARCHAR2(15000) VN NUMBER VT TIMESTAMP WITH TIME ZONE 47 name: Matthew McConaughey [T=1] admires age: 47 [T=2] weight:1.0 [T=4] birth-date:1969-11-04 12:00:00.0 [T=5] 46 47 1102

Analytics and Data Summit 2020 ID Data type Column 1 String V • 2 Integer VN • 3 Float VN • 4 Double VN • 5 Date VT 6 Boolean V 7 Long VN 8 Short VN 9 Byte VN 10 Char V 101 Serializable V

Analytics and Data Summit 2020 • •

Analytics and Data Summit 2020

Analytics and Data Summit 2020 Example le

Analytics and Data Summit 2020

Analytics and Data Summit 2020 BEGIN OPG_APIS.CREATE_PG('mysales'); END;

Analytics and Data Summit 2020

Analytics and Data Summit 2020 SELECT 'customer ID' as id, MIN(cust_id) as min_id, MAX(cust_id) as max_id, COUNT(DISTINCT cust_id) as unique_ids, COUNT(*) as nrows FROM sh.customers UNION ALL SELECT 'product ID', MIN(prod_id), MAX(prod_id), COUNT(DISTINCT prod_id), COUNT(*) FROM sh.products UNION ALL SELECT 'country ID', MIN(country_id), MAX(country_id), COUNT(DISTINCT country_id), COUNT(*) FROM sh.countries; SELECT 'customer ID' as id, MIN(cust_id) as min_id, MAX(cust_id) as max_id, COUNT(DISTINCT cust_id) as unique_ids, COUNT(*) as nrows FROM sh.customers UNION ALL SELECT 'product ID', MIN(prod_id + 200000), MAX(prod_id + 200000), COUNT(DISTINCT prod_id), COUNT(*) FROM sh.products UNION ALL SELECT 'country ID', MIN(country_id + 300000), MAX(country_id + 300000), COUNT(DISTINCT country_id), COUNT(*) FROM sh.countries;

Analytics and Data Summit 2020 SELECT country_id + 300000 as vid, 'label' as k, 1 as t, 'country' as v, NULL as vn, NULL as vt FROM sh.countries UNION ALL SELECT country_id + 300000 as vid, 'name' as k, 1 as t, country_name as v, NULL as vn, NULL as vt FROM sh.countries UNION ALL SELECT country_id + 300000 as vid, 'isoCode' as k, 1 as t, country_iso_code as v, NULL as vn, NULL as vt FROM sh.countries UNION ALL SELECT country_id + 300000 as vid, 'sourceId' as k, 2 as t, TO_CHAR(country_id) as v, country_id as vn, NULL as vt FROM sh.countries ORDER BY 1, 2;

Analytics and Data Summit 2020 INSERT INTO mysalesvt$ (vid, k, t, v, vn, vt) SELECT country_id + 300000 as vid, 'label' as k, 1 as t, 'country' as v, NULL as vn, NULL as vt FROM sh.countries UNION ALL SELECT country_id + 300000 as vid, 'name' as k, 1 as t, country_name as v, NULL as vn, NULL as vt FROM sh.countries UNION ALL SELECT country_id + 300000 as vid, 'isoCode' as k, 1 as t, country_iso_code as v, NULL as vn, NULL as vt FROM sh.countries UNION ALL SELECT country_id + 300000 as vid, 'sourceId' as k, 2 as t, TO_CHAR(country_id) as v, country_id as vn, NULL as vt FROM sh.countries ORDER BY 1, 2;

Analytics and Data Summit 2020 SELECT prod_id + 200000 as vid, 'label' as k, 1 as t, 'product' as v, NULL as vn, NULL as vt FROM sh.products UNION ALL SELECT prod_id + 200000 as vid, 'name' as k, 1 as t, prod_name as v, NULL as vn, NULL as vt FROM sh.products UNION ALL SELECT prod_id + 200000 as vid, 'category' as k, 1 as t, prod_category as v, NULL as vn, NULL as vt FROM sh.products UNION ALL SELECT prod_id + 200000 as vid, 'subcategory' as k, 1 as t, prod_subcategory as v, NULL as vn, NULL as vt FROM sh.products UNION ALL SELECT prod_id + 200000 as vid, 'listPrice' as k, 3 as t, TO_CHAR(prod_list_price) as v, prod_list_price as vn, NULL as vt FROM sh.products UNION ALL SELECT prod_id + 200000 as vid, 'sourceId' as k, 2 as t, TO_CHAR(prod_id) as v, prod_id as vn, NULL as vt FROM sh.products ORDER BY 1,2;

Analytics and Data Summit 2020 INSERT INTO mysalesvt$ (vid, k, t, v, vn, vt) SELECT prod_id + 200000 as vid, 'label' as k, 1 as t, 'product' as v, NULL as vn, NULL as vt FROM sh.products UNION ALL SELECT prod_id + 200000 as vid, 'name' as k, 1 as t, prod_name as v, NULL as vn, NULL as vt FROM sh.products UNION ALL SELECT prod_id + 200000 as vid, 'category' as k, 1 as t, prod_category as v, NULL as vn, NULL as vt FROM sh.products UNION ALL SELECT prod_id + 200000 as vid, 'subcategory' as k, 1 as t, prod_subcategory as v, NULL as vn, NULL as vt FROM sh.products UNION ALL SELECT prod_id + 200000 as vid, 'listPrice' as k, 3 as t, TO_CHAR(prod_list_price) as v, prod_list_price as vn, NULL as vt FROM sh.products UNION ALL SELECT prod_id + 200000 as vid, 'sourceId' as k, 2 as t, TO_CHAR(prod_id) as v, prod_id as vn, NULL as vt FROM sh.products ORDER BY 1,2;