Is there a way to do a delete statement in In-DB

Hi All,

Is there a way to do a delete statement in in-DB alteryx ? I know that we can do "Delete Data and Append" in creation mode of Write Data-InDB. But there is no way to specify the where condition or its values in here. In the normal Non-IndB tools, we could update the pre or post sql statement by placing it in the macro. But when I do the same for In-DB tools, there is no way to specify the SQL statements here. Could someone help on this.

Regards,

Jeeva.

Macros

In Database

In-Database

Accepted answers

KaneG

In answer to

| And when I try to use any flag, I am not sure how I can update the flag in table based on the months I found out.

The Filter/Formula combo in my last post should do this

| Also, I dont know how I can delete based on flag in In-DB

I don't think that you can.

So, I would say your method goes something like,

Stream your new data into a temp table
Use In-DB tools to compare new data to old data
Flag data to be removed as it is being replaced
Merge new data into the table
Flag old data to be removed as it is too old
Stream 1 record out of the DB and into a block until done tool (like the screenshot above)
First part of the block until done can be anything (a browse for example)
Second part of the block until done is your Output tool with Post SQL

Kane

s_pichaipillai

Jeeva

Since you don’t have Key columns, finding the rows that changed for existing rows on Main table is little tricky

I would like to add some change in your Table .i.e Add HASHBYTES (here I used “SHA2_256” Algorithm)

I have created a sample Table with Hash Key columns as a computed column like below

[HashKey]  AS (CONVERT([bigint],hashbytes('SHA2_256',((((((((([MonthFlag]+'|')+[CarrierTrackingNumber])+'|')+CONVERT([varchar](255),isnull([OrderQty],(0))))+'|')+CONVERT([varchar](255),isnull([ProductID],(0))))+'|')+CONVERT([varchar](255),isnull([SpecialOfferID],(0))))+'|')+CONVERT([varchar](255),isnull([UnitPrice],(0)))))) PERSISTED

Then I connect In- DB for Main table and connect the source file which comes with Moving data and join them (Make sure you do Full Outer Join) and join with All columns

This give you data comparison of both source and main table

Now flag them based on the new and existing row. Here I flagged New Rows that are not exist in main table as “I” and “D” as the existing row which needs to be deleted

Finally load them using write InDB to your Staging table. I selected Delete and Append as this is a Staging Table

One Issue here: when you design your staging table computed column cannot be used while writing In DB. So I Derived the Hashkey at final SQL Script

Finally I created a stored Procedure to Delete ONLY the row changed and Insert all New Rows (UPSERT) and execute the SP using SQLCMD

This proc does Delete and Insert task using Merge SQL Statement

CREATE PROC usp_OrderDetail_UPSERT
AS
MERGE [orderdetail] AS TARGET 
USING (SELECT [monthflag], 
              [carriertrackingnumber], 
              [orderqty], 
              [productid], 
              [specialofferid], 
              [unitprice], 
              [flag],
			  [HashKey]  =  CONVERT(BIGINT,HASHBYTES('SHA2_256',
																		[MonthFlag]
																+ '|' + [CarrierTrackingNumber]
																+ '|' + [OrderQty] 
																+ '|' +	[ProductID]
																+ '|' +	[SpecialOfferID]
																+ '|' +	[UnitPrice]

																))    
FROM [OrderDetail_Stg]
 ) AS SOURCE	ON source .[HashKey] = TARGET.[hashkey] 
WHEN MATCHED AND flag='D' THEN 
  DELETE 
WHEN NOT MATCHED AND flag='I' THEN 
  INSERT ([monthflag], 
          [carriertrackingnumber], 
          [orderqty], 
          [productid], 
          [specialofferid], 
          [unitprice]) 
  VALUES ([monthflag], 
          [carriertrackingnumber], 
          [orderqty], 
          [productid], 
          [specialofferid], 
          [unitprice]);

here is the actual workfow looks like

Call SP.PNG

Here is the step for, how this method does actually works:

Compare the Target and Source using Full Outer and Flag then NEW and OLD rows
Filter ONLY D and I flags rows into Staging table i.e for Delete and Insert
Using MERGE Delete ONLY the rows which are changed in Target then LOAD all New Rows. By this way you no need to delete all the data from Main table
Delete is not the direct option here, because I think , In-DB tool are intended for data retrieval performance
So call it using the Alteryx Event after successful run

Your challenges addressed here:

I don’t want to delete the whole data every time from main table : this method don’t delete all the data but delete ONLY the data which has changed
dont want to create new table every time: yes it just uses the existing staging table and every time delete and append the required data
Also I dont want to stream out the whole data out of In-DB: yes, this does not streams the data out but we did everything using InDB except the Stor Proc which has to be executed using Event. I am sure MERGE does good JOB for huge volume of data

finally , most of DB folks looking the SQL tool to call sql code in Alteryx ,

So please vote for chris idea

http://community.alteryx.com/t5/Alteryx-Product-Ideas/Have-an-SQL-Tool/idi-p/5392

i also raised for executing the Alteryx workflow with piece of Logic in Sequence, so that we can load the data into staing and do some data cleaning using SQL then load it into target. and it fits into your scenorio as well . vote the below too if you find its useful

http://community.alteryx.com/t5/Alteryx-Product-Ideas/Control-Order-of-Execution-of-Workflow-Objects-Container/idi-p/1851

hope this helps and let us know if you still need anything

Happy Data Blending

Jeeva.yxzp

All comments

michael_treadwell

There is no straightforward way to send a DELETE FROM WHERE command using the In-DB tools. Just to test, I tried using a Text Input and Data Stream In tool to send the command to the DB but it fails when no data is returned and you can't issue two statements semicolon separated.

You could always connect to the table, use Filter In-DB to remove the unwanted records, and then write the rest back using the Write Data In-DB tool.

RemcoD

This will only work using 2 Write Data In-DB tools. The first one to write the results into a temporary table, the second to write to the destination table using delete & append. Withouth the temporary table, the records will be deleted and there is nothing to append, because the records aren't loaded in-memory. The result would be an empty table.

jeeva_ganesan

This approach will drastically affect the performance as the data load is huge, we cant afford to have the write operation twice. Thats the main reason we have decided to go with In-DB tools. Alteryx should definitely consider this option, this is a basic delete statement with where condition on it, we cannot delete the whole data or filter out and write the whole data in to temp table just to delete few records.

jeeva_ganesan

@NeilR @s_pichaipillai @KaneG @chris_love Can any of you guys shed some light on this ?

michael_treadwell

If you don't want to write twice and the data has to go back into the same table, you could use Runtime Events to create a new table before the module runs, follow the steps above with Filter In-DB and a single Write Data In-DB, and then use a Runtime Event to change the name of the tables after a successful run.

MarqueeCrew

Are you ok with writing a temp table with keys using in-Db tools followed by a pre-sql action from a called module that uses delete from logic on join to your temp table?

just an airport thought,

Mark

jeeva_ganesan

Thanks for the replies. Hi Mark, I already have the approach mentioned by you implemented. Writing the data in to temporary table, getting the keys alone from that and doing pre-sql delete in the macro which has delete statement with these keys. Since it is an output tool in the macro, I cannot tell the outside workflow once the delete is completed, my data append using write -indb in outside workflow has to happen once the delete inside the macro pre-sql is completed. Is there a way to communicate or block till the delete inside the macro completes ?

Quick Links

This months top contributors

FláviaB 262

JORGE4900 235

caltang 177

DanM 167

IraWatt 157