OracleDB-Native-JSON动手实验

1. 实验说明

Oracle Database 12c Release 1开始已具备JSON处理能力；在此基础上 Oracle Database 21c新增了一个 JSON 的数据类型，让Oracle Database存储与处理JSON数据能力更强大 。本实验将用Oracle 23c Beta的虚拟机镜像，带领大家了解Oracle Database JSON相关的基本用法。

23c镜像试用申请说明

Oracle 23c Beta的虚拟机镜像为试用镜像，使用前请向甲骨文申请，地址 https://pdpm.oracle.com/pls/apex/f?p=108:158:11891478510322:::::

相关账号密码

Linux账号:

数据库账号:

数据库服务:

相关端口

注意：请确保宿主主机端口不冲突

2. 准备工作

准备工具清单

• 电脑：非苹果M1 CPU的电脑
• 虚拟机：VirtualBox
• 虚拟机系统镜像： Oracle23c Lab镜像
• (可选)SSH终端：putty 或其他。
• (可选)Oracle DB终端：SQL Developer

Step1. 安装VirtualBox

a) 下载VirtualBox

进入下载页面，根据自己的硬件及操作系统选择合适的虚拟机版本 https://www.virtualbox.org/wiki/Downloads

b) 安装VirtualBox

下载完成后，双击安装

Step2. 导入Oracle23c Lab镜像

试用前请先申请镜像，详情见“准备工作”

a) 下载镜像

使用下面的链接，下载VirtualBox的虚拟机镜像 ​https://oradocs.oracle.com/documents/fileview/D5266F7E17C3E955157F3572021C1BB7EF6CC5015FE2/_Oracle_Database_23c_Beta_1.ova

b) 导入镜像

下载完成后，双击导入镜像，可以根据自己的配置情况修改CPU和内存分配大小。


注意：

如果安装过Hyper-V，或者Docker（依赖Hyper-V)，需要把hyper-V关掉，否则启动会失败。

方法a) 用超级管理员权限打开宿主主机的cmd，执行以下命令：

#Using CMD as Administrator
bcdedit /set hypervisorlaunchtype off
DISM /Online /Disable-Feature:Microsoft-Hyper-V

完成后，重启宿主主机。

方法b) 在导入成功后修改“半虚拟化接口”选项为“Hyper-V”或“最小”，这样不必关闭Hyper-V（运行一些软件会出错，如MongoDB Server，说明。本实验涉及MongoDB Server，只能用方法a) 不能用方法b）：


c) 登录虚拟机

导入成功后，直接启动虚拟机。开机后，虚拟机的端口映射如下：详细信息如下：

#登录虚拟机
ssh oracle@localhost -p 2231

Step3（可选）. 安装Putty

下载地址：

https://www.putty.be/latest.html

安装完成后输入登录信息127.0.0.1和端口2231，可以打开。用户名oracle，密码oracle


注意，虚拟机内端口22，虚拟机外端口2231

Step4（可选）. 安装SQL Developer

下载地址, 最好是选带有 With JDK 11 Included 的版本：

https://www.oracle.com/database/sqldeveloper/technologies/download/

安装完成后输入登录信息，用户名roger, 密码 password, 主机名 localhost, 端口1523，服务名ORCLPDB1：


注意，虚拟机内端口1521，虚拟机外端口1523

Lab1. 存储多应用的JSON数据

Step1. 启动应用程序

启动Kafka及4个微服务应用程序

a) 启动Kafka

~/kafka/kafka_2.12-3.3.1/bin/zookeeper-server-start.sh -daemon ~/kafka/kafka_2.12-3.3.1/config/zookeeper.properties

#等半分钟，等zookeeper启动

~/kafka/kafka_2.12-3.3.1/bin/kafka-server-start.sh -daemon ~/kafka/kafka_2.12-3.3.1/config/server.properties

b) 编写汇聚应用

汇聚应用从kafka中读取消息，内容包括集合名称、数据正文，这些内容由Kafka消息生产者决定，汇聚应用将内容写入OracleDB时并不清楚具体的集合名称和数据结构。

这时 Oracle Database Native JSON将发挥作用。当使用JSON存储数据时，无需知道具体的数据结构，都可以存进数据库。

