This blog post is part of a series of blogs I published about @sap/dwc-cli. Find all blog posts related to this topic in my overview blog post here.

The Node.js-based Command-Line Interface (CLI) for SAP Data Warehouse Cloud, @sap/dwc-cli hosted on npmjs.com, allows you to interact with your SAP Data Warehouse Cloud tenant from the terminal or command line. With version 2022.02 of the CLI you can create, read, update and delete definitions for tables and views in your spaces in your SAP Data Warehouse Cloud tenant.

Introduction

This blog is part of a blog post series about the Command-Line Interface (CLI) for SAP Data Warehouse Cloud. Find the full overview of all available blogs in the overview blog post here.

Working with definitions of tables and views you create in the Data Builder in your SAP Data Warehouse Cloud tenant is a daily task for any modeler in the SAP Data Warehouse Cloud world. In this blog, I explain how you can use the SAP Data Warehouse Cloud CLI tool to not only create, read, update and delete spaces, but also create, read update and delete definitions, more specifically tables and views, graphical as well as SQL views, in your SAP Data Warehouse Cloud tenant.

In other blogs I published earlier I already explain how to install and work with the CLI, plus how to automate the passcode retrieval process to embed the CLI into eg CI/CD processes.

Before you proceed, make sure to install the latest version of the CLI. If you are unsure how to get the latest version, check out my other blog post “New Command-Line Interface for SAP Data Warehouse Cloud – code your way to the cloud!”.

Initializing the CLI

From version 2022.02 and onwards of the CLI, before you can start sending commands to your SAP Data Warehouse Cloud tenant, you need to initialize the CLI. Initializing the CLI will download a JSON file from your SAP Data Warehouse Cloud tenant to your local machine. The JSON file contains a description of all available commands you can send to your SAP Data Warehouse Cloud tenant. Without initializing the CLI first, you will get the standard list of commands available independently of any tenant you work with. To see the available commands for a specific tenant, specify the tenant using the -H, –host option. Add -h, –help to print the list of commands:

$ dwc -H https://dwc-my-example-company.eu10.hcs.cloud.sap/ -h
Usage: dwc [options] [command]

Command-Line Interface for SAP Data Warehouse Cloud.

Options:
  -v, --version           output the current version
  -H, --host <host>       specifies the url host where the tenant is hosted
  -h, --help              display help for command

Commands:
  cache                   work with the local CLI cache
  passcode-url [options]  print the passcode url
  help [command]          display help for command

To initialize the CLI, use the cache-init command:

$ dwc cache init -H https://dwc-my-example-company.eu10.hcs.cloud.sap/
✔ Do you want to retrieve a temporary authentication code from https://dwc-my-example-company.authentication.eu10.hana.ondemand.com/passcode? … yes
✔ Enter your temporary authentication code: … **********

When then printing the list of commands for the same tenant again, you notice that it contains a spaces command:

$ dwc -H https://dwc-my-example-company.eu10.hcs.cloud.sap/ -h
Usage: dwc [options] [command]

Command-Line Interface for SAP Data Warehouse Cloud.

Options:
  -v, --version           output the current version
  -H, --host <host>       specifies the url host where the tenant is hosted
  -h, --help              display help for command

Commands:
  cache                   work with the local CLI cache
  spaces [options]        manage and orchestrate spaces
  passcode-url [options]  print the passcode url
  help [command]          display help for command

Reading Definitions

In my previous blog, I already explained the general usage of the spaces command. With version 2022.02 of the CLI a new option -D, –definitions were added to the spaces read command, allowing you to request definitions of tables and views stored in the specified space:

$ dwc spaces read -H https://dwc-my-example-company.eu10.hcs.cloud.sap/ -h
Usage: dwc spaces read [options]

fetch space details for a specified space

