Couchbase is a schema-free document database that provides high performance, availability, and scalability. These features are not necessarily incompatible with a standards-compliant query language like SQL-92.
The adapter models the schema-free Couchbase objects into relational tables and translates SQL queries into N1QL or SQL++ (Analytics) queries to get the requested data. In this section we will show various schemes that the adapter offers to bridge the gap with relational SQL and a document database.
Automatic Schema Discovery
When the adapter first connects to Couchbase, it opens each bucket and scans a configurable number of rows from that bucket. It uses those rows to determine the columns in that bucket and their data types, as well as how to build flavored and child tables for any arrays within those documents. For Couchbase Enterprise version 4.5.1 and later, the adapter may can also be configured to use the INFER command when TypeDetectionScheme is set to INFER. This allows the adapter to get a more accurate column listing for the bucket, and to detect more complex flavors.
When using the Analytics service, the adapter only does column and child table detection. Flavored tables are provided by Couchbase itself using shadow datasets. Also, Analytics mode does not currently have INFER support, so only row scan is supported.
For more details, refer to Automatic Schema Discovery to see how flavored tables and child tables are modelled from Couchbase data. Setting NumericStrings is also recommended as it can avoid type detection issues with certain kinds of text data.
Custom Schema Definitions
Optionally, you can use Custom Schema Definitions to project your chosen relational structure on top of a Couchbase object. This allows you to define your chosen column names, their data types, and the locations of their values in the Couchbase document.
See Query Mapping for more details on how various N1QL and SQL++ operations are represented as SQL.
See Vertical Flattening for more details on how arrays and objects are mapped into fields.
See JSON Functions for more details on how to extract data from raw JSON strings.