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.
The data is finally exported to a simple CSV document with just two fields per record.
Now it is time to use SQL Developer:
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:
The Data Import Wizard has four steps – that are fairly straightforward. Select the file, specify the delimiter and accept the other default values:
Step 2- Define the Table Name
Step 3 – select the columns:
Step 4: refine the column definitions (name):
Check the summary and click on Finish:
The import is performed – successfully:
The table can be reviewed in SQL Developer:
as well as in SQL Developer Web (in the context of the OCI Autonomous Data Warehouse service console):
and used as the source for a Data Set in Data Visualization Desktop:
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:
And a pivot table of the “binned” week day death count sum
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:
