Data Virtuality SQL Native Procedure with Variables
Data Virtuality provides procedure named Native for every data source added into Data Virtuality server as a new data source which enables code pushdown to underlying data platforms.
The builtin Data Virtuality native procedure enables to send a SQL query string and a set of variables to the target database platform for SQL developers.
In this Data Virtuality tutorial, I want to show how SQL programmers can execute native SQL queries on data sources of data virtualization platform using Data Virtuality's native procedure calls.
For every data source added to Data Virtuality server as a new resource, Data Virtuality automatically creates at least the native procedure
Here you see, Greenplum, SAP HANA or Redshift databases added as data sources to Data Virtuality.
If the data virtualization platform developers click on the Procedures item, they can see the native procedure listed as follows:
native: Invokes translator with a native query that returns results in array of values
Actually, using native procedure, database developers can send a SQL statement in string format to the data platform and get the results in an array format as tuples.
Here is the default code template for native procedure if you double click on the native procedure.
call "Redshift.native"(
"request" => 'string_request',
/* Mandatory: The native query to execute */
"variable" => object_variable
/* Optional: Any number of varaibles; usage will vary by translator */
);;
Now, using below SQL Native procedure Call or execution, I filter a Redshift database table "Tab_Test_Parameters" by using a column value in a SQL query. SQL developers can realize that the query filter value is replaced with a "?" question mark character in the SQL query. And the variable value is passed in the variable list in the following line.
The passing of the variable is interesting here :)
The native procedure variable parameter takes input parameter values as an array.
In below sample, the ID_Result parameter is string in Amazon Redshift database table and inside the array "(select '.....')" provides this string variable value.
call "Redshift_Results.native"(
"request" => 'SELECT * FROM dbo.Tab_Test_Parameters where ID_Result = ?;',
/* Mandatory: The native query to execute */
"variable" => array( (select '9312BF09-2B5D-4EA6-9077-00000801A6E6') )
/* Optional: Any number of variables; usage will vary by translator */
);;
Here is the output of above Data Virtuality SQL SELECT statement pushed down to underneath data source, Amazon Redshift database.
As developers can realize easily, it is not showing the columns. It returns as an array of column values as tuples. We have to parse the tuples using ARRAYTABLE table-valued function.
Although this input variable is in fact a GUID or unique-identifier column value in an other data platform in SQL Server database table, sending the variable as string is also works without an SQL error in this case.
Another Data Virtuality SQL sample which deletes data from a remote data source SQL Server database table.
In this sample case, within a SQL script block, first I read a GUID value into a local variable.
Then I do some other tasks which are not totally related with our case.
And finally, I delete data rows from the SQL Server table with column value equals to local variable.
BEGIN
declare string guid = '';
guid = (SELECT "ID_Result" FROM "SQLServerKodyaz.Tab_Test_Parameters" limit 1);
...
call "SQLServerKodyaz.native"(
"request" => 'DELETE FROM dbo.Tab_Test_Parameters where ID_Result = ?;',
"variable" => array( (select guid) )
) without return;
END;;
By default DELETE command will return the number of rows affected or deleted, I added "without return" option at the end of the CALL procedure command. I ignore this returned value with "without return" option.
SQL Script Block with Native Procedure Call including Variables on Data Virtuality
I want to share an other code example with SQL developers developing applications on Data Virtuality. In following SQL script or in code block, I read data from a Snowflake Cloud database.
In the first block, I read the customer key of the first row by accessing Snowflake TPCH_SF1 sample database Customer table. I query Snowflake table by accessing it via metadata as a data source table.
In the following Data Virtuality SQL command, I execute a SQL SELECT statement and pass variable values to the Native procedure Call statement.
I pass the same value twice; because I want to see the customer key value in the returned SELECT list as well as use it in the WHERE clause as a filter criteria on Orders table.
BEGIN
declare string CustomerId = '';
CustomerId = (SELECT "C_CUSTKEY" FROM "Snowflake_Storm.SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER" limit 1);
select a.*
from (
call "Snowflake_Storm.native"(
"request" => 'select ?, count(*) from "SNOWFLAKE_SAMPLE_DATA"."TPCH_SF1"."ORDERS" where O_CUSTKEY = ?;',
"variable" => array( CustomerId, CustomerId )
)
) as t,
ARRAYTABLE(
t.tuple
COLUMNS
"Customer Key" string,
"Orders Count" integer
) a;
END;;
So finally, I get the customer key and its orders count as the result of this SQL code sample as seen in below screenshot of Data Virtuality Studio SQL editor.
Please note, if SQL database programmers experience error during execution of native procedure queries with some variables, please consider to CONVERT or CAST the input variable value within the native SQL query into expected data types.
I hope I could provide examples of executing SQL native query using native procedure on data sources or databases to SQL developers building application on Data Virtuality logical data warehouse or data virtualization platform