Merge json files using Pandas

Quick demo for merging multiple json files using Pandas –

import pandas as pd
import glob
import json

file_list = glob.glob("*.json")
>>> file_list
['b.json', 'c.json', 'a.json']

Use enumerate to assign counter to files.


allFilesDict = {v:k for v, k in enumerate(file_list, 1)}
>>> allFilesDict
{1: 'b.json', 2: 'c.json', 3: 'a.json'}

Append the data into list –

>>> data = []

for k,v in allFilesDict.items():
    if 1  (more...)

Making Longer Lists

For very long lists, we need the return values to be CLOBs. Alas, LISTAGG can only return VARCHAR2 lists, but XML and JSON aggregate functions can return CLOBs!

Tour de France Data Analysis using Strava data in Jupyter Notebook with Python, Pandas and Plotly – Step 2: combining and aligning multi rider data for analyzing and visualizing the Race

In this article, I analyze the race that took place in stage 14 of the 2019 Tour de France in a Jupyter Notebook using Python, Pandas and Plotly and based on the Strava performance data published by Steven Kruijswijk, Thomas de Gendt, Thibaut Pinot and Marco Haller. In this previous article I have explained how we can retrieve the Strava data for a specific rider for a stage in the Tour de France, and in (more...)

Tour de France Data Analysis using Strava data in Jupyter Notebook with Python, Pandas and Plotly – Step 1: single rider loading, exploration, wrangling, visualization

In this article, I will show how to analyze the performance of Steven Kruijswijk during stage 14 of the 2019 Tour de France in a Jupyter Notebook using Python, Pandas and Plotly. Strava collects data from athletes regarding their activities – such as running, cycling, walking and hiking. Members can upload data – and tens of millions do so, including some well known cyclists such as Steven Kruijswijk. In my previous article I have explained (more...)

Analyzing the 2019 Tour de France in depth using Strava performance data from Race Riders

This year’s Tour de France was quite a spectacle. Great performances, exciting stages, unexpected events: it had it all. Analyzing the race events as they unfolded during the stages of this year’s Tour is something I am keen to attempt. Using Jupyter Notebooks, Python and Pandas and Plotly for visualization, I am sure I can get more detailed stories extracted from raw race data. The starting point for such analysis activities is… the data.

However, (more...)

JSON, the Euro symbol and a WE8MSWIN1252 character set database

Uncategorized
| Jul 30, 2019
Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates. Trying to parse a JSON_OBJECT_T string containing the € (euro) character on a WE8MSWIN1252 12.2.0.1.0 database returns the following error:   declare j json_object_t; begin j := json_object_t.parse('{"currency":"€"}'); end; /   ORA-40441: JSON syntax error ORA-06512: at "SYS.JDOM_T",

Houston, we’ve got a problem with JSON PUT method

