Data > Datasource Adapters

Setting Up Datasource Adapters

AMI ships with several inbuilt datasource adapters for common data types, such as MS Excel, MySQL, etc. For a full list of supported softwares that AMI supports natively, see here.

Note

Not all the supported software is available by default in AMI. If you require a specific datasource adapter that we support, please contact us at support@3forge.com to receive the .jar files. Otherwise, follow the guide on creating a custom datasource adapter.

Overview

AMI datasource adapters provide a GUI interface in the web for attaching external databases to your layouts. Datasources can be added and accessed via the Data Modeler in the AMI dashboard.

For datasource adapters that aren't shipped by default with AMI, you will need to get them assigned to you via the 3forge client portal. These will be in the "files" section of the portal as .jar files.

To add a new datasource adapter, you will need to follow a few simple steps which are outlined below.

Setup

The general setup for adding new datasource adapters to your AMI is as follows:

1. Download the zip of the adapter.
2. Extract from the zip any .jar files you need for the adapter.
3. Add those files to your AMI library.
4. Configure your AMI properties to include the adapter.

Adding a Plugin

From the client portal, download either the zip folder or .jar files for the datasource adapter you need. Extract all the .jar files provided for your adapter.

Navigate to the root directory of your AMI installation and paste the .jar files in the amione/lib directory. This will enable the plugin's methods in AMI.

You will then need to configure your AMI properties to be able to access the plugin within AMI.

Properties

In local.properties, add the following line referencing the datasource adapter (this will be different for each adapter):

ami.datasource.plugins=$${ami.datasource.plugins},com.f1.ami.plugins.<ami.plugin.name>

For a custom datasource adapter, you will need to add instead the fully qualified class names of each Java class.

Note

$${ami.datasource.plugins} references the existing plugin list. Do not remove it, and ensure that any additional plugins are added after in a comma-delimited list with no spaces.

Upon restarting AMI, you will now see the new datasource option in the GUI.

Datasource Options

Datasources can be added via the GUI, but they can also be added programmatically via the AMIDB shell tool.

This is useful in cases where users want to supply parameters after launch, for example: supplying a password option in a datamodel.

Setup (AMIDB)

Adding datasources programmatically and passing additional parameters post-launch requires the following:

1. The center procedure __ADD_DATASOURCE (the full documentation can be found here), enabling the permittedOverrides option.

2. The following use ds_ options which can be called within fields that receive AmiScript:

   • ds_url
   • ds_username
   • ds_password
   • ds_password_enc
   • ds_options
   • ds_relay
   • ds_adapter

   These correspond to the permittedOverrides parameter.

Note

For a full description of each option and how it is used in a datamodel, see the table in this access clause documentation.

Example (AMIDB)

This example will guide you how to add a MySQL database via the AMIDB shell tool and pass the password as an additional parameter after launch.

Note

The example uses placeholders, it will not work if used directly. Please use your own password-hosted datasources in place.

1. Launch and log into AMI as normal. Open the AMIDB shell tool and input the following procedure:

   call __ADD_DATASOURCE("world", "MYSQL", "hostsite:port/world", "username", "", null, null, "PASSWORD");
   

   Note the following parameters in the procedure:

   • A "username" with access to the database
   • An empty string "" for the password field
   • The "PASSWORD" override allowing passwords to be supplied post-launch

2. Open the Data Modeler (Dashboard -> Data Modeler). The datasource "world" will now be visible:

   

   Right-click on the datasource and select "Add Datamodel." You will see a pop-up saying the connection has failed. This will be due to the missing password.

   Press the "Continue" button anyway and continue to the datamodel creation wizard.

3. In a datamodel, define a variable that will store the password object:

   {
       Password p = new Password("password");
   }
   

   Then in the same window, create a table:

   {
       CREATE TABLE world as USE ds_password=p EXECUTE SELECT * FROM Countries;
   }
   

   This will now return a table object populated with the SQL query.

Example (GUI)

This example will guide you how to add a MySQL database via the GUI and pass the password as an additional parameter after launch.

Note

The example uses placeholders, it will not work if used directly. Please use your own password-hosted datasources in place.

1. Launch and log into AMI as normal and go to add a datasource (Dashboard -> Data Modeler -> Attach Datasource)

   

2. Select "MySQL JDBC" from the adapter list and input the datasource details in the "Configuration" tab.

   

   Omit the "Password Field" and select the "Skip Test and force Add" checkbox. This ensures that the datasource is added even though the configuration is technically invalid.

3. Navigate to the "Security" tab and select the "Password" checkbox. This will enable you to override the password once the datasource has been added.

   

4. Click "Add Datasource" and accept the warnings. Now in a datamodel, define a variable that will store the password object:

   {
       Password p = new Password("password");
   }
   

   Then in the same window, create a table:

   {
       CREATE TABLE world as USE ds_password=p EXECUTE SELECT * FROM Countries;
   }
   

   This will now return a table object populated with the SQL query.

Supported Adapters

Some popular common adapters that we provide include:

1. Deephaven
2. Excel
3. Flat File
4. MongoDB
5. Redis
6. REST
7. Shell & SSH
8. Snowflake
9. Bloomberg data

If you require documentation for specific datasource adapter plugins not on this list, please reach out to us.

Custom Datasource Adapters

We provide the Java interface for writing your own datasource adapters in the event that you have a datasource not currently supported.

The instructions can be found here. Alternatively, reach out to us at support@3forge.com.