This page provides a guide to Establishing a Connection to Elasticsearch in CData Cloud, as well as information on the available resources, and a reference to the available connection properties.

Connecting to Elasticsearch

Establishing a Connection shows how to authenticate to Elasticsearch and configure any necessary connection properties to create a database in CData Cloud

Accessing Data from CData Cloud Services

Accessing data from Elasticsearch through the available standard services and CData Cloud administration is documented in further details in the CData Cloud Documentation.

Connect to Elasticsearch by selecting the corresponding icon in the Database tab. Required properties are listed under Settings. 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=01.02.03.04 
    OR 
  Server=01.01.01.01:1234,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 Cloud 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 Cloud will attempt to automatically identify the region based on the Server value).

The Cloud 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

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 navigate to the Security Credentials tab.

1. Sign into the AWS Management console with the credentials for your root account.
2. Select your account name or number.
3. In the menu that displays, select My Security Credentials.
4. To manage or create root account access keys, click Continue to Security Credentials and expand the "Access Keys" section.

Standard Authentication

Set the AuthScheme to Basic, and set User and Password properties and/or use PKI (public key infrastructure) to authenticate. Once the Cloud 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 Cloud, set UseSSL to true;.

Root Credentials

To authenticate using account root credentials, set these parameters:

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

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

If multi-factor authentication is required, specify the following:

• CredentialsLocation: The location of the settings file where MFA credentials are saved. See the Credentials File Location page under Connection String Options for more information.
• MFASerialNumber: The serial number of the MFA device if one is being used.
• MFAToken: The temporary token available from your MFA device.

Note: If you want to control the duration of the temporary credentials, set the TemporaryTokenDuration property (default: 3600 seconds).

Temporary Credentials

To authenticate using temporary credentials, specify the following:

• AuthScheme: TemporaryCredentials.
• AWSAccessKey: The access key of the IAM user who will assume the role.
• AWSSecretKey: The secret key of the IAM user who will assume the role.
• AWSSessionToken: Your AWS session token, provided with your temporary credentials. For details, see AWS Identity and Access Management User Guide.

The Cloud 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.

To authenticate using both temporary credentials and an IAM role, set all the parameters described above, and specify these additional parameters:

• AWSRoleARN: The Role ARN for the role you'd like to authenticate with. This prompts the Cloud to retrieve credentials for the specified role.
• AWSExternalId (optional): Only required if you are assuming a role in another AWS account.

If multi-factor authentication is required, specify the following:

• CredentialsLocation: The location of the settings file where MFA credentials are saved. See the Credentials File Location page under Connection String Options for more information.
• MFASerialNumber: The serial number of the MFA device if one is being used.
• MFAToken: The temporary token available from your MFA device.

Note: If you want to control the duration of the temporary credentials, set the TemporaryTokenDuration property (default: 3600 seconds).

Using AWS From an EC2 Instance

Set AuthScheme to AwsEC2Roles.

If you are using the Cloud from an EC2 Instance and have an IAM Role assigned to the instance, you can use the IAM Role to authenticate. Since the Cloud automatically obtains your IAM Role credentials and authenticates with them, it is not necessary to specify AWSAccessKey and AWSSecretKey.

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 Cloud to attempt to retrieve credentials for the specified role.
• AWSExternalId (optional): Only required if you are assuming a role in another AWS account.

IMDSv2 Support

The Elasticsearch Cloud now supports IMDSv2. Unlike IMDSv1, the new version requires an authentication token. Endpoints and response are the same in both versions.

In IMDSv2, the Elasticsearch Cloud first attempts to retrieve the IMDSv2 metadata token and then uses it to call AWS metadata endpoints. If it is unable to retrieve the token, the Cloud reverts to IMDSv1.

Note that this method of authentication is only possible with Opensearch Service, and not with Elasticsearch.

AWS IAM Roles

To authenticate as an AWS role, set these properties:

• 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.
• AWSRoleARN: Specify the Role ARN for the role you'd like to authenticate with. This will cause the Cloud to attempt to retrieve credentials for the specified role.
• AWSExternalId (optional): Only required if you are assuming a role in another AWS account.

If multi-factor authentication is required, specify the following:

• CredentialsLocation: The location of the settings file where MFA credentials are saved. See the Credentials File Location page under Connection String Options for more information.
• MFASerialNumber: The serial number of the MFA device if one is being used.
• MFAToken: The temporary token available from your MFA device.

Note: If you want to control the duration of the temporary credentials, set the TemporaryTokenDuration property (default: 3600 seconds).

