Loading Data into Always Free Oracle Autonomous Data Warehouse Cloud – from JSON and CSV to Database Table

0

In a number of recent articles, I have described how to provision an instance of Oracle Data Warehouse Cloud in Oracle Cloud’s Always Free tier. I have also described how to connect both SQL Developer and Data Visualization Desktop to this ADW instance. In this article, we take this one step further and load data into the ADW instance using the Import tool in SQL Developer. Specifically, I start with a JSON file that I load into a Pandas Data Frame in a Jupyter Notebook; that data is saved to a CSV document. This document is imported into SQL Developer and converted into a relational database table. This table and its data can be accessed from SQL Developer Web, SQL Developer, Data Visualization Desktop  and other tools.

The data set in this case is retrieved from the Dutch Central Bureau for Statistics; it concerns a JSON file that contains data on the number of deaths per day, between 1995 and 2017: https://opendata.cbs.nl/statline/#/CBS/nl/dataset/70703ned/table?ts=1566653756419 .

The JSON file is subsequently loaded and manipulated in a Python based Jupyter Notebook, using a Pandas Data frame.

image

The data is finally exported to a simple CSV document with just two fields per record.

image

Now it is time to use SQL Developer:

image

Open the Database Connection to the ADW Instance (that was created in one of my earlier articles); right click on the Tables node and select the option Import Data:

image

The Data Import Wizard has four steps – that are fairly straightforward. Select the file, specify the delimiter and accept the other default values:

image

Step 2- Define the Table Name

image

Step 3 – select the columns:

image

Step 4: refine the column definitions (name):

image

Check the summary and click on Finish:

SNAGHTML397406c2

The import is performed – successfully:

image

The table can be reviewed in SQL Developer:

image

as well as in SQL Developer Web (in the context of the OCI Autonomous Data Warehouse service console):

image

and used as the source for a Data Set in Data Visualization Desktop:

imageimage

This article demonstrated how we can take data in CSV format and turn it into a Database Table in Autonomous Data Warehouse Cloud using SQL Developer Import. A similar feature is available in APEX – also part of the developer toolset in Autonomous Data Warehouse and Autonomous Transaction Processing. CSV files can also be imported into Data Visualization Desktop and via a Data Flow saved to a table in Autonomous Data Warehouse.

Note: two quick summaries created with Data Visualization Desktop:

Death count per year over the years 1995-2017:

image

And a pivot table of the “binned” week day death count sum

image

this pivot table indicates that Sunday is in bin 1 – on the low end of the range – with Friday in the highest bin. Friday turns out to be the day of the week with the highest number of deaths and Sunday the ‘slowest’ day.

And finally this bar chart with the total number of deaths – that confirms the previous finding about the days of the week:

image

About Author

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director and Oracle Developer Champion. Solution architect and developer on diverse areas including SQL, JavaScript, Kubernetes & Docker, Machine Learning, Java, SOA and microservices, events in various shapes and forms and many other things. Author of the Oracle Press book Oracle SOA Suite 12c Handbook. Frequent presenter on user groups and community events and conferences such as JavaOne, Oracle Code, CodeOne, NLJUG JFall and Oracle OpenWorld.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.