Create a connection to Elasticsearch by navigating to the Connections page in the Sync App application and selecting the corresponding icon in the Add Connections panel. If the Elasticsearch icon is not available, click the Add More icon to download and install the Elasticsearch connector from the CData site.

Required properties are listed under the Settings tab. The Advanced tab lists connection properties that are not typically required.

Connecting to Elasticsearch Service

Set the following to connect to data:

• Server should be set to the IP Address or domain of the Elasticsearch instance. The Server could also be set to a comma-delimited list of node addresses or hostnames from a single cluster.
  

  Server=https://01.02.03.04 
    OR 
  Server=https://01.01.01.01:1234,https://02.02.02.02:5678

• Port should be set to the configured port for the Elasticsearch instance. If you include a port in a node specification for the Server property, that included port will take precedence over the specification for Port for that node only.

The Sync App uses X-Pack Security for authentication and TLS/SSL encryption. You can prefix the server value with "https://" to connect using TLS/SSL.

Connecting to Amazon OpenSearch Service

Set the following to connect to data:

• Server should be set to the Endpoint URL for the Amazon ES instance.
• Port should be set to 443.
• AWSRegion should be set to the Amazon AWS region where the Elasticsearch instance is being hosted (the Sync App will attempt to automatically identify the region based on the Server value).

The Sync App uses X-Pack Security for authentication and TLS/SSL encryption.

Note: Requests are signed using AWS Signature Version 4.

Authenticating to Elasticsearch

Obtain AWS Keys

To obtain the credentials for an IAM user, follow the steps below:

1. Sign into the IAM console.
2. In the navigation pane, select Users.
3. To create or manage the access keys for a user, select the user and then go to the Security Credentials tab.

To obtain the credentials for your AWS root account, follow the steps below:

1. Sign into the AWS Management console with the credentials for your root account.
2. Select your account name or number and select My Security Credentials in the menu that is displayed.
3. Click Continue to Security Credentials and expand the "Access Keys" section to manage or create root account access keys.

Standard Authentication

Set the AuthScheme to Basic, and set User and Password properties and/or use PKI (public key infrastructure) to authenticate. Once the Sync App is connected, X-Pack performs user authentication and grants role permissions based on the realms you have configured.

To use PKI, set the SSLClientCert, SSLClientCertType, SSLClientCertSubject, and SSLClientCertPassword properties.

Note: TLS/SSL and client authentication must be enabled on X-Pack to use PKI.

Securing Elasticsearch Connections

To enable TLS/SSL in the Sync App, prefix the Server value with 'https://'.

Root Credentials

To authenticate using account root credentials, set the following:

• AuthScheme: Set this to AwsRootKeys.
• AWSAccessKey: The access key associated with the AWS root account.
• AWSSecretKey: The secret key associated with the AWS root account.

Note: Use of this authentication scheme is discouraged by Amazon for anything but simple tests. The account root credentials have the full permissions of the user, making this the least secure authentication method.

Temporary Credentials

To authenticate using temporary credentials, specify the following:

• AuthScheme: Set this to TemporaryCredentials.
• AWSAccessKey: The access key of the IAM user to assume the role for.
• AWSSecretKey: The secret key of the IAM user to assume the role for.
• AWSSessionToken: Your AWS session token. This will have been provided alongside your temporary credentials. See AWS Identity and Access Management User Guide for more info.

The Sync App can now request resources using the same permissions provided by long-term credentials (such as IAM user credentials) for the lifespan of the temporary credentials.

If you are also using an IAM role to authenticate, you must additionally specify the following:

• AWSRoleARN: Specify the Role ARN for the role you'd like to authenticate with. This will cause the Sync App to attempt to retrieve credentials for the specified role.
• AWSExternalId: Only if required when you assume a role in another account.

AWS IAM Roles

In many situations it may be preferable to use an IAM role for authentication instead of the direct security credentials of an AWS root user.

To authenticate as an AWS role, set the following:

• AuthScheme: Set this to AwsIAMRoles.
• AWSRoleARN: Specify the Role ARN for the role you'd like to authenticate with. This will cause the Sync App to attempt to retrieve credentials for the specified role.
• AWSExternalId: Only if required when you assume a role in another account.
• AWSAccessKey: The access key of the IAM user to assume the role for.
• AWSSecretKey: The secret key of the IAM user to assume the role for.

Note: Roles may not be used when specifying the AWSAccessKey and AWSSecretKey of an AWS root user.

Kerberos

Please see Using Kerberos for details on how to authenticate with Kerberos.

API Key

To authenticate using APIKey set the following:

• AuthScheme: Set this to APIKey.
• APIKey: Set this to APIKey returned from Elasticsearch.
• APIKeyId: Set this to the Id returned alongside APIKey.

This section shows how to use the Sync App to authenticate using Kerberos.

Kerberos

To authenticate to Elasticsearch using Kerberos, set the following properties:

• AuthScheme: Set this to NEGOTIATE.
• KerberosKDC: Set this to the host name or IP Address of your Kerberos KDC machine.
• KerberosSPN: Set this to the service and host of the Elasticsearch Kerberos Principal. This is the value prior to the '@' symbol (for instance, HTTP/MyHost) of the principal value (for instance, HTTP/[email protected]).

Retrieve the Kerberos Ticket

You can use one of the following options to retrieve the required Kerberos ticket.

MIT Kerberos Credential Cache File

This option enables you to use the MIT Kerberos Ticket Manager or kinit command to get tickets. Note that you do not need to set the User or Password connection properties with this option.

1. Ensure that you have an environment variable created called KRB5CCNAME.
2. Set the KRB5CCNAME environment variable to a path pointing to your credential cache file (for instance, C:\krb_cache\krb5cc_0 or /tmp/krb5cc_0). This file is created when generating your ticket with MIT Kerberos Ticket Manager.
3. To obtain a ticket, open the MIT Kerberos Ticket Manager application, click Get Ticket, enter your principal name and password, then click OK. If successful, ticket information appears in Kerberos Ticket Manager and is stored in the credential cache file.
4. Now that you have created the credential cache file, the Sync App uses the cache file to obtain the Kerberos ticket to connect to Elasticsearch.

As an alternative to setting the KRB5CCNAME environment variable, you can directly set the file path using the KerberosTicketCache property. When set, the Sync App uses the specified cache file to obtain the Kerberos ticket to connect to Elasticsearch.

Keytab File

If the KRB5CCNAME environment variable has not been set, you can retrieve a Kerberos ticket using a Keytab File. To do so, set the User property to the desired username and set the KerberosKeytabFile property to a file path pointing to the keytab file associated with the user.

User and Password

If both the KRB5CCNAME environment variable and the KerberosKeytabFile property have not been set, you can retrieve a ticket using a user and password combination. To do this, set the User and Password properties to the user/password combination that you use to authenticate with Elasticsearch.

Cross-Realm

More complex Kerberos environments may require cross-realm authentication where multiple realms and KDC servers are used (e.g., where one realm/KDC is used for user authentication and another realm/KDC is used for obtaining the service ticket).

In such an environment, set the KerberosRealm and KerberosKDC properties to the values required for user authentication. Also set the KerberosServiceRealm and KerberosServiceKDC properties to the values required to obtain the service ticket.

Fine Tuning Data Access

You can use the following properties to gain greater control over Elasticsearch API features and the strategies the Sync App uses to surface them:

• QueryPassthrough: This property enables you to use Elasticsearch's Search DSL language instead of SQL.
• RowScanDepth: This property determines the number of rows that will be scanned to detect column data types when generating table metadata. This property applies if you are working with the dynamic schemas generated from Automatic Schema Discovery or if you are using QueryPassthrough.

Querying Multiple Indices

Multiple indices can be queried by executing a query using one of the following formats:

• Query all indices via the _all view: SELECT * FROM [_all]

• Query a list of indices: SELECT * FROM [index1,index2,index3]

• Query indices matching a wildcard pattern: SELECT * FROM [index*]

Note, index lists can contain wildcards and indices can be excluded by prefixing an index with '-'. For example: SELECT * FROM [index*,-index3]

Fine Tuning Performance

