AMI SQL

Goals

The 3Forge SQL language provides a comprehensive instruction set for working with table(s) of data.

• Retrieval - Accessing and Normalizing data from external sources
• Blending - Perform Joins and Unions on tables
• Modifying - Inserting, Updating, and Deleting rows
• Analyzing - Grouping, Sorting, and Filtering
• Schema Definition - Creating, Updating, and Dropping tables

Broadly speaking, the logic steps involved in data-visualization are:

1. Running a query, or set of queries, on external datasources and/or the AMI-Realtime database.

• Each datasource is predefined and uniquely named.
• The results of queries on datasources will result in temporary, in-memory tables
• The queries can be constructed using user-input/user-selected/user-associated data
• In more advanced cases, the queries can be sequential, meaning the results from one query are then fed into a subsequent query (allowing for reactionary data blending)

2. Blending the results from Step 1, resulting in new temporary in-memory tables 3. Building visualizations on the tables from Step 1 and/or Step 2

AMI SQL and Standard SQL Differences

For those familiar with standard SQL, these are the differences:

1. Equality is done with ==

• AMISQL: SELECT * FROM Users WHERE userid == 15;
• MYSQL: SELECT * FROM Users WHERE userid = 15;

2. Checking for null is done with equality, ex. == null

• AMISQL: SELECT * FROM Users WHERE status == null;
• MYSQL: SELECT * FROM Users WHERE status IS NULL;

3. Strings are surrounded in double quotes

• AMISQL: SELECT * FROM Users WHERE name == "David";
• MYSQL: SELECT * FROM Users WHERE name = "David";

4. "Matches" (~~) instead of LIKE

• AMISQL: SELECT * FROM Users WHERE name ~~ "^Dav";
•  MYSQL: SELECT * FROM Users WHERE name LIKE "Dav%";

 (See Simplified Text Matching for pattern matching rules/expressions)

5. Boolean expressions && works same as AND.  || works same as OR

• AMISQL: SELECT * FROM Users WHERE userid == 15 AND status == null && name == "David";
• MYSQL: SELECT * FROM Users WHERE userid = 15 AND status IS NULL && name = "David";

6. By default all tables are TEMPORARY, use the PUBLIC key word to make non-temporary tables:

• AMISQL: CREATE TABLE MyTemporaryTable(id String);
• AMISQL: CREATE PUBLIC TABLE MyPermanantTable(id String);

Data Source Access Clauses

use...execute_clause

Overview

This command enables you to "execute" code on an external datasource and process/store the results locally

• USE: While datamodels have default options, you can override them for a particular query.  Valid options include
  • ds = "datasource_name"
    • The name of the datasource to execute this script on
  • timeout = timeout_in_millis
    • The number of milliseconds before the execute command times out
  • limit = row_limit_to_return
    • The max number of records returned
  • ds_url = "url"
    • Overrides the url of the datasource
    • When used in conjunction with ds="datasource_name", supplied datasource must contain URL in the PermittedOverrides field
  • ds_username = "username"
    • Overrides the username of the datasource
    • When used in conjunction with ds="datasource_name", supplied datasource must contain USERNAME in the PermittedOverrides field
  • ds_password = "plain_text_password"
    • Overrides the password of the datasource using a plain text password
    • When used in conjunction with ds="datasource_name", supplied datasource must contain PASSWORD in the PermittedOverrides field
    • Mutually exclusive with the ds_password_enc option
  • ds_password_enc = "aes_encrypted_password"
    • Overrides the password of the datasource, using an encrypted password
    • Use strEncrypt(...) to create an encrypted password
    • When used in conjunction with ds="datasource_name", supplied datasource must contain PASSWORD in the PermittedOverrides field
    • Mutually exclusive with the ds_password option
  • ds_options = "some options"
    • Override the options of the datasource
    • When used in conjunction with ds="datasource_name", supplied datasource must contain OPTIONS in the PermittedOverrides field
  • ds_relay = "relayid"
    • Override the relay of the datasource
    • Run SHOW RELAYS command and inspect RelayId for available relays
    • When used in conjunction with ds="datasource_name", supplied datasource must contain RELAY in the PermittedOverrides field
  • ds_adapter = "SOME_ADAPTER"
    • Use an "anonymous" datasource of the specified adapter type
    • Run SHOW DATASOURCE_TYPES command and inspect the I column for available adapters
    • Mutually exclusive with ds="datasource_name" option
    • See ami.db.anonymous.datasources.enabled option for permission details
  • _custom=some_directive
    • Prefixing with an underbar (_) passes the option through to the datasource's adapter
• EXECUTE: The literal text to execute. The text will be sent directly to the datasource for execution. Therefore, variables cannot be referenced. Instead, substitution can be used. See dollarsign_substitution

Syntax

[USE option = value  [  option = value  ...]]  EXECUTE code_to_execute

Example

CREATE TABLE

 final_table AS

USE

 ds="mydb"

 timeout=10000

 limit=100

EXECUTE

 select * from mytable;

use ... show tables clause

Overview

This command enables you to see what tables are available on a given datasource (similar to what the AMI gui wizard shows you when selecting a table).

• USE: While datamodels have default options, you can override them for a particular query.  Valid options include
  • ds = "datasource_name"             The name of the datasource to execute the script on

The resulting table will be called TABLE and contain two String columns: TableName and Schema

Syntax                                                                                                  

USE ds="mydb" SHOW TABLES;

Example

This example will return a table displaying all tables available in the mydb datasource. If the datasource supports schema names, that will be populated as well.

USE ds="mydb" SHOW TABLES;

//returns:

Overview

This command uploads data from AMI into a datasource.

• USE: While datamodels have default options, you can override them for a particular query.  Valid options include
  • ds = "datasource_name"             The name of the datasource to execute the script on

The resulting table will be called TABLE and contain two String columns: TableName and Schema

Syntax

USE ds="datasource_name"  INSERT [INTO] datasource_table  [(col1[, col2 ...])] select_clause
USE ds="datasource_name"  INSERT [INTO] datasource_table  [(col1[, col2 ...])] analyze_clause 
USE ds="datasource_name"  INSERT [INTO] datasource_table  [(col1[, col2 ...])] prepare_clause 
USE ds="datasource_name"  INSERT [INTO] datasource_table  [(col1[, col2 ...])] execute_clause
USE ds="datasource_name"  INSERT [INTO] datasource_table  [(col1[, col2 ...])] VALUES values_expr[, values_expr ...]
USE ds="datasource_name"  INSERT [INTO] datasource_table  [(col1[, col2 ...])] Use_execute_clause

datasource_name - string name of the datasource containing the table to insert values into

datasource_table - name of the table to insert values into