WebsiteDiscordBlog

SQL Views

Views in SxT

Space and Time currently supports three types of views.

  1. Standard - A standard view is a virtual table based on the result of executing a SQL statement. A standard view does not save the data resulting from the SQL statement. When you query a standard view, the underlying SQL statement is executed, and the result is presented as a real table.
  2. Materialized - Unlike a standard view, a materialized view saves the results of executing the underlying query. The materialized view can be refreshed at user-defined intervals, saving the new data each time the underlying query is executed.
  3. Parameterized - A parameterized view is like a standard view, except it can take additional parameters supplied when a query is executed against the view.

How to create views in SxT

  1. Views are created using the same Configure Resources (DDL) endpoint that you would use to create a table in Space and Time.
  2. At a minimum, you must supply a Biscuit with CREATE permissions for the resourceId where the view is being created. This works if the underlying table your query runs against was created with access_type=public_readpermissions. If the table your view will query is access_type=permissioned then you will also need to supply a Biscuit with the appropriate SELECT permissions for the underlying table.

Create a standard view

curl -i --request POST \               
     --url "https://<API_URL>/v1/sql/ddl" \
     --header "accept: application/json" \
     --header "authorization: Bearer <ACCESS_TOKEN>" \
     --header "content-type: application/json" \
     --data '
{
  "biscuits": ["EuwBCoEBCg5zeHQ6Y2Fw..."],
  "sqlText": "CREATE VIEW SE_LAND.my_new_view WITH \"public_key=<BISCUIT_PUBLIC_KEY>\" AS SELECT * FROM SE_LAND.ofac_eth"
}
'

Here is a closer look at the SQL we're executing:

CREATE VIEW SE_LAND.my_new_view 
WITH \"public_key=<BISCUIT_PUBLIC_KEY>\" 
AS 
SELECT * 
FROM SE_LAND.ofac_eth;
  1. The view name is my_new_view and its full resourceId is SE_LAND.my_new_view
  2. The underlying table is ofac_ethand its full resourceId is SE_LAND.ofac_eth
  3. The public_key is the biscuit public key side of the pair that was used to grant the create permissions.
    That is to say, the biscuit public key associated with the biscuit that contains this fact: 
sxt:capability("ddl_create", "se_land.my_new_view");
  1. If the underlying table (ofac_eth) was created asaccess_type=permissioned then you would also need to supply a biscuit to the create command with this fact: 
sxt:capability("dql_select", "se_land.ofac_eth");

Create a materialized view

A material view can be created in almost the same way. There is one required difference and one optional.

First, you must tell SxT you want to create a materialized view. That is as simple as adding MATERIALIZED to your SQL CREATE command like this:

CREATE MATERIALIZED VIEW se_playground.my_new_mat_view...

The next flag is optional and allows you to set the refresh_interval (in minutes) for the query. The refresh interval defines how often the query will be executed on the back end. 

CREATE MATERIALIZED VIEW se_playground.my_new_mat_view 
WITH \"public_key=<BISCUIT_PUBLIC_KEY>,refresh_interval=30\" 
AS 
SELECT * 
FROM se_playground.ofac_eth;
  • Currently, materialized views are hard-coded to refresh once a day. In the near term this will change to use the refresh_interval, so it is highly recommended to set to desired length (or 1440 = minutes per day)
  • The access_type for the material view is derived directly from the underlying table and doesn't need to be specified in the create statement.
  • The biscuit public key specified is only used for DDL (create and drop) operations against the materialized view.

Create a parameterized view

A parameterized view is like a standard view, except it can take additional parameters supplied when a query is executed against the view. Therefore, when we create the view, we will also define the parameter (s). 

curl --location '<API_URL>/v1/sql/ddl' \
--header 'Content-Type: application/json' \
--header 'Authorization: Bearer ACCESS_TOKEN' \
--data '{
    "biscuits": [
        "<BISCUIT-WITH-CREATE-PERMS>"
    ],
    "sqlText": "CREATE PARAMETERIZED VIEW se_land.my_new_para_view WITH \"public_key=<BISCUIT_PUBLIC_KEY>\" AS SELECT * FROM se_land.dnft_42 WHERE POINTS>@param1"
    }'

Let's take a look at the SQL:

CREATE PARAMETERIZED VIEW se_land.my_new_para_view 
WITH "public_key=<BISCUIT_PUBLIC_KEY>" 
AS 
SELECT 
    * 
FROM 
    se_lan.dnft_42 
WHERE 
    POINTS > @param1;

As you can see, we specify the parameter with @param1.

Query a view

Querying views is done in the same way that you'd query any other table.

  1. Standard
    • SELECT * FROM SE_LAND.my_new_view
  2. Materialized
    • SELECT * FROM SE_LAND.my_new_mat_view
  3. Parameterized
    • SELECT * FROM SE_LAND.my_new_para_view('param1=4')or with multiple params:
    • SELECT * FROM SE_LAND.my_new_para_view('param1=10','param2=20')

DROP a view:

Please note the VIEW_NAME should be the full resourceId. For example, SE_LAND.my_new_para_view

DROP VIEW <VIEW_NAME>

DROP MATERIALIZED VIEW <VIEW_NAME>

DROP PARAMETERIZED VIEW <VIEW_NAME>

Updated 5 months ago