• PageSize: This property enables you to optimize performance based on your resource provisioning.
  Paging has an impact on sorting performance in a distributed system, as each shard must first sort results before submitting them to the coordinating server.
  By default, the Sync App requests a page size of 10,000. This is the default index.max_result_window setting in Elasticsearch.
• MaxResults: This property sets a limit on the results for queries at connection time, without requiring that you specify a LIMIT clause.
  By default, this is the same value as the index.max_result_window setting in Elasticsearch.

  If you are using the Scroll API, set ScrollDuration instead.

• ScrollDuration: This property specifies how long the server should keep the search context alive. Setting this property to a nonzero value and time unit enables the Scroll API.

Elasticsearch is a document-oriented database that provides high performance searching, flexibility, and scalability. These features are not necessarily incompatible with a standards-compliant query language like SQL-92. In this section we will show various schemes that the Sync App offers to bridge the gap with relational SQL and an Elasticsearch database.

The Sync App models Elasticsearch objects into relational tables and translates SQL queries into Elasticsearch queries to get the requested data. See Schema Mapping for more details on how Elasticsearch objects are mapped to tables to generate schemas. See Query Mapping for more details on how various Elasticsearch operations are represented as SQL.

The Automatic Schema Discovery scheme automatically finds the data types by retrieving the mapping for the Elasticsearch type. You can use RowScanDepth, FlattenArrays, and FlattenObjects to control the relational representation of the collections in Elasticsearch.

The CData Sync App models the Elasticsearch REST APIs as relational tables and stored procedures that can be accessed with standard SQL. This enables access from standards-based tools.

The table definitions are dynamically retrieved. When you connect, the Sync App connects to Elasticsearch and retrieves the schemas, list of tables, and the metadata for the tables by querying the Elasticsearch REST server. Any changes to the remote data are immediately reflected in your queries.

The following table maps Elasticsearch concepts to relational ones:

Elasticsearch Versions 6 and Above:

Note: Starting in Elasticsearch 6, indices are limited to a single type. Therefore the type is no longer treated as a table, since an index and type have a one-to-one relation. Types are hidden and used internally where necessary to issue the proper request to Elasticsearch.

Elasticsearch Versions Prior to Version 6:

Elasticsearch contains the ability to establish parent-child relationships. This relationship maps closely to SQL JOIN functionality. The Sync App models these parent-child relationships in a way to enable the ability to perform JOIN queries.

Elasticsearch Versions 6 and Above:

In version 6 and above of Elasticsearch, relationships are established by using the join datatype. Included in this functionality is the ability to define multiple children for a single parent and to create multiple levels of relations.

The Sync App supports all of these relationships and will generate a separate table for each relation in Elasticsearch. The table name will be in the form: [index]_[relation].

All child tables will have an additional column containing the parent table id. The column name will be in the form: _[parent_table]_id. This column is a foreign key to the _id column of the parent table and can be used to perform SQL JOIN queries.

When querying these tables individually, filtering logic is pushed to the server to improve performance by only returning the data relevant to the table selected.

Elasticsearch Versions Prior to Version 6:

In versions prior to 6, a relationship is established between two types via a _parent field. This creates a single parent-child relationship.

The tables identified in this parent-child relationship do not change (they are still based on the Elasticsearch type). However the child table will have an additional column containing the parent id. The column name will be in the form: _[parent_table]_id. This column is a foreign key to the _id column of the parent table and can be used to perform SQL JOIN queries.

Below is the raw data used throughout this chapter. Following is the mapping for the "insured" table (index):

{
  "insured": {
    "mappings": {
      "properties": {
        "name": { "type":"string" },
        "address": {
          "street": { "type":"string" },
          "city": { "type":"string" },
          "state": { "type":"string" }
        },
        "insured_ages": { "type": "integer" },
        "vehicles": {
          "type": "nested",
          "properties": {
            "year": { "type":"integer" },
            "make": { "type":"string" },
            "model": { "type":"string" },
            "body_style" { "type": "string" }
          }
        }
      }
    }
  }
}

The following is the sample data set for the "insured" table (index):

{
  "hits": {
    "total": 2,
    "max_score": 1,
    "hits": [
      {
        "_index": "insured",
        "_type": "_doc",
        "_id": "1",
        "_score": 1,
        "_source": {
          "name": "John Smith",
          "address": {
            "street": "Main Street",
            "city": "Chapel Hill",
            "state": "NC"
          },
          "insured_ages": [ 17, 43, 45 ],
          "vehicles": [
            {
              "year": 2015,
              "make": "Dodge",
              "model": "RAM 1500",
              "body_style": "TK"
            },
            {
              "year": 2015,
              "make": "Suzuki",
              "model": "V-Strom 650 XT",
              "body_style": "MC"
            },
            {
              "year": 1992,
              "make": "Harley Davidson",
              "model": "FXR",
              "body_style": "MC"
            }
          ]
        }
      },
      {
        "_index": "insured",
        "_type": "_doc",
        "_id": "2",
        "_score": 1,
        "_source": {
          "name": "Joseph Newman",
          "address": {
            "street": "Oak Street",
            "city": "Raleigh",
            "state": "NC"
          },
          "insured_ages": [ 23, 25 ],
          "vehicles": [
            {
              "year": 2010,
              "make": "Honda",
              "model": "Accord",
              "body_style": "SD"
            },
            {
              "year": 2008,
              "make": "Honda",
              "model": "Civic",
              "body_style": "CP"
            }
          ]
        }
      }
    ]
  }
}

The Sync App automatically infers a relational schema by retrieving the mapping of the Elasticsearch type. The columns and data types are generated from the retrieved mapping.

Detecting Arrays

Any field within Elasticsearch can be an array of values, but this is not explicitly defined within the mapping. To account for this, the Sync App will query the data to detect if any fields contain arrays. The number of Elasticsearch documents retrieved during this array scanning is based on the RowScanDepth property.

Elasticsearch nested types are special types that denote an array of objects and thus will always be treated as such when generating the metadata.

Detecting Columns

The columns identified during the discovery process depend on the FlattenArrays and FlattenObjects properties.

Example Data Set

To provide an example of how these options work, consider the following mapping (where 'insured' is the name of the table):

{
  "insured": {
    "properties": {
      "name": { "type":"string" },
      "address": {
        "street": { "type":"string" },
        "city": { "type":"string" },
        "state": { "type":"string" }
      },
      "insured_ages": { "type": "integer" },
      "vehicles": {
        "type": "nested",
        "properties": {
          "year": { "type":"integer" },
          "make": { "type":"string" },
          "model": { "type":"string" },
          "body_style" { "type": "string" }
        }
      }
    }
  }
}

Also consider the following example data for the above mapping:

{
  "_source": {
    "name": "John Smith",
    "address": {
      "street": "Main Street",
      "city": "Chapel Hill",
      "state": "NC"
    },
    "insured_ages": [ 17, 43, 45 ], 
    "vehicles": [
      {
        "year": 2015,
        "make": "Dodge",
        "model": "RAM 1500",
        "body_style": "TK"
      },
      {
        "year": 2015,
        "make": "Suzuki",
        "model": "V-Strom 650 XT",
        "body_style": "MC"
      },
      {
        "year": 2012,
        "make": "Honda",
        "model": "Accord",
        "body_style": "4D"
      }
    ]
  }
}

Using FlattenObjects

If FlattenObjects is set, all nested objects will be flattened into a series of columns. The above example will be represented by the following columns:

If FlattenObjects is not set, then the address.street, address.city, and address.state columns will not be broken apart. The address column of type string will instead represent the entire object. Its value would be the following:

{street: "Main Street", city: "Chapel Hill", state: "NC"}

Using FlattenArrays

The FlattenArrays property can be used to flatten array values into columns of their own. This is only recommended for arrays that are expected to be short. It is best to leave unbounded arrays as they are and piece out the data for them as needed using JSON Functions.

Note: Only the top-most array will be flattened. Any subarrays will be represented as the entire array.

The FlattenArrays property can be set to 3 to represent the arrays in the example above as follows (this example is with FlattenObjects not set):

Using Both FlattenObjects and FlattenArrays

If FlattenObjects is set along with FlattenArrays (set to 1 for brevity), the vehicles field will be represented as follows:

The Sync App offers three basic configurations to model documents as tables, described in the following sections. The Sync App will parse the Elasticsearch document and identify the nested documents.

