TDV Adapter for Avro

Build 22.0.8462

Relational Model

The Avro Adapter can be configured to create a relational model of the data, treating nested object arrays as individual tables containing a primary key and a foreign key that links to the parent document. This is particularly useful if you need to work with your data in existing BI, reporting, and ETL tools that expect a relational data model.

Joining Nested Arrays as Tables

With DataModel set to "Relational", any JOINs are controlled by the query. Any time you perform a JOIN query, the file or source will be queried once for each table (nested array) included in the query.

Example

Below is a sample query against the sample document in Raw Data, using a relational model.

Connecting String


URI=C:\people.avro;DataModel=Relational;'

Query

The following query explicitly JOINs the people, vehicles, and maintenance tables.

SELECT 
  "people"."personal.age" AS age, 
  "people"."personal.gender" AS gender, 
  "people"."personal.name.first" AS first_name, 
  "people"."personal.name.last" AS last_name, 
  "people"."source", 
  "vehicles"."type", 
  "vehicles"."model", 
  "vehicles"."insurance.company" AS ins_company, 
  "vehicles"."insurance.policy_num" AS ins_policy_num, 
  "maintenance"."date" AS maint_date, 
  "maintenance"."desc" AS maint_desc
FROM 
  "people"
JOIN 
  "vehicles" 
ON 
  "people"."_id" = "vehicles"."people_id"
JOIN 
  "maintenance" 
ON 
  "vehicles"."_id" = "maintenance"."vehicles_id"

Results

In the example query, each maintenance object is JOINed to its parent vehicle object, which is JOINed to its parent people object to produce a table with 8 rows (2 maintenance entries for each of 2 vehicles each for 2 people).

agegenderfirst_namelast_namesourcetypemodelins_companyins_policy_nummaint_datemaint_desc
20MJohnDoeinternetcarHonda CivicABC Insurance123452017-07-17oil change
20MJohnDoeinternetcarHonda CivicABC Insurance123452018-01-03new tires
20MJohnDoeinternettruckDodge RamABC Insurance123452017-08-27new tires
20MJohnDoeinternettruckDodge RamABC Insurance123452018-01-08oil change
24FJaneRobertsphonecarToyota CamryCar Insurance987652017-05-11tires rotated
24FJaneRobertsphonecarToyota CamryCar Insurance987652017-11-03oil change
24FJaneRobertsphonecarHonda AccordCar Insurance987652017-10-07new air filter
24FJaneRobertsphonecarHonda AccordCar Insurance987652018-01-13new brakes

Copyright (c) 2023 CData Software, Inc. - All rights reserved.
Build 22.0.8462