DMAP API
DMAP API
o Overview
o End points
o Quickstart
¦ Example
o Schema and Table
¦ Table Aliases
o Selecting Fields
All fields
¦ Field Aliases
¦ Field Casts
¦ Valid Cast Types
¦ Aggregations
¦ Available aggregation methods
¦ Distinct
¦ Functions
¦ Standard Functions
¦ Sum Function
¦ Selecting from a Function
¦ Case Statements
o Filtering
¦ Available Filter Operators
¦ Casting fields in filters
¦ Valid Field Cast Types
¦ Using functions in a filter
o Dates
o Joins
¦ Join Operator Types
¦ Functions in Joins
o Subqueries
o Pagination
o Ordering Results
¦ Ordering Joined Columns
o Grouping Results
o Variables
¦ Example Query with limit, offset, and order by variables
¦ Query
¦ Variables
¦ Example Query with where clause variables
¦ Query
¦ Variables
o Multiple queries
o Returned data
-------
o Export formats
o Development Environment
o SSL Certs - AWS Dev Configuration
o Deployment
o Run Tests
Overview
The DMAP API is a GraphQL-like API that will allow users to query all the tables in the Envirofacts
database. The backend is written in Lambda functions using Chalice. It is not a true GraphQL
implementation as it lacks the ability to use fragments, perform introspection, have a schema file,
and a host of other functionality.
Endpoints
The production endpoint is located at https://data.epa.aov/dmapservice/querv. The internal
endpoint is located at https://internal.aws-prod.aws.epa.gov/api/querv.
Quickstart
The basic structure of a query consists of a schema, table, and fields. All examples below are for
tables and schemas which do not currently exist. They are simplified examples to clearly indicate
functionality.
Example
query sampleQuery {
users contact_list {
id
name
address
}
}
This query will select the fields id, name, and address from the contactjist table in the users schema.
It will return all the records up to the current limit of 10,000 records.
Schema and Table
A schema and table must be supplied when querying data. The format is schema**table. For
example, when to query the contactjist table in the users schema, the format would
be users**contact_list.
Table Aliases
A table can be renamed in the resulting data set with the use of the alias function.
query sampleQuery {
users contact list (alias: contacts) {
-------
id
name
address
}
}
In the example above, the contact//sf data will be under the "contacts" element In the resulting data
set. The original schema and table name are not referenced In the results. _The table alias Is not used
when exporting results In the graphql format.
Selecting Fields
At least one field must be supplied (if not querying an aggregate column, see below) in a query.
Fields are specified as a nested list within the table element There is no delimiter between fields. In
the query:
query sampleQuery {
users contact_list {
id
name
address
}
}
The 3 fields id, name, and address will be returned in the result set.
All fields
If you want to return all the fields in a table, use the special field all columns . You do not
need to include any other columns in the table.
query sampleQuery {
users contact_list {
all_columns
}
}
Field Aliases
To rename a column's name for the purposes of a query, use the alias function with the value you
want the column name's output to be in the result set.
query sampleQuery {
users contact_list {
id
name
address (alias: "street_address")
}
-------
}
In the example above, the address column will be returned as street_address in the JSON object
array.
Field Casts
To cast a selected field to a different type when it is returned, add the cast attribute to the field in the
query
query sampleQuery {
users contact_list {
id
name
age (cast: "text")
}
}
In this example, the age field will be returned as a text type instead of an integer.
Valid Cast Types
• date
• decimal
• float
• integer
• number
• text
Aggregations
To perform an aggregation method on a table (e.g. count, min, max), the aggregate table needs to
be queried. This aggregate table can have any filter and join applied to it. As an example of querying
the count on the contactjist table, the table queried would
be users contact_list aggregate. Then instead of listing the fields, query the count object
within the aggregate.
query sampleQuery {
users contact_list aggregate {
aggregate {
count
}
}
}
Available aggregation methods
• count
o Purpose: Returns the total count of records.
-------
o Format: count
max
o Purpose: Get the maximum value of a column,
o Format: max column_name
min
o Purpose: Get the mininum value of a column,
o Format: min column_name
Distinct
To add a distinct clause in the query, add an argument to the primary table that
specifies distinct: true.
query sampleQuery {
users contact_list (distinct: true) {
id
name
address (alias: "street_address")
}
}
Functions
To select a function in the results, add a parameters parameter that contains an array of the data
to pass to the function.
query sampleQuery {
users contact_list {
id
name
address (alias: "street_address")
users name_concat (parameters: ["John", "Smith"])
}
}
Standard Functions
To use a standard function in the query, add f n before the function name. Currently only coalesce,
coalesceText and count are supported. Pass any parameters in the parameters argument.
fn_add will add the values of any columns and numbers passed in as parameters. fn__coalesce will
return the value in the database field supplied as the second parameter if the database field in the
first parameter has a NULL value. fn_coalesceText will return the text supplied as the second
parameter if the database field in the first parameter has a NULL value. fn_count will return the
count of the database field supplied in the first parameter. fn_dateadd will add or subtract the
interval provided to the date supplied in the first parameter. The second parameter is the date part
to be added or subtracted, alid values for precision are: microseconds, milliseconds, second, minute,
hour, day, week, month, quarter, year, decade, century, millennium. The third parameter is the
-------
interval to be applied. fn_datetrunc will truncate a date database field supplied in the first
parameter. The second parameter is the precision of the date field to be retrieved. Valid values for
precision are: microseconds, milliseconds, second, minute, hour, day, week, month, quarter, year,
decade, century, millennium. The third parameter is optional and is the time zone (e.g.
America/New_York). fn_divide will divide the values of any columns and numbers passed in as
parameters. fn__extract (fn__datepart is an alias) will return the specified part from a date database
field supplied in the first parameter. The second parameter is the part of the date field to be
retrieved. Valid values for the date part are: microseconds, milliseconds, second, minute, hour, day,
dow (day of week), doy (day of year), week, month, quarter, year, decade, century, millennium.
fn_lpad will return a padded version of the database field supplied in the first parameter. The
second parameter is the length of the total number of characters to be returned. The third parameter
is the character used to pad the database field. fn_ltrim will return a trimmed version of left
beginning of the database field supplied in the first parameter. The trimming will occur on the
leading and trailing characters. The second parameter is optional (it defaults to a blank space 1 ') and
is the characters that are to be removed. fn_max is an aggregate function and will return the highest
value based on the criteria supplied for the database field supplied in the first parameter. fn_min is
an aggregate function and will return the lowest value based on the criteria supplied for the
database field supplied in the first parameter. fn_multiply will multiply the values of any columns
and numbers passed in as parameters. fn_replace rill return a modified version fo the database field
supplied in the first parameter. The second parameter is the pattern to search for in the field's data.
The third parameter is optional (it defaults to an empty string) and is the characters that will replace
the pattern. fn_rtrim will return a trimmed version of right ending of the database field supplied in
the first parameter. The trimming will occur on the leading and trailing characters. The second
parameter is optional (it defaults to a blank space 1 ') and is the characters that are to be removed.
fn_subtract will subtract the values of any columns and numbers passed in as parameters. fn_sum
will return the sum of the values of the chosen column. It needs to be used in conjunction with a
group by clause. fn_trim will return a trimmed version of the database field supplied in the first
parameter. The trimming will occur on the leading and trailing characters. The second parameter is
optional (it defaults to a blank space 1 ') and is the characters that are to be removed.
query sampleQuery {
users contact_list {
id
name
address (alias: "street_address")
fn coalesce (parameters: ["name", "address"], alias:
"coalesce_resuit")
}
}
Sum Function
The fn_sum function returns the sum of the values of the chosen column. As it is an aggregation
function, if there are any other columns being selected, they need to be in a groupBy clause.needs to
be used in conjunction with the groupBy clause.
query sampleQuery {
-------
employees financials_list (groupBy: ["city", state"]) {
city
state
fn sum (parameters: ["salary"], alias: "sum_salaries")
}
}
This query will return the sum of salaries with it being grouped by employee cities and states.
Selecting from a Function
To select results from a function, add a parameters parameter to the function and query the
function as if it was a table.
query sampleQuery {
users combine_user_orders (parameters: ["123ABC456"]) {
all_columns
}
}
Case Statements
To enable if/else functionality when selecting fields, you can use a case statement in the query.
query sampleQuery {
users contact_list {
name
street_addres s
city
state_abbr (case: {if: {equals: "VA", then: "Virginia"}, elseif:
{equals: "MD", then: "Maryland"}, elseif: {equals: "DC", then: "District
of Columbia"}, else: "Other State"})
}
}
In the query above, when the results are returned, if the state_abbr field is "VA", then "Virginia" will
be returned in the record. Likewise if the state_abbr field is "MD" or "DC", it will return "Maryland" or
"District of Columbia", respectively. If the state_abbr field is any other value, "Other State" will be
returned.
If you need to create a case statement that that will be based on multiple fields, you can create a
query that uses the "case" keyword.
query sampleQuery {
users contact_list {
name
street_addres s
city
-------
state_abbr (case: {if: {equals: "VA", then: "Virginia"}, elseif:
{equals: "MD", then: "Maryland"}, elseif: {equals: "DC", then: "District
of Columbia"}, else: "Other State"})
}
}
query sampleQuery {
employees financials_list {
city
state
case (if: {fn sum (parameters: ["salary"], greaterThanEqual: 1000000},
then: "Too much"}, elseif: {fn\_\_sum (parameters: ["salary"], lessThan:
1000000}, then: "Just right"}, else: "Not supplied", alias:
"sum_salaries")
}
}
Filtering
Any table can have filters applied to it When creating the query, put a parentheses after the table
with the filter criteria using the where operator.
query sampleQuery {
users\_\_contact_list (where: {state_abbr: {in: ["VA", "MD"]}}) {
id
name
street_address
city
state_abbr
}
}
In this query, only contacts whose state abbreviation is in "VA" or "MD" will be returned.
Multiple filters can be applied to a table, by default, multiple filters are applied using "AND" logic.
If you need to specify a filter on a separate table (for instance if you are using an "OR" clause on
separate filter conditions) then qualify the column name with the schema and table in
the schema table column format (each separated by two underscores).
query sampleQuery {
-------
users\_\_contact_list (where: {state_abbr: {in: ["PA", "IL"]}, city:
{equals: "Springfield"}}) {
id
name
street_address
city
state_abbr
}
}
Here all users who reside in the city of Springfield and who live in Pennsylvania or Illinois will be
returned.
Multiple filter conditions can also be applied with "OR" logic.
query sampleQuery {
users\_\_contact_list (where: {state_abbr: {equals: "CT"}, or: {city:
{equals: "New York"}}}) {
id
name
street_address
city
state_abbr
}
}
This query will return all users who live in Connecticut or their city of residence is New York.
Filter conditions can also be nested (the equivalent of parentheses in order of operators).
query sampleQuery {
users\_\_contact_list (where: {state_abbr: {equals: "CT"}, or: {city:
{equals: "New York"}, and: {street_address: {like: "%Avenue of the
Americas"}}}}) {
id
name
street_address
city
state_abbr
}
-------
}
This query will get users who reside in Connecticut or they live in New York and their street address
is on "Avenue of the Americas". (Equivalent to: (state_abbr=CT OR (city="New York" AND
street_address LIKE "%Avenue of the Americas"))).
Available Filter Operators
When working with string values, most operators are case-insensitive. If applicable, there will be a
case-sensitive version availabe in the format {operator} Sensitive.
• beginsWith
o Purpose: Finds values that will start with the supplied value,
o Example: street_address : {beginsWith: "100"}
o Expected Result: All users with street addresses that start with 100 will be returned,
o beginsWithSensitive is available for case-sensitive queries.
• between
o Purpose: Finds values that are between two values, inclusive of the lower and upper
values.
o Example: age: {between: {lower: 20, upper: 30}}
o Expected Result: All users who have an age greater than or equal to 20 and less than
or equal to 30 will be returned,
o betweenSensitive is available for case-sensitive queries.
• contains
o Purpose: Finds records that have the supplied value anywhere in the field,
o Example: street_address : {contains: "Main"}
o Expected Result: All users who have an address that contains "Main" will be returned.
• endsWith
o Purpose: Finds values that will end with the supplied value,
o Example: street_address : {endsWith: "St."}
o Expected Result: All users with street addresses that end with St. will be returned,
o endsWithSensitive is available for case-sensitive queries.
• equals
o Purpose: Finds values that will equal the supplied value,
o Example: state_abbr: {equals: "CA"}
o Expected Result: All users in California will be returned,
o equalsSensitive is available for case-sensitive queries.
• excludes
o Purpose: Finds values that will not be like the supplied value anywhere in the field
(the opposite of contains),
o Example: street_address : {excludes: "Main"}
o Expected Result: All users whose street address does not include Main will be
returned.
o excludesSensitive is available for case-sensitive queries.
• greaterThan
o Purpose: Finds values that are greater than the supplied value.
-------
o Example: age : { greaterThan : 30}
o Expected Result: All users with an age of 31 or higher will be returned,
o greaterThanSensitive is available for case-sensitive queries.
greaterThanEqual
o Purpose: Finds values that are greater than or equal to the supplied value,
o Example: age: {greaterThanEqual: 30}
o Expected Result: All users with an age of 30 or higher will be returned,
o greaterThanEqualSensitive is available for case-sensitive queries.
in
o Purpose: Finds values that are in the supplied list,
o Example: state_abbr: {in: ["MS", "AL", "LA"]}
o Expected Result: All users in the states of Mississippi, Alabama, and Lousiana will be
returned.
o insensitive is available for case-sensitive queries.
inSubQuery
o Purpose: Finds values that are in a subquery in the query.
o Example: state_abbr : {inSubQuery: insubquery: "state_query"} }
o Expected Result: All users whose state is supplied in the subquery aliased as
"state_query" will be returned,
o insensitive is available for case-sensitive queries.
lessThan
o Purpose: Finds values that are less than the supplied value,
o Example: age: {lessThan: 30}
o Expected Result: All users with an age of 29 or lower will be returned,
o lessThanSensitive is available for case-sensitive queries.
lessThanEqual
o Purpose: Finds values that are less than or equal to the supplied value,
o Example: age: {lessThanEqual: 30}
o Expected Result: All users with an age of 30 or lower will be returned,
o lessThanEqualSensitive is available for case-sensitive queries.
like
o Purpose: Finds values that are similar to the supplied value. Uses the % character as a
wildcard.
o Example: street_address : {like: "%Main St."}
o Expected Result: All users with an any address that begins with Main St. will be
returned.
o likeSensitive is available for case-sensitive queries.
notBeginsWith
o Purpose: Finds values that will not start with the supplied value,
o Example: street_address : {notBeginsWith: "100"}
o Expected Result: All users with street addresses that do not start with 100 will be
returned.
o notBeginsWithSensitive is available for case-sensitive queries.
notEndsWith
o Purpose: Finds values that will not end with the supplied value,
o Example: street_addres s : {notEndsWith: "St."}
-------
o Expected Result: All users with street addresses that do not end with St will be
returned.
o notEndsWithSensitive is available for case-sensitive queries.
• notEquals
o Purpose: Finds values that are not equal to the supplied value,
o Example: state_abbr: {notEquals: "FL"}
o Expected Result: All users who do not reside in Florida will be returned,
o notEqualsSensitive is available for case-sensitive queries.
• notln
o Purpose: Finds values that are not in the supplied list,
o Example: state_abbr: {in: ["TX", "OK", "KS"]}
o Expected Result: All users who are not in the states of Texas, Oklahoma, and Kansas
will be returned,
o notlnSensitive is available for case-sensitive queries.
• notLike
o Purpose: Finds values that are not similar to the supplied value. Uses the % character
as a wildcard.
o Example: street_address : {notLike: "%Main St."}
o Expected Result: All users with an any address that do not begin with Main St. will be
returned.
o likeSensitive is available for case-sensitive queries.
• null
o Purpose: Finds values that are either null or not null,
o Example: state_abbr: {null: true}
o Expected Result: All users who do not have a value for state will be returned,
o Example: state_abbr: {null: false}
o Expected Result: All users who do have a value for state will be returned.
• regex
o Purpose: Finds values that match the supplied regular expression.
o Example: street_address : {regex: " (main) {1, 3 } " }
o Expected Result: All users who have a street address that contains main at least once
and at most three times,
o regexSensitive is available for case-sensitive queries.
Casting fields in filters
When needing to cast a field to a different type to work with a filter, use the cast attribute in the
filter.
query sampleQuery {
users\_\_contact_list (where: {street_number: {like: "%10%", cast:
"text"}}) {
id
name
-------
address (alias: "street_address")
}
}
In the example above, the results will be filtered by the street number. Since the street number is
stored as an integer, it is cast to a text field so the like operator can be used.
Valid Field Cast Types
• date
• decimal
• float
• integer
• number
• text
Using functions in a filter
You can use the LPad, Replace, and Trim (along with LTrim and RTrim) functions in the filter. To do
so, add the function as an attribute for the field in the where clause.
• Ipad - Left-pads a string with a specified character. Example: (where : {age: {like:
"%l", lpad: [3, " 0 "}}). The first parameter is the length, the second is the character
to pad with, the default pad character is a single space (" ").
• replace - Replaces the pattern in the database field values with the replacement text.
Example: "(where: {email: {like: "@gmail.com", replace: "."]}}). The first parameter is the
pattern to search for, the second parameter is the text to replace the pattern with, the default
replacement text is an empty string.
• trim - Trim the string. Example: (where : {age: {like: "%1", trim: "123"}}).The
parameter is the characters to trim, ihe default trim is a single space (" ").
• Itrim and rtrim - Trim the left or right of the string, similar to trim.
Dates
You can filter by dates and datetimes for fields that are of a date data type. For dates, the value must
be in the format yyyy-mm-dd. For datetimes, the value must be in the format yyyy-mm-dd
hh :mm : ss. Datetimes use the 24 hour format.
query sampleQuery {
users\_\_contact_list (where: {start_date: {greaterThanEqual: "2021-05-
21"}}) {
id
name
address (alias: "street address")
-------
}
}
In the example above, the results will be filtered to get the users who started on or after May 21,
2021.
query sampleQuery {
users\_\_contact_list (where: {start_date: {greaterThanEqual: "2021-06-13
15:30:00"}}) {
id
name
address (alias: "street_address")
}
}
In the example above, the results will be filtered to get the users who started on or after June 13,
2021 at 3:30 PM.
Joins
Tables can be joined to each other in queries. A joined table can still have filters, orderBy, and
groupBy clauses applied to it.
Tables can be joined using the equals, notEquals, equalslnsensitive, and notEqualslnsensitive
operators. When using the equals or notEquals operators, the join will be equalivalent to
equalsSensitive and notEqualsSensitive, respectively.
query sampleQuery {
users contact_list (where: {state_abbr: {in: ["VA", "MD"]}}) {
id
name
street_address
city
state_abbr
users orders (left_outer_join: {users contact_list id: {equals:
user_id}}) {
order_id
order_name
order_date
}
}
-------
}
This query will be run against the contactjist and orders table in the users schema. A left outer join
will be performed on the contactjist table's id column and the orders table's userjd column.
Join Operator Types
• cross_join: Cross Join
• full_outer_j oin: Full Outer Join
• hash_j oin: Hash Join
• inner_j oin: Inner Join
• left_j oi n: Left Join
• left_outer_join: Left Outer Join
• right_j oin: Right Join
• right_outer_j oin: Right Outer Join
• outer_j oin: Outer Join
Functions in Joins
Joins support the use of functions when joining tables. Trim (along with LTrim and RTrim), LPad,
Replace, and Cast are supported. Here is an example of a join using Ipad:
query joinQuery {
users contact_list {
name
street_address
form_type_ind
users orders (left_outer_join: {users contact_list id: {equals:
{user_id: {lpad: [10, '0']}}, lpad: [10, '0']}}) {
order_id
order_name
order_date
}
}
}
Subqueries
The API supports a limited version of subqueries. A subquery can be created similar to a join, only
using the subquery parameters. A subquery can still have filters, orderBy, and groupBy clauses
applied to it.
-------
query sampleQuery {
users contact_list (where: {state_abbr: {in: ["VA", "MD"]}}) {
id
name
street_address
city
state_abbr
users orders (subquery: {alias: "orders"}) {
order_id
order_name
order_date
}
}
}
This query will be run against the contactjist and orders table in the users schema. A subquery will
be performed on the orders table as it pulls in the orderjd, order_name, and order_date columns.
If the subquery is to be used with the inSubquery filter, then the alias parameter must be
supplied!
Pagination
To paginate through results, the limit and offset parameters can be supplied.
query sampleQuery {
users\_\_contact_list (limit: 10, offset: 20) {
id
name
street_address
city
state_abbr
}
}
In this query, a maximum of 10 records will be returned. The first 20 matching records in the
contactjist table will be skipped and the next 10 records will be returned.
The API will return a maximum of 10,000 records.
Ordering Results
Results can be ordered through the orderBy query parameter.
-------
query sampleQuery {
users\_\_contact_list (orderBy: {city: "asc", state_abbr: "desc"}) {
id
name
street_address
city
state_abbr
}
}
The results in this example will be ordered by first the city field ascending and then by the state_abbr
field descending, "asc", "ascNullsFirst", "ascNullsLast","desc", "descNullsFirst",
and "descNullsLast" are the only valid values for orderBy.
Ordering Joined Columns
When joining tables and wanting to order the results by columns in a joined table, specify all the
joins in the primary table. For the columns in the joined table, use the
format schema table column to specify the column that should be joined. (The schema, table,
and column are separated by double underscores .)
query sampleQuery {
users contact_list (where: {state_abbr: {in: ["VA", "MD"]}}, orderBy:
{name: "asc", users orders order_date: "desc"}) {
id
name
street_address
city
state_abbr
users orders (left_outer_join: {users contact_list id: {equals:
user_id}}) {
order_id
order_name
order_date
}
}
}
-------
In this query, the results will be ordered first in ascending order by the name column in the
users.contactjist table. Then the results will be ordered by the order_date column in the users.orders
table in descending order.
Grouping Results
query sampleQuery {
users\_\_contact_list (groupBy: ["city", "state_abbr"]) {
id
name
street_address
city
state_abbr
}
}
The results in this example will be grouped by the city and state_abbr fields.
Variables
As with normal GraphQL queries, variables can be substituted for values in query parameters, for
example in the limit field or the filter values. The allowed types of values are String, Int, Float, and
OrderBy. The String, Int, and Float values can also be stored in arrays.
Example Query with limit, offset, and order by variables
Query
query query($limit: Int!, $offset: Int!, $orderBy: OrderBy!]) {
users contact_list (limit: $limit, offset: $offset, where: {state_abbr:
{in: ["VA", "MD"]}}, order_by: $orderBy) {
all_columns\_\_
}
}
Variables
{"limit": 10, "offset": 30, "orderBy": {"city": "asc", "state_abbr":
"desc"}}
Here the limit, offset, and orderBy variables will be substituted in the query when it is executed
against the data source. The $limit, $offset variables are required integers. The $orderBy variable is a
required variable in the order by clause structure.
-------
Example Query with where clause variables
Query
query query($state: [String]) {
users contact_list (state_abbr: {in: $state}}) {
all_columns\_\_
}
}
Variables
{"state": ["ND", "SD"]}
Here the $state variable is an array of strings. The values of "ND" and "SD" will be provided to the
query when it runs against the database.
Multiple queries
Multiple queries can be sent in the same request.
query sampleQuery {
users contact_list {
id
name
street_address
city
state_abbr
}
users contact_list\_\_aggregate {
aggregate {
count
}
}
}
They will be run sequentially. This increases the chance of a timeout on the request with no results
being returned.
Returned data
-------
Data is returned from the service in a nested JSON object The primary element is called data. Each
table's results will be returned as a property within the data element For example:
{
"data": {
"users contact list": [
Main Street" },
Broad Street" },
"id": 123, "name": "John Doe", "address": "123
"id": 124, "name": "Jane Doe", "address": "456
users contact_list\_\_aggregate": [
"count": 45 6 }
Export formats
Data can also be returned in the following formats: CSV, Excel, JSON, JSONP, XML, HTML, PDF, and
GraphQL. To export a query's results to a different format, append the format to the end of the URL
If using JSONP, add the callback to wrap the result in with the "callback" query parameter.
CSV: /query/csv Excel: /query/excel XML: /query/xml JSON: /query/json JSONP:
/query/jsonp?callback=callback-value HTML: /query/html PDF: /query/pdf GraphQL: /query/graphql
The GraphQL export format is similar to the regular format (/query). This format is provided to have
greater compatibility with some GraphQL clients.
------- |