Skip to Content
Technical Articles
Author's profile photo Jascha Kanngiesser

@sap/dwc-cli & SAP Data Warehouse Cloud Data Builder: CRUD Operations on Tables & Views

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

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-clean             clean the local CLI cache
  cache-init [options]    initialize 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-clean             clean the local CLI cache
  cache-init [options]    initialize 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 was 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 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, –filePath 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 about 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 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

Assigned Tags

      6 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Lukas Weixler
      Lukas Weixler

      Hi Jascha,

      thank you very much for this useful documentation. I am able to retreive information from Space Entities into a local JSON file using powershell.

      Concerning the part "Creating and updating Definitions" I reproduced my own JSON file, named new.json based on the template of an existing space's JSON, did some changes and ran

      dwc spaces create -H https://***-dwc-hcs.eu10.hcs.cloud.sap/ -f .\new.json.

      But even though I add the correct authentication code, I consistently get the error,

      "Failed to create or update space details based on an import file"

      The file is recognized within the shell and as it is from another JSON template I suppose there is also no logical issue within the file. I have attached it at the bottom of this post.

       

      Do you have a suggestion on why this space creation failure appears?

       

      Thanks ahead!

      Lukas

       

      {
      "CLISPACE": {
      "spaceDefinition": {
      "version": "1.0.4",
      "label": "TSTSPACE",
      "assignedStorage": 1000000000,
      "assignedRam": 1000000000,
      "priority": 5,
      "auditing": {
      "dppRead": {
      "retentionPeriod": 30,
      "isAuditPolicyActive": true
      },
      "dppChange": {
      "retentionPeriod": 30,
      "isAuditPolicyActive": true
      }
      },
      "allowConsumption": true,
      "enableDataLake": false,
      "members": [

      {
      "name": "SAC_USER_1",
      "type": "user"
      }
      ],
      "dbusers": {},
      "hdicontainers": {},
      "workloadClass": {
      "totalStatementMemoryLimit": {
      "value": null,
      "unit": "Gigabyte"
      },
      "totalStatementThreadLimit": {
      "value": null,
      "unit": "Counter"
      }
      }
      } }
      }

      Author's profile photo Jascha Kanngiesser
      Jascha Kanngiesser
      Blog Post Author

      Hi Lukas Weixler

      can you add -V to the command and provide the correlation ID? You can also send it to me via DM.

      Thanks!

      Author's profile photo Lukas Weixler
      Lukas Weixler

      Hi Jascha Kanngiesser

      I managed to clear the issue.

      I was lacking DWC Space Administrator and DW Administrator privileges. As I have them now, everything works fine.

      Thank you!

      Lukas

      Author's profile photo Oliver Huth
      Oliver Huth

      Great Blog Jascha! Directly added a link in my Data Marketplace Overview blog as this is a great way for data providers to onboard their views in a scalable & programmatic fashion.

      Author's profile photo Deniz Osoy
      Deniz Osoy

      Very valuable and helpful for DWC customers. Great blog Jascha! Thank you very much.

      Author's profile photo Jan Macholz
      Jan Macholz

      Hi Jascha,

      thanks a lot for this great blog! I really like the idea of "Point and Click meets command line".

      I have used the CLI to copy a whole template space including (remote) tables and views and i works great but still requires a little bit of manual work.

      I found, that the connections of a space are not covered in the created JSON-file.
      When i import the JSON-file the first time to create a copy of a space, the space itself is created but without the tables and views.

      After that I need to manually create the connections in the target space and import the JSON-file again.

      Then, the tables and views are created.
      It would be great if for such scenarios (like training preparation) the connections are also in the JSON-file.

       

      Thanks a lot!

      Jan