cd ~/native-json-labs
vim ecom_convergence.py

#pip install kafka-python
from kafka import KafkaConsumer
import json
#pip install cx_Oracle
import cx_Oracle
#windows 需指定cx_Oracle.init_oracle_client(lib_dir=r"D:\\OraclePySDK\\instantclient_21_7")，linux不用

connection = cx_Oracle.connect(user="roger", password="password", dsn="localhost:1521/orclpdb1")
soda = connection.getSodaDatabase()
connection.autocommit = True

collectionMap = {}

#保存文档，这里并不知道具体的集合名及文档内容，均由消息生产者决定
def saveDoc(colName, doc):
  if colName in collectionMap:
    collection = collectionMap[colName]
  else:
    collection = soda.createCollection(colName)
    collectionMap[colName] = collection
  returned_doc = collection.insertOneAndGet(doc)
  key = returned_doc.key
  print(f"保存 {key} 到集合 {colName}，数据：{str(doc)}")

consumer = KafkaConsumer('json-topic',bootstrap_servers=['127.0.0.1:9092']) 
print("启动成功，正在监听Kafka消息")
for message in consumer:  # consumer是一个消息队列，当后台有消息时，这个消息队列就会自动增加．所以遍历也总是会有数据，当消息队列中没有数据时，就会堵塞等待消息带来
    #print("%s:%d:%d: key=%s value=%s" % (message.topic, message.partition,message.offset, message.key,message.value))
    playload = json.loads(message.value)
    saveDoc(playload["colName"], playload["doc"])

启动汇聚程序，新建一个SSH窗口，运行如下命令：

python3 ecom_convergence.py

这时，python将监听Kafka消息，并将消息存入数据库

c) 启动微服务应用

新建4个SSH窗口，分别运行4个微服务应用及一个Kafka汇聚应用

#订单
cd ~/native-json-labs
python3 ecom_order.py

#埋点
cd ~/native-json-labs
python3 ecom_tracking.py

#商品
cd ~/native-json-labs
python3 ecom_product.py

#评论
cd ~/native-json-labs
python3 ecom_review.py

下面是评论微服务ecom_review.py产生的随机评论：


这时，切换到汇聚应用ecom_convergence.py的SSH窗口，可以看到汇聚的应用，可以看到4个微服务产生的对象都被存入了数据库：


看看数据库效果：

评论表：


产品表：


数据库中有新数据写入。到此，可以把ecom_convergence.py、ecom_order.py、ecom_tracking.py、ecom_product.py、ecom_review.py 按ctrl+c停掉

Step3. 查看JSON数据结构定义

Native JSON 的字段类型为 JSON

select t.table_name 表名,
c.comments 字段名称,
t.column_name 字段编码,
t.data_type || '(' || to_char(t.data_length) || ')' 类型
FROM user_tab_cols t, user_col_comments c
WHERE c.table_name = t.table_name
and c.column_name = t.column_name
and t.hidden_column = 'NO'
and c.TABLE_NAME In ('ECOM_TRACKING')
order by c.TABLE_NAME, t.column_name;


或者用SQL Developer自带的工具查看表结构，在表上右键，选 编辑，可以看到字段类型为JSON



Lab2. 通过数据库终端操作JSON数据

Step1. 查询JSON数据

a) 以JSON文档形式输出

select json_serialize(json_document)
from ECOM_TRACKING
where rownum <3;


b) 以表格形式输出，直接使用JSON路径

select 
  t.id,
  t.json_document.track_no.string(),
  t.json_document.product_id.string(),
  t.json_document.user_name.string(),
  t.json_document.activity_name.string(),
  t.json_document.action.path_level.number(),
  t.json_document.action.page.string(),
  t.json_document.retention.number(),
  t.json_document.add_time.string()
from ECOM_TRACKING t
where rownum <3;


c) 以表形式输出，使用json_table进行表定义

