Create a View#

DEPRECATED

Apperate and its features (including this feature) are deprecated.

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 IEX Cloud Financial 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 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 Apperate API Docs, go to the dataset endpoint’s doc under IEX Cloud Data if it’s an IEX Cloud Financial 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), 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 Console. Go to Datasets. Click on IEX Cloud Data or your workspace name and select the dataset. The dataset’s Database page lists the dataset properties.

      Icons on the right side of the property names indicate the dataset’s key index, subkey index (optional), and date index properties.

      For example, the Historical Equity Prices dataset’s date index property is priceDate and its key index property 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 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)! 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.