• Flattened Documents Model: Implicitly join nested documents into a single table.
• Relational Model: Model nested documents as individual tables containing a primary key and a foreign key that links to the parent document.
• Top-Level Document Model: Model a top-level view of an Elasticsearch document. Nested documents are returned as JSON strings.

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

Joining Object Arrays into a Single Table

With DataModel set to "FlattenedDocuments", nested documents will behave as separate tables and act in the same manner as a SQL JOIN. Any nested documents, at the same height (e.g. sibling documents), 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 insured document with the nested vehicles document.

Query

The following query drills into the nested documents in each insured document.

SELECT
  [_id],
  [name],
  [address.street] AS address_street,
  [address.city.first] AS address_city,
  [address.state.last] AS address_state,
  [insured_ages],
  [year],
  [make],
  [model],
  [body_style],
  [_insured_id],
  [_vehicles_c_id]
FROM
  [insured]

Results

See Also

• Automatic Schema Discovery: Configure the columns reported in the table schemas.
• FreeForm;: Use dot notation to select nested data.
• VerticalFlattening;: Access nested object arrays as separate tables.
• JSON Functions: Manipulate the data returned to perform client-side aggregation and transformations.

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

One aspect to consider is performance. You forego the time and resources to process and parse nested elements -- the Sync App parses the returned data once, using streaming to read the JSON 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 JSON.

Modeling a Top-Level Document View

With DataModel set to "Document" (the default), the Sync App scans only the top-level object by default. The top-level object elements are available as columns due to the default object flattening. Nested objects are returned as aggregated JSON.

Example

Below is a sample query and the results, based on the sample document in Raw Data. The query results in a single "insured" table.

Query

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

SELECT
  [_id],
  [name],
  [address.street] AS address_street,
  [address.city] AS address_city,
  [address.state] AS address_state,
  [insured_ages],
  [vehicles]
FROM
  [insured]
  

Results

With a document view of the data, the address object is flattened into 3 columns (when FlattenObjects set to true) and the _id, name, insured_ages, and vehicles elements are returned as individual columns, resulting in a table with 7 columns.

[{"year":2015,"make":"Dodge","model":"RAM 1500","body_style":"TK"},{"year":2015,"make":"Suzuki","model":"V-Strom 650 XT","body_style":"MC"},{"year":1992,"make":"Harley Davidson","model":"FXR","body_style":"MC"}]

[{"year":2010,"make":"Honda","model":"Accord","body_style":"SD"},{"year":2008,"make":"Honda","model":"Civic","body_style":"CP"}]

See Also

• Automatic Schema Discovery: Configure column discovery with horizontal flattening.
• FreeForm;: Use dot notation to select nested data.
• VerticalFlattening;: Access nested object arrays as separate tables.
• JSON Functions: Manipulate the data returned to perform client-side aggregation and transformations.

The CData Sync App can be configured to create a relational model of the data, treating nested documents as individual tables containing a primary key and a foreign key that links to the parent document. This is particularly useful if you need to work with your Elasticsearch data in existing BI, reporting, and ETL tools that expect a relational data model.

Joining Nested Arrays as Tables

With DataModel set to "Relational", any JOINs are controlled by the query. Any time you perform a JOIN query, the Elasticsearch index will be queried once for each table (nested document) included in the query.

Example

Below is a sample query against the sample document in Raw Data, using a relational model.

Query

The following query explicitly JOINs the insured and vehiclestables.

SELECT 
  [insured].[_id], 
  [insured].[name], 
  [insured].[address.street] AS address_street, 
  [insured].[address.city.first] AS address_city, 
  [insured].[address.state.last] AS address_state, 
  [insured].[insured_ages], 
  [vehicles].[year], 
  [vehicles].[make], 
  [vehicles].[model], 
  [vehicles].[body_style],
  [vehicles].[_insured_id],
  [vehicles].[_c_id]
FROM 
  [insured]
JOIN 
  [vehicles] 
ON 
  [insured].[_id] = [vehicles].[_insured_id]

Results

In the example query, each vehicle document is JOINed to its parent insured object to produce a table with 5 rows.

See Also

• Automatic Schema Discovery: Configure the columns reported in the table schemas.
• FreeForm;: Use dot notation to select nested data.
• VerticalFlattening;: Access nested object arrays as separate tables.
• JSON Functions: Manipulate the data returned to perform client-side aggregation and transformations.

The Sync App can return JSON structures as column values. The Sync App enables you to use standard SQL functions to work with these JSON structures. The examples in this section use the following array:

[
     { "grade": "A", "score": 2 },
     { "grade": "A", "score": 6 },
     { "grade": "A", "score": 10 },
     { "grade": "A", "score": 9 },
     { "grade": "B", "score": 14 }
]

JSON_EXTRACT

SELECT Name, JSON_EXTRACT(grades,'[0].grade') AS Grade, JSON_EXTRACT(grades,'[0].score') AS Score FROM Students;

JSON_COUNT

SELECT Name, JSON_COUNT(grades,'[x]') AS NumberOfGrades FROM Students;

JSON_SUM

SELECT Name, JSON_SUM(score,'[x].score') AS TotalScore FROM Students;

JSON_MIN

SELECT Name, JSON_MIN(score,'[x].score') AS LowestScore FROM Students;

JSON_MAX

SELECT Name, JSON_MAX(score,'[x].score') AS HighestScore FROM Students;

DOCUMENT

The DOCUMENT function can be used to retrieve the entire document as a JSON string. See the following query and its result as an example:

SELECT DOCUMENT(*) FROM Employee;

 
{
  "_index": "megacorp",
  "_type": "employee",
  "_id": "2",
  "_score": 1,
  "_source": {
    "first_name": "Jane",
    "last_name": "Smith",
    "age": 32,
    "about": "I like to collect rock albums",
    "interests": [
      "music"
    ]
  }
} 

This section describes how SQL statements are interpreted and translated into Elasticsearch queries. Examples are also provided to explain the behavior of various queries.

Query/Filter Context and Scoring

Text Matching and Search

To demonstrate this point, an analyzed field in Elasticsearch was created with a value of 'Bike'. After being analyzed, the value will be stored in the inverted index (using the default analyzer) as 'bike'. A non-analyzed field, on the other hand, would not analyze the search value and thus would be stored as 'Bike'.

When performing searches, some Elasticsearch query types run the search value through an analyzer (which will make the search case insensitive) and some do not (making the search case sensitive). Additionally, the default analyzer breaks up fields containing multiple words into separate terms. When performing searches on these fields, Elasticsearch may return records that contain the same words but in a different order. For example, a search is performed using a value of 'blue sky' but a record with 'sky blue' is returned.

To work around these case-sensitivity and ordering issues, the CData Sync App will identify the column as analyzed or non-analyzed and will issue the appropriate Elasticsearch query based on the specified operator (such as =) and the search value.

Equals and Not Equals

Analyzed Columns
Analyzed columns are stored after being run through an analyzer. As a result of that, the search values specified will be run through an analyzer on the Elasticsearch server prior to the search. This makes the searches case-insensitive (provided the analyzer used handles casing).

Non-Analyzed Columns
Non-analyzed columns are stored without being run through an analyzer. Thus, non-analyzed columns are case sensitive and thus search values specified for these columns are case sensitive. If the search value is a single word, the Sync App will check the filter with the original casing specified along with three common forms: uppercase, lowercase, and capitalized. If the search value contains multiple words, the search value will be sent as-is and thus is case sensitive.

IN and NOT IN

LIKE and NOT LIKE

Aggregate Filtering

JSON objects and arrays of objects will be treated as raw strings and all filtering will be performed by the Sync App. Therefore an equals operation must match the entire JSON aggregate to return a result, unless a CONTAINS or LIKE operation is used.

If JSON objects are flattened into individual columns (via FlattenObjects and FlattenArrays), the column for the specific JSON field will be treated as individual columns. Thus the data type will be that as contained in the Elasticsearch mapping and all filters will be pushed to the server (where applicable).

JSON primitive array aggregates will also be treated as raw strings by default and filters will be performed by the Sync App. To filter data based on whether a primitive array contains a single value, the INARRAY function can be used (e.g. INARRAY(column) = 'value'). When performing a search on array fields, Elasticsearch looks at each value individually within an array. Thus when the INARRAY function is specified in a WHERE clause, the filter will be pushed to the server which performs a search within an array.

