JDBC Driver for Couchbase

Build 21.0.7930

Custom Schema Definitions

In addition to Automatic Schema Discovery the driver also allows you to statically define the schema for your Couchbase object. Schemas are defined in text-based configuration files, which makes them easy to extend. You can call the CreateSchema stored procedure to generate a schema file; see Automatic Schema Discovery for more information.

Set the Location property to the file directory that will contain the schema file. The following sections show how to extend the resulting schema or write your own.

Example Document

Let's consider the document below and extract out the nested properties as their own columns:

/* Primary key "1" */
{
  "id": 12,
  "name": "Lohia Manufacturers Inc.",
  "homeaddress": {"street": "Main "Street", "city": "Chapel Hill", "state": "NC"},
  "workaddress": {"street": "10th "Street", "city": "Chapel Hill", "state": "NC"}
  "offices": ["Chapel Hill", "London", "New York"]
  "annual_revenue": 35600000
}
/* Primary key "2" */
{
  "id": 15,
  "name": "Piago Industries",
  "homeaddress": {street": "Main Street", "city": "San Francisco", "state": "CA"},
  "workaddress": {street": "10th Street", "city": "San Francisco", "state": "CA"}
  "offices": ["Durham", "San Francisco"]
  "annual_revenue": 42600000
}

Custom Schema Definition


<rsb:info title="Customers" description="Customers" other:dataverse="" other:bucket=customers"" other:flavorexpr="" other:flavorvalue="" other:isarray="false" other:pathspec="" other:childpath="">
  <attr name="document.id"        xs:type="string"  key="true" other:iskey="true" other:pathspec=""  />
  <attr name="annual_revenue"     xs:type="integer" other:iskey="false"           other:pathspec=""  other:field="annual_revenue" />
  <attr name="homeaddress.city"   xs:type="string"  other:iskey="false"           other:pathspec="{" other:field="homeaddress.city" />
  <attr name="homeaddress.state"  xs:type="string"  other:iskey="false"           other:pathspec="{" other:field="homeaddress.state" />
  <attr name="homeaddress.street" xs:type="string"  other:iskey="false"           other:pathspec="{" other:field="homeaddress.street" />
  <attr name="name"               xs:type="string"  other:iskey="false"           other:pathspec=""  other:field="name" />
  <attr name="id"                 xs:type="integer" other:iskey="false"           other:pathspec=""  other:field="id" />
  <attr name="offices"            xs:type="string"  other:iskey="false"           other:pathspec=""  other:field="offices" />
  <attr name="offices.0"          xs:type="string"  other:iskey="false"           other:pathspec="[" other:field="offices.0" />
  <attr name="offices.1"          xs:type="string"  other:iskey="false"           other:pathspec="[" other:field="offices.1" />
  <attr name="workaddress.city"   xs:type="string"  other:iskey="false"           other:pathspec="{" other:field="workaddress.city" />
  <attr name="workaddress.state"  xs:type="string"  other:iskey="false"           other:pathspec="{" other:field="workaddress.state" />
  <attr name="workaddress.street" xs:type="string"  other:iskey="false"           other:pathspec="{" other:field="workaddress.street" />
</rsb:info>
In Custom Schema Example, you will find the complete schema that contains the example above.

Table Properties

The schema above uses the following properties to define specific qualities for the whole table. All of them are required:

Property Meaning
other:dataverse The name of the dataverse the dataset belongs to. Empty if not an Analytics view.
other:bucket The name of the bucket or dataset within Couchbase
other:flavorexpr The URL encoded condition in a flavored table. For example, "%60docType%60%20%3D%20%22chess%22".
other:flavorvalue The name of the flavor in a flavored table. For example, "chess".
other:isarray Whether the table is an array child table.
other:pathspec This is used to interpret the separators within other:childpath. See Column Properties for more details.
other:childpath The path to the attribute that is used to UNNEST the child table. Empty if not a child table.

Column Properties

The schema above uses the following properties to define specific qualities for each column:

Property Meaning
name Required. The name of the column, lower-cased.
key Used to mark the primary key. Required for Document.Id but optional for other columns.
xs:type Required. The type of the column within the driver.
other:iskey Required. Must be the same value as key, or "false" if key is not included.
other:pathspec Required. This is used to interpret the separators within other:field.
other:field Required. The path to the field in Couchbase.

Note that the fields which are produced by vertical flattening use the same syntax for separating array values and field values. This introduces a potential ambiguity in cases like the following, where the driver exposes the columns "numeric_object.0" and "array.0":

{
  "numeric_object": {
    "0": 0
  },
  "array": [
    0
  ]
}
To ensure that the driver can distinguish between field and array accesses, the pathspec is used to determine whether each "." in the field is an array or an object. Each "{" represents a field access, while each "[" represents an array access.

For example, with a field of "a.0.b.1" and a "pathspec" of "[{[", the N1QL expression "a[0].b[1]" would be generated. If instead the "pathspec" were "{{{", then the N1QL expression "a.`0`.b.`1`" would be generated.

Copyright (c) 2021 CData Software, Inc. - All rights reserved.
Build 21.0.7930