Generate CDS artifact definitions from SQL statements

[freekmenger]

Why:

When migrating your application from an SQL Server database to an SAP HANA 2 database, and you want to make use of WEB IDE / HANA database containers, the table definitions will have to be created in a different programming language, in HDB CDS files. You would usually also need a .hdbsynonym file if you are setting up cross-container access to these tables. If you want this cross-container access to be table-based, instead of schema-based, you would also need a .hdbgrants file with all the tables.

This migration means you have to translate the SQL ‘Create table’ scripts to .hdbcds, .hdbsynonym and .hdbgrants files. This can be a time-consuming task and I have created an open-source program that can automatically do this conversion.

What:

The python program will take SQL scripts, containing one or multiple create table scripts, and output a .hdbcds, .hdbsyonnym and .hdbgrants file to the desired output folder.

After the files are generated, you can import these definition files into your SAP HANA WEB IDE project and run a build on your HDB Module. Note that you do not need to import the .hdbgrants and .hdbsynonym files, this is only required if you want to setup cross-container access. There are more steps that need to be followed if you want to set up cross-container access, please search for the documentation on this (help.sap.com or SAP Community). Note that the .hdbsynonym file is to be imported in the HDB Module/Container that is to get read access, so a different container than the one that the tables are defined in. Finally, the .hdbgrants file will give access on a table level, you can adjust according to your needs, or you can create your own .hdbgrants file that gives complete schema access.

How:

Download the files from the GitHub page: https://github.com/freekmenger/sql_to_cds_converter

Follow the instructions:

• install python & install appropriate packages (pandas, codecs, re)


• export the SQL create table statements in one big SQL file (SQL Server: Tasks > Generate Scripts)


• download the SQLtoCDS.py program from GitHub (see url above)


• run the following command in the command line or PowerShell (windows): python SQLtoCDS.py [input SQL file with directory] [output directory]


• find the output files in the output directory

All the SQL tables will be grouped into one cds (.hdbcds) file and according .hdbgrants and .hdbsynonym file. If you want to change this:

• either look for the 'context' field in the program and adjust the logic


• export the SQL create script files in batches and rename the context or files manually

The output files can then be imported into you HDB Module/Container.

Extend:

The program is open-source, so you are free to take the source code and extend it. There are some limitations, see terms of the GNU General Public License v3.0 license.