Create a View#
Views enable you to aggregate data from various datasets (private and/or Core), transform the data, and share the resulting data with applications. An Apperate view is a virtual dataset created from a SQL query that joins multiple datasets.
Here we’ll create a view from two real-time core datasets.
Creating a View#
Here’s how to create a view of stock prices and associated company information. We will use data from the Stock Quote and Company Information core datasets.
Determine equivalent index properties between the datasets. We will join the datasets on these properties. Here are two ways to find dataset index properties:
In the API reference, go to the dataset endpoint’s doc under IEX Cloud Data if it’s a core dataset or under Workspace Endpoints if it’s a private dataset.
In the Path Parameters or Response Attributes sections of the dataset endpoint doc, find the attributes labeled as the
key
index,subkey
index (optional), ordate
index. These are index properties that you can compare with the other dataset’s index properties. For example, the arrows in the image below point to the COMPANY dataset’s date index property calleddate
and its key index property calledsymbol
.Another way to look up index properties is by viewing the dataset properties in the Datasets navigation tree. In the console, click the Data icon to open the Datasets page, expand one of the namespaces (either Core or your workspace), and click one of the dataset names to show the dataset’s property list.
Icons on the left side of the property names indicate the dataset’s
key
index,subkey
index (optional), anddate
index properties. For example, the image below highlights the QUOTE dataset’s date index property calledlastTradeTime
and its key index property calledsymbol
.
Determine all the dataset properties you want to use in your view. You’ll add them to your SQL query in the next step. For example, you can list properties you want from the datasets.
QUOTE properties:
symbol
latestPrice
latestUpdate
COMPANY properties:
symbol
companyName
ceo
website
phone
In a SQL editor in the console (e.g., in the Datasets page or in either dataset’s Database page), construct a query that selects the properties you want (the properties you listed above) and joins the datasets on the equivalent index properties (i.e., the
symbol
properties).The following query, for example, selects properties from the COMPANY and QUOTE datasets, and joins on each dataset’s
symbol
key index property.SELECT c.symbol, q.companyName, q.latestPrice, q.latestUpdate, c.ceo, c.website, c.phone FROM CORE.COMPANY c JOIN CORE.QUOTE q ON c.symbol = q.symbol;
Important
Qualifying a dataset’s indexed property for indexing in a view requires prefixing the property with the first dataset’s name/alias in the SELECT statment. For example, in the above example since CORE.COMPANY is the first dataset, you’d specify SELECT c.symbol to qualify that property for a view’s index. View indexing is discussed in one of the next steps.
Important
WHERE clauses and ON clauses must only operate on indexed properties. See the Unique Index components here.
Important
Apperate does not support using SELECT * queries with views.
Test your query by clicking Run in the bottom right of the SQL editor. The results appear in a table below the SQL editor.
Start creating a view from the query by clicking Create view at the top right of the Results table. The Create view dialog appears.
Name your view (or go with the unique auto-generated name) and specify any dataset index properties from your SELECT statement as view indexes (key, subkey, or date). For example, the
symbol
property below qualifies to select as an index.In the above example, the indexed
symbol
property enables the users to query on a symbol such asAAPL
as the:key
path parameter for theGET /data
method like this:https://WORKSPACE.iex.cloud/v1/data/WORKSPACE/VIEW_COMPANY_QUOTES/AAPL?token=YOUR_TOKEN
See also
See Understanding Datasets for more details on Unique Index components.
Create the view by clicking Create View. A page appears for your new view. You can visit your view’s Overview and Database pages.
Important
The _system prefix (case-insensitive) is reserved for Apperate system tables and columns. You are forbidden to prefix view IDs, dataset IDs, and dataset property names with _system.
Awesome! You created a view with data from two datasets.
Apperate also auto-generated a REST endpoint for the view and an endpoint documentation page (click Open Docs in the Overview)! You can grant people endpoint Read access via a token.
Next let’s get data from the view.
Getting Data From a View#
Here we’ll get view data in two ways:
Example request URL
iexjs JavaScript client module
Example Request URL#
Click the Overview tab to see the view’s example request URL.
Click the Example request to query your view.
Query URL example:
https://WORKSPACE.iex.cloud/v1/data/WORKSPACE/VIEW_COMPANY_QUOTES?token=your_token
Response example:
[ { "ceo": null, "phone": null, "symbol": "002291-CS", "website": null, "companyName": "Foshan Yowant Technology Co. Ltd. - Class A", "latestPrice": 14.7, "latestUpdate": 1674190800000 } ]
The example request returns the last result from the view’s query.
Note
Some companies may not have a current price and some securities may not have company data.
If you specified a Primary index property for the view, you can plug in a property value as the
:key
path parameter. Below we’ll useAAPL
as the parameter value.Query URL example:
https://WORKSPACE.iex.cloud/v1/data/WORKSPACE/VIEW_COMPANY_QUOTES/AAPL?token=your_token
[ { "ceo": "Timothy Cook", "phone": "14089961010", "website": "https://www.apple.com/", "symbol": "AAPL", "companyName": "Apple Inc", "latestPrice": 152.74, "latestUpdate": 1663876800335 } ]
The Example request is the quickest way to try out your view. You can of course use your favorite language and the REST endpoint to get view data. We’ll use JavaScript and the iexjs module next.
iexjs JavaScript Module#
The iexjs module makes querying views a snap.
For example, you can use the apperate.queryData()
iexjs JavaScript library method from JavaScript code or in an npmjs environment, such as RunKit.
Copy the following code into your RunKit editor and replace the CAPITALIZED parameter values mentioned below.
const {Client} = require("@apperate/iexjs")
const client = new Client({api_token: "SECRET_TOKEN", version: "VERSION"});
client.apperate.write({
workspace: "WORKSPACE",
id: "VIEW"})
.then((res) => {
console.log(res);
});
Replace in the Code
Placeholder |
Replace with … |
---|---|
|
Your secret API token |
|
Apperate API version ( |
|
Your workspace name |
|
Target view ID |
The view returns the last query result. For example,
[
{
"ceo": "Ted Love",
"phone": "16507417700",
"website": "http://www.globalbloodtx.com/",
"symbol": "0IVZ-LN",
"companyName": "Global Blood Therapeutics Inc.",
"latestPrice": 67.6049,
"latestUpdate": 1664942400000
}
]
Way to go! Now you know how to query views in your browser and in JavaScript.