A stored procedure is a function that allows you to group together several database processes, save these processes in the database, and easily reuse them.
On the other hand, when you start to use Alteryx, I think that you will do the data processing by Alteryx. However, you might want to continue to use stored procedures that have been used for some time.
How can you use stored procedure in Alteryx
Alteryx can connect many database systems, but there are few databases that Alteryx can support stored procedure natively. This is the list which is shown at the knowledge base.
- Microsoft SQL Server
- Oracle
- SAP HANA
Let’s see how to use the stored procedures at the Microsoft SQL Server. First, I set the stored procedure that is “uspStudentDelete” in “testdb” as follows.
I connect that stored procedure by Input Data tool.
As the above screenshot, you can select it at the Stored Procedures tab. This means that you can simply choose the stored procedures at the any officially supported database system.
How to call the PostgreSQL stored procedures
Alteryx doesn’t officially support calling PostgreSQL stored procedures.
However, there is a way to use the stored procedure. Do you remember the function “Pre SQL Statement” at the Input Data tool?
This “Pre SQL Statement” allow you set the SQL statement that you want to run before “Query”. So, if you set the stored procedure at “Pre SQL Statement”, you can run the stored procedures before SQL. Let’s try it out!
Unfortunately, this is my first stored procedure, so I follow this site to make it. You can see the stored procedure which you make by pdAdmin4 as follows.
That stored procedure contain the statement below. This means that 1000 dollar is transfered from id 1 to id 2.
call transfer(1,2,1000);Let’s set that stored procedure to the “Pre SQL Statement” at the Input Data tool. When you click on the three dot button on the right side of the “Pre SQL Statement”, the “Configure Pre SQL Statements and Stored Procedures” dialogue will be shown. Here, select the SQL Editor tab and set that stored procedure as follows.
After that, when you click on the OK button, it will be applied.
Note that “Run PreSQL on tool configuration” option is checked by default, however if it is checked, that Pre SQL will run at your unintended timing. Actually, if it is checked, the PreSQL will run when you open the configuration of the Input Data tool. I recommend that you should uncheck that option because I think you only want PreSQL to run when the workflow run.
The query is “SELECT * FROM accounts;”, so that Input Data tool execute the stored procedure and show the result.
Let’s try it out
So, the original data is as follows.
Let’s run the workflow with PreSQL. After running, the log is as follows.
According to the log, it seems it worked. Now the data is as follows.
$1000 has been successfully transferred from Bob with ID 1 to Alice with ID 2.
If you only want to run the stored procedure and don’t need to watch the result, you can only count the data as follows.
Note(9th March 2024) : The SQL statement “count(*)” is sometimes performance slow depends on database system. If then, you should do tuning SQL. For example, the SQL below is more fast than “count(*)” on the PostgreSQL.
select count(1) from accounts; Or if you don’t need the count of records, you can also use the below SQL as dummy SQL.
select 1 as "dummy" from accounts Limit 1;Summary
- I explained how to call the PostgreSQL stored procedure by Alteryx.
- If you use the database that Alteryx doesn’t supprt the stored procedures, you can use “PreSQL statement” option to call the stored procedures.
- Alteryx support calling the stored procedures at Microsoft SQL Server, Oracle and SAP HANA, so you can select stored procedures directly.
Contents of the next blog
Next post will be about database, too.
コメント