Note: In some circumstances it might be preferable to use an IAM role for authentication, rather than the direct security credentials of an AWS root user. If you are specifying the AWSAccessKey and AWSSecretKey of an AWS root user, you cannot use roles.

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.

Fine Tuning Data Access

You can use the following properties to gain greater control over Elasticsearch API features and the strategies the Cloud 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 Cloud 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 Cloud offers to bridge the gap with relational SQL and an Elasticsearch database.

The Cloud 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 Cloud 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 Cloud 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 Cloud 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 Cloud 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 Cloud 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 Cloud 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 Cloud offers three basic configurations to model documents as tables, described in the following sections. The Cloud 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 Cloud 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 Cloud 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 Cloud 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 Cloud 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 Cloud 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 Cloud can return JSON structures as column values. The Cloud 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 Cloud 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 Cloud 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 Cloud. 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 Cloud. 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.

Customizing the SSL Configuration

By default, the Cloud attempts to negotiate TLS with the server. The server certificate is validated against the default system trusted certificate store. You can override how the certificate gets validated using the SSLServerCert connection property.

To specify another certificate, see the SSLServerCert connection property.

Client SSL Certificates

The Elasticsearch Cloud 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 authenticate to an HTTP proxy, set the following:

• ProxyServer: the hostname or IP address of the proxy server that you want to route HTTP traffic through.
• ProxyPort: the TCP port that the proxy server is running on.
• ProxyAuthScheme: the authentication method the Cloud uses when authenticating to the proxy server.
• ProxyUser: the username of a user account registered with the proxy server.
• ProxyPassword: the password associated with the ProxyUser.

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 Cloud 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 Cloud).

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. Given how many indices and documents the _all view could cover, certain queries agains the '_all' view could be very expensive. Additionally, for scanning for table metadata, as governed by RowScanDepth, will be less accurate for '_all' views that cover very large or very heterogenous indices. See Automatic Schema Discovery for more information about this.

Stored Procedures

The Cloud models the data in Elasticsearch as a list of tables in a relational database that can be queried using standard SQL statements.

CData Cloud - Elasticsearch Tables

General information about index templates

Columns

Views are similar to tables in the way that data is represented; however, views are read-only.

Queries can be executed against a view as if it were a normal table.

CData Cloud - Elasticsearch Views

General information about index settings

Columns

General information about the installed X-Pack features

Columns

Stored procedures are function-like interfaces that extend the functionality of the Cloud beyond simple SELECT/INSERT/UPDATE/DELETE operations with Elasticsearch.

Stored procedures accept a list of parameters, perform their intended function, and then return any relevant response data from Elasticsearch, along with an indication of whether the procedure succeeded or failed.

CData Cloud - Elasticsearch Stored Procedures

Submits a request to create an index with specified settings.

EXECUTE Example:

EXECUTE CreateIndex Index = 'firstindex', Alias = 'search', NumberOfShards = '3' 

Input

Result Set Columns

Submits an alias request to modify index aliases.

EXECUTE Example:

EXECUTE ModifyIndexAliases Action = 'add;add', Index = 'index_1;index_2', Alias = 'my_alias;my_alias' 

Note: The Index parameter supports the asterisk (*) character to perform a pattern match to add all matching indices to the alias.

Input

Result Set Columns

Procedure for updating index settings. Note that some settings may only be updated on a closed index.

See this documentation for information on index settings in Elasticsearch, and what they control and impact across indices and clusters. Make sure to reference documentation for the version of Elasticsearch being connected to.

EXECUTE Example:

EXECUTE UpdateIndexSettings IndexName='traffic', NumberOfReplicas='3'

Input

Result Set Columns

You can query the system tables described in this section to access schema information, information on data source functionality, and batch operation statistics.

Schema Tables

The following tables return database metadata for Elasticsearch:

• sys_catalogs: Lists the available databases.
• sys_schemas: Lists the available schemas.
• sys_tables: Lists the available tables and views.
• sys_tablecolumns: Describes the columns of the available tables and views.
• sys_procedures: Describes the available stored procedures.
• sys_procedureparameters: Describes stored procedure parameters.
• sys_keycolumns: Describes the primary and foreign keys.
• sys_indexes: Describes the available indexes.

Data Source Tables

The following tables return information about how to connect to and query the data source:

• sys_connection_props: Returns information on the available connection properties.
• sys_sqlinfo: Describes the SELECT queries that the Cloud can offload to the data source.

Query Information Tables

The following table returns query statistics for data modification queries, including batch operations::

• sys_identity: Returns information about batch operations or single updates.

