Technical Articles
@sap/datasphere-cli & SAP Datasphere Data Builder: CRUD Operations on Tables & Views
This blog post is part of a series of blogs I published about @sap/datasphere-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 Datasphere, @sap/datasphere-cli hosted on npmjs.com, allows you to interact with your SAP Datasphere 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 Datasphere tenant.
Introduction
This blog is part of a blog post series about the Command-Line Interface (CLI) for SAP Datasphere. 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 Datasphere tenant is a daily task for any modeler in the SAP Datasphere world. In this blog, I explain how you can use the SAP Datasphere 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 Datasphere 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 Datasphere – 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 Datasphere tenant, you need to initialize the CLI. Initializing the CLI will download a JSON file from your SAP Datasphere tenant to your local machine. The JSON file contains a description of all available commands you can send to your SAP Datasphere 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:
$ datasphere -H https://datasphere-my-example-company.eu10.hcs.cloud.sap/ -h
Usage: datasphere [options] [command]
Command-Line Interface for SAP Datasphere.
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:
$ datasphere cache init -H https://datasphere-my-example-company.eu10.hcs.cloud.sap/
✔ Do you want to retrieve a temporary authentication code from https://datasphere-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:
$ datasphere -H https://datasphere-my-example-company.eu10.hcs.cloud.sap/ -h
Usage: datasphere [options] [command]
Command-Line Interface for SAP Datasphere.
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:
$ datasphere spaces read -H https://datasphere-my-example-company.eu10.hcs.cloud.sap/ -h
Usage: datasphere 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
$ datasphere spaces read -H https://datasphere-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:
$ datasphere spaces read -H https://datasphere-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 Datasphere tenant:
$ datasphere spaces create -H https://datasphere-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:
$ datasphere spaces delete -H https://datasphere-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 Datasphere is a super cool next step towards a “programmable” application. This is another great example of how CLI can improve your experience when working with SAP Datasphere 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 Datasphere easier. Let me know in the comments!
Further Reading
Blog: New Command-Line Interface for SAP Datasphere – code your way to the cloud!
Blog: Automatically Add Members to Spaces in SAP Datasphere Using @sap/datasphere-cli
Command-Line Interface for SAP Datasphere on npmjs.com
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"
}
}
} }
}
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!
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
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.
Very valuable and helpful for DWC customers. Great blog Jascha! Thank you very much.
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
Thank you Jan Macholz ! We noted down your feedback and will consider it in our future planning for the CLI tool.
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:
This leads to the following Error:
As I couldn't figure this out, my question is, where can I set 'maxBodyLength': Infinity in the DWC CLI?
Thanks ahead!
Lukas
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
Hi Lukas Weixler ,
I wanted to ask what did you do for loading the data into the tables. CLI will only helps us with the definitions, right?. So do we have to load the data into tables one by one or is there any other way?
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:
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
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! 🙂
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?
Thanks,
Sebastian
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
Thank you @Jascha, I have sent an email. I will update this thread once we manage to resolve the issue. Cheers, Sebastian
Any news? I have the same problem.
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!
Ok, thanks!
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!
Hi Xavier Polo how big is the file you‘re trying to send?
88 Mb.
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
For your information, version 2022.11.1 has just been released on npmjs.com.
Thanks for the update, it seems to work now 😉
Hi, can you clarify whether we can create a table using cli without administrator privileges?
Hi,
the user used for authentication via the CLI requires the same privileges as when creating the table in the Data Builder (DW Modeler).
Thanks!
Hi,
We are trying to use command line interface in our server, While trying to create a table inside the DWC server, the command prompt is showing error message as "failed to create or upload based on an import file"
Can you add --verbose option and share the full output?
We got this error message while trying to create the table definitions
Hello, Just to rec-check, are there any option to restrict DW Modeler the access to create a table via command line interface
Hello Everyone, Just to add an additional point here,
When I tried to install the dwc package in my system, It prompted an error message as 'dwc' cannot be identified as an internal or external command. The package was installed in my system, yet it was not identified. I was able to fix this error in the following steps:
Now if you check in the command prompt, you can see the dwc is package is successfully installed.
Hi Jascha Kanngiesser ,
I just wanted to know if we can import csv files using CLI or add the data into tables using CLI.
Thanks,
Atharva.
Hi Atharva Pangerkar
no, this is not possible at the moment, sorry!
hi Jascha Kanngiesser
the CLI is definitely a step in the right direction 🙂 However, to be really useful to create metadata driven automation or deployment processes without manual steps it would be essential if you also could maintain
via the CLI. Are there any plans to add this functionality to the CLI in the future?
Thanks, Ilona
Hi Ilona Tag
thank you for the nice feedback! 🙂 Stay tuned regarding the missing items you mentioned, something's coming along the way sometime soon this year ...
Thanks,
Jascha
hi Jascha Kanngiesser
thank you so much for your quick reply. That sounds very promising! I'll keep my eyes wide open :)!
Thanks, Ilona