Uncategorized
| Jul 3, 2019
Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates. In brief, in Oracle 12.2.0.1 there is some problem with PUT method of JSON_OBJECT_T, for some reason numbers are not recognized as such, but they are stored as scalar. The workaround is to use JSON_OBJECT_T.PARSE in combination with JSON_OBJECT SQL function for entering numbers (see the

JSON get_Date() method always discards time portion

Uncategorized
| May 16, 2019
Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates. In case you wonder why you don't see the time portion when you call the method get_Date() on a JSON object: -- tested on Oracle 12.2.0.1.0 set serveroutput on declare l_job_description JSON_OBJECT_T; l_job_params JSON_ARRAY_T; l_job_arr_elem JSON_OBJECT_T; begin

Understanding Nested Lists Dictionaries of JSON in Python and AWS CLI


After lots of hair pulling, bouts of frustration, I was able to grasp this nested list and dictionary thingie in JSON output of AWS cli commands such as describe-db-instances and others. If you run the describe-db-instances for rds or describe-instances for ec2, you get a huge pile of JSON mumbo-jumpo with all those curly and square brackets studded with colons and commas. The output is heavily nested.


For example, if you do :

aws rds (more...)

My #OUGIreland19 Presentation on JSON

Thanks to those who came to my presentation today at OUG Ireland 2019. As promised, here is the link to the slides. JSON in 18c and 19c from stewashton I had a great time at the conference, meeting new folks and spending time (but never enough) with the less new. The talks I attended were […]

named tuple to JSON – Python

In pgdb – PostgreSQL DB API, the cursor which is used to manage the context of a fetch operation returns list of named tuples. These named tuples contain field names same as the column names of the database query.

An example of a row from the list of named tuples –


Row(log_time=datetime.datetime(2019, 3, 20, 5, 41, 29, 888000, tzinfo=), user_name='admin', connection_from='72.20.208.64:21132', command_tag='INSERT', message='AUDIT: SESSION,1,1,WRITE,INSERT,TABLE,user.demodml,"insert into user.demodml (id) values (1),(2),(3),(4),(5),(6),(7),(8),(9),(11);",',  (more...)

limit IOs by instances

This is a follow up to my previous post.

I had to answer if I can limit the (IO) resources a DB instance can utilize. Unfortunately, in a simple instance I can not do so. It can be done in PDBs, but right now PDBs are out of scope.
So a simpler approach was developed: limiting IOs by creating cgroups.

There are 2 steps:

  1. disks
  1. create a proper cgroup
  2. get all related devices for (more...)

Documented JSON Classes

Looking through the PeopleTools 8.57 Feature Overview document, you may have noticed that 8.57 now includes support for several JSON classes as well as PeopleBooks documentation. As Chris Malek showed us a couple of years ago, the classes listed in the Feature Overview document are not new. What is new is the keyword Support and PeopleBooks documentation. Using the documentation, I was able to generate a sample on PeopleTools 8.57:

Local  (more...)

Schema-on-what? How to model JSON

How do you make sense out of schema-on-read? This post shows you how to turn a JSON document into a relational data model that any modeler or relational database person could understand.

101 Ways to Process JSON with PeopleCode

... well... maybe not 101 ways, but there are several!

There is a lot of justified buzz around JSON. Many of us want to (or must) generate and parse JSON with PeopleSoft. Does PeopleSoft support JSON? Yes, actually. The Documents module can generate and parse JSON. Unfortunately, many of us find the Documents module's structure too restrictive. The following is a list of several alternatives available to PeopleSoft developers:

Using the Slack webhook API from PL/SQL

Slack is a web-based chat room popular with many companies. Slack also has an API that can be used to post messages to a given "channel" or chat room. The simplest API offered is the "webhook" integration, which is "a simple way to post messages from external sources into Slack. They make use of normal HTTP requests with a JSON payload that includes the message text and some options."



To create a new Slack (more...)

Pivot Table plugin for APEX (Updated)

Some years ago I created an APEX region plugin for displaying the results of a query as a Pivot Table. You can read the original blog post about that here.

The plugin proved quite popular, but people were having problems using it with APEX 5 due to a jQuery versioning conflict.

I've now updated the plugin so it works with APEX, both 5.0 and 5.1.



To use it, simply add a region to (more...)

JSON_TABLE and the Top 2000

Oracle database 12c provides native JSON parsing. It is relatively easy to go from data in a JSON format to a relational representation.
In this example I will use data from the Top 2000. Every year in The Netherlands a playlist is produced by popular vote and fully broadcast between Christmas and the last day of the year, with the number one being played just before the New Year. You can see the complete list (more...)

Oracle and the Autonomous Database (a personal perspective from afar)

Yeah, if you hadn’t seen that one coming, hmmm, what can I say… Lot’s of…

Getting Started with REST Enabled SQL

Uncategorized
| Sep 7, 2017
POST a query or DML or DDL or even a short script over HTTPS to ORDS and have ORDS run that on your Oracle Database and have the results returned in JSON. That's the plan.

What You Need To Know

REST Enabled SQL uses Schema Authentication as well First Party Authentication.  



This means you can run SQL against an Oracle Database if the following is true.
  • REST Enabled SQL is enabled in ORDS
  • A (more...)