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:

use...insert clause

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

Examples

This example will insert two rows into the mydb datasource's users table:

USE ds="mydb" INSERT INTO users(name,city,state) values ("Rob","NYC","NY"),("Sara","OC","CA");

This example copy all rows from AMI's my_users table into the "mydb" datasource's users table:

USE ds="mydb" INSERT INTO users(name,city,state) SELECT Name,Local,Region FROM my_users;

This example copy all rows from the old_users table in the otherds datasource to the users table in the mydb datasource.

USE ds="mydb" INSERT INTO users(name,city,state) USE ds="otherds" EXECUTE SELECT * FROM old_users

concurrent_block (advanced)

Overview

When running analysis that needs to query many datasources, there can be large performance gains by executing these queries simultaneously. The CONCURRENT{...} key word causes all EXECUTE clauses within the block to be run in parallel (instead of sequentially).

In both of the examples below, we are going to execute three queries on three separate datasources (mydb1, mydb2, mydb3). The first example executes the queries sequentially and the second example executes the queries concurrently.

Assuming each query takes 2 seconds for a database to process, the first example will complete in about 6 seconds (2+2+2), while the second, concurrent, example will complete in only 2 seconds:

Example of Sequential Execution (Default)

{
  CREATE TABLE mytable1 AS USE ds="mydb1" EXECUTE select * from mytable;
  CREATE TABLE mytable2 AS USE ds="mydb2" EXECUTE select * from mytable;
  CREATE TABLE mytable3 AS USE ds="mydb3" EXECUTE select * from mytable;
}

Here is the sequence of events:

00:00:00 -  EXECUTE clause is sent to mydb1

00:00:00 -  AMI Script sleeps until there is a response from mydb1...

00:00:02 - mytable1 is created (from result of query to mydb1)

00:00:02 -  EXECUTE clause is sent to mydb2

00:00:02 -  AMI Script sleeps until there is a response from mydb2...

00:00:04 - mytable2 is created (from result of query to mydb2)

00:00:04 -  EXECUTE clause is sent to mydb3

00:00:04 -  AMI Script sleeps until there is a response from mydb3...

00:00:06 - mytable3 is created (from result of query to mydb3)

00:00:06 -  Block is complete after 6 seconds (code after the block will be executed)