OracleDB-API-for-MongoDB动手实验

1. 实验说明

Oracle Database 19c开始，Oracle数据库已具备MongoDB API能力，能很好的兼容MongoDB的应用。本实验将用Oracle 23c Beta的虚拟机镜像，带领大家了解Oracle Database API for MongoDB的基本用法。

23c镜像试用申请说明

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

数据说明

数据来源于共享单车配送信息

https://github.com/MobilityData/gbfs/blob/master/gbfs.md

相关账号密码

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

完成后，重启宿主主机。

恢复方法如下

bcdedit /set hypervisorlaunchtype auto
DISM /Online /Enable-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. MongoDB迁移到OracleDB

将数据从MongoDB迁移到OracleDB涉及mangoexport 和mongoimport工具

Step1. 从MongoDB中导出数据

export MONGO_URI=mongodb://localhost:27018/admin
mkdir ~/mongo-api-lab
cd ~/mongo-api-lab
mongoexport -u admin -p password --collection station --uri=$MONGO_URI -o station.json
mongoexport -u admin -p password --collection status --uri=$MONGO_URI -o status.json


Step2. 把数据导入Oracle DB

mongoimport --tlsInsecure --uri='mongodb://roger:password@localhost:27017/roger?authMechanism=PLAIN&authSource=$external&ssl=true&retryWrites=false&loadBalanced=true' --collection station --file=station.json
mongoimport --tlsInsecure --uri='mongodb://roger:password@localhost:27017/roger?authMechanism=PLAIN&authSource=$external&ssl=true&retryWrites=false&loadBalanced=true' --collection status --file=status.json


看结果

mongosh --tlsAllowInvalidCertificates 'mongodb://roger:password@localhost:27017/roger?authMechanism=PLAIN&authSource=$external&ssl=true&retryWrites=false&loadBalanced=true'
db.station.find({_id:'120'})


Lab2. MongoDB客户端 连接Oracle Database

Step1. 用mongosh连接OracleDB

mongosh --tlsAllowInvalidCertificates 'mongodb://roger:password@127.0.0.1:27017/roger?authMechanism=PLAIN&authSource=$external&ssl=true&retryWrites=false&loadBalanced=true'

show collections


Step2. 增删查改

db.station.find({_id:'120'})
db.station.updateOne({_id:'120'}, {$set:{capacity:27}});
db.station.find({_id:'120'});


db.station.remove({_id:'120'});
db.station.find({_id:'120'});
db.station.insert({
    _id: '120',
    station_type: 'classic',
    electric_bike_surcharge_waiver: false,
    eightd_has_key_dispenser: false,
    has_kiosk: true,
    rental_methods: [ 'KEY', 'CREDITCARD' ],
    lat: 40.68676793,
    eightd_station_services: [],
    lon: -73.95928168,
    legacy_id: '120',
    region_id: '71',
    capacity: 24,
    name: 'Lexington Ave & Classon Ave',
    external_id: '66db29e6-0aca-11e7-82f6-3863bb44ef7c',
    short_name: '4452.03',
    rental_uris: {
      ios: 'https://bkn.lft.to/lastmile_qr_scan',
      android: 'https://bkn.lft.to/lastmile_qr_scan'
    }
  }
);

#退出
quit


Lab3. 通过应用程序操作数据

这是一个Spring Boot的微服务，集成了Spring-data-mongo-starter，可以通过HTTP操作MongoDB，开源地址如下：

https://github.com/oracle/json-in-db/tree/master/MongoExamples/SpringBoot

Step1. 启动应用

运行程序

cd ~/mongo-api-lab/json-in-db-master/MongoExamples/SpringBoot/

java -jar ./target/bikes-0.0.1.jar \
--spring.data.mongodb.uri='mongodb://roger:password@localhost:27017/roger?authMechanism=PLAIN&authSource=$external&ssl=true&retryWrites=false&loadBalanced=true&tlsInsecure=true' \
   --spring.data.mongodb.database=roger --server.port=8081 


Step2. 通过应用进行增删查改

测试应用连接ORDS + OracleDB 效果

新开一个SSH窗口，通过应用进行增删改查

a) 新增

curl -i -X POST -H "Content-Type:application/json" http://localhost:8081/station -d '{
    "_id":"120",
    "name": "Lexington Ave & Classon Ave",
    "region_id": "71",
    "lon": -73.95928168,
    "lat": 40.68676793,
    "eightd_has_key_dispenser": false,
    "legacy_id": "120",
    "rental_methods": [
        "KEY",
        "CREDITCARD"
    ],
    "external_id": "66db29e6-0aca-11e7-82f6-3863bb44ef7c",
    "capacity": 24,
    "short_name": "4452.03",
    "electric_bike_surcharge_waiver": false,
    "station_type": "classic",
    "eightd_station_services": [],
    "has_kiosk": true,
    "rental_uris": {
        "ios": "https://bkn.lft.to/lastmile_qr_scan",
        "android": "https://bkn.lft.to/lastmile_qr_scan"
    }
}'