Primitive arrays may consist of different data types, such as strings or ints. Therefore the INARRAY function supports comparison operators applicable to the data type within the Elasticsearch mapping for the field. For example, INARRAY(int_array) > 5, will return all rows of data in which the int_array contains a value greater than 5. Supported comparison operators include the use of the LIKE operator for string arrays.

View schemas persist the relational structure the Sync App infers for Elasticsearch types and queries. To provide an example of how custom schemas work, we will use the below mapping (where 'insured' is the name of the table).

{
  "insured": {
    "properties": {
      "name": { "type":"string" },
      "address": {
        "street": { "type":"string" },
        "city": { "type":"string" },
        "state": { "type":"string" }
      },
      "insured_ages": { "type": "integer" },
      "vehicles": {
        "type": "nested",
        "properties": {
          "year": { "type":"integer" },
          "make": { "type":"string" },
          "model": { "type":"string" },
          "body_style" { "type": "string" }
        }
      }
    }
  }
}

Also, consider the following example data for the above mapping:

{
  "_source": {
    "name": "John Smith",
    "address": {
      "street": "Main Street",
      "city": "Chapel Hill",
      "state": "NC"
    },
    "insured_ages": [ 17, 43, 45 ], 
    "vehicles": [
      {
        "year": 2015,
        "make": "Dodge",
        "model": "RAM 1500",
        "body_style": "TK"
      },
      {
        "year": 2015,
        "make": "Suzuki",
        "model": "V-Strom 650 XT",
        "body_style": "MC"
      },
      {
        "year": 2012,
        "make": "Honda",
        "model": "Accord",
        "body_style": "4D"
      }
    ]
  }
}

Defining a Custom Schema

Schemas persisted when GenerateSchemaFiles is set are placed into the folder specified by the Location property. For example, set GenerateSchemaFiles to "OnUse" and execute a SELECT query:

SELECT * FROM insured

You can then change column behavior in the resulting schema. The following schema uses the other:xPath property to define where the data for a particular column should be retrieved from. Using this model you can flatten arbitrary levels of hierarchy.

The es_index and es_type attributes specify the Elasticsearch index and type to retrieve. The es_index and es_type attributes give you the flexibility to use multiple schemas for the same type. If es_type is not specified, the filename determines the collection that is parsed.

Below is an example is an example of the column behavior markup. You can find a complete schema in Custom Schema Example.

  <rsb:script xmlns:rsb="http://www.rssbus.com/ns/rsbscript/2">  
  
    <rsb:info title="StaticInsured" description="Custom Schema for the Elasticsearch insured data set.">  
      <!-- Column definitions -->
      <attr name="_id"                         xs:type="string"  other:xPath="_id"                                                         other:sourceField="_id"                 other:analyzed="true"  />
      <attr name="_score"                      xs:type="double"  other:xPath="_score"                                                      other:sourceField="_score"              other:analyzed="true"  />
      <attr name="name"                        xs:type="string"  other:xPath="_source/name"                                                other:sourceField="name"                other:analyzed="true"  />
      <attr name="address.street"              xs:type="string"  other:xPath="_source/address/street"                                      other:sourceField="address.street"      other:analyzed="true"  />
      <attr name="address.city"                xs:type="string"  other:xPath="_source/address/city"                                        other:sourceField="address.city"        other:analyzed="true"  />
      <attr name="address.state"               xs:type="string"  other:xPath="_source/address/state"                                       other:sourceField="address.state"       other:analyzed="true"  />
      <attr name="insured_ages"                xs:type="string"  other:xPath="_source/insured_ages"          other:valueFormat="aggregate" other:sourceField="insured_ages"        other:analyzed="false" />
      <attr name="insured_ages.0"              xs:type="integer" other:xPath="_source/insured_ages[0]"                                     other:sourceField="insured_ages"        other:analyzed="false" />
      <attr name="vehicles"                    xs:type="string"  other:xPath="_source/vehicles"              other:valueFormat="aggregate" other:sourceField="vehicles"            other:analyzed="true"  />
      <attr name="vehicles.0.year"             xs:type="integer" other:xPath="_source/vehicles[0]/year"                                    other:sourceField="vehicles.year"       other:analyzed="true"  />
      <attr name="vehicles.0.make"             xs:type="string"  other:xPath="_source/vehicles[0]/make"                                    other:sourceField="vehicles.make"       other:analyzed="true"  />
      <attr name="vehicles.0.model"            xs:type="string"  other:xPath="_source/vehicles[0]/model"                                   other:sourceField="vehicles.model"      other:analyzed="true"  />
      <attr name="vehicles.0.body_style"       xs:type="string"  other:xPath="_source/vehicles[0]/body_style"                              other:sourceField="vehicles.body_style" other:analyzed="true"  />
  
      <input name="rows@next" desc="Internal attribute used for paging through data."  />
    </rsb:info>  
  
  
    <rsb:set attr="es_index" value="auto"/>
    <rsb:set attr="es_type"  value="insured"/>
  
  </rsb:script>
  

In this section is a complete schema. The info section enables a relational view of an Elasticsearch object. For more details, see Custom Schema Definitions. The table below only supports SELECT commands. INSERT, UPDATE, and DELETE commands are not currently supported.

Use the es_index and es_type attributes to specify the name of the Elasticsearch type and index you want to retrieve and parse. You can use the es_index and es_type attributes to define multiple schemas for the same Elasticsearch type.

If es_type is not specified, the filename determines the Elasticsearch type that is parsed.

Copy the rows@next input as-is into your schema. The operations, such as elasticsearchadoSelect, are internal implementations and can also be copied as is.

<rsb:script xmlns:rsb="http://www.rssbus.com/ns/rsbscript/2">  

  <rsb:info title="StaticInsured" description="Custom Schema for the Elasticsearch insured data set.">  
    <!-- Column definitions -->
    <attr name="_id"                         xs:type="string"  other:xPath="_id"                                                         other:sourceField="_id"                 other:analyzed="true"  />
    <attr name="_score"                      xs:type="double"  other:xPath="_score"                                                      other:sourceField="_score"              other:analyzed="true"  />
    <attr name="name"                        xs:type="string"  other:xPath="_source/name"                                                other:sourceField="name"                other:analyzed="true"  />
    <attr name="address.street"              xs:type="string"  other:xPath="_source/address/street"                                      other:sourceField="address.street"      other:analyzed="true"  />
    <attr name="address.city"                xs:type="string"  other:xPath="_source/address/city"                                        other:sourceField="address.city"        other:analyzed="true"  />
    <attr name="address.state"               xs:type="string"  other:xPath="_source/address/state"                                       other:sourceField="address.state"       other:analyzed="true"  />
    <attr name="insured_ages"                xs:type="string"  other:xPath="_source/insured_ages"          other:valueFormat="aggregate" other:sourceField="insured_ages"        other:analyzed="false" />
    <attr name="insured_ages.0"              xs:type="integer" other:xPath="_source/insured_ages[0]"                                     other:sourceField="insured_ages"        other:analyzed="false" />
    <attr name="vehicles"                    xs:type="string"  other:xPath="_source/vehicles"              other:valueFormat="aggregate" other:sourceField="vehicles"            other:analyzed="true"  />
    <attr name="vehicles.0.year"             xs:type="integer" other:xPath="_source/vehicles[0]/year"                                    other:sourceField="vehicles.year"       other:analyzed="true"  />
    <attr name="vehicles.0.make"             xs:type="string"  other:xPath="_source/vehicles[0]/make"                                    other:sourceField="vehicles.make"       other:analyzed="true"  />
    <attr name="vehicles.0.model"            xs:type="string"  other:xPath="_source/vehicles[0]/model"                                   other:sourceField="vehicles.model"      other:analyzed="true"  />
    <attr name="vehicles.0.body_style"       xs:type="string"  other:xPath="_source/vehicles[0]/body_style"                              other:sourceField="vehicles.body_style" other:analyzed="true"  />

    <input name="rows@next" desc="Internal attribute used for paging through data."  />
  </rsb:info>  

  <rsb:set attr="es_index" value="auto"/>
  <rsb:set attr="es_type"  value="insured"/>

  <rsb:script method="GET">
    <rsb:call op="elasticsearchadoSelect">
      <rsb:push/>
    </rsb:call>
  </rsb:script>

  <rsb:script method="POST">
    <rsb:call op="elasticsearchadoModify">
      <rsb:push/>
    </rsb:call>
  </rsb:script>

  <rsb:script method="MERGE">
    <rsb:call op="elasticsearchadoModify">
      <rsb:push/>
    </rsb:call>
  </rsb:script>

  <rsb:script method="DELETE">
    <rsb:call op="elasticsearchadoModify">
      <rsb:push/>
    </rsb:call>
  </rsb:script>

