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