今すぐお試しください!

製品の詳細CData BizTalk Adapter for Amazon Athena を確認して、無償評価版をダウンロード:

今すぐダウンロード

Tutorial: Use the CData BizTalk Adapter for Athena to Execute Updategrams

Create a simple application in which you use the adapter for Athena to execute an updategram and then write the response from Athena to a file.

The CData BizTalk Adapter for Athena enables you to execute SQL commands, updategrams, and stored procedures on Athena data in BizTalk. This article provides a walk-through of an application that uses an updategram to insert Athena data.

  1. A one-way file receive location picks up the XML document that contains the updategram.
  2. The document is routed to a solicit-response send port configured for the adapter.
  3. The adapter executes the command. The results of an insert contain the Id of the new record.
  4. The response from Athena is routed to a one-way file send port, which writes the BizTalk message to a file.

Create the Sample Application

The following sections walk through creating the sample application:

  1. Generate the Schema and an Updategram Instance
  2. Configure a Receive Port
  3. Configure a File Receive Location
  4. Configure the Adapter in a Solicit-Response Send Port
  5. Configure a File Send Port
  6. Create Filters to Route BizTalk Messages Through the Application
  7. Enlist and Enable the Locations and Ports
  8. View Application Logs

Generate the Schema and an Updategram Instance

You can use wizards in Visual Studio to generate the schema and updategram instance:

  1. Add theAdapter to Your Project
  2. Generate a Schema to Insert into the Customers table
  3. Generate an Updategram Instance

Add the Adapter to Your Project

Use the Add Adapter wizard to add the adapter to your project. You will use the adapter to query Athena for metadata about the table you want to insert to; for example, "Customers".

  1. Right-click on the project in the Solution Explorer and click Add -> Add Generated Items.
  2. Select Add Adapter Metadata in the resulting dialog box.
  3. In the resulting Add Adapter Wizard, select the CData BizTalk Adapter for Athena from the list view.
  4. In the Port menu, leave the selection blank. Or, if you want to use the connection string for an adapter you have already configured, select the send port or receive location from your BizTalk application.

Generate a Schema to Insert into the Customers Table

You will use the schema as a template for creating an updategram that will insert Athena data. The schema is an .xsd file that defines the XML request and response. As an example, an insert updategram is generated. You can follow the same process to create update and delete updategrams.

  1. In the Connection String page of the Schema Wizard, enter authentication credentials and other connection properties, if you have not already configured them in your BizTalk application.
  2. Select the Send Port option.
  3. Disable the One-Way option. The One-Way option ignores any response from the adapter for the updategram, such as the AffectedRows value that is returned. The Id of the newly created row is also returned. By disabling the One-Way option you will get an updategram schema that supports retrieving the Id.
  4. In the Command Type menu, select Updategram.
  5. Select the Insert option and select the table and columns you want to insert into. This tutorial uses Name and TotalDue.

    Note: When you create the updategram, you are limited to selecting the columns you include in the schema. Id is required for updates and deletes. It is not required for inserts.

  6. On the summary page, verify the settings you defined and click Finish to close the wizard.

Generate an Updategram Instance

In this step, you will create the XML message that will in later steps be sent to the adapter. To create the updategram, right-click the .xsd file in the Solution Explorer and then click Generate Instance. The location where the updategram is saved is listed in the Output tab.

The updategram is structured to define what response is desired from Athena. For example updategrams and more information, see Create Updategrams with the CData BizTalk Adapter for Athena.

Configure the Receive Port

To add a receive location to your application, you first need to add a receive port. Receive ports can receive data from multiple receive locations.

  1. If you have not already done so, open your application in the BizTalk Server Administration Console.
  2. In your application, right-click Receive Ports and click New -> One-Way Receive Port. The Receive Port Properties dialog is displayed.
  3. In the Name menu, enter a name for the Receive Port.

Configure the File Receive Location

The Static One-Way File Receive Location will enable you to use the updategram in BizTalk. The File Receive Location creates a BizTalk message containing the updategram.

  1. Right-click Receive Locations and click New -> One-Way Receive Location.
  2. Select the Receive Port of which the new Receive Location will be a member. The Receive Location Properties dialog is displayed.
  3. In the Name menu, enter a name for the Receive Location.
  4. In the Receive Location properties, select FILE in the Transport Type menu.
  5. Click Configure. The File Transport Properties dialog is displayed.
  6. In the Receive Folder box, enter the path to a folder; for example, DemoOutbound. The Receive Location will scan this folder for files that match the criteria in the File mask box.
  7. In the Receive pipeline menu, select the default option, PassThruReceive.

Configure the Athena Adapter in a solicit-response send port

Configuration consists of the following steps:

  1. Create and Configure the Solicit-Response Send Port
  2. Configure the Adapter
  3. Configure the Connection to Athena

Create and Configure the Solicit-Response Send Port

Use a solicit-response send port to obtain the results of an updategram command; in this tutorial, the Id of the newly inserted record is returned in BizTalk.

  1. If you have not already done so, open your BizTalk application in the BizTalk Administration Console.
  2. In the node for your BizTalk application, right-click Send Ports and select New -> Static solicit-response send port. The Send Port Properties dialog is displayed.
  3. In the send port properties, enter a name for the send port.
  4. In the Transport Type menu, select CData.AmazonAthena.

Configure the CData Athena Adapter

