Ingesting JSON Data

Objective

Configure JSON IW source
Configure mapping to create source tables
Perform ingestion of source tables
Validate ingested data

Prerequisites

A JSON IW source called json_demo should exist in Infoworks ADE
Base directory for JSON data. This tutorial assumes that the base directory is: /home/infoworks-user/json_demo
A JSON file called orders.json should exist in the base directory: /home/infoworks-user/json_demo
Data in orders.json should be

{“orderid”:10545,”customerid”:”LAZYK”,”customer_address”:{“street_address”:”12 Orchestra Terrace”,”city”:”Walla Walla”,”state”:”WA”,”postalcode”:99362},”order_products”:[{“productid”:”J1KL”,”productqty”:20},{“productid”:”C4GL”,”productqty”:30},{“productid”:”E3TH”,”productqty”:45}]} {“orderid”:10546,”customerid”:”VICTE”,”customer_address”:{“street_address”:”2817 Milton Dr.”,”city”:”Albuquerque”,”state”:”NM”,”postalcode”:87110},”order_products”:[{“productid”:”K3DF”,”productqty”:24},{“productid”:”K4DF”,”productqty”:28},{“productid”:”J9NM”,”productqty”:77}]} {“orderid”:10547,”customerid”:”SEVES”,”customer_address”:{“street_address”:”Split Rail Beer and Ale”,”city”:”Seattle”,”state”:”WA”,”postalcode”:98124},”order_products”:[{“productid”:”L3DF”,”productqty”:33},{“productid”:”M4DF”,”productqty”:86},{“productid”:”N9NM”,”productqty”:90}]}

The source data zip file must be downloaded from the following link if needed

wget https://s3.amazonaws.com/iw-training-data/json_demo_orders/orders_json_02_2019.zip

Configure JSON IW Source

Navigate to the source from Sources tab with name json_demo
Click on the Settings icon
Select the From Filesystem option.
In the Source Base Path field, enter the base directory path to JSON data as assumed in the prerequisites
Click on Record Scope to select Line (each line is a record) option (since orders.json has each json record as a line in the file)
Click the Save Settings button
Click on Source Configuration icon to configure the mapping

Click on Configure Mapping

Clicking on Configure Mapping leads to the following window which contains table structure diagram with attributes and the components

The first table of three source tables to be created

Main table/Direct table: which contains the following attributes

orderid: int; customerid: string; customer_address: struct; order_products: array ( containing structs)

Click on the first struct icon in the table structure diagram

Click on Create Table in the top left of option bar

Clicking on Create Table leads to Table Configuration window with name Create Table, where table name and relative target HDFS path should be given.

For this first table, we will be using the default table name as orders and relative target HDFS path as /orders

Check the columns.

Scroll down and click on Save to confirm the creation of table orders

Now, we will create the second table: orders_customer_address

Attributes in the table:

orderid: int; customerid: string; street_address: string; city: string; state: string; postalcode: int ; order_products: array ( containing structs)

Click Shift to select multiple structs which are the first root struct and customer_address struct

After the required structs are selected, create a table similar to the above procedure followed for the first table

For the second table, table and Target HDFS path should be given as mentioned below

Table Name: orders_customer_address

Target HDFS path (Target base path: __/json_demo) : /orders_customer_address

Similarly, we will create the table by selecting the first root struct, order_products array and order_products struct

Which will have the attributes

orderid: int; customerid: string; customer_address: struct; productid: string; productqty: int

With

Table Name: orders_order_products

Target HDFS path (Target base path: __/json_demo) : /orders_order_products

Since a single order can have more than one product, multiple products can have the same orderid implying that there will be multiple records with the same orderid for the third table orders_order_products

Now that we have created the required source tables, close the window to get back to the source configuration

Perform Ingestion of Source tables

Click on Table Groups option in Source Configuration
Click Add Table Group button in the top right
Configure the table group after adding all the three tables created, with the following properties

Table Group Name: IngestOrdersJSONdata

Max. Parallel Tables: 3

Click on Initialize and Ingest Now button in the table group window to the start the first ingestion
Wait for the ingestion job to successfully complete

Validate Ingested Data

In this session, we will be directly accessing hive to validate the data

For reference, we will check the schema of the three hive tables

orders

orders_customer_address

orders_order_products

To validate the data, we will execute the following Hive select queries which show the data for the Hive table columns except for Infoworks audit column

select orderid, customerid, customer_address, order_products from orders;

select street_address, city, state, postalcode, orderid, customerid, order_products from orders_customer_address;

select productid, productqty, orderid, customerid, customer_address from orders_order_products;

Success

Congratulations! You have completed ingesting orders data from a JSON file and validated the data ingested into the three tables from the JSON file. You can now ingest data from any JSON file into the data lake using Infoworks.

Session Summary

Configured JSON IW source
Configured Mapping to create source tables
Performed ingestion for the created source tables
Validated the ingested data

Last updated on