Query Database Objects

This topic introduces how to query database objects in a DW service unit through a DW service unit console or by using a Terraform script. The operations supported include:

List databases
Query the details about a database
List schemas in a database
Query the details about a schema

Before you start

Ensure the following:

A DW service unit is available in your environment. For details about how to create a DW service unit, see Create a DW Service Unit.
A database exists in the target DW service unit. For details about how to create a database, see Create a Database.
You have the credentials of a DW user (or cloud account) with access to the target DW service unit.

Use the DW service unit console

1. List databases

Sign in to the DW service unit console.

info
If you have signed in to the Relyt global console, navigate to the DW Service Units page. Locate your target service unit and click Connect to access its console.
In the left sidebar, choose Databases. Then you can view the list of databases and their respective owners.

2. Query the details about a database

In the list of databases, click the target database to expand its details.

On the details page, you can view the owner, size, schemas, and the top 10 largest tables in the database.

3. List schemas in the database

Click the Schemas tab. On the list of schemas, you can check the name and owner of each schema.

4. Check the details about a schema

Click the target schema name. Then you can check the tables and views that exist in the schema.
To check the details about a table or a view in the schema, click the table or view name in the corresponding tab.

The following figures use a table as an example.
- In the Privileges tab, you can view the DW users that have access privileges to the table.
- In the Table Details tab, you can view the table DDL statement.
- In the Columns tab, you can view the name, data type, and some other information of each column.

Use a Terraform script

This section describes how to run a Terraform script to query different information about databases.

Before you start

Download the latest Terraform script.

Ensure you have obtained the following information:

An API key

How to obtain: Sign in to your Relyt console, choose API Keys from the top navigation bar, and click + API Key to create one.
An pair of access key and secret key

How to obtain:
1. Sign in to your DW service unit console and choose Access Control > Open API.
2. Copy the access key and secret key.
  
  If no access key/secret key pair is available, click + Access Key to generate one.
The endpoint for accessing the DW service unit

For details about how to check endpoints, see Query Endpoints.

1. List databases

Following is a code snippet example from the module for listing databases. Configure the parameters according to your needs.

terraform {
  required_providers {
    relyt = {
      source = "relytcloud/relyt"
    }
  }
}

provider "relyt" {
  role     = "SYSTEMADMIN"

  data_access_config = {
  access_key = "<access_key>"
  secret_key = "<secret_key>"
  endpoint = "<endpoint>"
 }
}

data relyt_dwsu_databases databases { 
}

Field description:

Field	Description
`source`	The name of the Relyt plugin. It is fixed to `relytcloud/relyt`.
`role`	The system role of your Relyt cloud account. It is fixed to `SYSTEMADMIN`.
`access_key`	The access key for Open API operations.
`secret_key`	The secret key for Open API operations.
`endpoint`	The endpoint for accessing the DW service unit.

For details about how to obtain the access_key, secret_key, and endpoint, see the instructions provided in Before you start.

Run the following command to export the auth key.
```
export RELYT_AUTH_KEY="<api_key>"
```
Replace <api_key> with the API key you have obtained. For details about how to obtain the API key, see the instructions provided in Before you start.
Run the following command to initialize the Terraform working directory.
```
terraform init
```
Run the following command to apply the module.
```
terraform apply --target=module.datasource
```
When prompted, enter yes to confirm the operation.
When Apply complete is displayed, run the following command.
```
terraform show
```
Information similar to the following will be displayed.

Fields returned for each database include:
- name: the name of each database.
- owner: the owner of the database.

2. Query the details about a database

Following is a code snippet example from the module for obtaining the details about a specific database. Configure the parameters according to your needs.

terraform {
  required_providers {
    relyt = {
      source = "relytcloud/relyt"
    }
  }
}

provider "relyt" {
  role     = "SYSTEMADMIN"

  data_access_config = {
  access_key = "<access_key>"
  secret_key = "<secret_key>"
  endpoint = "<endpoint>"
 }
}

data relyt_dwsu_database databases {
name = "<database_name>"
}

Field description:

Field	Description
`source`	The name of the Relyt plugin. It is fixed to `relytcloud/relyt`.
`role`	The system role of your Relyt cloud account. It is fixed to `SYSTEMADMIN`.
`access_key`	The access key for Open API operations.
`secret_key`	The secret key for Open API operations.
`endpoint`	The endpoint for accessing the DW service unit.
`name`	The name of the database, which uniquely identifies the database in the DW service unit.

For details about how to obtain the access_key, secret_key, and endpoint, see the instructions provided in Before you start.