Lists the available databases.

The following query retrieves all databases determined by the connection string:

SELECT * FROM sys_catalogs

Columns

Lists the available schemas.

The following query retrieves all available schemas:

          SELECT * FROM sys_schemas
          

Columns

Lists the available tables.

The following query retrieves the available tables and views:

          SELECT * FROM sys_tables
          

Columns

Describes the columns of the available tables and views.

The following query returns the columns and data types for the [CData].[Elasticsearch].Employee table:

SELECT ColumnName, DataTypeName FROM sys_tablecolumns WHERE TableName='Employee' AND CatalogName='CData' AND SchemaName='Elasticsearch'

Columns

Lists the available stored procedures.

The following query retrieves the available stored procedures:

          SELECT * FROM sys_procedures
          

Columns

Describes stored procedure parameters.

The following query returns information about all of the input parameters for the CreateTable stored procedure:

SELECT * FROM sys_procedureparameters WHERE ProcedureName = 'CreateTable' AND Direction = 1 OR Direction = 2

To include result set columns in addition to the parameters, set the IncludeResultColumns pseudo column to True:

SELECT * FROM sys_procedureparameters WHERE ProcedureName = 'CreateTable' AND IncludeResultColumns='True'

Columns

Pseudo-Columns

Describes the primary and foreign keys.

The following query retrieves the primary key for the [CData].[Elasticsearch].Employee table:

         SELECT * FROM sys_keycolumns WHERE IsKey='True' AND TableName='Employee' AND CatalogName='CData' AND SchemaName='Elasticsearch'
          

Columns

Describes the foreign keys.

The following query retrieves all foreign keys which refer to other tables:

         SELECT * FROM sys_foreignkeys WHERE ForeignKeyType = 'FOREIGNKEY_TYPE_IMPORT'
          

Columns

Describes the primary keys.

The following query retrieves the primary keys from all tables and views:

         SELECT * FROM sys_primarykeys
          

Columns

Describes the available indexes. By filtering on indexes, you can write more selective queries with faster query response times.

The following query retrieves all indexes that are not primary keys:

          SELECT * FROM sys_indexes WHERE IsPrimary='false'
          

Columns

Returns information on the available connection properties and those set in the connection string.

The following query retrieves all connection properties that have been set in the connection string or set through a default value:

SELECT * FROM sys_connection_props WHERE Value <> ''

Columns

Describes the SELECT query processing that the Cloud can offload to the data source.

See SQL Compliance for SQL syntax details.

Discovering the Data Source's SELECT Capabilities

Below is an example data set of SQL capabilities. Some aspects of SELECT functionality are returned in a comma-separated list if supported; otherwise, the column contains NO.

The following query retrieves the operators that can be used in the WHERE clause:

SELECT * FROM sys_sqlinfo WHERE Name = 'SUPPORTED_OPERATORS'

Columns

Returns information about attempted modifications.

The following query retrieves the Ids of the modified rows in a batch operation:

         SELECT * FROM sys_identity
          

Columns

Describes the available system information.

The following query retrieves all columns:

SELECT * FROM sys_information

Columns

Data Type Mappings

The Cloud 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

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, AwsEC2Roles, APIKey, and TemporaryCredentials. None is the default.

Possible Values

Data Type

string

Default Value

"Basic"

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 Cloud 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.
• AwsEC2Roles: Set to use an IAM Role assigned to an EC2 instance for the connection.
• APIKey: Set to use APIKey and APIKeyId for the connection.
• TemporaryCredentials: Set this to leverage temporary security credentials alongside a session token to connect.

The user who is authenticating to Elasticsearch.

Data Type

string

Default Value

""

Remarks

The user who is authenticating to Elasticsearch.

The password used to authenticate to Elasticsearch.

Data Type

string

Default Value

""

Remarks

The password used to authenticate to Elasticsearch.

This property sets whether the provider attempts to negotiate TLS/SSL connections to the server.

Data Type

bool

Default Value

true

Remarks

This property sets whether the Cloud attempts to negotiate TLS/SSL connections to the server.

When set to false, (the default), for compatibility with the previous method of specifying the protocol prefix in the Server property, the Cloud category respects the protocol behavior set in Server, and then uses the protocol dictated by UseSSL=False. NOTE: This means that if you set UseSSL=False, but also specify Server="https://localhost", the Cloud attempts to connect and communicate over HTTPS, despite UseSSL being set to False.

When UseSSL is set to true, the Cloud attempts to strictly follow the property's specification, and it throws an exception if there is a conflict with the specification in Server. For example, if you set UseSSL=true, but specify Server as "http://localhost", the Cloud generates an exception.

