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

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-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

      23 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

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

      Thank you Jan Macholz ! We noted down your feedback and will consider it in our future planning for the CLI tool.

      Author's profile photo Lukas Weixler
      Lukas Weixler

      Hi Jascha Kanngiesser ,

       

      When creating a space based on a JSON extract from an existing space named startertraining_cp.json, which contains 23 Tables and 40 Views I run:

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

      This leads to the following Error:

      Request failed with message "Request body larger than maxBodyLength limit"
      Failed to create or update space details based on an import file

      As I couldn't figure this out, my question is, where can I set 'maxBodyLength': Infinity in the DWC CLI?

       

      Thanks ahead!

      Lukas

       

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

      Hi Lukas Weixler thanks for pointing this out! The CLI doesn't yet allow to override this by the user, but I'll take this with me and see whether we can address this with the next version of the CLI. In the meantime it should be possible to split up the file into multiple files and send them one by one through the CLI to your tenant. Let me know if that works!

      Thanks,
      Jascha

      Author's profile photo Jens Rottmann-Matthes
      Jens Rottmann-Matthes

      Hi Jascha,

      thank you so much for the work. The CLI helps me a lot with some tasks. There are so many things that would be nice to be able to script with the CLI, but two of my most important topics:

      1. Is it somehow possible to tell the CLI that a view shall be shared to some other space?
        For example assume the following very simple setting:
        I want to create two spaces, each with on view: space S1 with view V1 and space S2 with view V2. And in my data model the view V2 consumes view V1 from space S1. Is it possible to create via the CLI this setup? If not, it would be great if you could enhance it further in that direction.

      Thank's a lot

      Jens Rottmann-Matthes

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

      Hi Jens Rottmann-Matthes thanks for your comment! I circled back your requirement to our product management. I cannot guarantee any developments and timelines, but watch our for any updates in our Road Map! 🙂

      Author's profile photo Sebastian Gesiarz
      Sebastian Gesiarz

      Hello Jascha Kanngiesser ,

      Could you please tell me what could be wrong with the setup if the cache-init runs OK and the spaces command is still not recognized?

      dwc cache-init -H <dwc_url>
      
      dwc -H <dwc_url> -h
      dwc -H <dwc_url> spaces -h


      Thanks,

      Sebastian

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

      Hi Sebastian Gesiarz

      thanks for reaching out! Unfortunately, I cannot tell from the screenshot why it's not considering the previous cache-init command. Can you please append option --verbose and run both commands again?

      You may also send me an e-mail at jascha.kanngiesser@sap.com and we can follow up there.

      Thanks,
      Jascha

      Author's profile photo Sebastian Gesiarz
      Sebastian Gesiarz

      Thank you @Jascha, I have sent an email. I will update this thread once we manage to resolve the issue. Cheers, Sebastian

      Author's profile photo Xavier Polo
      Xavier Polo

      Any news? I have the same problem.

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

      Hi all,

      we found a bug in version 2022.9.0 (sorry for not testing it thoroughly!) which is fixed with the next to-be-released version 2022.11.1 next week. For now, until 2022.11.1 is released, please downgrade to 2022.8.1.

      Thank you!

      Author's profile photo Xavier Polo
      Xavier Polo

      Ok, thanks!

      Author's profile photo Xavier Polo
      Xavier Polo

      Jascha Kanngiesser I have problems updating a space that has 250 entities. The error indicates that in the json there are too many entities. "Request failed with 413 Request Entity Too Large" do you know what is the limit? in the new version, is it extended?

      Thanks!

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

      Hi Xavier Polo how big is the file you‘re trying to send?

      Author's profile photo Xavier Polo
      Xavier Polo

      88 Mb.

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

      Hi Xavier Polo

      the current limit is 25MB as you can read here: https://help.sap.com/docs/SAP_DATA_WAREHOUSE_CLOUD/9f804b8efa8043539289f42f372c4862/3fcbf619f2774b849fa7df58163b3609.html

      You'd have to split your file into separate files and send them one after another to DWC.

      Thanks,
      Jascha

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

      For your information, version 2022.11.1 has just been released on npmjs.com.

      Author's profile photo Sebastian Gesiarz
      Sebastian Gesiarz

      Thanks for the update, it seems to work now 😉