Select the fields below to construct a simple SQL query. For more advanced examples, review the Query Templates in the tab below.
Retrieve these fields:
Where the following conditions are true:
Zoom or use the Jump to Map Location search box below to target your geolocation. Then click the Start Drawing button to begin:
. Clear the pins by clicking the trash icon; save the query by clicking Save.
Use the controls below to select a date and time range, then click Save. NOTE: Range defaults to the last 24 hours, and all times are shown in UTC (Coordinated Universal Time).
Filter records:
STARTING on
STARTING on
Month
Day
Year
Hour
Minute
Second
Millis
and ENDING on
Month
Day
Year
Hour
Minute
Second
Millis
Calculating available Query Credits…
Validate & PriceSQL Builder Tools
Select a tool below to start authoring queries. You can also choose a query template from the tab below, or type raw SQL directly to your left.
The Afero Data Explorer allows you to design and submit SQL-based query "jobs" across your Afero IoT device historical data. SQL Query Jobs
are distributed across multiple server resources, with different computing nodes working on a part of each job.
Each job has a "Cost" measured in bytes, which can be thought of as "the amount of data that must be reviewed across compute nodes in order to satisfy your query".
In general, more complex queries spanning longer timeframes will have a higher cost. Each month Afero will provide you with a number of credits, in bytes, that you
can use to execute query jobs. The cost of each query job will be displayed when the query is validated (prior to executing the job).
Use system-provided Query Templates (and create templates of your own) to start executing Query Jobs quickly.
On-Demand, Massively Parallel Processing (MPP) Computing
Query jobs against your data are intelligently distributed across computing resources in a parallel fashion, allowing you to analyze huge quantities of Afero IoT data in a short period of time.Petabyte Scale Data Warehousing
Able to support petabyte-scale datasets, meaning you can analyze thousands of terabytes of Afero IoT device historical data if necessary.Fully Managed - No Engineering Resources or Infrastructure Required
No need to design, provision, secure, monitor, scale, or troubleshoot your IoT data. Afero handles everything for you.Secure, Web-Based Access - No Software to Install or Support
No software to install, upgrade, or support. All access to your Afero data happens via this web-based Console.
With the Afero Data Explorer API, you can submit query jobs directly from your own applications without using this web-based interface. Submitting query jobs via the API happens in four (4) steps:
Required Header: Authorization: Bearer [Your Bearer Token]
- (1) Validate and Price (optional) - Validate and price your query. This step makes sure your SQL is valid, and tells you how many Query Credits you should expect to pay for this query.
- (2) Execute - Send your SQL to Afero and get a unique Query Job ID in response (subject to available Query Credits).
- (3) Monitor for Status - After your job is submitted, you poll for job status (RUNNING, DONE, ERROR, etc.) using the Job ID returned above.
- (4) Return Results - After your job is in the DONE state, return the results of your query in a paged fashion using the Job ID returned above.
(1) Validate and Price (optional)
Required Header: Authorization: Bearer [Your Bearer Token]
Sample Request Body
{
"queryText": "SELECT phaseOfDay, count(attributeValue) as attributeCount FROM attribute_history group by phaseOfDay"
}Sample Response Body
{
"queryText": "SELECT phaseOfDay, count(attributeValue) as attributeCount FROM attribute_history group by phaseOfDay",
"valid": true,
"message": "Query is valid SQL",
"totalBytes": 10485760,
"bigQueryJobAllotmentStatisticsDto": {
"monthlyQueryAllotmentBytes": 1397558433,
"currentQueryBytes": 10485760,
"monthlyQueryUsedBytes": 1059061760,
"allotmentDifferenceBytes": 338496673,
"bigQuerySchemaAccessInfoDtoList": []
},
"insufficientCredits": false
}
NOTES: Use the validation endpoint to validate your SQL text, and return a cost in bytes for this query.
The "insufficientCredits" field will be set to 'true' if your query exceeds your alloted credits. Refer to the 'bigQueryJobAllotmentStatisticsDto' field for specifics.
The "insufficientCredits" field will be set to 'true' if your query exceeds your alloted credits. Refer to the 'bigQueryJobAllotmentStatisticsDto' field for specifics.
With the Afero Data Explorer Query Scheduler, you can schedule queries to happen at specific times. Afero will automatically execute your query (subject to available Query Credits), and send you a link to the results via email once the job is complete.
SQL Query Templates
To help you get familiar with the Afero Data Explorer, we've provided some pre-configured SQL Query Templates you can run right away. Simply select a query template from the listing to your left to get started.
After running a query, you can edit and save that query as a Custom Template by selecting the Save button displayed with your query results.
Reports Package Only
- Provide authorized access to your Report Dashboard
- Limit by device type, or allow access to all reports
$99.00 |
month suggested
+ Plus Data Explorer
- Everything that comes with the Reports Package PLUS:
- Allow consumers to access your IoT data
$299.00 |
month suggested
+ Plus Realtime Events
- Everything that comes with the Data Explorer Package PLUS:
- Send real-time event updates to consumers
$899.00 |
month suggested
+ Plus Bulk Data Archive
- Everything that comes with the Realtime Package PLUS:
- Provide bulk data download packages to consumers
$1499.00 |
month suggested
Schema Fields for the ATTRIBUTE_HISTORY table
Use the schema reference below to help you format your Afero SQL queries:
| Name | Data Type | Nullable? | Description | Usage |
|---|---|---|---|---|
| historyId | STRING | REQUIRED | Unique history ID for this record | Globally unique ID for a history event across the Afero Platform |
| historyType | STRING | REQUIRED | History type of the event | History type of the event |
| createdTimestamp | NUMBER | REQUIRED | Timestamp in milliseconds of the event | UTC Timestamp in milliseconds when the event took place |
| accountId | STRING | NULLABLE | Account ID associated with this Afero device | Afero unique account identifier associated with the device that created this event |
| deviceFriendlyName | STRING | NULLABLE | Friendly name of the Afero device | If specified by the end user, the friendly name associated with the device that created this event |
| deviceId | STRING | REQUIRED | Unique ID of the Afero device | Unique ID of the Afero device |
| presentationId | STRING | NULLABLE | Unique Presentation ID of the Afero device | Unique Presentation ID of the Afero device |
| deviceProfileId | STRING | REQUIRED | Unique Profile ID of the Afero device | Unique Profile ID of the Afero device |
| deviceTypeId | STRING | REQUIRED | Unique Device Type ID of the Afero device | Unique Device Type ID of the Afero device |
| localityType | STRING | NULLABLE | Locality Type of the event | Locality Type of the event, for instance CITY or INTLCITY |
| countryIso3166 | STRING | NULLABLE | Country name of the Afero device | ISO 3166 compatible name of the country in which the device resides |
| state | STRING | NULLABLE | State name of the event | US state name where the event took place |
| city | STRING | NULLABLE | City name of the event | City name where the event took place |
| zipCode | STRING | NULLABLE | US zip code of the event | US zip code where the event took place |
| timezoneLong | STRING | NULLABLE | Long version of the timezone of the event | Long version of the timezone where the event took place, like 'America/Los_Angeles' |
| timezoneShort | STRING | NULLABLE | Short version of the timezone of the event | Short version of the timezone where the event took place, like 'Pacific Standard Time' |
| month | STRING | NULLABLE | Month of the event | Month when the event took place, like 'december' |
| dayOfMonth | NUMBER | NULLABLE | Day of the month of the event | Day of the month when the event took place, like '14' |
| dayOfWeek | STRING | NULLABLE | Day of the week of the event | Day of the week when the event took place, like 'wednesday' |
| hourOfDay | NUMBER | NULLABLE | Hour of the day of the event | Hour of the day when the event took place (using 24 hour time), like '14' |
| phaseOfDay | STRING | NULLABLE | Phase of the day of the event | Phase of the day when the event took place relative to the location of the device, like 'afternoon' |
| latitude | FLOAT | NULLABLE | Geographical latitude of the Afero device that created the event | Geographical latitude of the device that created the event, like '37.04566385' |
| longitude | FLOAT | NULLABLE | Geographical longitude of the Afero device that created the event | Geographical longitude of the device that created the event, like '-122.01425576' |
| attributeId | NUMBER | NULLABLE | Attribute ID that sent the event for this record | Attribute ID that sent the event for this record |
| attributeValueRaw | STRING | NULLABLE | Raw attribute value of the event | Raw attribute value of the event |
| attributeDataType | STRING | NULLABLE | Data type of the attribute that sent the event for this record | Data type of the attribute that sent the event for this record, like UINT8 or SINT32 |
| attributeValue | STRING | NULLABLE | String representation of the attribute value | String representation of the attributeValueRaw field, after conversion using the attributeDataType field |
| attributeUpdateReason | STRING | NULLABLE | Reason for the attribute update | Reason for the attribute update |
| attributeUpdateState | STRING | NULLABLE | State for the attribute update | State for the attribute update |
| deviceTimestamp | NUMBER | NULLABLE | Timestamp from the Afero device | Stores a different value from the createdTimestamp, if it is set and timestampFromDevice is true |
| deviceTypeName | STRING | NULLABLE | The friendly name of the device type of the device that generated this record | For instance, "Air Conditioner" or "Connected Fan" or "Industrial Water Pump" |
| attributeName | STRING | NULLABLE | The friendly name of the attribute that this record identifies | For instance, "Power" or "Fan Speed" or "Rotations per Minute" |
| attributeValueName | STRING | NULLABLE | The friendly name of the attribute value this record identifies | For instance, "On" or "High" or "300" |
| timestampFromDevice | BOOLEAN | NULLABLE | Indicates whether a timestamp originated from an Afero device | Indicates whether a timestamp originated from a device |
| dateTime | TIMESTAMP | REQUIRED | The Timestamp in UTC when this event occurred, in millisecond precision, using 24-hour format | For instance, to query all records that occurred on the 5th day of February in 2019, your WHERE clause might include: '...where dateTime between TIMESTAMP('2019-02-05 00:00:00.000') AND TIMESTAMP('2019-02-05 23:59:59.999')... |
| geoPoint | GEOGRAPHY | NULLABLE | Formatted geospatial data in WKT (Well Known Text) format | For instance: POINT(-122.332069 47.606209) |
| Date/Time | SQL Text | Time (ms) | Cost (bytes) | Run Again |
|---|