How to EXPORT procedure result as CSV via SQL?

[Former Member]

Dear HANA Experts,

I face some issues with exporting results of SQLScript procedures as CSV to Unix. 

I defined some procedures that do joins of multiple tables. Procedure looking like this:

CREATE PROCEDURE _SYS_BIC.TABLE_JOIN(OUT TABLE TT_TABLE)

...

TABLE1_RESULT =

SELECT DISTINCT TABLE1.FIELDA,
    TABLE1.FIELDB,
    TABLE1.FIELDC,
    TABLE2.FIELDD,
    TABLE3.FIELDE
   
    FROM MY.TABLE3 AS TABLE3 INNER JOIN MY.TABLE4 AS TABLE3K ON TABLE3.FIELDE = TABLE4.FIELDE
                INNER JOIN MY.TABLE1 AS TABLE1 ON TABLE3.FIELDA = TABLE1.FIELDA
                INNER JOIN MY.TABLE2 AS TABLE2 ON TABLE2.FIELDD = TABLE1.FIELDD;

...

When calling these within HANA Studio all works fine. However now I want to export the result of the joins to the Unix file system using a SQL command like this:

EXPORT  "_SYS_BIC"."my.procedures/TABLE_JOIN" AS CSV INTO '/tmp' WITH REPLACE THREADS 10;

However the result is that I get a CSV file with the raw data from each of the tables involved in the JOIN operation. In this case I find 4 CSV files, one for each of the tables involved in the query,  But I do not get the one table that my procedure generates (using my JOINS, WHERE clauses etc).

Is there any way to write the result of the procedure to a file on Unix? Using SQL commands or HDBSQL?

Thanks

Daniel

[former_member784264]

Is there a way to export the results automatically daily in csv file format

[lbreddemann · Accepted solution]

The EXPORT functionality is there to transport data and structure information - not processing results.

Depending on your needs and the number of result rows you need to export, you can use the save to file functionality from the SAP HANA studio result view. Since SAP HANA studio limits to total number of records returned, this might not be a working solution in all cases.

Alternatively, you can simply execute the query in HDBSQL and save the output to a file. There will be some editing required but it might work.

Of course you could use any other JDBC client to execute the query and save the result. Personally I like http://squirrel-sql.sourceforge.net/ for all things JDBC, but you go wild on your fav. search engine.

A last approach would be to store the result in a table first and export this table then.

Concerning your "threads 10" command argument: you do know that this means the export would process up to ten objects in parallel and not a 10x speedup of a single large table, right?

- Lars

[Former Member · Accepted solution]

Hi Lars,

thank you for your helpful reply.

Indeed exporting out of HANA Studio is not an option for us a we need extracts on an ongoing basis scheduled by background jobs.

Putting the query in HDBSQL is an interesting option and I actually tried that. However unfortunately the performance of HDBSQL does in no way match our expectations. While the export triggered out of HANA Studio to the file system of the HANA instance goes quite fast (write speeds of more than 80 MB/s), the same is terribly slow with hdbsql (about 1 MB/s). This unfortunately is not within our expectations and eats up any performance gain by the in-memory processing we were hoping for. The statement we got from SAP was that this is known and there is basically nothing we can do about it.

Do you have experience with alternative SQL JDBC clients (command line based) under Linux? Are they performing better? And are we able to execute stored procedures with such an approch?

Thanks

Daniel

[lbreddemann · Accepted solution]

Not sure what you base your expectations on, but the EXPORT data facilities are not meant as a data integration technology.

For high-speed data unloading you may want to look into proper tools like SAP DataServices.

Alternatively, you may simply write your own JDBC or ODBC data dump tool that fits exactly your requirements.

HDBSQL is not particularly slow, but will of course suffer from network bandwidth and latency limitations, while the server side HANA export simply avoids that completely.

Given the requirement that you want to export many GBs of result data from calculation views, I tend to say: better review the total data processing chain and look into performing whatever analysis you want to apply on these GBs of data within SAP HANA.

- Lars

[william_andradebatista · Accepted solution]

Since your reply was in 2015, today 2020 would your answar be the same?

Regards,

William