b) 查询

curl http://localhost:8081/station/120
curl  http://localhost:8081/status/search/findByStationId/?id=120 



c) 修改

curl -i http://localhost:8081/station/120
curl -X PUT -H "Content-Type:application/json" http://localhost:8081/station/120 -d '{
    "capacity": 29
}'


d) 删除

curl -i -X DELETE http://localhost:8081/station/120
curl -i http://localhost:8081/station/120


Lab4. SQL 与 JSON 相结合

Step1. 登录Oracle DB

在虚拟机中用SQL Plus或宿主主机中用SQL Developer登录均可

sqlplus roger/password@127.0.0.1:1521/ORCLPDB1

Step2. SQL结合JSON进行分析工作

a) 数据准备(可选步骤)

为了让结果更具意义，这里修改了一些数据：

delete from tb_station_status s where station_id in ('72', '79','82');
insert into tb_station_status (num_docks_available,eightd_has_available_keys,num_ebikes_available,is_returning,num_bikes_disabled,station_id,num_bikes_available,station_status,is_installed,num_docks_disabled,last_reported,is_renting,legacy_id) values 
(8,  'false',  1,  1,  3,  '79',  21,  'active',  1,  0,  1668494676,  1,  '79'),
(8,  'false',  1,  1,  3,  '79',  39,  'active',  1,  0,  1668494676,  1,  '10791'),
(8,  'false',  1,  1,  3,  '79',  95,  'active',  1,  0,  1668494676,  1,  '10792'),

(17,  'false',  0,  0,  0,  '72',  0,  'out_of_service',  0,  0,  1656077825,  0,  '72'),
(17,  'false',  0,  0,  0,  '72',  2,  'active',  0,  0,  1656077825,  0,  '10721'),
(17,  'false',  0,  0,  0,  '72',  2,  'active',  0,  0,  1656077825,  0,  '10722'),
(17,  'false',  0,  0,  0,  '72',  6,  'active',  0,  0,  1656077825,  0,  '10723'),
(17,  'false',  0,  0,  0,  '72',  94,  'active',  0,  0,  1656077825,  0,  '10724'),

(0,  'false',  5,  1,  1,  '82',  25,  'active',  1,  0,  1668493458,  1,  '82'),
(0,  'false',  5,  1,  1,  '82',  75,  'active',  1,  0,  1668493458,  1,  '10821'),
(0,  'false',  5,  1,  1,  '82',  93,  'active',  1,  0,  1668493458,  1,  '10822');

b) Json文档的多种形式

查询文本形式的JSON 文档:

select json_serialize(data)
from station
where rownum <3;


查询列格式: （之后的步骤建议在SQL Developer中操作，便于查看结果。SQL Plus也能操作，但显示的表格不够直观）

select 
  s.id,
  s.data.name.string(),
  s.data.region_id.string(),
  s.data.lat.number(),
  s.data.lon.number(),
  s.data.station_type.string()
from station s
where rownum <3;


c) 通过 region_id 组合站点:

select 
  t.data.region_id.string() as region_id,
  count(*) as count
from station t
where t.data.station_type.string() = 'classic'
group by t.data.region_id.string();


d) 使用 json_dataguide 自动创建关联视图:

declare
  dg clob;
begin
  select json_dataguide(data, dbms_json.FORMAT_HIERARCHICAL, dbms_json.pretty) into dg
  from station;

  dbms_json.create_view('STATION_VIEW', 'STATION', 'DATA', dg, resolveNameConflicts => true, path => '$._id');
end;
/

查询创建好的视图:

select * from station_view where rownum <3;


e) 关联集合（Collections）与表：

将JSON的视图 station_view 与 关系表 tb_station_status 相结合：

create or replace view station_availability as
select s.station_id ,
       (select t."name" from station_view t where t."_id" = s.station_id) name,
       min(s.num_bikes_available) min,
       max(s.num_bikes_available) max,
       round(avg(s.num_bikes_available)) avg
from tb_station_status s
group by s.station_id;

select * from station_availability;


选出单车数量充足的站点 :

select station_id, name, min, max, avg
from station_availability
where min >20 and avg >50 and max > 90
order by max desc;


选出容量不足的站点:

select station_id, name, min, max, avg
from station_availability
where min < 30 and min/(max+1) < 0.1
order by max desc;