select t.id, jt.* 
from ECOM_TRACKING t,
json_table(t.json_document, '$' ERROR ON ERROR NULL ON EMPTY
              COLUMNS (
                track_id       VARCHAR2(64)         PATH '$.id',
                track_no       VARCHAR2(64)   PATH '$.track_no',
                user_name      VARCHAR2(64)         PATH '$.user_name',
                activity_name       VARCHAR2(64)   PATH '$.activity_name',
                path_level       NUMBER         PATH '$.action.path_level',
                page       VARCHAR2(64)   PATH '$.action.page',
                retention       NUMBER         PATH '$.retention',
                add_time       VARCHAR2(64)   PATH '$.add_time'
              )
) jt
where rownum <3;


创建实体化视图

drop MATERIALIZED VIEW mv_test;
CREATE MATERIALIZED VIEW mv_test
  BUILD IMMEDIATE
  REFRESH FAST ON STATEMENT WITH PRIMARY KEY
  AS 
  select t.id, jt.* 
from ECOM_TRACKING t,
json_table(t.json_document, '$' ERROR ON ERROR NULL ON EMPTY
              COLUMNS (
                track_id       VARCHAR2(64)         PATH '$.id',
                track_no       VARCHAR2(64)   PATH '$.track_no',
                user_name      VARCHAR2(64)         PATH '$.user_name',
                activity_name       VARCHAR2(64)   PATH '$.activity_name',
                path_level       NUMBER         PATH '$.action.path_level',
                page       VARCHAR2(64)   PATH '$.action.page',
                retention       NUMBER         PATH '$.retention',
                add_time       VARCHAR2(64)   PATH '$.add_time'
              )
) jt;
select * from mv_test;


Step2. 修改JSON数据

Native JSON支持只更新部分数据，避免更新整个JSON文档带来的额外消耗。

UPDATE 
  ECOM_TRACKING t 
SET  
  t.json_document = json_mergepatch(t.json_document, '{"user_name":"user-233"}') 
where t.json_document.product_id.string()='product-59';

Lab3. 应用通过SODA操作JSON数据

Step 1. SODA自动创建JSON表

不论是使用 API for MongoDB 还是使用SODA， 类似于常规文档数据库自动创建Collection集合的功能，Oracle Database能自动为JSON文档创建表。

a) 确认表不存在

先删除表，确保我们的数据库中不存在 表 ECOM_TRACKING_AUTO_CREATE

drop TABLE SODA_AUTO_CREATE;
drop TABLE SODA_AUTO_CREATE;

--这条Select命令用于清除JSON集合的元数据信息
select dbms_soda.drop_collection('SODA_AUTO_CREATE') from dual;
select dbms_soda.drop_collection('SODA_CRUD_COL') from dual;

--结果为空，表不存在
select table_name from user_tables where TABLE_NAME ='SODA_AUTO_CREATE';


b) 启动测试应用

cd ~/native-json-labs
vim soda_auto_create.py

输入以下代码

import random 
import cx_Oracle

#连接数据库
connection = cx_Oracle.connect(user="roger", password="password", dsn="localhost:1521/orclpdb1")
connection.autocommit = True
soda = connection.getSodaDatabase()
#创建集合
collection = soda.createCollection("SODA_AUTO_CREATE")


#新增文档
doc = {
  'product_name': "oracle-cup-0",
  'price': 10,
}
returned_doc = collection.insertOneAndGet(doc)
key = returned_doc.key

#根据KEY查询文档
respDoc = collection.find().key(key).getOne().getContent()
print(f"插入单条后，根据返回的KEY {key} 查询文档:")
print(respDoc)

运行测试脚本

python3 soda_auto_create.py


c) 查看表是否自动创建

可以看到，当应用程序运行时，表创建成功了。

select table_name from user_tables where TABLE_NAME ='SODA_AUTO_CREATE';


select t.table_name 表名,
c.comments 字段名称,
t.column_name 字段编码,
t.data_type || '(' || to_char(t.data_length) || ')' 类型
FROM user_tab_cols t, user_col_comments c
WHERE c.table_name = t.table_name
and c.column_name = t.column_name
and t.hidden_column = 'NO'
and c.TABLE_NAME In ('SODA_AUTO_CREATE')
order by c.TABLE_NAME, t.column_name;


Step2. 通过SODA进行增删改查

创建文件

vim soda-crud-demo.py

输入以下脚本

