Read Document by SQL

Operation Name

Read Document by SQL

Function Overview

Get documents stored in Microsoft Azure Cosmos DB collection using arbitrary SQL statement.

Data Model

Table model type.

Properties

For details on use of variables, refer to "Variables".
Basic settings
Item name Required/Optional Use of variables Description Remarks
Name Required Not available Enter the name to show on the script canvas.  
Required settings
Item name Required/Optional Use of variables Description Remarks
Destination Required Not available Select a global resource.
  • [Add]:
    Add a new global resource.
  • [Edit list]:
    Global resource settings can be edited in “Edit resource list” screen.
  
Collection Required Available Select or enter a collection name to be read.  
SQL statement Required Available Enter the SQL statement to get documents.
  • Default value is “SELECT TOP 100 * FROM c”. With this SQL statement, gets documents with 100 as upper limit, without limiting items.
  • For more details on SQL in Azure Cosmos DB, refer to the following pages.
    • “SQL queries for Azure Cosmos DB DocumentDB API” -(https://docs.microsoft.com/en-us/azure/cosmos-db/documentdb-sql-query)
    • “Azure Cosmos DB DocumentDB API: SQL syntax reference” -(https://msdn.microsoft.com/library/azure/dn782250.aspx)
Schema definition Required - Specify item list for the collection specified in [Collection].
Added rows will be the target items.

Each target item can be operated with the following buttons.
  • [Add]:
    Add a target item.
  • [Up]:
    Move the selected item upwards by one row.
  • [Down]:
    Move the selected item downwards by one row.
  • [Delete]:
    Delete the item.
  
Schema definition/Item name Required Available Enter an item name of the collection specified in [Collection].
  • Specified item name will be displayed in Mapper schema.
Schema definition/Type Required Not available Select the type of [Item name].
Property actions
Item name Description Remarks
Update collection list Get collections from the specified destination and set them in [Collection].  
Test execution Execute the specified SQL statement.
  • Disabled when [Destination], [Collection], or [SQL statement] is not specified.
Execute SQL statement and set schema Execute the SQL statement for the specified collection, get the type information from the extracted results, and set it in [Schema definition].
  • Execute the SQL specified in [SQL statement] for the collection specified in [Collection], gets 100 documents, and then extract the item names and types.
    • One or more documents should be registered in collection.
  • If item type information is not determined or if the type is not supported, the item type will be blank.
  • Disabled when [Destination], [Collection], or [SQL statement] is not specified.
Data processing method
Item name Required/Optional Use of variables Description Remarks
Data processing method Required Not available Select the data processing method.
  • [Apply script settings]: (default)
    Apply the mass data processing settings of the script property to the adapter.
  • [Do not perform mass data processing]:
    Mass data processing will not be performed.
  • [Perform mass data processing]:
    Mass data processing will be performed.
  
Comment
Item name Required/Optional Use of variables Description Remarks
Comment Optional Not available You can write a short description of this adapter.
The description will be reflected in the specifications.		  

Schema

Input schema

None.

Output schema

The number of columns differs depending on the [Attribute list] settings.
For more details, refer to “Schema of table model type”.

Loading schema in Mapper

Schema is loaded automatically.
For more details, refer to “Edit Schema”.

Mass Data Processing

Mass Data Processing is supported.

PSP Usage

PSP is supported.
For more details, refer to “Parallel Stream Processing”.

Available Component Variables

Component variable name Description Remarks
count The number of got documents is stored.
  • Default value is null.
  • Values are not stored in PSP.
message_category When an error occurs, category of the corresponding message code is stored.
  • Default value is null.
message_code When an error occurs, code of the corresponding message code is stored.
  • Default value is null.
message_level When an error occurs, severity of the corresponding message code is stored.
  • Default value is null.
error_type When an error occurs, the error type is stored.
  • Default value is null.
  • The error type would be in the following format.
    Example: “java.io.FileNotFoundException”
  • The contents that will be stored may vary depending on DataSpider Servista versions.
error_message When an error occurs, the error message is stored.
  • Default value is null.
  • The contents that will be stored may vary depending on DataSpider Servista versions.
error_trace When an error occurs, trace information of the error is stored.
  • Default value is null.
  • The contents that will be stored may vary depending on DataSpider Servista versions and client applications.

Specification Limits

For details on specification limits common to the operations, refer to ”Microsoft Azure Cosmos DB Adapter Limitations”.

Major Exceptions

Exception name Cause Solution
ResourceNotFoundException
Resource definition is not found. Name: []		 [Destination] is not specified. Specify [Destination].
ResourceNotFoundException
Resource definition is not found. Name: [<global resource name>]		 The resource definition specified in [Destination] is not found. Check the global resource specified in [Destination].
InvalidPropertyConfigurationException
<property name> is not specified.		 [<property name>] is not specified. Specify [<property name>].
InvalidPropertyConfigurationException
Item name of schema is duplicate.		 [<Schema definition/Item name>] is duplicate. Specify item names of [<Schema definition/Item name>] so as not to duplicate them.
InvalidPropertyConfigurationException
Set the type of id item of schema to String.		 The type of id item of [<Schema definition/Item name>] is other than String. Set the type of id item of [<Schema definition/Item name>] to String.
com.microsoft.azure.documentdb.DocumentClientException The cause differs according to each error message. Check the contents of the error message.
DatabaseNotFoundException
Database [<database>] is not found.		 The database specified in [Destination] is not found. Check [Database] of the global resource specified in [Destination].
CollectionNotFoundException
Collection [<collection>] is not found.		 The collection specified in [Collection] is not found. Check the collection specified in [Collection].

Notes