</rsb:script>

This section details a selection of advanced features of the Elasticsearch Sync App.

User Defined Views

The Sync App allows you to define virtual tables, called user defined views, whose contents are decided by a pre-configured query. These views are useful when you cannot directly control queries being issued to the drivers. See User Defined Views for an overview of creating and configuring custom views.

SSL Configuration

Use SSL Configuration to adjust how Sync App handles TLS/SSL certificate negotiations. You can choose from various certificate formats; see the SSLServerCert property under "Connection String Options" for more information.

Firewall and Proxy

Configure the Sync App for compliance with Firewall and Proxy, including Windows proxies and HTTP proxies. You can also set up tunnel connections.

Query Processing

The Sync App offloads as much of the SELECT statement processing as possible to Elasticsearch and then processes the rest of the query in memory (client-side).

See Query Processing for more information.

Logging

See Logging for an overview of configuration settings that can be used to refine CData logging. For basic logging, you only need to set two connection properties, but there are numerous features that support more refined logging, where you can select subsets of information to be logged using the LogModules connection property.

Customizing the SSL Configuration

By default, the Sync App attempts to negotiate SSL/TLS by checking the server's certificate against the system's trusted certificate store.

To specify another certificate, see the SSLServerCert property for the available formats to do so.

Client SSL Certificates

The Elasticsearch Sync App also supports setting client certificates. Set the following to connect using a client certificate.

• SSLClientCert: The name of the certificate store for the client certificate.
• SSLClientCertType: The type of key store containing the TLS/SSL client certificate.
• SSLClientCertPassword: The password for the TLS/SSL client certificate.
• SSLClientCertSubject: The subject of the TLS/SSL client certificate.

Connecting Through a Firewall or Proxy

HTTP Proxies

To connect through the Windows system proxy, you do not need to set any additional connection properties. To connect to other proxies, set ProxyAutoDetect to false.

In addition, to authenticate to an HTTP proxy, set ProxyAuthScheme, ProxyUser, and ProxyPassword, in addition to ProxyServer and ProxyPort.

Other Proxies

Set the following properties:

• To use a proxy-based firewall, set FirewallType, FirewallServer, and FirewallPort.
• To tunnel the connection, set FirewallType to TUNNEL.
• To authenticate, specify FirewallUser and FirewallPassword.
• To authenticate to a SOCKS proxy, additionally set FirewallType to SOCKS5.

The CData Sync App models Elasticsearch entities in relational Tables, Views, and Stored Procedures.

Tables

Searching with SQL describes in further detail how the tables are dynamically retrieved.

Views

Views are treated in a similar manner to Tables and thus exhibit similar behavior. There are some differences in the background though which are a direct result of how aliases work within Elasticsearch. (Note: In the following description, 'alias', 'index', 'type', and 'field' are referring to the Elasticsearch objects and not directly to anything within the Sync App).

Views (aliases) are tied to an index and thus span all the types within an index. Additionally aliases can span multiple indices. Therefore you may see an alias (view) listed multiple times under different schemas (index). When querying the view, regardless of the schema specified, data will be retrieved and returned for all indices and types associated with the corresponding alias. Thus the generated metadata will contain a column for each field within each type of each index associated with the alias.

Searching with SQL describes in further detail how the views are dynamically retrieved.

The ModifyIndexAliases stored procedure can be used to create index aliases within Elasticsearch.

In addition to the Elasticsearch aliases, an '_all' view is returned which enables querying the _all endpoint to retrieve data for all indices in a single query.

Stored Procedures

Data Type Mappings

The Sync App maps types from the data source to the corresponding data type available in the schema. The table below documents these mappings.

*Parsed into multiple fields with individual types (see FlattenArrays)

The connection string properties are the various options that can be used to establish a connection. This section provides a complete list of the options you can configure in the connection string for this provider. Click the links for further details.

Authentication

Connection

AWS Authentication

Kerberos

SSL

Firewall

Proxy

Logging

Schema

Miscellaneous

This section provides a complete list of the Authentication properties you can configure in the connection string for this provider.

The scheme used for authentication. Accepted entries are None, Basic, Negotiate (Kerberos), AwsRootKeys, AwsIAMRoles, and APIKey. None is the default.

Remarks

This field is used to authenticate against the server. Use the following options to select your authentication scheme:

• None: No authentication is performed, unless User and Password properties are set in which BASIC authentication will be performed.
• Basic: Basic authentication is performed.
• Negotiate: If AuthScheme is set to Negotiate, the Sync App will negotiate an authentication mechanism with the server. Set AuthScheme to Negotiate if you want to use Kerberos authentication.
• AwsRootKeys: Set this to use the root user access key and secret. Useful for quickly testing, but production use cases are encouraged to use something with narrowed permissions.
• AwsIAMRoles: Set to use IAM Roles for the connection.
• APIKey: Set to use APIKey and APIKeyId for the connection.

The user who is authenticating to Elasticsearch.

Remarks

The user who is authenticating to Elasticsearch.

The password used to authenticate to Elasticsearch.

Remarks

The password used to authenticate to Elasticsearch.

The host name or IP address of the Elasticsearch REST server. Alternatively, multiple nodes in a single cluster can be specified, though all such nodes must be able to support REST API calls.

Remarks

The host name or IP address of the Elasticsearch REST server. Alternatively, multiple nodes in a single cluster can be specified, though all such nodes must be able to support REST API calls.

To use SSL, prefix the host name or IP address with 'https://' and set SSL connection properties such as SSLServerCert.

To specify multiple nodes, set the property to a comma delimited list of addresses, with ports optionally specified after the address and delimited from the address by a colon. For example, you could specify two dedicated, coordinating nodes for your cluster with 'https://01.01.01.01:1234,https://02.02.02.02:5678'. If a port is specified with a node, that port will take precedence over the Port property for connections to that node only.

The port for the Elasticsearch REST server.

Remarks

The port the Elasticsearch REST server is bound to.

The APIKey used to authenticate to Elasticsearch.

Remarks

The APIKey used to authenticate to Elasticsearch.

The APIKey Id to authenticate to Elasticsearch.

Remarks

The APIKey Id to authenticate to Elasticsearch.

This section provides a complete list of the Connection properties you can configure in the connection string for this provider.

Specifies the data model to use when parsing Elasticsearch documents and generating the database metadata.

Remarks

Select a DataModel configuration to configure how the Sync App models nested documents into tables. See Parsing Hierarchical Data for examples of querying the data in the different configurations.

Selecting a Data Modeling Strategy

The following DataModel configurations are available. See Parsing Hierarchical Data for examples of querying the data in the different configurations.

• Document

  Returns a single table representing a row for each document. In this data model, any nested documents will not be flattened and will be returned as aggregates.

• FlattenedDocuments

  Returns a single table representing a JOIN of the parent and nested documents. In this data model, nested documents will act in the same manner as a SQL JOIN. Additionally, nested sibling documents (nested documents at same height), will be treated as a SQL CROSS JOIN. The Sync App will identify the nested documents available by parsing the returned document.

• Relational

  Returns multiple tables, one for each nested document (including the parent document) in the document. In this data model, any nested documents will be returned as relational tables that contain a primary key and a foreign key that links to the parent table.

See Also

• FlattenArrays and FlattenObjects: Customize the columns that will be identified for each of these data models. See Automatic Schema Discovery for examples of using these properties.
• Parsing Hierarchical Data: Compare the schemas resulting from different DataModel settings, with example queries.
• Searching with SQL: Learn about the data modeling and flattening techniques available in the Sync App.

This section provides a complete list of the AWS Authentication properties you can configure in the connection string for this provider.

