Skip to content

End to end use-case to query the data on workbench

Create Database migration

001_migration.up.sql
CREATE TABLE IF NOT EXISTS customers(
  id serial PRIMARY KEY,
  first_name VARCHAR (50) NOT NULL,
  last_name VARCHAR (50) NOT NULL,
  email VARCHAR (300) UNIQUE NOT NULL);
002_migration.up.sql
INSERT INTO customers (first_name, last_name, email) VALUES
    ('John', 'Doe', 'john.doe@example.com'),
    ('Jane', 'Smith', 'jane.smith@example.com'),
    ('Alice', 'Johnson', 'alice.johnson@example.com');

Create the Database manifest

database.yaml
name: customersdb
version: v1
type: database
description: customersdb database created for testing.
tags:
  - database
database:
  migrate:
    includes: 
      - migration/
    command: up
  compute: runnable-default

Create the Service manifest

Always update the PGRST_OPENAPI_SERVER_PROXY_URI with the latest dataos context.

service.yaml
  version: v1
  name: customers-test1          
  type: service
  service:
    replicas: 1
    ingress:
      enabled: true
      stripPath: true
      path: /customersdb/api/v1    # naming of path what you want but formatting is neccessary.          
      noAuthentication: true
    stack: beacon+rest
    compute: runnable-default
    envs:
      PGRST_OPENAPI_SERVER_PROXY_URI: https://emerging-hawk.dataos.app/customersdb/api/v1

    stackSpec:
      source:
        type: database
        name: customersdb              
        workspace: public
    topology:
      - name: database
        type: input
        doc: customers database connection
      - name: rest-api
        type: output
        doc: serves up the customers database as a RESTful API
        dependencies:
          - database

Create a Depot on the hosted Database Service

Once you have the Database Service up and running, the next step involves creating a Depot on the Postgres Database associated with that Service. This necessitates the formulation of a Postgres Depot Manifest. Detailed configuration specifications are available on the PostgreSQL Depot config templates. In this specific context, certain attributes demand precise configuration, as outlined below:

In this specific context, certain attributes demand precise configuration, as outlined below:

  • Database name: The name of your PostgreSQL database.
  • Hostname/URL of the server: The hostname or URL of the PostgreSQL server.
  • Parameters: Additional parameters for the connection, if required.
  • Username: The username for authentication.
  • Password: The password for authentication.

The resulting manifest file after configuration for this specific context will appear as follows:

depot.yaml
version: v1
name: databasetestdepot
type: depot
layer: user
depot:
  type: JDBC                                # Depot type
  description: To write data to retaildb postgresql database
  external: false
  connectionSecret:                               # Data source specific configurations
    - acl: r
      type: key-value-properties
      data:
        username: "postgres"
        password: "049a1aa1a90e448fb0bd9dbc8babc226"
  spec:                                           # Data source specific configurations
    host: usr-db-dataos-ck-emergingh-dev.postgres.database.azure.com
    port: 5432
    database: customersdb
    subprotocol: postgresql

Apply Depot manifest

Once you have created a Depot manifest, simply copy the or relative path of the manifest file and apply it through the DataOS CLI, using the command given below:

dataos-ctl apply -f ${manifest file path} -w ${workspace-name}
dataos-ctl apply -f depot.yaml -w curriculum

Verify Depot Creation

dataos-ctl resources get -t depot  -w ${workspace-name}

dataos-ctl apply -t depot 
#Expected Output shell NAME | VERSION | TYPE | WORKSPACE | STATUS | RUNTIME | OWNER ----------------------|---------|-------|-----------|--------|---------|----------------- customersdb0curriculum | v1 | depot | | active | | iamgroot customersdbdatabase | v1 | depot | | active | | iamgroot databasetestdepot | v1 | depot | | active | | iamgroot

Target a cluster

Add Depot to Cluster Sources to enable the Minerva/Themis Query Engine to access database, you can create the Postgres Depot. This allows you to query the data and create dashboards using the DataOS Workbench and Atlas

Create a new Cluster manifest with specified depot address. Below is a sample Cluster manifest provided for reference.

cluster.yaml
name: databasetestcluster
version: v1
type: cluster
cluster:
  compute: runnable-default
  type: minerva
  minerva:
    replicas: 1
    resources:
      requests:
        cpu: 2000m
        memory: 2Gi
      limits:
        cpu: 2000m
        memory: 2Gi
    depots:
      - address: dataos://databasetestdepot:default
    debug:
      logLevel: DEBUG
      trinoLogLevel: DEBUG

Apply the Cluster manifest

dataos-ctl resource apply -f ${cluster file name} 
dataos-ctl resource apply -f cluster.yaml 

Verify the creation

dataos-ctl resource get -t cluster 
dataos-ctl resource get -t cluster

Expected Output

NAME         | VERSION |  TYPE   | WORKSPACE | STATUS |   RUNTIME   |     OWNER       
----------------------|---------|---------|-----------|--------|-------------|-----------------
databasetestcluster | v1      | cluster | public    | active | running:1 | iamgroot

Use Bundle Resource

Alternatively, you can use Bundle Resource to execute all Resources in one manifest file only.

The sample Bundle manifest is given below:

bundle.yaml
# Resource meta section
name: databasetestbundle
version: v1beta
type: bundle
layer: user 
tags: 
  - database
  - service
description: this bundle resource is for testing database resource

# Bundle-specific section
bundle:

  # Bundle Resources section
  resources:
    - id: create-database # Resource ID (mandatory)
      workspace: public # Workspace (optional)
      # spec: # Resource spec (mandatory) 
      #   ${{resource spec manifest}}
      file: ./database.yaml # Resource spec file (optional)
      # dependencies: # Resource dependency (optional)
      #   - ${{bundle-depot}}

    - id: create-service # Resource ID (mandatory)
      workspace: public # Workspace (optional)
      file: ./service.yaml # Resource spec file (optional)
      dependencyConditions: # Resource dependency conditions (optional)
        - resourceId: create-database # Resource ID (mandatory)
          status: # Status dependency condition (optional)
            is: # Status is (optional)
              - active


    - id: create-depot # Resource ID (mandatory)
      workspace: public # Workspace (optional)
      file: ./depot.yaml # Resource spec file (optional)
      dependencyConditions: # Resource dependency conditions (optional)
        - resourceId: create-service # Resource ID (mandatory)
          status: # Status dependency condition (optional)
            is: # Status is (optional)
              - active
          runtime: # Runtime dependency condition (optional)
            contains: # Runtime is (optional)
              - running

    - id: create-cluster # Resource ID (mandatory)
      workspace: public # Workspace (optional)
      file: ./database_cluster.yaml # Resource spec file (optional)
      dependencyConditions: # Resource dependency conditions (optional)
        - resourceId: create-depot # Resource ID (mandatory)
          status: # Status dependency condition (optional)
            is: # Status is (optional)
              - active

Query from Workbench

To verify the successful execution of the query, navigate to the Workbench, then select the cluster, depot collections and dataset associated with the created database customersdb.

  • Catalog, Schema, and Table Selection: The user must select the appropriate catalog, schema, and tables within the Workbench interface which here is databasetestdepot ,public and customers respectively .
  • Query Execution: After formulating the query, the user executes it by clicking the 'Run' button.
  • Result Retrieval: The outcomes of the executed query are displayed in the pane situated below the query input area.

 Query Workbench

Quering the customers database through Workbench

For comprehensive details on the features and capabilities of Workbench, refer to the dedicated Workbench documentation.