import random 
import cx_Oracle
#cx_Oracle.init_oracle_client(lib_dir=r"D:\\OraclePySDK\\instantclient_21_7")

#连接数据库
connection = cx_Oracle.connect(user="roger", password="password", dsn="localhost:1521/orclpdb1")
connection.autocommit = True
soda = connection.getSodaDatabase()
#创建集合
collection = soda.createCollection("soda_crud_col")


#新增文档
doc = {
  'product_name': "oracle-cup-0",
  'price': 10,
}
returned_doc = collection.insertOneAndGet(doc)
key = returned_doc.key

#根据KEY查询文档
respDoc = collection.find().key(key).getOne().getContent()
print(f"插入单条后，根据返回的KEY {key} 查询文档:")
print(respDoc)

#根据KEY更新文档
doc = {
  'product_name': "oracle-cup-100",
  'price': 20
}
collection.find().key(key).replaceOne(doc)

#根据KEY查询文档
respDoc = collection.find().key(key).getOne().getContent()
print(f"修改成绩后，根据返回的KEY {key} 查询文档:")
print(respDoc)

#删除单条
collection.find().key(key).remove()

#查询个数
remainCount = collection.find().key(key).count()
print(f"删除后，还剩{remainCount}个文档")

#批量新增文档
docs = []
for i in range(1,100):
  i = random.randrange(50)
  doc = {
    'product_name': "oracle-cup-"+str(i),
    'price': 10+i,
  }
  docs.append(doc)
collection.insertMany(docs)

#分页查询
print("批量插入后，搜索第3~6条数据")
respList = collection.find().skip(2).limit(3).getDocuments()
for respDoc in respList:
    print(respDoc.getContent())

#清空集合
collection.truncate()
print("集合已清空")

执行脚本

python3 soda-crud-demo.py


Lab4. 业务分析

ORACLE 拥有强大的SQL与JSON分析能力。以下的步骤中，展示了电商场景进行业务分析，涉及的表为：

• ECOM_ORDER ：订单表
• ECOM_ORDER_ITEM ：订单明细表，存有买了哪些商品以及价格
• ECOM_TRACKING：埋点表，记录了用户的页面访问记录，，并记录了客户是从什么营销渠道进来的
• ECOM_PRODUCT: 商品表，记录有商品的类别等信息
• ECOM_REVIEW：客户评价表，Score最大为5星好评，最差为1星差评。经过AI分析已经得到评价的主体Subject和评价的关键词，主题和关键词都存入了 ECOM_REVIEW.keywords中

实验步骤可以总结为：

1. 将JSON集合转成实体化视图，便于SQL查询
2. 为实体化视图添加索引,加速分析能力
3. 制作一些常用的中间视图
4. 通过SQL快速、方便得到业务分析结果

Step1. 营销活动分析

通过 ECOM_TRACKING 访问信息、ECOM_ORDER 、ECOM_ORDER_ITEM 订单信息，我们可以分析出哪个渠道的广告投放更合理，及用户点击页面的过程中，在哪些页面可能遇到有困难。

a) ECOM_TRACKING实体化视图

drop  MATERIALIZED VIEW mv_ecom_tracking;
CREATE MATERIALIZED VIEW mv_ecom_tracking
  BUILD IMMEDIATE
  REFRESH FAST ON STATEMENT WITH PRIMARY KEY
  AS 
  select t.id, jt.* 
from ECOM_TRACKING t,
json_table(t.json_document, '$' ERROR ON ERROR NULL ON EMPTY
              COLUMNS (
                track_id       VARCHAR2(64)         PATH '$.id',
                track_no       VARCHAR2(64)   PATH '$.track_no',
                product_id       VARCHAR2(64)   PATH '$.product_id',
                user_name      VARCHAR2(64)         PATH '$.user_name',
                activity_name       VARCHAR2(64)   PATH '$.activity_name',
                path_level       NUMBER         PATH '$.action.path_level',
                page       VARCHAR2(64)   PATH '$.action.page',
                retention       NUMBER         PATH '$.retention',
                add_time       VARCHAR2(64)   PATH '$.add_time'
              )
) jt;

select * from mv_ecom_tracking;