Your AWS account access key. This value is accessible from your AWS security credentials page.

Remarks

Your AWS account access key. This value is accessible from your AWS security credentials page:

1. Sign into the AWS Management console with the credentials for your root account.
2. Select your account name or number and select My Security Credentials in the menu that is displayed.
3. Click Continue to Security Credentials and expand the Access Keys section to manage or create root account access keys.

Your AWS account secret key. This value is accessible from your AWS security credentials page.

Remarks

Your AWS account secret key. This value is accessible from your AWS security credentials page:

1. Sign into the AWS Management console with the credentials for your root account.
2. Select your account name or number and select My Security Credentials in the menu that is displayed.
3. Click Continue to Security Credentials and expand the Access Keys section to manage or create root account access keys.

The Amazon Resource Name of the role to use when authenticating.

Remarks

When authenticating outside of AWS, it is common to use a Role for authentication instead of your direct AWS account credentials. Entering the AWSRoleARN will cause the CData Sync App to perform a role based authentication instead of using the AWSAccessKey and AWSSecretKey directly. The AWSAccessKey and AWSSecretKey must still be specified to perform this authentication. You cannot use the credentials of an AWS root user when setting RoleARN. The AWSAccessKey and AWSSecretKey must be those of an IAM user.

The hosting region for your Amazon Web Services.

Remarks

The hosting region for your Amazon Web Services. Available values are OHIO, NORTHERNVIRGINIA, NORTHERNCALIFORNIA, OREGON, CAPETOWN, HONGKONG, JAKARTA, MUMBAI, OSAKA, SEOUL, SINGAPORE, SYDNEY, TOKYO, CENTRAL, BEIJING, NINGXIA, FRANKFURT, IRELAND, LONDON, MILAN, PARIS, STOCKHOLM, ZURICH, BAHRAIN, UAE, SAOPAULO, GOVCLOUDEAST, and GOVCLOUDWEST.

Your AWS session token.

Remarks

Your AWS session token. This value can be retrieved in different ways. See this link for more info.

The amount of time (in seconds) an AWS temporary token will last.

Remarks

Temporary tokens are used with Role based authentication. Temporary tokens will eventually time out, at which time a new temporary token must be obtained. The CData Sync App will internally request a new temporary token once the temporary token has expired.

For Role based authentication, the minimum duration is 900 seconds (15 minutes) while the maximum if 3600 (1 hour).

A unique identifier that might be required when you assume a role in another account.

Remarks

A unique identifier that might be required when you assume a role in another account.

This section provides a complete list of the Kerberos properties you can configure in the connection string for this provider.

The Kerberos Key Distribution Center (KDC) service used to authenticate the user.

Remarks

The Kerberos properties are used when using SPNEGO or Windows Authentication. The Sync App will request session tickets and temporary session keys from the Kerberos KDC service. The Kerberos KDC service is conventionally colocated with the domain controller.

If Kerberos KDC is not specified, the Sync App will attempt to detect these properties automatically from the following locations:

• KRB5 Config File (krb5.ini/krb5.conf): If the KRB5_CONFIG environment variable is set and the file exists, the Sync App will obtain the KDC from the specified file. Otherwise, it will attempt to read from the default MIT location based on the OS: C:\ProgramData\MIT\Kerberos5\krb5.ini (Windows) or /etc/krb5.conf (Linux).
• Domain Name and Host: If the Kerberos Realm and Kerberos KDC could not be inferred from another location, the Sync App will infer them from the configured domain name and host.

The Kerberos Realm used to authenticate the user.

Remarks

The Kerberos properties are used when using SPNEGO or Windows Authentication. The Kerberos Realm is used to authenticate the user with the Kerberos Key Distribution Service (KDC). The Kerberos Realm can be configured by an administrator to be any string, but conventionally it is based on the domain name.

If Kerberos Realm is not specified, the Sync App will attempt to detect these properties automatically from the following locations:

• KRB5 Config File (krb5.ini/krb5.conf): If the KRB5_CONFIG environment variable is set and the file exists, the Sync App will obtain the default realm from the specified file. Otherwise, it will attempt to read from the default MIT location based on the OS: C:\ProgramData\MIT\Kerberos5\krb5.ini (Windows) or /etc/krb5.conf (Linux)
• Domain Name and Host: If the Kerberos Realm and Kerberos KDC could not be inferred from another location, the Sync App will infer them from the user-configured domain name and host. This might work in some Windows environments.

The service principal name (SPN) for the Kerberos Domain Controller.

Remarks

If the SPN on the Kerberos Domain Controller is not the same as the URL that you are authenticating to, use this property to set the SPN.

The Keytab file containing your pairs of Kerberos principals and encrypted keys.

Remarks

The Keytab file containing your pairs of Kerberos principals and encrypted keys.

The Kerberos realm of the service.

Remarks

The KerberosServiceRealm is the specify the service Kerberos realm when using cross-realm Kerberos authentication.

In most cases, a single realm and KDC machine are used to perform the Kerberos authentication and this property is not required.

This property is available for complex setups where a different realm and KDC machine are used to obtain an authentication ticket (AS request) and a service ticket (TGS request).

The Kerberos KDC of the service.

Remarks

The KerberosServiceKDC is used to specify the service Kerberos KDC when using cross-realm Kerberos authentication.

In most cases, a single realm and KDC machine are used to perform the Kerberos authentication and this property is not required.

This property is available for complex setups where a different realm and KDC machine are used to obtain an authentication ticket (AS request) and a service ticket (TGS request).

The full file path to an MIT Kerberos credential cache file.

Remarks

This property can be set if you wish to use a credential cache file that was created using the MIT Kerberos Ticket Manager or kinit command.

This section provides a complete list of the SSL properties you can configure in the connection string for this provider.

The TLS/SSL client certificate store for SSL Client Authentication (2-way SSL).

Remarks

The name of the certificate store for the client certificate.

The SSLClientCertType field specifies the type of the certificate store specified by SSLClientCert. If the store is password protected, specify the password in SSLClientCertPassword.

SSLClientCert is used in conjunction with the SSLClientCertSubject field in order to specify client certificates. If SSLClientCert has a value, and SSLClientCertSubject is set, a search for a certificate is initiated. See SSLClientCertSubject for more information.

Designations of certificate stores are platform-dependent.

The following are designations of the most common User and Machine certificate stores in Windows:

In Java, the certificate store normally is a file containing certificates and optional private keys.

When the certificate store type is PFXFile, this property must be set to the name of the file. When the type is PFXBlob, the property must be set to the binary contents of a PFX file (for example, PKCS12 certificate store).

The type of key store containing the TLS/SSL client certificate.

Remarks

This property can take one of the following values:

The password for the TLS/SSL client certificate.

Remarks

If the certificate store is of a type that requires a password, this property is used to specify that password to open the certificate store.

The subject of the TLS/SSL client certificate.

Remarks

When loading a certificate the subject is used to locate the certificate in the store.

If an exact match is not found, the store is searched for subjects containing the value of the property. If a match is still not found, the property is set to an empty string, and no certificate is selected.

The special value "*" picks the first certificate in the certificate store.

The certificate subject is a comma separated list of distinguished name fields and values. For example, "CN=www.server.com, OU=test, C=US, [email protected]". The common fields and their meanings are shown below.

If a field value contains a comma, it must be quoted.

The certificate to be accepted from the server when connecting using TLS/SSL.

Remarks

If using a TLS/SSL connection, this property can be used to specify the TLS/SSL certificate to be accepted from the server. Any other certificate that is not trusted by the machine is rejected.

This property can take the following forms:

If not specified, any certificate trusted by the machine is accepted.

Use '*' to signify to accept all certificates. Note that this is not recommended due to security concerns.

This section provides a complete list of the Firewall properties you can configure in the connection string for this provider.

The protocol used by a proxy-based firewall.

Remarks

This property specifies the protocol that the Sync App will use to tunnel traffic through the FirewallServer proxy. Note that by default, the Sync App connects to the system proxy; to disable this behavior and connect to one of the following proxy types, set ProxyAutoDetect to false.

To connect to HTTP proxies, use ProxyServer and ProxyPort. To authenticate to HTTP proxies, use ProxyAuthScheme, ProxyUser, and ProxyPassword.

