Oracle ADB Pre-Authenticated URL - Part IV

One of many enhancements Introduced in Oracle 23ai is the ability to stream external JSON sources into the database using the ORACLE_BIGDATA adapter in (inline) external tables. This enhancement can be combined with PAR URL to provide the SQL access to JSON documents. In this blogpost we will see about how that can be achieved.
 
We will start with generating a PAR URL for data source.
 
rajesh@ATP23ai> declare
  2     l_output clob;
  3  begin
  4     dbms_data_access.get_preauthenticated_url(
  5             schema_name => user,
  6             schema_object_name => 'EMP',
  7             expiration_minutes => 120 ,
  8             result => l_output );
  9
 10     dbms_output.put_line( l_output );
 11  end;
 12  /
{"status":"SUCCESS","id":"-L-SeZAK9oMOUudM10xVhXRyuUjeZOuWhYHCM7KbHpv886zK8HeWhYz3uR6xJnXb","preauth_url":"https://dataaccess.adb.us-ashburn-1.oraclecloudapps.com/adb/p/L4XY28c6hZnC
7cy_-eEl-2KsMAbmbH36ZPCPO0F7m8sU4nPbzuM4nFt-JnMqNGi57xl8TtOZcC0/data","expiration_ts":"2024-08-01T15:14:59.164Z","expiration_count":null}
 
PL/SQL procedure successfully completed.
 
Here is the sample data accessing the above URL endpoint (to confirm it works fine).
 
rajesh@ATP23ai> $ curl -i --location https://dataaccess.adb.us-ashburn-1.oraclecloudapps.com/adb/p/L4XY28c6hZnC7cy_-eEl-2KsMAbmbH36ZPCPO0F7m8sU4nPbzuM4nFt-JnMqNGi57xl8TtOZcC0/data
HTTP/1.1 200 OK
Date: Thu, 01 Aug 2024 13:15:41 GMT
Content-Type: application/json
Content-Length: 1862
Connection: keep-alive
Strict-Transport-Security: max-age=31536000;includeSubDomains
X-Content-Type-Options: nosniff
Request-Id: defaultCTKlCQLumkwA9YYjohdQQqrgmbSHOMX1oKoSqOfppIAjopr2Q5eDyA4r
 
{"items":[{"EMPNO":7369,"ENAME":"SMITH","JOB":"CLERK","MGR":7902,"HIREDATE":"1980-12-17T00:00:00","SAL":800,"DEPTNO":20},{"EMPNO":7499,"ENAME":"ALLEN","JOB":"SALESMAN","MGR":7698,"HIREDATE":"1981-02-20T00:00:00","SAL":1600,"COMM":300,"DEPTNO":30},{"EMPNO":7521,"ENAME":"WARD","JOB":"SALESMAN","MGR":7698,"HIREDATE":"1981-02-22T00:00:00","SAL":1250,"COMM":500,"DEPTNO":30},{"EMPNO":7566,"ENAME":"JONES","JOB":"MANAGER","MGR":7839,"HIREDATE":"1981-04-02T00:00:00","SAL":2975,"DEPTNO":20},{"EMPNO":7654,"ENAME":"MARTIN","JOB":"SALESMAN","MGR":7698,"HIREDATE":"1981-09-28T00:00:00","SAL":1250,"COMM":1400,"DEPTNO":30},{"EMPNO":7698,"ENAME":"BLAKE","JOB":"MANAGER","MGR":7839,"HIREDATE":"1981-05-01T00:00:00","SAL":2850,"DEPTNO":30},{"EMPNO":7782,"ENAME":"CLARK","JOB":"MANAGER","MGR":7839,"HIREDATE":"1981-06-09T00:00:00","SAL":2450,"DEPTNO":10},{"EMPNO":7788,"ENAME":"SCOTT","JOB":"ANALYST","MGR":7566,"HIREDATE":"0087-04-19T00:00:00","SAL":3000,"DEPTNO":20},{"EMPNO":7839,"ENAME":"KING","JOB":"PRESIDENT","HIREDATE":"1981-11-17T00:00:00","SAL":5000,"DEPTNO":10},{"EMPNO":7844,"ENAME":"TURNER","JOB":"SALESMAN","MGR":7698,"HIREDATE":"1981-09-08T00:00:00","SAL":1500,"COMM":0,"DEPTNO":30},{"EMPNO":7876,"ENAME":"ADAMS","JOB":"CLERK","MGR":7788,"HIREDATE":"0087-05-23T00:00:00","SAL":1100,"DEPTNO":20},{"EMPNO":7900,"ENAME":"JAMES","JOB":"CLERK","MGR":7698,"HIREDATE":"1981-12-03T00:00:00","SAL":950,"DEPTNO":30},{"EMPNO":7902,"ENAME":"FORD","JOB":"ANALYST","MGR":7566,"HIREDATE":"1981-12-03T00:00:00","SAL":3000,"DEPTNO":20},{"EMPNO":7934,"ENAME":"MILLER","JOB":"CLERK","MGR":7782,"HIREDATE":"1982-01-23T00:00:00","SAL":1300,"DEPTNO":10}],"hasMore":false,"limit":100,"offset":0,"count":14,"links":[{"rel":"self","href":"https://dataaccess.adb.us-ashburn-1.oraclecloudapps.com/adb/p/L4XY28c6hZnC7cy_-eEl-2KsMAbmbH36ZPCPO0F7m8sU4nPbzuM4nFt-JnMqNGi57xl8TtOZcC0/data"}]}
rajesh@ATP23ai>
 
 
then we will use Inline-external table feature to access the PAR URL using Oracle BIGDATA adaptors
 
rajesh@ATP23ai> variable n varchar2(180)
rajesh@ATP23ai> exec :n := 'https://dataaccess.adb.us-ashburn-1.oraclecloudapps.com/adb/p/L4XY28c6hZnC7cy_-eEl-2KsMAbmbH36ZPCPO0F7m8sU4nPbzuM4nFt-JnMqNGi57xl8TtOZcC0/data';
 
PL/SQL procedure successfully completed.
 
rajesh@ATP23ai>
rajesh@ATP23ai> select count(*)
  2  from external(
  3             (data json)
  4              type oracle_bigdata
  5              access parameters(
  6              com.oracle.bigdata.fileformat = jsondoc
  7              )
  8              location (:n)
  9      reject limit unlimited);
 
  COUNT(*)
----------
         1
 
rajesh@ATP23ai>
 
Notice, the type is ORACLE_BIGDATA, using the ORACLE_BIGDATA access driver, we can access data stored in object stores as if that data is stored in tables within Oracle database. Currently it supports access to Oracle (OCI) object storage, Azure blob storage and Amazon S3 storage.
 
com.oracle.bigdata.fileformat – this is one of the common access parameter the specifies the format of the file, the possible values are parquet, orc, textfile, avro, csv, jsondoc, jsontable
 
the value jsondoc reads the input as JSON file, the JSON values are mapped to the single JSON column that can be queried using SQL/JSON.
  
rajesh@ATP23ai> select count(*)
  2  from external(
  3             (data json)
  4              type oracle_bigdata
  5              access parameters(
  6              com.oracle.bigdata.json.path = '$.items[*]'
  7              com.oracle.bigdata.fileformat = jsondoc
  8              )
  9              location (:n)
 10      reject limit unlimited);
 
  COUNT(*)
----------
        14
 
com.oracle.bigdata.json.path – A json path expression which identifies a sequence of nested JSON values which will be mapped to table rows. In the above json document returned from while accessing PAR URL, we are asking to iterate each element of the array $.items[*] as a set of JSON values mapping to separate table rows.
 
Then using SQL/JSON path expression and dot notation syntax, we can have relational access to the JSON document like this
 
rajesh@ATP23ai> select t.DATA.EMPNO.number() as empno,
  2        t.DATA.ENAME.string() as ename,
  3        t.DATA.HIREDATE.date() as hire_dt
  4  from external(
  5             (data json)
  6              type oracle_bigdata
  7              access parameters(
  8              com.oracle.bigdata.json.path = '$.items[*]'
  9              com.oracle.bigdata.fileformat = jsondoc
 10              )
 11              location (:n)
 12      reject limit unlimited) t
 13  where t.DATA.DEPTNO.number() = 10;
 
     EMPNO ENAME           HIRE_DT
---------- --------------- -----------
      7782 CLARK           09-JUN-1981
      7839 KING            17-NOV-1981
      7934 MILLER          23-JAN-1982
 
 
Or even better, set the fileformat to JSONTABLE (read a JSON file, the JSON values are assumed to be JSON objects and the top level attributes are mapped to specific columns based on the column-name)
 
rajesh@ATP23ai> select empno,ename,job
  2  from external(
  3             (empno number
  4             , ename varchar2(10)
  5             , job varchar2(10)
  6             , mgr number
  7             , hiredate date
  8             , sal number
  9             , deptno number
 10             )
 11              type oracle_bigdata
 12              access parameters(
 13              com.oracle.bigdata.json.path = '$.items[*]'
 14              com.oracle.bigdata.fileformat = jsontable
 15              )
 16              location (:n)
 17      reject limit unlimited)
 18  where deptno = 10;
 
     EMPNO ENAME           JOB
---------- --------------- ----------
      7782 CLARK           MANAGER
      7839 KING            PRESIDENT
      7934 MILLER          CLERK
 
SQL is an extremely powerful language and that with mastery of just a few features, you can do things in a highly scalable fashion with very little code. Having SQL Access, to the JSON documents provided endless uses case and leverage many insights to data.