Export data to SQL format

Question

Question

Augusto Estefan · Jul 24, 2020

#Databases #Data Import and Export #Red Hat Enterprise Linux (RHEL) #Caché

Hi!

I need to export some data from a table on CACHE and then import this data to a POSTGRESQL table.

Is this possible? I know I can export this data as a CSV format, but I prefer if there is a way to export that data as SQL format.

Thanks!!!

Discussion (16)1

Log in or sign up to continue

Robert Cemper · Jul 24, 2020

There is a JDBC driver to PostgreSQL available.
Using that drive you define an SQL Gateway for your PostgreSQL and establish a link for your tables:
The projection can be accessed as if was a table in Caché.
Details & HowTo here: https://cedocs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=GSQL_gateway

2 0

score 0 · Answer 1 · 2020-07-28T13:44:07-04:00

Thanks Robert, but I cant get from PostgreSQL server to the Cache Server thats why I need to export the data, but this information help me with another problem.

Thanks a lot.

score 1 · Answer 2 · 2020-07-28T14:20:00-04:00

The so-called SQLformat doesn't exist in Caché (at least not in the last 20 years)
But over the gateway, you can move data from Caché to PostgeSQL. "from a table on CACHE to a POSTGRESQL table"

So you need a trigger to do it:
- Caché has to be the active part here
you can start the upload over a webservice, an REST call, even as an SQLprocedure, or automatically by the scheduler
And if you don't want to touch your target table directly you may have a shadow-copy in PostgreSQL that
then serves as a source for your final updates. ( some INSERT...SELECT....)

So instead of import from CSV, you do an import from a local table in PostgreSQL
Anything is better than CSV.
This would even allow filling your shadow over the day in small steps instead of a big bang (if needed)

score 0 · Answer 3 · 2021-03-29T16:13:30-04:00

@Robert Cemper Hello! Now I got the SQLGateway aimed to the PostgreSQL.

The issue I have now is that I need to migrate/copy the HSPI_Data.Patient table to PostgreSQ. What i have to do to see this table on PostgreSQL?

Thanks to much!!

score 0 · Answer 4 · 2021-03-29T16:59:00-04:00

a few simple steps.

if not existing yet create a table on Postgres with the identic columns that you see in Cache or use an existing target
next use SMP > SQL > Wizzard > Link Table
It creates a class /table that reflects all columns /properties of the linked table. + storage method that can read, write, delete, insert over ODBC / JDBC on the postgreSQL table.

score 0 · Answer 5 · 2021-03-30T12:59:25-04:00

Thanks for the answer, I did what you told me but this create me a new class/table on Cache. I need to link an existant Cache class/table to a postgres table.

Is this possible?

Thanks Robert!!!

score 0 · Answer 6 · 2021-03-30T13:15:31-04:00

you are mixing up 2 things:

one is the class that stores data in Caché
the Class generated as LINKED TABLE that stores its data in postgreSQL.'

so the LINKED describes the structure that is used by generated SQL statements
to work on postgreSQL. Take a look at the storage definition of the class
and see the difference.
But your code in Cache acts as if data were local !!
Either by Objects or by Tables.
if you issue a %Save() on the class it runs a INSERT OR UPDATE undercover.

score 0 · Answer 7 · 2021-03-30T13:22:58-04:00

Now I see the table, but now I need to copy/migrate the data from HSPI_Data.patient (in cache) to PostgreSQL.

How can I do that?

This is my recently created linked table. (i got to move the data there)

score 0 · Answer 8 · 2021-03-30T13:48:48-04:00

Robert Cemper Mar 30, 2021 to Augusto Estefan

eg. with SQL:
INSERT INTO... Linked_Table Select ...from Cache_Table
as described in docs

0 0

score 0 · Answer 9 · 2021-03-31T14:21:13-04:00

Now when I execute this insert give me this error:

I read the doc you gave about inserts but I'm not getting a SQL Error that are described there, any idea of what the problem can be?

Again, thanks a lot for the help Robert!!

score 0 · Answer 10 · 2021-03-31T14:46:49-04:00

Seems just a typo in INSERT statement.
Target Columns must be in parenthesis.

INSERT INTO HSIPIData.datos_usarios2 (addresses)
SELECT Addresses FROM HSPI_Data.patient

score 0 · Answer 11 · 2021-03-31T14:57:45-04:00

Augusto Estefan Mar 31, 2021 to Robert Cemper

Yes, I was thinking the same but happens too when I add the parenthesis.

0 0

score 0 · Answer 12 · 2021-03-31T17:55:22-04:00

as you are in SMP anyhow just try to do an insert manually.
Just 1 row.
you seem to require something else <UNDEFINED>
in addition, if your record exists already You may need an
INSERT OR UPDATE (if this is possible in postgreSQL)

It could be OK, but I wonder that you insert just 1 value
Eventually, there is something required that is missing?
Check the definition of the generated Caché class,

score 0 · Answer 13 · 2020-07-24T17:39:46-04:00

Evgeny Shvarov · Jul 24, 2020

Augusto, what do you mean by “sql format”?

0 0

score 0 · Answer 14 · 2020-07-28T13:42:05-04:00

Hi Evgeny, I dont know if this exist on cache, but on PostgreSQL you can export the data as SQL format (on a dump file).

And I was thinking there is maybe a way to export data from cache as this format (SQL format).

This exists on Cache?

Thanks and sorry for the delay to answer.

score 1 · Answer 15 · 2020-07-28T13:49:04-04:00

And this "SQL format" could be imported then into PostgreSQL only or to any SQL-driven DBMS?

There is no such feature in IRIS, but if you share an example I think it could be baked shortly.

IRIS can export data into Globals format in Global output file (GOF), or XML. which could be imported then into any IRIS.

And you can export CSV file from IRIS class/table.