Options:
  -o, --output <output>            specifies the file to store the output of the command
  -S, --space <space>              space ID
  -n, --no-space-definition        read space definition
  -D, --definitions [definitions]  read definitions
  -V, --verbose                    print detailed log information to console (optional)
  -H, --host <host>                specifies the url host where the tenant is hosted
  -p, --passcode <passcode>        passcode for interactive session authentication (optional)
  -h, --help                       display help for command

You probably also notice that there are a few other changes, such as the space ID is now specified by option -S, –space instead of using an argument on the command line, and you can optionally save the returned JSON content in a file using the -o, –output option, which was possible before using the -f, –file-path option. If you only want to retrieve the definitions and omit the space metadata, you can add the -n, –no-space-definition option. If you do not specify the -D, –definitions option no definitions are retrieved from the tenant, and only the space metadata is returned.

To read the space metadata including all existing table and view definitions run

$ dwc spaces read -H https://dwc-my-example-company.eu10.hcs.cloud.sap/ -S MYSPACE -D -p somepassword
{
  "MYSPACE": {
    "spaceDefinition": {
      ...
    },
    "definitions": {
      "some_table": {
        "@EndUserText.label": "some table",
        "kind": "entity",
        "elements": {
          ...
        }
      },
      "some_other_table": {
        "@EndUserText.label": "some other table",
        "kind": "entity",
        "elements": {
          ...
        }
      },
      "another_table": {
        "@EndUserText.label": "another table",
        "kind": "entity",
        "elements": {
          ...
        }
      }
    }
  }
}

The -D, –definitions option lets you also specify a comma-separated array of table or view technical names to reduce the returned list of definitions:

$ dwc spaces read -H https://dwc-my-example-company.eu10.hcs.cloud.sap/ -S MYSPACE -D some_table,another_table -p somepassword
{
  "MYSPACE": {
    "spaceDefinition": {
      ...
    },
    "definitions": {
      "some_table": {
        "@EndUserText.label": "some table",
        "kind": "entity",
        "elements": {
          ...
        }
      },
      "another_table": {
        "@EndUserText.label": "another table",
        "kind": "entity",
        "elements": {
          ...
        }
      }
    }
  }
}

Creating and Updating Definitions

Creating new tables and views or updating definitions of existing entities is as simple as calling the spaces create command and pointing to a JSON file containing the list of definitions. You specify a new property definitions on the same level as the spaceDefinition property. To get a good overview of the correct syntax and available annotations, how to correctly build the SQL query for a view, and how to define table functions, simply retrieve the definition of a space of your choice including the entity definitions and use the returned JSON content as a template for other spaces or definitions you want to create using the CLI. When you created a JSON file, use the spaces create command to push it to your SAP Data Warehouse Cloud tenant:

$ dwc spaces create -H https://dwc-my-example-company.eu10.hcs.cloud.sap/ -f path/to/file.json

Deleting Definitions

Unfortunately, the CLI does not yet support deleting selected definitions of tables and views. You can only delete a complete space, including all its content, through the CLI using the spaces delete command:

$ dwc spaces delete -H https://dwc-my-example-company.eu10.hcs.cloud.sap/ -S MYSPACE -p somepasscode
✔ Do you really want to delete space MYSPACE? … yes

Conclusion

Being able to CRUD on definitions within spaces in SAP Data Warehouse Cloud is a super cool next step towards a “programmable” application. This is another great example of how the CLI can improve your experience when working with SAP Data Warehouse Cloud to the next level.

I’d be happy to hear your thoughts, ideas, and comments on this tool and what you think would be a nice-to-have enhancement to the CLI, making your life and work with SAP Data Warehouse Cloud easier. Let me know in the comments!

Further Reading

Blog: New Command-Line Interface for SAP Data Warehouse Cloud – code your way to the cloud!

Blog: Automatically Add Members to Spaces in SAP Data Warehouse Cloud Using @sap/dwc-cli

Command-Line Interface for SAP Data Warehouse Cloud on npmjs.com

Command-Line Interface for SAP Data Warehouse Cloud on SAP Help

Get your SAP Data Warehouse Cloud 30 Days Trial Account