Reference > AmiScript

AMI SQL: Datasource Access¶

This page covers the USE clause for accessing external datasources, query parameters, concurrent execution, AMIDB scope options, and escaping rules.

`USE...` Options¶

The USE key word allows users to specify which datasource the access clause should act on, as well as any other additional options the user can supply.

There are a number of default options which can be overridden for a particular query. The general format is: USE use_option=some_option. For example:

USE ds="AMI" 

See the table below for a list of the different USE options available.

Option	Description
`ds` or `datasource`	The name of the datasource to execute this script on
`timeout`	The number of milliseconds before the execute command times out
`limit`	The max number of records returned
`string_template`	Whether to use string templating Off by default; to switch on, pass `string_template="on"`
`ds_url`	Overrides the url of the datasource When used in conjunction with `ds="datasource_name",` the supplied datasource must contain URL in the PermittedOverrides field
`ds_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`	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 Use the more secure `ds_password_enc` option instead if supplying an encrypted password
`ds_password_enc`	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 Use instead of `ds_password`
`ds_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`	Override the Relay of the datasource Run the `SHOW RELAYS` command in the AMI shell tool to inspect RelayId for available Relays When used in conjunction with `ds="datasource_name"`, supplied datasource must contain RELAY in the PermittedOverrides field
`ds_adapter`	Use an "anonymous" datasource of the specified adapter type Run the `SHOW DATASOURCE_TYPES` command in the AMI shell tool and inspect the I column for available adapters Use as an alternative to the `ds` option, cannot be used in conjunction with one another See the `ami.db.anonymous.datasources.enabled` option for permission details
`_custom`	Some custom user-supplied directive Prefixing with an underbar (`_`) passes the option through to the datasource's adapter

`USE...EXECUTE`¶

This command enables you to "execute" code on an external datasource and process/store the results locally. This can be used to query datasources and must be formatted for the appropriate datasource.

The text passed into the EXECUTE command is sent directly to the datasource for execution. Therefore, variables cannot be referenced. Instead, substitution can be used (see String Templating).

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;

Query Parameters¶

For more complex queries, it is possible to declare parameters after the initial query using AmiScript ? notation.

The general format of using placeholder parameters uses the following syntax:

String query = "SQL_Query... ? ...";
[USE option = value] _p1=? ... EXECUTE ${query}

Parameters can either be passed individually in the order of being called:

_p1=param1 _p2=param2 ... _pn=paramn

String query = "SELECT * FROM `myTable` WHERE Name = ? || id = ? || Species = ? || Owner = ? || Age = ? group by ID";

String name = "Crouton";
int id = 1;
String species = "dog";
String owner = "Rob";
int age = 8;

CREATE TABLE City AS use ds="pets" _p01=name _p02=id _p03=species _p04=owner _p05 =age EXECUTE ${query};

or as a list of parameters:

_paramList

String query = "SELECT * FROM `myTable` WHERE Name = ? || id = ? || Species = ? || Owner = ? || Age = ? group by ID";

String name = "Crouton";
int id = 1;
String species = "dog";
String owner = "Rob";
int age = 8;
list pList = new list (name,id,species,owner,age);

CREATE TABLE City AS use ds="pets" _paramsList=pList EXECUTE ${query};

`USE...SHOW TABLES`¶

This command displays which tables are available on a given datasource. The resulting table output in the AMIDB Shell Tool will be called TABLE and contain two String columns: TableName and Schema.

Syntax¶

USE ds="AMI" 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`¶

This command uploads data from AMI into a datasource.

The resulting table output in the AMIDB Shell Tool 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 copies 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 copies 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{}` and `DISPATCH{}` Blocks (advanced)¶

Deferred Statements¶

3forge supports asynchronous execution using deferred statements. These are AmiScript statements that can be paused, or "deferred," allowing AMI to pause the current state of the coroutine until a response is received. This allows AMI to process other queued AmiScript blocks instead of hanging for improved performance.

The access clause USE...EXECUTE is an example of an AmiScript deferred statement. It sends an executable query to an external datasource and pauses the AmiScript context until a response is received. Any additional logic depending on that response is also deferred.

Users can take advantage of deferred statements to run multiple queries simultaneously within a block of AmiScript. Using the CONCURRENT{...} and DISPATCH{...} key words in a block will cause all EXECUTE clauses within that block to run in parallel (instead of sequentially). This is particularly useful for querying multiple datasources.

The examples below compare how EXECUTE queries perform on 3 external datasources (mydb1, mydb2, mydb3), depending on whether concurrency is used.

