Exploration of Lens using SQL APIs¶

In this guide, we'll walk you through the steps to connect to a deployed Lens using a SQL client (like psql and dataos-ctl usql interfaces.

Retrieve or create your API key token¶

Existing API key token retrieval¶

If you already possess an API key token, execute the following command to retrieve it:

dataos-ctl user apikey get

Upon successful execution, the output will resemble the following, listing available API keys:

dataos-ctl user apikey get 
# Expected Output
INFO[0000] 🔑 user apikey get...                         
INFO[0000] 🔑 user apikey get...complete                 

                        TOKEN                          │  TYPE  │        EXPIRATION         │                   NAME                    
───────────────────────────────────────────────────────┼────────┼───────────────────────────┼───────────────────────────────────────────
  abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz │ apikey │ 2024-03-02T05:30:00+05:30 │ token_officially_recently_alphabet
  alphabetaomegasigmapizeroonetwothreefourfivesixseven │ apikey │ 2024-02-28T05:30:00+05:30 │ token_newly_mathematics_equations

If there are no apikey’s available, as shown in the code block below:

dataos-ctl user apikey get 
# Expected Output
INFO[0000] 🔑 user apikey get...                         
INFO[0000] 🔑 user apikey get...complete                 

                        TOKEN                          │  TYPE  │        EXPIRATION         │                   NAME                    
───────────────────────────────────────────────────────┼────────┼───────────────────────────┼───────────────────────────────────────────

If you get a similar output, create a new apikey.

Creating a new API key¶

To generate a new API key, use the apikey create command with the following syntax:

CommandExample

dataos-ctl user apikey create -d ${{duration}} -i ${{user-id}} -n ${{apikey-name}}

Command Parameters

-d, --duration flag (optional): Specifies the lifetime of the API key. Acceptable units are m for minutes and h for hours. The default duration is "24h".
-i, --id flag (optional): Identifies the user ID.
-n, --name flag (optional): Assigns a name to the API key.

dataos-ctl user apikey create -d 21h -i iamgroot -n marvel
# Expected Output
INFO[0000] 🔑 user apikey...
INFO[0001] 🔑 user apikey...complete

                            TOKEN                             │  TYPE  │        EXPIRATION         │  NAME
───────────────────────────────────────────────────────────────┼────────┼───────────────────────────┼─────────
    bWFydmVsLmY4NTA1MzMwLThhZWItNDIwMS04MTNjLTUwOWYyMTY3ZDM=  │ apikey │ 2024-02-22T15:30:00+05:30 │ marvel

Copy the value of the Token column for the procedure ahead.

Retrieving Lens name¶

To identify the Lens name for generating a Cluster Token, you'll need to list all Lenses within a specific Workspace. Use the following command to retrieve a comprehensive list of Lenses:

CommandExample

This command requires specifying the workspace name to filter the Lens accordingly.

dataos-ctl resource get -t lens -w ${workspace} -a

# For a workspace named "curriculum", the command and its expected output are as follows:
dataos-ctl resource get -t lens -w curriculum -a
# Expected Output
INFO[0000] 🔍 get...                                     
INFO[0000] 🔍 get...complete                             

              NAME             | VERSION |  TYPE   | WORKSPACE  | STATUS |   RUNTIME   |     OWNER        
-------------------------------|---------|---------|------------|--------|-------------|-------------------
        monitor360            | v1      | cluster | curriculum | active | running:1   |     thor       
        sales360              | v1      | cluster | curriculum | active | running:1   |     ironman  
        finance360            | v1      | cluster | curriculum | active | running:2   |     thanos

For the rest of the documentation, we will use be using the sales360 and the apikey token to create a wrapped token.

Creating a Wrap Token for Lens¶

To create a wrap token for a Lens, you can use the following commands for Unix-based systems (such as Linux or macOS) and Windows, respectively:

Unix-Based SystemsWindows

Replace <your_api_key_here>, <workspace>, and <lens_name> with your actual API key, deploying Workspace name, and Lens Resource name. This command will encode the JSON object into base64 format, producing a wrap token.

echo '{"apikey":"<API_KEY>","lens2":"<WORKSPACE>:<LENS_NAME>"}' | base64

Replace <your_api_key_here>, <workspace>, and <lens_name> with your actual API key, deploying Workspace name and Lens Resource name. This PowerShell command will encode the JSON object into base64 format, creating the wrap token.

[System.Convert]::ToBase64String([System.Text.Encoding]::UTF8.GetBytes('{"apikey":"<your_api_key_here>","lens2": "<workspace>:<lens_name>"}'))

Connecting to Lens using `psql`¶

Open your terminal.
Use the following command to connect to the Lens using psql:
```
psql -h tcp.<dataos-fqdn> -p 6432
```
Replace the <dataos-fqdn> with your DataOS Fully qualified domain name. For instance, liberal-monkey.dataos.app.
Enter your ‘Wrap Token’ in place of password when prompted.

You should now be connected to the Lens. You can verify the connection by listing the available relations using the \dt command:

iamgroot=> \dt

Expected output

Password for user iamgroot: 
psql (16.3 (Ubuntu 16.3-1.pgdg22.04+1), server 14.2 (Lens2/public:sales400 v0.35.41-01))
Type "help" for help.

iamgroot=> \dt
                List of relations
 Schema |       Name        | Type  |    Owner    
--------+-------------------+-------+-------------
 public | account           | table | iamgroot
 public | product           | table | iamgroot
 public | sales             | table | iamgroot
 public | wallet_sales_view | table | iamgroot
(4 rows)

To exit psql, type:
```
iamgroot=> \q
```