Differences between the new and the old method:

In the new method, Server should now just specify server name, domain name, IP address, or similar. For the previous method of specifying Server as a combination of protocol prefix and hostname, like "http://localhost", this now maps to Server being set to "localhost", and UseSSL to false;. What was formerly set to Server="https://localhost" now maps to Server="localhost";UseSSL=true;.

New users of the driver are encouraged to not specify a protocol in Server.

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.

Data Type

string

Default Value

""

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, UseSSL to true; 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 '01.01.01.01:1234,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.

Data Type

string

Default Value

"9200"

Remarks

The port the Elasticsearch REST server is bound to.

The APIKey used to authenticate to Elasticsearch.

Data Type

string

Default Value

""

Remarks

The APIKey used to authenticate to Elasticsearch.

The APIKey Id to authenticate to Elasticsearch.

Data Type

string

Default Value

""

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.

Possible Values

Data Type

string

Default Value

"Document"

Remarks

Select a DataModel configuration to configure how the Cloud 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 Cloud 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 Cloud.

If false, indices whose name starts with a '.' (dot indices) will not be exposed as tables or views by the provider. If true, dot indices will be exposed as tables or views.

Data Type

bool

Default Value

false

Remarks

In most standard scenarios with newer versions of Elasticsearch, dot indices are system indices or hidden indices. These are indices that usually should not be directly interacted with by users, or whose indexed documents will not usually be returned in the results of queries that search over sets of multiple indices. As such, the Cloud does not expose dot indices by default in its table or view metadata.

A comma-separated list of alias names or filters that define the aliases exposed as views.

Data Type

string

Default Value

""

Remarks

The alias names provided should match existing aliases in Elasticsearch. Filters can use parts of alias names and the wildcard character *.

For example, the following value matches the aliases "qa," "sprint_testing," and "sprint_metrics":

qa,sprint_*

A comma-separated list of index and data stream names or filters.

Data Type

string

Default Value

""

Remarks

Depending on the version of Elasticsearch connected to, this filter limits the indices and data streams exposed as tables or schemas. See Schema Mapping for more details.

The values provided should match existing index or data stream names in Elasticsearch. Filters for indices or data streams can include parts of their names and the wildcard character *.

This filter applies only to open, non-hidden indices and data streams.

For example, the following value matches the data streams "my_logs_0" and "my_logs_1" and the index "sources":

sources,my_logs_*

When this property is set to true, AWSLakeFormation service will be used to retrieve temporary credentials, which enforce access policies against the user based on the configured IAM role. The service can be used when authenticating through OKTA, ADFS, AzureAD, PingFederate, while providing a SAML assertion.

Data Type

bool

Default Value

false

Remarks

When this property is set to true, AWSLakeFormation service will be used to retrieve temporary credentials, which enforce access policies against the user based on the configured IAM role. The service can be used when authenticating through OKTA, ADFS, AzureAD, PingFederate, while providing a SAML assertion.

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

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

Data Type

string

Default Value

""

Remarks

To find your AWS account access key:

1. Sign into the AWS Management console with the credentials for your root account.
2. Select your account name or number.
3. Select My Security Credentials in the menu.
4. Click Continue to Security Credentials.
5. To view or manage root account access keys, expand the Access Keys section.

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

Data Type

string

Default Value

""

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.

Data Type

string

Default Value

""

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 Cloud 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.

Possible Values

Data Type

string

Default Value

"NORTHERNVIRGINIA"

Remarks

The hosting region for your Amazon Web Services. Available values are OHIO, NORTHERNVIRGINIA, NORTHERNCALIFORNIA, OREGON, CAPETOWN, HONGKONG, TAIPEI, HYDERABAD, JAKARTA, MALAYSIA, MELBOURNE, MUMBAI, OSAKA, SEOUL, SINGAPORE, SYDNEY, THAILAND, TOKYO, CENTRAL, CALGARY, BEIJING, NINGXIA, FRANKFURT, IRELAND, LONDON, MILAN, PARIS, SPAIN, STOCKHOLM, ZURICH, TELAVIV, MEXICOCENTRAL, BAHRAIN, UAE, SAOPAULO, GOVCLOUDEAST, GOVCLOUDWEST, ISOLATEDUSEAST, ISOLATEDUSEASTB, ISOLATEDUSEASTF, ISOLATEDUSSOUTHF, ISOLATEDUSWEST and ISOLATEDEUWEST.

Your AWS session token.

