AMI SQL

From 3forge Documentation
Jump to navigation Jump to search

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:

AMISQL.DSAccessTable.jpg

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