添加索引能让分析更快

CREATE INDEX mv_ecom_tracking_idx ON mv_ecom_tracking(track_no, path_level, page);


b) 创建频繁用到的分析视图

CREATE or replace VIEW v_ecom_market_analysis
  AS
select 
  t.track_no as track_no,
  t.product_id as product_id,
  t.user_name as user_name,
  t.activity_name as activity_name,
  max(t.path_level) as path_level,
  LISTAGG(t.page, '->') WITHIN GROUP(ORDER BY t.path_level) AS pages,
  sum(t.retention) as retention,
  max(t.add_time) as add_time,
  o.status as order_status,
  max(oi.price) as order_price
from 
   mv_ecom_tracking t 
   left join ECOM_ORDER o on t.track_no=o.track_no 
   left join ECOM_ORDER_ITEM oi on oi.order_id = o.id
group by t.track_no,t.product_id,t.user_name,t.activity_name,o.status;

select * from v_ecom_market_analysis;


c) 页面留存率情况

select 
   activity_name, 
   pages, 
   sum(order_price), 
   (select count(*) from v_ecom_market_analysis where activity_name=a.activity_name and  instr(pages, a.pages)>0) as remainPeople, 
    round((select count(*) from v_ecom_market_analysis where activity_name=a.activity_name and  instr(pages, a.pages)>0)/(select count(*) from v_ecom_market_analysis where activity_name=a.activity_name)*100, 2) as remainRate
from v_ecom_market_analysis a
group by activity_name , pages, path_level
order by activity_name, path_level asc;


Step2. 产品问题分析

通过分析ECOM_REVIEW 客户评价表中的差评，找出ECOM_PRODUCT 商品表的同一类目出现频率比较多的关键词，这些关键词往往就是产品的问题所在，有助于帮助产品设计部门或其他部门改善产品。

a) 创建评论实体化视图

drop  MATERIALIZED VIEW mv_ecom_review;
CREATE MATERIALIZED VIEW mv_ecom_review
  BUILD IMMEDIATE
  REFRESH FAST ON STATEMENT WITH PRIMARY KEY
  AS 
select r.id, rt.* 
from ECOM_REVIEW r,
json_table(r.json_document, '$' ERROR ON ERROR NULL ON EMPTY
              COLUMNS (
                review_id       VARCHAR2(64)         PATH '$.id',
                product_id       VARCHAR2(64)   PATH '$.product_id',
                user_name      VARCHAR2(64)         PATH '$.user_name',
                score       NUMBER         PATH '$.score',
                title       VARCHAR2(128)   PATH '$.title',
                content       VARCHAR2(256)         PATH '$.content',
                NESTED PATH '$.keywords[*]'
                  COLUMNS (keyword VARCHAR2(24)  PATH '$')
              )
) rt;

select * from mv_ecom_review;

添加索引，让分析更快

CREATE INDEX mv_ecom_review_idx ON mv_ecom_review(product_id, score, keyword);


b) 创建产品实体化视图

drop  MATERIALIZED VIEW mv_ecom_product;
CREATE MATERIALIZED VIEW mv_ecom_product
  BUILD IMMEDIATE
  REFRESH FAST ON STATEMENT WITH PRIMARY KEY
  AS 
SELECT
    p.id,
    pt.product_id,
    pt.product_name,
    pt.product_class
FROM
    ECOM_PRODUCT p,
    json_table(p.json_document, '$' ERROR ON ERROR NULL ON EMPTY
        COLUMNS (
            product_id VARCHAR2 ( 64 ) PATH '$.id',
            product_name VARCHAR2 ( 256 ) PATH '$.name',
            product_class VARCHAR2 ( 64 ) PATH '$.class'
        )
    ) pt;
    
select * from mv_ecom_product;

添加索引，让分析更快

CREATE INDEX mv_ecom_product_idx ON mv_ecom_product(product_id, product_class);


c) 分析产品问题

select 
  p.product_class,
  r.keyword,
  count(*) as cnt
from 
  mv_ecom_review r 
  left join mv_ecom_product p on r.product_id=p.product_id
where r.score < 3
group by p.product_class, r.keyword
order by cnt desc