How can I excute sql file in the "WebAPI\ddl\resuls"

11112 · March 5, 2020, 6:12am

Hi I’m Atlas 2.7.6 user in oracle.
I found out the “WebAPI\WEB-INF\classes\ddl\results” folder in deployed “WebAPI.war”.

It’s folder have Query that Query is excuted manually.

So! I wonder that how can I excute that’s Query automatically.

11112 · March 5, 2020, 6:23am

For example “heracles_period” table didn’t excute by migration.

So I excuted the “WebAPI\WEB-INF\classes\ddl\results\heracles_periods.sql” manually.
In that case, I should fix the Query Syntax to Oracle version.

Ajit_Londhe · March 5, 2020, 8:12am

You can use the /ddl/results web service to get the full script, and then execute it in a SQL client.

Using your WebAPI endpoint, you can parameterize the SQL dialect, the results schema name, and the vocab schema name.

For instance, if I had a Redshift instance, and the results schema was named test and the vocab was in a schema named vocab5:

/ddl/results?dialect=redshift&schema=test&vocabSchema=vocab5

11112 · March 5, 2020, 11:24pm

Thanks for your reply @Ajit_Londhe
but I’m so sorry, I can’t understand.

Are you say that I should excute the SQL in DBMS console?
or
Should I fix the "srttings.xml " to make “WebAPI.war”?

I can’t catch “web service”,“WebAPI endpoint” meaning you said.

Ajit_Londhe · March 5, 2020, 11:33pm

You just need to go to the address in your web browser to get the full results schema script.

In the example I gave, you would enter the following URL. Substitute in the appropriate values for the bolded sections.

WebAPI address/ddl/results?dialect=redshift&schema=test&vocabSchema=vocab5

11112 · March 6, 2020, 12:32am

Thank you for your kind @Ajit_Londhe
I totally under stand!
.

" http://localhost:38080/WebAPI/ddl/results?dialect=oracle&schema=WEBAPI&vocabSchema=CDM"

It’s my test server. And I can see the converting SQL to oracle version like image.(oracle is my settings environment).
.

.

<it is well converted SQL to oracle version. >

.

<it is in incorrect parameter & original SQL>

It’s clear now.

11112 · March 6, 2020, 12:46am

Thanks to your tip , it was clearly.

But I have question one more,

It was excute by manual. the manual mean that the SQL should run by myself.
Is there any way to make it automatic?

Ajit_Londhe · March 6, 2020, 1:43am

I don’t think WebAPI includes a step that executes the DDL script automatically – @Chris_Knoll can you confirm?

In the past, I have simply run this code any time we deploy a new CDM database to Atlas.

11112 · March 6, 2020, 5:07am

Thanks a lot @Ajit_Londhe !
Your frank answers have been very helpful.

Chris_Knoll · March 6, 2020, 5:51am

WebAPI only manages it’s own database (where the meta data lives about which CDMs are defined, and what JDBC url to connect to, etc). CDMs are not automatically manged. You need to perform your own ETL and deploy a results schema yourself.