ODBC Driver for Avro

Build 23.0.8839

トップレベルのドキュメントモデル

Using a top-level document view of the data provides ready access to top-level elements. The 本製品 returns nested elements in aggregate, as single columns.

One aspect to consider is performance. You forgo the time and resources to process and parse nested elements -- the 本製品 parses the returned data once, using streaming to read the data. Another consideration is your need to access any data stored in nested parent elements, and the ability of your tool or application to process the data.

Modeling a Top-Level Document View

With DataModel set to "Document" (the default), the 本製品 scans only a single object array, the top-level object array by default. The top-level object elements are available as columns due to the default object flattening. Nested object arrays are returned as aggregated strings.

Example

Below is a sample query and the results, based on the sample document in Raw データ. The query results in a single "people" table.

Connection String

Set the DataModel connection property to "Document" to perform the following query and see the example result set.

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

Query

The following query pulls the top-level object elements and the vehicles array into the results.

SELECT
  [personal.age] AS age,
  [personal.gender] AS gender,
  [personal.name.first] AS name_first,
  [personal.name.last] AS name_last,
  [source],
  [vehicles]
FROM
  [people]
  

Results

With a document view of the data, the personal object is flattened into 4 columns and the source and vehicles elements are returned as individual columns, resulting in a table with 6 columns.

agegendername_firstname_lastsourcevehicles
20MJohnDoeinternet
[{"type":"car","model":"Honda Civic","insurance":{"company":"ABC Insurance","policy_num":"12345"},"maintenance":[{"date":"07-17-2017","desc":"oil change"},{"date":"01-03-2018","desc":"new tires"}]},{"type":"truck","model":"Dodge Ram","insurance":{"company":"ABC Insurance","policy_num":"12345"},"maintenance":[{"date":"08-27-2017","desc":"new tires"},{"date":"01-08-2018","desc":"oil change"}]}]
24FJaneRobertsphone
[{"type":"car","model":"Toyota Camry","insurance":{"company":"Car Insurance","policy_num":"98765"},"maintenance":[{"date":"05-11-2017","desc":"tires rotated"},{"date":"11-03-2017","desc":"oil change"}]},{"type":"car","model":"Honda Accord","insurance":{"company":"Car Insurance","policy_num":"98765"},"maintenance":[{"date":"10-07-2017","desc":"new air filter"},{"date":"01-13-2018","desc":"new brakes"}]}]

Copyright (c) 2024 CData Software, Inc. - All rights reserved.
Build 23.0.8839