The name or IP address of a proxy-based firewall.

Remarks

This property specifies the IP address, DNS name, or host name of a proxy allowing traversal of a firewall. The protocol is specified by FirewallType: Use FirewallServer with this property to connect through SOCKS or do tunneling. Use ProxyServer to connect to an HTTP proxy.

Note that the Sync App uses the system proxy by default. To use a different proxy, set ProxyAutoDetect to false.

The TCP port for a proxy-based firewall.

Remarks

This specifies the TCP port for a proxy allowing traversal of a firewall. Use FirewallServer to specify the name or IP address. Specify the protocol with FirewallType.

The user name to use to authenticate with a proxy-based firewall.

Remarks

The FirewallUser and FirewallPassword properties are used to authenticate against the proxy specified in FirewallServer and FirewallPort, following the authentication method specified in FirewallType.

A password used to authenticate to a proxy-based firewall.

Remarks

This property is passed to the proxy specified by FirewallServer and FirewallPort, following the authentication method specified by FirewallType.

This section provides a complete list of the Proxy properties you can configure in the connection string for this provider.

This indicates whether to use the system proxy settings or not. This takes precedence over other proxy settings, so you'll need to set ProxyAutoDetect to FALSE in order use custom proxy settings.

Remarks

This takes precedence over other proxy settings, so you'll need to set ProxyAutoDetect to FALSE in order use custom proxy settings.

To connect to an HTTP proxy, see ProxyServer. For other proxies, such as SOCKS or tunneling, see FirewallType.

The hostname or IP address of a proxy to route HTTP traffic through.

Remarks

The hostname or IP address of a proxy to route HTTP traffic through. The Sync App can use the HTTP, Windows (NTLM), or Kerberos authentication types to authenticate to an HTTP proxy.

If you need to connect through a SOCKS proxy or tunnel the connection, see FirewallType.

By default, the Sync App uses the system proxy. If you need to use another proxy, set ProxyAutoDetect to false.

The TCP port the ProxyServer proxy is running on.

Remarks

The port the HTTP proxy is running on that you want to redirect HTTP traffic through. Specify the HTTP proxy in ProxyServer. For other proxy types, see FirewallType.

The authentication type to use to authenticate to the ProxyServer proxy.

Remarks

This value specifies the authentication type to use to authenticate to the HTTP proxy specified by ProxyServer and ProxyPort.

Note that the Sync App will use the system proxy settings by default, without further configuration needed; if you want to connect to another proxy, you will need to set ProxyAutoDetect to false, in addition to ProxyServer and ProxyPort. To authenticate, set ProxyAuthScheme and set ProxyUser and ProxyPassword, if needed.

The authentication type can be one of the following:

• BASIC: The Sync App performs HTTP BASIC authentication.
• DIGEST: The Sync App performs HTTP DIGEST authentication.
• NEGOTIATE: The Sync App retrieves an NTLM or Kerberos token based on the applicable protocol for authentication.
• PROPRIETARY: The Sync App does not generate an NTLM or Kerberos token. You must supply this token in the Authorization header of the HTTP request.

If you need to use another authentication type, such as SOCKS 5 authentication, see FirewallType.

A user name to be used to authenticate to the ProxyServer proxy.

Remarks

The ProxyUser and ProxyPassword options are used to connect and authenticate against the HTTP proxy specified in ProxyServer.

You can select one of the available authentication types in ProxyAuthScheme. If you are using HTTP authentication, set this to the user name of a user recognized by the HTTP proxy. If you are using Windows or Kerberos authentication, set this property to a user name in one of the following formats:

user@domain
domain\user

A password to be used to authenticate to the ProxyServer proxy.

Remarks

This property is used to authenticate to an HTTP proxy server that supports NTLM (Windows), Kerberos, or HTTP authentication. To specify the HTTP proxy, you can set ProxyServer and ProxyPort. To specify the authentication type, set ProxyAuthScheme.

If you are using HTTP authentication, additionally set ProxyUser and ProxyPassword to HTTP proxy.

If you are using NTLM authentication, set ProxyUser and ProxyPassword to your Windows password. You may also need these to complete Kerberos authentication.

For SOCKS 5 authentication or tunneling, see FirewallType.

By default, the Sync App uses the system proxy. If you want to connect to another proxy, set ProxyAutoDetect to false.

The SSL type to use when connecting to the ProxyServer proxy.

Remarks

This property determines when to use SSL for the connection to an HTTP proxy specified by ProxyServer. This value can be AUTO, ALWAYS, NEVER, or TUNNEL. The applicable values are the following:

A semicolon separated list of destination hostnames or IPs that are exempt from connecting through the ProxyServer .

Remarks

The ProxyServer is used for all addresses, except for addresses defined in this property. Use semicolons to separate entries.

Note that the Sync App uses the system proxy settings by default, without further configuration needed; if you want to explicitly configure proxy exceptions for this connection, you need to set ProxyAutoDetect = false, and configure ProxyServer and ProxyPort. To authenticate, set ProxyAuthScheme and set ProxyUser and ProxyPassword, if needed.

This section provides a complete list of the Logging properties you can configure in the connection string for this provider.

Core modules to be included in the log file.

Remarks

Only the modules specified (separated by ';') will be included in the log file. By default all modules are included.

See the Logging page for an overview.

This section provides a complete list of the Schema properties you can configure in the connection string for this provider.

A path to the directory that contains the schema files defining tables, views, and stored procedures.

Remarks

The path to a directory which contains the schema files for the Sync App (.rsd files for tables and views, .rsb files for stored procedures). The folder location can be a relative path from the location of the executable. The Location property is only needed if you want to customize definitions (for example, change a column name, ignore a column, and so on) or extend the data model with new tables, views, or stored procedures.

If left unspecified, the default location is "%APPDATA%\\CData\\Elasticsearch Data Provider\\Schema" with %APPDATA% being set to the user's configuration directory:

This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC.

Remarks

Listing the schemas from databases can be expensive. Providing a list of schemas in the connection string improves the performance.

This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA,TableB,TableC.

Remarks

Listing the tables from some databases can be expensive. Providing a list of tables in the connection string improves the performance of the Sync App.

This property can also be used as an alternative to automatically listing views if you already know which ones you want to work with and there would otherwise be too many to work with.

Specify the tables you want in a comma-separated list. Each table should be a valid SQL identifier with any special characters escaped using square brackets, double-quotes or backticks. For example, Tables=TableA,[TableB/WithSlash],WithCatalog.WithSchema.`TableC With Space`.

Note that when connecting to a data source with multiple schemas or catalogs, you will need to provide the fully qualified name of the table in this property, as in the last example here, to avoid ambiguity between tables that exist in multiple catalogs or schemas.

Restricts the views reported to a subset of the available tables. For example, Views=ViewA,ViewB,ViewC.

Remarks

Listing the views from some databases can be expensive. Providing a list of views in the connection string improves the performance of the Sync App.

This property can also be used as an alternative to automatically listing views if you already know which ones you want to work with and there would otherwise be too many to work with.

Specify the views you want in a comma-separated list. Each view should be a valid SQL identifier with any special characters escaped using square brackets, double-quotes or backticks. For example, Views=ViewA,[ViewB/WithSlash],WithCatalog.WithSchema.`ViewC With Space`.

Note that when connecting to a data source with multiple schemas or catalogs, you will need to provide the fully qualified name of the table in this property, as in the last example here, to avoid ambiguity between tables that exist in multiple catalogs or schemas.

Set FlattenObjects to true to flatten object properties into columns of their own. Otherwise, objects nested in arrays are returned as strings of JSON.

Remarks

Set FlattenObjects to true to flatten object properties into columns of their own. Otherwise, objects nested in arrays are returned as strings of JSON. The property name is concatenated onto the object name with a period to generate the column name.

For example, you can flatten the nested objects below at connection time:

"manager": {
  "name": "Alice White",
  "age": 30
}

Set FlattenArrays to the number of nested array elements you want to return as table columns. By default, nested arrays are returned as strings of JSON.

Remarks

By default, nested arrays are returned as strings of JSON. The FlattenArrays property can be used to flatten the elements of nested arrays into columns of their own. This is only recommended for arrays that are expected to be short.