Define the command the adapter will execute in the Adapter Transport Properties dialog.

  1. In the send port properties, click the Configure button. The Adapter Transport Properties dialog is displayed.
  2. In the CommandType property, select "Updategram or Stored Procedure".
Refer to the "Adapter Configuration" section in the help documentation for a description of the various adapter properties and their functions.

Configure the Connection to Athena

Configure credentials and other properties required to connect to Athena in the Connection String Options dialog.

  1. If you have not already done so, open the Adapter Transport properties dialog: In the send port properties, click Configure.
  2. Click the button in the Connection String property. The Connection String Options dialog is displayed.
  3. Enter connection properties. Below is a typical connection string:

    AccessKey='a123';SecretKey='s123';Region='IRELAND';Database='sampledb';S3StagingDirectory='s3://bucket/staging/';

    Amazon Athena への接続

    Amazon Athena リクエストの認証には、アカウントの管理のクレデンシャルか、IAM ユーザーのカスタムPermission を設定します。 AccessKey にAccess Key Id、SecretKey にはSecret Access Key を設定します。

    Note: AWS アカウントアドミニストレータとしてアクセスできる場合でも、AWS サービスへの接続にはIAM ユーザークレデンシャルを使用することが推奨されます。

    Access Key の取得

    IAM ユーザーのクレデンシャル取得は以下の通り:

    1. IAM コンソールにログイン
    2. Navigation ペインでUsers を選択。
    3. To create or manage the access keys for a user, select the user and then select 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.

    Authenticating from an EC2 Instance

    If you are using the CData Data Provider for Amazon Athena 2018 from an EC2 Instance and have an IAM Role assigned to the instance, you can use the IAM Role to authenticate. To do so, set UseEC2Roles to true and leave AccessKey and SecretKey empty. The CData Data Provider for Amazon Athena 2018 will automatically obtain your IAM Role credentials and authenticate with them.

    Authenticating as an AWS Role

    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. An AWS role may be used instead by specifying the RoleARN. This will cause the CData Data Provider for Amazon Athena 2018 to attempt to retrieve credentials for the specified role. If you are connecting to AWS (instead of already being connected such as on an EC2 instance), you must additionally specify the AccessKey and SecretKey of an IAM user to assume the role for. Roles may not be used when specifying the AccessKey and SecretKey of an AWS root user.

    Authenticating with MFA

    For users and roles that require Multi-factor Authentication, specify the MFASerialNumber and MFAToken connection properties. This will cause the CData Data Provider for Amazon Athena 2018 to submit the MFA credentials in a request to retrieve temporary authentication credentials. Note that the duration of the temporary credentials may be controlled via the TemporaryTokenDuration (default 3600 seconds).

    Connecting to Amazon Athena

    In addition to the AccessKey and SecretKey properties, specify Database, S3StagingDirectory and Region. Set Region to the region where your Amazon Athena data is hosted. Set S3StagingDirectory to a folder in S3 where you would like to store the results of queries.

    If Database is not set in the connection, the data provider connects to the default database set in Amazon Athena.

  4. Click Test Connection to verify the values.
Refer to the "Connection String Options" chapter in the help documentation for a description of the various connection properties and their functions.

Configure a File Send Port

The file send port will write to a file the Id that is returned from the insert. Follow the steps below to configure a one-way send port to write incoming messages to a file.

  1. Right-click Send Ports and then click Static one-way send port.
  2. In the Type menu, select FILE.
  3. Click Configure.
  4. In the Destination folder box, enter the path to the folder where the message will be written. For example, DemoInbound.

Create Filters to Route BizTalk Messages Through the Application

Follow the steps below to create filter rules that will define the following associations in your application:

  • Associate the solicit-response send port with the BizTalk message that contains the updategram.
  • Associate the file send port with the BizTalk message created by the results of executing the updategram.

  1. In the solicit-response send port properties, click Filters and set the following values:

    • Property: Select BTS.InboundTransportLocation from the menu.
    • Value: Enter the URI of the one-way receive location, "C:\DemoOutbound\*.xml" in this example.
  2. In the one-way send port properties, click Filters and set the following values:

    • Property: Select BTS.SPName from the menu.
    • Value: Enter the name (not URI) of the solicit-response send port.

Enlist and Enable the Locations and Ports

To ensure that the updategram is picked up by the receive location, follow the steps below when starting your BizTalk application:

  1. Right-click the solicit-response Send Port and click Start. This will also enlist the Send Port.
  2. Right-click the one-way send port and click Start. This will also enlist the Send Port.
  3. Right-click the Receive Location and click Enable.

You can now copy the updategram into the folder that you configured for the Receive Location.

Open the folder that you configured for the one-way send port: This folder contains the new Id in an XML file.The message will resemble the following example:

<table xmlns="http://www.CData.com/AmazonAthenaProvider" AffectedRows="1"><row><Id>0014000001XERBUAA5</Id></row> </table>

View the Application Logs

In the Application Logs, you can view error messages and quickly check that your application has initialized successfully. To open the logs, expand the Event Viewer (Local) in the navigation tree in the Administration Console. Expand Windows Logs and select Application.

A list of errors that have occurred will be available here. This will include error messages for all applications on the system, so it is important to check the source of the error message. For the receive location, the source should be "CData BizTalk AmazonAthena Receive Adapter". Details of the error message should provide insight into why the error is occurring.

Contact support@cdata.com/jp for guidance on resolving the error.

 
 
ダウンロード