Examples¶

The examples below compare the sequence of events and syntax of sequential execution and concurrent execution.

For these examples, we assume each query takes 2 seconds to execute (this does not necessarily reflect actual performance).

Sequential Execution (Default)Concurrent Execution

In sequential execution, each query is processed one after the other. The AmiScript state of each block will pause after execution while it waits for a response.

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

{
  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;
}

Sequence of Events

00:00:00 - EXECUTE clause is sent to mydb1
00:00:00 - AmiScript block is paused 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 - AmiScript block is paused 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 - AmiScript block is paused 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)

For parallel or concurrent execution, each deferred statement (the EXECUTE query) is processed simultaneously.

Compared to the sequential (2+2+2), this example will complete in only 2 seconds:

Use the CONCURRENT keyword to mark a concurrent block, and then the keyword DISPATCH to coordinate each deferred statement.

CONCURRENT{ 

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

}

In this case, all 3 EXECUTE queries will be initiated at the same time.

Sequence of Events

00:00:00 - EXECUTE clause is sent to mydb1
00:00:00 - EXECUTE clause is sent to mydb2
00:00:00 - EXECUTE clause is sent to mydb3
00:00:00 - AmiScript task swaps until there is a response from all 3 queries...
00:00:02 - mytable1 is created (from result of query to mydb1)
00:00:02 - mytable2 is created (from result of query to mydb2)
00:00:02 - mytable3 is created (from result of query to mydb3)
00:00:02 - Block is complete after 2 seconds (code after the block will be executed)

AMIDB Scope Options¶

TEMPORARY

A table visible only to the user. It exists as long as the user session is active.

VARIABLE

A table created as an AMI Script Table object. It has the same scope and lifetime as a TEMPORARY table.

Table vt = SELECT * FROM Tablename;

PUBLIC

A table which is visible to all users. Its definition (but not the content) persists. For persistent content see PersistEngine options.

If the scope is not specified, AMI will first check TEMPORARY tables, then VARIABLE tables, and lastly PUBLIC tables. Tables with identical names can exist in different scopes. However, if a PUBLIC table already exists, then it cannot be created again.

You may look at the DefinedBy column to see if a column is a variable or temporary.

Display¶

SHOW [TEMPORARY|PUBLIC|VARIABLE] TABLES;

shows scoped tables

SHOW [TEMPORARY|PUBLIC|VARIABLE] COLUMNS;

shows columns of scoped tables

SHOW [TEMPORARY|PUBLIC|VARIABLE] TABLE TableName; 

shows column details of a scoped table

DESCRIBE [TEMPORARY|PUBLIC|VARIABLE] Tablename;

provides the command which can be used to create the scoped table

Schema Modification¶

CREATE¶

CREATE [TEMPORARY|PUBLIC] TABLE Tablename ...;

creates a scoped table

Examples

CREATE TABLE TempTable1 (a int);
CREATE TEMP TABLE TempTable2 (a int);
CREATE TEMPORARY TABLE TempTable3 (a int);
CREATE PUBLIC TABLE PubTable1 (x int);
CREATE PUBLIC TABLE PubTable2 (x int, y double);

ALTER¶

ALTER [TEMPORARY|PUBLIC|VARIABLE] TABLE Tablename ...;

modifies a scoped table

Examples

ALTER TEMPORARY TABLE TempTable1 ADD id String;
ALTER TEMPORARY TABLE TempTable2 MODIFY a as a BigInteger;
ALTER TEMPORARY TABLE TempTable3 RENAME a to b;
ALTER PUBLIC TABLE PubTable2 DROP y;

DROP¶

DROP [TEMPORARY|PUBLIC|VARIABLE] TABLE Tablename;

deletes a scoped table

Examples

DROP TEMPORARY TABLE TempTable3;

DROP PUBLIC TABLE PubTable2;

RENAME¶

RENAME  [TEMPORARY|PUBLIC|VARIABLE] TABLE Tablename TO NewTablename;

renames a scoped table

Examples

RENAME TEMPORARY TABLE TempTable2 TO TempTableTwo;
RENAME PUBLIC TABLE PubTable1 TO PubTable;

(All schema modification syntax options apply as in Schema and Data Modification)

Data Modification¶

INSERT¶

INSERT INTO [TEMPORARY|PUBLIC|VARIABLE] Tablename ...;

inserts row into scoped table

Examples

INSERT INTO TEMPORARY TempTable1 VALUES (1, "ID1"), (2, "ID2"), (3, "ID3");
INSERT INTO PUBLIC PubTable VALUES (4), (5);