Run the following command to export the auth key.
```
export RELYT_AUTH_KEY="<api_key>"
```
Replace <api_key> with the API key you have obtained. For details about how to obtain the API key, see the instructions provided in Before you start.
Run the following command to initialize the Terraform working directory.
```
terraform init
```
Run the following command to apply the module.
```
terraform apply --target=module.datasource
```
When prompted, enter yes to confirm the operation.
When Apply complete is displayed, run the following command.
```
terraform show
```
Information similar to the following will be displayed.

Fields returned include:
- name: the name of the database.
- owner: the owner of the database.

3. List schemas in a database

Following is a code snippet example from the module for listing schemas in a specific database. Configure the parameters according to your needs.

terraform {
  required_providers {
    relyt = {
      source = "relytcloud/relyt"
    }
  }
}

provider "relyt" {
  role     = "SYSTEMADMIN"

  data_access_config = {
  access_key = "<access_key>"
  secret_key = "<secret_key>"
  endpoint = "<endpoint>"
 }
}

data relyt_dwsu_schemas schemas {
database = "<database_name>"
}

Fields description:

Field	Description
`source`	The name of the Relyt plugin. It is fixed to `relytcloud/relyt`.
`role`	The system role of your Relyt cloud account. It is fixed to `SYSTEMADMIN`.
`access_key`	The access key for Open API operations.
`secret_key`	The secret key for Open API operations.
`endpoint`	The endpoint for accessing the DW service unit.
`database`	The name of the database.

For details about how to obtain the access_key, secret_key, and endpoint, see the instructions provided in Before you start.

Run the following command to export the auth key.
```
export RELYT_AUTH_KEY="<api_key>"
```
Replace <api_key> with the API key you have obtained. For details about how to obtain the API key, see the instructions provided in Before you start.
Run the following command to initialize the Terraform working directory.
```
terraform init
```
Run the following command to apply the module.
```
terraform apply --target=module.datasource
```
When prompted, enter yes to confirm the operation.
When Apply complete is displayed, run the following command.
```
terraform show
```
Information similar to the following will be displayed.

Fields returned for each schema include:
- catalog: the catalog of the schema. null is returned if the schema is not an external schema.
- database: the database of the schema.
- external: whether the schema is an external schema. true indicates yes; false indicates no.
- name: the name of the schema.
- owner: the owner of the schema.

4. Query the details about an external schema

Following is a code snippet example from the module for checking the details about an external schema. Configure the parameters according to your needs.

terraform {
  required_providers {
    relyt = {
      source = "relytcloud/relyt"
    }
  }
}

provider "relyt" {
  role     = "SYSTEMADMIN"

  data_access_config = {
  access_key = "<access_key>"
  secret_key = "<secret_key>"
  endpoint = "<endpoint>"
 }
}

data relyt_dwsu_external_schema schema {
database = "<database_name>"
catalog  = "<catalog_name>"
name          = "<schema_name>" 
}

Field description:

Field	Description
`source`	The name of the Relyt plugin. It is fixed to `relytcloud/relyt`.
`role`	The system role of your Relyt cloud account. It is fixed to `SYSTEMADMIN`.
`access_key`	The access key for Open API operations.
`secret_key`	The secret key for Open API operations.
`endpoint`	The endpoint for accessing the DW service unit.
`database`	The name of the database.
`catalog`	The catalog of the schema.
`name`	The name of the schema.

For details about how to obtain the access_key, secret_key, and endpoint, see the instructions provided in Before you start.

Run the following command to export the auth key.
```
export RELYT_AUTH_KEY="<api_key>"
```
Replace <api_key> with the API key you have obtained. For details about how to obtain the API key, see the instructions provided in Before you start.
Run the following command to initialize the Terraform working directory.
```
terraform init
```
Run the following command to apply the module.
```
terraform apply --target=module.datasource
```
When prompted, enter yes to confirm the operation.
When Apply complete is displayed, run the following command.
```
terraform show
```
Information similar to the following will be displayed.

Fields returned for the schema include:
- catalog: the catalog of the schema. null is returned if the schema is not an external schema.
- database: the database of the schema.
- external: whether the schema is an external schema. true indicates yes; false indicates no.
- name: the name of the schema.
- owner: the owner of the schema.

Query Database Objects

Before you start​

Use the DW service unit console​

1. List databases​

2. Query the details about a database​

3. List schemas in the database​

4. Check the details about a schema​

Use a Terraform script​

Before you start​

1. List databases​

2. Query the details about a database​

3. List schemas in a database​

4. Query the details about an external schema​

Before you start

Use the DW service unit console

1. List databases

2. Query the details about a database

3. List schemas in the database

4. Check the details about a schema

Use a Terraform script

Before you start

1. List databases

2. Query the details about a database

3. List schemas in a database

4. Query the details about an external schema