For users who simply need access to the entirety of their Avro data, flattening the data into a single table is the best option. The cmdlet will use streaming and only parses the data once per query in this mode.

Joining Object Arrays into a Single Table

With DataModel set to "FlattenedDocuments" values will act in the same manner as a SQL JOIN. Any nested sibling values (child paths at the same height) will be treated as a SQL CROSS JOIN.

Example

Below is a sample query and the results, based on the sample document in Raw Data. This implicitly JOINs the people collection with the vehicles collection and implicitly JOINs the vehicles collection with the maintenance collection.

Connection String

Use the following connection string to query the Raw Data in this example.

URI=C:\people.avro;DataModel=FlattenedDocuments;

Query

The following query drills into the nested elements in each people object.

SELECT
  [personal.age] AS age,
  [personal.gender] AS gender,
  [personal.name.first] AS name_first,
  [personal.name.last] AS name_last,
  [source],
  [type],
  [model],
  [insurance.company] AS ins_company,
  [insurance.policy_num] AS ins_policy_num,
  [date] AS maint_date,
  [desc] AS maint_desc
FROM
  [people]

Results

With horizontal and vertical flattening based on the described paths, each vehicle object is implicitly JOINed to its parent people object and each maintenance object is implicitly JOINed to its parent vehicle object.