Data Type

string

Default Value

""

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.

Data Type

string

Default Value

"3600"

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 Cloud 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.

Data Type

string

Default Value

""

Remarks

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

The OAuth 2.0 access token or OpenID Connect ID token that is provided by an identity provider.

Data Type

string

Default Value

""

Remarks

The OAuth 2.0 access token or OpenID Connect ID token that is provided by an identity provider. An application can get this token by authenticating a user with a web identity provider. If not specified, the value for this connection property is automatically obtained from the value of the 'AWS_WEB_IDENTITY_TOKEN_FILE' environment variable.

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

Identifies the Kerberos Key Distribution Center (KDC) service used to authenticate the user. (SPNEGO or Windows authentication only).

Data Type

string

Default Value

""

Remarks

The Kerberos properties are used when using SPNEGO or Windows Authentication. The Cloud requests session tickets and temporary session keys from the Kerberos KDC service, which is usually co-located with the domain controller.

If KerberosKDC is not specified, the Cloud tries 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 Cloud obtains the KDC from the specified file. If it is not found there, the Cloud tries 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 cannot be inferred from another location, the Cloud infers them from the configured domain name and host.

Identifies the Kerberos Realm used to authenticate the user.

Data Type

string

Default Value

""

Remarks

A realm is a logical network, similar to a domain, that defines a group of systems under the same master KDC. Some realms are hierarchical, where one realm is a superset of the other realm, but usually realms are nonhierarchical (or “direct”) and the mapping between the two realms must be defined. Kerberos cross-realm authentication enables authentication across realms. Each realm only needs to have a principal entry for the other realm in its KDC.

The Kerberos properties are used when using SPNEGO or Windows Authentication. The Cloud requests session tickets and temporary session keys from the Kerberos KDC service, which is usually co-located with the domain controller. The Kerberos Realm can be configured by an administrator to be any string, but it is usually based on the domain name.

If Kerberos Realm is not specified, the Cloud 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 Cloud 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 Cloud will infer them from the user-configured domain name and host. This might work in some Windows environments.

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

Data Type

string

Default Value

""

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 to the KDC's URL.

Confirms the principal name for the Kerberos Domain Controller, which uses the format host/user@realm.

Data Type

string

Default Value

""

Remarks

If there is a Kerberos principal, that Kerberos principal name should always be used to authenticate to the database.

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

Data Type

string

Default Value

""

Remarks

A keytab (short for “key table”) stores long-term keys for one or more principals. In most cases, end users authenticate to the KDC using their client secret (password). However, in situations where authentication or re-authentication happen using automated scripts and applications, it may be more efficient to use a keytab, which sends passwords to the KDC in encrypted form, automatically.

Keytabs are normally represented by files in a standard format, and named using the format type:value. Usually type is FILE and value is the absolute pathname of the file. The other possible value for type is MEMORY, which indicates a temporary keytab stored in the memory of the current process.

A keytab contains one or more entries, where each entry consists of a timestamp (indicating when the entry was written to the keytab), a principal name, a key version number, an encryption type, and the encryption key itself. They can be generated using kutil.

For example:

[admin@myhost]# ktutil

ktutil: addent -password -p starlord/[email protected] -k 1 -e aes256-cts-hmac-sha1-96
Password for starlord/myhost.galaxy.com:

ktutil: addent -password -p starlord/[email protected] -k 1 -e aes128-cts-hmac-sha1-96
Password for starlord/myhost.galaxy.com:

ktutil: addent -password -p starlord/[email protected] -k 1 -e des3-cbc-sha1
Password for starlord/myhost.galaxy.com:

ktutil: wkt /path/to/starlord.keytab

Note: You must create principals for all authentication methods (encryption types) you want to support.

To display a keytab, use klist -k.

Identifies the service's Kerberos realm. (Cross-realm authentication only).

Data Type

string

Default Value

""

Remarks

The KerberosServiceRealm is used to specify a service's KerberosRealm when using cross-realm Kerberos authentication.

In most cases, a single realm and KDC machine are used to perform the Kerberos authentication, which means that this property would not be required. However, the 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).

Identifies the service's Kerberos Key Distribution Center (KDC).

Data Type

string

Default Value

""

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, which means that this property would not be required. However, the 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).

Specifies the full file path to an MIT Kerberos credential cache file.

Data Type

string

Default Value

""

Remarks

Set this property if you want 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.

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

Data Type

string

Default Value

""

Remarks

If you are using a TLS/SSL connection, use this property to specify the TLS/SSL certificate to be accepted from the server. If you specify a value for this property, all other certificates that are not trusted by the machine are rejected.

