Examples
Create script with a CTAS script
curl -d
"[\"CREATE SCRIPT /shared/examples/ctasScript DEFINE AS PROCEDURE ctasScript()
BEGIN CREATE TABLE /shared/examples/ds_inventory/tutorial/OrdersTable as
select OrderId, ProductID, Discount, OrderDate, CompanyName, CustomerContactFirstName,
CustomerContactLastName, CustomerContactPhone
FROM /shared/examples/ViewOrder;
END\"]"
-u "admin:admin" -X POST "http://localhost:9400/rest/execute/v1/actions/dsl/invoke" -H "Content-Type:application/json"
Create script with CTAS script and annotation
curl -d
"[\"CREATE SCRIPT /shared/examples/ctasScript DEFINE AS PROCEDURE ctasScript()
BEGIN CREATE TABLE /shared/examples/ds_inventory/tutorial/OrdersTable as
select OrderId, ProductID, Discount, OrderDate, CompanyName, CustomerContactFirstName,
CustomerContactLastName, CustomerContactPhone
FROM /shared/examples/ViewOrder;
END
SET ANNOTATION 'this script is created using DSL'\"]"
-u "admin:admin" -X POST "http://localhost:9400/rest/execute/v1/actions/dsl/invoke" -H "Content-Type:application/json"
Create Script with an INSERT script
Assuming a table exists, an INSERT script can be used in the create sql script syntax. If the table in which records are inserted does not exist, then the script is created , however it will remain impacted till the table is created.
curl -d
"[\"CREATE SCRIPT /shared/examples/sqlScriptProc DEFINE AS PROCEDURE sqlScriptProc()
BEGIN INSERT INTO /shared/examples/ds_inventory/tutorial/T
( SELECT OrderId, ProductID, Discount, OrderDate, CompanyName, CustomerContactFirstName,
CustomerContactLastName, CustomerContactPhone
FROM /shared/examples/ViewOrder);
END\"]"
-u "admin:admin" -X POST "http://localhost:9400/rest/execute/v1/actions/dsl/invoke" -H "Content-Type:application/json"
Delete script
curl -d "[\"DROP SCRIPT /users/composite/admin/dslscript\"]" -u "admin:admin" -X POST "http://localhost:9400/rest/execute/v1/actions/dsl/invoke" -H "Content-Type:application/json"
Alter script
curl -d "[\"ALTER SCRIPT /shared/examples/ctasScript DEFINE AS PROCEDURE ctasScript()
BEGIN CREATE TABLE /shared/examples/ds_inventory/tutorial/OrdersTable as
select OrderId, ProductID, OrderDate, CustomerContactFirstName, CustomerContactLastName
FROM /shared/examples/ViewOrder;
END
SET ANNOTATION 'this SCRIPT is altered using DSL'\"]"
-u "admin:admin" -X POST "http://localhost:9400/rest/execute/v1/actions/dsl/invoke" -H "Content-Type:application/json"
Relocate/Rename Script
//script relocate -- rename only
curl -d "[\"ALTER SCRIPT /shared/examples/ctasScript RELOCATE AS /shared/examples/dslscript\"]" -u "admin:admin" -X POST "http://localhost:9400/rest/execute/v1/actions/dsl/invoke" -H "Content-Type:application/json"
//script relocate -- path only
curl -d "[\"ALTER SCRIPT /shared/examples/dslscript RELOCATE AS /users/composite/admin/dslscript\"]" -u "admin:admin" -X POST
"http://localhost:9400/rest/execute/v1/actions/dsl/invoke" -H "Content-Type:application/json"
//script relocate -- path and name
curl -d "[\"ALTER SCRIPT /shared/examples/ctasScript RELOCATE AS /users/composite/admin/dslscript\"]"
-u "admin:admin" -X POST "http://localhost:9400/rest/execute/v1/actions/dsl/invoke" -H "Content-Type:application/json"
Get script of a procedure
curl -u "admin:admin" -X POST "http://localhost:9400/rest/execute/v1/actions/query/invoke" -H "Content-Type:application/json"
-d "{\"query\":\"SELECT * FROM model.ALL_RESOURCE_PROPERTIES
WHERE property_name = 'script' AND
metadata_id = (
SELECT PROCEDURE_ID FROM model.ALL_PROCEDURES WHERE PROCEDURE_NAME = 'ctasScript' AND parent_path = '/shared/examples')\",
\"standardSQL\":true}"
[[10132,"Script","java.lang.String","PROCEDURE ctasScript()\nBEGIN\n CREATE TABLE \/shared\/examples\/ds_inventory\/tutorial\/OrdersTable AS\nSELECT OrderId, ProductID, OrderDate, CustomerContactFirstName, CustomerContactLastName\nFROM \/shared\/examples\/ViewOrder\n;\nEND"]]
Script containing multiple DSL statements
CREATE SCRIPT /shared/examples/pubdbScr3 DEFINE AS PROCEDURE pubdbScr3()
BEGIN
CREATE VIRTUAL DATABASE 'PUB5';
CREATE VIRTUAL TABLE /services/databases/PUB5/link2 SET TARGET /shared/examples/ds_orders/tutorial/view1;
END