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