This property can take the following forms:

Note: It is possible to use '*' to signify that all certificates should be accepted, but due to security concerns this is not recommended.

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

Specifies the verbosity level of the log file, which controls the amount of detail logged. Supported values range from 1 to 5.

Data Type

string

Default Value

"1"

Remarks

This property defines the level of detail the Cloud includes in the log file. Higher verbosity levels increase the detail of the logged information, but may also result in larger log files and slower performance due to the additional data being captured.

The default verbosity level is 1, which is recommended for regular operation. Higher verbosity levels are primarily intended for debugging purposes. For more information on each level, refer to Logging.

When combined with the LogModules property, Verbosity can refine logging to specific categories of information.

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

Optional setting that restricts the schemas reported to a subset of all available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC .

Data Type

string

Default Value

""

Remarks

Listing all available database schemas can take extra time, thus degrading performance. Providing a list of schemas in the connection string saves time and improves performance.

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

Data Type

bool

Default Value

true

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.

Data Type

string

Default Value

""

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.

Data Type

bool

Default Value

false

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.

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

Data Type

string

Default Value

"10000"

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.

Specifies the maximum number of rows returned for queries that do not include either aggregation or GROUP BY.

Data Type

int

Default Value

-1

Remarks

The default value for this property, -1, means that no row limit is enforced unless the query explicitly includes a LIMIT clause. (When a query includes a LIMIT clause, the value specified in the query takes precedence over the MaxRows setting.)

Setting MaxRows to a whole number greater than 0 ensures that queries do not return excessively large result sets by default.

This property is useful for optimizing performance and preventing excessive resource consumption when executing queries that could otherwise return very large datasets.

The number of results to return per request from Elasticsearch.

Data Type

string

Default Value

"10000"

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).

Specifies whether to use PIT with search_after or scrolls to page through query results.

Possible Values

Data Type

string

Default Value

"Scroll"

Remarks

PIT with search_after can only be used with Elasticsearch 7.10+ or OpenSearch 2.4.0+.

Specifies the time unit to use for keep alive when retrieving results via PIT API.

Data Type

string

Default Value

"1m"

Remarks

When a nonzero value is specified alongside setting PaginationMode to 'PIT', the PIT 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 PIT 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 PITDuration 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 and ScrollDuration 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:

Specifies the pseudocolumns to expose as table columns, expressed as a string in the format 'TableName=ColumnName;TableName=ColumnName'.

Data Type

string

Default Value

""

Remarks

This property allows you to define which pseudocolumns the Cloud exposes as table columns.

To specify individual pseudocolumns, use the following format:

Table1=Column1;Table1=Column2;Table2=Column3

To include all pseudocolumns for all tables use:

*=*

Specifies whether to replace invalid UTF8 byte sequences found in reads of indexed document content with the U+FFFD replacement character.

Data Type

bool

Default Value

false

Remarks

Specifies whether to replace invalid UTF8 byte sequences found in reads of indexed document content with the U+FFFD replacement character.

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

Data Type

string

Default Value

"100"

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 or when the scanned documents are very heterogenous.

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

Data Type

string

Default Value

"1m"

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 Scroll 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 and PITDuration 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:

Specifies the maximum time, in seconds, that the provider waits for a server response before throwing a timeout error.

Data Type

int

Default Value

60

Remarks

The timeout applies to each individual communication with the server rather than the entire query or operation. For example, a query could continue running beyond 60 seconds if each paging call completes within the timeout limit.

Timeout is set to 60 seconds by default. To disable timeouts, set this property to 0.

Disabling the timeout allows operations to run indefinitely until they succeed or fail due to other conditions such as server-side timeouts, network interruptions, or resource limits on the server.

Note: Use this property cautiously to avoid long-running operations that could degrade performance or result in unresponsive behavior.

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

Data Type

bool

Default Value

false

Remarks

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

LZMA from 7Zip LZMA SDK

LZMA SDK is placed in the public domain.

Anyone is free to copy, modify, publish, use, compile, sell, or distribute the original LZMA SDK code, either in source code form or as a compiled binary, for any purpose, commercial or non-commercial, and by any means.

LZMA2 from XZ SDK

Version 1.9 and older are in the public domain.

Xamarin.Forms

Xamarin SDK

The MIT License (MIT)

Copyright (c) .NET Foundation Contributors

All rights reserved.

Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.

NSIS 3.10

