Embedded Files
The New Notion RESTful API Integration with Oracle ADW
The New Notion RESTful API Integration with Oracle ADW
May 17, 2021 | Data Integration
May 17, 2021 | Data Integration
If you haven't tried Notion yet, I can highly recommend giving it a go. Notion helps you to be more organised through an all-in-one workspace where you can write, plan and collaborate - it allows you to take notes, add tasks, manage projects and much more. Notion provides the building blocks so you can create your own layouts and toolkits to be more productive.
Notion databases allow you to add tables, calendars, galleries, lists and board views for managing projects and activities. Up until now, it's always been easier to get data into Notion than it has been to get it out. That's all changed with their new API. In this article, I want to provide a quick demo of how easy it is to pull data from your Notion workspace into your Oracle Autonomous Data Warehouse (ADW).
I've created a simple database called Client Directory with some fictitious companies to demonstrate the new RESTfull API.
Create a new integration
Create a new integration
First thing you'll need to create an integration within Notion. Open your integrations and click + New integration and follow the instructions. Then find the Internal Integration Token under Secrets. Show and copy the token and paste it somewhere as you'll need this later.
Share a database with your integration
Share a database with your integration
Within your Notion application open the database you want to integrate with ADW and select Share. Click on the Invite button and find your new integration. Select the integration and click invite. Your integration now has permission to read, write, and edit your Notion database. Finally, you'll need the database ID. Click on the Share link again and select Copy link. The ID is the part of the URL after your workspace name and the slash (acme/) and before the question mark (?). The ID is 32 characters long, containing numbers and letters. See the Notion developer documentation Getting Started section for more details.
https://www.notion.so/myworkspace/a8aec43384f447ed84390e8e42c2e089?v=...
|--------- Database ID --------|
Notion API
Notion API
The Notion API reference documentation tells us we need to perform a POST request to retrieve data from a Notion Database (see Query a database section). Looking at the cURL request method in the example provided within the documentation, we need the following information;
the URL - This is https://api.notion.com/v1/databases/<DatabaseID>/query. Replacing the <DatabaseID> with the one you saved away earlier.
the header details for the authorization bearer. This is the Internal Integration Token you also saved earlier.
curl -X POST 'https://api.notion.com/v1/databases/<DatabaseID>/query' \
-H 'Authorization: Bearer '"<IntegrationToken>"'' \
-H 'Notion-Version: 2021-05-13' \
-H "Content-Type: application/json" \
APEX Web Service
APEX Web Service
There's lots of ways to call the Notion API from within your ADW. For this demo, we'll use the APEX_WEB_SERVICE package to make a rest request. Because our call contains headers to set the authorization, we need to define this using the g_request_headers name and value pairs. Next, we can call the make_rest_request procedure to get the contents of the Notion database as JSON into a CLOB.
declare
l_response clob;
begin
-- define the headers
apex_web_service.g_request_headers(1).name := 'Authorization';
apex_web_service.g_request_headers(1).value := 'Bearer <IntegrationToken>';
-- make the request and retreive JSON to l_response
l_response := apex_web_service.make_rest_request(
p_url => 'https://api.notion.com/v1/databases/<DatabaseID>/query',
p_http_method => 'POST');
dbms_output.put_line(substr(l_response,1,2000));
end;
/
In this example, we're simply outputting the first 2,000 characters of the JSON payload using DBMS_OUTPUT. In a real scenario, you'll most likely want to save the JSON to a table for downstream processing into a staging area of your data warehouse. Below is the JSON for one of the Client Directory records returned by the APEX_WEB_SERVICE request.
In a future post, we'll look at creating a relational view of the JSON payload using the JSON_TABLE function (introduced in 12c), where we will review some of the more complex elements of Notion Databases, such as multi-select and relational operators. We will also look at populating your Oracle data warehouse in near real-time.
{
"object": "list",
"results": [
{
"object": "page",
"id": "767492ec-f20a-4062-b8d7-397e68e1aad7",
"created_time": "2020-07-25T21:33:29.474Z",
"last_edited_time": "2021-05-05T12:35:00.000Z",
"parent": {
"type": "database_id",
"database_id": "4c80b40c-1dbe-45aa-9181-10542d2e27dd"
},
"archived": false,
"properties": {
"Sector": {
"id": "7%fE",
"type": "select",
"select": {
"id": "2fdccc3f-3963-481a-a0e3-c717e3d195e3",
"name": "Consumer Products Manufacturing",
"color": "yellow"
}
},
"Job Title": {
"id": "9rMz",
"type": "text",
"text": [
{
"type": "text",
"text": {
"content": "Vice President of Sales",
"link": null
},
"annotations": {
"bold": false,
"italic": false,
"strikethrough": false,
"underline": false,
"code": false,
"color": "default"
},
"plain_text": "Vice President of Sales",
"href": null
}
]
},
"Contact": {
"id": "@`qh",
"type": "text",
"text": [
{
"type": "text",
"text": {
"content": "Brian Welker",
"link": null
},
"annotations": {
"bold": false,
"italic": false,
"strikethrough": false,
"underline": false,
"code": false,
"color": "default"
},
"plain_text": "Brian Welker",
"href": null
}
]
},
"Projects": {
"id": "Ae6-",
"type": "relation",
"relation": []
},
"Website": {
"id": "Hb'-",
"type": "url",
"url": "adventureworks.clearresolutiondesign.com"
},
"Short Name": {
"id": "LC>:",
"type": "text",
"text": [
{
"type": "text",
"text": {
"content": "AWC",
"link": null
},
"annotations": {
"bold": false,
"italic": false,
"strikethrough": false,
"underline": false,
"code": false,
"color": "default"
},
"plain_text": "AWC",
"href": null
}
]
},
"Phone": {
"id": "Mj$g",
"type": "phone_number",
"phone_number": "1234-567890"
},
"Location": {
"id": "khG`",
"type": "select",
"select": {
"id": "43df5fce-6e5e-417e-9699-36a014e3a463",
"name": "Washington",
"color": "green"
}
},
"Primary Country of Operations": {
"id": "pX@{",
"type": "select",
"select": {
"id": "155e78c3-7111-4eb8-884c-a03071bedc64",
"name": "United States of America",
"color": "orange"
}
},
"Department": {
"id": "qeOI",
"type": "select",
"select": {
"id": "dfe35989-4abe-47e3-a980-cec6ba52669d",
"name": "Sales & Marketing",
"color": "yellow"
}
},
"Mobile": {
"id": "t6aJ",
"type": "phone_number",
"phone_number": "098765421"
},
"Email": {
"id": "y/XJ",
"type": "email",
"email": "brian.welker@adventureworks.com"
},
"Client": {
"id": "title",
"type": "title",
"title": [
{
"type": "text",
"text": {
"content": "Adventure Works Cycles",
"link": null
},
"annotations": {
"bold": false,
"italic": false,
"strikethrough": false,
"underline": false,
"code": false,
"color": "default"
},
"plain_text": "Adventure Works Cycles",
"href": null
}
]
}
}
}
],
"next_cursor": null,
"has_more": false
}
Page updated
Google Sites
Report abuse