RazorSQL DynamoDB SQL Support

RazorSQL provides the ability to connect to Amazon DynamoDB databases. It provides visual tools for creating and dropping tables, editing table data, and more. RazorSQL also allows users to use SQL syntax to execute SQL selects, inserts, updates, and deletes against DynamoDB databases and has specific syntax to allow the user to force a query or scan operation.

Listed below is information on the SQL syntax supported by RazorSQL with DynamoDB.

General Notes

The Amazon DynamoDB database does not natively support SQL. Any SQL statements executed in RazorSQL are translated into DynamoDB specific API calls by RazorSQL. RazorSQL does not support the full SQL standard for DynamoDB. Listed below are the select, select_query, select_scan, insert, update, and delete SQL syntax supported by RazorSQL.

Select

RazorSQL supports select statements against single tables when querying DynamoDB. Selecting all attributes via a select * statement or selecting specific attributes by adding the attribute names to the select clause are supported. Joins against multiple tables are not supported. RazorSQL also supports adding filter criteria to the where clause of SQL statements. See the examples below for more information.

select * from table_name select * from employee select attribute_name1, attribute_name2, . . . attribute_namen from table_name select first_name, last_name, address from employee select count(*) from table_name select count(*) from employee When filtering data via a where clause, RazorSQL supports the =, !=, <>, like, not like, and in operators for character data and =, !=, <>, >, >=, <, <=, and in operators for numeric data. The "is null" and "is not null" options are also supported. select * from table_name where string_attribute = 'value' select * from employee where first_name = 'John' select * from table_name where numeric_attribute < value select * from employee where salary < 40000 select * from table_name where string_attribute like 'value%' select * from table_name where string_attribute not like '%value%' select * from table_name where string_or_numeric_attribute in ('value1', 'value2') select * from table_name where string_or_numeric_attribute is not null When filtering data, only "and" clauses are supported. "Or" clauses are not supported. select * from employee where first_name = 'John' and salary < 40000

Select Query

RazorSQL includes a "select_query" syntax to force a query to use a query operation instead of relying on RazorSQL to determine whether to use a query or a scan operation. The select_query syntax can also be used to execute a query operation on a specific index if the index name is passed as a hint to the query. Here are examples of using the select_query syntax: select_query * from table_name where hash_attribute = value select_query * from CustomerOrders where CustomerId = 'alice@example.com' select_query * from table_name where hash_attribute = value and range_attribute > value select_query * from CustomerOrders where CustomerId = 'alice@example.com' and OrderId > 1 select_query /* IndexName */ * from table_name where hash_attribute = value and index_column < value select_query /* OrderCreationDateIndex */ * from CustomerOrders where CustomerId = 'bob@example.com' and OrderCreationDate < 20130131;

Select Scan

RazorSQL includes a "select_scan" syntax to force a query to use a scan operation instead of a query operation. Here is an example of the select_scan syntax: select_scan * from CustomerOrders where CustomerId = 'alice@example.com'

Scanning for Map Elements

RazorSQL supports for scanning for elements inside of maps using the map_column_name.map_attribute_name syntax. For example, if a DynamoDB table has a map column named measurement, and the map has elements named width and height, the following examples show how to scan for the map elements.

Note that if the map elements are character based, they should be wrapped in single quotes. If the map elements are not character based, they should not be wrapped in single quotes in the query. select * from map_table where measurement.width = 33; select * from map_table where measurement.width = '24-1/8th'; RazorSQL supports scanning for nested maps up to five levels deep. To scan for map elements within another map, use the following syntax. select * from test_nested_map where outermap.innermap.width = 10;

Scanning Using an AWS Command Line Type Syntax

RazorSQL supports scanning DynamoDB tables using a syntax similar to the AWS Command Line.

The format of the syntax is listed below. The projection expression is optional. If the projection expression is not included, all columns will be returned. There needs to be an expression-attribute-values= line for each value in the filter expression. Text values should be wrapped in single quotes. Numeric values should not be. scan table-name=TableName projection-expression=Column1,Column2 filter-expression=enter filter expression here expression-attribute-values=:value1,'Value 1 Text' expression-attribute-values=:value2,999 Here is an example scan that scans the table ProductCatalog returning rows where the Description column contains the :x value (Red) and the id column is greater than the :y value (10). This scan returns the columns Brand and id: scan table-name=ProductCatalog projection-expression=Brand,id filter-expression=contains(Description, :x) and id > :y expression-attribute-values=:x,'Red' expression-attribute-values=:y,10 Here is a similar scan, but the projection expression is not included so all columns are returned, and all rows with an id greater than 10 are returned: scan table-name=ProductCatalog filter-expression=id > :x expression-attribute-values=:x,10

Parallel Scans

RazorSQL supports DynamoDB parallel scans with a query hint inside of comments. The syntax for a parallel scan is the following:

select /*parallel:x:y*/ * from table where . . .

x is the number of threads to execute in parallel

y is the maximum number of results to return per thread

Below are two examples: select /*parallel:5:100*/ * from employee; select /*parallel:10:1000*/ name, id from employee where salary > 30000;

Insert

RazorSQL adds its own support for DynamoDB SQL insert statements. RazorSQL requires the insert statement to be in a specific syntax to be accepted. Listed below is the required syntax.

insert into table_name (attribute_name_list) values (value_list) INSERT INTO employee(first_name, last_name, salary) VALUES ('John', 'Smith', 40000)

To insert data into a set, separate each value in the set with the | character. For example:

INSERT INTO string_set_test1(id, sset) VALUES (1, 'one|two')

To insert data into a map or list column, the value must be in valid JSON format. Below is an example of inserting map data:

INSERT INTO map_table(id, measurement) VALUES (1, '{"width":54,"height":55}');

Update

RazorSQL adds its own support for DynamoDB SQL update statements. RazorSQL requires the update statement to be in a specific syntax to be accepted. All keys for the table must be specified in the update where clause. If the table only has a HASH key, that key must be in the where clause. If the table has both a HASH and a RANGE key, both keys must be in the where clause.

update table_name set attribute_name = value where hash_key_attribute_name = value [and range_key_attribute_name = value] update employee set first_name = 'John' where id = 44 update forum set status = 'Deleted' where forum_name = 'Sports' and forum_subject = 'Hockey'

When updating a map or list column, the data must be in valid JSON format. Below is an example of updating map data:

UPDATE map_table SET measurement = '{"width":54,"height":55}' WHERE id = 1;

Below is an example of updating list data:

UPDATE list_table SET alist = '[{"name":"chen","type":"office","enabled":true},{"name":"smith","type":"online","enabled":false}]' WHERE id = 1;

Delete

RazorSQL adds its own support for DynamoDB SQL delete statements. RazorSQL requires the delete statement to be in a specific syntax to be accepted. All keys for the table must be specified in the delete where clause. If the table only has a HASH key, that key must be in the where clause. If the table has both a HASH and a RANGE key, both keys must be in the where clause.

delete from table_name where hash_key_attribute_name = value [and range_key_attribute_name = value] delete from employee where id = 1

Create Table

RazorSQL supports creating tables in DynamoDB via a custom create table statement. The create table statement creates the hash / range keys and sets the values for the read and write capacity units. Below is an example:

create table test_table( id HASH Number, name RANGE String, read capacity units(1), write capacity units(1) );

Drop Table

RazorSQL supports dropping / deleting DynamoDB tables via the drop table statement. Below is an example:

drop table test_table;