Copyright (C) 1999-2025 Contributors THE ACCOMPANYING PROGRAM IS PROVIDED UNDER THE TERMS OF THIS COMMON PUBLIC LICENSE ("AGREEMENT"). ANY USE, REPRODUCTION OR DISTRIBUTION OF THE PROGRAM CONSTITUTES RECIPIENT'S ACCEPTANCE OF THIS AGREEMENT.

1. DEFINITIONS

"Contribution" means:

a) in the case of the initial Contributor, the initial code and documentation distributed under this Agreement, and b) in the case of each subsequent Contributor:

i) changes to the Program, and

ii) additions to the Program;

where such changes and/or additions to the Program originate from and are distributed by that particular Contributor. A Contribution 'originates' from a Contributor if it was added to the Program by such Contributor itself or anyone acting on such Contributor's behalf. Contributions do not include additions to the Program which: (i) are separate modules of software distributed in conjunction with the Program under their own license agreement, and (ii) are not derivative works of the Program.

"Contributor" means any person or entity that distributes the Program.

"Licensed Patents " mean patent claims licensable by a Contributor which are necessarily infringed by the use or sale of its Contribution alone or when combined with the Program.

"Program" means the Contributions distributed in accordance with this Agreement.

"Recipient" means anyone who receives the Program under this Agreement, including all Contributors.

2. GRANT OF RIGHTS

a) Subject to the terms of this Agreement, each Contributor hereby grants Recipient a non-exclusive, worldwide, royalty-free copyright license to reproduce, prepare derivative works of, publicly display, publicly perform, distribute and sublicense the Contribution of such Contributor, if any, and such derivative works, in source code and object code form.

b) Subject to the terms of this Agreement, each Contributor hereby grants Recipient a non-exclusive, worldwide, royalty-free patent license under Licensed Patents to make, use, sell, offer to sell, import and otherwise transfer the Contribution of such Contributor, if any, in source code and object code form. This patent license shall apply to the combination of the Contribution and the Program if, at the time the Contribution is added by the Contributor, such addition of the Contribution causes such combination to be covered by the Licensed Patents. The patent license shall not apply to any other combinations which include the Contribution. No hardware per se is licensed hereunder.

c) Recipient understands that although each Contributor grants the licenses to its Contributions set forth herein, no assurances are provided by any Contributor that the Program does not infringe the patent or other intellectual property rights of any other entity. Each Contributor disclaims any liability to Recipient for claims brought by any other entity based on infringement of intellectual property rights or otherwise. As a condition to exercising the rights and licenses granted hereunder, each Recipient hereby assumes sole responsibility to secure any other intellectual property rights needed, if any. For example, if a third party patent license is required to allow Recipient to distribute the Program, it is Recipient's responsibility to acquire that license before distributing the Program.

d) Each Contributor represents that to its knowledge it has sufficient copyright rights in its Contribution, if any, to grant the copyright license set forth in this Agreement.

3. REQUIREMENTS

A Contributor may choose to distribute the Program in object code form under its own license agreement, provided that:

a) it complies with the terms and conditions of this Agreement; and

b) its license agreement:

i) effectively disclaims on behalf of all Contributors all warranties and conditions, express and implied, including warranties or conditions of title and non-infringement, and implied warranties or conditions of merchantability and fitness for a particular purpose;

ii) effectively excludes on behalf of all Contributors all liability for damages, including direct, indirect, special, incidental and consequential damages, such as lost profits;

iii) states that any provisions which differ from this Agreement are offered by that Contributor alone and not by any other party; and

iv) states that source code for the Program is available from such Contributor, and informs licensees how to obtain it in a reasonable manner on or through a medium customarily used for software exchange.

When the Program is made available in source code form:

a) it must be made available under this Agreement; and

b) a copy of this Agreement must be included with each copy of the Program.

Contributors may not remove or alter any copyright notices contained within the Program.

Each Contributor must identify itself as the originator of its Contribution, if any, in a manner that reasonably allows subsequent Recipients to identify the originator of the Contribution.

4. COMMERCIAL DISTRIBUTION

Commercial distributors of software may accept certain responsibilities with respect to end users, business partners and the like. While this license is intended to facilitate the commercial use of the Program, the Contributor who includes the Program in a commercial product offering should do so in a manner which does not create potential liability for other Contributors. Therefore, if a Contributor includes the Program in a commercial product offering, such Contributor ("Commercial Contributor") hereby agrees to defend and indemnify every other Contributor ("Indemnified Contributor") against any losses, damages and costs (collectively "Losses") arising from claims, lawsuits and other legal actions brought by a third party against the Indemnified Contributor to the extent caused by the acts or omissions of such Commercial Contributor in connection with its distribution of the Program in a commercial product offering. The obligations in this section do not apply to any claims or Losses relating to any actual or alleged intellectual property infringement. In order to qualify, an Indemnified Contributor must: a) promptly notify the Commercial Contributor in writing of such claim, and b) allow the Commercial Contributor to control, and cooperate with the Commercial Contributor in, the defense and any related settlement negotiations. The Indemnified Contributor may participate in any such claim at its own expense.