Set FlattenArrays to the number of elements you want to return from nested arrays. The specified elements are returned as columns. The zero-based index is concatenated to the column name. Other elements are ignored.

For example, you can return an arbitrary number of elements from an array of strings:

"employees": [
  {
    "name": "John Smith",
    "age": 34
  },
  {
    "name": "Peter Brown",
    "age": 26
  },
  {
    "name": "Paul Jacobs",
    "age": 30
  }
]

See JSON Functions to use JSON paths to work with unbounded arrays.

This section provides a complete list of the Miscellaneous properties you can configure in the connection string for this provider.

Set ClientSideEvaluation to true to perform Evaluation client side on nested objects.

Remarks

Set ClientSideEvaluation to true to perform Evaluation (GROUP BY, filtering) client side on nested objects.

For example, with ClientSideEvaluation set to false(default value), GROUP BY on nested object 'property.0.name' would be grouped as 'property.*.name', while if set to true, results would be grouped as 'property.0.name'.

Similarly, with ClientSideEvaluation set to false(default value), filtering on nested object 'property.0.name' would be filtered as 'property.*.name', while if set to true, results would be filtered as 'property.0.name'.

This would affect performance as query is evaluated client side.

Indicates the user preference as to when schemas should be generated and saved.

Remarks

This property outputs schemas to .rsd files in the path specified by Location.

Available settings are the following:

• Never: A schema file will never be generated.
• OnUse: A schema file will be generated the first time a table is referenced, provided the schema file for the table does not already exist.
• OnStart: A schema file will be generated at connection time for any tables that do not currently have a schema file.
• OnCreate: A schema file will be generated by when running a CREATE TABLE SQL query.

Generate Schemas with SQL

When you set GenerateSchemaFiles to OnUse, the Sync App generates schemas as you execute SELECT queries. Schemas are generated for each table referenced in the query.

When you set GenerateSchemaFiles to OnCreate, schemas are only generated when a CREATE TABLE query is executed.

Generate Schemas on Connection

Another way to use this property is to obtain schemas for every table in your database when you connect. To do so, set GenerateSchemaFiles to OnStart and connect.

The maximum number of total results to return from Elasticsearch when using the default Search API.

Remarks

This property corresponds to the Elasticsearch index.max_result_window index setting. Thus the default value is 10000, which is Elasticsearch's default limit.

This value is not applicable when using the Scroll API. Set ScrollDuration to use this API.

When a LIMIT is specified in a query, the LIMIT will be taken into account provided it is less than MaxResults. Otherwise the number of results returned will be limited to the MaxResults value.

If you receive an error stating that the result window is too large, this is caused by the MaxResults value being greater than the Elasticsearch index.max_result_window index setting. You can either change the MaxResults value to match the index.max_result_window index setting or use the Scroll API by setting ScrollDuration.

Limits the number of rows returned rows when no aggregation or group by is used in the query. This helps avoid performance issues at design time.

Remarks

Limits the number of rows returned rows when no aggregation or group by is used in the query. This helps avoid performance issues at design time.

These hidden properties are used only in specific use cases.

Remarks

The properties listed below are available for specific use cases. Normal driver use cases and functionality should not require these properties.

Specify multiple properties in a semicolon-separated list.

Integration and Formatting

The number of results to return per request from Elasticsearch.

Remarks

The PageSize can control the number of results received per request from Elasticsearch on a given query.

The default value is 10000, which is Elasticsearch's default limit (based on the Elasticsearch index.max_result_window index setting).

This property indicates whether or not to include pseudo columns as columns to the table.

Remarks

This setting is particularly helpful in Entity Framework, which does not allow you to set a value for a pseudo column unless it is a table column. The value of this connection setting is of the format "Table1=Column1, Table1=Column2, Table2=Column3". You can use the "*" character to include all tables and all columns; for example, "*=*".

This option allows you to pass exact queries to Elasticsearch.

Remarks

Setting this property to True enables the Sync App to pass an Elasticsearch query as-is to Elasticsearch. There are two options for submitting as-is queries to Elasticsearch: SQL and Search DSL.

SQL API

Elasticsearch version 6.3 and above supports a SQL API endpoint. When set to true, this option allows you to pass SQL queries directly to the Elasticsearch SQL API. Columns will be identified based on the metadata returned in the response.

Supported SQL syntax and commands can be found in the Elasticsearch documentation.

Note: SQL functionality is limited to what is supported by Elasticsearch.

Search DSL

Alternatively, queries can be submitted using Elasticsearch's Search DSL language, which includes Query DSL. This functionality is available in all versions of Elasticsearch.

The supported query syntax is JSON using the query passthrough syntax described below.

The JSON Passthrough Query Syntax supports the following elements:

The index, type, docid, and apiendpoint are used to generate the URL where the requestdata will be sent. The URL is generated using the following format: [Server]:[Port]/[index]/[type]/[docid]/[apiendpoint]. If any of the JSON passthrough elements are not specified, they will not be added to the URL.

Below is an example of a passthrough query. This example will retrieve the first 10 documents from megacorp.employee that contain a last_name of 'smith'. The results will be ordered by first_name in descending order.

{ 
  "index": "megacorp", 
  "type": "employee", 
  "requestdata": 
  {
    "from": 0,
    "size": 10,
    "query": {"bool":{"must":{"term":{"last_name":"smith"}}}},
    "sort": {"first_name":{"order":"desc"}}
  }
}

When using QueryPassthrough queries, the metadata is determined by the data returned in the response. RowScanDepth identifies the depth of the records that will be scanned to determine the metadata (columns and types). Since the metadata is based on the response data, passthrough queries may display different metadata than a similar query performed using the SQL syntax (where the metadata is retrieved directly from Elasticsearch).

The maximum number of rows to scan when generating table metadata. Set this property to gain more control over how the provider detects arrays.

Remarks

This property is used when generating table metadata and specifically is used to identify arrays within the data. Elasticsearch allows any field to be an array and does not identify which fields are arrays in the mapping data. Thus RowScanDepth rows will be queried and scanned to identify if any of the fields contain arrays.

When QueryPassthrough is set to True, the columns in a table must be determined by scanning the data returned in the request. This value determines the maximum number of rows that will be scanned to determine the table metadata. The default value is 100.

Setting a high value may decrease performance. Setting a low value may prevent the data type from being determined properly, especially when there is null data.

Specifies the time unit to use when retrieving results via the Scroll API.

Remarks

When a nonzero value is specified, the Scroll API will be used.

The time unit specified will be sent in each request made to Elasticsearch to specify how long the server should keep the search context alive. The value specified only needs to be long enough to process the previous batch of results (not to process all the data). This is because the ScrollDuration value will be sent in each request, which will extend the context time.

Once all the results have been retrieved, the search context will be cleared.

The format for this value is: [integer][time unit]. For example: 1m = 1 minute.

Setting this property to '0' will cause the default Search API to be used. In such a case, the maximum number of results that can be returned are equal to MaxResults.

Supported Time Units:

The value in seconds until the timeout error is thrown, canceling the operation.

Remarks

If Timeout = 0, operations do not time out. The operations run until they complete successfully or until they encounter an error condition.

If Timeout expires and the operation is not yet complete, the Sync App throws an exception.

Set this to true to set the generated table name as the complete source path when flattening nested documents using Relational DataModel .

Remarks

Set this to true to set the generated table name as the complete source path when flattening nested documents using Relational DataModel.

A filepath pointing to the JSON configuration file containing your custom views.

Remarks

User Defined Views are defined in a JSON-formatted configuration file called UserDefinedViews.json. The Sync App automatically detects the views specified in this file.

You can also have multiple view definitions and control them using the UserDefinedViews connection property. When you use this property, only the specified views are seen by the Sync App.

This User Defined View configuration file is formatted as follows:

• Each root element defines the name of a view.
• Each root element contains a child element, called query, which contains the custom SQL query for the view.

For example:

{
	"MyView": {
		"query": "SELECT * FROM [CData].[Elasticsearch].Employee WHERE MyColumn = 'value'"
	},
	"MyView2": {
		"query": "SELECT * FROM MyTable WHERE Id IN (1,2,3)"
	}
}

"UserDefinedViews", "C:\\Users\\yourusername\\Desktop\\tmp\\UserDefinedViews.json"