You can customize the table schemas detected through Automatic Schema Discovery to change column names and data types, enable update functionality, and more. The processing operations of the CData Power BI Connector for JSON hide the complexity of processing data and communicating with remote data sources, but they also expose control over these layers through custom schemas.

Custom schemas are defined in configuration files. In this chapter we outline the structure of these files.

Generating Schemas

Generating Schemas allows you to persist and customize the dynamic schemas discovered through Automatic Schema Discovery.

Editing Schema Files

Tables and views are defined by authoring schema files in APIScript. APIScript is a simple configuration language that allows you to define the columns and the behavior of the table. It also has built-in operations that enable you to process JSON. In addition to these data processing primitives, APIScript is a full-featured language with constructs for conditionals, looping, etc. However, as shown by the example schema, for most table definitions you will not need to use these features.

Example Schema

Below is a fully functional table schema that models the people document in the Raw Data example and contains all the components you will need to execute SQL to local or remote JSON.

The schema reflects the following connection string, which returns a single table containing the data delineated by the specified XPaths -- see Parsing Hierarchical Data for a guide to the different DataModel settings.

DataModel=FLATTENEDDOCUMENTS;URI=C:\people.xml;XPath='/root/people;/root/people/vehicles;/root/people/vehicles/maintenance';Location=C:\myschemas;GenerateSchemaFiles=OnStart;

You can find more information on each of the components of a schema in Column Definitions, SELECT Execution, INSERT Execution, UPDATE Execution, and DELETE Execution. You can also create stored procedures to implement capabilities of your API that cannot be modeled as SELECT, INSERT, UPDATE, or DELETE statements. See Defining Stored Procedures for more information.

<api:script xmlns:api="http://apiscript.com/ns?v1" xmlns:xs="http://www.w3.org/2001/XMLSchema">

  <api:info title="people" desc="Generated schema file." xmlns:other="http://apiscript.com/ns?v1">
    <!-- You can modify the name, type, and column size here. -->
    <!-- See Column Definitions to specify column behavior and use XPaths to extract column values from JSON. -->
    <attr name="date"                 xs:type="date"    readonly="false"              other:xPath="/json/people/vehicles/maintenance/date"     />
    <attr name="desc"                 xs:type="string"  readonly="false"              other:xPath="/json/people/vehicles/maintenance/desc"     />
    <attr name="insurance.company"    xs:type="string"  readonly="false"              other:xPath="/json/people/vehicles/insurance/company"    />
    <attr name="insurance.policy_num" xs:type="string"  readonly="false"              other:xPath="/json/people/vehicles/insurance/policy_num" />
    <attr name="maintenance:_id"      xs:type="string"  readonly="false"  key="true"  other:xPath="/json/people/vehicles/maintenance/_id"      />
    <attr name="model"                xs:type="string"  readonly="false"              other:xPath="/json/people/vehicles/model"                />
    <attr name="people:_id"           xs:type="string"  readonly="false"  key="true"  other:xPath="/json/people/_id"                           />
    <attr name="personal.age"         xs:type="integer" readonly="false"              other:xPath="/json/people/personal/age"                  />
    <attr name="personal.gender"      xs:type="string"  readonly="false"              other:xPath="/json/people/personal/gender"               />
    <attr name="personal.name.first"  xs:type="string"  readonly="false"              other:xPath="/json/people/personal/name/first"           />
    <attr name="personal.name.last"   xs:type="string"  readonly="false"              other:xPath="/json/people/personal/name/last"            />
    <attr name="source"               xs:type="string"  readonly="false"              other:xPath="/json/people/source"                        />
    <attr name="type"                 xs:type="string"  readonly="false"              other:xPath="/json/people/vehicles/type"                 />
    <attr name="vehicles:_id"         xs:type="string"  readonly="false"  key="true"  other:xPath="/json/people/vehicles/_id"                  />
  </api:info>

  <api:set attr="DataModel" value="FLATTENEDDOCUMENTS" />
  <api:set attr="URI" value="C:\\tmp\\people.json" />

  <api:set attr="JSONPath" value="$.people;$.people.vehicles;$.people.vehicles.maintenance" />

  <!-- The GET method corresponds to SELECT. Here you can change the parameters of the request for data. The results of processing are pushed to the schema's output. See SELECT Execution for more information. -->
  <api:script method="GET">
    <api:call op="jsonproviderGet">
      <api:push/>
    </api:call>
  </api:script>

  <!-- To add support for INSERTS please see the INSERT Execution page within the help for further information and examples. -->
  <api:script method="POST">
    <api:set attr="method" value="POST"/>
    <api:call op="jsonproviderGet">
      <api:throw code="500" desc="INSERTs are not currently supported."/>
      <api:push/>
    </api:call>
  </api:script>

  <!-- To add support for UPDATES please see the UPDATE Execution page within the help for further information and examples. -->
  <api:script method="MERGE">
    <api:set attr="method" value="PUT"/>
    <api:call op="jsonproviderGet">
      <api:throw code="500" desc="Updates are not currently supported."/>
      <api:push/>
    </api:call>
  </api:script>

  <!-- To add support for DELETES please see the DELETE Execution page within the help for further information and examples. -->
  <api:script method="DELETE">
    <api:set attr="method" value="DELETE"/>
    <api:call op="jsonproviderGet">
      <api:throw code="500" desc="Deletes are not currently supported."/>
      <api:push/>
    </api:call>
  </api:script>

</api:script>