Create a View#

Views enable you to aggregate data from various datasets, 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.

Important

You can create views by joining private datasets and Core Datasets. QUOTE and COMPANY are the first real-time Core Dataset you can use in a view; support for more real-time Core Datasets is ongoing.

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 QUOTE and COMPANY Core Datasets.

  1. 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 Core Datasets if it’s a Core Dataset or under Your Datasets if it’s a private dataset.

      In the Response Attributes section of the dataset endpoint doc, find the attributes labeled as the key index, subkey index (optional), or date 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 called date and its key index property called symbol.

    • 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), and date index properties. For example, the image below highlights the QUOTE dataset’s date index property called lastTradeTime and its key index property called symbol.

  2. 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

  3. 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.

  4. Test your query by clicking Run in the bottom right of the SQL editor. The results appear in a table below the SQL editor.

  5. Start creating a view from the query by clicking Create view at the top right of the Results table. The Create view dialog appears.

  6. 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 as AAPL as the :key path parameter for the GET /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.

  7. 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) for sharing with others (click Share Docs)! 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#

  1. Click the Overview tab to see the view’s example request URL.

  2. 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.

  3. 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 use AAPL 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 …

SECRET_TOKEN

Your secret API token

VERSION

Apperate API version (v1 is the current version)

WORKSPACE

Your workspace name

VIEW

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.