For example, a Contributor might include the Program in a commercial product offering, Product X. That Contributor is then a Commercial Contributor. If that Commercial Contributor then makes performance claims, or offers warranties related to Product X, those performance claims and warranties are such Commercial Contributor's responsibility alone. Under this section, the Commercial Contributor would have to defend claims against the other Contributors related to those performance claims and warranties, and if a court requires any other Contributor to pay any damages as a result, the Commercial Contributor must pay those damages.

5. NO WARRANTY

EXCEPT AS EXPRESSLY SET FORTH IN THIS AGREEMENT, THE PROGRAM IS PROVIDED ON AN "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, EITHER EXPRESS OR IMPLIED INCLUDING, WITHOUT LIMITATION, ANY WARRANTIES OR CONDITIONS OF TITLE, NON-INFRINGEMENT, MERCHANTABILITY OR FITNESS FOR A PARTICULAR PURPOSE. Each Recipient is solely responsible for determining the appropriateness of using and distributing the Program and assumes all risks associated with its exercise of rights under this Agreement, including but not limited to the risks and costs of program errors, compliance with applicable laws, damage to or loss of data, programs or equipment, and unavailability or interruption of operations.

6. DISCLAIMER OF LIABILITY

EXCEPT AS EXPRESSLY SET FORTH IN THIS AGREEMENT, NEITHER RECIPIENT NOR ANY CONTRIBUTORS SHALL HAVE ANY LIABILITY FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING WITHOUT LIMITATION LOST PROFITS), HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OR DISTRIBUTION OF THE PROGRAM OR THE EXERCISE OF ANY RIGHTS GRANTED HEREUNDER, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGES.

7. GENERAL

If any provision of this Agreement is invalid or unenforceable under applicable law, it shall not affect the validity or enforceability of the remainder of the terms of this Agreement, and without further action by the parties hereto, such provision shall be reformed to the minimum extent necessary to make such provision valid and enforceable.

If Recipient institutes patent litigation against a Contributor with respect to a patent applicable to software (including a cross-claim or counterclaim in a lawsuit), then any patent licenses granted by that Contributor to such Recipient under this Agreement shall terminate as of the date such litigation is filed. In addition, if Recipient institutes patent litigation against any entity (including a cross-claim or counterclaim in a lawsuit) alleging that the Program itself (excluding combinations of the Program with other software or hardware) infringes such Recipient's patent(s), then such Recipient's rights granted under Section 2(b) shall terminate as of the date such litigation is filed.

All Recipient's rights under this Agreement shall terminate if it fails to comply with any of the material terms or conditions of this Agreement and does not cure such failure in a reasonable period of time after becoming aware of such noncompliance. If all Recipient's rights under this Agreement terminate, Recipient agrees to cease use and distribution of the Program as soon as reasonably practicable. However, Recipient's obligations under this Agreement and any licenses granted by Recipient relating to the Program shall continue and survive.

Everyone is permitted to copy and distribute copies of this Agreement, but in order to avoid inconsistency the Agreement is copyrighted and may only be modified in the following manner. The Agreement Steward reserves the right to publish new versions (including revisions) of this Agreement from time to time. No one other than the Agreement Steward has the right to modify this Agreement. IBM is the initial Agreement Steward. IBM may assign the responsibility to serve as the Agreement Steward to a suitable separate entity. Each new version of the Agreement will be given a distinguishing version number. The Program (including Contributions) may always be distributed subject to the version of the Agreement under which it was received. In addition, after a new version of the Agreement is published, Contributor may elect to distribute the Program (including its Contributions) under the new version. Except as expressly stated in Sections 2(a) and 2(b) above, Recipient receives no rights or licenses to the intellectual property of any Contributor under this Agreement, whether expressly, by implication, estoppel or otherwise. All rights in the Program not expressly granted under this Agreement are reserved.

This Agreement is governed by the laws of the State of New York and the intellectual property laws of the United States of America. No party to this Agreement will bring a legal action under this Agreement more than one year after the cause of action arose. Each party waives its rights to a jury trial in any resulting litigation.