PREPARE¶

PREPARE ... FROM  [TEMPORARY|PUBLIC|VARIABLE] ... Tablename ...;

prepares additional columns on scoped table

ANALYZE¶

ANALYZE ... FROM  [TEMPORARY|PUBLIC|VARIABLE] ... Tablename;

runs analysis on scoped table

Examples

ANALYZE COUNT(win.a) as Count FROM TEMPORARY TempTable1 WINDOW win ON true;
ANALYZE SUM(win.x) as Sum FROM PUBLIC PubTable WINDOW win ON true;

SYNC¶

SYNC [TEMPORARY|PUBLIC|VARIABLE] Tablename([comma_delimited_columns]) FROM ... ProjectingClause; 
SYNC [TEMPORARY|PUBLIC|VARIABLE] t FROM SELECT * FROM src;
SYNC [TEMPORARY|PUBLIC|VARIABLE] t ON(id) FROM SELECT * FROM src;
SYNC [TEMPORARY|PUBLIC|VARIABLE] t (col1, col2) ON(id) FROM SELECT col1, col2 FROM src;
SYNC [TEMPORARY|PUBLIC|VARIABLE] t (col1, col2) ON(id1, id2, id3) FROM SELECT col1, col2 FROM src;

syncs scoped table to ProjectingClause result

Rows in source not in target -> inserted
Rows in target not in source -> deleted
Rows present in both (matched via ON keys) -> updated

Note

ON (key_columns) specifies which columns identify a row for matching. Without ON, AMI cannot match rows and will fall back to full replace (equivalent to TRUNCATE + INSERT).

Example

SYNC TEMPORARY TempTableTwo(a) FROM select * from PubTable;

TRUNCATE¶

TRUNCATE[TEMPORARY|PUBLIC|VARIABLE][TABLE] Tablename;

deletes all rows from scoped table

(All data modification syntax options apply as in Schema and Data Modification)

Query Clauses¶

SELECT¶

SELECT ... FROM  [TEMPORARY|PUBLIC|VARIABLE] Tablename ...[AS Asname]  ...

queries scoped table

Examples

SELECT * FROM TEMPORARY TempTable1 WHERE id == "ID2";
SELECT * FROM PUBLIC PubTable;

UPDATE¶

UPDATE [TEMPORARY|PUBLIC|VARIABLE] Tablename [AS Asname] ...[JOIN_CLAUSE   [TEMPORARY|PUBLIC|VARIABLE] Tablename 2 [AS Asname2] ] ...

updates scoped table

Examples

UPDATE TEMPORARY TempTable1 set id = "NewID";
UPDATE PUBLIC PubTable set x = -1;

DELETE FROM¶

DELETE FROM [TEMPORARY|PUBLIC|VARIABLE] Tablename [AS Asname] [JOIN_CLAUSE  [TEMPORARY|PUBLIC|VARIABLE]  Tablename2 [AS Asname2] ] ...

deletes data from scoped table

Examples

DELETE FROM TEMPORARY TempTable1 WHERE a == 3;
DELETE FROM PUBLIC PubTable;

(All query clauses syntax options apply as in SQL Querying)

AMI-SQL Escaping Rules¶

If you want to use a reserve keyword as your table name, such as order,limit, you need to use backtick ` to escape it.

CREATE PUBLIC TABLE order(id int, sym string, price double,qty int);
INSERT INTO `order` VALUES(123,"aapl",20.5,100);
SELECT * FROM `order`;

AMI SQL: Datasource Access¶

USE... Options¶

USE...EXECUTE¶

Syntax¶

Example¶

Query Parameters¶

USE...SHOW TABLES¶

Syntax¶

Example¶

USE...INSERT¶

Syntax¶

Examples¶

CONCURRENT{} and DISPATCH{} Blocks (advanced)¶

Deferred Statements¶

Examples¶

AMIDB Scope Options¶

Display¶

Schema Modification¶

CREATE¶

ALTER¶

DROP¶

RENAME¶

Data Modification¶

INSERT¶

PREPARE¶

ANALYZE¶

SYNC¶

TRUNCATE¶

Query Clauses¶

SELECT¶

UPDATE¶

DELETE FROM¶

AMI-SQL Escaping Rules¶

`USE...` Options¶

`USE...EXECUTE`¶

`USE...SHOW TABLES`¶

`USE...INSERT`¶

`CONCURRENT{}` and `DISPATCH{}` Blocks (advanced)¶